sisteme de gestiune a bazelor de date...

47
SISTEME DE GESTIUNE a BAZELOR DE DATE ÎNDRUMAR DE LABORATOR Liviu Şerbănescu UNIVERSITATEA HYPERION

Upload: truonghanh

Post on 10-Apr-2018

234 views

Category:

Documents


6 download

TRANSCRIPT

Page 1: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

SISTEME DE GESTIUNE aBAZELOR DE DATE

ÎNDRUMAR DE LABORATOR

Liviu Şerbănescu UNIVERSITATEA HYPERION

Page 2: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LISTA LUCRĂRILOR

1. Funcții SQL;

2. Crearea bazelor de date, a sesiunilor şi a utilizatorilor. Creare tabele cu diverse tipuri de

câmpuri;

3 Interogarea bazei de date (cazul unui singur tabel)

4. Popularea bazei de date

5. Combinarea interogărilor

6. Condiţii de selecţie a datelor

7. Expresii condiţionale

8. Tipuri de asocieri între înregistrările unor tabele

9. Interogarea mai multor tabele

10. Moştenirea;

11. Subinterogări

12; Tipul CAST

13. Funcţii SQL la nivel de server

14. Teste.

Page 3: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.1

FUNCȚII SQL

Scop:

1. Introducere în server-side programing

2. Înţelegerea funcțiilor SQL

Exemplificare:

--- FUNCTII -----sunt la nivel de schema-- functiile se deosebesc intre ele prin nume, numarul si tipul argumentelor-- functiile pot sa preia mai multe argumente si intorc cel mult un singur obiect, care poate fi reprezentat de o valoare gen sir de caractere, numar, -- valoare de tip boolean , structura de tip inregistrare sau un tabel-- FUNCTII CE CONTIN COD SQL -- --structura unei astfel de functii este-- [ ----] optionalCREATE [OR REPLACE] function nume_functie -- in loc de DROP ...; CREATE .... ; RETURNS tipul_de_data_intors_de_functie AS$$--- corpul functiei---$$LANGUAGE 'sql';

-------------------------------------------------------------------------------functia c1.ftest0() NU preia si NU intoarce valoriCREATE OR REPLACE function cx.ftest0()RETURNS void AS$$ DROP TABLE IF EXISTS t1; CREATE TEMP TABLE t1(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));$$LANGUAGE 'sql';-- corpul functiei este incadrat de $$ sau $BODY$ si contine doar o comanda SQL------------------------------------------------------------------------------- apelul functiei (executia functiei)

select cx.ftest0() as x1;----------------------------------------------------------------------------

CREATE OR REPLACE function cx.ftest1()RETURNS void AS$$

Page 4: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

--DROP TABLE IF EXISTS t1; --CREATE TEMP TABLE t1(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));INSERT INTO t1(a2,a3,a4) VALUES (7,8,'AA1'),(12,7,'AA2'),(11,5,'AA3');$$LANGUAGE 'sql';-- Pentru functii de tip "language SQL" nu putem avea CREAREA tabelului PERSISTENT si inserarea in aceeasi functie--deoarece secventa SQL este realizata intr-un singur bloc, adica valirea(COMMIT) este realizata la terminarea functiei iar pana astunci tabelul nu exista--sintaxa pentru "language SQL" este VERIFICATA in momentul definirii/explicitarii functiei-- pentru functii de tip SQL este verificata sintaxa comenzilor SQL din corpul functiei-------------------------------------------------------------------------------apel functie varianta 1SELECT cx.ftest1();-------------------------------------------------------------------------------- functie SQL ptr. afisarea continutului lui t1 --VARIANTA1CREATE OR REPLACE function cx.ftest2()RETURNS SETOF t1 AS -- intoarce o grupare de inreg. cu structura inregistrarii definita de t1$$SELECT * FROM t1$$LANGUAGE 'sql';-----------------------------------------------------------------------------apel functieSELECT cx.ftest2(); -- (1,7,...) are semnificatie de structura-- FIECARE INREGISTRAARE se constituie intr-o structura-- intoarce o singura colana ce contine ca data o structura c1.t1----------------------------------------------------------------------SELECT a1,a2,a3,a4 FROM cx.ftest2(); -- functia actioneaza ca o subinterogare!!!!!-- a1,a2,a3,a4 le cunoaste datorita: RETURNS SETOF c1.t1--afiseaza fiecare coloana-----------------------------------------------SELECT a1,wx,a3,a4 FROM cx.ftest2(); -- err , a1-a4 le identifica din cadrul functiei

------------------------------------------------------------------------------ functie SQL ptr. afisarea continutului lui t1 ---VARIANTA2CREATE OR REPLACE function cx.ftest3()RETURNS SETOF RECORD AS -- RECORD este tipul inregistrare -- un grup de campuri$$SELECT a1,a2,a3,a4 FROM t1$$LANGUAGE 'sql';-------------------------------------------------------------------------------apel functie --- varianta A-- daca apelam SELECT a1,a2,a3,a4 FROM c1.ftest3() NU cunoaste numele campurilorSELECT cx.ftest3(); -- rezultat pe o singura coloana-- apel functie --- varianta BSELECT a1,a2,a3,a4 FROM cx.ftest3() AS (a1 int,a2 int, a3 int, a4 varchar(20));-- in AS este definita structura RECORD--rezultatul apare pe patru coloane

-------------------------------------------------------------------------------- functie SQL ptr. afisarea continutului lui t1 --VARIANTA3CREATE OR REPLACE function cx.ftest4()RETURNS SETOF t1 AS$$

Page 5: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

SELECT a1,a2,a3,a4 FROM t1$$LANGUAGE 'sql';-----------------------------------------------------------------------------apel functie --- varianta ASELECT cx.ftest4(); -- campurile sunt agregate intr-un singur camp de tip structura de date-- apel functie --- varianta BSELECT * FROM cx.ftest4(); -- intoarce tabel-- deoarece a1,a2,a3,a4 sunt deja definite-- apel functie --- varianta CSELECT a1,a2,a3 as w3,a4 FROM cx.ftest4(); -- campurile sunt afisate distinct--tipul fiecarui camp este cunoscut din "RETURNS SETOF c1.t1"------------------------------------------------------------------------------ functie SQL ptr. afisarea continutului lui t1 --VARIANTA4CREATE OR REPLACE function cx.ftest5()RETURNS SETOF RECORD AS$$SELECT a1,a2,a3,a4 FROM t1$$LANGUAGE 'sql';-----------------------------------------------------------------------------apel functie --- varianta ASELECT cx.ftest5(); -- grupeaza campurile-- apel functie --- varianta B--SELECT * FROM c1.ftest5();-- nu cunoaste structura inregistrarii--SELECT a1,a2,a3,a4 FROM cx.ftest5();--err: nu cunoaste structura inregistrariiSELECT a1,a2,a3,a4 FROM cx.ftest5() AS (a1 int,a2 int, a3 int, a4 varchar(20));--------------------------------------------------------------------------------

-- functie SQL --- transfer prin argument-- ex: functie ce intoarce un subset de inregistrari--referirea la argumente se realizeaza prin $nr_argument (primul argument este $1)CREATE OR REPLACE function cx.ftest6(b2 int, b3 int) -- tipul variabilei se pune dupa numele acesteia - invers CRETURNS SETOF RECORD AS$$SELECT a1,a2,a3,a4 FROM t1 WHERE a2>$1 AND a3<$2; -- $1 -- primul argument(b2) , $2 - al doilea argument (b3)$$LANGUAGE 'sql';----------------------------------------------------------------------------- apel functieSELECT a1,a2,a3,a4 FROM cx.ftest6(10,15) AS (a1 int,a2 int, a3 int, a4 varchar(20));---------------------------------------------------------------------------

--- functie SQL ce intoarce o valoare de tip booleanCREATE OR REPLACE function cx.ftest7(b2 int)RETURNS BOOLEAN AS$$SELECT EXISTS(SELECT * FROM t1 WHERE a2>$1); -- intoarce TRUE daca exista cel putin o inregistrare in cadrul celui de al doilea SELECT altfel intoarce FALSE$$LANGUAGE 'sql';--------------------------------------------------------------------------------SELECT cx.ftest7(2) as f1;

Page 6: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

----------------------------------------------------------------------------

Desfăşurarea lucrării:

1. Se lansează interfaţa pgAdmin3

2. Se introduc funcții descrise în secțiunea exemplificare

3. Se testează

4. Se extind cerințele pentru alte tipuri de argumente

5. Se extinde lista de argumente

6. Se testează

7. Se trag concluzii

Page 7: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.2

INTRODUCERE ÎN plpgSQL

Scop:

1. Înţegerea conceptelui privind plpgSQL

2. Construcția câtorva funcții simple de tip plpgSQL

Exemplificare

----- PROCEDURI plpgsql ---------------- sunt de cateva ori mai lente decat functiile SQL insa sunt mult mai flexibile

-- Function: c10.gtest1()

-- DROP FUNCTION c10.gtest1();

CREATE OR REPLACE FUNCTION cx.gtest1() RETURNS void AS$$BEGIN --- SPECIFIC plpgsqlDROP TABLE IF EXISTS t2;CREATE TEMP TABLE t2(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));INSERT INTO t2(a2,a3,a4) VALUES (17,18,'AA11'),(112,17,'AA12'),(111,15,'AA13');END$$ LANGUAGE 'plpgsql'; -- apelulselect cx.gtest1();--select * from t2;

---un alt test SQL vs plpgsql -- executie imposibila -- se realizeaza o analiza a codului SQL si nu gaseste c1.t3 pentru cda INSERT--ceea ce in cazul plpgsql nu duce la eroare

