Seminarul 9

1. Tipuri de date definite de utilizator

1.1. Tipuri scalare1.1.1. CreareCREATE TYPE nume_tipFROM tip_scalar

1.1.2. ModificareALTER TYPE nume_tip..........

1.1.3. tergereDROP TYPE nume_tip

1.2. Tipuri tabelare1.2.1. CreareCREATE TYPE nume_tipASTABLE (...)

1.2.2. ModificareALTER TYPE nume_tip...

1.2.3. tergereDROP TYPE nume_tip

1.2.4. Folosirea unei variabile cu tip de date tabelar definit de utilizator ntr-o procedurVariabilele care au un tip de date tabelar definit de utilizator pot fi folosite ntr-o procedur, numai dac sunt de tip READONLY.

Ex:CREATE PROC procedura@parametru nume_tip_tabelar READONLYASBEGIN....................END2. Structura repetitiv WHILE

2.1. T-SQLWHILE BEGINinstruciune1instruciune2...instruciuneNBREAK | CONTINUEinstruciuneN+1END

2.2. BREAKBREAK ntrerupe executarea structurii repetitive.

2.3. CONTINUECONTINUE trece la pasul urmtor fr a mai executa liniile de sub el.

3. Funcii

3.1. Asemnrile principale ntre funcii i proceduri

Obiecte memorate n baza de date care conin cod ce va fi executat n momentul apelrii De obicei sunt scrise n T-SQL, dar ar putea fi scrise n orice limbaj al platformei .NET

3.2. Deosebirile principale dintre funcii i proceduri

Funciile nu pot modifica permanent mediul de lucru SQL (ex.: nu putem avea INSERT sau UPDATE), pe cnd procedurile pot face acest lucru Funciile returneaz ntotdeauna o valoare, pe cnd procedurile pot s nu returneze nimic Funciile pot fi folosite n instruciunea SELECT, pe cnd procedurile sunt apelate numai cu EXEC Funciile pot avea doar parametri de tip INPUT, pe cnd procedurile pot avea i parametri de tip OUTPUT O funcie nu poate apela o procedur (excepie fac extended stored procedures), pe cnd o procedur poate apela o funcie Funciile sunt folosite, de obicei, pentru a returna date, iar procedurile pentru a modifica date.

3.3. Funcii scalareCREATE FUNCTION numele_functiei ( [ { @nume_parametru_1 [ AS ] tip_de_date [ =default ]} [ ,...n ]])RETURNS tip_de_date_returnat[ AS ]BEGIN instruciuni RETURN valoare_scalarEND

3.4. Funcii tabelare 3.4.1. Cu mai multe instruciuniCREATE FUNCTION numele_functiei ( [ { @numele_parametrului[ AS ] tip_de_date [ = default ] } [ ,...n ]])RETURNS @numele_variabilei_returnate TABLE definiia_tipului_de_date_tabelar[ AS ]BEGIN instruciuni RETURNEND

3.4.2. In-line CREATE FUNCTION numele_functiei ( [ { @numele_parametrului[ AS ] tip_de_date [ = default ] } [ ,...n ]])RETURNS TABLE [ AS ]RETURN ( instruciune )

3.5. Apelarea funciilor3.5.1. Apelarea cu SELECT3.5.1.1. Funcii scalareSELECT dbo.Nume_Funcie(parametru1, parametru2,...,parametruN, [default])

3.5.1.2. Funcii tabelareSELECT *FROM dbo.Nume_Funcie(parametru1, parametru2,...,parametruN, [default])

3.5.2. Apelarea cu EXEC a unei funcii scalareEXEC @variabil = Nume_Funcie parametru1, parametru2,..., parametruN, [default]

NOT: n cazul apelrii cu EXEC, pentru a folosi valoarea implicit a unui parametru, putem s nu scriem nimic.

4. Operatorul APPLY

4.1. Despre operator

Expresia de tip tabelar din stnga tabelului este evaluat prima, apoi, expresia din dreapta este executat pentru fiecare rnd produs de cea din stnga.

4.2. Tipurile de APPLY

Exist 2 tipuri de APPLY: CROSS i OUTER.

4.2.1. CROSS APPLYDoar acele rnduri din stnga care au corespondent n dreapta.

4.2.2. OUTER APPLYToate rndurile din stnga, chiar dac nu au corespondent n dreapta.

5. Diagrama

6. Probleme

6.1. Tipuri de date definite de utilizator1) Definii un tip de date denumit tCNP care s se bazeze pe char(13).2) Definii un tip de date denumit tVz care s conin o coloan denumit Nume de tip nvarchar(50) i o coloan denumit Prenume de tip nvarchar(50).3) Creai o procedur denumit S9P1 care s primeasc drept parametru o variabil de tip tVz i s afieze pe ecran marca vnztorilor cu acel nume i prenume.4) Declarai o variabil de tip tVz care s conin urmtoarea nregistrare (Oprescu,Monica). Apelai procedura S9P1 folosind ca parametru aceast variabil.

6.2. Instruciunea WHILE1) Creai o procedur denumit S9P2 care s dubleze preul de vnzare al produselor aferente bonului de vnzare cu numrul 5, pn ce suma preurilor este mai mare de 100. Dac media preurilor este mai mare de 20, oprii procesul de dublare.

6.3. Funcii i APPLY Pentru fiecare problem care va cere crearea unei funcii, se va folosi notaia: S9Fx, unde x=numrul problemei. De exemplu, pentru problema 1, se va folosi S9F1, pentru problema 5, se va folosi S9F5.

1) Creai o funcie care primete cantitatea i preul de vnzare al unui produs i returneaz valoarea absolut a TVA-ului de 24%.2) Apelai funcia pentru cantitatea 20 i preul de vnzare 5.3) Apelai funcia pentru toate nregistrrile tabelei ProduseVndute.

4) Creai o funcie care primete ca parametri numrul unui bon de cas, o dat i o variabil de tip bit i returneaz suma total a plilor pentru acel bon pn la data specificat, dac ultimul parametru este 0, sau dup data specificat, dac este 1. Valoarea implicit a parametrului este 0.5) Folosind funcia anterioar, afiai suma plilor aferente bonului cu numrul 11 pn la 1/1/2013.

6) Creai o funcie tabelar cu mai multe instruciuni care primete ca parametru numrul unui bon de cas i returneaz un tabel format din id-ul plii, tipul plii i suma pltit pentru toate plile aferente acelui bon de cas.7) Folosind funcia anterioar, afiai plile aferente bonului cu numrul 3.8) Creai o funcie tabelar in-line asemntoare cu cea de la punctul 6).9) Inserai n tabela BonuriDeCas urmtoarea nregistare (1000,1/1/2000,16211).10) Pentru toate nregistrrile din BonuriDeCas, afiai rezultatul funciei S9F8.11) Pentru toate nregistrrile din BonuriDeCas care au corespondent n funcia S9F8, afiai rezultatul acelei funcii.

12) Creai o funcie care s returneze valoarea total a unui bon de cas definit prin numrul su.13) Afiai rezultatul funciei anterioare pentru toate bonurile de cas.14) Creai o funcie care primete ca parametri numrul unui bon de cas i o dat i returneaz un tabel format din valoarea bonului, totalul sumelor pltite pn la data sepcificat, precum i diferena de plat. Se vor folosi funciile S9F12 i S9F4.15) Folosii funcia anterioar pentru toate nregistrrile tabelului BonuriDeCas i ziua de azi.

16) Creai o funcie care primete ca parametru un numr ntreg i returneaz 1, dac este prim i 0, altfel. 17) Aplicai funcia anterioar pentru afiarea produselor care au id-ul prim.