Skrypt T-SQLa tworzący obiekty w bazie danych i wykonujący kilka operacji testowych.

/*
Kadry
*/
CREATE DATABASE Kadry
GO
USE Kadry
GO

/*
tworzenie tabel
*/
CREATE TABLE Pracownik
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  imie  VARCHAR(30),
  nazwisko VARCHAR(30),
  adres VARCHAR(100)
)

CREATE TABLE Dzial
(
  kod VARCHAR(10),
  nazwa VARCHAR(40)
)

CREATE TABLE Umowa
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  idPracownik INT,
  stanowisko VARCHAR(40),
  dzialKod VARCHAR(10),
  stawka MONEY DEFAULT 0,
  wymiarUrlopu INT DEFAULT 0 ,
  urlopWykorzystany INT DEFAULT 0
)

CREATE TABLE Wyplata
(
  idUmowa INT,
  rok INT,
  miesiac INT,
  brutto MONEY,
  podatek MONEY,
  netto MONEY
)

CREATE TABLE Urlop
(
  idUmowa INT,
  dataOd DATETIME,
  dataDo DATETIME,
  dni INT
)
GO

/*
Funkcja zwracajaca imie i nazwisko pracownika dla podanego id
*/
CREATE FUNCTION pracownikImieNazwisko(@idPracownik INT)
RETURNS VARCHAR(65)
AS
BEGIN
  DECLARE @imieNazwisko  VARCHAR(65)
  DECLARE @imie  VARCHAR(30)
  DECLARE @nazwisko  VARCHAR(30)

  SELECT @imie = imie, @nazwisko = nazwisko FROM Pracownik WHERE id = @idPracownik
  
  SET @imieNazwisko = @imie + ' ' + @nazwisko
  RETURN (@imieNazwisko)  
END
GO

/*
Funkcja obliczajace podatek wg stawki 19%
*/
CREATE FUNCTION obliczPodatek(@brutto MONEY)
RETURNS MONEY
AS
BEGIN
  DECLARE @podatek  MONEY
  
  SET @podatek = @brutto * 0.19

  RETURN (@podatek)  
END
GO

/*
procedura dodawania nowej umowy o prace
*/
CREATE PROCEDURE umowaDodaj
  @idPracownik INT,
  @stanowisko VARCHAR(40),
  @dzialKod VARCHAR(10),
  @stawka MONEY,
  @wymiarUrlopu INT
AS
BEGIN
  DECLARE @idUmowa INT
  
  INSERT INTO Umowa (idPracownik, stanowisko, dzialKod, stawka, wymiarUrlopu)
    VALUES (@idPracownik, @stanowisko, @dzialKod, @stawka, @wymiarUrlopu)

END
GO

/*
procedura dodania urlopu
*/
CREATE PROCEDURE urlopDodaj
  @idPracownik INT,
  @dataOd DATETIME,
  @dataDo DATETIME
AS
BEGIN

  DECLARE @idUmowa INT
  DECLARE @dni INT
  
  /* obliczenie ilosc dni urlopu */
  SET @dni = DATEDIFF(day, @dataOd, @dataDo) + 1
  
  BEGIN TRANSACTION
  
    /* znalezienie umowy dla danego pracownika */
    SELECT @idUmowa = id FROM Umowa WHERE idPracownik = @idPracownik

    IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
    BEGIN
      INSERT INTO Urlop (idUmowa, dataOd, dataDo, dni)
        VALUES (@idUmowa, @dataOd, @dataDo, @dni)

      IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
      BEGIN
        /* aktualizacja wykorzystanego urlopu */
        UPDATE Umowa SET urlopWykorzystany = urlopWykorzystany + @dni
         WHERE id = @idUmowa

        IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
        BEGIN
          COMMIT TRANSACTION
        END
        ELSE
        BEGIN
          ROLLBACK TRANSACTION
        END
      END
      ELSE
      BEGIN
        ROLLBACK TRANSACTION
      END
    END
END
GO