CREATE OR REPLACE FUNCTION cx.gtest1sql() RETURNS void AS$$DROP TABLE IF EXISTS t3;CREATE TABLE t3(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));INSERT INTO t3(a2,a3,a4) VALUES (17,18,'AA11'),(112,17,'AA12'),(111,15,'AA13');$$ LANGUAGE 'sql'; -- apelulselect cx.gtest1sql();

Page 8: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

-----------------------------------------------------------EXEMPLU ADAUGA o INREGISTRARE DACA NR. ACESTORA (inainte de adaugare) ESTE IMPAR--SI DOUA INREG. DACA NR. DE INREG ESTE par

-- Function: c1.gtest2()

-- DROP FUNCTION c1.gtest2();

CREATE OR REPLACE FUNCTION cx.gtest2() RETURNS void AS$$DECLARE -- deschide blocul pentru declaratii - acestea pot acea acelasi tip ca si campurile SQLnr_inreg int;BEGINnr_inreg:=-1; -- atribuire -- nu are nici un efectSELECT count(*) FROM t2 INTO nr_inreg; -- rezultatul de la select va fi o singura inreg si un singur camp--instructiune conditionala IF conditie THEN instructiuni_1 [ELSE instructiuni_2 ] END IF;IF NOT (nr_inreg%2)=0 THEN -- daca este impar INSERT INTO t2(a2,a3,a4) VALUES (1001,0,'---');ELSE INSERT INTO t2(a2,a3,a4) VALUES (1002,0,'M'),(1003,17,'N');END IF;END$$LANGUAGE plpgsql;------------------------------------------------------------------ apelulselect cx.gtest2();--select * from t2;-----------------------------------------------------------------IN (implicit) parametru de intrare OUT parametru de iesirecreate or replace function cx.f1(IN a int, IN b int,OUT c int) returns int as$$BEGIN c=a+b; -- de obicei se scrie c:=a+b; altfel poate fi interpretat ca si == -- nu este necesara specificatia return c; deoarece exista OUT c;END$$language plpgsql;-- In cadrul functiei nu exista RETURN, denumirile variabilelor de iesire sunt date prin specificatorul OUT

--------------------------------------------------

Page 9: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

select cx.f1(5,7); -- campul rezultat are numele functiei--------------------------------------------------select * from cx.f1(4,9); -- campul rezulta are numele variabilei de iesire-----------------------------------------------------------------------create or replace function cx.f2(a int, IN b int,OUT suma int, OUT produs int, OUT impartire numeric) as$$BEGIN -- nu am pus RETURNS -- sunt recunoscute denumirile variabilelor suma:=a+b; produs:=a*b; impartire:=a/(b*1.0);END$$language plpgsql;

-- IN (INPUT) este optional, acesta fiind tipul implicit ptr. arguumenteselect produs,suma, impartire from cx.f2(5,7);select produs,suma, CAST(impartire as numeric(3,2)) from cx.f2(5,7);----------------------------------------------------------select * from cx.f2(6,7); --idem-------------------------------------------------------------------------adaugarea codului RETURNS RECORD este optionalacreate or replace function cx.f2_1(a int, IN b int,OUT suma int, OUT produs int, OUT impartire numeric) RETURNS RECORD as$$BEGIN-- sunt recunoscute denumirile variabilelor suma:=a+b; produs:=a*b; impartire:=a/(b*1.0);END$$language plpgsql;

-- IN (INPUT) este optional, acesta fiind tipul implicit ptr. arguumenteselect suma, produs, impartire from cx.f2_1(5,7);

---------------------------------------------------------------------------------------------- operatii elementare cu notificari in plpgsqlcreate or replace function cx.f3() returns text as$$DECLAREnr_ins int; --nr linii adaugate/inseratenr_up int; --nr. linii modificate achR text;total int;BEGIN achR:='OK';--textul este marginit de apostrofDROP TABLE IF EXISTS t4;

Page 10: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

CREATE TEMP table t4(a serial PRIMARY KEY, b int, c varchar(10));INSERT INTO t4(b,c) VALUES (11,'A1'),(21,'B2'),(31,'B3'),(41,'C3'); -- GET DIAGNOSTICS intoarce valori ale variabilelor setate in urma executiei ultimei comenzi

GET DIAGNOSTICS nr_ins = ROW_COUNT; -- RAISE NOTICE -- creaza o NOTIFICARE de la server catre client

RAISE NOTICE 'Au fost adaugate: % inregistrari',nr_ins;-- asemanator cu structura formatului de la printf,, in loc de % pune valoarea din

variabila nr_ins UPDATE t4 SET b=b+2 WHERE c LIKE 'B%';

GET DIAGNOSTICS nr_up = ROW_COUNT; RAISE NOTICE 'Au fost actualizate: % inregistrari',nr_up; SELECT sum(b) FROM c1.t4 INTO total;if total < 60 then achR:='NOK'; end if;return achR;END$$ language plpgsql;-- select nextval('c10.t1_a_seq');select c1.f3();

-- a se testa cu debuger-ul asociat!!!

----- PROCEDURI plpgsql ---------------- sunt de cateva ori mai lente decat functiile SQL insa sunt mult mai flexibile

-- Function: c10.gtest1()

-- DROP FUNCTION c10.gtest1();

CREATE OR REPLACE FUNCTION cx.gtest1() RETURNS void AS$$BEGIN --- SPECIFIC plpgsqlDROP TABLE IF EXISTS t2;CREATE TEMP TABLE t2(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));INSERT INTO t2(a2,a3,a4) VALUES (17,18,'AA11'),(112,17,'AA12'),(111,15,'AA13');END$$ LANGUAGE 'plpgsql'; -- apelulselect cx.gtest1();--select * from t2;

---un alt test SQL vs plpgsql -- executie imposibila -- se realizeaza o analiza a codului SQL si nu gaseste c1.t3 pentru cda INSERT--ceea ce in cazul plpgsql nu duce la eroare

CREATE OR REPLACE FUNCTION cx.gtest1sql() RETURNS void AS

Page 11: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

$$DROP TABLE IF EXISTS t3;CREATE TABLE t3(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));INSERT INTO t3(a2,a3,a4) VALUES (17,18,'AA11'),(112,17,'AA12'),(111,15,'AA13');$$ LANGUAGE 'sql'; -- apelulselect cx.gtest1sql();

-----------------------------------------------------------EXEMPLU ADAUGA o INREGISTRARE DACA NR. ACESTORA (inainte de adaugare) ESTE IMPAR--SI DOUA INREG. DACA NR. DE INREG ESTE par

-- Function: c1.gtest2()

-- DROP FUNCTION c1.gtest2();

CREATE OR REPLACE FUNCTION cx.gtest2() RETURNS void AS$$DECLARE -- deschide blocul pentru declaratii - acestea pot acea acelasi tip ca si campurile SQLnr_inreg int;BEGINnr_inreg:=-1; -- atribuire -- nu are nici un efectSELECT count(*) FROM t2 INTO nr_inreg; -- rezultatul de la select va fi o singura inreg si un singur camp--instructiune conditionala IF conditie THEN instructiuni_1 [ELSE instructiuni_2 ] END IF;IF NOT (nr_inreg%2)=0 THEN -- daca este impar INSERT INTO t2(a2,a3,a4) VALUES (1001,0,'---');ELSE INSERT INTO t2(a2,a3,a4) VALUES (1002,0,'M'),(1003,17,'N');END IF;END$$LANGUAGE plpgsql;------------------------------------------------------------------ apelulselect cx.gtest2();--select * from t2;-----------------------------------------------------------------IN (implicit) parametru de intrare OUT parametru de iesirecreate or replace function cx.f1(IN a int, IN b int,OUT c int) returns int as$$BEGIN c=a+b; -- de obicei se scrie c:=a+b; altfel poate fi interpretat ca si ==

Page 12: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

-- nu este necesara specificatia return c; deoarece exista OUT c;END$$language plpgsql;-- In cadrul functiei nu exista RETURN, denumirile variabilelor de iesire sunt date prin specificatorul OUT

--------------------------------------------------select cx.f1(5,7); -- campul rezultat are numele functiei--------------------------------------------------select * from cx.f1(4,9); -- campul rezulta are numele variabilei de iesire-----------------------------------------------------------------------create or replace function cx.f2(a int, IN b int,OUT suma int, OUT produs int, OUT impartire numeric) as$$BEGIN -- nu am pus RETURNS -- sunt recunoscute denumirile variabilelor suma:=a+b; produs:=a*b; impartire:=a/(b*1.0);END$$language plpgsql;

-- IN (INPUT) este optional, acesta fiind tipul implicit ptr. arguumenteselect produs,suma, impartire from cx.f2(5,7);select produs,suma, CAST(impartire as numeric(3,2)) from cx.f2(5,7);----------------------------------------------------------select * from cx.f2(6,7); --idem-------------------------------------------------------------------------adaugarea codului RETURNS RECORD este optionalacreate or replace function cx.f2_1(a int, IN b int,OUT suma int, OUT produs int, OUT impartire numeric) RETURNS RECORD as$$BEGIN-- sunt recunoscute denumirile variabilelor suma:=a+b; produs:=a*b; impartire:=a/(b*1.0);END$$language plpgsql;

-- IN (INPUT) este optional, acesta fiind tipul implicit ptr. arguumenteselect suma, produs, impartire from cx.f2_1(5,7);

---------------------------------------------------------------------------------------------- operatii elementare cu notificari in plpgsqlcreate or replace function cx.f3() returns text as$$

Page 13: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

DECLAREnr_ins int; --nr linii adaugate/inseratenr_up int; --nr. linii modificate achR text;total int;BEGIN achR:='OK';--textul este marginit de apostrofDROP TABLE IF EXISTS t4;CREATE TEMP table t4(a serial PRIMARY KEY, b int, c varchar(10));INSERT INTO t4(b,c) VALUES (11,'A1'),(21,'B2'),(31,'B3'),(41,'C3'); -- GET DIAGNOSTICS intoarce valori ale variabilelor setate in urma executiei ultimei comenzi

