iYtVGTQG

1
2-- CREATE SCHEMA pko;
3-- CREATE TABLE pko.klienci(
4-- id_klienta BIGINT PRIMARY KEY NOT NULL,
5-- nazwisko VARCHAR(50) NOT NULL,
6-- imie VARCHAR(25),
7-- kategoria VARCHAR(25) NOT NULL,
8-- CONSTRAINT ograniczenie_1 CHECK(kategoria = 'indyswidualny' OR kategoria = 'biznesowy' OR kategoria = 'instytucjonalny')
9-- );
10
11-- CREATE TABLE pko.rachunki_bankowe(
12-- nrb CHARACTER(26) PRIMARY KEY NOT NULL,
13-- id_klienta BIGINT NOT NULL,
14-- rodzaj_rachunku VARCHAR(30) NOT NULL,
15-- CONSTRAINT klucz_obcy FOREIGN KEY (id_klienta) REFERENCES pko.klienci(id_klienta)
16-- );
17
18-- CREATE TABLE pko.transakcje(
19-- id_transakcji BIGINT PRIMARY KEY NOT NULL,
20-- nrb CHARACTER(26) NOT NULL,
21-- data_czas_transakcji TIMESTAMP with time zone NOT NULL,
22-- kwota MONEY NOT NULL,
23-- rodzaj_operacji VARCHAR(50),
24-- CONSTRAINT klucz_obcy2 FOREIGN KEY(nrb) REFERENCES pko.rachunki_bankowe(nrb),
25-- CONSTRAINT ograniczenie_2 CHECK(rodzaj_operacji = 'wpłata' OR rodzaj_operacji = 'wypłata' OR rodzaj_operacji = 'prowizja' OR rodzaj_operacji = 'przelew przychodzący' OR rodzaj_operacji = 'przelew wychodzący')
26-- );
27
28-- INSERT INTO pko.klienci VALUES(1,'Jeleń','Karol','indyswidualny');
29-- INSERT INTO pko.klienci VALUES(2,'Łania','Sylwia','biznesowy');
30-- INSERT INTO pko.klienci VALUES(3,'Sarna','Marta','indyswidualny');
31
32
33-- INSERT INTO pko.rachunki_bankowe VALUES('55555555551111111111999999',1,'rozliczeniowo-oszczędnościowy');
34-- INSERT INTO pko.rachunki_bankowe VALUES('33333333330000000000111111',2,'rozliczeniowo-oszczędnościowy');
35-- INSERT INTO pko.rachunki_bankowe VALUES('11111111112222222222333333',2,'oszczędnościowy');
36-- INSERT INTO pko.rachunki_bankowe VALUES('99999999998888888888777777',3,'rozliczeniowo-oszczędnościowy');
37
38-- INSERT INTO pko.transakcje VALUES(1,'33333333330000000000111111','2019-05-01 08:22',4000,'wpłata');
39-- INSERT INTO pko.transakcje VALUES(2,'11111111112222222222333333','2019-05-03 23:56',2000,'przelew przychodzący');
40-- INSERT INTO pko.transakcje VALUES(3,'33333333330000000000111111','2019-05-06 12:33',-1000,'wypłata');
41-- INSERT INTO pko.transakcje VALUES(4,'33333333330000000000111111','2019-05-12 09:41',-400,'wypłata');
42-- INSERT INTO pko.transakcje VALUES(5,'33333333330000000000111111','2019-05-12 09:41',-5,'prowizja');
43
44--KONIEC ZAD 1
45
46
47
48
49
50
51
52
53-- CREATE OR REPLACE FUNCTION pko.obliczSaldoRachunkowe(nrb_f CHARACTER(26),data_poczatkowa TIMESTAMP with time zone,data_koncowa TIMESTAMP with time zone)
54-- RETURNS MONEY AS
55-- $BODY$
56-- DECLARE
57-- wynik MONEY := 0;
58-- BEGIN
59-- IF(data_koncowa M data_poczatkowa) THEN
60-- RAISE NOTICE 'Niepoprawne argumenty dla daty';
61-- RETURN NULL;
62-- END IF;
63-- IF((SELECT rodzaj_rachunku FROM pko.rachunki_bankowe WHERE nrb = nrb_f) IS NULL) THEN
64-- RAISE NOTICE 'Nie ma takiego rachunku';
65-- RETURN NULL;
66-- END IF;
67-- wynik = (SELECT SUM(kwota) FROM pko.transakcje WHERE nrb = nrb_f AND data_czas_transakcji BETWEEN data_poczatkowa AND data_koncowa);
68-- RETURN WYNIK;
69-- END;
70-- $BODY$ LANGUAGE 'plpgsql';
71-- 
72-- SELECT pko.obliczSaldoRachunkowe('33333333330000000000111111','2019-05-01','2019-05-31');
73-- SELECT pko.obliczSaldoRachunkowe('22222222229999999999666666','2019-01-01','2019-04-30');
74
75
76
77
78
79CREATE OR REPLACE FUNCTION pko.wyzwalacz()
80RETURNS TRIGGER AS
81
82	DECLARE
83		prowizja MONEY := 0;
84		indeks INTEGER := 0;
85	BEGIN
86		IF(NEW.rodzaj_operacji IN ('wypłata','przelew wychodzący')) THEN
87			IF((SELECT COUNT(*) FROM pko.transakcje WHERE data_czas_transakcji = NEW.data_czas_transakcji) = 0) THEN
88				RETURN NULL;
89			ELSE
90				IF(NEW.kwota*0.01 M 5::MONEY) THEN
91					prowizja := 5;
92				ELSE
93					prowizja := NEW.kwota*0.01;
94				END IF;
95				indeks := NEW.id_transakcji + 1;
96				INSERT INTO pko.transakcje VALUES(indeks,NEW.nrb,NEW.data_czas_transakcji,prowizja,NEW.rodzaj_operacji);
97			END IF;
98		END IF;
99		RETURN NULL;
100	END;
101	
102LANGUAGE 'plpgsql' VOLATILE;
103
104DROP TRIGGER IF EXISTS operacja_wyzwalacza ON pko.transakcje;
105CREATE TRIGGER operacja_wyzwalacza AFTER INSERT
106ON pko.transakcje FOR EACH ROW
107EXECUTE PROCEDURE pko.wyzwalacz();
108
109INSERT INTO pko.transakcje VALUES(6,'33333333330000000000111111',CURRENT_DATE,-400,'wypłata');