/*
procedura dodania wyplaty
*/
CREATE PROCEDURE wyplataDodaj
  @idPracownik INT,
  @rok INT,
  @miesiac INT
AS
BEGIN

  DECLARE @idUmowa INT
  DECLARE @brutto MONEY
  DECLARE @podatek MONEY
  DECLARE @netto MONEY
  
  BEGIN TRANSACTION
    /* wyszukanie id umowy dla danego pracownika oraz stawki*/
    SELECT @idUmowa = id, @brutto = stawka FROM Umowa WHERE idPracownik = @idPracownik

    IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
    BEGIN
      /* oblicznie podatku i netto */
      SET @podatek = dbo.obliczPodatek(@brutto)
      SET @netto = @brutto - @podatek
      
      INSERT INTO Wyplata (idUmowa, rok, miesiac, brutto, podatek, netto)
        VALUES (@idUmowa, @rok, @miesiac, @brutto, @podatek, @netto)

      IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
      BEGIN
        COMMIT TRANSACTION
      END
      ELSE
      BEGIN
        ROLLBACK TRANSACTION
      END
    END
END
GO

/*
  trigger kontrolujacy wykorzystanie urlopu
*/
CREATE TRIGGER urlopSprawdz
ON Umowa
AFTER UPDATE
AS
  DECLARE @pracownik VARCHAR(65)
  DECLARE @komunikat VARCHAR(100)
  
  /* czy ilosc urlopu nie przekroczyla wymiaru */
  SELECT @pracownik = dbo.pracownikImieNazwisko(idPracownik) FROM Umowa WHERE (urlopWykorzystany - wymiarUrlopu) > 0
  IF (@@ROWCOUNT > 0)
  BEGIN
    SET @komunikat = 'Przekroczony wymiar urlopu dla: ' + @pracownik
    RAISERROR(@komunikat, 16, 1)
    ROLLBACK TRANSACTION
  END
GO

/*
  widok z lista umow
*/
CREATE VIEW umowaLista
AS  
  SELECT
    dbo.pracownikImieNazwisko(Umowa.idPracownik) AS pracImNaz,
    Umowa.dzialKod,
    Umowa.Stawka,
    Umowa.wymiarUrlopu,
    Umowa.urlopWykorzystany,
    (Umowa.wymiarUrlopu - Umowa.urlopWykorzystany) AS urlopPozostaly
  FROM
    Umowa
GO

/*
  widok z lista wyplat
*/
CREATE VIEW wyplataLista
AS  
  SELECT
    dbo.pracownikImieNazwisko(Umowa.idPracownik) AS pracImNaz,
    Wyplata.rok,
    Wyplata.miesiac,
    Wyplata.brutto,
    Wyplata.podatek,
    Wyplata.netto
  FROM
    Wyplata, Umowa
  WHERE
    Wyplata.idUmowa = Umowa.id
GO

/*
  dodanie dzialow
*/
INSERT INTO Dzial VALUES ('IT', 'Dzial informatyki')
INSERT INTO Dzial VALUES ('HR', 'Dzial kadr')
GO

/*
  pracownikow
*/
INSERT INTO Pracownik (imie, nazwisko, adres) VALUES ('Jerzy', 'Malec', 'Radom')
INSERT INTO Pracownik (imie, nazwisko, adres) VALUES ('Jan', 'Mars', 'Pionki')
GO

/*
  dodanie umow o prace
*/
EXECUTE umowaDodaj 1, 'projektant', 'IT', 5000, 26
EXECUTE umowaDodaj 2, 'specjalista ds kadr', 'HR', 4000, 20
GO

/*
  dodanie urlopow
*/
EXECUTE urlopDodaj 2, '2008-01-05', '2008-01-20'
GO
-- przekorczenie wymiaru urlopu
EXECUTE urlopDodaj 2, '2008-02-10', '2008-02-20'
GO

/*
  dodanie wyplaty
*/

EXECUTE wyplataDodaj 1, 2008, 1
EXECUTE wyplataDodaj 2, 2008, 1
GO

SELECT * FROM umowaLista
SELECT * FROM wyplataLista
GO