GET DIAGNOSTICS nr_ins = ROW_COUNT; -- RAISE NOTICE -- creaza o NOTIFICARE de la server catre client

RAISE NOTICE 'Au fost adaugate: % inregistrari',nr_ins;-- asemanator cu structura formatului de la printf,, in loc de % pune valoarea din

variabila nr_ins UPDATE t4 SET b=b+2 WHERE c LIKE 'B%';

GET DIAGNOSTICS nr_up = ROW_COUNT; RAISE NOTICE 'Au fost actualizate: % inregistrari',nr_up; SELECT sum(b) FROM c1.t4 INTO total;if total < 60 then achR:='NOK'; end if;return achR;END$$ language plpgsql;-- select nextval('c10.t1_a_seq');select c1.f3();

-- a se testa cu debuger-ul asociat!!!

Desfăşurarea lucrării:

1. Se execută exemplele corespunzătoare din secţiunea TESTE

Page 14: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.3

INSTRUCȚIUNI CICLICE ÎN plpgSQL

Scop:

Utilizarea buclelor în cadrul plpgSQL

Exemplificare:

CREATE SCHEMA c3; -- sa realizeze o functie plpgsql ce preia un vector cu 3 elemente de tip intreg si intoarce vectorul ordonat CREATE OR REPLACE FUNCTION c3.f0(IN v1 int[3],OUT v2 int[3]) AS $$ begin v2=v1; IF v2[1]>v2[2] THEN v2[1]:=v2[1]#v2[2];v2[2]:=v2[1]#v2[2];v2[1]:=v2[1]#v2[2]; END IF; IF v2[1]>v2[2] THEN v2[1]:=v2[1]#v2[2];v2[2]:=v2[1]#v2[2];v2[1]:=v2[1]#v2[2]; END IF; IF v2[2]>v2[3] THEN v2[2]:=v2[2]#v2[3];v2[3]:=v2[2]#v2[3];v2[2]:=v2[2]#v2[3]; END IF; IF v2[1]>v2[2] THEN v2[1]:=v2[1]#v2[2];v2[2]:=v2[1]#v2[2];v2[1]:=v2[1]#v2[2]; END IF; IF v2[1]>v2[2] THEN v2[1]:=v2[1]#v2[2];v2[2]:=v2[1]#v2[2];v2[1]:=v2[1]#v2[2]; END IF; IF v2[2]>v2[3] THEN v2[2]:=v2[2]#v2[3];v2[3]:=v2[2]#v2[3];v2[2]:=v2[2]#v2[3]; END IF; end $$language plpgsql;

-- SELECT c3.f0(ARRAY[4,2,1]);

----------------------------------------------------------------------------

--INSTRUCTIUNI CICLICE

LOOP--- bloc instructiuniEND LOOP;-- iesirea din bucla se realizeaza cu instructiunea EXIT (echivalent break in c++)-- revenirea la inceputul buclei (fara executarea restului de instructiuni) se realizeaza cu instr. CONTINUE (idem in C++)/*i:=0;LOOP--- instr.IF i<10 THEN CONTINUE; END IF;IF i>100 THEN EXIT; END IF;-- instructiuni in care i este intre 10 si 100END LOOP;*/------------------------------------------------------------------WHILE

Page 15: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

-- conditie (expresie evaluata la true --- adica diferita de 0)LOOP--instructiuniEND LOOP;

-----------------------------------------------FOR nume IN expresieDomeniuLOOP--instructiuniEND LOOP;-------------------------------------------FOR i IN 1..100LOOP-- -- i are valori de la 1 la 100END LOOP;-------------------------------------------------------------FOR r IN TABEL --aici expresieDomeniul este alcatuit din multimea liniilor tabeluluiLOOP-- -- r este variabila ce contine o inregistrare (linie de tabel)-- r parcurge tot tabelul (la fiecare ciclu preia o alta inregistrare din tabel)END LOOP;---------------------------------------------------------- functie contorcreate or replace function f0_1(_start_ int) RETURNS int AS$$DECLAREi int; j int;begini:=_start_; j:=0; -- i:=$1; j:=0;LOOPi:=i+1;j:=j+1;if j=100 then exit; end if;END LOOP;return i;end$$ language plpgsql;

select f0_1(17);

----------------------------------------------------------------------------------------

DROP TABLE IF EXISTS t1;create TEMP table t1(a serial PRIMARY KEY, b int, c varchar(10));INSERT INTO t1(b,c) VALUES(10,'A10'),(17,'A17'),(14,'A14'),(13,'A13'),(11,'A11');select * from t1;

--RECORD --- inregistrare--SETOF RECORDS --- un set de inregistrari === TABEL

--functie care citeste valorile din tabel : varianta 1create or replace function c3.f1() RETURNS SETOF RECORD AS

Page 16: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

$$DECLAREr record;BEGIN FOR r IN SELECT a,b,c FROM t1 -- r este variabila de parcurgere -- bucla se opreste in momentul in care aceasta variabila devine NULL, adica cand NU mai sunt -- inregistrari de parcurs -- tabelul parcurs este dat de : "SELECT a,b,c FROM sgbd2.t1"

LOOP -- inceput bucla -- echivalent in C cu {RETURN NEXT r; --- intoarce inregistrare cu inregistrare

END LOOP;--sfarsit bucla -- echivalent in C cu }END$$ language plpgsql;

select a,b,c FROM c3.f1() AS (a int, b int, c varchar(10));------------------------------------create or replace function c3.f1_1() RETURNS SETOF RECORD AS$$DECLAREr record;BEGIN FOR r IN SELECT * FROM t1

LOOP RETURN NEXT r;

END LOOP;END$$ language plpgsql;

select a,b,c FROM c3.f1_1() AS (a int, b int, c varchar(10));

--functie care afiseaza numai inregistrarile a caror valoare b -- este mmai mica decat valoarea anterioara a acestuiacreate or replace function c3.f2() RETURNS SETOF RECORD AS$$DECLAREr record;b1 int; -- memoreaza valoarea anterioara BEGIN b1:=0; FOR r IN SELECT a,b,c FROM t1

LOOP if r.b < b1 THEN b1:=r.b; RETURN NEXT r; ELSE b1:=r.b; --- "FARA return next r;" NU adauga in tabelul intors inregistrare

curenta end if; END LOOP;

END$$ language plpgsql;

select a,b,c FROM c3.f2() AS (a int, b int, c varchar(10));

--functie care intoarce inregistrarile ce contin valoarea minima, maxima a lui bcreate or replace function c3.f2_1() RETURNS SETOF RECORD AS$$

Page 17: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

DECLAREr record;b_min int; -- memoreaza valoarea minima b_max int; -- memoreaza val. max.BEGIN b_min:=1000; b_max:=-1000; FOR r IN SELECT a,b,c FROM t1

LOOP if r.b < b_min THEN b_min:=r.b; end if; if r.b >= b_max THEN b_max:=r.b; end if;END LOOP;

FOR r IN SELECT a,b,c FROM t1LOOP IF r.b = b_min or r.b = b_max THEN return next r; END IF;END LOOP;

END$$ language plpgsql;

select a,b,c FROM c3.f2_1() AS (a int, b int, c varchar(10));

---echivalent in SQL clasic: -- presupunem b distincte select t1.a,t1.b,t1.c FROM (SELECT min(b) as b_min, max(b) as b_max FROM t1) par, t1 WHERE t1.b=par.b_min OR t1.b=par.b_max;

Desfăşurarea lucrării:1. Se rulează exemplele din secţiunea exemplificări

2. Se continuă cu exemplele corespunzătoare din secţiunea TESTE

Page 18: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.4

CONSTRUCȚIA FUNCȚIILOR DINAMICE ÎN plpgSQL

Scop:

Construcția funcțiilor plpgSQL ce permit execuția de comenzi SQL ce se construiesc în

momentul execuției funcției pe baza datelor transmise prin argument și a altor date.

Exemplificare:

CREATE SCHEMA c4;

--functii dinamice --- comenzile sunt reprezentate prin siruri de caractere ce se formeaza--in momentul apelarii functiei--ex2: sa se scrie o funcie/procedura ce preia un tablou cu siruri de caractere ce reprezinta filtre--pentru o selectie din t1 si creaza un tabel t2 cu rezultatul selectieidrop table if exists t1;create temporary table t1(idx serial, val varchar(20));insert into t1(val) values ('A111'),('A222'),('A333'),('A222'),('B111');select * from t1;------------------------------------------------------------------------------functie ce preia un vector cu valori pentru un filtrucreate or replace function c4.f0(_filtru_ text[]) --preia un vector cu siruri de caractereRETURNS void AS$$ DECLARE achQ text; -- vom pune comanda SQL pentru interogare i int; BEGIN --sirul preluat se termina cu NULL achQ:='DROP TABLE IF EXISTS t2; CREATE TEMP TABLE t2 as SELECT * FROM t1 WHERE '; i:=1; LOOP IF _filtru_[i] IS NULL THEN achQ:=achQ||';'; EXECUTE achQ; RAISE NOTICE 'sirul format: % ',achQ; RETURN ; END IF; IF i>1 THEN achQ:=achQ||' OR '; END IF; achQ:=achQ||' val='||quote_literal(_filtru_[i]); i:=i+1; -- where val='...' or val=... or val=... END LOOP; END $$LANGUAGE 'plpgsql';------------------------------------------------------- cuvantul cheie EXECUTE lansaseaza pentru executie comanda formata din sirul de de caractere-- functia plpgsql quote_literal(...) adauga apostrof pentru sirul din argument, aceasta functie reduce numarul simbolurilor '-- din cadrul plpgsql, pentru scrierea intr-un sir delimitat cu apostrof a unui apostrof este necesarea

Page 19: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

adaugarea a inca unui apostrof -- in fata acestuia-----------------------select * from t1;--afiseaza doar liniile ce cuprind valorile A222 sau A333SELECT c4.f0(ARRAY['A222','A333',NULL]);select * from t2;

-----------------------------------------------------------------

Desfăşurarea lucrării:

1. Se rulează exemplul din secţiunea exemplificare

2. Se continuă cu exemplele corespunzătoare din secţiunea TESTE

Page 20: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.5

CREAREA TRIGGERE--LOR

Scop:

1. Controlul evenimentelor de editare prin implementarea triggere-lor

2. Testarea execuției triggere-lor

Exemplificări

/*

Pentru captarea evenimentelor de editare a unui tabel (insert,update,delete)

pot fi utilizate functii/proceduri speciale ce pot fi apelate automat de

SGBD in momentul aparitiei unui eveniment de editare asupra unui tabel.

De asemenea, in cazul unui eveniment de tip UPDATE aceste functii/proceduri

speciale au acces atat la vechile valori cat si la noile valori din cadrul campurilor liniei

care se modifica.

*/

/*Ex: Construim un tabel care afiseaza data si anul.

Daca mosdificam data, celelalte campuri trebuie se actualizeze automat anul

fara a apela explicit vreo functie*/

drop table if exists c5.t1;

CREATE TABLE c5.t1(datac date, an smallint,idx serial PRIMARY KEY);

-- In functia de tip triger NEW si OLD sunt predefinite si sunt de tip RECORD

-- acestea memoreaza noua, respectiv vechea valoare a inregistrarii afectate

-- CREAREA FUNCTIEI DE TIP TRIGGER

---------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION c5.f1()

RETURNS trigger AS

$$

BEGIN

SELECT EXTRACT( YEAR FROM NEW.datac) INTO NEW.an;

return NEW;

END

$$

Page 21: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LANGUAGE 'plpgsql';

---------------------------------------------------------------------------------------------------

--- ATASAREA FUNCTIEI DE TIP TRIGER UNUI TABEL

CREATE TRIGGER t1_trg

BEFORE UPDATE

ON c5.t1

FOR EACH ROW

WHEN ((old.* IS DISTINCT FROM new.*))

EXECUTE PROCEDURE c5.f1();

-------------------------------------

--verificare

INSERT INTO c5.t1(datac) VALUES('2013-03-15'); -- campul anul nu este modificat deoare functia triger

-- este setata sa raspundda doar la evenimente de tipul update

select * from c5.t1;

UPDATE c5.t1 SET datac=datac+2;-- este apelata automat functia triger

select * from c5.t1;

-----------------------------------------------------------------

/* EX:Construim un tabel care afiseaza data si anul_v -inainte de modificare si -anul_n -dupa modificare.

Daca mosdificam data, celelalte campuri trebuie se actualizeze automat anul

fara a apela explicit vreo functie*/

drop table if exists c5.t2;

CREATE TABLE c5.t2(datac date, an_v smallint, an_n smallint,idx serial PRIMARY KEY);

---------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION c5.f2()

RETURNS trigger AS

$$

BEGIN

SELECT EXTRACT( YEAR FROM OLD.datac) INTO NEW.an_v;

SELECT EXTRACT( YEAR FROM NEW.datac) INTO NEW.an_n;

return NEW;

END

$$

LANGUAGE 'plpgsql';

---------------------------------------------------------------------------------------------------

--- ATASAREA FUNCTIEI DE TIP TRIGER UNUI TABEL

Page 22: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

CREATE TRIGGER t2_trg

BEFORE UPDATE

ON c5.t2

FOR EACH ROW

WHEN ((old.* IS DISTINCT FROM new.*))

EXECUTE PROCEDURE c5.f2();

---------------------------------------------------------------------------------------------------

--verificare

INSERT INTO c5.t2(datac) VALUES('2013-03-15'); -- campul anul nu este modificat deoare functia triger

-- este setata sa raspundda doar la evenimente de tipul update

select * from c5.t2;

UPDATE c5.t2 SET datac=datac+2022;-- este apelata automat functia triger

select * from c5.t2;

-- functia poate intoarce si inregistrarea OLD adica, vechile valori

/*

construim un tabel t3 cu 2 coloane: nr. zilei din saptamana, denumirea zilei si un camp idx

In momentul introducerii nr. zilei - daca este intre 1-7 se va afisa denumirea acesteia

altfel nr. zilei ramane nemodificat

*/

---------------------------------------------------------------------------------------------------

drop table if exists c5.t3;

create table c5.t3(nz smallint,denzi varchar(15),idx serial PRIMARY KEY);

---------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION c5.f3()

RETURNS trigger AS

$$

BEGIN

IF NEW.nz=1 THEN NEW.denzi='Luni'; END IF;

IF NEW.nz=2 THEN NEW.denzi='Marti'; END IF;

IF NEW.nz=3 THEN NEW.denzi='Miercuri'; END IF;

IF NEW.nz=4 THEN NEW.denzi='Joi'; END IF;

IF NEW.nz=5 THEN NEW.denzi='Vineri'; END IF;

IF NEW.nz=6 THEN NEW.denzi='Sambata'; END IF;

IF NEW.nz=7 THEN NEW.denzi='Duminica'; END IF;

IF NEW.nz<1 OR NEW.nz>7 THEN OLD.denzi=NULL; return OLD;

Page 23: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

ELSE return NEW; END IF;

END

$$

LANGUAGE 'plpgsql';

---------------------------------------------------------------------------------------------------

CREATE TRIGGER t3_trg

BEFORE UPDATE

ON c5.t3

FOR EACH ROW

WHEN ((old.* IS DISTINCT FROM new.*))--implicit condtitia de aplicare a trigeru-lui este cand una din valorile noi difera

-- dar poate fi aplicate si coditii mai complexe asemanatoare celor sin conditia WHEN din cadrul comenzii SELECT

EXECUTE PROCEDURE c5.f3();

---------------------------------------------------------------------------------------------------

--verificare

INSERT INTO c5.t3(nz) VALUES(NULL); -- campul anul nu este modificat deoare functia triger

-- este setata sa raspundda doar la evenimente de tipul update

select * from c5.t3;

UPDATE c5.t3 SET nz=2;-- este apelata automat functia triger

select * from c5.t3;

UPDATE c5.t3 SET nz=8;-- este apelata automat functia triger

select * from c5.t3;

-------------------------------------------------------------------------

DROP TRIGGER IF EXISTS t3_trg ON c5.t3;

--IN CAZUL TRIGGER-ului pentru INSERT NU exista inregistrarea OLD, ci numai NEW

DROP TRIGGER IF EXISTS t4_trg ON c5.t3;

CREATE TRIGGER t4_trg

BEFORE INSERT

ON c5.t3

FOR EACH ROW

WHEN ((new.nz>5))

EXECUTE PROCEDURE c5.f3();

----------------------------------------------------------------------------

--verificare

DELETE FROM c5.t3;

Page 24: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

INSERT INTO c5.t3(nz) VALUES(NULL); -- nu este apelata fctia trigger new.nz<5

select * from c5.t3;

INSERT INTO c5.t3(nz) VALUES(6); -- este apelata functia triger new.nz>5 (6>5)

select * from c5.t3;

INSERT INTO c5.t3(nz) VALUES(3); -- nu este apelata functia triger new.nz<5 (3<5)

select * from c5.t3;

-- Conditia pentru executia TRIGGER-ului poate fi data atat la nivelul functiei

-- trigger cat si la nivelul crearii trigger-ui --- atasarii acestia de un tabel dat

-- o functie de tip trigger poate sa deserveasca mai multe tabele

---

---------------------------------------------------------------------------------------------------

DROP TABLE IF EXISTS c5.t5; CREATE TABLE c5.t5( idx serial PRIMARY KEY, nz smallint);

INSERT INTO c5.t5(nz) VALUES (15),(20),(25);

CREATE OR REPLACE FUNCTION c5.f_bu()

RETURNS trigger AS

$$

BEGIN

RAISE NOTICE 'Before UPDATE: NEW.nz= % OLD.nz=%',NEW.nz,OLD.nz;

RETURN NEW;

END

$$

LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION c5.f_au()

RETURNS trigger AS

$$

BEGIN

RAISE NOTICE 'After UPDATE: NEW.nz= % OLD.nz=%',NEW.nz,OLD.nz;

RETURN NEW;

END

$$

LANGUAGE 'plpgsql';

--DROP TRIGGER t_bu_trg ON c5.t5;

CREATE TRIGGER t_bu_trg

Page 25: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

BEFORE UPDATE ON c5.t5

FOR EACH ROW EXECUTE PROCEDURE c5.f_bu();

--DROP TRIGGER t_au_trg ON c5.t5;

CREATE TRIGGER t_au_trg

AFTER UPDATE ON c5.t5

FOR EACH ROW EXECUTE PROCEDURE c5.f_au();

UPDATE c5.t5 SET nz=nz+1 WHERE nz=22;

Desfăşurarea lucrării:

1. Se rulează exemplele din secţiunea exemplificări

2. Se continuă cu exemplele corespunzătoare din secţiunea TESTE

Page 26: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.6

FUNCȚII UTILIZATE PE PARTEA SERVER SIDE

Scop:

Utilizarea funcțiilor postgreSQL cu utilizare specifică în plpgSQL.

Exemplificări:

create schema c7;

-- sunt preluati doi vectori unul contine date de tip numeric (a), iar celalalt date de tip text -a (sir de

caractere) asociate fiecarui numar din a

-- ultimul element din a este NULL, vectorul are cel putin 2 elemente

-- este intors vectorul ordonat descrescator (ra) si vectorul asociat, cu elementele repozitionate rb

---------------------------------------------------------------------------------------------------------

create or replace function c7.f1(a numeric[], b text[], OUT ra numeric[],OUT rb text[])

as

$$

DECLARE

ax numeric;

bx text; k int;

ordonat boolean;

i int;

begin

--se realizeaza ordonarea descrescatoare dupa vectorul a

LOOP

i:=2;ordonat=true;

LOOP

IF a[i] IS NULL THEN exit; END IF;

IF a[i-1]<a[i]

THEN ax:=a[i];a[i]:=a[i-1];a[i-1]:=ax; bx:=b[i];b[i]:=b[i-1];b[i-1]:=bx;

ordonat:=false;

END IF;

i:=i+1;

END LOOP;

k:=1;

LOOP

Page 27: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

RAISE NOTICE 'B1-a[%]=% ',k,a[k]; k:=k+1; if a[k] IS NULL THEN exit; end if;

END LOOP;

IF ordonat THEN exit; END IF;

END LOOP;

--copiaza a[] si b[] in ra[] si rb[]

i:=1;

--RAISE NOTICE 'a[1]=%',a[1];

LOOP

IF a[i] IS NULL THEN return; END IF;

ra[i]=a[i]; rb[i]=b[i];

i:=i+1;

END LOOP;

end

$$

language 'plpgsql';

---------------------------------------------------------------------------------------------------------

select c7.f1(ARRAY[4,1,7,5,6,9, NULL],ARRAY['A4','B1','C7','D5','E6','F9',NULL]);

--------------------------------------------------------------------------------------------------------

--utilizare functiilor quote_ident() si quote_literal() in constructia interogarilor dinamice

--ex: preia un sir, il concateneaza cu sirul test si intoarce sirul complet

create or replace function c7.f2_1(b text)

returns text as

$$

declare

achQ text;

rez text;

begin

--varianta intitiala

SELECT b||' test' into rez; return rez;

-- in acest caz operatorul de concatenare a sirurilor este interpretat de SQL

RETURN rez;

end

$$

language 'plpgsql';

Page 28: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

-------------------------------------------------------------------------------------------------

--verificare

select c7.f2_1('trei');

-------------------------------------------------------------------------------------------------

create or replace function c7.f2_2(b text)

returns text as

$$

declare

achQ text;

rez text;

begin

--sau dinamic

achQ='SELECT b'||' test'; --eroare : nu poate executa "SELECT b test";

--operatorul de concatenare a sirurile este local functiei, nu apare in evaluarea ptr. SQL

--achQ='SELECT b'||'|| test';; eroare : nu stie de campul b

--achQ='SELECT '||quote_literal(b)||' ||'' test''';--corect dar complicat

--achQ='SELECT '||quote_literal(b)||'||'|| quote_literal(' test');--corect si mai simplu

EXECUTE achQ INTO rez;

RETURN rez;

end

$$

language 'plpgsql';

-------------------------------------------------------------------------------------------------

--verificare

select c7.f2_2('trei');

-------------------------------------------------------------------------------------------------

create or replace function c7.f2_3(b text)

returns text as

$$

declare

achQ text;

rez text;

begin

--sau dinamic

achQ='SELECT '||quote_literal(b)||' ||'' test''';--corect dar complicat

Page 29: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

--achQ='SELECT '||quote_literal(b)||'||'|| quote_literal(' test');--corect si mai simplu

EXECUTE achQ INTO rez;

RETURN rez;

end

$$

language 'plpgsql';

-------------------------------------------------------------------------------------------------

--verificare

select c7.f2_3('trei');

--------------------------------------------------------------------------------------------------

create or replace function c7.f2_4(b text)

returns text as

$$

declare

achQ text;

rez text;

begin

--sau dinamic

achQ='SELECT '||quote_literal(b)||'||'|| quote_literal(' test');--corect si mai simplu

EXECUTE achQ INTO rez;

RETURN rez;

end

$$

language 'plpgsql';

-------------------------------------------------------------------------------------------------

--verificare

select c7.f2_4('trei');

--------------------------------------------------------------------------------------------------

-- FUNCTII UZUALE

1.-- CONVERSIA UNUI SIR IN VECTOR cu celule de tip TEXT

---- EXEMPLU split_part --- intoarce celula specificata

SELECT split_part('12;15;23;45',';',4); --R:45

-- split_part('şirul ce urmează a fi spart','secvenţa ptr. delimitare', indexul subşirului ce va fi returnat)

/*sparge şirul transmis în subşiruri delimitate de secvenţa transnisă

şi întoarce subşirul cu indexul transmis */

Page 30: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

--2. lungimea sirului

SELECT length('ABC'); --3

--length('şirul') --

--3. extragere subsir

SELECT substr('Acesta este un sir!',8,4); --este

--substr('şir',indexul de unde va începe subşirul, nr. caractere subşir)

SELECT substr('Acesta este un sir!',8); -- preia tot restul sirului

SELECT CURRENT_DATE;

SET datestyle TO German;

SELECT CURRENT_DATE;

SET datestyle TO SQL,'MDY';

SELECT CURRENT_DATE;

SET datestyle TO Postgres;

SELECT CURRENT_DATE;

-- converteşte un şir în vector cu subşiruri

select regexp_split_to_array('hello world', E'\\s+') -- preia spaţii multiple(+) (separator)

-- toate spaţiile acţionează ca un singur spaţiu

select regexp_split_to_array('hello world', E'\\s') -- fiecare spaţiu este un separator

select regexp_split_to_array('hello world', E'\\t+') -- sepatorul este tab

select regexp_split_to_array('helloQQQworld', E'Q+') -- sepatorul este Q

--sparge o înregistrare în mai multe înregistrări

select regexp_split_to_table('hello world', E'\\s+');

select 15 as a1,regexp_split_to_table('AX1 AX2 AX3', E'\\s+') as a2;

-

Desfăşurarea lucrării:

1. Se rulează exemplele din secţiunea texemplificari

2. Se continuă cu exemplele corespunzătoare din secţiunea TESTE

Page 31: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.7

TIPURI DE DATE DEFINITE DE UTILIZATOR

Scop: Crearea de noi tipuri de date și înțelegerea necesității acestora

Exemplificări:

--TIPURI DE DATE DEFINITE DE UTILIZATOR

create schema c9;

DROP TYPE IF EXISTS adresa01 CASCADE;

-- sunt asemanatoare cu definirea structurilor in C

CREATE TYPE adresa01 AS

(

tara varchar(30),

regiune varchar(30),

oras varchar(40),

str varchar(50),

nr int,

cod_postal int,

tel varchar(20),

e_mail varchar(20)

);

DROP TABLE IF EXISTS c9.pers;

CREATE TABLE c9.pers

(idx serial PRIMARY KEY,

nume varchar(40),

adresa adresa01

);

SELECT * FROM c9.pers;

INSERT INTO c9.pers(nume,adresa) VALUES('Nume1','(''Ro'',''BV'',''BV'',''Str1'',5,0,''-'',''-'')');

--ordinea dintre parantezele rotunde este cea din cadrul structurii/tipului

SELECT * FROM c9.pers;

--adresarea elementelor se face asemanator celor din cadrul structurilor

SELECT nume, (adresa).tara as tara, (adresa).oras as orasul FROM c9.pers;

Page 32: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

INSERT INTO c9.pers(nume,adresa) VALUES

('Nume5','(''Tara1'',''Reg3'',''Oras4'',''Str1'',5,50005,''-'',''-'')'),

('Nume3','(''Tara2'',''Reg4'',''Oras3'',''Str1'',5,50006,''-'',''-'')'),

('Nume7','(''Tara2'',''Reg5'',''Oras1'',''Str1'',5,50007,''-'',''-'')'),

('Nume9','(''Tara1'',''Reg5'',''Oras9'',''Str1'',5,50009,''-'',''-'')'),

('Nume8','(''Tara2'',''Reg4'',''Oras3'',''Str1'',5,50008,''-'',''-'')'),

('Nume8','(''Tara1'',''Reg3'',''Oras2'',''Str1'',5,50003,''-'',''-'')');

SELECT * FROM c9.pers;

SELECT * FROM c9.pers ORDER BY adresa;

--IMPLICIT, fara a defini un operator de indexare ordonarea se realizeaza in ordinea campurilor din

cadrul structurii de date definite de utilizator (valabil incepand cu vers 9.x PostgreSQL)

--echivalent cu

SELECT * FROM c9.pers ORDER BY (adresa).tara,(adresa).regiune,(adresa).oras,(adresa).str,

(adresa).nr,(adresa).tel,(adresa).e_mail;

-- in cadrul cond.

SELECT * FROM c9.pers WHERE (adresa).oras ilike '%bv%';

--sau

SELECT * FROM c9.pers WHERE adresa =

'(''Tara2'',''Reg4'',''Oras3'',''Str1'',5,50006,''-'',''-'')'::adresa01;

--Fara a avea operatori definiti explicit pentru acest tip de dat sunt utilizati operatorii standard

-- ce aplica pe rand fiecarui tip de data din cadrul structurii definitite de utilizator

SELECT * FROM c9.pers WHERE adresa <

'(''Tara2'',''Reg4'',''Oras3'',''Str1'',5,50006,''-'',''-'')'::adresa01;

Desfăşurarea lucrării:

1. Se rulează exemplele din secţiunea exemplificari

2. Se continuă cu exemplele corespunzătoare din secţiunea TESTE

Page 33: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.8

SUPRAÎNCĂRCAREA OPERATORILOR

Scop:

Supaîncărcarea operatorilor SQL pentru tipurile de date definite de utilizator

Exemplificare:

-- Daca dorim ca sa existe egalitate intre doua adrese cu informatii partial introduse dar -- suficiente pentru identidicare este necesar sa rescriem/supraincarcam operatorul SQL de egalitate

--DEFINIM PROCEDURI PENTRU FIECARE OPERATOR--intoarce true daca cele doua adrese sunt din aceeasi taraCREATE OR REPLACE FUNCTION c9.op_egal_niv_tara(_a_ adresa01, _b_ adresa01) RETURNS boolean AS$BODY$BEGINIF $1.tara = $2.tara OR $1.cod_postal=$2.cod_postal THEN RETURN TRUE; END IF;RETURN FALSE;END$BODY$ LANGUAGE 'plpgsql';

--intoarce true daca cele doua adrese sunt din acelasi orasCREATE OR REPLACE FUNCTION c9.op_egal_niv_oras(_a_ adresa01, _b_ adresa01) RETURNS boolean AS$BODY$BEGINIF ($1.tara = $2.tara AND $1.oras=$2.oras) OR $1.cod_postal=$2.cod_postal THEN RETURN TRUE; END IF;RETURN FALSE;END$BODY$ LANGUAGE 'plpgsql';

--intoarce true daca cele doua adrese sunt pe aceeasi stradaCREATE OR REPLACE FUNCTION c9.op_egal_niv_str(_a_ adresa01, _b_ adresa01) RETURNS boolean AS$BODY$BEGINIF ($1.tara = $2.tara AND $1.oras=$2.oras AND $1.str=$2.str) OR $1.cod_postal=$2.cod_postal THEN RETURN TRUE; END IF;RETURN FALSE;END$BODY$ LANGUAGE 'plpgsql';

--intoarce true daca cele doua adrese sunt pe aceeasi strada

Page 34: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

CREATE OR REPLACE FUNCTION c9.op_egal_identice(_a_ adresa01, _b_ adresa01) RETURNS boolean AS$BODY$BEGINIF ($1.tara = $2.tara AND $1.oras=$2.oras AND $1.str=$2.str AND $1.nr=$2.nr) OR ($1.cod_postal=$2.cod_postal AND $1.nr=$2.nr) THEN RETURN TRUE; END IF;RETURN FALSE;END$BODY$ LANGUAGE 'plpgsql';

--operatorul mai mic -- pentru ordonareCREATE OR REPLACE FUNCTION c9.op_mai_mic(_a_ adresa01, _b_ adresa01) RETURNS boolean AS$BODY$BEGINIF $1.tara < $2.tara THEN RETURN TRUE; END IF;IF $1.tara = $2.tara THEN IF $1.oras < $2.oras THEN RETURN TRUE;END IF; END IF;IF $1.tara = $2.tara and $1.oras=$2.oras THEN IF $1.str < $2.str THEN RETURN TRUE; END IF; END IF;IF $1.tara = $2.tara and $1.oras=$2.oras and $1.str=$2.str THEN IF $1.nr < $2.nr THEN RETURN TRUE; END IF; END IF;RETURN FALSE;END$BODY$ LANGUAGE plpgsql;

--operatorul mai mare -- pentru ordonareCREATE OR REPLACE FUNCTION c9.op_mai_mare(_a_ adresa01, _b_ adresa01) RETURNS boolean AS$BODY$BEGINIF $1.tara > $2.tara THEN RETURN TRUE; END IF;IF $1.tara = $2.tara THEN IF $1.oras > $2.oras THEN RETURN TRUE;END IF; END IF;IF $1.tara = $2.tara and $1.oras=$2.oras THEN IF $1.str > $2.str THEN RETURN TRUE; END IF; END IF;IF $1.tara = $2.tara and $1.oras=$2.oras and $1.str=$2.str THEN IF $1.nr > $2.nr THEN RETURN TRUE; END IF; END IF;RETURN FALSE;END$BODY$ LANGUAGE plpgsql;

--operatorul mai mic sau egal -- pentru ordonareCREATE OR REPLACE FUNCTION c9.op_mai_mic_egal(_a_ adresa01, _b_ adresa01) RETURNS boolean AS

Page 35: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

$BODY$BEGINIF $1.tara <= $2.tara THEN RETURN TRUE; END IF;IF $1.tara = $2.tara THEN IF $1.oras <= $2.oras THEN RETURN TRUE;END IF; END IF;IF $1.tara = $2.tara and $1.oras=$2.oras THEN IF $1.str <= $2.str THEN RETURN TRUE; END IF; END IF;IF $1.tara = $2.tara and $1.oras=$2.oras and $1.str=$2.str THEN IF $1.nr <= $2.nr THEN RETURN TRUE; END IF; END IF;RETURN FALSE;END$BODY$ LANGUAGE plpgsql;

--operatorul mai mare sau egal -- pentru ordonareCREATE OR REPLACE FUNCTION c9.op_mai_mare_egal(_a_ adresa01, _b_ adresa01) RETURNS boolean AS$BODY$BEGINIF $1.tara >= $2.tara THEN RETURN TRUE; END IF;IF $1.tara = $2.tara THEN IF $1.oras >= $2.oras THEN RETURN TRUE;END IF; END IF;IF $1.tara = $2.tara and $1.oras=$2.oras THEN IF $1.str >= $2.str THEN RETURN TRUE; END IF; END IF;IF $1.tara = $2.tara and $1.oras=$2.oras and $1.str=$2.str THEN IF $1.nr >= $2.nr THEN RETURN TRUE; END IF; END IF;RETURN FALSE;END$BODY$ LANGUAGE plpgsql;

--operatorul de indexare (implicit ptr. indexare SGBD-ul utlizeaza btree-- arbore binar)CREATE OR REPLACE FUNCTION c9.op_btree(_a_ adresa01, _b_ adresa01) RETURNS integer AS$BODY$BEGIN IF $1.tara<$2.tara OR ($1.tara=$2.tara and $1.oras < $2.oras) OR ($1.tara=$2.tara and $1.oras = $2.oras and $1.str < $2.str) OR ($1.tara=$2.tara and $1.oras = $2.oras and $1.str = $2.str and $1.nr<$2.nr) THEN RETURN -1; END IF; IF $1.tara>$2.tara OR ($1.tara=$2.tara and $1.oras > $2.oras) OR ($1.tara=$2.tara and $1.oras = $2.oras and $1.str > $2.str) OR ($1.tara=$2.tara and $1.oras = $2.oras and $1.str = $2.str and $1.nr>$2.nr) THEN RETURN 1; END IF; RETURN 0;

Page 36: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

END;$BODY$ LANGUAGE plpgsql ;

--ASIGNAM SIMBOLURI DE OPERATORI SQL CU PROCEDURILE definite mai sus-- select '2'+3 --- rezultatul 5-- select '2'::varchar+3 --- rezultatul: ERROR: operator does not exist: character varying + integer-- select '2'+'3'; ERROR: operator is not unique: unknown + unknown -- incearca sa-l converteasca la un tip de data dar are mai multe variante-- select '2'::int +3; --- rezultatul 5-- select '2'::date+3; --- rezultatul: ERROR: invalid input syntax for type date: "2"-- in spatele fiecarui operator de afla o functie ce difera de alte functii si prin tipul argumentelor

-- simbol operator -- functia apelanta -- tip argument stanga --- tip argument dreaptaCREATE OPERATOR ==( PROCEDURE = c9.op_egal_niv_tara, LEFTARG = adresa01, RIGHTARG = adresa01);

CREATE OPERATOR ===( PROCEDURE = c9.op_egal_niv_oras, LEFTARG = adresa01, RIGHTARG = adresa01);

CREATE OPERATOR ====( PROCEDURE = c9.op_egal_niv_str, LEFTARG = adresa01, RIGHTARG = adresa01);

CREATE OPERATOR ===*( PROCEDURE = c9.op_egal_identice, LEFTARG = adresa01, RIGHTARG = adresa01);

CREATE OPERATOR <( PROCEDURE = c9.op_mai_mic, LEFTARG = adresa01, RIGHTARG = adresa01);

CREATE OPERATOR <=( PROCEDURE = c9.op_mai_mic_egal, LEFTARG = adresa01, RIGHTARG = adresa01);

CREATE OPERATOR >( PROCEDURE = c9.op_mai_mare, LEFTARG = adresa01, RIGHTARG = adresa01);

CREATE OPERATOR >=( PROCEDURE = c9.op_mai_mare_egal, LEFTARG = adresa01, RIGHTARG = adresa01);

--- ESTE creata clasa de operatori pentru functia de indexare

Page 37: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

CREATE OPERATOR CLASS adresa01_operatii DEFAULT FOR TYPE adresa01 USING btree AS OPERATOR 1 <, OPERATOR 2 <=, OPERATOR 3 ===*, OPERATOR 4 >=, OPERATOR 5 >, FUNCTION 1 c9.op_btree(adresa01, adresa01);

--familia de operatori--CREATE OPERATOR FAMILY adresa01_operatii USING btree; select * from c9.pers where adresa=='(''Tara2'',''Reg4'',''Oras3'',''Str1'',5,50006,''-'',''-'')'::adresa01; --ordonareselect * from c9.pers order by adresa;

--test dublplicate

DROP TABLE IF EXISTS c9.pers2;CREATE TABLE c9.pers2(nume varchar(40), adresa adresa01 PRIMARY KEY );

SELECT * FROM c9.pers2;

INSERT INTO c9.pers2(nume,adresa) VALUES('Nume1','(''Ro'',''BV'',''BV'',''Str1'',5,0,''-'',''-'')'); SELECT * FROM c9.pers2;

SELECT nume, (adresa).tara as tara, (adresa).oras as orasul FROM c9.pers2;--inregistrare cu numarul 3 se repeta de doua ori -- functia btree INSERT INTO c9.pers2(nume,adresa) VALUES('Nume5','(''Tara1'',''Reg3'',''Oras4'',''Str1'',5,50005,''-'',''-'')'),('Nume3','(''Tara2'',''Reg4'',''Oras3'',''Str1'',5,50006,''-'',''-'')'),('Nume3','(''Tara2'',''Reg4'',''Oras3'',''Str1'',5,50096,''-'',''-'')'),('Nume7','(''Tara2'',''Reg5'',''Oras1'',''Str1'',5,50007,''-'',''-'')'),('Nume9','(''Tara1'',''Reg5'',''Oras9'',''Str1'',5,50009,''-'',''-'')'),('Nume8','(''Tara2'',''Reg4'',''Oras3'',''Str1'',5,50008,''-'',''-'')'),('Nume8','(''Tara1'',''Reg3'',''Oras2'',''Str1'',5,50003,''-'',''-'')');

-- NU permite item-uri duble in PRIMARY KEY

Desfăşurarea lucrării:

1. Se rulează exemplele din secţiunea exemplificari

2. Se continuă cu exemplele corespunzătoare din secţiunea TESTE

Page 38: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

LABORATORUL NR.9

INTEROGAREA CU DATE DE TIP TABLOU

Scop:

Dezvoltarea interogarilor ce cuprind date de tip tablou.

:

--testedrop table if exists t1;create temp table t1(a serial primary key, b int[]);insert into t1(b) values (array[10]),(array[10,20]),(array[20,30,40]),(array[10,20,30]);select * from t1;

-- ANY -- orice valoare--cauta in b -- oriunde -- exista val 10select t1.* from t1 where 10=ANY(b); -- 3 inregistrari-- toate valorile din toate inregistrarile trebuie sa indeplineasca conditiaselect t1.* from t1 where 10=ALL(b); -- o inreg.

-- pentru a utiliza elemente dintr-un data de tip tablou se utilizeaza functia generate_subscripts-- expandeaza tabloul -- fiecare noua inregistrare va contine cate o celula -- cazul de mai jos tablou cu o dimensiuneselect b, s, b[s] as "celula b[s]" FROM (select b, generate_subscripts(b,1) as s from t1)par-- ex: selecteaza toate celule care au valoarea 30select b, s, b[s] as "celula b[s]" FROM (select b, generate_subscripts(b,1) as s from t1)par WHERE b[s]=30;

-- aceasta este util cand celule tabloului au alte celule corespondente-- ex:se da tabloul avand dimensiunea cod egala cu dimensiunea descriere

drop table if exists t2;create temp table t2(a serial primary key, cod varchar(30)[], descriere varchar(50)[]);insert into t2(cod,descriere) values (array['A1','A2','A3'],array['descriere A1','descriere A2','descriere A3']),(array['B1','B2','B3','B4'],array['descriere B1','descriere B2','descriere B3']),(array['C1','C2'],array['descriere C1','descriere C2']);select * from t2;

--sa se afiseze toate descrierile pentru codurile care se termina cu cifra 3

select cod[s] as cod_s, descriere[s] as descriere_s FROM (select cod,descriere, generate_subscripts(cod,1) as s from t2)par WHERE cod[s] ILIKE '%3';

Desfăşurarea lucrării:

1. Se rulează exemplele din secţiunea teoretică

2. Se continuă cu exemplele corespunzătoare din secţiunea TESTE

Page 39: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing
Page 40: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

generale, implementate la nivel de server şi un grup specific de restricţii dat de nivelul cel mai de jos al restricţiilor.

SELECT Server Side : RD - Restriction function (Fn

RD )

ANDLocal group : RD - Restriction function (F

nx

RD )

ANDApplication restrictions

UPDATE Server Side : WR - Restriction function (Fn

WR )

ANDLocal group :WR - Restriction function (F

nx

WR )

ANDApplication restrictions

DELETE

INSERT Function for evaluation of inserted rows restrictions

UPDATE Function for evaluation ofUpdated rows restrictions

Implementarea restricţiilor

Pentru operaţiile de „INSERT” şi „UPDATE” este necesară o simulare prealabilă a acestora în vederea verificării datelor afectate. Altfel ar fi posibil ca un utilizator să adauge date, prin editare – fără selecţie, pentru care nu are acces.

O aplicabilitate imediată a acestor tehnici o constituie gestiunea nomenclatoarelor pentru echipamentele electronice. De obicei un nomenclator conţine: Referinţă tehnică, Referinţă comercială, Descriere, furnizor, etc. Codificările se pot schimba frecvent fără a exista schimbari semnificative ale echipamentului, iar o căutare ulterioară nu va permite cumularea cu echipamentele similare. În acest caz sunt necesare definiri de tipul conceptelor şi termenilor, conceptul fiind dat de funcţionalitatea generală a echipamentului. De asemenea componentele acestor echipamente pot fi interschimbate ceea ce inpune definirea unor relaţii între componentele compatible ale echipamentelor.

Ex: de stabilire drepturi de acces

create schema c6;--GROUP ROLES --- grupuri de acces, un utilizator (LOGIN ROLES) poate sa apartina unui grup sau mai multor grupuri de acces-- accesul este:-- la nivel de baza de date

Page 41: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

-- la nivel de schema--la nivel de tabel--la nivel de functii--la nivel de secvente--etc--Tipurile de acces variaza in functie de tipul obiectului caruia i se adreseaza-- de ex:

--la nivel de baza de date avem acces pentru: conectare si creare,--la nivel de schema : utilizare si creare (de obiecte in cadrul

schemei)--la nivel de tabel : select, insert, update,delete, evenimente

triger, etc

--DREPTURILE de ACCES se pot da fie la nivel de grup (recomandat) fie la nivel de utilizator

--creare grupuri de acces-- DROP ROLE "CONSULTARE2";

CREATE ROLE "CONSULTARE2" NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;CREATE ROLE "EDITARE2" NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;CREATE ROLE "SUPERVIZOR2" SUPERUSER INHERIT CREATEDB CREATEROLE;

--creare utilizatori--DROP ROLE student21; CREATE ROLE student21 LOGIN PASSWORD '123' -- (student21 este <username>) NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE CONNECTION LIMIT 1; -- CONNECTION LIMIT 1 --->este permisa o singura conexiune pentru utilizator (nu pot intra simultan doi utilizatotori cu acelasi <username> )--atrbuire pentru student21 a drepturilor de acces specifice grupului "CONSULTARE2"

-- LOGIN face deosebirea intre grup de acces si utilizator (grupurile nu se pot 'loga')GRANT "CONSULTARE2" TO student21;

CREATE ROLE student22 LOGIN PASSWORD '12' CONNECTION LIMIT 1;GRANT "CONSULTARE2" TO student22;GRANT "EDITARE2" TO student22;

-- DROP ROLE student23CREATE ROLE student23 LOGIN PASSWORD '1' CONNECTION LIMIT 5;GRANT "CONSULTARE2" TO student23;GRANT "EDITARE2" TO student23;GRANT "SUPERVIZOR2" TO student23;

-- cream un tabel cu diverse drepturi de acces

CREATE TABLE c6.t1(a serial PRIMARY KEY, b varchar(10),c int);INSERT INTO c6.t1(b,c) VALUES('A1',10),('A2',20),('A3',30);select * from c6.t1;

--Pentru un GRUP--1. Se ofera acces la nivel de BAZA DE DATE

Page 42: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

GRANT CONNECT ON DATABASE "CURS_BD2012" TO "CONSULTARE2";GRANT CONNECT ON DATABASE "CURS_BD2012" TO "EDITARE2";GRANT ALL ON DATABASE "CURS_BD2012" TO "SUPERVIZOR2";

/* NOTA:PENTRU TESTAREA DREPTURILOR DE ACCES DIN pgAdmin3 se va deconecta serverul de baze de date curentsi se va reintra cu numele si parola utilizatorului ales-- se vor testa drepturi ptr.: creare obiecte(database,schema, tabel,etc)

stergere inregistrariactualizare inregistrariinserare inregistrari

pentru cele 3 campuri ale tabelului.*/

--2. Se ofera acces la nivel de schemaGRANT USAGE ON SCHEMA c6 TO "CONSULTARE2";GRANT USAGE ON SCHEMA c6 TO "EDITARE2";GRANT ALL ON SCHEMA c6 TO "SUPERVIZOR2";

--3.Se ofera acces la nivel de tabel, sequence, function, etc.

GRANT ALL ON TABLE c6.t1 TO "SUPERVIZOR2";GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE c6.t1 TO "EDITARE2";GRANT SELECT ON TABLE c6.t1 TO "CONSULTARE2";--

Desfăşurarea lucrării:

1. Se rulează exemplele din secţiunea teoretică

2. Se continuă cu exemplele corespunzătoare din secţiunea TESTE

Page 43: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

TESTE REZOLVATE

I. Să se realizeze o funcție pentru ordonarea descrescatoare a elementelor dintr-un vector și a celor dintr-un vector asociat acestuia.CREATE SCHEMA c6; create or replace function c6.f1(a int[], b text[], OUT ra int[],OUT rb text[])as$$DECLAREax numeric;bx text;ordonat boolean;i int;begin--se realizeaza ordonarea descrescatoare dupa vectorul a, elementele din b sunt asociate vectorul aLOOPordonat=true;i:=2;

LOOPIF a[i] IS NULL THEN exit; END IF; IF a[i-1]<a[i]

THEN -- ax:=a[i];a[i]:=a[i-1];a[i-1]:=ax; bx:=b[i];b[i]:=b[i-1];b[i-1]:=bx;

a[i]:=a[i]#a[i-1]; a[i-1]:=a[i]#a[i-1]; a[i]:=a[i]#a[i-1]; bx:=b[i];b[i]:=b[i-1];b[i-1]:=bx;

ordonat:=false; END IF; i:=i+1;END LOOP;IF ordonat THEN --copiaza a[] si b[] in ra[] si rb[]

i:=1;LOOPIF a[i] IS NULL or i>3 THEN return; END IF; ra[i]=a[i]; rb[i]=b[i];i:=i+1;END LOOP;

END IF;END LOOP;end$$language 'plpgsql';---------------------------------------------------------------------------------------------------------select c6.f1(ARRAY[4,1,7,NULL],ARRAY['A4','B1','C7',NULL]);

--------------------------------------------------------------------------------------------------------II. Într-o parcare sunt înregistrate:nr.înmatric, data calendarică şi timpul (oră,min etc.) --pentru intrarea/ieşire în/din parcare. --create table c6.parc( nrinm varchar(20), datac date, ora time, CONSTRAINT kx PRIMARY KEY(nrinm,datac,ora))-- 1.să se scrie o funcţie ce preia numărul de înmatriculare şi verifică dacă autovehicolul se află sau nu în parcare

Page 44: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

-- 2.să se scrie o funcţie ce preia numărul de înmatriculare şi întoarce durata ultimei --staţionări în parcare (auotovehiculul poate fi încă în parcare sau nu)/*DELETE FROM c6.parc; INSERT INTO c6.parc VALUES ('B-45-ABC','2014-02-05','12:24'),('B-47-ADC','2014-02-05','12:44'), ('B-45-ABC','2014-02-05','18:24'),('B-48-ABC','2014-02-05','12:24'), ('B-45-ABC','2014-07-05','12:24'), ('B-45-ABC','2014-07-08','1:15'); select * from c6.parc; */CREATE OR REPLACE function c6.vfparc(_nrinm_ varchar(20),OUT exista boolean)AS$$DECLARE nr int;BEGINselect COUNT(*) FROM c6.parc WHERE nrinm=_nrinm_ INTO nr;exista:=nr%2;END$$ language 'plpgsql';

SELECT c6.vfparc('B-45-ABC');

CREATE OR REPLACE function c6.durataparc(_nrinm_ varchar(20),OUT durata interval)AS$$DECLARE exista boolean;r record;t1 timestamp;t2 timestamp;i int;BEGINSELECT c6.vfparc(_nrinm_) INTO exista;IF NOT exista THENi:=2;FOR r IN SELECT * FROM c6.parc WHERE nrinm=_nrinm_ ORDER BY datac DESC,ora DESC LIMIT 2 LOOP if i=2 then t2:=r.datac+r.ora; end if; if i=1 then t1:=r.datac+r.ora; end if; i:=i-1; END LOOP;ELSE FOR r IN SELECT * FROM c6.parc WHERE nrinm=_nrinm_ ORDER BY datac DESC,ora DESC LIMIT 1 LOOP t1:=r.datac+r.ora; END LOOP; SELECT CURRENT_DATE+CURRENT_TIME INTO t2;END IF;durata:=t2-t1; END$$ language 'plpgsql';

Page 45: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

SELECT c6.durataparc('B-45-ABC');

------------------------------------var 2CREATE OR REPLACE function c6.durataparc2(_nrinm_ varchar(20),OUT durata interval)AS$$DECLARE exista boolean;r record;t1 timestamp;t2 timestamp;i int;BEGINSELECT c6.vfparc(_nrinm_) INTO exista;IF NOT exista THENi:=2;FOR r IN SELECT * FROM c6.parc WHERE nrinm=_nrinm_ ORDER BY datac DESC,ora DESC LIMIT 2 LOOP if i=2 then t2:=r.datac+r.ora; end if; if i=1 then t1:=r.datac+r.ora; end if; i:=i-1; END LOOP;ELSE SELECT datac+ora FROM c6.parc WHERE nrinm=_nrinm_ ORDER BY datac DESC,ora DESC LIMIT 1 INTO t1; SELECT CURRENT_DATE+CURRENT_TIME INTO t2;END IF;durata:=t2-t1; END$$ language 'plpgsql';

SELECT c6.durataparc2('B-45-ABC');

----------------------------------------------------------------------------- să scrie o inter. care întoarce numărul de maşini existente în parcareCREATE OR REPLACE function c6.nrparc(OUT nr int)AS$$DECLARE BEGINSELECT COUNT(*) FROM (SELECT nrinm FROM c6.parc GROUP BY nrinm HAVING count(*)%2 <> 0 ) par INTO nr;END$$ language 'plpgsql';---------------------------------------------------------------------------SELECT c6.nrparc();

--select (current_date+current_time)::timestamp -'27:59'::interval

---- EXEMPLU split_partSELECT split_part('12;15;23;45',';',4); --R:45

Page 46: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

-- split_part('şirul ce urmează a fi spart','secvenţa ptr. delimitare', indexul subşirului ce va fi returnat)/*sparge şirul transmis în subşiruri delimitate de secvenţa transnisă şi întoarce subşirul cu indexul transmis */SELECT length('ABC'); --3--length('şirul') -- SELECT substr('Acesta este un şir!',8,4); --este--substr('şir',indexul de unde va începe subşirul, nr. caractere subşir)--select pg_client_encoding()

-- converteşte un şir în vector cu subşiruriselect regexp_split_to_array('hello world', E'\\s+') -- preia spaţii multiple(+) (separator)+ -- toate spaţiile acţionează ca un singur spaţiuselect regexp_split_to_array('hello world', E'\\s') -- fiecare spaţiu este un separatorselect regexp_split_to_array('hello world', E'\\t+') -- sepatorul este tabselect regexp_split_to_array('helloQQQworld', E'Q+') -- sepatorul este Q--sparge o înregistrare în mai multe înregistrăriselect regexp_split_to_table('hello world', E'\\s+');select 15 as a1,regexp_split_to_table('AX1 AX2 AX3', E'\\s+') as a2;-- exCREATE TABLE c6.np(numeprenume varchar(100) PRIMARY KEY,varsta int);INSERT INTO c6.np VALUES ('Popescu Vasile',27),('Andrei Gh-Ion',33),('Radu Alexandru-Dan',41);CREATE TEMPORARY TABLE complet AS select split_part(numeprenume,' ',1) as nume, split_part(numeprenume,' ',2) as prenume FROM c6.np;

select current_date+current_time

--SUPRAPUNERE TIMPI ( INTERVAL ORAR )-- select (TIME '12:00',TIME '14:00') OVERLAPS (TIME '12:30', TIME '15:20')

select (TIMESTAMP '2014-01-01 12:00',TIMESTAMP '2014-01-01 14:00') OVERLAPS (TIMESTAMP '2014-01-01 12:30',TIMESTAMP '2014-01-01 15:20');

Page 47: SISTEME DE GESTIUNE a BAZELOR DE DATE …informatica.hyperion.ro/wp-content/uploads/2015/05/Indrumar-lab... · LABORATORUL NR.1 FUNCȚII SQL Scop: 1. Introducere în server-side programing

TESTE

#T1

1. Să se scrie o funcţie plpgsql ce preia doi vectori, un vector conţine date de tip numeric, iar celălalt vector conţine siruri de caractere. Funcţia întoarce doi vectori cu dimensiunea 3; un vector conţine primele 3 numere din şirul format în ordine descrescătoare, iar celălalt vector conţine şirurile corespondente (din momentul preluării) a celor trei numere.

2. Se dă un tabel având structura (a date (PK), b numeric). Să se scrie o funcţie ce preia un interval dată calendaristică şi întoarce înregistrările care au data cuprinsă în intervalul dat iar fiecare valoare a campului b va fi media aritmetică formată din valoarea lui b din inregistrarea curentă, din înregistrarea anterioară şi din înregistrarea urmatoare. Dacă valoarea anterioară sau următoare nu există se va considera valoarea zero. 3. Se dă un tabel avand structura (a serial (PK), b date, d int ). Să se scrie codul necesar implementării unui trigger pentru operaţiile de actualizare şi adăugare care modifică/constituie valoarea din campul d astfel: - seteaza valoarea din câmpul d cu numarul de zile dintre noua valoare a câmpului b şi vechea valoare a câmpului b daca noua valoare este mai mare decat vechea maloare altfel va pune -1;- dacă nu există valoare anterioară pentru câmpul b setează câmpul d cu numărul de zile dintre data curentă şi noua valoare din câmpul c.

#T2A1. Să se scrie o funcţie plpgsql ce preia doi vectori _a_[ ] şi _b_[ ] ce conţin date de tip întreg. Ultimul element din fiecare vector este NULL. Execuţia funcţiei va avea ca rezultat doi vectori. Primul vector va conţine reuniunea celor doi vectori iar al doilea vector va conţine intersecţia celor doi vectori.

A2. Se dă un tabel ce conţine evidenţa intrărilor într-o parcare şi altul care conţine evidenţa ieşirilor din parcare. Fiecare tabel va avea câmpurile: data calendaristică, ora (format time) şi numărul de îmnatriculare. Să se scrie o funcţie plpgsql ce întoarce un tabel cu autoturismele existente în parcare, acesta va conţine numărul de îmnatriculare, data calendaristică şi ora intrării în parcare.

A3. Se dă un tabel cu denumirea NOM ce conţine câmpurile cod_produs şi tva şi un alt tabel cu denumirea NIR conţine câmpurile: cod_produs, val_fara_tva, val_cu_tva. Să se scrie codul necesar implementării unui trigger pentru operaţia de actualizare a câmpului val_cu_tva. În cazul în care nu exista valoare corespondentă pentru tva câmpul val_cu_tva va fi NULL.

B1. Se dă un tabel ce contine un câmp de tip varchar(20) [ ] (vector cu date de tip varchar(20)). Să se scrie o funcţie plpgsql ce preia o dată de tip (varchar(20) ) _m_ şi întoarce TRUE dacă valoarea lui _m_ se regăseşte în tabel, altfel întoarce FALSE.

B2. Să se scrie codul necesar pentru implementarea unei structuri definite de utilizator (denumită eveniment) ce conţine: data calendaristică, ora (de tip time), utilizator, cod_eveniment (de tip varchar). Se vor alege operatorii ce vor fi supraîncărcaţi şi operaţiile asociate acestora.