Baze de Date Indrumar de Laborator

Claudiu Lefter Florin Ostafi

Baze de date - Îndrumar de laborator -

1

Sintaxa de bază a instrucţiunilor SQL. Restricţionarea şi sortarea datelor

obţinute în urma interogării unei baze de date Obiective

• Cunoaşterea capabilităţilor instrucţiunilor SELECT • Executarea unor instrucţiuni SELECT de bază • Cunoaşterea diferenţelor dintre instrucţiunile SQL şi comenzile SQL*Plus • Limitarea numărului înregistrărilor returnate de o interogare • Sortarea înregistrărilor returnate de o interogare

Introducere

SQL (Structured Query Language) este unul dintre limbajele relaţionale de cereri care formează nucleul multor sisteme de gestiune a bazelor de date. El a fost dezvoltat pentru sistemul de gestiune al bazelor de date System R de cercetători din cadrul IBM Research Laboratory, San Jose-California la sfârşitul anilor ’70. SQL a fost standardizat şi a devenit limbajul de referinţă pentru bazele de date relaţionale.

SQL este un limbaj de comenzi pentru comunicarea cu serverul Oracle din orice aplicaţie. Când se introduce o instrucţiune SQL, aceasta este stocată într-o zonă de memorie numită buffer-ul SQL şi ea va rămâne în buffer până în momentul introducerii unei noi instrucţiuni.

SQL*Plus este un instrument Oracle care recunoaşte şi trimite instrucţiuni SQL la server pentru execuţie şi conţine propriul limbaj de comenzi. Caracteristicile limbajului SQL:

• poate fi folosit de o gamă largă de utilizatori, inclusiv de cei ce nu sunt programatori; • este un limbaj neprocedural; • reduce timpul necesar pentru crearea si întreţinerea sistemelor. Caracteristicile limbajului SQL*Plus:

• acceptă fişiere de instrucţiuni SQL; • furnizează un editor în mod linie pentru modificarea instrucţiunilor SQL; • controlează setările de mediu; • formatează rezultatele interogărilor sub formă de rapoarte; • accesează baze de date locale si la distanţă.

Buffer

SQL*Plus

SERVER

Instrucţiuni SQL

Comenzi SQL*Plus

Instrucţiuni SQL

Rezultatele interogării

Raport formatat

2

Următorul tabel cuprinde câteva elemente de comparaţie între SQL şi SQL*Plus. SQL SQL*Plus Este un limbaj pentru comunicarea cu serverul Oracle pentru accesarea datelor

Recunoaşte instrucţiuni SQL şi le trimite la server

Este bazat pe standardul ANSI SQL Este o interfaţă proprietate Oracle pentru executarea instrucţiunilor SQL

Manipulează date şi definiţii de tabele în baze de date Nu permite manipularea valorilor în baze de date O instrucţiune se stochează in bufferul SQL pe una sau mai multe linii

Este permisă o comandă pe linie şi nu este memorată în bufferul SQL

Nu are caracter de continuare Foloseşte caracterul (-) pentru continuarea unei comenzi dacă aceasta nu încape pe o linie

Foloseşte un caracter de terminare pentru executarea imediată a comenzii

Nu are nevoie de caractere de terminare. Se execută imediat

Nu pot fi abreviate Pot fi abreviate Foloseşte funcţii pentru anumite formatări Foloseşte comenzi pentru formatări

Mediul SQL*Plus oferă numeroase facilităţi, permiţând:

• executarea instrucţiunilor SQL pentru extragerea, modificarea, adăugarea şi ştergerea datelor din baza de date;

• formatarea, efectuarea de calcule, stocarea şi listarea rezultatele interogărilor sub formă de rapoarte;

• crearea de fişiere de script pentru stocarea instrucţiunilor SQL. Comenzile SQL*Plus se împart în următoarele mari categorii:

Categorie Scop Mediu Afectează comportamentul general al instrucţiunilor SQL pentru sesiunea respectivă Formatare Formatează rezultatele interogării Manipulări de fişiere Salvează, încarcă şi rulează fişiere de script Execuţie Trimite instrucţiuni SQL din bufferul SQL la serverul Oracle Editare Modifică instrucţiunea SQL aflată în buffer Interacţiune Permite crearea şi pasarea variabilelor către instrucţiuni SQL, afişarea variabilelor şi listarea

mesajelor pe ecran Diverse Diferite comenzi pentru conectarea la baza de date, manipularea mediului SQL*Plus şi afişarea

coloanelor

Modul de apel al SQL*Plus depinde de tipul sistemului de operare sau a mediului Windows utilizat. Pentru conectarea în mediul Windows NT trebuie urmaţi următorii paşi:

1. Start -> Programs -> Oracle-OraHome92 -> Application Development -> SQL Plus;

2. Se completează: username, parola şi numele bazei de date. Pentru conectarea într-un mediu de tip linie de comandă se lansează următoarea comandă:

Sqlplus [username[/password[@database]]] unde: username = numele utilizatorului din baza de date; password = parola de conectare la baza de date; @database = şirul de conectare la baza de date (numele bazei de date).

Notă: Din motive de securitate se recomandă introducerea numelui de utilizator şi a numelui bazei de date, urmând ca apoi, la promptul Password, să se introduce parola.

3

Sintaxa de bază a instrucţiunilor SQL Instrucţiunea SELECT Pentru extragerea informaţiilor din baza de date se utilizează instrucţiunea SELECT. Cu ajutorul acestei comenzi se pot realiza următoarele operaţii asupra datelor din baza de date:

• SELECŢIE (SELECTION): evaluarea rezultatului interogării va lua în calcul numai anumite linii din tabelele de date, pe baza unor criterii de selecţie.

• PROIECŢIE (PROJECTION): rezultatul interogării va conţine numai anumite coloane din tabele de date.

• JONCŢIUNE (JOIN) : utilizată pentru a combina date aflate în tabele diferite prin crearea unei legături între coloanele corespunzătoare.

Sintaxa de bază a comenzii SELECT este următoarea: SELECT [ DISTINCT ] {*, coloana [alias] , …} FROM tabel; În cea mai simplă formă instrucţiunea SELECT conţine: • o clauză SELECT care specifică coloanele ce vor fi afişate; • o clauză FROM care specifică tabelele ce conţin coloanele menţionate în clauza SELECT. Din punct de vedere sintactic: SELECT este o listă de una sau mai multe coloane; DISTINCT suprimă duplicatele;

* selectează toate coloanele; coloana numele coloanei; alias redenumeşte coloana selectată; FROM tabel specifica tabelul ce conţine coloanele din clauza SELECT.

Indicaţii privind scrierea instrucţiunilor SQL Pentru a construi instrucţiuni valide, uşor de citit şi de editat trebuie avute în vedere următoarele reguli:

• în general instrucţiunile SQL pot fi scrise cu litere mari sau mici; • instrucţiunile SQL pot fi introduse pe una sau mai multe linii; • cuvintele cheie nu pot fi abreviate sau despărţite în linii diferite; • de obicei clauzele sunt plasate pe linii separate pentru creşterea lizibilităţii textului; • este de preferat ca toate cuvintele cheie să fie introduse cu majuscule, iar toate celelalte cuvinte,

cum ar fi numele de tabele şi coloane, să fie introduse cu litere mici; • în mediul SQL*Plus, o instrucţiune SQL este introdusă la promt-ul SQL, iar următoarele linii

aparţinând instrucţiunii sunt numerotate. Acesta se numeşte buffer-ul SQL. Numai o singură instrucţiune SQL poate exista în buffer la un moment de timp.

Notă: - un cuvânt cheie se referă la o clauză SQL, cum ar fi SELECT, FROM; - o clauză reprezintă o parte a unei instrucţiuni SQL (e.g. SELECT empno, ename); - o instrucţiune este dată de o combinaţie de una sau mai multe clauze (e.g. SELECT * FROM

emp).

4

Execuţia instrucţiunilor SQL se poate face prin:

• plasarea caracterului ‘;’ la sfârşitul ultimei clauze; • plasarea caracterului ‘/’ la sfârşitul ultimei linii din buffer; • plasarea unui caracter slash ‘/’ la prompt-ul SQL; • lansarea în SQL*Plus a comenzii RUN (la promt-ul SQL). Selectarea tuturor coloanelor şi liniilor Dacă se doreşte afişarea tuturor coloanele cu date dintr-un tabel se foloseşte cuvântul cheie SELECT împreună cu un asterix (*) . În exemplul de mai jos, tabelul dept conţine trei coloane (DEPTNO, DNAME si LOC) şi patru linii, câte o linie pentru fiecare departament. SQL> SELECT *

2 FROM scott.dept;

DEPTNO DNAME LOC ------ ----------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICACO 40 OPERATIONS BOSTON

Acelaşi rezultat se poate obţine şi dacă enumerăm toate coloanele după cuvântul cheie SELECT. De exemplu, următoarea instrucţiune SQL afişează toate coloanele şi toate liniile din tabelul dept. SQL> SELECT deptno, dname, loc

2 FROM scott.dept; Selectarea unor anumite coloane În cazul în care se doreşte afişarea unor anumite coloane din tabel, se va specifica în clauza SELECT numele acelor coloane, separate prin virgulă (‘,’). Ordinea în care coloanele selectate vor fi afişate pe ecran este dată de ordinea în care au fost enumerate în clauza SELECT. De exemplu, pentru a afişa toate departamentele şi oraşele din tabelul scott.dept se va executa următoarea instrucţiune SQL: SQL> SELECT deptno, loc

2 FROM scott.dept;

DEPTNO LOC ------ ---------- 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON

Afişarea rezultatului unei interogări respectă următoarele convenţii:

- numele coloanelor de tip şir de caractere şi dată calendaristică, precum şi datele din tabel asociate coloanelor respective sunt aliniate la stânga;

- numele coloanelor de tip numeric şi datele din coloanele respective sunt aliniate la dreapta; - numele unei coloane de tip dată calendaristică sau şir de caractere poate fi trunchiat, dar capul

de coloană de tip numeric nu poate fi trunchiat; - numele coloanelor sunt afişate implicit cu litere mari; coloanele pot fi redenumite cu ajutorul

unui alias.

Expresii aritmetice În cazul în care se doreşte modificarea modului de afişare a datelor, în sensul executării unor calcule sau creării unor scenarii, se pot utiliza expresii aritmetice. Expresiile aritmetice pot conţine

5

nume de coloane, constante numerice si operatori aritmetici (+, -, *, /). Aceşti operatori pot fi utilizaţi în orice clauză SQL, exceptând clauza FROM.

În exemplul următor se doreşte afişarea numelui (ename), salariului (sal) şi a valorii sal+300 pentru fiecare angajat din tabelul scott.emp. Instrucţiunea SQL corespunzătoare este: SQL> SELECT ename, sal, sal+300

2 FROM scott.emp;

ENAME SAL SAL+300 -------- ------ ---------- KING 5000 5300 BLAKE 2850 3150 CLARK 2450 2750 JONES 2975 3275 MARTIN 1250 1550

Exemplul anterior utilizează operatorul “+” pentru a mări salariile cu 300 pentru toţi angajaţii şi afişează o nouă coloană SAL+300. De notat că SAL+300 nu este o coloană nouă în tabelul scott.emp. Implicit, denumirea noii coloane este dată de expresia aritmetică care a generat-o (în acest caz SAL+300). Notă: SQL*Plus ignoră spaţiile prezente înainte şi după operatorii aritmetici. Dacă o expresie aritmetică conţine mai mult de un operator, înmulţirea şi împărţirea sunt evaluate primele, apoi adunarea şi scăderea. Dacă operatorii folosiţi într-o expresie sunt de aceeaşi prioritate, evaluarea se va face de la stânga la dreapta. Folosirea parantezelor determină ca expresia dintre paranteze sa fie evaluată prima.

Valoarea null Dacă o linie nu are date pentru o coloană particulară, această valoare se numeşte null. Semnificaţia valori null este de dată nedisponibilă, neatribuită, necunoscută sau inaplicabilă. Valoarea null nu este aceeaşi cu valoarea 0 (pentru date de tip numeric) sau “” (pentru datele de tip şir de caractere).

Coloanele de orice tip pot conţine valoarea null, cu excepţia celor care au fost definite la creare ca fiind NOT NULL sau chei primare (PRIMARY KEY). SQL> SELECT name, job, comm

2 FROM scott.emp;

ENAME JOB COMM ------ ---------- ------ KING PRESIDENT BLAKE MANAGER … TURNER SALESMAN 0

Se observă că în coloana COMM din tabelul scott.emp doar angajatul TURNER poate avea comision, ceilalţi angajaţi nefiind îndreptăţiţi să primească comision.

Dacă valoarea unei coloane ce apare într-o expresie aritmetică este null, rezultatul este null. După cum se ştie, o încercare de împărţire la zero generează o eroare. În cazul unei împărţiri la o valoarea null, rezultatul este null (necunoscut).

Definirea alias-urilor pentru coloane

La afişarea rezultatului unei interogări, SQL* Plus foloseşte numele coloanei ca şi cap de tabel. Deoarece în multe cazuri acest cap de tabel nu este sugestiv, se poate schimba numele coloanei afişate folosind un alias de coloană. Alias-ul se specifică în clauza SELECT, imediat după numele coloanei. Dacă alias-ul conţine spaţii, caractere speciale ($ sau # - plasate la începutul alias-ului) sau dacă au importanţă literele mari (mici) se va scrie alias-ul între ghilimele (“ ”).

6

SQL> SELECT ename AS name, sal salary 2 FROM scott.emp;

NAME SALARY ---- -------- ………

SQL> SELECT ename “Name”, sal*12 “Annual Salary”

2 FROM scott.emp;

Name Annual Salary ------ -------------- ……

Primul exemplu afişează numele şi salariul lunar al tuturor angajaţilor. Cuvântul cheie opţional AS a fost folosit înainte de alias, rezultatul fiind acelaşi dacă AS ar fi lipsit din clauza SELECT. De notat că alias-urile name şi salary au fost scrise cu litere mici, iar la afişare au apărut scrise cu majuscule.

În cel de-al doilea exemplu se doreşte afişarea numelor anagajaţilor şi a salariilor anuale. Deoarece alias-ul Annual Salary implică folosirea spaţiului, alias-ul trebuie scris între ghilimele (“ ”).

Operatorul de concatenare ( || ) În cazul în care se doreşte concatenarea unor coloane, a unor expresii aritmetice sau valori constante pentru a crea expresii de tip şir de caractere se va utiliza operatorul de concatenare (||). Coloanele ce apar de o parte şi de alta a operatorului de concatenare sunt combinate pentru a forma o singură coloană la afişare. SQL> SELECT ename || job AS “Employees”

2 FROM scott.emp;

Employees -------------- KINGPRESIDENT BLAKEMANAGER CLARKMANAGER JONESMANAGER MARTINSALESMAN ………

În exemplul de mai sus, coloanele ENAME şi JOB sunt concatenate, iar coloanei rezultante i se atribuie alias-ul Employees. Cuvântul cheie AS folosit înaintea alias-ului măreşte lizibilitatea instrucţiunii SELECT.

Şiruri de caractere

Un “literal” este orice caracter, expresie sau număr inclus în lista SELECT care nu este un nume de coloană sau alias. Un literal este afişat pentru fiecare linie returnată de interogare. Valorile literale pentru datele calendaristice şi caractere trebuie incluse între (‘ ’). SQL> SELECT ename || ‘ ‘ || ‘is a’ || job

2 AS “Employee Details” 3 FROM scott.emp;

Employee Details -------------------- KING is a PRESIDENT BLAKE is a MANAGER CLARK is a MANAGER JONES is a MANAGER MARTIN is a SALESMAN ………

Exemplul de mai sus afişează numele şi ocupaţia tuturor angajaţilor, numele coloanei fiind Employee Details. Spaţiile dintre ghilimelele simple din instrucţiunea SELECT au fost introduse pentru îmbunătăţirea lizibilităţii ieşirii.

7

Eliminarea rândurilor duplicat In mod predefinit interogările afişează toate liniile, inclusiv cele duplicat. SQL> SELECT deptno

2 FROM scott.emp;

DEPTNO --------- 20 30 30 20 ......

SQL*Plus va afişa rezultatul interogării fără a elimina rândurile duplicat. Exemplul de mai sus afişeaza toate departamentele din tabelul scott.emp.

Eliminarea duplicatelor se face folosind cuvântul cheie DISTINCT în clauza SELECT. SQL> SELECT DISTINCT deptno

2 FROM scott.emp;

DEPTNO --------- 10 20 30

În cazul în care se specifică mai multe coloane după clauza DISTINCT vor fi afectate toate coloanele selectate, rezultatul reprezentând o combinaţie distinctă a acestor coloane. SQL> SELECT DISTINCT deptno, job

2 FROM scott.emp; Comenzi PL*SQL Afişarea structurii unui tabel

Se foloseşte comanda SQL*Plus DESC[RIBE] tablename, unde tablename este numele unui tabel , al unei vederi sau este un sinonim accesibil utilizatorului. SQL> DESCRIBE scott.dept

Name Null? Type --------- --------- --------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)

Tabelul de mai sus conţine informaţii privind structura tabelului scott.dept, numele coloanelor fiind enumerate în coloana Name. Coloana Null? indică dacă o coloană trebuie să conţină date. Valoarea NOT NULL semnifică faptul că valoarea null nu este permisă în acea coloană. Type reprezintă tipul de date al coloanei. Tip de date Descriere NUMBER(p,s) Valori numerice având un număr maxim de p cifre, unde s este numărul de cifre a părţii

subunitare VARCHAR2(s) Şir de caractere de lungime variabilă cu lungime maximă s DATE Date calendaristice între 1 ianuarie 4712 b.c. şi 31 decembrie 9999 a.c. CHAR(s) Şir de caractere de lungime fixă s

Comenzile de editare ale mediului SQL*Plus Câteva comenzi SQL*Plus utile în gestiunea buffer-ului SQL sunt: • A[PPEND] text - adaugă text la sfârşitul liniei curente din buffer

8

• C[HANGE] / old / new - schimbă textul old cu cel new în linia curentă • C[HANGE] / text / - şterge text din linia curentă din buffer • C[LEAR] BUFF[ER] - şterge toate liniile din bufferul SQL • DEL - şterge linia curentă din buffer • DEL n - şterge linia n din buffer • DEL m n - şterge din buffer toate liniile având numărul cuprins între m şi n • I[NPUT] - inserează un număr nedefinit de linii • I[NPUT] text - inserează o linie în buffer, conţinutul liniei fiind dat de text • L[IST] - listează toate liniile din bufferul SQL • L[IST] n - listează linia n din buffer • L[IST] m n - listează liniile de la m la n din buffer • n - linia n devine linia curentă din buffer • n text - înlocuieşte linia n din buffer cu text • 0 text - inserează în buffer o linie înaintea liniei 1

Notă: La prompt-ul SQL se poate introduce doar o singură comandă SQL*Plus, ce nu este stocată în buffer. Pentru a continua o comandă pe linia următoare se va plasa caracterul (-) la sfârşitul liniei. Comenzi SQL*Plus pentru fişiere SAVE filename [REP[LACE]APP[END]]

- salvează conţinutul buffer-ului SQL într-un fişier. Opţiunea APPEND adaugă la sfârşitul fişierului, iar opţiunea REPLACE suprascrie conţinutul fişierului;

GET filename - conţinutul fişierului filename este încărcat în buffer-ul SQL (extensia implicită pentru fişier este .sql);

START filename - rulează fişierul script filename; @ filename - are acelaşi efect ca în cazul utilizării comenzii START; ED[IT] - lansează editorul şi salvează conţinutul buffer-ului în fişierul afiedt.buf; ED[IT] filename - lansează editorul pentru editarea conţinutului unui fişier salvat; SPO[OL] [filename] OFF | OUT

- stochează rezultatul unei interogări într-un fişier. OFF închide fişierul. OUT închide fişierul şi îl trimite la imprimantă sistem;

EXIT - ieşire din SQL*Plus;

Restricţionarea şi sortarea datelor obţinute în urma interogării unei baze de date

În urma interogării unei baze de date poate apare necesitatea reducerii numărului de linii afişate sau specificării ordinii în care să fie afişate datele. Acest referat prezintă regulile SQL folosite pentru realizarea acestor acţiuni, noţiunile prezentate fiind însoţite de numeroase exemple. Limitarea înregistrărilor folosind o selecţie: EMP EMPNO ENAME JOB … DEPTNO

7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20

…

“… returnează toţi angajaţii din departamentul 10”

EMP

EMPNO ENAME JOB … DEPTNO

7839 KING PRESIDENT 10 7782 CLARK MANAGER 10 7934 MILLER CLERK 10

9

Să presupunem, conform exemplului de mai sus, că se doreşte afişarea tuturor angajaţilor din departamentul 10 (prezintă interes doar setul de linii care au valoarea 10 în coloana DEPTNO). Această metodă de restricţionare reprezintă baza clauzei WHERE în SQL. Limitarea liniilor selectate Se poate restricţiona numărul de linii returnate de o interogare folosind clauze WHERE. O clauză WHERE conţine o condiţie ce trebuie îndeplinită de fiecare linie din rezultat şi urmează imediat după clauza FROM. SELECT [DISTINCT] {*, coloana [alias], …} FROM tabel [WHERE conditie];

unde: WHERE restricţionează interogarea la liniile ce îndeplinesc condiţia conditie. conditie reprezintă condiţia ce trebuie satisfăcută de fiecare înregistrare ce

apare în rezultat; este compusă din nume de coloane, expresii, constante şi operatori de comparaţie.

Clauza WHERE poate compara valorile din coloane, valori literale, expresii aritmetice sau funcţii, fiind compusă din trei elemente: • numele coloanei • operatorul de comparaţie • nume de coloană, constantă sau listă de valori Folosirea clauzei WHERE SQL> SELECT ename, job, deptno

2 FROM scott.emp 3 WHERE job=‘CLERK’;

ENAME JOB DEPTNO ------- ------- ------- JAMES CLERK 30 SMITH CLERK 20 ADAMS CLERK 20 MILLER CLERK 10

Exemplul anterior afişează toţi angajaţii care au funcţia CLERK. Şiruri de caractere şi date calendaristice Şirurile de caractere şi datele calendaristice utilizate în clauza WHERE trebuie incluse între apostrofuri (‘ ‘). Toate căutările la nivel de caracter sunt case-sensitive (i.e. se face distincţie între litere mici şi majuscule). Datele calendaristice sunt memorate de Oracle în formatul secol, an, luna, zi, ore, minute şi secunde. Afişarea implicită a datei este DD-MON-YY.

Notă: valorile numerice nu trebuie incluse între apostrofuri. Operatori de comparaţie Operatorii de comparaţie (=, >, >=, <, <=, <>) sunt folosiţi în condiţii care compară două expresii. Utilizarea lor în clauza WHERE respectă următorul format: WHERE expresie operator valoare Exemple: WHERE hiredate=’01-JAN-95’

10

WHERE sal>=1500 WHERE ename=’SMITH’

Alţi operatori de comparaţie ce pot fi utilizaţi într-o clauză WHERE sunt prezentaţi în tabelul următor: Operator Semnificaţie BETWEEN… AND … Între două valori (inclusiv) IN (listă) Potriveşte orice valoare din listă LIKE Potriveşte un tip de caracter IS NULL Este valoare null

Operatorul BETWEEN Operatorul BETWEEN se utilizează pentru selectarea valorilor dintr-un interval. SQL> SELECT ename, sal

2 FROM scott.emp 3 WHERE sal BETWEEN 1000 AND 1500;

Limita Limita

inferioară superioară

ENAME SAL ------- ------ MARTIN 1250 TURNER 1500 WARD 1250 ADAMS 1100 MILLER 1300

Operatorul IN Operatorul IN este utilizat pentru căutare într-o listă de valori. El poate fi utilizat cu orice tip de dată.

SQL> SELECT empno, ename, sal, mgr 2 FROM scott.emp 3 WHERE mgr IN (7902, 7566, 7788);

EMPNO ENAME SAL MGR ------ ------- ------ ----- 7902 FORD 3000 7566 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788

Următorul exemplu returnează câte o linie din tabelul scott.emp pentru fiecare angajat al cărui nume este inclus în lista de nume din clauza WHERE. SQL> SELECT empno, ename, mgr, deptno

2 FROM scott.emp 3 WHERE ename IN (‘FORD’, ‘ALLEN’);

Notă: dacă în listă sunt folosite caractere sau date calendaristice, acestea trebuie incluse între apostrofuri (‘ ‘).

Operatorul LIKE

SQL> SELECT ename

2 FROM scott.emp 3 WHERE ename LIKE ‘S%’;

Nu întotdeauna se cunoaşte valoarea exactă pe baza căreia se va efectua căutarea. Instrucţiunea SELECT permite selectarea liniilor care corespund unui tipar de caractere cu ajutorul operatorului LIKE. Operaţia de potrivire după un tipar de caractere este referită drept căutare cu caractere wildcard. Pentru construirea şirurilor de căutare pot fi utilizate două simboluri:

11

Simbol Descriere % Reprezintă orice secvenţă de caractere _ (underscore) Reprezintă un singur caracter

Instrucţiunea SELECT de mai sus returnează numele angajaţilor din tabelul scott.emp al căror nume începe cu “S”. Numele care încep cu “s” nu vor fi returnate.

În anumite cazuri, operatorul LIKE poate fi utilizat în locul operatorului BETWEEN. Următorul exemplu afişează numele şi data angajării tuturor angajaţilor a căror angajare s-a făcut între ianuarie 1981 şi decembrie 1981.

SQL> SELECT ename, hiredate 2 FROM scott.emp 3 WHERE hiredate LIKE ‘%81’;

• Pot fi combinate diferite tipuri de potriviri pe caracter SQL> SELECT ename

2 FROM scott.emp 3 WHERE ename LIKE ‘_A%’;

ENAME ----- JAMES WARD

• Dacă se doreşte căutarea caracterelor ‘%’ sau ‘_’ se va folosi opţiunea ESCAPE, care precizează de fapt caracterul care va fi utilizat drept Escape.

Următoarea instrucţiune SELECT afişează numele tuturor angajaţilor al căror nume conţine secvenţa de caractere “A_S”.

SQL> SELECT ename 2 FROM scott.emp 3 WHERE ename LIKE ‘%A\_S’ ESCAPE ‘\’;

Operatorul IS NULL Operatorul IS NULL este utilizat pentru căutarea valorilor null. Deoarece valoarea null are semnificaţia unei valori indisponibile, neatribuite, necunoscute sau neaplicabile ea nu poate apare în cadrul unei operaţii de comparaţie, deoarece ar conduce la un rezultat null. De exemplu, pentru a afişa numele, funcţia şi comisionul tuturor angajaţilor care nu au dreptul la comision se va folosi următoarea instrucţiune SELECT: SQL> SELECT ename, job, comm

2 FROM scott.emp 3 WHERE comm IS NULL;

ENAME JOB COMM ------ --------- ----- KING PRESIDENT BLAKE MANAGER CLARK MANAGER

Operatori logici (AND, OR, NOT)

Un operator logic combină două componente de tip condiţie pentru a produce un singur rezultat bazat pe acestea sau inversează rezultatul unei singure condiţii. În SQL sunt disponibili trei operatori logici: AND, OR şi NOT. Operator Comentariu AND Returnează TRUE dacă ambele componente ale condiţiei sunt adevărate OR Returnează TRUE dacă una din componentele condiţiei este adevărată NOT Returnează TRUE dacă respectiva condiţie este falsă

12

Folosirea operatorului AND SQL> SELECT empno, ename, job, sal

2 FROM scott.emp 3 WHERE sal>=1100 4 AND job=’CLERK’;

EMPNO ENAME JOB SAL ----- ------- ------ ------ 7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300

În exemplul de mai sus ambele condiţii trebuie să fie adevărate pentru a fi selectată o înregistrare. De aceea, un angajat care are funcţia CLERK şi câştigă mai mult de $1100 va fi selectat.

Notă: - toate căutarile de tip caracter sunt case-sensitive. - şirurile de caracter trebuie incluse între apostrofuri (‘ ‘). Folosirea operatorului OR SQL> SELECT empno, ename, job, sal

2 FROM scott.emp 3 WHERE sal>=1100 4 OR job=’CLERK’;

EMPNO ENAME JOB SAL ------ ------ ---------- ------ 7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 ……

În exemplul de mai sus, vor fi selectate înregistrările care îndeplinesc cel puţin o condiţie: fie sal>=1100, fie job=’CLERK’.

Folosirea operatorului NOT SQL> SELECT ename, job

2 FROM scott.emp 3 WHERE job NOT IN (‘CLERK’, ‘MANAGER’, ‘ANALYST’);

ENAME JOB ------- ---------KING PRESIDENTMARTIN SALESMAN ALLEN SALESMAN TURNER SALESMAN WARD SALESMAN

În exemplul de mai sus este afişat numele şi funcţia tuturor angajaţilor a căror funcţie nu este CLERK, MANAGER sau ANALYST. Notă: operatorul NOT poate fi combinat şi cu alţi operatori SQL, cum ar fi BETWEEN, LIKE şi IS NULL. WHERE job NOT IN (‘CLERK’, ‘ANALYST’) WHERE sal NOT BETWEEN 1000 AND 1500 WHERE comm IS NOT NULL Precedenţa operatorilor

Prioritate Operator 1 – maximă Toţi operatorii de comparaţie

2 NOT 3 AND

4 – minimă OR

Notă: aceste reguli pot fi încălcate folosind paranteze.

13

Clauza ORDER BY

Liniile returnate de o interogare sunt afişate într-o ordine oarecare. Pentru sortarea liniilor se utilizează clauza ORDER BY, care, dacă este folosită, trebuie să apară ultima în instrucţiunea SELECT. Sortarea se poate face dupa o coloană, o expresie sau după un alias de coloană.

Sintaxă: SELECT expresie FROM tabel [WHERE conditie] [ORDER BY {coloana, expresie} [ASC|DESC];

unde: ORDER BY specifică ordinea în care sunt afişate liniile.

ASC ordonează liniile ascendent – implicit. DESC ordonează liniile descendent. Dacă nu este folosită clauza ORDER BY ordinea sortării este nedefinită şi Serverul Oracle poate afişa liniile în ordine diferită pentru două interogări identice.

SELECT-ul următor afişează rezultatele interogării ordonate descrescător după coloana hiredate. SQL> SELECT ename, job, deptno, hiredate

2 FROM scott.emp 3 ORDER BY hiredate DESC;

ENAME JOB DEPTNO HIREDATE ------- -------- ------- --------- ADAMS CLERK 20 12-JAN-83 SCOTT ANALYST 20 09-DEC-82 MILLER CLERK 10 23-JAN-82 JAMES CLERK 30 03-DEC-81 FORD ANALYST 20 03-DEC-81 KING PRESIDENT 10 17-NOV-81 MARTIN SALESMAN 30 28-SEP-81 …

Ordinea implicită a sortării datelor este cea ascendentă:

• valorile numerice sunt afişate începând cu cea mai mică valoare – de exemplu 1- 999. • datele sunt afişate începând cu cea mai recentă – de exemplu 01-JAN-92 înaintea lui 01-JAN-

95. • valorile tip caracter sunt afişate în ordine alfabetică – de exemplu A înaintea lui Z. • valorile null sunt afişate ultimele în cazul sortărilor ascendente si primele pentru cele

descendente.

Următoarea instrucţiune SELECT ordonează liniile după aliasul coloanei sal*12 (de tip expresie aritmetică). SQL> SELECT empno, ename, sal*12 annsal

2 FROM scott.emp 3 ORDER BY annsal;

EMPNO ENAME ANNSAL ------ ------- ------- 7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7654 MARTIN 15000 7521 WARD 15000 7934 MILLER 15600 7844 TURNER 18000 ………

În următorul exemplu liniile sunt sortate după coloanele deptno şi sal, iar liniile având aceeaşi valoare pentru deptno fiind sortate descendent după sal.

14

SQL> SELECT ename, deptno, sal 2 FROM scott.emp 3 ORDER BY deptno, sal DESC;

ENAME DEPTNO SAL ------- --------- ------ KING 10 5000 CLARK 10 2450 MILLER 10 1300 FORD 20 3000 ……

Notă: se poate face sortare şi după o coloană care nu este în lista clauzei SELECT. Probleme 1. Afişaţi structura tabelului scott.dept şi conţinutul lui. 2. Afişaţi structura tabelului scott.emp. Creaţi o interogare care să afişeze numele, funcţia, data

angajării şi numărul angajatului pentru fiecare angajat, numărul angajatului apărând pe prima poziţie. Salvaţi instrucţiunea într-un fisier p2.sql.

3. Rulaţi interogarea din fişierul p2.sql. 4. Afişaţi funcţiile (coloana job) din tabelul scott.emp, eliminând duplicatele. 5. Încărcaţi fişierul p2.sql în buffer-ul SQL. Modificaţi instrucţiunea SQL din buffer astfel încât să

redenumiţi numele coloanelor cu Emp#, Employee, Job, Hire Date şi apoi rulaţi interogarea. 6. Creaţi o interogare care să afişeze, pe o singură coloană, numele fiecărui angajat concatenat cu

funcţia sa, separate de virgulă şi spaţiu. Denumiţi coloana Employee and Title. 7. Afişaţi datele din tabelul scott.emp, concatenând toate coloanele. Separaţi coloanele cu o

virgulă şi denumiţi coloana THE_OUTPUT.

Rezultatul interogării este de forma:

THE_OUTPUT -------------------------------------------- 7369,SMITH,CLERK,7902,17-DEC-80,800,,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,307521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30

8. Afişaţi numele şi salariul angajaţilor din tabelul scott.emp care câştigă mai mult de $2850.

Salvaţi instrucţiunea SQL în fişierul p8.sql şi apoi rulaţi-l. 9. Modificaţi p8.sql astfel încât să afişaţi numele şi salariul tuturor angajaţilor ale căror salarii nu

intră în intervalul $1500 - $2850. Salvaţi instrucţiunea în fişierul p9.sql şi apoi rulaţi din nou interogarea.

10. Afişaţi numele şi numerele de departament ale angajaţilor care lucrează în departamentele 10, respectiv 30, ordonaţi alfabetic după nume.

11. Modificaţi fişierul p9.sql şi listaţi numele şi salariul angajaţilor care câştigă mai mult de $1500 şi lucrează în departamentul 10 sau 30. Redenumiţi coloanele din rezultat Angajat şi Salar Lunar. Salvaţi modificările în fişierul p11.sql şi apoi rulaţi-l.

12. Afişaţi numele şi funcţia pentru angajaţii care nu au manager. 13. Afişaţi numele, salariul şi comisionul pentru toţi angajaţii care au comision. Sortaţi datele în

ordine descendentă după salariu şi comision. 14. Afişaţi numele angajaţilor care conţin 2 caractere ‘L’ consecutive în numele lor şi îndeplinesc

următoarea condiţie: lucrează în departamentul 30 sau au manager cu marca 7782. 15. Modificaţi p11.sql şi afişaţi numele, salariul şi comisionul pentru toţi angajaţii care au

comisionul mai mare decât salariul mărit cu 10%. Salvaţi modificările în fişierul p15.sql şi apoi rulaţi-l.

16. Afişaţi numele, funcţia şi salariul angajaţilor ce au funcţia Clerk sau Analyst şi al căror salariu nu este de $1000, $3000 sau $5000.

17. Afişaţi numele, funcţia şi data angajării persoanelor angajate între 10 februarie 1981 şi 1 mai 1981. Ordonaţi înregistrările returnate de interogare crescător după data angajării.

1

Metode de selecţie a datelor din tabele multiple. Gruparea rezultatelor unei interogări

Metode de selecţie a datelor din tabele multiple Obiective După parcurgerea acestei secţiuni, studentul va avea cunoştinţele necesare:

• scrierii unei expresii SELECT pentru a accesa date din mai multe tabele folosind legături (joncţiuni) de egalitate si nonegalitate;

• vizualizării datelor care nu îndeplinesc o condiţie de joncţiune folosind condiţii de joncţiune externă;

• efectuării unei joncţiuni între un tabel şi el însuşi; EMP DEPT Există situaţii când trebuie să accesăm date din mai multe tabele. În exemplul de mai sus, rezultatul afişat conţine date din două tabele separate.

• atributul EMPNO există în tabelul EMP; • atributul DEPTNO există în ambele tabele EMP şi DEPT; • atributul LOC există în tabelul DEPT.

Pentru a obţine rezultatul dorit trebuie realizată o legătură între tabelele EMP şi DEPT.

Definirea joncţiunilor

Vom folosi o condiţie de joncţiune ori de câte ori trebuie să accesăm date din mai multe tablele din baza de date. Se poate crea o corespondenţă între liniile unui tabel şi liniile altui tabel pe baza valorilor comune existente în coloanele corespondente, care sunt de obicei chei primare şi străine.

Pentru afişarea datelor din două sau mai multe tabele aflate în relaţie se va scrie o simplă condiţie de joncţiune în clauza WHERE: SELECT tabel1.coloana1, tabel1.coloana2, tabel2.coloana3 FROM tabel1, tabel2 WHERE tabel1.coloana1 = tabel2.coloana3;

EMPNO ENAME … DEPTNO 7893 KING … 10 7698 BLAKE … 30

… … 7934 MILLER … 10

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

EMPNO DEPTNO LOC 7893 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO

… 14 rows selected

2

unde: tabel.coloana indică tabelul şi coloana de unde este extrasă data

tabel1.coloana1 = este condiţia care leagă tabel2.coloana2 cele două tabele

Observaţii: • în momentul scrierii unei expresii SELECT care conţine o condiţie de joncţiune, este indicat ca

numele coloanelor să fie precedate de numele tabelului de care aparţin; în acest fel este mărită claritatea codului SQL şi se îmbunătăţeşte accesul la baza de date.

• dacă un acelaşi nume de coloană apare în mai multe tabele, numele coloanei trebuie prefixat cu numele tabelului de care aparţine.

• pentru a realiza o legătură între n tabele este nevoie de minim n-1 condiţii de joncţiune (e.g. pentru a lega 4 tabele sunt necesare 3 joncţiuni). Această regula s-ar putea să nu se aplice dacă tabelul are o cheie primară formată din mai multe atribute şi astfel este necesară mai mult de o coloană pentru a identifica în mod unic fiecare linie.

Produsul Cartezian Atunci când o condiţie de joncţiune este invalidă sau complet omisă, rezultatul este un produs cartezian în care vor fi afişate toate combinaţiile de linii din tabelele implicate. Un produs cartezian tinde să genereze un număr mare de linii, iar rezultatul său este în general nefolositor. De aceea trebuie inclusă întotdeauna o condiţie de joncţiune validă în clauza WHERE, cu excepţia cazului când se doreşte în mod explicit combinarea tuturor liniilor din tabele implicate în relaţie.

Exemplul următor afişează numele fiecărui angajat şi numele departamentului în care lucrează din tabelele EMP şi DEPT. Deoarece nu a fost specificată clauza WHERE, toate liniile (14) din tabelul EMP sunt combinate cu toate liniile (4) din tabelul DEPT, generând astfel 56 de linii în tabelul rezultat. SQL> SELECT emp.ename, dept.dname 2 FROM emp, dept;

ENAME DNAME ------ ----------- KING ACCOUNTING BLAKE ACCOUNTING ... KING RESEARCH BLAKE RESEARCH ... 56 rows selected.

Tipuri de condiţii de joncţiune Principalele tipuri de condiţii de joncţiune sunt: 1. echi-joncţiune; 2. non-echi-joncţiune.

Pe lângă acestea mai există şi alte tipuri de condiţii de joncţiune: 3. joncţiune externă; 4. joncţiune între un tabel şi el însuşi. 5.

1. Echi-joncţiuni Pentru a determina numele departamentului unui angajat trebuie comparată valoarea din coloana DEPTNO din tabelul EMP cu valorile DEPTNO din tabelul DEPT. Legătura astfel creată între tabelele EMP şi DEPT este o echi-joncţiune - valorile din coloana DEPTNO din ambele tabele trebuie să coincidă.

3

EMP DEPT

Cheie străină Cheie primară

SQL> SELECT emp.empno, emp.ename, 2 emp.deptno, dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;

EMPNO ENAME DEPTNO DEPTNO LOC ----- ----- ------ ------ ------- 7839 KING 10 10 NEW YORK 7698 BLAKE 30 30 CHICAGO 7782 CLARK 10 10 NEW YORK 7566 JONES 20 20 DALLAS ……

14 rows selected.

În exemplul de mai sus: • Clauza SELECT specifică numele coloanelor ce vor fi afişate - numele şi numărul angajatului şi numărul departamentului, care sunt coloane în tabelul EMP; - numărul, numele şi locaţia departamentului sunt coloane în tabelul DEPT. • Clauza FROM specifică cele 2 tabele ce conţin informaţiile utile: tabelul EMP şi tabelul DEPT. • Clauza WHERE specifică modul în care se va realiza legătura între tabele.

Suplimentar condiţiei de joncţiune, clauza WHERE poate conţine şi alte criterii necesare operaţiei de selecţie a datelor. De exemplu, pentru a afişa codul angajatului KING, numele, numărul şi locaţia departamentului este nevoie de o condiţie suplimentară în clauza WHERE.

EMPNO ENAME DEPTNO LOC ------ ------ ------- -------- 7839 KING 10 NEW YORK

SQL> SELECT emp.empno, emp.ename, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno= dept.deptno 5 AND INITCAP(emp.ename) = 'King' ;

Alias-uri de tabele

Calificarea coloanelor cu ajutorul numelui tabelului poate consuma mult timp, mai ales în cazul în care numele tabelului este un şir lung. Pentru simplificarea codului SQL se pot folosi alias-uri de tabele în locul numelor acestora. SQL> SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc 2 FROM emp e, dept d 3 WHERE e.deptno=d.deptno;

Observatii: • Alias-urile de tabel pot avea lungimea maximă de 30 caractere;

EMPNO ENAME DEPTNO 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20

... 14 rows selected.

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCH DALLAS 20 RESEARCH DALLAS


4

• dacă în clauza FROM se introduce un alias pentru tabel, atunci acel alias trebuie să înlocuiască numele tabelului în toată instrucţiunea SELECT;

• alias-urile de tabel ar trebui sa fie semnificative; • alias-ul unui tabel este valid numai pentru SELECT-ul curent. Realizarea unei legături între mai multe tabele CUSTOMER ORD Uneori apare necesitatea realizării unei joncţiuni între mai multe tabele. De exemplu, pentru a afişa numele, numărul comenzii, codul articolelor din comandă, total număr articole şi totalul fiecărei comenzi pentru clientul “TKB SPORT” va trebui să realizăm o legătură între tabelele CUSTOMER, ORD şi ITEM.

NAME ORDID ITEMID ITEMTOT TOTAL --------- ----- ------ ------- ------ TKB SPORT 610 3 58 101.4 TKB SPORT 610 1 35 101.4 TKB SPORT 610 2 8.4 101.4

SQL> SELECT c.name, o.ordid, i.itemid, 2 i.itemtot, o.total 3 FROM customer c, ord o, item i 4 WHERE c.custid = o.custid 5 AND o.ordid = i.ordid 6 AND c.name = 'TKB SPORT'; 2. Non-echi-joncţiuni

EMP SALGRADE

"salariul din tabelul EMP este între salariul minim şi maxim din tabelul SALGRADE"

Relaţia dintre tabelul EMP si SALGRADE este o non-echi-joncţiune, în sensul că nici o coloana din tabelul EMP nu corespunde direct unei coloane din tabelul SALGRADE. Legătura dintre cele două tabele este următoarea: coloana SAL din EMP este cuprinsă între coloanele LOSAL şi HISAL din tabelul SALGRADE. Legătura se obţine folosind un operator, altul decât operatorul egal (‘=’).

NAME CUSTID JOCKSSPORTS 100 TKB SPORT 101 VOLLYRITE 102 JUST TENNIS 103 K+T SPORTS 105 SHAPE UP 106 WOMENS SPORTS 107 ... ... 9 rows selected.

CUSTID ORDID 100 610 101 611 102 612 103 601 105 602 106 107


ORDID ITEMID 610 3 611 1 612 1 601 1 602 1


EMPNO ENAME SAL 7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950


GRADE LOSAL HISAL 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

ITEM

5

SQL> SELECT e.name, e.sal, s.grade 2 FROM emp e, salgrade s 3 WHERE e.sal BETWEEN s.losal AND s.hisal;

ENAME SAL GRADE ------ ---- ------ JAMES 950 1 SMITH 800 1 ADAMS 1100 1 …… 14 rows selected

Exemplul de mai sus crează o non-echi-joncţiune pentru a determina gradul de salarizare al unui angajat. Salariul trebuie să fie între (between) limita inferioară (LOSAL) şi cea superioară (HISAL) a unui nivel de salarizare. 3. Joncţiune externă EMP DEPT Nici un angajat în departamentul OPERATIONS Dacă o linie (înregistrare) nu satisface condiţia de joncţiune, acea linie nu va apare în rezultatul interogării. De exemplu, în condiţia de echi-joncţiune a tabelelor EMP şi DEPT, departamentul OPERATIONS nu va apare pentru că nu există nici o persoană care lucrează în acel departament.

Liniile lipsă pot fi returnate dacă în condiţia de joncţiune se utilizează operatorul de joncţiune externă. Operatorul este semnul "+" între paranteze - (+) şi este plasat în acea parte a condiţiei de joncţiune corespunzătoare tabelului deficient în informaţii. Acest operator are ca efect crearea uneia sau a mai multor linii nule la care se poate adaugă una sau mai multe linii din tabelul ce conţine informaţiile neselectate. SELECT tabel.coloana1, tabel.coloana2, ... FROM tabel1, tabel2 WHERE tabel1.coloana1 = tabel2.coloana2(+) ;

unde:

tabel1.coloana1 = este condiţia care realizează legătura între tabele;

tabel2.coloana2(+) este simbolul pentru joncţiune externă; poate fi plasat in oricare parte a clauzei WHERE, dar nu în ambele părţi simultan. Se va plasa operatorul de joncţiune externă după numele coloanei din tabelul deficitar în informaţii.

Următorul exemplu afişează toate numerele şi numele departamentelor. Departamentul OPERATIONS, care nu are nici un angajat este de asemenea afişat. SQL> SELECT e.ename, d.deptno, d.dname 2 FROM emp e, dept d 3 WHERE e.deptno(+) = d.deptno 4 ORDER BY e.deptno ;

ENAME DEPTNO DNAME ------ ------ --------- KING 10 ACCOUNTING CLARK 10 ACCOUNTING …… 40 OPERATIONS 15 rows selected

Restricţii în cazul utilizării unei condiţii de joncţiune externă:

ENAME DEPTNO KING 10 BLAKE 30 CLARK 10 JONES 20 ...

DEPTNO DNAME 10 ACCOUNTING 30 SALES 10 ACCOUNTING 20 RESEARCH ... 40 OPERATIONS

6

• operatorul de joncţiune externă poate apare numai într-o singură parte a unei expresii - partea care nu deţine informaţia ce nu este returnată de interogare. El returnează acele linii din tabel care nu au corespondent direct în celălalt tabel.

• o condiţie ce implică operatorul de joncţiune externă nu poate utiliza operatorul IN şi nici nu poate fi legată de o altă condiţie prin operatorul OR.

4. Condiţii de joncţiune a unui tabel cu el însuşi EMP (WORKER) EMP (MANAGER) "MGR din tabelul WORKER este egal cu EMPNO din tabelul MANAGER" Unele aplicaţii impun realizarea unei joncţiuni între un tabel şi el însuşi. De exemplu, pentru a găsi numele managerului lui Blake va trebui să: - găsim înregistrarea corespunzătoare angajatului Blake în tabelul EMP, inspectând coloana ENAME; - găsim codul managerului lui Blake din coloana MGR. Codul managerului lui Blake este 7839.

- găsim numele managerului având codul EMPNO egal cu 7893 în coloana ENAME. Codul angajatului King este 7839. Deci King este managerul lui Blake.

Pe parcursul acestui proces am căutat în tabel de două ori. Prima dată pentru a-l găsi pe Blake în coloana ENAME şi pentru a citi valoarea MGR - 7839. A doua oară am căutat în coloana EMPNO valoarea 7839 şi am extras din coloana ENAME valoarea King. SQL> SELECT worker.ename || 'works for' || man.ename 2 FROM emp worker, emp man 3 WHERE worker.mgr = man.empno

WORKER.ENAME||'WORKSFOR'||MANAG --------------------------------BLAKE works for KING CLARK works for KING JONES works for KING MARTIN works for BLAKE …… 13 rows selected

Exemplul de mai sus crează o legătură între tabelul EMP şi el însuşi. Pentru a simula două tabele în clauza FROM, se folosesc două aliasuri, numite WORKER şi MAN pentru acelaşi tabel EMP. În acest exemplu, clauza WHERE conţine condiţia de joncţiune care înseamnă "pentru care codul managerului unui angajat coincide cu codul de angajat al managerului".

EMPNO ENAME MGR 7839 KING 7698 BLAKE 7839 7782 CLARK 7839 7566 JONES 7839 7654 MARTIN 7698 7499 ALLEN 7698

EMPNO ENAME 7839 KING 7839 KING 7839 KING 7698 BLAKE 7698 BLAKE

7

Gruparea rezultatelor unei interogări Obiective Prin parcurgerea acestei secţiuni studentul va dobândi cunoştinţele necesare: • identificării funcţiilor grup disponibile; • descrierii modului de utilizare a funcţiilor grup; • grupării datelor folosind clauza GROUP BY; • includerii sau excluderii câmpurilor grupate folosind clauza HAVING; Funcţii grup

Funcţiile grup operează pe mulţimi de înregistrări şi furnizează un singur rezultat pentru întregul grup. Exemplul de mai jos afişează salariul maxim din tabelul emp.

EMP

MAX(SAL) ---------

5000

DEPTNO --------

10 10 10 20 20 20 20 20 30 30 30 30 30 30

SAL --------

2450 5000 1300 800 1100 3000 3000 2975 1600 2850 1250 950 1500 1250

“salariul maxim în tabelul EMP”

În tabelul următor sunt descrise funcţiile grup ce pot fi utilizate în clauza SELECT. Parametrii impliciţi apar subliniaţi.

Funcţie Descriere AVG([DISTINCT|ALL] n) Returnează valoarea medie a lui n, ignorând valorile null; COUNT({*|[DISTINCT|ALL] expr}) Returnează numărul de rânduri selectate, unde expr evaluează

altceva decât valori null. Pentru a număra toate rândurile selectate, inclusiv rândurile duplicat şi cele cu valori null, se va folosi ‘*’;

MAX([DISTINCT|ALL]expr) Returnează valoarea maximă pentru expr, ignorând valorile null;

MIN([DISTINCT|ALL] expr) Returnează valoarea minimă pentru expr, ignorând valorile null;

STDDEV([DISTINCT|ALL] n) Calculează abaterea standard a lui n, ignorând valorile null; SUM([DISTINCT|ALL] n) Calculează suma valorilor lui n, ignorând valorile null; VARIANCE([DISTINCT|ALL] n) Calculează varianţa lui n, ignorând valorile null;

8

Folosirea funcţiilor grup

SELECT coloana, functie_grup(coloana) FROM tabel [WHERE conditie] [ORDER BY coloana];

• DISTINCT implică luarea în considerare numai a valorilor neduplicat. Argumentul ALL ia în

considerare fiecare valoare, inclusiv valorile duplicat; este opţiunea implicită. • tipurile de date admise pentru expr sunt: CHAR, VARCHAR2, NUMBER sau DATE. • toate grupurile de funcţii, cu excepţia funcţiei COUNT(*) ignoră valorile null. Pentru a înlocui

o valoare null cu o altă valoare se poate utiliza funcţia NVL (e.g. funcţia NVL(COMM,0) va returna 0 dacă COMM are valoarea null şi valoarea COMM dacă aceasta e diferită de null).

1. Funcţiile AVG, SUM, MIN şi MAX Funcţiile AVG şi SUM pot fi aplicate doar coloanele ce conţin date numerice, în timp ce funcţiile MIN şi MAX operează asupra oricărui tip de date. Exemplul următor afişează media salariilor lunare, salariul maxim, respectiv minim şi suma salariilor lunare pentru toţi angajaţii cu funcţia SALESMAN.

AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) -------- -------- -------- -------- 1400 1600 1250 5600

SQL> SELECT AVG(SAL),MAX(sal), 2 MIN(sal),SUM(sal) 3 FROM emp 4 WHERE job LIKE ‘SALES’;

Pentru a afişa numele primului, respectiv ultimului angajat din lista ordonată alfabetic a tuturor angajaţilor se va executa următoarea interogare:

SQL> SELECT MIN(ename),MAX(ename) 2 FROM emp;

MIN(ENAME) MAX(ENAME) --------- ---------- ADAMS WARD

Notă : Funcţiile AVG, SUM, VARIANCE şi STDDEV pot fi utilizate doar cu tipuri de date numerice. 2. Funcţia COUNT Funcţia COUNT are două formate: - COUNT(*) şi - COUNT(expr) COUNT(*) returnează numărul de rânduri din interogare, inclusiv liniile duplicat şi cele conţinând valori null, în timp ce COUNT(expr) returnează numărul valorilor diferite de null din coloana identificată prin expr. Exemplul de mai jos afişează numărul angajaţilor din departamentul 30. SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE deptno = 30;

COUNT(*) ------- 6

9

’’ salariul mediu în tabelul scott.emp

pentru fiecare departament “

Clauza GROUP BY

EMP DEPTNO SAL ------ ------- 10 2450 10 5000 2916.6667 10 1300 20 800 DEPTNO AVG(SAL) 20 1100 ------ --------- 20 3000 2175 10 2916.6667 20 3000 20 2175 20 2975 30 1566.6667 30 1600 30 2850 30 1250 1566.6667 30 950 30 1500 30 1250

Până acum, toate funcţiile grup au tratat tabelul ca fiind un grup larg de informaţii. Pentru împărţirea tabelului în grupuri mai mici se poate utiliza clauza GROUP BY.

SELECT coloana, functie_grup(coloana) FROM tabel [WHERE conditie] [GROUP BY expresie_de_grupare] [ORDER BY coloana];

unde expresie_de_grupare specifică acele coloane ale căror valori determină criteriul de grupare a liniilor. Clauza GROUP BY determină împărţirea rândurilor din tabel în grupuri. În cazul utilizării unei funcţii grup în conjuncţie cu clauza GROUP BY se va returna rezultatul funcţiei grup pentru fiecare grup. Observaţii:

• dacă se include o funcţie grup într-o clauză SELECT, lista coloanelor individuale (coloanele care nu apar în funcţia grup) trebuie să apară în clauza GROUP BY. În caz contrar se va genera un mesaj de eroare.

• utilizarea unei clauze WHERE exclude rândurile care nu satisfac condiţia impusă înainte de a se efectua divizarea în grupuri;

• în clauza GROUP BY trebuie incluse coloane; este interzisă utilizarea unui alias de coloană în clauza GROUP BY;

• rândurile sunt sortate implicit în ordinea ascendentă a coloanelor incluse în lista GROUP BY. Este posibilă încălcarea acestei reguli folosind clauza ORDER BY.

Următorul exemplu afişează numărul fiecărui departament împreună cu media salariilor pe acel departament. Modul de evaluare al instrucţiunii SELECT conţinând o clauză GROUP BY este următorul:

- clauza SELECT specifică acele coloane ce urmează să fie afişate: - coloana deptno corespunzătoare numărului departamentului - media salariilor din grupul specificat în clauza GROUP BY

10

- clauza FROM indică tabelul ce trebuie accesat - emp. - clauza WHERE specifică rândurile ce vor fi selectate. Dacă nu există clauză WHERE vor fi luate în

considerare toate rândurile. - clauza GROUP BY specifică modul de grupare al rândurilor. Rândurile sunt grupate după

numărul departamentului, deci funcţia AVG (aplicată coloanei sal) va calcula media salariilor pentru fiecare departament.

SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno;

DEPTNO AVG(SAL) ------ --------- 10 2916.6667 20 2175 30 1566.6667

Notă: Coloana ce apare în clauza GROUP BY nu trebuie să apară obligatoriu în clauza SELECT. De exemplu, instrucţiunea SELECT de mai jos afişează media salariilor pentru fiecare departament, fără să afişeze numărul departamentului respectiv. Este permisă utilizarea unei funcţii grup în clauza ORDER BY. SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno; 4 ORDER BY AVG(sal);

DEPTNO AVG(SAL) ------- ---------- 30 1566.6667 20 2175 10 2916.6667

Gruparea după mai multe coloane

EMP

DEPTNO JOB SAL

------ -------- ------ 10 MANAGER 2450 10 PRESIDENT 5000 DEPTNO JOB SUM(SAL) 10 CLERK 1300 ------- ------ -------- 20 CLERK 800 10 CLERK 1300 20 CLERK 1100 10 MANAGER 2450 20 ANALYST 3000 10 PRESIDENT 5000 20 ANALYST 3000 20 ANALYST 6000 20 MANAGER 2975 20 CLERK 1900 30 SALESMAN 1600 20 MANAGER 2975 30 MANAGER 2850 30 CLERK 950 30 SALESMAN 1250 30 MANAGER 2850 30 CLERK 950 30 SALESMAN 5600 30 SALESMAN 1500 30 SALESMAN 1250

Unele situaţii cer afişarea unor rezultate de sinteză pentru grupurile din interiorul altor grupuri. Figura de mai sus reprezintă totalul salariilor plătite pentru fiecare funcţie, în cadrul fiecărui departament. Tabelul emp este grupat mai întâi după numărul departamentului şi apoi, în fiecare grup obţinut se realizează o grupare după numele funcţiei. De exemplu, cei doi angajaţi cu funcţia CLERK din departamentul 20 sunt grupaţi împreuna şi este returnat un singur rezultat (salariul total) pentru toţi angajaţii din acest grup.

Se pot returna rezultate sinteză pentru grupuri şi subgrupuri prin specificarea mai multor coloane în clauza GROUP BY. Ordinea implicită a rezultatelor afişate poate fi controlată prin ordinea în care apar coloanele în clauza GROUP BY.

’’ suma salariilor din tabelul scott.emp pentru fiecare funcţie, grupate după

departament’’

11

’’ salariul maxim pe departament mai mare de 2900 $ “

SQL> SELECT deptno, job,sum(sal) 2 FROM emp 3 GROUP BY deptno, job;

DEPTNO JOB AVG(SAL) ------ ------- --------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 ... 9 rows selected.

Observaţii: clauza WHERE nu poate fi utilizată pentru restricţionarea grupurilor. Dacă se doreşte acest lucru se va utiliza clauza HAVING. SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;

WHERE AVG(sal)>2000 * ERROR at line 3: ORA-00934: group function is not allowed here

Această eroarea poate fi corectată prin folosirea clauzei HAVING pentru a restricţiona grupurile ce vor fi afişate. SQL> SELECT deptno,AVG(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING AVG(sal)>2000;

DEPTNO AVG(SAL)------ --------- 10 2916.6667 20 2175

Clauza HAVING

În acelaşi mod în care clauza WHERE este utilizată pentru a restricţiona rândurile selectate, se poate utiliza clauza HAVING pentru a restricţiona grupuri. Pentru a găsi salariul maxim din fiecare departament, dar la afişare să participe doar departamentele în care salariul maxim este mai mare de $2900 trebuie efectuate următoarele operaţii: 1. se va căuta salariul maxim pentru fiecare departament, grupând înregistrările după numărul

departamentului; 2. se vor restricţiona grupurile la acele departamente cu un salariu maxim mai mare de $2900.

EMP

DEPTNO SAL ------- ------ 10 2450 10 5000 5000 10 1300 20 800 DEPTNO AVG(SAL) 20 1100 ------ -------- 20 3000 3000 10 5000 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 2850 30 1500 30 1250

Pentru a păstra în rezultat doar acele grupuri ce îndeplinesc o condiţie se va utiliza clauza HAVING.

SELECT coloana, functie_grup FROM tabel

12

[WHERE conditie ] [GROUP BY expresie_de_grupare] [HAVING conditie_grup] [ORDER BY coloana];

unde conditie_grup restricţionează grupurile de rânduri la acele grupuri ce satisfac condiţia specificată.

Serverul Oracle parcurge următorii pasi când întâlneşte o clauză HAVING : - grupează rândurile - aplică funcţia grup fiecărui grup - afişează doar grupurile care îndeplinesc criteriul din clauza HAVING.

Exemplul următor afişează numărul departamentului şi salariul maxim pentru acele departamente în care salariul maxim este mai mare de $2900. SQL> SELECT deptno,max(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)>2900;

DEPTNO MAX(SAL) ------ -------- 10 5000 20 3000

Probleme: 1. Scrieţi o interogare care să afişeze numele fiecărui angajat, precum şi numărul şi numele

departamentului în care lucrează. 2. Creaţi un listing unic pentru toate funcţiile (job) angajaţilor din departamentul SALES. 3. Scrieţi o interogare care afişează numele, numele departamentului şi locaţia departamentului

pentru toţi angajaţii ce au dreptul la comision. 4. Afişaţi numele angajatului şi numele departamentului pentru toţi angajaţii al căror nume conţine

un caracter ‘A’. 5. Scrieţi o interogare care afişează numele, funcţia, numărul departamentului şi numele

departamentului pentru toţi angajaţii care lucrează în DALLAS. 6. Afişaţi numele angajaţilor şi codurile lor, împreună cu numele managerilor şi codurile acestora.

Redenumiţi coloanele Employee, Emp#, Manager şi Mgr#. 7. Modificaţi interogarea anterioară pentru a afişa toţi angajaţii, inclusiv pe King care nu are

manager. 8. Creaţi o interogare care va afişa numele fiecărui angajat, numărul departamentului în care

lucrează şi numele tuturor angajaţilor care lucrează în acelaşi departament. Redenumiţi cât mai expresiv coloanele.

9. Afişaţi structura tabelului SALGRADE. Creaţi o interogare care va afişa numele, funcţia, numele departamentului, salariul şi gradul de salarizare pentru toţi angajaţii.

10. Creaţi o interogare care afişează numele şi data angajării pentru lucrătorii angajaţi după data de angajare a lui Blake.

11. Afişaţi toate numele angajaţilor şi datele de angajare împreună cu numele managerilor şi data lor de angajare pentru toţi cei care au fost angajaţi înaintea managerilor lor. Etichetaţi coloanele Employee, Emp, Hiredate, Manager şi Mgr Hiredate.

12. Afişaţi numărul managerilor (pe baza valorilor din coloana mgr) din tabelul emp. Etichetaţi coloana “Numar Manageri”.

13. Scrieţi o interogare care să afişeze diferenţa dintre salariul cel mai mare şi cel mai mic din tabelul emp. Etichetaţi coloana “Diferenţa”.

14. Afişaţi numărul fiecărui manager şi salariul celui mai prost plătit subaltern al său. Excludeţi angajaţii ce nu au manager. Excludeţi grupurile care au salariul minim mai mic decât 1000$. Sortaţi rezultatele în ordine descrescătoare după salar.

15. Scrieţi o interogare care să afişeze numele fiecărui departament, localitatea, numărul de angajaţi şi salariul mediu al angajaţilor din acel departament. Etichetaţi coloanele Dname, Loc, Nr Persoane şi Salariu.

Subinterogări. Rapoarte interactive Instrucţiuni pentru manipularea datelor

I. Subinterogări Obiective

Prin parcurgerea acestei secţiuni studentul va căpăta cunoştinţele necesare: • definirii unei subinterogări; • scrierii subinterogărilor single-row, multiple-row, multiple-column; • descrierii şi explicării subinterogărilor atunci când sunt returnate valori null; • scrierii unor subinterogări în clauza FROM; • scrierii unei interogări care necesită o variabilă de intrare;

Să presupunem că dorim să interogăm o bază de date pentru a afla numele angajatului cu un salariu mai mare decât salariul lui Jones.

Pentru rezolvarea acestei probleme sunt necesare două interogări: o interogare pentru a afla ce salariu are Jones şi o a doua pentru a determina cine câştigă mai mult decât această sumă. Problema poate fi rezolvată combinând aceste două interogări, integrând una din cereri în cealaltă.

O cerere inclusă sau subinterogare returnează o valoare folosită de interogarea exterioară. Folosirea unei subinterogări este echivalentă executării a două cereri secvenţiale şi folosirii rezultatului primei cereri ca valoare de căutare pentru cea de a doua cerere. SELECT lista_selectie1 FROM tabel1 WHERE expr operator (SELECT lista_selectie2 FROM tabel2);

O subinterogare reprezintă o instrucţiune SELECT care este inclusă într-o clauză aparţinând altei instrucţiuni SELECT. Se pot astfel construi instrucţiuni puternice, pornind de la instrucţiuni simple, prin utilizarea subinterogărilor. Acestea pot fi utile în cazurile în care se doreşte selectarea unor rânduri dintr-un tabel în raport cu o condiţie care depinde de datele din acelaşi tabel.

Subinterogările pot fi plasate în clauzele WHERE, HAVING şi FROM.

În sintaxa instrucţiunii SELECT prezentată mai sus operator indică un operator de comparaţie (>, =, IN, ...).

Notă: Operatorii de comparaţie se împart în două clase: • operatori ce acţionează asupra unui singur rând (single-row): >, =, >=, <, <>, <= • operatori ce acţionează asupra mai multor rânduri (multiple-row): IN, ANY, ALL.

Subinterogarea este deseori referită ca fiind o instrucţiune SELECT inclusă, un sub-SELECT sau o instrucţiune SELECT internă. În general, subinterogarea se execută prima, iar rezultatul este folosit pentru a finaliza condiţia de cerere din interogarea principală. SQL> SELECT ename 2 FROM emp 3 WHERE sal > (SELECT sal FROM emp WHERE empno=7566);

ENAME ------- KING FORD SCOTT

În exemplul anterior cererea internă determină salariul angajatului cu numărul 7566. Cererea externă preia rezultatul cererii interne şi îl foloseşte pentru a afişa toţi angajaţii care câştigă un salariu mai mare decât această sumă.

Indicaţii privind scrierea subinterogărilor:

• o subinterogare trebuie inclusă între paranteze. • o subinterogare trebuie să apară în partea dreaptă a unui operator de comparaţie. • subinterogările nu pot conţine clauza ORDER BY. Pentru o instrucţiune SELECT poate exista doar

o singură clauză ORDER BY, iar dacă această clauză este specificată, ea trebuie să fie ultima clauză din instrucţiunea SELECT principală.

• subinterogările folosesc două clase de operatori de comparare: operatori single-row şi operatori multiple-row.

Tipuri de subinterogări:

1. subinterogări pe coloană (single-row): cereri care returnează doar un rând din instrucţiunea SELECT internă;

2. subinterogări multi-rând (multiple-row): cereri care returnează mai mult de un rând din instrucţiunea SELECT internă;

3. subinterogări multi-coloană (multiple-column): cereri care returnează mai multe coloane din instrucţiunea SELECT internă..

1. Subinterogări single-row O subinterogare single-row este acea subinterogare care returnează un singur rând din

instrucţiunea SELECT internă. Acest tip de subinterogare foloseşte un operator single-row.

Exemplu: Afişaţi numele şi postul angajaţilor al căror post este acelaşi cu cel al angajatului cu numărul 7369 şi al căror salariu este mai mare decât cel al angajatului 7876.

ENAME JOB ------ ------ MILLER CLERK

SQL> SELECT ename, job 2 FROM emp 3 WHERE job = (SELECT job FROM emp WHERE empno = 7369) 5 AND sal > (SELECT sal FROM emp WHERE empno = 7876);

Interogarea de mai sus este formată din 3 blocuri de cereri: o cerere exterioară şi două cereri interne. Blocurile de cereri interne se vor executa primele, producând rezultatele cererii: CLERK, respectiv 1100. Blocul exterior de cereri este apoi procesat şi foloseşte valorile returnate de cererile interne pentru a-şi finaliza propriile condiţii de căutare. Ambele cereri interne returnează câte o singură valoare, astfel că această instrucţiune SQL este denumită subinterogare single-row.

Notă: Interogările exterioare şi cele incluse pot prelua date din tabele diferite.

O interogare principală poate afişa date utilizând valoarea returnată de o funcţie grup din subinterogare. Subinterogarea se va plasa între paranteze, după operatorul de comparaţie.

Exemplul următor afişează numele, postul şi salariul tuturor angajaţilor al căror salariu este egal cu salariul minim. Funcţia MIN returnează o singură valoare (şi anume 800), care este folosită de interogarea principală.

ENAME JOB SAL ------ ----- --- SMITH CLERK 800

SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal = (SELECT MIN(sal) FROM emp);

Subinterogări în clauza HAVING

Subinterogările pot fi folosite nu numai în clauza WHERE, dar şi în clauza HAVING. Server-ul Oracle execută subinterogarea, returnând apoi rezultatul către clauza HAVING a interogării principale.

Următoarea instrucţiune SQL are ca scop final afişarea tuturor departamentelor la nivelul cărora salariul minim are o valoare mai mare decât valoarea salariului minim din cadrul departamentului 20.

DEPTNO MIN(SAL) ------- ------- 10 1300 30 950

SQL> SELECT deptno, MIN(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 20);

Exemplu: Să se găsească funcţia având cel mai scăzut salariu mediu: SQL> SELECT job, AVG(sal) 2 FROM emp 3 GROUP BY job 4 HAVING AVG(sal) = (SELECT MIN(AVG(sal)) FROM emp GROUP BY job);

Erori ce pot apare la folosirea subinterogărilor:

- returnarea mai multor rânduri de către o subinterogare dorită a fi de tip single-row; - posibilitatea neselectării nici unui rând de către interogarea inclusă. 2. Subinterogări multiple-row

Subinterogările care returnează mai mult de un rând se numesc subinterogări multiple-row. În cazul acestui tip de subinterogări se folosesc operatori multiple-row în locul celor single-row.

Operator Semnificaţie IN Egal cu oricare din elementele listei

ANY Compară valoarea cu fiecare valoare returnată de subinterogare ALL Compară valoarea cu toate valorile returnate de subinterogare

Următorul exemplu selectează toţi angajaţii care câştigă un salariu egal cu salariul minim la

nivel de departament. SQL> SELECT ename, sal, deptno 2 FROM emp 3 WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);

Interogarea internă va fi prima executată, producând ca răspuns la cerere trei rânduri: 800, 950, 1300. Blocul cererii externe este apoi procesat şi foloseşte valorile returnate de către interogarea internă pentru a-şi finaliza propria condiţie de căutare. De fapt, interogarea principală este privită din perspectiva server-ului Oracle astfel: SQL> SELECT ename, sal, deptno 2 FROM emp 3 WHERE sal IN (800, 950, 1300);

Operatorul ANY (şi operatorul SOME, sinonim acestuia) compară o valoare cu fiecare valoare returnată de subinterogare. Exemplul de mai jos afişează angajaţii ale căror salarii sunt mai mici decât al oricărui angajat cu funcţia CLERK şi care nu au funcţia CLERK. Salariul maxim pe care îl câştigă un angajat cu funcţia CLERK este $1300. Instrucţiunea SQL afişează toţi angajaţii care nu au funcţia CLERK, dar câştigă mai puţin de $1300.

EMPNO ENAME JOB ----- ------ --------7654 MARTIN SALESMAN 7521 WARD SALESMAN

SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK') 4 AND job <> 'CLERK';

Operatorul ALL compară o valoare cu toate valorile returnate de o subinterogare. Exemplul următor afişează toţi angajaţii ale căror salarii sunt mai mari decât salariile medii la nivel de departamente. Cum cel mai mare salariu mediu al vreunui departament este $2916.6667, rezultă că interogarea va selecta acei angajaţi ale căror salarii sunt mai mari decât $2916.6667. SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal > ALL (SELECT avg(sal) FROM emp GROUP BY deptno);

EMPNO ENAME JOB ----- ------ ----- 7839 KING PRESIDENT 7566 JONES MANAGER 7902 FORD ANALYST 7788 SCOTT ANALYST

3. Subinterogări multiple-column

Sintaxa unei subinterogări pe mai multe coloane este: SELECT coloana1, coloana2, … FROM tabel WHERE (coloana1, coloana2, …) IN (SELECT coloana3, coloana4, …FROM tabel WHERE conditie);

În cazul în care în clauza WHERE se doreşte compararea a două sau mai multe coloane, condiţia corespunzătoare va trebui formulată o condiţie compusă cu ajutorul operatorilor logici. Subinterogările ”multiple-column” oferă posibilitatea de a combina două condiţii duplicat de selecţie în una singură.

Să presupunem că se cere afişarea numelui, a numărului departamentului, a salariului şi a comisionului pentru angajaţii ale căror salarii şi comisioane coincid cu salariul şi comisionul oricărui angajat din departamentul 30. Această cerere va fi formulată cu ajutorul următoarei instrucţiuni SELECT: SQL> SELECT ename, deptno, sal, comm 2 FROM emp 3 WHERE (sal,NVL(comm,-1)) IN 4 (SELECT sal, NVL(comm,-1) 5 FROM emp 6 WHERE deptno=30);

ENAME DEPTNO SAL COMM -------- ------- ------ ------ JAMES 30 950 WARD 30 1250 500 MARTIN 30 1250 1400 TURNER 30 1500 0 ALLEN 30 1600 300 BLAKE 30 2850

În exemplul de mai sus a fost utilizată o subinterogare pe coloane multiple, deoarece subinterogarea returnează două coloane. Condiţia din clauza WHERE compară perechea formată din coloanele sal şi comm cu perechile returnate de subinterogare.

Comparaţiile pe coloane în subinterogările de tip “multiple-row” pot fi clasificate în comparaţii pereche sau individuale. Exemplul anterior utilizează o comparaţie pereche în clauza WHERE, deoarece fiecare linie returnată de instrucţiunea SELECT exterioară trebuie să aibă acelaşi salariu şi comision ca un angajat din departamentul 30. Dacă se doreşte utilizarea unei comparaţii individuale va trebui să folosim o clauză WHERE cu mai multe condiţii.

Exemplul următor foloseşte o comparaţie individuală pentru a afişa numele, numărul departamentului, salariul şi comisionul oricărui angajat al cărui salariu şi comision coincid cu salariul şi comisionul oricărui angajat din departamentul 30: SQL> SELECT ename, deptno, sal, comm 2 FROM emp 3 WHERE sal IN (SELECT sal FROM emp 4 WHERE deptno=30) 5 AND 6 NVL(comm, -1) IN (SELECT NVL(comm, -1) 7 FROM emp 8 WHERE deptno=30);

ENAME DEPTNO SAL COMM ------- ------- ------- ------ JAMES 30 950 BLAKE 30 2850 TURNER 30 1500 0 ALLEN 30 1600 300 WARD 30 1250 500 MARTIN 30 1250 1400

Deşi condiţiile din cele două interogări sunt diferite, rezultatele coincid. Această coincidenţă se datorează datelor conţinute de tabelul emp.

Exerciţiu: Să presupunem că salariul lui CLARK este de $1500, iar comisionul său este de $300. Explicaţi de ce rezultatele celor două interogări prezentate mai sus diferă (a doua interogare va returna înregistrarea suplimentară CLARK 10 1500 300 ).

Returnarea valorilor null în rezultatul unei subinterogări

SQL> SELECT employee.ename 2 FROM emp employee 3 WHERE employee.empno NOT IN no rows selected. 4 (SELECT manager.mgr 5 FROM emp manager);

Instrucţiunea SQL de mai sus afişează toţi angajaţii care nu au nici un subordonat. Logic, această interogare SQL ar trebui să returneze 8 linii, dar instrucţiunea SQL nu returnează nici una. Una din valorile returnate de interogarea interioară este o valoare null şi astfel, condiţia de selecţie din interogare principală nu este adevărată pentru nici o linie din tabelul emp. Acest lucru se explică prin aceea că toate condiţiile care compară o valoare null cu o altă valoare vor returna valoarea null.

Notă: Când există valori null în rezultatul unei subinterogări nu este indicată folosirea operatorului NOT IN, deoarece acesta este echivalent cu != ALL. În astfel de cazuri se poate utiliza operatorul IN.

De exemplu, pentru afişarea angajaţilor care au subordonaţi se poate utiliza următoarea instrucţiune SQL :

SQL> SELECT employee.ename 2 FROM emp employee 3 WHERE employee.empno IN (SELECT manager.mgr FROM emp manager);

ENAME ------ KING …

6 rows selected.

Utilizarea unei subinterogări în clauza FROM Exemplul următor afişează numele angajaţilor, salariile, numărul departamentului şi media

salariilor din fiecare departament pentru toţi angajaţii care câştigă mai mult decât media salariilor din departamentul în care lucrează.

SQL> SELECT a.ename, a.sal, a.deptno, b.salavg 2 FROM emp a, (SELECT deptno, AVG(sal) salavg 3 FROM emp 4 GROUP BY deptno) b 5 WHERE a.deptno = b.deptno 6 AND a.sal > b.salavg;

ENAME SAL DEPTNO SALAVG ------- ------- ------- ---------- KING 5000 10 2916.6667 JONES 2975 20 2175 SCOTT 3000 20 2175 6 rows selected.

În exemplul anterior instrucţiunea SELECT va opera pe tabelul emp (cu aliasul a) şi pe tabelul returnat de interogarea din clauza FROM (cu aliasul b). Tabelul b va avea două coloane (deptno şi salavg) şi va conţine pentru fiecare departament, numărul său şi media salariilor. II. Rapoarte interactive

Exemplele prezentate până acum nu au prezentat nici un grad de interactivitate. Prin utilizarea SQL*Plus este posibilă crearea de rapoarte care cer utilizatorului sa furnizeze propriile valori pentru a limita domeniul datelor returnate. Pentru a crea rapoarte interactive se pot include variabile de substituţie într-un fişier de comenzi sau într-o singură instrucţiune SQL. Variabile de substituţie

În SQL*Plus se pot folosi variabile de substituţie cu ajutorul caracterului ‘&’ pentru a stoca temporar valori. Variabilele pot fi definite cu ajutorul comenzilor ACCEPT sau DEFINE. ACCEPT citeşte valoarea introdusă de utilizator şi o memorează într-o variabilă. DEFINE creează şi atribuie o valoare unei variabile.

Utilizarea variabilelor de substituţie &

În timpul execuţiei unui raport, utilizatorii doresc adeseori să limiteze în mod dinamic rezultatele returnate de o interogare. SQL*Plus oferă această flexibilitate prin intermediul variabilelor utilizator. Pentru a identifica fiecare variabilă din instrucţiunea SQL se va utiliza caracterul ‘&’. SQL> SELECT empno,ename,sal,deptno 2 FROM emp 3 WHERE empno=&employee_num;

Enter value for employee_num:7369 EMPNO ENAME SAL DEPTNO ------- ------- ------ ------- 7369 SMITH 800 20

NOTAŢIE DESCRIERE &variabila_utilizator Indică o variabilă într-o instrucţiune SQL; dacă variabila nu există,

SQL*Plus va crea variabila şi va cere utilizatorului să introducă o valoare.

Exemplu de mai sus reprezintă o instrucţiune SQL care va cere utilizatorului să introducă numărul unui angajat în momentul execuţiei.

Notă: în cazul utilizării unui singur & utilizatorul trebuie să introducă valoarea acelei variabile ori de câte ori se execută instrucţiunea, în cazul în care variabila nu există.

Comanda SET VERIFY SQL> SET VERIFY ON SQL> SELECT empno ,sal, deptno 2 FROM emp 3 WHERE empno=&employee_num;

Enter value for employee_num:7369 old 3: WHERE empno=&employee_num new 3: WHERE empno=7369 ............

În cazul în care SET VERIFY este activă (pe ON), SQL*Plus este forţat să afişeze textul unei

instrucţiuni înainte şi după ce sunt înlocuite variabilele de substituţie cu valorile corespunzătoare. În exemplul de mai sus este afişată atât valoarea veche cât şi cea nouă pentru coloana empno.

Regula conform căreia într-o clauză WHERE valorile de tip caracter şi date calendaristice trebuie încadrate de apostrofuri (‘ ‘) se aplică şi în cazul variabilelor de substituţie. Pentru a evita introducerea apostrofurilor în momentul execuţiei se va încadra variabila între apostrofuri. În următorul exemplu este prezentată o interogare pentru a afla numele angajatului, numărul departamentului şi salariul anual pe baza funcţiei introdusă la prompt de utilizator. SQL> SELECT ename,deptno,sal*12 2 FROM emp 3 WHERE job=’&job_title’;

Enter value for job_title: ANALYST ENAME DEPTNO SAL*12 ------- ------- ------- SCOTT 20 36000 FORD 20 36000

Notă: se pot folosi şi funcţiile UPPER şi LOWER în conjuncţie cu &. ( UPPER(‘&job_title’) va converti caracterele din şirul argument în majuscule; efect opus are funcţia LOWER )

Variabilele de substituţie pot fi utilizate pentru a suplimenta: - o condiţie WHERE; - o clauză ORDER BY; - numele unei coloane;

- numele unui tabel; - o instrucţiune SELECT.

Pentru exemplificare să considerăm cererea de a afişa numărul de înregistrare al angajaţilor şi valorile din coloana introdusă de la tastatură pe baza unei condiţii introdusă, de asemenea, de la tastatură. SQL> SELECT empno, &column_name 2 FROM emp 3 WHERE &condition;

Enter value for column_name: job Enter value for condition: deptno=10 EMPNO JOB ------- -------- 7839 PRESIDENT 7782 MANAGER 7934 CLERK

Notă: Dacă nu introduceţi o valoare pentru variabila de substituţie se va genera o eroare când se va executa instrucţiunea de mai sus.

Utilizarea variabilelor de substituţie &&

Se va utiliza ampersand dublu (&&) atunci când se doreşte reutilizarea valorii acelei variabile, fără a fi necesară reintroducerea de la tastatură a unei valori pentru variabilă. SQL> SET VERIFY ON SQL> SELECT empno, ename, job, 2 &&column_name 3 FROM emp 4 ORDER BY &column_name;

Enter value for column_name: deptno old 1: select empno,ename,job,&&column_name new 1: select empno,ename,job,deptno old 3: order by &column_name new 3: order by deptno EMPNO ENAME JOB DEPTNO ------ ------- ---------- ------ 7839 KING PRESIDENT 10 7782 CLARK MANAGER 10 7934 MILLER CLERK 10 ........ 14 rows selected

În exemplul de mai sus, utilizatorului i se cere să furnizeze o valoarea pentru variabila column_name o singură dată . Valoarea furnizată de utilizator (deptno) va fi utilizată atât pentru afişare cât şi pentru ordonarea datelor .

SQL*Plus memorează valoarea unei variabile cu ajutorul comenzii SQL*Plus DEFINE şi va utiliza acea valoare ori de câte ori se face referire la variabilă. Pentru a şterge o variabilă trebuie folosită comanda UNDEFINE.

Variabilele utilizator pot fi definite înainte de a executa o instrucţiune SELECT cu ajutorul comenzilor DEFINE şi ACCEPT.

Comanda Descriere DEFINE variabilă = valoare Creează o variabilă de tip CHAR şi atribuie acelei variabile o valoare. DEFINE variabilă Afişează variabila, valoarea şi tipul acesteia. DEFINE Afişează toate variabilele utilizator, împreună cu valorile asociate şi tipurile de date ACCEPT Citeşte o linie introdusă de utilizator şi o memorează într-o variabilă.

Sintaxa comenzii ACCEPT este următoarea: ACCEPT variabila [tip_date] [FORMAT format] [PROMPT text] [HIDE]

unde: variabila este denumirea variabilei. Dacă aceasta nu există, ea va fi creată de SQL*Plus. tip_date este tipul de date asociat variabilei: NUMBER, CHAR sau DATE.

CHAR are o lungime de maxim 240 octeţi. FOR[MAT] precizează modelul de format - de exemplu A10 sau 9.999

format PROMPT afişează textul text înainte ca utilizatorul să introducă valoarea text

HIDE ascunde cea ce introduce utilizatorul – de exemplu o parolă

Notă: nu se va prefixa numele variabilei cu ‘&‘ în comanda ACCEPT. SQL> ACCEPT dept PROMPT ‘Introdu nume dept: ’ SQL> SELECT * 2 FROM scott.dept 3 WHERE dname=UPPER(‘&dept’);

Introdu nume dept: Sales DEPTNO DNAME LOC ------ -------- --------- 30 SALES CHICAGO

O variabilă va rămâne definită până când : - se va utiliza comanda UNDEFINE pentru a o anula; - se va părăsi mediul SQL*Plus.

Când anulaţi o variabilă există posibilitatea de a verifica modificările efectuate executând comanda DEFINE. III. Instrucţiuni pentru manipularea datelor

Scopul acestei secţiuni este de a prezenta instrucţiunile SQL caracteristice limbajului de manipulare a datelor (LMD), instrucţiuni ce sunt utilizate pentru a opera modificări asupra instanţei bazei de date. Aceste instrucţiuni permit inserarea într-un tabel, precum şi actualizarea şi ştergerea de înregistrări într-un tabel. De asemenea, va fi prezentat şi modul în care pot fi controlate tranzacţiile cu ajutorul comenzilor COMMIT, SAVEPOINT şi ROLLBACK.

Prin parcurgerea acestei secţiuni studentul va dobândi cunoştinţele necesare pentru: - descrierea fiecărei comenzi LMD; - inserarea unei înregistrări într-un tabel; - actualizarea înregistrărilor dintr-un tabel; - ştergerea unei înregistrări dintr-un tabel; - controlul tranzacţiilor. Limbajul de manipulare a datelor

Limbajul de manipulare a datelor (LMD) reprezintă o componentă importantă a SQL. O comandă din LMD are ca obiectiv adăugarea, modificarea sau ştergerea datelor dintr-o bază de date. O colecţie de instrucţiuni LMD care formează o unitate logică de lucru se numeşte tranzacţie.

Să considerăm o bază de date din domeniul bancar. Atunci când un client al băncii doreşte să transfere bani dintr-un depozit într-un cont curent, tranzacţia ar putea consta în 3 operaţii distincte: se scade suma din depozit, se măreşte suma din contul curent, înregistrează tranzacţia în jurnalul de tranzacţii. Serverul Oracle trebuie să garanteze că toate cele 3 instrucţiuni SQL sunt executate în aşa fel încât să menţină corect balanţa celor două conturi. Dacă o instrucţiune dintr-o tranzacţie este împiedicată să se execute, celelalte instrucţiuni ale tranzacţiei în cauză trebuie anulate.

Adăugarea unei înregistrări într-un tabel

Pentru a introduce înregistrări noi într-un tabel se va utiliza instrucţiunea INSERT, al cărei format este:

INSERT INTO tabel [(coloana [,coloana…])] VALUES (valoare [,valoare…]);

unde: tabel este numele tabelului coloana este numele coloanei în care se vor introduce valori. valoare este valoarea corespunzătoare coloanei.

Notă : Instrucţiunea INSERT împreună cu clauza VALUES adaugă numai un singur rând la un tabel.

Exemplu: SQL> INSERT INTO dept(deptno, dname, loc) 2 VALUES (50, ‘DEVELOPMENT’, ‘DETROIT’);

În cazul inserării unei noi înregistrări care conţine valori pentru toate coloanele, lista coloanelor din instrucţiunea INSERT nu mai este necesară. Dacă această listă nu este precizată, valorile trebuie enumerate conform ordinii coloanelor din tabel.

Pentru a insera înregistrări ce conţin valori null se poate utiliza una din următoarele metode:

- metoda implicită constă în omiterea din lista de coloane a coloanei corespunzătoare valorii null.

SQL> INSERT INTO dept(deptno, dname) 2 VALUES (60, ‘MIS’); 1 row created.

- metoda explicită constă în specificarea fie a valorii NULL, fie a şirului vid (‘‘) în lista VALUES. Şirul vid va fi utilizat doar pentru date de tip dată calendaristică sau şir de caractere.

SQL> INSERT INTO dept 2 VALUES (70, ‘FINANCE’, NULL); 1 row created.

Înainte de introducerea unei valori null într-un tabel trebuie verificat dacă valoarea null este permisă în coloana corespunzătoare, inspectând câmpul Null? furnizat de comanda DESCRIBE.

Inserarea de valori speciale folosind funcţii SQL Pentru a introduce valori speciale în tabele se pot utiliza pseudocoloane. Exemplul următor înregistrează informaţia pentru angajatul Green în tabelul emp. Pentru a introduce data şi ora curentă în câmpul HIREDATE este folosită funcţia SYSDATE. O altă funcţie ce poate fi utilizată este funcţia USER, care furnizează numele utilizatorului curent. SQL>INSERT INTO emp(empno, ename, job, mgr,hiredate, sal, comm, deptno) 1 row created. 2 VALUES (7196, ‘GREEN’, ‘SALESMAN’, 7782, SYSDATE, 2000, NULL, 10);

Inserarea valorilor de tip dată calendaristică

Formatul folosit pentru inserarea unei valori de tip dată calendaristică este DD-MON-YY. Dacă o dată calendaristică necesită specificarea altui secol sau a altei ore, se va apela funcţia TO_DATE. Exemplul de mai jos înregistrează informaţia despre angajatul Brian în tabelul emp. Câmpul HIREDATE primeşte valoarea February 3, 1997. Dacă formatul RR este setat, secolul poate fi diferit de cel curent. SQL>INSERT INTO emp VALUES (2269, ‘BRIAN’, ‘SALESMAN’, 7782, 2 TO_DATE(‘FEB-3 , 97’, 'MON DD, YY'), 1300, NULL, 10); 1 row created. Formatul funcţiei TO_DATE este: TO_DATE(date_char, fmt) unde:

date_char este data calendaristică sub forma unui şir de caractere fmt indică formatul argumentului date_char.

Inserarea de valori folosind variabile de substituţie

Este permisă scrierea unei instrucţiuni INSERT care să permită utilizatorului să adauge valori interactiv, folosind variabilele de substituţie SQL*Plus. Exemplul următor înregistrează informaţiile pentru un departament în tabelul dept. Numărul departamentului, numele şi locaţia sunt introduse în mod interactiv de către utilizator. Pentru valori de tip dată calendaristică sau şir de caractere, ampersandul (&) şi numele variabilei sunt încadrate de apostrofuri (‘ ’). SQL>INSERT INTO dept(deptno, dname, loc) 2 VALUES (&department_id, 3 ‘&department_name’, '&location');

Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for location: ATLANTA 1 row created.

Crearea unui script pentru manipularea datelor

Instrucţiunea SQL împreună cu variabilele de substituţie pot fi salvate într-un fişier şi ori de câte ori se va executa fişierul script se va cere introducerea unor valori noi pentru variabile. Mesajele afişate la cererea introducerii valorilor pot fi modificate prin intermediul comenzii SQL*Plus ACCEPT. ACCEPT department_id PROMPT 'Introduceti numarul departamentului:' ACCEPT department_name PROMPT 'Introduceti numele departamentului:' ACCEPT location PROMPT 'Introduceti orasul:' INSERT INTO dept(deptno, dname, loc) VALUES(&department_id,'&department_name','&location');

Introduceti numarul departamentului: 90 Introduceti numele departamentului: PAYROLL Introduceti orasul: HOUSTON 1 row created.

Variabila de substitutie SQL*Plus nu trebuie precedată de & când este referită într-o comanda

ACCEPT. Pentru a continua o comanda SQL*PLUS pe linia următoare se foloseşte caracterul ‘-‘.

Copierea înregistrărilor dintr-un alt tabel Instrucţiunea INSERT poate fi utilizată pentru adăugarea unor înregistrări într-un tabel, valorile

atributelor fiind derivate dintr-un tabel existent. Pentru aceasta se foloseşte în locul clauzei VALUES o subinterogare.

SQL> INSERT INTO managers(id, name, salary, hiredate) 2 SELECT empno, ename, sal, hiredate 3 rows created. 3 FROM emp 4 WHERE job = 'MANAGER';

Sintaxa este următoarea:

INSERT INTO tabel [ coloana (, coloana) ] subinterogare;

unde: tabel este numele tabelului; coloana este numele coloanei din tabelul în care se face inserarea.

subinterogare este subinterogarea care returnează înregistrări în tabel.

Notă: Numărul şi tipul câmpurilor (coloanelor) din lista specificată în instrucţiunea INSERT trebuie să corespundă numărului şi tipului valorilor din subinterogare.

Modificarea datelor dintr-un tabel

Înregistrările existente într-un tabel pot fi modificate cu ajutorul instrucţiunii UPDATE. UPDATE tabel SET coloana = valoare [, coloana=valoare] [WHERE conditie];

unde:

tabel este numele tabelului; coloana este numele coloanei din tabelul în care se face modificarea; valoare este valoarea sau subinterogarea corespunzătoare coloanei; conditie identifică înregistrările care trebuie modificate, fiind alcătuită din expresii, nume

de coloane, constante, subinterogări şi operatori de comparaţie.

Confirmarea unei operaţii de modificare se obţine prin interogarea tabelului, afişând tuplurile modificate. Notă: Este indicată utilizarea cheii primare pentru a identifica o singură înregistrare. Folosirea altor coloane poate conduce la modificarea mai multor înregistrări. De exemplu, identificarea unei singure înregistrări din tabelul emp prin atributul ename poate fi periculoasă, deoarece pot exista mai mulţi angajaţi cu acelaşi nume.

Comanda UPDATE modifică numai anumite înregistrări dacă este specificată clauza WHERE. Exemplul următor transferă angajatul cu numărul 7782 (Clark) la departamentul 20. SQL>UPDATE emp 2 SET deptno = 20 1 row updated. 3 WHERE empno = 7768;

Notă: Dacă se omite clauza WHERE vor fi modificate toate înregistrările din tabel.

Modificarea înregistrărilor folosind subinterogări după mai multe coloane Pentru a modifica simultan mai multe valori ale unui tuplu se va utiliza o subinterogare în

clauza SET a instrucţiunii UPDATE. Formatul unei astfel de instrucţiuni UPDATE este: UPDATE tabel SET (coloana, coloana, …) = (SELECT coloana, coloana, … FROM tabel WHERE conditie) WHERE conditie;

Următoarea instrucţiune UPDATE modifică departamentul şi funcţia angajatului cu numărul 7698 cu valorile corespunzătoare angajatului având numărul 7499. SQL> UPDATE emp 2 SET (job, deptno) = (SELECT job, deptno 3 FROM emp 4 WHERE empno = 7499) 1 row updated. 5 WHERE empno = 7698;

Pentru a efectua modificări într-un tabel pe baza valorilor din alt tabel este necesară utilizarea subinterogărilor în instrucţiunile UPDATE. Exemplul de mai jos actualizează tabelul employee pe baza valorilor din tabelul emp. Este schimbat numărul de departament pentru toţi angajaţii cu aceeaşi funcţie ca cea a angajatului 7788, noul lor număr de departament devenind egal cu al acestuia.

SQL> UPDATE employee 2 SET deptno = (SELECT deptno FROM emp 3 WHERE empno = 7788) 4 WHERE job = (SELECT job FROM emp 2 row updated. 5 WHERE empno = 7788);

Ştergerea înregistrărilor dintr-un tabel Pentru ştergerea uneia sau a mai multor înregistrări dintr-un tabel se va utiliza instrucţiunea

DELETE, al cărei format este. DELETE [FROM] table [WHERE conditie];

unde: tabel este numele tabelului; conditie identifică înregistrările care trebuie şterse şi este alcatuită din expresii, nume de

coloane, constante, subinterogări şi operatori de comparaţie.

Condiţia din clauza WHERE identifică înregistrările ce vor fi şterse. Dacă se omite clauza WHERE se vor şterge toate înregistrările din tabel.

De exemplu, pentru ştergerea persoanelor angajate după 1 Ianuarie 1997 se va executa următoarea instrucţiune DELETE: SQL> DELETE FROM emp 2 WHERE hiredate > TO_DATE('01.01.97', 'DD.MM.YY'); 1 row deleted.

Ştergerea înregistrărilor folosind valori dintr-un alt tabel

Ca şi în cazul instrucţiunii UPDATE se pot folosi subinterogări pentru a şterge înregistrări dintr-un tabel, folosind informaţiile din alt tabel. Exemplul următor şterge toţi angajaţii care lucrează în departamentul cu numele SALES. Subinterogarea caută în tabelul dept numărul departamentului SALES, apoi furnizează acest număr interogării principale care şterge înregistrările corespunzătoare din emp.

SQL> DELETE FROM emp 2 WHERE deptno = (SELECT deptno FROM dept 3 WHERE dname = 'SALES'); 6 rows deleted.

Încălcarea constrângerii de integritate Încercarea de a şterge o înregistrare care conţine un câmp legat de o constrângere de integritate

va fi sancţionată cu o eroare. În exemplul de mai jos se încearcă ştergerea departamentului cu numărul 10 din tabelul dept,

dar această încercarea ar provoca o eroare dacă numărul de departament ar fi o cheie externă pentru tabelul emp. Dacă înregistrarea părinte pe care dorim să o ştergem are înregistrări fii, atunci se va genera mesajul de eroare child record found cu numărul ORA - 02292. SQL> DELETE FROM dept 2 WHERE deptno = 10;

DELETE FROM dept * ERROR at line 1 ORA-02292: integrity constraint(USR.EMP_DEPTNO_FK) violated - child record found

Problema impunerii unor constrângeri de integritate va fi tratată pe larg într-un referat ulterior. Probleme: 1. Să se scrie o interogare care să afişeze numele şi data angajării pentru toţi angajaţii din acelaşi

departament ca Blake (a se exclude Blake).

2. Să se scrie o interogare pentru a afişa numărul şi numele pentru toţi angajaţii care câştigă mai mult decât salariul mediu. Să se sorteze rezultatele în ordinea descrescătoare a salariului.

3. Să se scrie o cerere care va afişa numerele de înregistrare şi numele angajaţilor din departamentele în care lucrează cel puţin un angajat al cărui nume conţine litera ‘T‘.

4. Afişaţi numele, numărul departamentului şi funcţia deţinută pentru toţi angajaţii al căror departament este situat în Dallas.

5. Afişaţi numele şi salariul tuturor angajaţilor subordonaţi lui King. 6. Afişaţi numărul departamentului, numele şi funcţia tuturor angajaţilor din departamentul de vânzări

(Sales). 7. Afişaţi numărul, numele şi salariul tuturor angajaţilor care câştigă mai mult decât salariul mediu şi,

în plus, lucrează într-un departament care deţine cel puţin un angajat al cărui nume conţine litera ‘T‘.

8. Scrieţi o interogare care să afişeze numele, numărul departamentului şi salariul oricărui angajat al cărui număr de departament şi salariu să se potrivească cu numărul departamentului şi salariul oricărui angajat care poate percepe comision.

9. Afişaţi numele, numele departamentului şi salariul oricărui angajat al cărui salariu şi comision coincide cu salariul şi comisionul oricărui angajat ce lucrează în Dallas.

10. Scrieţi o interogare care să afişeze numele, data angajării şi salariul pentru toţi angajaţii care au acelaşi salariu şi comision ca angajatul Scott.

11. Scrieţi o interogare care să afişeze angajaţii care câştigă un salariu mai mare decât al oricărui angajat cu funcţia CLERK. Sortaţi rezultatele descrescător după salariu.

12. Scrieţi un fişier script pentru a afişa numele fiecărui angajat, funcţia şi numele departamentului pentru acei angajaţi care lucrează în localitatea furnizată la prompt de utilizator. Condiţia de căutare a localităţii trebuie să fie case-insensitive (să nu depindă de tipul caracterelor: majuscule sau litere mici). Salvaţi fişierul script cu numele p12.sql . Indicaţie: pentru a efectua o căutare case-insensitive se va utiliza funcţia LOWER (transformă şirul argument în caractere mici) sau UPPER (transformă şirul argument în majuscule).

13. Modificaţi p12.sql pentru a crea un raport ce conţine numele departamentului, numele angajatului, data angajării, salariul şi salariul anual pentru toţi angajaţii dintr-o anumită localitate, introdusă de utilizator de la tastatură. Se etichetează coloanele DEPARTMENT NAME, EMPLOYEE NAME, START DATE, SALARY şi ANNUAL SALARY, plasând etichetele formate din două şiruri pe mai multe linii. Se salvează fişierul script cu numele p6.sql .

14. Executaţi fişierul script p14.sql pentru a crea tabelul MY_EMPLOYEE, care va fi utilizat de următoarele problemele.

15. Descrieţi structura tabelului MY_EMPLOYEE pentru a identifica numele câmpurilor. 16. Adăugaţi prima înregistrare din tabelul de mai jos.

ID LAST_NAME FIRST_NAME USERID SALARY 1 Patel Ralph rpatel 795 2 Dancs Betty bdancs 860 3 Biri Ben bbiri 1100 4 Newman Chad cnewman 750 5 Ropeburn Audry aropebur 1550

17. Creaţi un script numit loademp.sql pentru a insera înregistrări în tabelul MY_EMPLOYEE în mod interactiv. Cereţi utilizatorului numărul de identificare (ID), prenumele (FIRST_NAME), numele de familie (LAST_NAME) şi salariul (SALARY) fiecărui angajat. Concatenaţi prima litera a prenumelui şi primele 7 caractere ale numelui de familie pentru a crea USERID.

Indicaţie: pentru extragerea unui subşir din alt şir se va utiliza funcţia SQL SUBSTR(string, m, n)

unde: string este un şir de caractere m poziţia la care începe subşirul n numărul de caractere din subşir

Daca m = 0, se va considera m = 1.

Dacă m > 0, Oracle va căuta de la începutul şirului string. Dacă m < 0, Oracle va căuta de la sfârşitul şirului string. Dacă n este omis, Oracle va returna toate caracterele din string. Dacă n < 1, se va returna null.

Exemplu: SUBSTR(’ABCDEFG’,3,4) = ‘CDEF’ 18. Inseraţi următoarele trei înregistrări din tabelul de mai sus în tabelul MY_EMPLOYEE prin

intermediul fişierului script creat la problema 5. 19. Modificaţi numele de familie al angajatului având ID = 3 în Drexler. 20. Modificaţi salariul la 1000 pentru toţi cei cu salariul < 900. 21. Ştergeţi înregistrarea corespunzătoare lui Betty Dancs din tabelul MY_EMPLOYEE. 22. Ştergeţi toate înregistrările din tabel.

Secţiune opţională

Tranzacţii în bazele de date

Serverul Oracle asigură consistenţa datelor pe baza tranzacţiilor. Tranzacţiile oferă un plus de flexibilitate şi

control în modificarea datelor şi asigură consistenţa datelor în eventualitatea unei erori în sistem sau în procesul utilizator. Tranzacţiile constau din instrucţiuni ale LMD (Limbajul de Manipulare a Datelor) care produc o modificare

consistentă a datelor. De exemplu, un transfer de fonduri între două conturi ar trebui să includă debitarea unui cont şi creditarea celuilalt cu aceeaşi sumă de bani. Ambele acţiuni ar trebui fie să reuşească împreună, fie să eşueze împreună. Operaţia de creditare nu trebuie să fie efectuată fără ca cea de debit să aibă succes. Tipuri de tranzacţii

Tip Descriere

Limbajul de manipulare a datelor (LMD) Constă din orice număr de instrucţiuni LMD pe care serverul Oracle le tratează ca fiind o singură unitate logică de lucru.

Limbajul de definire a datelor (LDD) Constă dintr-o singură instrucţiune LDD

O tranzacţie începe când este întâlnită prima instrucţiune SQL executabilă şi se termină în momentul apariţiei unuia din următoarele evenimentele:

• se execută instrucţiunea COMMIT sau ROLLBACK; • se execută o instrucţiune LDD, cum ar fi, de exemplu, instrucţiunea CREATE; • se execută o instrucţiune LCD (instucţiuni de acordare provilegii); • utilizatorul părăseşte mediul SQL*Plus; • apare o eroare de sistem.

După încheierea unei tranzacţii, următoarea tranzacţie va începe automat la întâlnirea primei instrucţiuni SQL. Modificările realizate de o instrucţiune LDD sunt salvate automat şi de aceea încheie în mod implicit o tranzacţie.

Comenzi pentru controlul explicit al tranzacţiilor Logica tranzacţiilor poate fi controlată cu ajutorul instrucţiunilor COMMIT, SAVEPOINT şi ROLLBACK.

INSERT UPDATE DELETE INSERT

COMMIT Punct salvare A Punct salvare B

ROLLBACK la punctul B

ROLLBACK la punctul A

ROLLBACK

Tranzacţie

Comandă Descriere

COMMIT Încheie actuala tranzacţie, toate modificările efectuate asupra datelor devenind permanente.

SAVEPOINT nume Marchează un punct de întoarcere în cadrul tranzacţiei curente.

ROLLBACK [TO SAVEPOINT nume]

Instrucţiunea ROLLBACK încheie tranzacţia curentă pierzându-se toate modificările temporare asupra datelor. Instrucţiunea ROLLBACK TO SAVEPOINT nume şterge punctul de întoarcere specificat de nume şi anulează toate schimbările survenite după el.

Procesarea implicită a tranzacţiilor

• Salvarea automată a modificărilor are loc în următoarele circumstanţe: - se execută o comandă LDD;

- se părăseşte normal mediul SQL*Plus, fără a executa explicit instrucţiunea COMMIT sau ROLLBACK • Un rollback automat se produce în condiţiile unei terminări anormale a sesiunii SQL*Plus sau în cazul unei căderi

a sistemului.

Notă: În SQL*Plus este disponibilă o a treia comandă, instrucţiunea AUTOCOMMIT, ce poate fi setată ON sau OFF. Dacă este setată pe ON, fiecare instrucţiune LMD conduce la salvarea modificărilor imediat ce este executată. În acest caz nu se mai poate reface starea anterioară (un rollback nu mai este posibil). Dacă este setată pe OFF, instrucţiunea COMMIT poate fi executată explicit. De asemenea, COMMIT este executată odată cu o instrucţiune LDD sau la părăsirea mediului SQL*Plus.

Fiecare modificare efectuată în timpul unei tranzacţii este temporară până în momentul execuţiei comenzii COMMIT.

Starea datelor înainte de un COMMIT sau ROLLBACK:

• operaţiile de manipulare a datelor afectează iniţial buffer-ul bazei de date; de aceea, starea iniţiala a datelor poate fi reconstituită;

• utilizatorul curent poate revedea schimbările efectuate prin interogarea tabelelor; • alţi utilizatori nu pot vedea modificările făcute de utilizatorul curent. Serverul Oracle asigură consistenţa

datelor la citire pentru a se asigura că fiecare utilizator vede datele aşa cum existau ele în momentul ultimei salvări.

• înregistrările afectate de modificări sunt protejate (locked), astfel încât alţi utilizatori să nu poată efectua modificări asupra lor.

Starea datelor după execuţia instrucţiunii COMMIT:

• modificările asupra datelor sunt salvate în baza de date; • starea anterioară a datelor nu mai poate fi restaurată; • toţi utilizatorii pot vedea rezultatele tranzacţiei; • protecţia înregistrărilor modificate este înlăturată; astfel, aceste înregistrări pot fi modificate de alţi utilizatori; • toate punctele de întoarcere sunt şterse.

Următorul exemplu creează un nou departament ADVERTISING cu cel puţin un angajat şi salvează modificările

făcute. SQL> INSERT INTO department(deptno, dname, loc) 2 VALUES (50, 'ADVERTISING','MIAMI'); 1 row created. SQL> UPDATE employee 2 SET deptno = 50 1 row updated. 3 WHERE empno = 7876; SQL> COMMIT; Commit complete.

Anularea modificărilor (refacerea stării iniţiale)

Anularea modificărilor temporare se face cu ajutorul instrucţiunii ROLLBACK. După un ROLLBACK:

• modificările efectuate sunt anulate; • este refăcută starea anterioară a datelor; • este ridicată protecţia asupra înregistrărilor implicate în tranzacţie.

Ca exemplu, în încercarea de a şterge o înregistrare din tabelul emp, se poate întâmpla să ştergem accidental întregul tabel. Această greşeală se poate corecta, urmând ca apoi să se execute instrucţiunile corecte şi să se salveze modificările. SQL> DELETE FROM emp; 14 rows deleted. SQL> ROLLBACK; Rollback complete. SQL> DELETE FROM emp 2 WHERE empno = 7902; 1 row deleted. SQL> SELECT * 2 FROM emp No rows selected. 3 WHERE empno = 7902; SQL> COMMIT; Commit complete. Anularea modificărilor până la un punct de salvare

Crearea unui marcaj în cadrul tranzacţiei curente se poate face cu ajutorul instrucţiunii SAVEPOINT. Astfel, tranzacţia poate fi împărţită în secţiuni mai mici, oferind posibilitatea anulării modificărilor temporare până la acel marcaj folosind instrucţiunea ROLLBACK TO SAVEPOINT nume_savepoint. Dacă se creează un al doilea punct de salvare cu acelaşi nume ca unul anterior, punctul anterior de salvare este şters. SQL> UPDATE … SQL> SAVEPOINT update_done; Savepoint created. SQL> INSERT … SQL> ROLLBACK TO update_done; Rollback complete.

Instrucţiunea ROLLBACK anterioară va anula modificările efectuate de instrucţiunea INSERT.

Rollback la nivel de comandă Se poate anula o parte din tranzacţie printr-un rollback implicit dacă este detectată o eroare la execuţia unei

instrucţiuni. Dacă o singură instrucţiune LMD eşuează în timpul execuţiei unei tranzacţii, efectul ei este anulat printr-un rollback la nivel de comandă, dar schimbările efectuate de comenzi anterioare nu vor fi anulate. Ele pot fi salvate (COMMIT) sau anulate (ROLLBACK) în mod explicit de către utilizator.

Oracle execută o comandă COMMIT implicită înainte şi după orice comandă LDD. În cazul în care o instrucţiune LDD nu se execută cu succes, nu pot fi anulate instrucţiunile anterioare pentru că serverul a executat un COMMIT.

Consistenţa la citire Utilizatorii unei baze iniţiază două tipuri de acces la baza de date:

• operaţii de citire (instrucţiunea SELECT); • operaţii de scriere (instrucţiunile INSERT, UPDATE, DELETE).

Consistenţa la citire este necesară pentru ca:

• utilizatorii care citesc / modifică datele să aibă o vedere consistentă asupra datelor; • utilizatorii care citesc datele să nu vadă datele care sunt în curs de modificare; • utilizatorii care modifică datele să aibă siguranţa că schimbările în baza de date se fac în mod consistent; • modificările efectuate de un utilizator să nu intre în conflict sau să afecteze modificările efectuate de un alt

utilizator.

Scopul consistenţei la citire este să asigure că fiecare utilizator vede datele în starea în care erau la ultima salvare, înainte de execuţia unei instrucţiuni LMD.

Implementarea consistenţei la citire Consistenţa la citire este o implementare automată ce păstrează o copie parţială a bazei de date în segmente de

rollback.

Când se realizează o operaţie de inserare, actualizare sau ştergere asupra bazei de date serverul Oracle copie datele, înainte de modificare, într-un segment de rollback. Toţi utilizatorii, cu excepţia celui care a iniţiat modificarea, văd

baza de date în starea anterioară începerii modificării; de fapt ei văd copia datelor iniţiale din segmentul de rollback. Când rezultatul unei instrucţiuni LMD este salvat, modificarea din baza de date devine vizibilă oricărui utilizator ce execută o instrucţiune SELECT. Spaţiul ocupat de datele vechi din segmentul de rollback este eliberat pentru a fi reutilizat.

Dacă tranzacţia este anulată, modificările sunt la rândul lor anulate:

• datele originale din segmentul rollback sunt rescrise în tabel; • toţi utilizatorii văd baza de date aşa cum era înainte de începerea tranzacţiei.

Comenzi SQL*Plus pentru formatarea modului de afişare a rezultatelor

Următoarele comenzi pot fi utilizate pentru a controla anumite caracteristici de afişare ale unui raport:

Comanda

Descriere

COL[UMN] [coloana optiune] Controlează formatul coloanelor TTI[TLE] [text | OFF | ON ] Specifică un ”header” care va apare în partea superioară a fiecărei pagini BTI[TLE] [text | OFF | ON ] Specifică un “footer” care va apare în partea de jos a fiecărei pagini BRE[AK] [ON elem_raport] Anulează valorile duplicat şi împarte liniile din raport. Pentru mai multe detalii

executaţi comanda HELP break. Indicaţii: - toate comenzile de formatare rămân valabile până la sfârşitul sesiunii SQL*Plus sau până în momentul rescrierii

sau ştergerii acestor opţiuni. - după fiecare raport se vor restaura setările SQL*Plus la valorile implicite. - dacă se va crea un alias pentru o coloană, referirile ulteriore se vor face la acel alias, nu la numele coloanei.

Comanda COLUMN

COL[UMN] [ {coloana | alias} [optiuni]]

Optiune Descriere CLE[AR] anulează orice format de coloană FOR[MAT] format modifică formatul de afişare al datelor din coloană HEA[DING] text setează titlul coloanei. Dacă nu este utilizată opţiunea JUSTIFY, o linie verticală (|) va

forţa trecerea pe rândul următor a textului rămas din header. JUS[TIFY] {aliniere} aliniază titlul coloanei la stânga, centrat sau la dreapta NOPRI[NT] ascunde coloana. NUL[L] text indică textul ce va fi afişat pentru valori null PRI[NT] arată coloana TRU[NCATED] trunchiază şirul la sfârşitul primei linii de afişaj WRA[PPED] transferă sfârşitul de şir pe linia următoare

Exemple de utilizare a comenzii COLUMN: COLUMN ename HEADING ‘Employee|Name’ FORMAT A15 – noua denumire a coloanei ename este Employee Name (scris pe două rânduri), iar pentru valori se alocă un spaţiu de 15 caratere COLUMN sal JUSTIFY LEFT FORMAT $99,999.00 COLUMN mgr FORMAT 99999999 NULL ‘No manager’

COLUMN ename - afişează setarea curentă pentru coloana ENAME COLUMN ename CLEAR – anulează setările pentru coloana ENAME Modele de format pentru coloane:

Element Descriere Exemplu Rezultat An Setează lăţimea zonei de afişare la n caractere N/A N/A 9 O singură cifră, cu suprimarea valorilor 0 999999 1234 0 prefixează numărul cu un zero 099999 01234 $ Simbolul pentru dolar $9999 $1234 L Moneda locală L9999 L1234 . Poziţia punctului zecimal 9999.99 1234.00 , Virgulă pentru separarea cifrei zecilor de mii 9,999 1,234

Comenzile TTITLE şi BTITLE

TTI[TLE] [text | OFF | ON]

BTI[TLE] [text | OFF | ON] unde: text reprezintă titlul raportului. Se introduc apostrofuri dacă textul conţine mai multe cuvinte.

Comanda TTITLE este utilizată pentru a formata header-ul paginii, iar comanda BTITLE pentru footers. Procedura de creare a unui fişier script care să execute un raport

1. Se creează instrucţiunea SQL SELECT 2. Se salveaza instrucţiunea SELECT într-un fişier script 3. Se încarcă fişier script într-un editor text 4. Se adaugă comenzile de formatare înaintea instrucţiunii SELECT 5. Se verifică dacă după instrucţiunea SELECT urmează caracterul ‘/’ sau ‘;’ 6. După instrucţiunea SELECT se anulează comenzile de formatare 7. Se salvează fişierul script 8. Se execută comanda START fisier pentru a executa script-ul Exemplu de raport

Fri Oct 24 page 1

Employee Report

Job Category Employee Salary ............................................. ........................................... ................................ CLERK ADAMS $1,100.00 CLERK JAMES $950.00 CLERK MILLER $1,300.00 CLERK SMITH $800.00 MANAGER BLAKE $2,850.00 MANAGER CLARK $2,450.00 MANAGER JONES $2,975.00 SALESMAN ALLEN $1,600.00 SALESMAN MARTIN $1,250.00 SALESMAN TURNER $1,500.00 SALESMAN WARD $1,250.00

Confidential Să se creeze un fişier script pentru a executa un raport care afişează funcţia, numele şi salariul fiecărui angajat al

cărui salariu este sub 3000$. Se adaugă ca header şirul “Employee Report”, centrat, pe două rânduri şi ca footer şirul “Confidential”, centrat. Se redenumeşte coloana JOB “Job category”, despărţită pe două rânduri. Se redenumeşte coloana angajaţilor “Employee”. Se redenumeşte coloana pentru salariu “Salary” şi se alege formatul corespunzător exemplului $2,500.00

TTITLE ‘EmployeeIReport’ BTITLE ‘Confidential’ COLUMN job HEADING ‘Job|Category’ Format A15 COLUMN ename HEADING ‘Employee’ Format A15 COLUMN sal HEADING ‘Salary’ Format $99,999.99 REM ** Insert SELECT statement SELECT job,ename,sal FROM scott.emp WHERE sal<3000 ORDER BY job,ename

Notă: REM indică un comentariu în SQL*Pus

1

Crearea şi gestionarea tabelelor. Definirea constrângerilor de integritate în SQL

Obiective Scopul acestui referat este de a prezenta instrucţiunile LDD ce pot fi utilizate pentru modificarea schemei logice a unei baze de date (crearea de tabele, modificarea structurii unui tabel, redenumirea unui tabel). De asemenea, este indicat modul în care pot fi utilizate constrângerile de integritate pentru a preveni introducerea unor date invalide în baza de date. După parcurgerea acestui referat, studentul va deţine cunoştinţele necesare:

• descrierii obiectelor din baza de date; • pentru crearea unui tabel; • descrierii tipurilor de date ce pot fi utilizate în momentul specificării definiţiilor de coloane; • modificării structurii unui tabel; • ştergerii, redenumirii şi trunchierii tabelelor. • definirii constrângerilor de integritate; O bază de date Oracle poate conţine structuri de date multiple. Fiecare structură trebuie definită în faza de proiectare a bazei de date, astfel încât să poată fi creată în momentul construirii bazei de date. Tipurile de obiecte ale unei baze de date sunt date în tabelul următor.

Obiect Descriere Tabel Unitatea de bază pentru stocarea datelor; compusă

din linii şi coloane. Vedere Reprezentare logică a unei submulţimi de date

dintr-unul sau mai multe tabele. Secvenţă Generează valori pentru chei primare.

Index Îmbunătăţeşte performanţele în cazul unor interogări.

Sinonim Furnizează alte nume obiectelor.

Instrucţiunea CREATE TABLE Această instrucţiune face parte din Limbajul de Definire a Datelor (LDD) şi permite crearea unui tabel în care vor fi stocate date. În general, instrucţiunile LDD reprezintă o submulţime a instrucţiunilor SQL şi sunt utilizate pentru crearea, modificarea şi ştergerea obiectelor unei baze de date Oracle. Pentru ca un utilizator să poată crea un tabel, el trebuie să deţină dreptul CREATE TABLE şi o zonă de stocare în care va crea obiecte de tip tabel. Dreptul de a crea un tabel este acordat de administratorul bazei de date prin intermediul instrucţiunilor LCD (Limbajul de Control al Datelor) GRANT şi REVOKE.

2

Sintaxa instrucţiunii CREATE TABLE este următoarea: CREATE TABLE [schema.]tabel

(col tip_date [DEFAULT expr] [, col tip_date [DEFAULT expr] ...]);

unde:

schema coincide cu numele utilizatorului proprietar al tabelului; tabel numele tabelului; DEFAULT expr specifică o valoare implicită; col numele coloanei; tip_date tipul de date şi lungimea coloanei col.

Convenţii pentru denumirea tabelelor

• numele trebuie sa înceapă cu o literă; • numele unui tabel poate avea lungimea de 1-30 caractere; • caracterele permise sunt numai A-Z, a-z, 0-9, _, $ şi #; • numele unui tabel nu trebuie să coincidă cu numele altor obiecte din baza de date (obiecte ale

aceluiaşi utilizator); • nu trebuie sa fie un nume rezervat Oracle. Opţiunea DEFAULT

Unei coloane i se poate asigna o valoare implicită utilizând opţiunea DEFAULT. Această opţiune previne introducerea unor valori NULL în tabel în cazul inserării unei linii care nu specifică o valoare pentru coloana în cauză. Valoarea implicită poate fi o valoare literală, o expresie sau o funcţie SQL, cum ar fi SYSDATE sau USER, dar nu poate fi cea a unei alte coloane sau o pseudocoloană (cum ar fi NEXTVAL sau CURRVAL). Valoarea implicită trebuie să corespundă tipului de date al coloanei. … hiredate DATE DEFAULT SYSDATE, …

Pentru coloana hiredate s-a definit valoarea implicită returnată de funcţia SYSDATE (data sistemului). Exemplul următor creează tabelul dept, având trei coloane: DEPTNO, DNAME şi LOC. Pentru a se confirma crearea tabelului dept se va executa comanda DESCRIBE. SQL> CREATE TABLE DEPT 2 (deptno NUMBER(2), 3 dname VARCHAR(14), 4 loc VARCHAR(13)); Table created.

SQL> DESCRIBE dept

Name Null? Type ---------------- --------- ---------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR(14) LOC VARCHAR(13)

3

Interogarea dicţionarului de date Rezultatele execuţiei instrucţiunilor LDD sunt memorate în dicţionarul de date. Prin interogarea dicţionarului de date se pot obţine diverse informaţii despre obiectele aparţinând unui utilizator. Tabelele cele mai utilizate din dicţionarul de date sunt USER_TABLES (conţine informaţii despre tabelele create de utilizatorul curent), USER_OBJECTS (conţine informaţii despre toate obiectele create de utilizatorul curent) şi USER_CATALOG. • Afişarea tabelelor unui utilizator: SQL> SELECT table_name 2 FROM user_tables;

• Afişarea obiectelor distincte aparţinând unui utilizator: SQL> SELECT DISTINCT object_name, object_type 2 FROM user_objects;

• Afişarea tabelelor, vederilor, sinonimelor şi secvenţelor unui utilizator: SQL> SELECT * 2 FROM user_catalog;

Deoarece tabelul USER_CATALOG are definit un sinonim, numit CAT, se poate folosi acest nume în loc de USER_CATALOG. SQL> SELECT * 2 FROM CAT;

Tipuri de date

Tipurile de date ce pot fi utilizate în definiţiile de coloane sunt prezentate în tabelul următor: Tipul de data Descriere VARCHAR2(size) Dată de tip şir de caractere, de lungime variabilă (trebuie specificată

dimensiunea maximă size; valoarea implicită este 1, iar cea maximă 4000)

CHAR(size) Dată de tip şir de caractere de lungime size fixă (valoarea implicită şi cea minimă este 1, iar cea maximă 2000)

NUMBER(p,s) Număr având p cifre şi s cifre zecimale. (p poate lua valori între 1 şi 38, s între –84 şi 127)

DATE Valori de tip dată calendaristică şi timp cuprinse între Ianuarie 1, 4712 B.C. şi Decembrie 31, 9999 A.D.

LONG Dată de tip şir de caractere de lungime variabilă, dimensiunea maximă fiind 2 Gb.

CLOB Dată de tip şir de caractere pe 1 octet, dimensiunea maximă fiind 4 Gb. RAW(size) Dată binară de dimensiune size octeţi. Dimensiunea maximă este de

2000 octeţi. Valoarea size trebuie specificată. LONG RAW Dată binară de dimensiune maximă 2 Gb. BLOB Dată binară de dimensiune maximă 4 Gb. BFILE Conţine un pointer către un fişier binar extern bazei de date ce poate

avea dimensiunea maximă de 4 Gb.

4

Crearea unui tabel utilizând o subinterogare A doua metodă de creare a unui tabel constă în utilizarea clauzei AS subinterogare în instrucţiunea CREATE TABLE, care creează tabelul şi inserează înregistrările furnizate de subinterogare. Sintaxa acestei instrucţiuni este: SQL> CREATE TABLE tabel 2 (coloana [, coloana …]) 3 AS subinterogare;

unde:

tabel numele tabelului; coloana numele coloanei împreună cu valoare implicită şi constrângeri de

integritate (ultimele două elemente sunt opţionale); subinterogare instrucţiunea SELECT care furnizează mulţimea de înregistrări ce trebuie

inserate în tabel. Instrucţiunea următoare creează tabelul dept30, inserând informaţii despre angajaţii ce lucrează în departamentul 30. Structura tabelului dept30 este dată de lista de coloane din clauza SELECT.

SQL> CREATE TABLE dept30 2 AS 3 SELECT empno, ename, sal*12 ANNSAL, hiredate 4 FROM emp 5 WHERE deptno=30; Table created. SQL> DESCRIBE dept30

Name Null? Type ----------- ------------- ----------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR(10) ANNSAL NUMBER HIREDATE DATE

Instrucţiunea ALTER TABLE Această instrucţiune este utilă dacă se urmăreşte modificarea structurii unui tabel prin: • adăugarea unei coloane; • ştergerea unei coloane; • modificarea definiţiei unei coloane existente; • definirea unei valori implicite pentru o coloană. Pentru adăugarea unei coloane se va utiliza instrucţiunea ALTER TABLE împreună cu clauza ADD, iar pentru modificarea unei coloane se va utiliza clauza MODIFY. Dacă se doreşte eliminarea unor coloane din definiţia tabelului se va utiliza clauza DROP.

5

SQL> ALTER TABLE tabel 2 ADD (coloana tip_date [DEFAULT expr]

3 [, coloana tip_date ...]); SQL> ALTER TABLE tabel 2 MODIFY (coloana tip_date [DEFAULT expr] 3 [, coloana tip_date ...]);

SQL> ALTER TABLE tabel 2 DROP (coloana [, coloana …]);

unde:

tabel nume tabel; coloana nume coloană; tip_date tipul de date şi dimensiunea; DEFAULT expr specifică valoarea implicită pentru coloană.

Următorul exemplu introduce o nouă coloană în tabelul emp30, numele acestei coloane fiind job, iar tipul de date asociat este varchar(9). Pentru verificare se utilizează instrucţiunea SELECT. SQL> ALTER TABLE dept30 2 ADD (job VARCHAR(9)); Table altered. SQL> SELECT * FROM dept30;

EMPNO ENAME ANNSAL HIREDATE JOB ------ -------- ------- --------- ----- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 …… 6 rows selected

Observaţii:

Cu ajutorul instrucţiunii ALTER TABLE se pot adăuga, modifica şi şterge coloane În cazul inserării unei coloane nu se poate specifica locul de apariţie al acesteia în schema tabelului. Noua coloană devine automat ultima coloană. Dacă tabelul conţine înregistrări în momentul adăugării unei coloane noi, atunci noua coloană va fi iniţializată cu valori NULL pentru toate înregistrările. Se pot modifica specificaţiile coloanelor utilizând instrucţiunea ALTER TABLE cu clauza MODIFY. Modificările permise sunt schimbarea tipului de date, a dimensiunii, a valorii iniţiale şi impunerea unei constrângeri NOT NULL. SQL> ALTER TABLE dept30 2 MODIFY (ename VARCHAR(15)); Table altered.

6

Observaţii: • se poate mări precizia sau scala unei coloane numerice; • se poate micşora lăţimea unei coloane dacă aceasta conţine numai valori NULL sau dacă

tabelul nu are înregistrări; • se poate schimba tipul de date dacă în coloana respectivă există numai valori NULL; • se poate converti o coloană de tip CHAR la tipul VARCHAR2 sau invers dacă aceasta

conţine valori NULL sau dacă nu se modifică lăţimea; • schimbarea valorii implicite pentru o coloană afectează numai inserările ulterioare în tabel.

Ştergerea unui tabel Comanda DROP TABLE şterge definiţia unui tabel. Atunci când se şterge un tabel, baza de date pierde toate înregistrările din tabel, împreună cu indecşii asociaţi acestuia. Sintaxa instrucţiunii DROP TABLE este: DROP TABLE nume_tabel;

Exemplu: SQL> DROP TABLE dept30;

Table dropped. Observaţii:

• toate datele sunt şterse; • orice obiect dependent de tabel (vedere sau sinonim) va continua să existe, dar va fi invalid; • orice tranzacţie în curs va fi finalizată; • numai utilizatorul care a creat tabelul sau cel care are privilegiul DROP ANY TABLE poate

şterge un tabel.

Redenumirea unui obiect din baza de date

Pentru a modifica numele unui tabel, a unei vederi, secvenţe sau sinonim se utilizează instrucţiunea RENAME.

RENAME nume_vechi TO nume_nou;

SQL> RENAME dept30 TO department; Table renamed.

Notă: Numai proprietarul poate modifica numele unui obiect. Trunchierea unui tabel Instrucţiunea TRUNCATE TABLE face parte din LDD şi şterge toate înregistrările din tabelul specificat, eliberând spaţiul folosit de tabel. Operaţiunea este ireversibilă, în sensul că instrucţiunea ROLLBACK nu va restaura conţinutul tabelului.

7

TRUNCATE TABLE nume_tabel;

SQL> TRUNCATE TABLE department; Table truncated.

Pentru ştergerea înregistrărilor dintr-un tabel se poate utiliza instrucţiunea DELETE, dar aceasta instrucţiune nu eliberează spaţiul de stocare.

Adăugarea comentariilor de tabel şi de coloană Se pot adăuga comentarii având maxim 2000 octeţi unei coloane, unui tabel sau unei vederi utilizând instrucţiunea COMMENT. COMMENT ON <TABLE tabel | COLUMN tabel.col>

IS text;

unde: tabel numele tabelului; col numele coloanei din tabel; text textul comentariului, încadrat de apostrofuri (’ ’).

Comentariile sunt stocate în dicţionarul de date şi pot fi vizualizate în coloana COMMENTS a uneia din următoarele vederi:

- ALL_COL_COMMENTS; - conţine toate comentariile de coloane - USER_COL_COMMENTS; - conţine comentariile asociate coloanelor utilizatorului curent - ALL_TAB_COMMENTS; - conţine toate comentariile de tabele - USER_TAB_COMMENTS. - conţine toate comentariile asociate tabelelor utilizatorului curent SQL> COMMENT ON TABLE emp 2 IS ’Employee Information’;

Comment created.

Se poate renunţa la un comentariu, setându-l ca fiind şirul vid (‘’).

SQL> COMMENT ON TABLE emp IS ’’; Impunerea constrângerilor de integritate în SQL Serverul Oracle utilizează constrângerile de integritate pentru prevenirea introducerii unor date invalide în tabele. Constrângerile pot fi utilizate pentru: - forţarea unor reguli la nivel de tabel atunci când o linie este inserată, actualizată sau ştearsă;

constrângerea trebuie satisfăcută pentru ca operaţia să se termine cu succes. - prevenirea ştergerii unui tabel dacă există dependenţe din alte tabele. - oferirea unor reguli pentru alte medii Oracle, cum ar fi Developer/2000.

8

Există cinci tipuri de constrângeri de integritate ce pot fi impuse:

- NOT NULL - specifică faptul că o coloană nu poate conţine o valoare NULL; - UNIQUE - specifică o coloană (o combinaţie de coloane) a cărei valoare

(combinaţie de valori) trebuie să fie unică pentru toate înregistrările din tabel;

- PRIMARY KEY - identifică unic fiecare înregistrare; - FOREIGN KEY - stabileşte o legătură pe baza unei chei externe între o coloană

din tabel şi o coloană din tabelul referit; - CHECK - specifică o condiţie ce trebuie satisfăcută de fiecare înregistrare. Toate constrângerile definite de un utilizator sunt păstrate în dicţionarul de date. Pentru vizualizarea constrângerilor impuse unui tabel putem interoga dicţionarul de date, mai exact vederea USER_CONSTRAINTS.

Definirea constrângerilor

Sintaxa instrucţiunii CREATE ce impune constrângeri asupra unei coloane sau asupra unui tabel este: CREATE TABLE [schema.]tabel ( coloana tip_date [DEFAULT expr] [constrangere_coloana], ……… [constrangere_tabel]);

unde: schema numele utilizatorului proprietar; tabel numele tabelului; DEFAULT expr specifică o valoare implicită, dacă este omisă o valoare

pentru atribut la inserare; coloana numele coloanei; tip_date tipul de date şi dimensiunea; constrangere_coloana constrângere de integritate ca parte a definiţiei de coloană; constrangere_tabel constrângere de integritate ca parte a definiţiei tabelului.

CREATE TABLE emp( empno NUMBER(4), ename VARCHAR(2), ……… deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_empno_pk

PRIMARY KEY(EMPNO)); Constrângerile sunt de obicei definite în acelaşi timp cu tabelul. Ele pot fi adăugate şi după crearea tabelului cu ajutorul instrucţiunii ALTER TABLE. Constrângerile pot fi definite la unul din următoarele două nivele:

Nivel constrângere

Descriere

Coloană face referire la o singură coloană şi poate defini orice tip de constrângere Tabel face referire la una sau mai multe coloane şi este definită separat de definiţiile

coloanelor din tabel; poate defini orice tip de constrângere, cu excepţia constrângerii NOT NULL

9

• Constrângere la nivel de coloană coloana [CONSTRAINT nume_constrangere] tip_constrangere,

• Constrângere la nivel de tabel coloana , ... [CONSTRAINT nume_constrangere] tip_constrangere (coloana, ...), ...

unde:

nume_constrangere este numele constrângerii; tip_constrangere este tipul constrângerii. În definiţia constrângerii utilizatorul poate furniza un nume sugestiv pentru aceasta. În acest caz constrângerea trebuie definită la nivel de tabel. Dacă unei constrângeri nu i se furnizează un nume, serverul Oracle generează un nume după formatul SYS_Cn, unde n este un întreg care determină unicitatea numelui. Constrângerea NOT NULL Constrângerea NOT NULL indică faptul că valoarea NULL nu este permisă în coloana afectată de constrângere. Coloanele fără constrângeri NOT NULL pot conţine, implicit, valori NULL.

EMP EMPNO ENAME JOB … COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 …

Constrângerea NOT NULL poate fi specificată numai la nivel de coloană. SQL> CREATE TABLE emp( 2 empno NUMBER(4) 3 ename VARCHAR2(10) NOT NULL 4 job VARCHAR2(9) 5 mgr NUMBER(4) 6 hiredate DATE 7 sal NUMBER(7,2) 8 comm NUMBER(7,2) 9 deptno NUMBER(7,2) NOT NULL);

Constrângere NOT NULL (nici o înregistrare nu poate conţine o valoare NULL în această coloană)

Absenţa constrângerii NOT NULL (orice înregistrare poate conţine valoarea NULL pentru această coloană)

Constrângere NOT NULL

10

În exemplul de mai sus se aplică constrângerea NOT NULL coloanelor ename şi deptno din tabelul emp. Deoarece aceste constrângeri nu au nume, server-ul Oracle va crea automat nume pentru ele. Numele unei constrângeri poate fi indicat în definiţia constrângerii, doar dacă aceasta apare la nivelul tabelului (după definirea tuturor coloanelor):

… deptno NUMBER(7,2) CONSTRAINT emp_deptno_nn NOT NULL …

Constrângerea UNIQUE

constrângere UNIQUE DEPT


50 SALES DETROIT 60 BOSTON

O constrângere de integritate de tip cheie unică cere ca fiecare valoare din coloană sau din mulţimea de coloane să fie unice – două înregistrări ale tabelului nu pot avea valori duplicat corespunzătoare cheii unice. Coloana (mulţimea de coloane) inclusă în definiţia cheii unice se numeşte cheie unică. Dacă o cheie unică conţine mai multe coloane se numeşte cheie unică compusă.

O constrângere de tip cheie unică permite introducerea valorilor NULL dacă nu a fost definită o constrângere NOT NULL pentru acea coloană. De fapt, orice număr de înregistrări pot include valori NULL în coloane fără constrângeri NOT NULL, deoarece valorile NULL nu sunt egale cu nimic. O valoare NULL într-o coloană (sau în toate coloanele unei chei unice compuse) va satisface întotdeauna o constrângere de cheie unică.

Notă: Din cauza mecanismului de căutare a constrângerilor UNIQUE în una sau mai multe coloane, nu este permisă existenţa unor valori identice în coloanele NOT NULL a unei constrângeri de cheie unică compusă. Constrângerea de tip cheie unică (UNIQUE) definită la nivel de tabel sau de coloană

Constrângerile de tip cheie unică pot fi definite la nivel de coloană sau de tabel. O cheie unică compusă este creată utilizând definiţia la nivel de tabel (după definirea tuturor coloanelor). SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR(14), 4 loc VARCHAR(13), 5 CONSTRAINT dept_dname_uk UNIQUE(dname));

Insert

Permisă

nu e permisă ( DNAME=’SALES’ există deja)

11

În exemplul anterior se aplică constrângerea de cheie unică coloanei dname din tabelul dept, numele constrângerii fiind DEPT_DNAME_UK.

Notă: Serverul Oracle forţează implicit constrângerea de cheie unică în momentul creării unui index unic după cheia unică. Constrângerea PRIMARY KEY PRIMARY KEY DEPT


20 MARKETING DALLAS FINANCE NEW YORK

Constrângerea de cheie primară creează o cheie primară pentru tabel. Doar o singură cheie primară poate fi creată pentru un anumit tabel. O cheie primară este o coloană sau mulţime de coloane ce identifică unic fiecare înregistrare din tabel. Această constrângere forţează unicitatea coloanei sau a mulţimii de coloane şi asigură că nici o coloană din cheia primară nu poate conţine valoarea NULL. Constrângerea cheii primare definită la nivel de coloană sau de tabel Constrângerea de cheie primară (PRIMARY KEY) poate fi definită la nivel de tabel (dacă implică mai multe coloane sau dacă i se atribuie un nume) sau de coloană (implică o singură coloană şi este nedenumită). O cheie primară compusă este creată utilizând definiţia la nivel de tabel. SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13), 5 CONSTRAINT dept_dname_uk UNIQUE (dname), 6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

Exemplul de mai sus defineşte o cheie primară formată din coloana deptno din tabelul dept. Numele constrângerii este DEPT_DEPTNO_PK. Notă: Un index unic este creat automat pentru o coloană cheie primară.

INSERT

Nu este permisă (DEPTNO este NULL)

Nu este permisă (DEPTNO=20 există deja)

12

Constrângerea FOREIGN KEY (sau de referinţă)

DEPT DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS ….

EMP EMPNO ENAME JOB … COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 …

7571 FORD MANAGER 200 9 7571 FORD MANAGER 200

Cheia externă sau constrângerea de integritate referenţială desemnează o coloană sau o combinaţie de coloane pe post de cheie externă şi stabileşte o relaţie cu o cheie primară sau o cheie unică din acelaşi tabel sau din alt tabel. În exemplul de mai sus, coloana deptno a fost definită cheie externă în tabelul emp (denumit dependent sau tabel copil); ea referă coloana DEPTNO din tabelul dept (denumit referit sau tabel părinte).

Valoarea unei chei externe trebuie să coincidă cu o valoare deja existentă în tabelul părinte sau să fie NULL.

Observaţie: Cheile externe sunt bazate pe valorile datelor din cele două tabele şi sunt pointeri pur logici, nu fizici.

Constrângere de referinţă definită la nivel de coloană sau tabel Constrângerile de chei externe pot fi definite la nivelul unui tabel sau unei coloane. O cheie externă compusă este creată folosind definiţia la nivel de tabel. SQL> CREATE TABLE emp( 2 empno NUMBER(4), 3 ename VARCHAR2(10) NOT NULL, 4 job VARCHAR2(9), 5 mgr NUMBER(4), 6 hiredate DATE, 7 sal NUMBER(7,2), 8 comm NUMBER(7,2), 9 deptno NUMBER(7,2) NOT NULL, 10 CONSTRAINT emp_detpno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno));

PRIMARY KEY

FOREIGN KEY

INSERT

Permisă Nu este permisă (DEPTNO=9 nu există în tabelul DEPT)

13

Exemplul de mai sus defineşte o constrângere de tip cheie externă pe coloana deptno din tabelul emp. Numele constrângerii este EMP_DEPTNO_FK.

Sintaxa constrângerii de referinţă este următoarea: [CONSTRAINT nume_constr] FOREIGN KEY (col, ...) REFERENCES tabel_referit (col, ...) [ON DELETE <CASCADE | SET NULL>]

• FOREIGN KEY Defineşte coloana din tabelul copil la nivelul constrângerii de tabel.

• REFERENCES Identifică tabelul părinte şi coloana din tabelul părinte.

• ON DELETE CASCADE Indică faptul că în situaţia în care se şterge o linie din tabelul părinte, liniile dependente din tabelul copil vor fi şterse.

• ON DELETE SET NULL Dacă se şterge o linie din tabelul părinte, liniile dependente din tabelul copil vor primi valori NULL în coloanele implicate în constrângere.

Fără opţiunea ON DELETE CASCADE | SET NULL linia din tabelul părinte nu va putea fi ştearsă dacă este referită în tabelul copil.

Constrângerea CHECK

Constrângerea de tip CHECK defineşte o condiţie ce trebuie îndeplinită de fiecare linie dintr-un tabel. Condiţia poate utiliza aceleaşi construcţii ca şi condiţiile de interogare, cu următoarele excepţii:

• referiri la pseudocoloanele CURRVAL, NEXTVAL, LEVEL şi ROWNUM. • apeluri de funcţii SYSDATE, UID, USER şi USERENV. • interogări care fac referire la alte valori din alte rânduri.

Nu există o limitare pentru numărul de constrângeri CHECK ce pot fi definite pe o coloană. Constrângerile CHECK pot fi definite la nivel de coloană sau de tabel.

Exemplul de mai jos defineşte o constrângere CHECK pentru coloana deptno din tabelul emp, condiţia constrângerii fiind ca valoarea din coloană să fie cuprinsă între 10 şi 99.

…, deptno NUMBER(2), CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99)…

Adăugarea unei constrângeri

Adăugarea unei constrângeri la un tabel deja existent se face cu instrucţiunea ALTER TABLE cu clauza ADD.

ALTER TABLE tabel ADD [ CONSTRAINT nume_constrangere] tip (coloana);

unde: tabel este numele tabelului nume_constrangere este numele constrângerii tip este tipul constrângerii

coloana este numele coloanei afectată de constrângere

14

Atentie! Deşi denumirea constrângerilor este opţională este recomandată furnizarea unor nume. În caz contrar va fi generat un nume de către sistem.

Observaţii: • se poate adăuga, şterge, activa sau dezactiva o constrângere, dar nu se poate modifica

definiţia acesteia. • se poate adăuga o constrângere de tip NOT NULL la o coloană existentă folosind clauza

MODIFY din instrucţiunea ALTER TABLE.

Exemplul următor creează o constrângere de referinţă (FOREIGN KEY) în tabelul emp. Impunerea acestei constrângeri externe indică faptul că un manager trebuie să existe ca angajat activ în tabelul EMP.

SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno);

Table altered.

Eliminarea unei constrângeri

Pentru a elimina o constrângere trebuie identificat numele constrângerii. Această identificare se poate face cu ajutorul vederilor USER_CONSTRAINTS şi USER_CONS_COLUMNS din dicţionarul de date. Odată identificat numele constrângerii se va utiliza instrucţiunea ALTER TABLE împreuna cu clauza DROP. ALTER TABLE tabel DROP PRIMARY KEY | UNIQUE (coloana) | CONSTRAINT nume_constrangere [CASCADE]; unde: tabel este numele tabelului coloana este numele coloanei afectată de constrângere nume_constrangere este numele constrângerii.

Opţiunea CASCADE din clauza DROP are ca efect eliminarea tuturor constrângerilor dependente.

Atentie: Când se elimină o constrângere de integritate, aceasta nu mai este folosită de către serverul Oracle şi nu mai este disponibilă în dicţionarul de date. Pentru eliminarea constrângerii în raport cu managerii din tabelul emp se va utiliza instrucţiunea

SQL> ALTER TABLE emp 2 DROP CONSTRAINT emp_mgr_fk;

Table altered.

Pentru eliminarea constrângerii de tip cheie primară din tabelul dept, împreună cu constrângerea externă asociată coloanei deptno se poate folosi instrucţiunea: SQL> ALTER TABLE dept 2 DROP PRIMARY KEY CASCADE;

Table altered.

15

Dezactivarea constrângerilor

Se poate dezactiva o constrângere fără să fie necesară eliminarea acesteia sau recrearea sa folosind instrucţiunea ALTER TABLE împreuna cu clauza DISABLE.

ALTER TABLE tabel DISABLE CONSTRAINT nume_constrangere [CASCADE];

unde: tabel este numele tabelului nume_constrangere este numele constrângerii.

Notă: - clauza DISABLE se poate folosi atât în instrucţiunea CREATE TABLE, cât şi în

instrucţiunea ALTER TABLE. - clauza CASCADE dezactivează constrângerile de integritate dependente.

Exemplul următor dezactivează constrângerea de tip cheie primară din tabelul emp. Prin utilizarea clauzei CASCADE vor fi dezactivate toate constrângerile dependente.

SQL> ALTER TABLE emp 2 DISABLE CONSTRAINT emp_empno_pk CASCADE;

Table altered. Pentru verificare se va afişa valoarea din coloana STATUS din vederea USER_CONSTRAINTS pentru constrângerea în cauză. Dacă este afişată valoarea ENABLED atunci constrângerea este activată. În cazul în care constrângerea este dezactivată se va afişa valoarea DISABLED.

Activarea unei constrângere dezactivate se face prin utilizarea instrucţiunii ALTER TABLE împreuna cu clauza ENABLE.

ALTER TABLE tabel ENABLE CONSTRAINT nume_constrangere;

unde: tabel este numele tabelului. nume_constrangere este numele constrângerii.

Notă: - dacă se activează o constrângere, constrângerea va fi aplicată tuturor datelor din tabel. Toate

datele din tabel trebuie să satisfacă constrângerea. - dacă se activează o constrângere de tip UNIQUE sau PRIMARY KEY, atunci este creat în

mod automat un index UNIQUE sau PRIMARY KEY.

- clauza ENABLE se poate folosi atât în instrucţiunea CREATE TABLE, cât şi în instrucţiunea ALTER TABLE.

Exemplu: SQL> ALTER TABLE emp 2 ENABLE CONSTRAINT emp_empno_pk;

Table altered.

16

Vizualizarea constrângerilor După crearea unui tabel, se poate confirma existenţa sa prin folosirea instrucţiunii DESCRIBE. Singura constrângere care poate fi verificată în acest mod este constrângerea NOT NULL. Pentru a vedea toate constrângerile din tabel, este necesară interogarea tabelului USER_CONSTRAINTS. Exemplul următor afişează toate constrângerile definite în tabelul EMP. SQL> SELECT constraint_name, constraint_type, 2 search_condition 3 FROM user_constraints 4 WHERE table_name=’EMP’;

CONSTRAINT_NAME C SEARCH_CONDITION ---------------- - ---------------- SYS_C00674 C EMPNO IS NOT NULL SYS_C00675 C DEPTNO IS NOT NULL EMP_EMPNO_PK P …

Notă: Constrângerilor care nu primesc un nume de la proprietarul tabelului li se atribuie un nume automat de către sistem. Coloana C indică tipul constrângerii: C provine de la CHECK, P de la PRIMARY KEY, R de la integritate referenţială şi U de la UNIQUE. De observat faptul că o constrângere NOT NULL este de fapt o constrângere de tip CHECK. Vizualizarea coloanelor asociate constrângerilor Pentru vizualizarea numelor coloanelor implicate în constrângeri se va interoga vederea USER_CONS_COLUMNS din dicţionarul de date. Această vizualizare este utilă în cazul utilizării mecanismului de asociere automată a numelor pentru contrângeri de către sistem. SQL> SELECT constraint_name, column_name, 2 FROM user_cons_columns 3 WHERE table_name=’EMP’;

CONSTRAINT_NAME COLUMN_NAME ---------------- ----------- EMP_DEPTNO_FK DEPTNO EMP_EMPNO_PK EMPNO EMP_MGR_FK MGR SYS_C00674 EMPNO SYS_C00675 DEPTNO

17

Probleme: 1. Creaţi tabelul DEPARTMENT având structura din tabelul următor. Salvaţi instrucţiunea în

scriptul p1.sql, executaţi scriptul şi verificaţi crearea tabelului.

Column Name Id Name Nulls/Unique Datatype Number Varchar2 Length 7 25

2. Introduceţi înregistrări în tabelul DEPARTMENT folosind înregistrările din tabelul DEPT. Includeţi doar coloanele de care aveţi nevoie.

3. Creaţi tabelul EMPLOYEE având structura din tabelul următor. Salvaţi instrucţiunea în fişierul script p3.sql şi apoi executaţi scriptul.

Column Name ID LAST_NAME FIRST_NAME DEPT_ID Nulls/Unique Datatype Number Varchar2 Varchar2 Number Length 7 10 25 7

4. Modificaţi tabelul EMPLOYEE pentru a permite stocarea unor nume mai lungi (de exemplu de lungime 25 de caractere) în coloana LAST_NAME.

5. Verificaţi că cele două tabele create sunt stocate în dicţionarul de date (Indicaţie: afişaţi datele de interes din vederea USER_TABLES).

6. Creaţi tabelul EMPLOYEE2 având aceeaşi structură ca şi tabelul EMP, incluzând doar coloanele EMPNO, ENAME şi DEPTNO. Redenumiţi coloanele în noul tabel astfel: ID, LAST_NAME, DEPT_ID. Se vor prelua înregistrările din tabelul emp.

7. Ştergeţi tabelul EMPLOYEE. 8. Redenumiţi tabelul EMPLOYEE2 în EMPLOYEE. 9. Adăugaţi comentarii la definiţiile celor 2 tabele, descriind semnificaţia tabelului respectiv.

Vizualizaţi modificările din dicţionarul de date. 10. Adăugati o constrângere de tip PRIMARY KEY la tabelul EMPLOYEE folosind coloana ID.

Constrângerea trebuie să fie activată la creare. 11. Creaţi o constrângere PRIMARY KEY în tabelul DEPARTMENT folosind coloana ID.

Constrângerea trebuie să fie activată la creare. 12. Adăugaţi o referinţă de tip cheie externă la tabelul EMPLOYEE care va asigura că angajaţii nu

sunt asignaţi la un departament inexistent. 13. Confirmaţi constrângerile adăugate interogând tabelul USER_CONSTRAINTS. Observaţi

numele şi tipul constrângerilor.

CONSTRAINT_NAME C ---------------------- - DEPARTEMENT_ID_PK P EMPLOYEE_ID_PK P EMPLOYEE_DEPT_ID_FK R

14. Afişaţi numele şi tipul obiectelor din vederea USER_OBJECTS pentru tabelele EMPLOYEE şi DEPARTMENT.

OBJECT_NAME OBJECT_TYPE ----------------- ----------- DEPARTMENT TABLE DEPARTMENT_ID_PK INDEX EMPLOYEE TABLE EMPLOYEE_ID_PK INDEX

1

CREAREA ŞI UTILIZAREA VEDERILOR. UTILIZAREA OBIECTELOR DE TIP SECVENŢĂ PENTRU GENERARE AUTOMATĂ DE VALORI

Obiective

O vedere este un tabel logic bazat pe un alt tabel sau pe o altă vedere şi nu conţine date proprii. Tabelele pe baza cărora sunt create vederile se numesc tabele de bază. La nivelul bazei de date o vedere este stocată sub forma unei instrucţiuni SELECT în dicţionarul de date. Prin parcurgerea acestui referat studentul va dobândi cunoştinţele necesare pentru:

- descrierea unei vederi; - crearea unui obiect de tip vedere; - extragerea datelor dintr-o vedere; - modificarea structurii unei vederi; - inserarea, actualizarea şi ştergerea datelor dintr-o vedere; - ştergerea unei vederi; - crearea şi utilizarea generatoarelor de secvenţe. Avantajele utilizării vederilor

- restricţionează accesul la baza de date deoarece o vedere pune la dispoziţia utilizatorilor doar o parte a datelor din baza de date;

- permit utilizatorilor să apeleze la interogări simple pentru a obţine rezultatele unor interogări complexe. De exemplu, vederile permit utilizatorilor să extragă informaţii din mai multe tabele fără a cunoaşte modul de realizare a joncţiunilor..

- asigură independenţa datelor pentru utilizatori şi aplicaţii. O singură vedere poate fi utilizată pentru a obţine date din mai multe tabele;

- furnizează grupurilor de utilizatori accesul la date în conformitate cu anumite criterii, stabilite de administratorul bazei de date.

Vederile se clasifică în două grupe: simple şi complexe. Diferenţa de bază între cele două grupe este legată de operaţiile LMD (inserare, actualizare şi ştergere).

Caracteristici Vederi simple Vederi complexe Număr de tabele 1 1 sau mai multe Conţine funcţii Nu Da Conţine grupuri de date Nu Da Operaţii LMD Da Nu întotdeauna

O vedere simplă este o vedere care:

- furnizează date dintr-un singur tabel; - nu conţine funcţii sau grupuri de date; - permite execuţia unor operaţii LMD. O vedere complexă este o vedere care :

- extrage date din mai multe tabele; - conţine funcţii sau grupuri de date; - nu permite întotdeauna operaţii LMD.

2

Crearea unei vederi Instrucţiunea ce permite crearea unei vederi este CREATE VIEW, având următoarea sintaxă.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW nume_vedere [(alias[, alias...])] AS subinterogare [WITH CHECK OPTION [CONSTRAINT nume_constr]] [WITH READ ONLY]

unde:

OR REPLACE - recreează vederea dacă aceasta există deja FORCE - creează vederea indiferent daca există sau nu tabelele

de bază NOFORCE - creează vederea numai dacă există tabelele de bază;

este opţiunea implicită nume_vedere - este numele vederii alias - specifică noul nume pentru o coloană a vederii;

numărul de alias-uri trebuie să corespundă numărului de expresii selectate de vedere

subinterogare - este o instrucţiune SELECT; se pot utiliza alias-uri pentru coloanele din clauza SELECT

WITH CHECK OPTION - specifică faptul că pot fi actualizate doar liniile accesibile vederii

nume_constr - este numele constrângerii WITH READ ONLY - interzice execuţia unei instrucţiuni LMD pe vedere.

Pentru a crea o vedere numită EMPVU10 care conţine detalii despre angajaţii din departamentul 10 se va executa următoarea instrucţiune: CREATE VIEW empvu10 AS SELECT empno, ename, job FROM emp WHERE deptno=10;

Observaţii:

- subinterogarea care defineşte o vedere poate conţine o instrucţiune SELECT complexă (inclusiv joncţiuni, grupuri şi subinterogări);

- subinterogarea care defineşte o vedere nu poate conţine clauza ORDER BY. Această clauză va fi specificată în momentul interogării vederii;

- opţiunea OR REPLACE poate fi utilizată pentru a putea modifica vederea fără a o şterge şi recrea.

Redenumirea coloanelor ce apar în vedere se poate face în două moduri:

1. se includ alias-urilor de coloană în subinterogare: CREATE VIEW salvu30 AS SELECT empno EMPLOYEE_NUMBER, ename NAME, sal SALARY FROM emp WHERE deptno=30;

3

Exemplul anterior creează o vedere care conţine numărul, numele şi salariul angajaţilor din departamentul 30. Coloanele acestei vederi au numele EMPLOYEE_NUMBER, NAME şi SALARY.

2. se includ alias-uri de coloană în clauza CREATE VIEW. CREATE VIEW salvu30 (EMPLOYEE_NUMBER, NAME, SALARY) AS SELECT empno, ename, sal FROM emp WHERE deptno=30;

Interogarea vederilor

Extragerea datelor din vederi se face în acelaşi mod ca în cazul tabelelor. SQL> SELECT *

2 FROM salvu30;

EMPLOYEE_NUMBER NAME SALARY ---------------- ------- ------- 7698 BLAKE 2850 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950 7521 WARD 1250

Odată creată vederea, se poate interoga tabelul USER_VIEWS din dicţionarul de date pentru a obţine numele şi definiţia vederii. Textul instrucţiunii SELECT care implementează vederea este memorat într-o coloană de tip LONG (şir de caractere). Accesarea datelor din baza de date corespunzătoare unei vederi presupune execuţia următoarelor operaţii de către serverul Oracle:

1. extrage definiţia vederii din dicţionarul de date (mai exact din tabelul USER_VIEWS); 2. verifică privilegiile de acces pentru tabelele de bază; 3. converteşte interogarea vederii într-o operaţie echivalentă asupra tabelului (tabelelor) de

bază. Cu alte cuvinte, datele sunt extrase din, sau actualizate în tabelul (tabelele) de bază. Modificarea unei vederi

Opţiunea OR REPLACE permite crearea unei vederi, chiar dacă există deja o vedere cu acelaşi nume, înlocuindu-se astfel vechea versiune a vederii cu cea nouă. Aceasta înseamnă că o vedere poate fi modificată fără a fi necesară ştergerea şi recrearea ei.

Următorul exemplu modifică vederea EMPVU10 utilizând clauza CREATE OR REPLACE VIEW. Suplimentar se adaugă un alias pentru fiecare coloană. SQL> CREATE OR REPLACE VIEW empvu10 2 (employee_number, employee_name, job_title) 3 AS SELECT empno, ename, job 4 FROM emp 5 WHERE deptno=10;

4

Notă: Când se furnizează nume alternative (alias-uri) coloanelor în clauza CREATE VIEW, alias-urile trebuie listate în aceeaşi ordine ca şi coloanele din subinterogare. Crearea unei vederi complexe

SQL> CREATE VIEW dept_sum_vu 2 (name, minsal, maxsal, avgsal) 3 AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal) 4 FROM emp e, dept d 5 WHERE e.deptno=d.deptno 6 GROUP BY d.dname;

Exemplul anterior creează o vedere complexă care conţine numele, salariul minim, salariul maxim şi salariul mediu pentru fiecare departament. De notat că au fost specificate alias-uri pentru vedere. Acest lucru este necesar dacă una din coloanele vederii rezultă din evaluarea unei funcţii sau expresii. Reguli pentru executarea operaţiilor LMD asupra unei vederi Pentru a putea executa operaţii LMD asupra datelor din baza de date prin intermediul vederii trebuie avute în vedere o serie de reguli. 1. Nu se poate şterge o linie aparţinând unei vederi dacă definiţia vederii conţine : - funcţii grup; - clauză GROUP BY; - cuvântul cheie DISTINCT. 2. Nu se pot modifica datele dintr-o vedere dacă vederea conţine: - oricare din elementele de la punctul 1; - coloane definite prin expresii (de exemplu, SALARY*12); - pseudocoloana ROWNUM. 3. Nu pot fi inserate date într-o vedere dacă: - vederea conţine oricare din elementele de la punctul 1 şi 2; - există coloane NOT NULL fără valoare implicită în tabelul de bază şi care nu au fost

selectate în vedere. Utilizarea clauzei WITH CHECK OPTION Există posibilitatea ca, prin intermediul vederilor, să se efectueze verificări de integritate referenţială asupra datelor. Se pot, de asemenea, forţa constrângeri la nivelul bazei de date. O vedere poate fi utilizată pentru a asigura integritatea datelor, dar într-o manieră destul de limitată. Clauza WITH CHECK OPTION specifică faptul că instrucţiunile INSERT şi UPDATE executate asupra vederii nu pot crea linii noi pe care vederea nu le poate selecta. Prin urmare, vederile pot forţa verificări asupra datelor ce vor fi inserate sau actualizate. Dacă se încearcă execuţia unei operaţii LMD pe linii care nu au fost selectate de vedere se va afişa un mesaj de eroare, împreună cu numele constrângerii (dacă acesta a fost specificat).

5

Pentru exemplificare să considerăm vederea SQL> CREATE OR REPLACE VIEW empvu20 2 AS SELECT * 3 FROM emp 4 WHERE deptno=20 5 WITH CHECK OPTION CONSTRAINT empvu20_ck;

Orice încercare de a modifica numărul departamentului pentru orice linie din vedere va eşua deoarece nu respectă constrângerea WITH CHECK OPTION. SQL> UPDATE empvu20 2 SET deptno=10 3 WHERE empno=7788;

update empvu20 * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

Nici o linie nu este actualizată pentru că, dacă numărul departamentului ar deveni 10, vederea nu ar mai putea selecta acel angajat. Datorită faptului că vederea poate “vedea” doar angajaţii din departamentul 20, utilizarea clauzei WITH CHECK OPTION nu permite ca numărul departamentului pentru aceşti angajaţi sa fie schimbat prin intermediul vederii. Interzicerea operaţiilor LMD

Dacă se doreşte ca asupra vederii să nu poată fi executată nici o operaţie LMD, la crearea vederii trebuie specificată opţiunea WITH READ ONLY. SQL> CREATE OR REPLACE VIEW empvu10 2 (employee_number, employee_name, job_title) 3 AS SELECT empno, ename, job 4 FROM emp 5 WHERE deptno=10 6 WITH READ ONLY;

Exemplul de mai sus modifică vederea EMPVU10 pentru a împiedica orice operaţie LMD asupra vederii. Orice încercare de ştergere a unei linii din vedere va genera o eroare. SQL> DELETE FROM empvu10 2 WHERE employee_number=7782; DELETE FROM empvu10 * ERROR at line 1: ORA_01752: Cannot delete from view without exactly one key-preserved table

Ştergerea definiţiei unei vederi

6

Pentru a şterge o vedere se va utiliza instrucţiunea: DROP VIEW nume_vedere;

Exemplu: DROP VIEW empvu10;

Această instrucţiune elimină definiţia vederii din baza de date. Ştergerea vederilor nu are nici un efect asupra tabelelor pe care se bazează vederea. Vederile sau aplicaţiile bazate pe o vedere ştearsă devin invalide. Ştergerea unei vederi poate fi efectuată fie de creatorul vederii fie de un utilizator ce deţine privilegiul DROP ANY VIEW. Obiecte de tip secvenţă Multe aplicaţii necesită folosirea de valori distincte pentru o cheie primară. Pentru a îndeplini această cerinţă fie se scriu linii de cod, fie se recurge la utilizarea obiectelor de tip secvenţă pentru a genera automat aceste numere distincte. O secvenţă este un obiect creat de un utilizator şi poate fi folosit de mai mulţi utilizatori. Secvenţa este generată şi incrementată (sau decrementată) de o rutină internă Oracle. Numerele secvenţei sunt stocate şi generate independent de tabel. Prin urmare, aceeaşi secvenţă poate fi folosită în mai multe tabele. Crearea unui obiect de tip secvenţă

Instrucţiunea CREATE SEQUENCE defineşte o secvenţă ce va genera automat numere secvenţiale. Sintaxa acestei instrucţiuni este următoarea: CREATE SEQUENCE secventa [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];

unde:

secventa este numele generatorului de secvenţă;

INCREMENT BY n specifică intervalul dintre două numere consecutive ale secvenţei, unde n este un întreg. Dacă această clauza lipseşte, n este implicit 1;

START WITH n specifică primul număr al secvenţei. Dacă această clauză lipseşte, implicit n = 1;

MAXVALUE n specifică valoarea maximă pe care secvenţa o poate genera;

NOMAXVALUE specifică o valoare maximă de 1027 pentru o secvenţă crescătoare şi –1 pentru o secvenţă descrescătoare. Aceste valori sunt implicite;

7

MINVALUE n specifică valoarea minimă a secvenţei;

NOMINVALUE specifică o valoare minimă de 1 pentru o secvenţă crescătoare şi –(1027) pentru una descrescătoare. Aceste valori sunt implicite;

CYCLE | NOCYCLE specifică dacă secvenţa continuă să genereze valori după ce a fost atinsă valoarea maximă / minimă, sau dacă nu mai generează nici o valoare. Opţiunea NOCYCLE este implicită;

CACHE n | NOCACHE specifică serverului Oracle pentru câte valori să aloce spaţiu în memorie. Implicit n are valoarea 20.

Exemplul următor creează secvenţa DEPT_DEPTNO, folosită pentru coloana DEPTNO din tabelul DEPT. Secvenţa începe cu valoarea 91, nu permite alocare de memorie şi nu permite cicluri de generare. SQL> CREATE SEQUENCE dept_deptno 2 INCREMENT BY 1 3 START WITH 91 4 MAXVALUE 100 5 NOCACHE 6 NOCYCLE; Sequence created.

Notă: Nu se recomandă folosirea clauzei CYCLE atunci când secvenţa este folosită pentru a genera valorile unei chei primare, excepţie făcând cazul când se dispune de un mecanism sigur de îndepărtare a vechilor înregistrări şi mai rapid decât ciclurile secvenţei. Confirmarea existenţei obiectelor de tip secvenţă

Odată creată o secvenţă, ea este înscrisă în dicţionarul de date şi poate fi identificată cu ajutorul tabelului USER_OBJECTS. De asemenea, se pot confirma setările unei secvenţe prin interogarea tabelului USER_SEQUENCES. SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number 3 FROM user_sequences;

• Coloana LAST_NUMBER conţine următorul număr disponibil al secvenţei. SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER -------------- ---------- ---------- ------------ ----------- CUSTID 1 1.000E+27 1 109 DEPT_DEPTNO 1 100 1 91 ORDID 1 1.000E+27 1 622 PRODID 1 1.000E+27 1 200381

Pseudocoloanele NEXTVAL şi CURRVAL

Odată creată secvenţa, ea poate fi folosită pentru a genera numere ce vor fi utilizate ulterior în tabele. Referirea următoarei valori din secvenţă şi a valorii curente se face cu ajutorul pseudocoloanelor NEXTVAL şi CURRVAL.

8

Pseudocoloana NEXTVAL este folosită pentru a extrage numere succesive dintr-o anumită secvenţă. Referirea la NEXTVAL se face împreună cu numele secvenţei. Atunci când se face referire la secventa.NEXTVAL se generează un nou număr al secvenţei, ce va fi memorat în CURRVAL. Pseudocoloana CURRVAL este folosită pentru a referi numărul unei secvenţe ce tocmai a fost generat. Înainte de a se face referire la CURRVAL trebuie utilizat NEXTVAL pentru a genera un nou număr. Referirea lui CURRVAL se face împreună cu numele secvenţei. Când sequence.CURRVAL este referită, va fi afişată ultima valoare generată de secvenţă. Reguli pentru utilizarea pseudocoloanelor NEXTVAL şi CURRVAL Folosirea pseudocoloanelor NEXTVAL şi CURRVAL este permisă în următoarele situaţii:

• în clauza SELECT a unei instrucţiuni SELECT ce nu face parte dintr-o subinterogare; • în clauza SELECT a unei subinterogări dintr-o instrucţiune INSERT; • în clauza VALUES a unei instrucţiuni INSERT; • în clauza SET a unei instrucţiuni UPDATE. Nu este permisă utilizarea pseudocoloanelor NEXTVAL şi CURRVAL în următoarele situaţii:

• în clauza SELECT a unei vederi; • într-o instrucţiune SELECT ce conţine cuvântul cheie DISTINCT; • într-o instrucţiune SELECT ce conţine clauzele GROUP BY, HAVING sau ORDER BY; • într-o subinterogare din instrucţiuni SELECT, DELETE sau UPDATE; • într-o expresie DEFAULT în instrucţiuni CREATE TABLE sau ALTER TABLE. Utilizarea unei secvenţe

Exemplul următor inserează un nou departament în tabelul DEPT. Se utilizează secvenţa DEPT_DEPTNO pentru generarea unui nou număr pentru departament. SQL> INSERT INTO dept(deptno, dname, loc) 2 VALUES(dept_deptno.NEXTVAL, 3 ‘MARKETING’, ‘SAN DIEGO’);

1 row created. Pentru vizualizarea valorii curente pentru secvenţa DEPT_DEPTNO se va executa următoarea instrucţiune: SQL> SELECT dept_deptno.CURRVAL 2 FROM dual;

CURRVAL -------- 91

Stocarea secvenţelor în memorie permite o accesare mai rapidă a valorilor secvenţei, stocarea realizându-se la prima referire a secvenţei. După ce a fost utilizată ultima valoare din secvenţă, următoarea cerere de generare determină o altă stocare a secvenţei în memorie.

9

Deşi generatorul de secvenţe furnizează numere consecutive fără goluri, această acţiune se petrece independent de un commit sau un rollback. De aceea, dacă se execută un rollback pe o instrucţiune ce utilizează o valoare dintr-o secvenţă, acea valoare este pierdută. Un alt eveniment ce poate provoca goluri în secvenţă este o cădere a sistemului. Dacă valorile secvenţei sunt stocate în memorie, acestea se pierd în cazul unei căderi de sistem. Este posibilă vizualizarea următoarei valori disponibile din secvenţă fără a o incrementa, doar dacă secvenţa a fost creată cu opţiunea NOCACHE, prin interogarea tabelului USER_SEQUENCES. Modificarea unei secvenţe

Dacă se atinge valoarea MAXVALUE într-o secvenţă definită cu opţiunea NOCYCLE nu va mai fi atribuită nici o valoare suplimentară şi se va genera un mesaj de eroare ce indică faptul că secvenţa a atins valoarea maximă. Pentru a putea folosi în continuare această secvenţă, va trebui să modificăm definiţia secvenţei cu ajutorul instrucţiunii ALTER SEQUENCE. ALTER SEQUENCE nume_secv [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];

unde: nume_secv este numele generatorului de secvenţe

Exemplul următor modifică valoarea incrementului, valoarea maximă şi minimă, opţiunea de ciclare şi de stocare pentru secvenţa DEPT_DEPTNO. SQL> ALTER SEQUENCE dept_deptno 2 INCREMENT BY 1 3 MAXVALUE 999999 4 NOCACHE 5 NOCYCLE; Sequence altered.

Pentru verificare se vor afişa informaţiile din vederea USER_SEQUENCES.

Observaţii: • o secvenţă poate fi modificată doar de utilizatorul care a creat secvenţa sau de un

utilizator ce deţine privilegiul ALTER pentru secvenţa în cauză; • sunt afectate doar numerele ce urmează să fie generate; • secvenţa trebuie distrusă şi recreată pentru a începe secvenţa cu un alt număr (specificat

în clauza START WITH). Ştergerea unei secvenţe

Pentru ştergerea unei secvenţe din dicţionarul de date se utilizează instrucţiunea DROP SEQUENCE. Această operaţie poate fi realizată doar de proprietarul secvenţei sau de utilizatorul cu privilegiul DROP ANY SEQUENCE.

10

DROP SEQUENCE nume_secv;

Exemplu: SQL> DROP SEQUENCE dept_deptno;

Sequence dropped.

Probleme

1. Creaţi o vedere EMP_VU care să conţină numărul, numele şi departamentul angajaţilor din tabelul emp. Redenumiţi coloana corespunzătoare numelui EMPLOYEE. Afişaţi conţinutul vederii EMP_VU.

2. Vizualizaţi informaţiile despre vederea creată la problema 1 din vederea USER_VIEWS (coloanele view_name şi text).

3. Folosind vederea EMP_VU scrieţi o interogare pentru a afişa numele tuturor angajaţilor, împreună cu departamentele în care lucrează.

4. Creaţi vederea DEPT20 care să conţină următoarele informaţii despre angajaţii ce lucrează în departamentul 20: număr angajat, nume angajat şi număr departament. Etichetaţi coloanele vederii astfel: EMPLOYEE_ID, EMPLOYEE şi DEPARTMENT_NO. Nu permiteţi ca unui angajat să i se poată modifica numărul departamentului prin intermediul vederii.

5. Afişaţi structura şi conţinutul vederii DEPT20.

6. Încercaţi să înlocuiţi numărul departamentului pentru angajatul Smith cu valoarea 30. Explicaţi cauza ce a condus la generarea erorii semnalată de server.

7. Creaţi vederea SALARY_VU care să conţină numele angajatului, numele departamentului, salariul şi gradul de salarizare pentru toţi angajaţii din emp. Etichetaţi coloanele astfel: Employee, Department, Salary şi Grade.

8. Să se creeze o secvenţă ce va fi folosită împreună cu cheia primară a tabelului DEPARTMENT. Secvenţa trebuie să înceapă cu 60 şi să aibă o valoare maximă de 200. Incrementul secvenţei este de 10. Numele secvenţei va fi DEPT_ID_SEQ.

9. Să se afişeze următoarele informaţii despre secvenţa de la problema 8: nume, valoarea maximă, mărimea incrementului şi ultimul număr creat.

10. Să se realizeze un program interactiv pentru a insera o înregistrare în tabelul DEPARTMENT. Pentru introducerea valorilor în coloana ID se va utiliza secvenţa creată la problema 8. Se va cere introducerea de la tastatură a numelui departamentului. Să se adauge două departamente numite ‘Education’ şi ‘Administration’. Să se confirme adăugarea celor două înregistrări.

Structura tabelului DEPARTMENT este următoarea:

Column Name ID NAME Nulls/Unique Datatype Number(7) Varchar2(25)

Limbajul PL/SQL PL/SQL (Procedural Language/SQL) este extensia procedurală a limbajului SQL dezvoltată de Oracle Corporation, ce permite includerea instrucţiunilor de manipulare şi interogare a datelor în unităţi de cod procedurale şi structurate pe bloc. PL/SQL este o tehnologie adoptată de server-ul Oracle şi de unele produse Oracle (eg. Developer/2000 Forms). Blocurile PL/SQL sunt trimise motorului PL/SQL şi executate de acesta, motor ce se poate afla fie în cadrul aplicaţiei din care s-a executat blocul, fie pe serverul Oracle. PL/SQL poate fi utilizat pentru gruparea mai multor instrucţiuni într-un singur bloc şi trimiterea întregului bloc la server printr-un singur apel. Fără PL/SQL, instrucţiunile SQL ar fi procesate una câte una, fiecare instrucţiune generând un apel către serverul Oracle.

Structura orientată pe blocuri a PL/SQL

În PL/SQL programele pot fi divizate în blocuri logice, principalele tipuri de blocuri ce pot fi utilizate fiind:

- blocuri anonime - bloc nedenumit şi care nu formează corpul unei proceduri, funcţii sau declanşator (trigger);

- blocuri de tip funcţie şi procedură – similare funcţiilor şi procedurilor din alte limbaje de programare şi sunt definite ca blocuri PL/SQL;

- blocuri declanşatoare (trigger) – blocuri PL/SQL ce definesc codul ce urmează a fi executat atunci când au loc anumite acţiuni sau evenimente la nivelul bazei de date;

- blocuri imbricate – utilizate pentru tratarea erorilor şi pentru creşterea modularităţii codului scris.

Blocuri anonime

Un bloc anonim PL/SQL este format din trei secţiuni: - secţiunea declarativă (opţională) conţine toate variabilele, constantele, cursoarele şi excepţiile

utilizator ce sunt referite în secţiunile executabile şi declarative; - secţiunea executabilă (obligatorie) conţine instrucţiuni SQL pentru manipularea datelor din baza

de date şi instrucţiuni PL/SQL pentru manipularea datelor din cadrul blocului; - secţiunea de tratare a excepţiilor (opţională) specifică acţiunile ce vor fi executate în momentul

apariţiei unor erori sau condiţii anormale în secţiunea executabilă. Structura unui bloc PL/SQL este următoarea: DECLARE – Opţional

- variabile, cursori, excepţii utilizator BEGIN – Obligatoriu - instrucţiuni SQL - instrucţiuni PL/SQL EXCEPTIONS - Opţional - acţiuni executate în caz de eroare END; - Obligatoriu

Pentru execuţia blocurilor PL/SQL în mediul gazdă SQL*PLUS se va ţine cont de următoarele reguli: - se va plasa caracterul ‘;’ la sfârşitul unei instrucţiuni SQL sau a unei instrucţiuni de control

PL/SQL; - se va plasa caracterul ‘/’ pentru rularea blocurilor anonime din buffer-ul SQL. Execuţia cu

succes a unui astfel de bloc va fi anunţată prin mesajul

PL/SQL procedure successfully completed

- se va plasa ‘.’ pentru a închide buffer-ul SQL (un bloc PL/SQL este tratat ca o singură instrucţiune şi caracterele ‘;’ plasate în interiorul blocului nu vor închide sau rula buffer-ul).

Variabile PL/SQL

Variabilele PL/SQL trebuie declarate în secţiunea declarativă, înainte de utilizarea lor într-un bloc PL/SQL. Sintaxa ce permite declararea unei variabile este următoarea:

identificator [CONSTANT] tip_date [NOT NULL] [:= | DEFAULT expr]

unde: identificator este numele variabilei;

CONSTANT utilizat în cazul în care identificator este o constantă; constantele trebuie iniţializate;

tip_date tipul de date al variabilei (scalar, compus, referinţă sau LOB); NOT NULL constrânge variabila să conţină valori diferite de NULL; variabilele

NOT NULL trebuie iniţializate la declarare; expr o expresie PL/SQL care poate fi un literal, o altă variabilă sau o

expresie ce implică operatori şi funcţii. PL/SQL suportă patru categorii de tipuri de date:

1. scalar – tipurile de date din această categorie permit stocarea unei singure valori; în această categorie intră tipurile de date utilizate pentru coloanele de tabele;

2. compus – permit definirea şi manipularea grupurilor de câmpuri în blocuri PL/SQL; această categorie conţine tipurile de date (numite şi colecţii) TABLE, RECORD, VARRAY;

3. referinţă – tipurile din această categorie sunt utilizate pentru memorarea pointerilor către alte elemente ale unui program;

4. LOB – stochează informaţii ce indică locaţia obiectelor de dimensiuni mari (imagini grafice, video-clipuri).

Exemplul următor indică modul de declarare a unor variabile:

DECLARE v_job varchar2(9); v_count number(9,2) := 0; c_tax_rate CONSTANT number(3,2) := 8.25; v_valid boolean NOT NULL := TRUE; g_mgr number(4) DEFAULT 7839; BEGIN ...

Regulile urmărite la declararea unei variabile sunt următoarele: - dacă se doreşte iniţializarea unei variabile cu o expresie, în momentul declarării se va utiliza

operatorul de asignare ‘:=’ sau se va specifica cuvântul cheie DEFAULT. Dacă variabila nu se iniţializează la declarare ea va conţine valoarea NULL până în momentul iniţializării.

- dacă se utilizează constrângerea NOT NULL, variabila TREBUIE iniţializată. - declararea unei singure variabile pe linie ajută la citirea şi întreţinerea cu mai multă uşurinţă a

codului. - pentru declararea constantelor trebuie utilizat identificatorul CONSTANT înaintea tipului de date.

O constantă trebuie iniţializată la declarare, în caz contrar generându-se un mesaj de eroare de compilare.

Pentru asignarea de valori unei variabile se va utiliza sintaxa

identificator := expr;

unde: identificator este numele variabilei

expr poate fi o variabilă, un literal sau un apel de funcţie, dar nu un nume de coloană

Un literal este o valoare explicită de tip numeric, caracter, şir de caractere sau boolean (exemple de literali: numărul 25.36, şirul ‘Test’, valoarea TRUE). Dacă se declară o variabilă pentru a fi utilizată cu scopul de a memora valorile unei coloane, tipul de date al variabilei trebuie ales în conformitate cu domeniul de definiţie al coloanei. Pentru aceasta se poate utiliza atributul %TYPE, care permite declararea unei variabile pe baza tipului unei alte variabile sau al unei coloane. %TYPE se utilizează, cel mai adesea, atunci când se declară o variabilă ce va fi folosită pentru stocarea valorilor din coloana unui tabel sau pentru înscrierea datelor într-o coloană. Acest atribut înlocuieşte tipul de date al variabilei şi va fi prefixat de numele tabelului şi al coloanei în cauză sau cu numele variabilei.

… v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10; …

În exemplul de mai sus, tipul variabilei v_ename va fi dat de domeniul de definiţie al coloanei ename din tabelul emp. Tipurile de date scalare ce pot fi utilizate la declararea unei variabile sunt prezentate în anexa 1.

Variabilele booleene (declarate cu tipul BOOLEAN) sunt variabile ce pot avea valorile TRUE, FALSE sau NULL. Astfel de variabile pot fi unite prin operatorii logici AND, OR sau NOT. Expresiile booleene sunt formate din expresii simple sau complexe, separate de operatori logici (e.g. clauza WHERE a unei instrucţiuni SELECT conţine o expresie booleană ce specifică liniile dintr-un tabel afectate de instrucţiune). De exemplu, dacă

v_sal1 := 5000; v_sal2 := 6000;

atunci expresia v_sal1 < v_sal2 va returna TRUE. v_comm_sal BOOLEAN := (v_sal1 < v_sal2); reprezintă declaraţia şi iniţializarea unei variabile booleene. O variabilă de legătură este o variabilă declarată într-un mediu gazdă (de exemplu SQL*Plus) şi utilizată în momentul execuţiei pentru a pasa valori spre, respectiv pentru a primi valori din unul sau mai multe programe PL/SQL. O variabilă de legătură poate fi tratată în aceeaşi manieră ca variabilele obişnuite. Pentru a declara o variabilă de legătură în mediul SQL*Plus se va utiliza comanda VARIABLE.

SQL> VARIABLE return_code NUMBER Atât SQL cât şi SQL*Plus pot referi variabilele de legătură, iar SQL*Plus poate afişa şi valoarea lor prin intermediul comenzii PRINT:

SQL> PRINT return_code O altă modalitate de a afişa valoarea unei variabile de legătură constă în utilizarea funcţiei PUT_LINE din pachetul DBMS_OUTPUT. Înainte de utilizarea acestei funcţii trebuie executată comanda SET SERVEROUTPUT ON (o singură dată pe sesiune) la promptul SQL*Plus, în caz contrar nu se va afişa nimic pe ecran. DECLARE y number := 3; BEGIN :x := y; DBMS_OUTPUT.PUT_LINE('Valoarea variabilei x este ' || :x); END; / Valoarea variabilei x este 3 PL/SQL procedure successfully completed Pentru a face referire la o variabilă de legătură, numele variabilei trebuie prefixat de ‘:’. În acest fel se va face distincţie între aceste variabile şi cele PL/SQL:

:host_sal := v_sal; :global_var1 := ‘TRUE’;

Deoarece PL/SQL este o extensie a limbajului SQL, regulile generale de sintaxă ce se aplică în SQL sunt valabile şi în PL/SQL: - unităţile lexicale (identificatori sau literali) pot fi separate de unul sau mai multe spaţii sau de

alţi delimitatori. Spaţiile nu pot fi incluse în unităţile lexicale cu excepţia literalilor sau comentariilor;

- instrucţiunile pot fi despărţite pe mai multe linii; cuvintele cheie nu pot fi despărţite; Delimitatorii utilizaţi în PL/SQL sunt:

Simbol Descriere Simbol Descriere + operatorul aditiv <> operator relaţional - operatorul de diferenţă sau negaţie != operator relaţional * operatorul multiplicativ || operatorul de concatenare / operatorul de diviziune -- indicator de comentariu pe o linie = operator relaţional /* delimitator început comentariu @ indicator de acces la distanţă */ delimitator sfârşit comentariu ; terminator de instrucţiune := operatorul de atribuire Vizibilitatea identificatorilor în PL/SQL Unul din avantajele PL/SQL în comparaţie cu SQL constă în abilitatea de a imbrica instrucţiuni. Se pot imbrica blocuri oriunde este permisă utilizarea instrucţiunilor executabile. Secţiunea excepţiilor (erorilor) poate conţine, de asemenea, blocuri imbricate.

Zona de vizibilitate a unui obiect este regiunea din program ce poate face referire la acel obiect. Variabilele declarate pot fi referite în cadrul secţiunii de execuţie.

Un identificator este vizibil doar în blocul în care este declarat şi în toate sub-blocurile, procedurile şi funcţiile imbricate. Dacă un bloc nu găseşte identificatorul declarat local, va căuta în secţiunea declarativă a blocurilor părinte. Nu se caută declaraţia unui identificator în sub-blocuri sau în blocurile de pe acelaşi nivel. …

x REAL; BEGIN

… DECLARE

y NUMBER; BEGIN

… END; …

END; În exemplul de mai sus, variabila y poate face referire la variabila x, în timp ce variabila x nu poate referi variabila y.

Funcţii PL/SQL

Aproape toate funcţii ce pot fi utilizate în SQL sunt valide în PL/SQL, cu excepţia funcţiilor grup (MAX, MIN, AVG, SUM, COUNT) şi a altor funcţii speciale, cum ar fi DECODE şi DUMP. În Anexa 2 sunt prezentate câteva din funcţiile ce pot fi utilizate în PL/SQL. În Anexa 3 sunt prezentate şabloanele ce pot fi utilizate cu funcţiile TO_DATE, TO_CHAR şi TO_NUMBER. Exemple: SELECT TO_CHAR(SYSDATE, ‘MONTH DDTH YYYY’) “Azi” FROM DUAL

Azi ------------------ NOVEMBER 28TH 2006

zona de vizibilitate pentru y

zona de vizibilitate pentru x

DECLARE v_convert_date DATE := TO_DATE(‘0611-2067’, ‘MMDDSYYYY’); v_hold_date VARCHAR2(100); BEGIN v_hold_date := TO_CHAR(v_convert_date, ‘MMDDSYYYY’); DBMS_OUTPUT.PUT_LINE(‘Data convertita este: ‘ || v_hold_date); END; /

Data convertita este: 0611-2067

DECLARE v_convert_date DATE; BEGIN V_convert_date:= TO_DATE(‘061167’, ‘DDMMYY’); DBMS_OUTPUT.PUT_LINE(‘Data convertita este: ‘ || v_convert_date); END; /

Data convertita este: 06-11-2067 DECLARE v_convert_number VARCHAR2(20) := ‘1997’; v_hold_number NUMBER; BEGIN V_hold_number:= TO_NUMBER(v_convert_number, ‘9999’); DBMS_OUTPUT.PUT_LINE(‘Nr convertit este:’ || v_hold_number); DBMS_OUTPUT.PUT_LINE(‘Nr convertit plus 10 este:’||(v_hold_number+10)); END; /

Nr convertit este: 1997 Nr convertit plus 10 este: 2007 Operatori PL/SQL Operatorii utilizaţi în PL/SQL corespund celor utilizaţi în SQL:

Operator Descriere **, NOT operatorii exponenţial şi negaţie logică +, - operatori de semn (+ şi -) *, / multiplicare, diviziune +, -, || adunare, scădere, concatenare =, !=, <>, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN

operatori de comparaţie

AND Conjuncţie OR Disjuncţie

Precedenţa operatorilor corespunde săgeţii desenate în stânga tabelului.

Instrucţiuni SQL în PL/SQL PL/SQL suportă toate instrucţiunile din Limbajul de Manipulare a Datelor (LMD) şi comenzile de control a tranzacţiilor din SQL. O instrucţiune SQL poate fi utilizată pentru a popula variabile cu valori rezultate din interogarea unui tabel.

Un bloc PL/SQL nu este o unitate de tranzacţie. Salvarea datelor (COMMIT), setarea unor puncte de salvare (SAVEPOINT) şi revenirea la un punct de salvare sau la ultimul COMMIT (ROLLBACK) sunt operaţii independente de bloc, dar aceste instrucţiuni se pot executa în interiorul unui bloc. PL/SQL nu suportă instrucţiunile din Limbajul de Definire a Datelor (LDD), cum ar fi CREATE TABLE, ALTER TABLE sau DROP TABLE. De asemenea, PL/SQL nu suportă instrucţiunile din Limbajul de Control a Datelor (GRANT, REVOKE). Sintaxa PL/SQL pentru extragerea unor informaţii din baza de date şi stocarea lor în variabile PL/SQL este:

SELECT lista_tinta INTO {nume_var [, nume_var] ... | nume_inreg} FROM tabel WHERE conditie;

unde: lista_tinta este o listă formată din cel puţin o coloană şi poate include expresii SQL şi funcţii

agregat; nume_var este variabilă scalară ce va memora valoarea returnată nume_inreg este variabila de tip RECORD ce va păstra valorile returnate tabel indică numele tabelului conditie este compusă din nume de coloane, expresii, constante şi operatori de comparaţie,

inclusiv variabile şi constante PL/SQL Exemplu: DECLARE

v_deptno NUMBER(2); v_loc VARCHAR2(15);

BEGIN SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname=’SALES’; DBMS_OUTPUT.PUT_LINE(‘Valoarea v_deptno este‘ || TO_CHAR(v_deptno)); DBMS_OUTPUT.PUT_LINE(‘Valoarea v_loc este‘ || v_loc);

END; /

Valoarea v_deptno este 30 Valoarea v_loc este CHICAGO Clauza INTO este OBLIGATORIE şi este poziţionată între clauzele SELECT şi FROM. Această clauză este utilizată pentru specificarea variabilelor ce vor memora valorile returnate de clauza SELECT. Se furnizează câte o variabilă pentru fiecare element din clauza SELECT, ordinea variabilelor trebuind să corespundă ordinii din lista clauzei SELECT.

Utilizarea unei instrucţiuni SQL într-un bloc PL/SQL trebuie să returneze numai o singură linie. În caz contrar se va genera un mesaj de eroare.

Observaţii: - instrucţiunile SQL trebuie terminate cu caracterul ‘;’ - clauza INTO este obligatorie atunci când o instrucţiune SQL este înglobată intr-un bloc PL/SQL

- clauza WHERE este opţională şi poate fi utilizată pentru a specifica variabile de intrare, constante, literali sau expresii PL/SQL

- numărul variabilelor de ieşire din clauza INTO trebuie să corespundă numărului de coloane ce apar în clauza SELECT.

Următorul exemplu returnează suma salariilor tuturor angajaţilor dintr-un anumit departament.

DECLARE v_sum emp.sal%TYPE; v_deptno NUMBER NOT NULL := 10;

BEGIN SELECT SUM(sal) -- funcţie agregat INTO v_sum

FROM emp WHERE deptno = v_deptno; DBMS_OUTPUT.PUT_LINE(‘Valoarea variabilei v_sum este ‘ || TO_CHAR(v_sum)); END;

/ Valoarea variabilei v_sum este 8750 PL/SQL permite utilizarea instrucţiunilor de manipulare a datelor INSERT, UPDATE şi DELETE.

DECLARE v_empno emp.empno%TYPE; BEGIN SELECT empno_sequence.NEXTVAL -- memoreaza urmatoarea -- valoare a secventei in -- variabila v_empno INTO v_empno FROM dual; INSERT INTO emp(empno, ename, job, deptno) VALUES(v_empno, ‘HARDING’, ‘CLERK’, 10); END;

/ Exemplul de mai sus inserează în tabelul emp o nouă înregistrare, valoarea cheii primare fiind obţinută cu ajutorul secvenţei empno_sequence. Următorul exemplu măreşte salariile angajaţilor din tabelul emp având funcţia ‘ANALYST’.

DECLARE v_sal_increase emp.sal%TYPE := 2000; BEGIN UPDATE emp SET sal = sal + v_sal_increase WHERE job = ‘ANALYST’; END;

/

Se poate produce ambiguitate în clauza SET datorită faptului că, deşi membrul stâng este numele unei coloane, membrul drept poate fi o coloană sau o variabilă PL/SQL. În situaţia în care o coloană şi o variabilă au acelaşi nume, serverul Oracle va căuta în baza de date mai întâi coloană, şi apoi variabila. Ştergerea liniilor din tabelul emp ce corespund angajaţilor din departamentul 10 se poate efectua cu ajutorul următorului bloc PL/SQL: DECLARE v_deptno emp.deptno%TYPE := 10; BEGIN DELETE FROM emp WHERE deptno = v_deptno; END;

/ Pentru evitarea situaţiilor de ambiguitate se poate avea în vedere următoarea convenţie de denumire: coloanele din baza de date şi variabilele trebuie să aibă nume distincte.

Controlul logicii tranzacţiilor se poate face cu ajutorul instrucţiunilor COMMIT şi ROLLBACK. Sintaxa acestor instrucţiuni este: COMMIT [WORK] ROLLBACK [WORK]

unde WORK apare din motive de compatibilitate cu standardele ANSI.

Structuri de control a execuţiei în PL/SQL

Principalele structuri de control a execuţiei sunt construcţiile condiţionale (instrucţiuni IF) şi cele repetitive. Instrucţiunea IF

Instrucţiunea IF permite execuţia selectivă a unor acţiuni pe baza unor condiţii. Sintaxa instrucţiuni IF este:

IF conditie THEN instructiuni; [ELSIF conditie THEN instructiuni;] [ELSE instructiuni;] END IF;

unde: conditie este o variabilă booleană sau o expresie (TRUE, FALSE sau NULL); ea este

asociată unei secvenţe de instrucţiuni ce se va executa dacă expresia are valoarea TRUE

instructiuni pot fi una sau mai multe instrucţiuni PL/SQL sau SQL; pot include alte instrucţiuni IF.

ELSIF este un cuvânt cheie ce introduce o nouă expresie booleană; dacă prima condiţie este FALSE sau NULL, atunci ELSIF introduce condiţii suplimentare;

ELSE este un cuvânt cheie, care odată atins, are ca efect execuţia instrucţiunilor ce urmează imediat după el.

Exemplul următor setează funcţia ‘SALESMAN’, numărul departamentul 35 şi comisionul de 20% din salariul curent dacă numele este ‘MILLER’: … IF v_ename = ‘MILLER’ THEN v_job := ‘SALESMAN’;

v_deptno := 35; v_comm := sal * 0.2;

END IF; …

Observaţii: - se pot specifica mai multe clauze ELSIF; - poate exista cel mult o clauză ELSE;

Să considerăm următoare structură IF:

IF conditie1 THEN instructiuni ELSIF conditie2 THEN instructiuni ELSE instructiuni END IF;

Modul de execuţie al acestei structuri este următorul: - dacă se evaluează conditie1 şi rezultă TRUE se vor executa instrucţiunile de pe ramura THEN; - altfel se evaluează conditie2. Dacă rezultă TRUE se execută instrucţiunile de pe ramura THEN; - altfel se execută instrucţiunile de pe ramura ELSE. Exemplu:

… IF v_start > 100 THEN v_start := 2 * v_start; ELSIF v_start >=50 THEN v_start := 0.5 * v_start ELSE v_start := 0.1 * v_start; END IF; …

Structuri repetitive Aceste structuri sunt utilizate pentru repetarea unei instrucţiuni sau a unei secvenţe de instrucţiuni de un anumit număr de ori.

1. Structuri de tip LOOP

Sintaxa acestei structuri este următoarea:

LOOP instrucţiune1; … EXIT [WHEN conditie] END LOOP;

unde: conditie este o variabilă booleană sau o expresie; De fiecare dată când fluxul de execuţie atinge END LOOP, controlul este predat instrucţiunii LOOP corespunzătoare. Pentru ieşirea din buclă trebuie utilizată instrucţiunea EXIT. Se poate executa EXIT fie ca o acţiune în cadrul unei instrucţiuni IF, fie ca instrucţiune din buclă. La instrucţiunea EXIT se poate ataşa o clauză WHEN de părăsire condiţionată a buclei. Exemplu de utilizare a structurii LOOP:

DECLARE v_ordid item.ordid%TYPE := 10; v_counter NUMBER(2) := 1 BEGIN LOOP INSERT INTO item(ordid, itemid) VALUES(v_ordid, v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; END;

/

2. Structuri de tip FOR

Sintaxa instrucţiunii FOR este:

FOR contor IN [REVERSE] limita_inf..lim_sup LOOP instructiune1; instructiune2; … END LOOP;

unde: contor este o variabilă întreagă declarată implicit, a cărei valoare este incrementată

sau decrementată (se face decrementare dacă se specifică REVERSE) cu o unitate la fiecare iteraţie, până se atinge lim_sup, respectiv lim_inf.

Notă: contor nu trebuie declarat ca variabilă; el este declarat implicit ca întreg.

Exemplu: DECLARE v_ordid item.ordid%TYPE := 101; BEGIN FOR i IN 1 .. 100 LOOP INSERT INTO item(ordid, itemid) VALUES(v_ordid, i); END LOOP;

END; /

3. Structuri WHILE

Cu ajutorul instrucţiunii WHILE se poate repeta o secvenţă de instrucţiuni până în momentul în care condiţia de control devine FALSE sau NULL.

WHILE conditie LOOP instructiune1; instructiune2;

... END LOOP;

Probleme 1. Creaţi un bloc PL/SQL care selectează numărul maxim de departament din tabelul dept şi

memorează această valoare într-o variabilă SQL*Plus. Se va afişa rezultatul pe ecran. 2. Creaţi un bloc PL/SQL care să permită introducerea unei noi înregistrări în tabelul dept. Se va

cere introducerea de la tastatură a numărului de departament şi a numelui său. Verificaţi inserarea noului departament.

3. Creaţi un bloc PL/SQL care să şteargă departamentul inserat la punctul 2. Se va prelua de tastatură numărul departamentului ce urmează a fi şters.

4. Creaţi tabelul MESAJE având următoarea schemă: MESAJE(Rezultate NUMBER(3))

Inseraţi în acest tabel numerele de la 1 la 10, exceptând valorile 6 şi 8. Salvaţi modificările efectuate asupra tabelului. Conţinutul tabelului după executarea blocului PL/SQL este cea din tabelul de mai jos. Rezultate --------- 1 2 3 4 5 7 9 10

5. Creaţi un bloc PL/SQL care actualizează comisionul unui anumit angajat din tabelul emp

conform următoarelor reguli: - dacă salariul său este mai mic decât 1000, comisionul este de 10% din salariu; - dacă salariul său este între 1000 şi 1500, comisionul este de 15% din salariu; - dacă salariul său este mai mare decât 1500, comisionul este de 20% din salariu; - dacă salariul său este NULL, comisionul este 0. Numărul angajatului va fi introdus de la tastatură. După ce se operează o modificare asupra tabelului emp salvaţi acea modificare. Verificaţi corectitudinea modificării.

Tipuri de date compuse

Tipurile de date compuse (cunoscute şi sub numele de colecţii) ce pot fi utilizate în PL/SQL sunt:

- RECORD - TABLE - tabele imbricate - VARARRAY

În continuare vor fi prezentate doar două dintre aceste tipuri compuse şi anume tipul RECORD şi TABLE. Tipul RECORD O variabilă de tip RECORD (numită înregistrare) reprezintă o colecţie de date memorate în câmpuri, fiecare câmp având asociat un nume şi un tip. Să presupunem că dispunem de diverse informaţii despre un angajat, cum ar fi nume, salariu şi data angajării. O înregistrare ce conţine câte un câmp pentru fiecare tip de informaţie permite tratarea datelor ca o singură unitate logică. Caracteristici ale înregistrărilor: - o înregistrare poate avea un număr arbitrar de câmpuri; - o înregistrare poate avea o valoare iniţială sau poate fi definită ca fiind NOT NULL; - câmpurile fără valori iniţiale sunt iniţializate cu valori NULL; - cuvântul cheie DEFAULT poate fi utilizat şi la definirea câmpurilor; - se pot defini tipuri RECORD şi se pot declara variabile de tip înregistrare în secţiunea declarativă

a oricărui bloc, subprogram sau pachet; - se pot declara şi apoi referi înregistrări imbricate (înregistrări ce au drept componentă o altă

înregistrare). Sintaxa ce permite definirea tipului RECORD este: TYPE nume_tip IS RECORD ( declaratie_camp {, declaratie_camp} ); declaratie_camp respectă sintaxa: nume_camp <tip_camp | variabila%TYPE | tabel.coloana%TYPE | tabel%ROWTYPE> [[NOT NULL] <:= | DEFAULT> expr] unde: nume_tip numele tipului RECORD nume_camp numele câmpului în cadrul înregistrării tip_camp tipul câmpului. Poate fi orice tip PL/SQL, exceptând REF CURSOR expr expresie de tipul tip_camp sau valoare iniţială Constrângerea NOT NULL previne iniţializarea câmpului corespunzător cu valoarea NULL şi forţează iniţializarea câmpului.

O înregistrare se declară în secţiunea declarativă, cu ajutorul sintaxei: identificator nume_tip; Fiecare câmp are un nume unic şi un tip de date specific. Deoarece în PL/SQL nu există tipuri de date predefinite pentru înregistrări, va trebui să definim mai întâi tipul de date şi apoi să declarăm o variabilă de tipul respectiv, aşa cum este ilustrat în următorul exemplu: DECLARE TYPE tip_inreg_emp IS RECORD -- defineste un tip RECORD (empno NUMBER(4) NOT NULL := 100, ename emp.ename%TYPE, job emp.job%TYPE); inreg_emp tip_inreg_emp; -- declara o variabila .... -- de tipul tip_inreg-emp Accesarea unui câmp dintr-o înregistrare se face pe baza numelui câmpului, conform sintaxei: nume_inreg.nume_camp De exemplu, accesarea câmpului job din variabila inreg_emp se face astfel: inreg_emp.job ... iar asignarea valorii 'CLERK' câmpului job se face cu instrucţiunea: inreg_emp.job := 'CLERK'; Înregistrările definite de utilizator sunt instanţiate la intrarea în bloc şi încetează să existe la ieşirea din bloc. Următorul exemplu utilizează o înregistrare formată din trei câmpuri (ename, job, sal) pentru a stoca informaţiile furnizate de o instrucţiune SELECT. Trebuie să existe o corespondenţă între câmpurile înregistrării şi lista ţintă a instrucţiunii SELECT. DECLARE TYPE tip_inreg_emp IS RECORD (ename emp.ename%TYPE, job emp.job%TYPE, sal emp.sal%TYPE); inreg_emp tip_inreg_emp; BEGIN SELECT ename, job, sal INTO inreg_emp FROM emp WHERE empno = 7369;

DBMS_OUTPUT.PUT_LINE('deptno = ' || inreg_emp.ename); DBMS_OUTPUT.PUT_LINE('dname = ' || inreg_emp.job); DBMS_OUTPUT.PUT_LINE('loc = ' || inreg_emp.sal);

END; /

Atributul %ROWTYPE DECLARE identificator referinta%ROWTYPE ...

unde: identificator este numele înregistrării

referinta este numele tabelului, vederii sau variabilei de tip cursor pe care se bazează înregistrarea. Referinţa TREBUIE să fie validă în momentul declarării înregistrării (tabelul sau vederea trebuie să existe, iar cursorul trebuie să fie declarat).

Atributul %ROWTYPE poate fi utilizat pentru declararea unei variabile de tip înregistrare pe baza tuturor coloanelor dintr-un tabel sau dintr-o vedere. Câmpurile din înregistrare vor prelua numele şi tipurile de date ale coloanelor din tabel sau din vedere. Exemplul următor declară o înregistrare folosind atributul %ROWTYPE: DECLARE inreg_emp emp%ROWTYPE; ... Înregistrarea inreg_emp va fi formată din următoarele câmpuri: empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7, 2), comm NUMBER(7, 2), deptno NUMBER(2)

Un câmp individual din înregistrare va fi referit folosind notaţia: identificator.nume_camp

La fel ca în cazul variabilelor scalare, instrucţiunea SELECT poate fi utilizată pentru a stoca valorile returnate de o interogare într-o înregistrare. Singura restricţie este ca instrucţiunea SELECT să returneze toate coloanele din tabelul (vederea) ce stă la baza definiţiei înregistrării. -- Nu uitati sa executati SET SERVEROUTPUT ON la promptul SQL DECLARE

inreg_dept dept%ROWTYPE; -- campurile inregistrarii corespund BEGIN -- coloanelor din tabelul dept -- instructiunea SELECT se va executa cu succes doar daca -- returneaza o singura linie

SELECT * INTO inreg_dept from dept WHERE dname = 'SALES'; DBMS_OUTPUT.PUT_LINE('Valorile memorate in inregistrare: '); DBMS_OUTPUT.PUT_LINE('deptno = ' || inreg_dept.deptno); DBMS_OUTPUT.PUT_LINE('dname = ' || inreg_dept.dname); DBMS_OUTPUT.PUT_LINE('loc = ' || inreg_dept.loc);

END; /

Tipul TABLE

Obiectele de tip TABLE, denumite şi tabele PL/SQL, sunt asemănătoare tabelelor din baza de date, cu unele diferenţe.

Un tabel PL/SQL: - este similar unui vector;

- poate avea cel mult două coloane: o o cheie primară de tipul BINARY_INTEGER, ce indexează tabelul PL/SQL; o o coloană scalară sau înregistrare, ce stochează elementele tabelului;

- se poate mări în mod dinamic, deoarece nu este limitat ca dimensiune fizică.

Diferenţa dintre un tabel PL/SQL şi un vector constă în următoarele: - un vector are o limită superioară pentru valoarea indexului, în timp ce indexul unui tabel

PL/SQL este nelimitat; - un vector trebuie sa fie dens (i.e. să aibă valori consecutive pentru index). Prin urmare, nu

vom putea elimina elemente din vector. Iniţial, un tabel PL/SQL este dens, dar ulterior pot fi şterse elemente astfel încât să apară goluri în valorile indexului. Cu ajutorul metodei NEXT putem determina indexul următorului element din tabel.

Crearea unui tabel PL/SQL implică parcurgerea următorilor paşi:

- definirea unui tip TABLE; - declararea unei variabile de tipul respectiv.

TYPE nume_tip IS TABLE OF <tip_coloana | variabila%TYPE | tabel.coloana%TYPE | tabel%ROWTYPE> [NOT NULL] INDEX BY BINARY_INTEGER; identificator nume_tip;

unde: nume_tip este numele tipului TABLE; tip_coloana este orice tip scalar (de exemplu VARCHAR2, DATE, NUMBER) sau compus; identificator este numele variabilei ce reprezintă întregul tabel PL/SQL. DECLARE TYPE tip_tabel_date IS TABLE OF DATE INDEX BY BINARY_INTEGER; tabel_datetip_tabel_date; TYPE tip_tabel_ename IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; tabel_datetip_tabel_date; ...

În exemplul de mai sus au fost definite două tipuri de date: tipul tip_tabel_date ce va conţine date calendaristice şi tipul tip_tabel_ename ce va conţine nume de angajaţi.

Ca şi în cazul tabelelor din baza de date, dimensiunea unui tabel PL/SQL nu este constrânsă, ea crescând cu fiecare linie adăugată. Tabelele PL/SQL pot avea o singură coloană şi o cheie primară, ambele elemente fiind nedenumite. Coloana poate aparţine oricărui tip de date scalar sau compus, dar cheia primară trebuie să fie de tipul BINARY_INTEGER. Nu se poate iniţializa un tabel PL/SQL la declarare. Gama de variaţie a unei valori BINARY_INTEGER este –2.147.483.647 ÷ +2.147.483.647, deci indexul unui tabel PL/SQL poate avea valori negative. Nu este obligatoriu ca indexarea să înceapă cu valoarea 1.

Exemplul următor declară două variabile de tip tabel şi ilustrează modul în care se pot referi elementele tabelelor. DECLARE

TYPE tip_tabel_emp IS TABLE OF emp.ename%TYPE

INDEX BY BINARY_INTEGER; TYPE tip_tabel_hiredate IS TABLE OF DATE INDEX BY BINARY_INTEGER; tabel_ename tip_tabel_ename; tabel_hiredate tip_tabel_hiredate;

BEGIN tabel_ename(1) := ‘CAMERON’; tabel_hiredate(3) := SYSDATE + 7; IF tabel_ename.EXISTS(1) THEN INSERT INTO ... ...

END;

Referirea unei linii dintr-un tabel PL/SQL se face astfel: nume_tabel(valoare_cheie_primara) În exemplul de mai sus, tabel_ename(1) reprezintă prima linia din tabelul tabel_ename, în timp ce tabel_hiredate(3) indică a treia linie din tabel_hiredate. Dacă la definirea unui tip tabel se utilizează tabel%ROWTYPE se va crea un tabel de înregistrări (fiecare element al tabelului va fi o înregistrare). DECLARE

TYPE tip_tabel_dept IS TABLE OF dept%ROWTYPE INDEX BY BIMARY_INTEGER;

tabel_dept tip_tabel_dept; -- fiecare element din tabel_dept este o inregistrare

În acest caz, referirea unui câmp al unui element al tabelului utilizează sintaxa: nume_tabel(index).nume_camp Exemplu: tabel_dept(3).loc := ‘ATLANTA’; -- se initializeaza campul loc din al treilea element din -- tabel PL/SQL pune la dispoziţie un set de metode ale obiectului TABLE. Sintaxa de apel a unei astfel de metode este: nume_tabel.nume_metoda [(parametri)] Metoda Descriere EXISTS(n) Returnează TRUE dacă există elementul n în tabel; încercarea de a referi un

element ce nu există va genera eroarea ORA-01403 COUNT returnează numărul de elemente din tabel FIRST LAST

- returnează indexul primului, respectiv ultimului element din tabel; - returnează NULL dacă tabelul este vid.

PRIOR(n) returnează valoarea indexului care precede indexul n NEXT(n) returnează valoarea indexului care succede indexul n

DELETE[(m [,n])]

- DELETE elimină toate elementele din tabel; - DELETE(n) elimină elementul de pe poziţia n din tabel; - DELETE(m, n) elimină toate elementele de la poziţia m la n din tabel;

Exemplu de utilizare a metodelor pentru tabele PL/SQL. DECLARE TYPE tip_tabel_dept IS TABLE OF dept.dname%TYPE

INDEX BY BINARY_INTEGER; tabel_dept tip_tabel_dept; i BINARY_INTEGER := 7; v_dname dept.dname%TYPE; BEGIN FOR j IN 1..4 LOOP SELECT dname

INTO v_dname FROM dept WHERE deptno = j * 10; IF i = 10 THEN i := 2 * i; ELSE i := i + 1; END IF; tabel_dept(i) := v_dname; END LOOP; DBMS_OUTPUT.PUT_LINE('Informatii tabel PL/SQL:'); DBMS_OUTPUT.PUT_LINE('Nr. valori in tabel:'||tabel_dept.count); IF tabel_dept.EXISTS(1) THEN DBMS_OUTPUT.PUT_LINE('Elementul cu indexul 1 exista.'); ELSE DBMS_OUTPUT.PUT_LINE('Elementul cu indexul 1 nu exista.'); END IF; DBMS_OUTPUT.PUT_LINE('Indexul primului element:' || tabel_dept.first); DBMS_OUTPUT.PUT_LINE('Indexul ultimului element:' || tabel_dept.last); DBMS_OUTPUT.PUT_LINE('Indexul elem. ce precede elem. 10: '

|| tabel_dept.prior(10)); DBMS_OUTPUT.PUT_LINE('Indexul elem. ce succede elem. 10:'

|| tabel_dept.next(10)); tabel_dept.delete(20); -- sterge elementul cu indexul 20 DBMS_OUTPUT.PUT_LINE('Informatii tabel PL/SQL dupa stergere:'); DBMS_OUTPUT.PUT_LINE('Nr. valori in tabel:'||tabel_dept.count); DBMS_OUTPUT.PUT_LINE('Indexul primului element: ' || tabel_dept.first); DBMS_OUTPUT.PUT_LINE('Indexul ultimului element: '|| tabel_dept.last); END; Informatii tabel PL/SQL: Nr. valori in tabel:4

Indexul primului element: 8 Elementul cu indexul 1 nu exista. Indexul ultimului element: 20 Indexul elementului ce precede elementul 10: 9 Indexul elementului ce succede elementul 10: 20 Informatii tabel PL/SQL dupa stergere: Nr. valori in tabel:3 Indexul primului element: 8 Indexul ultimului element: 10 PL/SQL procedure successfully completed.

Probleme

1. Creaţi tabelul TOP_SAL având următoarea structură:

TOP_SAL( nume VARCHAR2(25),

salariu NUMBER(11,2))

2. Scrieţi un bloc PL/SQL pentru a extrage numele şi salariul unui anumit angajat din tabelul emp, pe baza numărului său de înregistrare (utilizaţi tabele PL/SQL).

a. declaraţi două tabele PL/SQL (tabel_ename şi tabel_sal) pentru a memora temporar numele, respectiv salariul angajatului;

b. extrageţi din emp numele şi salariul angajatului al cărui număr de înregistrare a fost furnizat de la tastatură şi memoraţi-le în tabelele PL/SQL;

c. inseraţi în TOP_SAL informaţiile din tabelele PL/SQL.

Introduceti numarul de inregistrare: 7934 PL/SQL procedure successfully completed SQL> SELECT * 2 FROM top_sal; Nume Salariu ------- ------- MILLER 1300

Anexa 1 - Tipuri de date scalare utilizate la declararea unei variabile

Tip date Descriere VARCHAR2 (lungime_maxima) - şir alfanumeric de lungime variabilă.

- se utilizează 1 octet pe caracter şi poate avea o lungime maximă de 32767 octeţi.

CHAR [(lungime_maxima)] - şir alfanumeric de lungime fixă. - spre deosebire de şirurile VARCHAR2, un şir CHAR conţine

întotdeauna numărul maxim de caractere; şirurile mai scurte decât lungimea maximă sunt completate cu spaţii.

- se utilizează 1 octet pe caracter şi poate avea o lungime maximă de 32767 octeţi.

- dacă nu se specifică lungime_maxima, se consideră valoarea implicită de 1.

NUMBER[(precizie,[scala])] - tipul de bază pentru numerele în virgulă fixă sau mobilă. - precizie indică numărul de cifre utilizat pentru reprezentarea

numărului, iar scala indică poziţia punctului zecimal. - acoperă domeniul de valori 1.0E-123 - 9.99E125

BINARY_INTEGER - utilizat pentru declararea numerelor întregi cu semn. - variabilele BINARY_INTEGER sunt stocate în format binar, ocupând

astfel mai puţin spaţiu. De asemenea, calculele cu întregi binari se efectuează mai rapid, deoarece valorile sunt deja în format binar.

PLS_INTEGER - utilizat pentru declararea numerelor întregi cu semn în domeniul –2.147.483.647...2.147.483.647

- utilizează instrucţiuni cod maşină native pentru realizarea calculelor. DATE - utilizat pentru a stoca data şi momentul de timp, domeniul acoperit

fiind de la 1 ianuarie 4712 BC până la 31 decembrie 4712 AD. - dacă nu se specifică un moment de timp, se va utiliza valoarea

implicită 12:00:00 AM. BOOLEAN - utilizat pentru stocarea valorilor TRUE/FALSE/ LONG - similar tipului VARCHAR2, diferenţa constând în aceea că o variabilă

LONG poate avea maxim 32760 caractere RAW (lungime) - utilizat pentru a stoca şiruri de date orientate pe octeţi.

- diferenţa faţă de VARCHAR2 stă în aceea că o valoare de tip RAW nu va fi interpretată de Oracle (nu se face translare de seturi de caractere pe date brute (raw)).

- argumentul lungime poate fi o valoare între 1 şi 32767. LONG RAW (lungime) - similar tipului RAW.

- argumentul lungime poate fi o valoare între 1 şi 32760.

Anexa 2 - Funcţii utilizate în PL/SQL

Nume funcţie Descriere Funcţii ce operează pe şiruri de caractere ASCII(caracter) Returnează echivalentul zecimal al caracterului ASCII CHR(cod_ascii) Returnează caracterul având codul ASCII furnizat CONCAT(sir1, sir2) Concatenează cele două şiruri argumente LENGTH(sir) Returnează lungimea şirului LPAD(sir,lung_totala,[caract]) Completează sir la stânga cu caracter, lungimea maximă a

şirului astfel obţinut fiind lung_totala. Dacă este omis parametrul caracter se consideră valoarea implicită spaţiu.

LPAD(sir,lung_totala,[caract]) Comportare similară funcţiei LPAD, completarea realizăndu-se la dreapta argumentului sir.

Funcţii numerice ABS(numar) Returnează valoarea absolută CEIL(numar) Returnează cel mai mic număr întreg care este mai mare sau egal

cu numar FLOOR(numar) Returnează cel mai mare număr întreg care este mai mic sau egal

cu numar ROUND(numar, [nr_zec]) Rotunjeşte numar la numărul de zecimale specificat. Dacă

nr_zec este omis se va rotunji la întregul cel mai apropiat; dacă nr_zec>0 rotunjirea se face la dreapta separatorului zecimal; dacă nr_zec<0 se va rotunji la stânga separatorului zecimal

Funcţii de conversie TO_CHAR(data,format,[param_NLS]) Converteşte o valoare DATE într-un şir VARCHAR2, permiţând

prezentarea datei într-un anumit format. param_NLS indică specificarea limbii utilizată la afişarea pe ecran. O parte din măştile de format ce pot fi utilizate sunt descrise în tabelul următor.

TO_CHAR(num,format,[param_NLS]) Converteşte orice număr în şir VARCHAR2. Formatele ce pot fi utilizate sunt descrise în tabelul următor.

TO_DATE(sir,format,[NLS_LANG]) Converteşte un şir de caractere (CHAR sau VARCHAR2), delimitat de apostrofuri, la o valoare DATE. Parametrul opţional NLS_LANG permite precizarea limbii în care se va face afişarea. Limitări: - şirul argument nu poate avea mai mult de 220 caractere; - suntem limitaţi la măştile din tabelul următor; - nu putem combina formatele, cum ar fi specificarea

formatului de 24 ore şi, în acelaşi timp, AM sau PM; - nu putem specifica acelaşi element de două ori în şablonul de

conversie (de exemplu, YYYY-MM-MMM-DD) TO_NUMBER(sir,format,[param_NLS]) Converteşte un şir de caractere (CHAR sau VARCHAR2) sau

dată calendaristică într-un număr, cu scopul de a executa calcule numerice.Măştile de formatare sunt decrise în tabelul următor.

Anexa 3 - Şabloanele utilizate cu funcţiile TO_DATE, TO_CHAR şi TO_NUMBER Tip şablon Descriere Măşti de formatare DATE BC sau B.C. Indicator Before Christ AD sau A.D. Indicator Anno Domini SYYYY, YYYY Anul cu 4 cifre. Întoarce un număr negativ dacă se utilizează BC cu SYYYY. YYY, YY, Y Ultimele 3, 2 sau ultima cifră a reprezentării anului YEAR, SYEAR Returnează anul, în litere. SYEAR returnează o valoare negativă dacă se utilizează cu

BC MM Numărul lunii, de la 01 la 12 MONTH Numele lunii, totdeauna pe 9 caractere, eventual completate la dreapta cu spaţii RM Număr roman reprezentând luna, de la I la XII WW Numărul săptămânii în an, de la 1 la 53 W Numărul săptămânii înlună, de la 1 la 5. Prima săptămână începe în prima zi a lunii D Numărul zilei din săptămână, cu valori de la 1 la 7 DD Numărul zilei din lună, cu valori de la 1 la 31 DDD Numărul zilei din an, cu valori de la 1 la 366 DAY Numărul zilei din săptămână, în litere, totdeauna reprezentat pe 9 caractere, eventual

completat la dreapta cu spaţii HH, HH12 Ora din zi, cu valori de la 1 la 12 HH24 Ora din zi, cu valori de la 1 la 23 MI Numărul minutului din oră, de la 0 la 59 SS Numărul secundei din minut, de la 0 la 59 AM sau A.M. Indicator al orei ante-meridian PM sau P.M. Indicator al orei post-meridian Măşti de formatare NUMERE Mască Exemplu 9 9999 Fiecare 9 este considerat o cifră semnificativă. Zerourile din faţa numărului sunt tratate

drept spaţii 0 09999 sau

99990 Zerourile din stânga sau dreapta numărului sunt tratate şi afişate ca zerouri

$ $9999 Prefix reprezentând simbolul monetar, plasat în faţa numărului D 99D99 Indică poziţia punctului zecimal. Cifrele de 9 indică numărul maxim de cifre de o parte

şi de alta a separatorului zecimal

Cursori în PL/SQL La fiecare execuţie a unei instrucţiuni SQL serverul Oracle deschide o zonă de memorie în care este analizată şi executată instrucţiunea. Această zonă de memorie se numeşte cursor. Există două tipuri de cursori: - cursor implicit; - cursor explicit. Cursor implicit Un cursor implicit este deschis ori de câte ori un bloc PL/SQL execută o instrucţiune SQL. Acest cursor poate fi referit cu ajutorul identificatorului SQL şi este gestionat automat de PL/SQL.

Interacţiunea cu un cursor implicit se poate face doar prin intermediul a patru atribute ce permit evaluarea rezultatului execuţiei ultimei utilizări a cursorului implicit. Aceste atribute sunt:

a) SQL%ROWCOUNT - este o valoare întreagă, reprezentând numărul de linii afectate de cea mai recentă instrucţiune

SQL. b) SQL%FOUND - este TRUE dacă cea mai recentă instrucţiune SQL a afectat una sau mai multe linii. c) SQL%NOTFOUND - este TRUE dacă cea mai recentă instrucţiune SQL nu a afectat nici o linie. d) SQL%ISOPEN - are întotdeauna valoarea FALSE deoarece PL/SQL închide cursorul implicit imediat după

utilizare.

Exemplul următor şterge acele linii din tabelul emp pentru care sal = 950. Pe ecran se va afişa numărul de linii şterse. DECLARE

v_sal NUMBER := 950; BEGIN

DELETE FROM emp WHERE sal = v_sal;

DBMS_OUTPUT.PUT_LINE(‘Numar linii sterse: ‘ || SQL%ROWCOUNT);

END; / Numar linii sterse: 2

PL/SQL procedure successfully completed Cursor explicit

Un cursor explicit se va utiliza pentru a procesa, linie cu linie, rezultatul execuţiei unei instrucţiuni SELECT multi-linie (instrucţiunea returnează mai multe linii). Un cursor explicit poate fi controlat conform următoarei diagramei din figura 1.

1. se declară cursorul (se furnizează numele său şi interogarea cu care va fi utilizat). 2. se deschide cursorul. Instrucţiunea OPEN execută interogarea şi realizează legătura cu

eventualele variabile referite. Liniile identificate de interogare formează mulţimea activă şi sunt gata pentru a fi extrase.

creează o zonă identifică încarcă linia testează existenţa eliberează SQL identificată mulţimea curentă în altor linii; mulţimea printr-un nume activă variabile se întoarce la activă (rezultatul FETCH dacă mai interogării) există linii

Figura1. Controlul unui cursor explicit 3. se extrag datele din cursor. Instrucţiunea FETCH încarcă linia curentă din cursor în variabile.

Fiecare extragere determină cursorul să-şi avanseze pointerul la următoarea linie din mulţimea activă. În diagrama anterioară fiecare extragere testează dacă mai există linii neparcurse încă. Dacă da, încarcă linia curentă în variabile, dacă nu, închide cursorul.

4. se închide cursorul. Instrucţiunea CLOSE eliberează mulţimea activă de linii. În continuare vom analiza cei patru paşi enumeraţi anterior. 1. Declararea unui cursor Declararea unui cursor se face în secţiunea declarativă a unui bloc PL/SQL cu ajutorul următoarei sintaxe: CURSOR nume_cursor IS instructiune_SELECT;

unde: nume_cursor este un identificator PL/SQL instructiune_SELECT este o instrucţiune SELECT FĂRĂ clauză INTO

În instrucţiunea SELECT se poate face referire la alte variabile, dar acestea trebuie declarate înaintea cursorului.

DECLARE v_deptno dept.deptno%TYPE; CURSOR c1 IS SELECT empno, ename FROM emp; CURSOR c2 IS SELECT * FROM dept WHERE deptno = v_deptno; BEGIN ...

2. Deschiderea unui cursor Instrucţiunea ce realizează deschiderea unui cursor este OPEN nume_cursor;

OPEN este o instrucţiune ce realizează următoarele operaţii: - alocă dinamic memorie pentru o zonă de memorie context ce poate conţine informaţii

importante pentru procesare; - validează corectitudinea instrucţiunii SELECT;

1. DECLARE

2. OPEN

3. FETCH

4. CLOSE EMPTY?

Yes No

- leagă variabilele de intrare (setează valoarea variabilelor de intrare); - identifică mulţimea rezultat – mulţimea de linii ce satisfac criteriul de selecţie. Execuţia

acestei instrucţiuni nu conduce la extragerea liniilor din rezultat în variabile; - poziţionează pointerul înaintea primei linii din mulţimea activă.

Notă: - dacă interogarea nu returnează nici o linie în momentul deschiderii cursorului, PL/SQL nu va

genera nici o excepţie; - în cazul cursorilor declaraţi cu clauză FOR UPDATE instrucţiunea OPEN blochează accesul

la liniile returnate. 3. Extragerea datelor dintr-un cursor

Instrucţiunea FETCH extrage câte o linie din mulţimea activă. După fiecare extragere, cursorul avansează la următoarea linie din mulţime. FETCH nume_cursor INTO <var1, var2, … | nume_inregistrare>

unde: nume_cursor este numele unui cursor; var1, var2 sunt variabile de ieşire ce memorează rezultatele;

nume_inregistrare este numele înregistrării în care sunt stocate datele extrase. Înregistrarea poate fi declarată utilizând atributul %ROWTYPE.

Indicaţii: - includeţi acelaşi număr de variabile în clauza INTO a instrucţiunii FETCH câte coloane sunt

precizate în lista ţintă a instrucţiunii SELECT şi asiguraţi-vă că tipurile de date sunt compatibile;

- corespondenţa între variabile şi coloane se face pe baza poziţiei în cele două liste; - ca alternativă la utilizarea variabilelor se poate declara o variabilă de tip înregistrare pe baza

cursorului, clauza INTO referind înregistrarea; - obligatoriu trebuie realizat un test pentru a verifica dacă mai există linii în cursor. Dacă un

FETCH nu mai extrage nici o linie înseamnă că nu mai există linii de procesat şi nu se va genera nici o eroare.

Operaţiile realizate de instrucţiunea FETCH sunt: - avansează pointerul la următoarea linie din mulţimea activă; - citeşte datele din linia curentă în variabile de ieşire PL/SQL; DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; i NUMBER := 1; CURSOR c1 IS SELECT empno, ename FROM emp; BEGIN OPEN c1; FOR i IN 1..10 LOOP FETCH c1 INTO v_empno, v_ename; ... END LOOP; CLOSE c1; END; /

Acest exemplu memorează în variabilele v_empno şi v_ename numărul de înregistrare şi numele fiecărui angajat din tabelul emp. 4. Închiderea unui cursor

Închiderea unui cursor se realizează cu instrucţiunea

CLOSE nume_cursor; Această instrucţiune dezactivează cursorul, mulţimea activă devine nedefinită, iar zona context este eliberată. Încercarea de a extrage date dintr-un cursor închis va rezulta în ridicarea excepţiei INVALID_CURSOR. Ca şi în cazul cursorilor impliciţi, există patru atribute ce pot fi utilizate pentru a obţine informaţii în legătură cu starea unui cursor.

Atribut Tip Descriere %ISOPEN boolean evaluat TRUE dacă este deja deschis cursorul %NOTFOUND boolean evaluat TRUE dacă cea mai recentă instrucţiune FETCH

nu returnează nici o linie %FOUND boolean evaluat TRUE dacă cea mai recentă instrucţiune FETCH

returnează o linie %ROWCOUNT number indică numărul total de linii extrase până în momentul

respectiv Notă: Într-o instrucţiune SQL nu se poate face referire la atributul unui cursor. Metoda cea mai uzuală de a procesa mai multe linii dintr-un cursor este de a utiliza o buclă pentru a extrage câte o linie la fiecare iteraţie. După procesarea ultimei linii, un nou FETCH va determina evaluarea atributului %NOTFOUND la valoarea TRUE. Astfel, valoarea acestui atribut ar trebui utilizată pentru părăsirea buclei, întrucât nu mai există alte linii de procesat. Modul de utilizare a atributelor %NOTFOUND şi %ROWCOUNT pentru specificarea condiţiei de ieşire din buclă este ilustrat în următorul exemplu: DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; i NUMBER := 1; CURSOR c1 IS SELECT empno, ename FROM emp; BEGIN OPEN c1; LOOP FETCH c1 INTO v_empno, v_ename; EXIT WHEN c1%ROWCOUNT > 10 OR c1%NOTFOUND; … END LOOP; CLOSE c1; END; / Cursori şi înregistrări Am văzut cum datele returnate de o interogare pot fi stocate, linie cu linie, în variabile PL/SQL. Se poate defini o variabilă înregistrare pe baza coloanelor dintr-un cursor explicit şi apoi utiliza

pentru memorarea unei linii din cursor. Avantajul constă în aceea că valorile fiecărei linii vor fi încărcate direct în câmpurile corespunzătoare din înregistrare. Exemplul următor declară cursorul c1 şi înregistrarea inreg_emp, având structura unei linii din cursor. ... CURSOR c1 IS

SELECT empno, ename FROM emp; inreg_emp c1%ROWTYPE; -- inregistrarea va avea doua BEGIN -- campuri: empno si ename OPEN c1; ... FETCH c1 INTO inreg_emp; ... Bucla cursor FOR O buclă cursor FOR procesează liniile unui cursor explicit, realizând următoarele operaţii: - cursorul este deschis automat; - este extrasă, automat, câte o linie la fiecare iteraţie; - cursorul este închis automat după ce au fost procesate toate liniile; Sintaxa pentru o astfel de buclă FOR este: FOR nume_inreg IN nume_cursor LOOP instructiune1

instructiune2 ... END LOOP; unde: nume_inreg este numele unei înregistrări declarate implicit; nume_cursor este identificatorul cursorului.

DECLARE CURSOR c1 IS SELECT empno, ename FROM emp; BEGIN FOR inreg_emp IN c1 LOOP -- deschidere implicita şi FETCH implicit IF inreg_emp.empno = 7839 THEN ... END IF; END LOOP; -- inchidere automata cursor END; /

Notă: Se poate înlocui cursorul c1 din exemplul de mai sus cu o subinterogare SQL. Dezavantajul acestei variante constă în imposibilitatea de a mai testa atributele cursorului, cursorul nefiind declarat. BEGIN

-- deschidere implicita şi FETCH implicit FOR inreg_emp IN (SELECT empno, ename FROM emp) LOOP

IF inreg_emp.empno = 7839 THEN … END LOOP; -- inchidere automata cursor END; / Cursori expliciţi cu parametri PL/SQL permite declararea unui tip de cursor, numit cursor cu parametri, care returnează diferite mulţimi active în funcţie de valorile parametrilor. Parametrii permit pasarea de valori unui cursor atunci când acesta este deschis, valori ce vor fi utilizate în execuţia interogării asociate. Sintaxa ce permite declararea unui cursor explicit cu parametri este următoarea: CURSOR nume_cursor [(nume_parametru tip_date, …)] IS instructiuneSQL unde: nume_cursor este un identificator PL/SQL

nume_parametru este numele parametrului. Sintaxa asociată unui parametru este următoarea:

nume_parametru [IN] nume_tip [<:= | DEFAULT> expr]

tip_date este un tip scalar de date instructiuneSQL este o instrucţiune SELECT fără clauză INTO Observaţii: - fiecărui parametru formal din declaraţia cursorului trebuie să-i corespundă un parametru

actual în instrucţiunea OPEN. - tipurile de date ale parametrilor sunt aceleaşi ca în cazul variabilelor scalare, dar fără a

preciza dimensiunea. - numele parametrilor sunt utilizate pentru referiri în interogarea asociată cursorului. În exemplul următor este ilustrat modul de declarare a unui cursor cu doi parametri. DECLARE CURSOR c1 (v_deptno NUMBER, v_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = v_deptno AND job = v_job; BEGIN OPEN c1(10, 'CLERK'); ... END; Valorile pasate parametrilor unui cursor pot proveni şi de la variabile PL/SQL sau variabile ale mediului gazdă. DECLARE

job_emp emp.job%TYPE := 'CLERK'; v_ename emp.ename%TYPE; CURSOR c1

(v_deptno NUMBER, v_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = v_deptno AND job = v_job;

BEGIN job_emp := ’ANALYST’; OPEN c1(10, job_emp); …

END;

De asemenea se pot pasa parametri unui cursor utilizat în buclă cursor FOR: DECLARE

CURSOR c1 (v_deptno NUMBER, v_job VARCHAR2) IS SELECT ...

BEGIN FOR inreg_emp IN c1(10, ‘CLERK’) LOOP ... END LOOP; ... FOR inreg_emp IN c1(20, ‘ANALYST’) LOOP ... END LOOP;

END; Variabila inreg_emp va baleia două mulţimi active diferite: mulţimea angajaţilor ce lucrează în departamentul 10 şi au funcţia CLERK (corespunzătoare primei bucle cursor FOR), respectiv mulţimea angajaţilor ce lucrează în departamentul 20 şi au funcţia ANALYST (corespunzătoare celei de a doua bucle cursor FOR). Clauza FOR UPDATE Utilizarea clauzei FOR UPDATE în instrucţiunea SQL asociată unui cursor va avea ca efect blocarea liniilor ce urmează a fi actualizate sau şterse. SELECT … FROM … FOR UPDATE [OF referinta_coloana] [NOWAIT]

unde: referinta_coloana este o coloană din tabelul supus interogării. Se poate utiliza şi o listă de coloane.

NOWAIT returnează o eroare Oracle dacă liniile sunt blocate de o altă sesiune

Dacă interogarea implică o joncţiune între mai multe tabele, se pot bloca linii dintr-un anumit tabel doar dacă FOR UPDATE face referire la o coloană din acel tabel.

Clauza FOR UPDATE se va plasa la sfârşitul instrucţiunii SELECT, chiar şi după clauza ORDER BY, dacă aceasta există.

DECLARE CURSOR c1 IS SELECT empno, ename FROM emp FOR UPDATE NOWAIT;

Exemplul anterior utilizează opţiunea NOWAIT pentru a preveni situaţia în care ar trebui să aşteptăm un timp nedefinit dacă liniile sunt deja blocate de altă sesiune. Pentru astfel de situaţii este indicată utilizarea opţiunii NOWAIT în instrucţiunea SELECT FOR UPDATE şi testarea, într-o buclă, a codului de eroare returnat de încercarea nereuşită de a bloca liniile. Clauza WHERE CURRENT OF În cazul în care se doreşte referirea liniei curente dintr-un cursor explicit se va utiliza clauza WHERE CURRENT OF. Instrucţiunile UPDATE şi DELETE ce conţin această clauză fac referire la ultima linie procesată de instrucţiunea FETCH. WHERE CURRENT OF nume_cursor DECLARE

CURSOR c1 IS SELECT … FROM emp FOR UPDATE NOWAIT;

BEGIN … FOR inreg_emp IN c1 LOOP UPDATE … WHERE CURRENT OF c1; END LOOP; COMMIT;

END; În exemplul de mai sus, instrucţiunea SQL UPDATE va opera asupra ultimei linii extrase din cursor. Observaţi prezenţa clauzei FOR UPDATE în instrucţiunea SELECT asociată cursorului. (se blochează liniile tabelului emp ce vor fi actualizate cu scopul de a elimina posibilitatea ca alt utilizator să opereze simultan pe tabelul emp) Probleme

1. Implementaţi un bloc PL/SQL ce realizează un top al angajaţilor din tabelul emp în raport cu salariile lor.

a) cereţi utilizatorului introducerea unui număr n de la tastatură; b) extrageţi numele şi salariile primelor n persoane din tabelul emp având cele mai mari salarii; c) se presupune că nu există doi angajaţi cu acelaşi salariu; d) informaţiile vor fi inserate în tabelul TOP_SAL; e) testaţi blocul scris pentru diverse valori ale lui n: n = 0, n > nr_total_angajati.

Introduceti numarul de angajati din top: 5 PL/SQL procedure successfully completed Nume Salariu --------- -------- KING 5000 SCOTT 3000 FORD 3000 JONES 2975 BLAKE 2850

2. Aceeaşi problemă ca la punctul 1, dar considerând cazul în care mai mulţi angajaţi au acelaşi salariu. Dacă în TOP_SAL apare o persoană, atunci trebuie să apară toate persoanele având acelaşi salariu. De

exemplu, dacă n = 2 atunci în TOP_SAL trebuie să apară KING, SCOTT şi FORD, iar dacă n = 3 atunci trebuie să participe la top persoanele KING, SCOTT, FORD şi JONES.

Introduceti numarul de angajati din top: 2 PL/SQL procedure successfully completed Nume Salariu --------- -------- KING 5000 SCOTT 3000 FORD 3000

3. Scrieţi un bloc PL/SQL pentru a extrage din baza de date toate departamentele împreună cu angajaţii din fiecare departament. Rezultatele vor fi inserate în tabelul MESAJE. Utilizaţi un cursor pentru a extrage numărul fiecărui departament şi pasaţi fiecare număr de departament altui cursor care va extrage angajaţii din departamentul respectiv.

REZULTAT -------------------------- KING - Departament 10 CLARK - Departament 10 MILLER - Departament 10 JONES - Departament 20 FORD - Departament 20 SMITH - Departament 20 SCOTT - Departament 20 ADAMS - Departament 20 BLAKE - Departament 30 MARTIN - Departament 30 ALLEN - Departament 30 TURNER - Departament 30 JAMES - Departament 30 WARD - Departament 30 14 rows selected

Secţiune opţională

Tratarea excepţiilor în PL/SQL O excepţie corespunde acelor evenimente nedorite ce pot apare în timpul execuţiei unei aplicaţii. Astfel de evenimente pot apare din cauza căderilor sistemului sau reţelei sau din cauza erorilor de programare. În mod normal, la apariţia unei erori, blocul PL/SQL îşi termină execuţia, iar sarcina de lucru rămâne neterminată. Pentru a preveni astfel de situaţii, PL/SQL pune la dispoziţie un mecanism de tratare a excepţiilor, astfel încât programul să-şi poată continua execuţia şi în prezenţa erorilor. Există două metode de generare a unei excepţii:

- atunci când apare o eroare Oracle, excepţia asociată este generată automat. De exemplu, dacă apare eroarea ORA-01403 în cazul în care o instrucţiune SELECT nu returnează nici o linie, PL/SQL ridică excepţia NO_DATA_FOUND.

- utilizatorul poate ridica explicit o excepţie cu ajutorul instrucţiunii RAISE. Excepţia poate fi definită de utilizator sau predefinită.

Dacă excepţia este ridicată în secţiunea executabilă a blocului, motorul PL/SQL va căuta handlerul asociat din secţiunea EXCEPTION. Tratarea cu succes a excepţiei va împiedica propagarea excepţiei în blocul extern sau mediul gazdă. În acest caz, execuţia blocului se termină cu succes. Dacă excepţia este ridicată în secţiunea executabilă a blocului şi nu are asociat un handler, excepţia va fi propagată mai departe, execuţia blocului terminându-se cu eroare. Există trei tipuri de excepţii: - excepţii predefinite ale serverului Oracle

- nu trebuie declarate; - sunt generate implicit de server.

- excepţii ale serverului Oracle ce nu sunt predefinite - trebuie declarate în secţiunea declarativă; - sunt generate implicit de server.

- excepţii definite de utilizator - trebuie declarate în secţiunea declarativă; - trebuie generate explicit de utilizator.

Tratarea excepţiilor Tratarea unei excepţii se realizează prin includerea unei rutine în secţiunea de tratare a excepţiilor. EXCEPTION

WHEN exceptie1 [OR exceptie2 ] THEN instructiune1; instructiune2; [WHEN exceptie3 [OR exceptie4 ] THEN instructiune1; instructiune2;] [WHEN OTHERS THEN instructiune1; instructiune2;]

unde: exceptie este numele standard al unei excepţii predefinite sau numele

unei excepţii utilizator declarată în secţiunea DECLARE; instructiune este o instrucţiune SQL sau PL/SQL;

OTHERS este o clauză opţională al cărei scop este de a trata excepţiile nespecificate Fiecare handler este format dintr-o clauză WHEN (care specifică excepţia), urmată de o secvenţă de instrucţiuni ce vor fi executate în cazul apariţiei respectivei excepţii. Vor fi tratate doar excepţiile specificate în secţiunea EXCEPTION, iar orice altă excepţie va fi tratată doar dacă este inclusă clauza OTHERS. Clauza OTHERS va trata orice excepţie care nu a fost tratată şi din această cauză trebuie să fie ultima clauză în secţiune. O clauză WHEN poate trata două sau mai multe excepţii, caz în care trebuie specificate numele excepţiilor conectate prin operatorul logic OR.

Reguli de tratare a excepţiilor: - secţiunea de tratare a excepţiilor începe cu cuvântul cheie EXCEPTION; - se definesc mai multe handlere, fiecare dispunând de un set propriu de acţiuni; - în momentul apariţiei unei excepţii PL/SQL va procesa un singur handler, după care se va termina execuţia

blocului; - clauza OTHERS se va plasa după toate celelalte handlere; - poate exista cel mult o clauză OTHERS; - excepţiile nu pot apare în instrucţiuni de atribuire sau instrucţiuni SQL. Tratarea excepţiilor predefinite PL/SQL va ridica implicit o excepţie predefinită ori de câte ori un program încalcă o regulă Oracle. Excepţiile predefinite sunt declarate (i.e. sunt denumite) în pachetul STANDARD, deci utilizatorul nu trebuie să le atribuie nume cu ajutorul directivei de compilare EXCEPTION_INIT (după cum se va vedea la excepţiile nedefinite). Tratarea unei excepţii predefinite se face prin specificarea numelui excepţiei în rutina corespunzătoare de tratare din secţiunea EXCEPTION. Tabelul următor conţine numele câtorva erori predefinite, împreună cu descrierea condiţiilor ce conduc la generarea erorii.

Nume excepţie Număr eroare

Descriere

ACCESS_INTO_NULL ORA-06530 încercare de a asigna valori atributelor unui obiect ce nu a fost iniţializat

COLLECTION_IS_NULL ORA-06531 încercare de a aplica alte metode orientate pe colecţii decât EXISTS unui tabel imbricat sau varray neiniţializat

CURSOR_ALREADY_OPEN ORA-06511 încercare de deschidere a unui cursor deja deschis DUP_VAL_ON_INDEX ORA-00001 încercare de inserare a unei valori duplicat intr-o

coloana cu constrângere UNIQUE INVALID_CURSOR ORA-01001 operaţie ilegală asupra cursorului INVALID_NUMBER ORA-01722 conversie eşuată de la tipul şir de caractere la număr LOGIN_DENIED ORA-01017 login cu nume şi/sau parolă invalida NO_DATA_FOUND ORA-01403 SELECT single-row nu a returnat nici o linie NOT_LOGGED_ON ORA-01012 programul PL/SQL generează un apel la baza de date

fără a fi conectat la ea PROGRAM_ERROR ORA-06501 PL/SQL are o problemă internă STORAGE_ERROR ORA-06500 PL/SQL nu mai are memorie sau memoria este coruptă SUBSCRIPT_BEYOND_COUNT ORA-06533 referire la un element dintr-un tabel imbricat sau

varray prin intermediul unui index ce este mai mare decât numărul elementelor din colecţie

SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 referire la un element dintr-un tabel imbricat sau varray prin intermediul unui index ce este în afara limitei legale (e.g. -1)

TIMEOUT_ON_RESOURCE ORA-00051 expirare timp de aşteptare pentru câştigarea controlului asupra unei resurse

TOO_MANY_ROWS ORA-01422 SELECT single-row returnează mai mult de o linie VALUE_ERROR ORA-06502 eroare aritmetică, de conversie, trunchiere ZERO_DIVIDE ORA-01476 încercare de împărţire prin 0

Exemplu de tratare a unor excepţii predefinite:

BEGIN SELECT ... ... COMMIT;

EXCEPTION WHEN NO_DATA_FOUND THEN --tratare exceptie NO_DATA_FOUND instructiune1; instructiune2; WHEN TOO_MANY_ROWS THEN --tratare exceptie TOO_MANY_ROWS instructiune3; WHEN OTHERS THEN --tratare alte exceptii

instructiune4; instructiune5;

END; / Exemplul următor extrage din tabelul emp numele angajaţilor ce au ca salariu valoarea introdusă de la tastatură. Dacă instrucţiunea SELECT nu returnează nici o linie, Oracle va ridica excepţia NO_DATA_FOUND, iar dacă SELECT returnează mai mult de o linie va fi generată excepţia TOO_MANY_ROWS. Tratarea celor doua excepţii constă în afişarea pe ecran a unui mesaj de avertizare.

ACCEPT salar PROMPT 'Introduceti salariul: ' SET VERIFY OFF DECLARE

v_ename emp.ename%TYPE; BEGIN

SELECT ename INTO v_ename FROM emp WHERE sal = &salar;

EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('SELECT nu a returnat nici o linie.'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('SELECT a returnat mai multe linii.');

END; / SET VERIFY ON Pentru a testa blocul anterior se va introduce valoarea 1250 pentru variabila de substituţie salar. Deoarece există mai mulţi angajaţi cu acest salar, se va genera excepţia TOO_MANY_ROWS.

Introduceti salariul: 1250 SELECT a returnat mai multe linii. PL/SQL procedure successfully completed. Tratarea excepţiilor nedefinite O excepţie nedefinită este o excepţie ridicată implicit de server, dar care, spre deosebire de o excepţie predefinită nu are nume (nu este declarată în pachetul STANDARD). Tratarea unei astfel de excepţii se realizează declarând mai întâi excepţia sau apelând la handlerul OTHERS. În PL/SQL, directiva de compilare (pragma) EXCEPTION_INIT instruieşte compilatorul să asocieze un nume de excepţie cu numărul unei erori Oracle. Astfel, vom putea face referire la orice excepţie internă prin intermediul numelui ei şi vom putea scrie un handler corespunzător. Modul de tratare a unei erori nedefinite este ilustrat în diagrama următoare:

- denumire excepţie - asociere nume excepţie - tratare excepţie cu o eroare Oracle

Declararea unui nume de excepţie se face cu sintaxa:

nume_exceptie EXCEPTION; Asociere nume excepţie cu o eroare nedefinită Oracle:

Declarare Referire Asociere

secţiunea declarativă secţiunea de tratare a excepţiilor

PRAGMA EXCEPTION_INIT(nume_exceptie, numar_eroare)

unde: nume_exceptie este numele unei excepţii declarate anterior; numar_eroare este un număr standard de eroare Oracle.

referirea excepţiei declarate în cadrul rutinei de tratare corespunzătoare (din secţiunea EXCEPTION). Exemplul următor ilustrează modul de tratare a erorii nedefinite -2291 (încălcare constrângere de integritate la nivelul tabelului copil). Dacă se utilizează o valoare pentru deptno ce nu există în tabelul dept se va termina execuţia şi se va afişa un mesaj corespunzător. Acest exemplu presupune existenţa unei constrângeri de referinţă (constrângere FOREIGN KEY) între atributul deptno din tabelul emp şi atributul deptno din tabelul dept. DECLARE

e_invalid_deptno EXCEPTION; PRAGMA EXCEPTION_INIT(e_invalid_deptno, -2291);

BEGIN INSERT INTO emp(empno,ename,deptno) VALUES (8000, 'TOM', 50);

EXCEPTION WHEN e_invalid_deptno THEN DBMS_OUTPUT.PUT_LINE('EROARE! Numar departament invalid.');

END; /

SQL> START test3 EROARE! Numar departament invalid. PL/SQL procedure successfully completed.

Tratarea excepţiilor utilizator PL/SQL permite utilizatorului să definească excepţii specifice aplicaţiei. Spre deosebire de tipurile anterioare de excepţii, o excepţie utilizator trebuie declarata şi ridicata explicit. O excepţie utilizator poate fi tratată conform următoarei diagrame:

- denumire excepţie - generare explicită - tratare excepţie cu ajutorul instrucţiunii RAISE

Declarare excepţie O excepţie utilizator poate fi declarată doar în secţiunea declarativă a unui bloc PL/SQL, subprogram (procedură sau funcţie) sau pachet cu ajutorul următoarei sintaxe: nume_exceptie EXCEPTION; Există o anumită similaritate între excepţii şi variabile din punct de vedere al regulilor privind domeniul de existenţă.

Declarare Referire Generare

secţiunea declarativă secţiunea de tratare a excepţiilor

secţiunea executabilă

a) O excepţie nu poate fi declarată de două ori în acelaşi bloc, dar se poate declara o aceeaşi excepţie în două blocuri diferite.

b) Excepţiile declarate într-un bloc sunt considerate locale pentru acel bloc şi globale pentru toate sub-blocurile. Prin urmare, un bloc va putea referi o excepţie locală, în timp ce blocurile externe nu pot referi excepţiile declarate în sub-blocuri.

c) Dacă o excepţie globală este redeclarată într-un sub-bloc, va avea câştig de cauză declaraţia locală. Exemplul următor ilustrează regulile enumerate mai sus: DECLARE

past_due EXCEPTION; acct_num NUMBER;

BEGIN DECLARE ---- inceput sub-bloc past_due EXCEPTION; -- aceasta declaratie -- are castig de cauza acct_num NUMBER; BEGIN ... IF ... THEN RAISE past_due; -- exceptia locala nu este tratata END IF; END; ------------- sfarsit sub-bloc

EXCEPTION WHEN past_due THEN -- nu trateaza exceptia ridicata in sub-bloc ...

END; Blocul extern nu tratează excepţia ridicată deoarece declaraţia excepţiei past_due din sub-bloc are câştig de cauză. Deşi cele două excepţii au acelaşi nume, ele sunt diferite. Pentru ca excepţia past_due să fie tratată în blocul extern avem două alternative: să eliminăm declaraţia din sub-bloc sau să definim un handler OTHERS în blocul extern.

Utilizarea instrucţiune RAISE pentru generarea excepţiei utilizator

O excepţie utilizator trebuie ridicată explicit în momentul apariţiei unei erori ce împiedică terminarea cu succes a operaţiilor aferente programului. Acest lucru se face cu ajutorul instrucţiunii RAISE. RAISE nume_exceptie;

Tratare excepţie în secţiunea EXCEPTION În următorul exemplu se va genera o excepţie utilizator dacă se încearcă inserarea unei noi înregistrări în tabelul emp, valoarea comisionului fiind diferită de NULL deşi funcţia angajatului nu este 'SALESMAN' (considerăm valabilă regula conform căreia numai angajaţii cu funcţia 'SALESMAN' pot avea comision). ACCEPT comision PROMPT 'Valoare comision: ' DEFAULT NULL ACCEPT functie PROMPT 'Introduceti functia: ' SET VERIFY OFF DECLARE

e_invalid_comm EXCEPTION; v_comm emp.comm%TYPE; v_job emp.job%TYPE;

BEGIN v_job := UPPER('&functie'); v_comm := &comision; IF v_job <> 'SALESMAN' AND v_comm IS NOT NULL THEN RAISE e_invalid_comm; END IF; INSERT INTO emp VALUES(8001,'FROST',2500,v_job,v_comm,20);

EXCEPTION

WHEN e_invalid_comm THEN DBMS_OUTPUT.PUT_LINE('EROARE! Valoare invalida comision.');

END; / SET VERIFY ON Valoare comision: 100 Introduceti functia: ANALYST EROARE! Valoare invalida comision. PL/SQL procedure successfully completed.

În momentul apariţiei unei excepţii putem identifica codul de eroare şi mesajul asociat utilizând funcţiile SQLCODE şi SQLERRM. Funcţia SQLCODE returnează valoarea numerică asociată erorii:

- 0 : nu a fost generată nici o excepţie; - 1 : excepţie utilizator; - +100 : excepţie NO_DATA_FOUND; - - ### (valoare negativă) : alte erori Oracle.

Funcţia SQLERRM returnează mesajul asociat erorii.

Următorul exemplu va insera în tabelul erori informaţii (cod eroare şi mesaj) despre ultima eroare tratată.

DECLARE v_cod_err NUMBER; v_mesaj_err VARCHAR2(255);

BEGIN DELETE FROM myemp WHERE empno = 'sal'; -- se va genera o exceptie deoarece -- ‘sal’ nu este numar

EXCEPTION WHEN OTHERS THEN v_cod_err := SQLCODE; v_mesaj_err := SUBSTR(SQLERRM,1,255); INSERT INTO erori VALUES(TO_CHAR(v_cod_err) || ' : ' || v_mesaj_err);

END; / SQL> select * from erori; ERR -------------------------------------- -1722 : ORA-01722: invalid number

Observaţii: 1.Funcţiile SQLCODE şi SQLERRM nu pot fi utilizate direct în instrucţiuni SQL. Valorile celor două funcţii trebuie

memorate în variabile locale ce pot fi apoi utilizate cu instrucţiuni SQL. 2.Funcţia SUBSTR a fost utilizată deoarece dimensiunea maximă a şirului SQLERR este de 512 caractere. Această

trunchiere este necesară pentru a putea stoca mesajul asociat erorii în variabila v_mesaj_err.

Propagarea excepţiilor în sub-blocuri În cazul în care un sub-bloc tratează o excepţie, execuţia sa se va termina normal şi controlul este redat următoarei instrucţiuni din blocul extern. Dacă, totuşi, PL/SQL ridică o excepţie şi blocul curent nu deţine un handler pentru acea excepţie, aceasta se va reproduce succesiv în blocurile externe până când va găsi un handler. Dacă nici un bloc din lanţ nu tratează excepţia, ea se va propaga până la mediul gazdă (e.g. SQL*Plus).

DECLARE e_zero_linii EXCEPTION; ...

BEGIN FOR inreg_c IN cursor_emp LOOP BEGIN SELECT ... IF SQL%NOTFOUND THEN RAISE e_zero_linii; END IF; EXCEPTION WHEN e_zero_linii THEN ... -- exceptie generata de SELECT END; END LOOP;

EXCEPTION WHEN TOO_MANY_ROWS THEN ... – se trateaza exceptia

END; -- generata de SELECT din sub-bloc / Propagarea unei excepţii dintr-un sub-bloc într-unul aflat pe un nivel superior atrage după sine sărirea instrucţiunilor ce au mai rămas de executat în sub-bloc. Un avantaj al acestei comportări constă în aceea că putem separa instrucţiunile ce necesită un mod exclusiv de tratare a erorilor într-un bloc propriu, tratând erorile cu caracter general în blocul extern.

Procedura raise_application_error Procedura raise_application_error permite generarea unor mesaje de eroare definite de utilizator din subprograme (proceduri sau funcţii). Astfel, subprogramul poate raporta erori aplicaţiei apelante. Sintaxa de apel a procedurii mai sus menţionate este: raise_application_error(cod_err, mesaj [, {TRUE | FALSE}]); unde: cod_err este un număr negativ în gama -20000 .. -20999 mesaj este un şir de caractere de dimensiune maximă 2048 octeţi TRUE indică plasarea erorii în stiva de erori FALSE eroarea înlocuieşte toate erorile anterioare (este opţiunea implicită) La apelul procedurii raise_application_error subprogramul îşi va termina execuţia şi va returna un număr de eroare utilizator şi un mesaj de eroare aplicaţiei apelante.

CREATE PROCEDURE mareste_sal(emp_id NUMBER, suma NUMBER) IS salariu NUMBER;

BEGIN SELECT sal INTO salariu FROM emp WHERE empno = emp_id; IF salariu IS NULL THEN /* genereaza eroare utilizator */ raise_application_error(-20101, 'Lipseste salariu'); ELSE UPDATE emp SET sal = salariu + suma WHERE empno = emp_id; END IF;

END;

Programul PL/SQL ce apelează procedura mareste_sal poate capta şi trata excepţia ridicată de procedură.

DECLARE sal_null EXCEPTION; PRAGMA EXCEPTION_INIT(sal_null, -20101); BEGIN ... mareste_sal(:id, :suma); -- id si suma sunt variabile -- declarate si initializate in -- mediul SQL*Plus ... EXCEPTION WHEN sal_null THEN ... END; /

Probleme

4. Creaţi un bloc PL/SQL pentru a selecta numele unui angajat pe baza salariului furnizat de la tastatură. a) dacă salariul introdus are ca efect returnarea mai multor linii se va trata această excepţie prin inserarea în

tabelul MESAJE a mesajului 'Exista mai multi angajati cu salariul de <salariu>' (<salariu> este valoarea introdusă de la tastatură);

b) dacă nu este returnată nici o linie se va trata excepţia prin inserarea în tabelul MESAJE a mesajului 'Nu exista angajat cu salariul de <salariu>';

c) dacă este returnată o singura linie se va insera în tabelul MESAJE numele angajatului şi salariul său; d) orice altă excepţie va fi tratată prin inserarea mesajului 'Au aparut alte erori.' în tabelul MESAJE; e) testaţi programul pentru diverse situaţii.

REZULTAT ------------------------------------------------ SMITH - 800 Exista mai multi angajati cu salariul de 3000 Nu exista angajat cu salariul de 6000

5.Scrieţi un bloc PL/SQL ce afişează numărul angajaţilor ce au salariul cuprins între sal ± 100, unde sal este o valoare furnizată de la tastatură.

a) dacă nu există nici un angajat care satisface condiţia de mai sus, afişaţi un mesaj corespunzător pe ecran. Utilizaţi o excepţie pentru acest caz.

b) dacă există unul sau mai mulţi angajaţi care respectă condiţia, mesajul afişat trebuie să indice numărul lor. c) trataţi orice altă excepţie prin afişarea unui mesaj ce indică faptul că au apărut alte erori.

Introduceti salariul: 800 PL/SQL procedure successfully completed. Exista 1 angajat(i) cu salariul intre 790 si 810 Introduceti salariul: 3000 PL/SQL procedure successfully completed. Exista 3 angajat(i) cu salariul intre 2900 si 3100 Introduceti salariul: 6000 PL/SQL procedure successfully completed. Nu exista nici un angajat cu salariul intre 5900 si 6100

Subprograme şi pachete PL/SQL Subprograme PL/SQL Subprogramele sunt blocuri PL/SQL care au nume, acceptă parametri şi pot fi apelate din alte blocuri PL/SQL. Subprogramele pot fi declarate ca proceduri sau funcţii, în funcţie de operaţiile efectuate: o procedură execută o acţiune, iar o funcţie calculează o valoare. Subprogramele se pot stoca la nivelul bazei de date sau la nivelul aplicaţiilor.

Tip bloc Descriere Disponibilitate Bloc anonim Bloc PL/SQL fără nume, inclus în aplicaţie sau lansat

interactiv. în toate mediile PL/SQL

Procedură şi funcţie stocată

Blocuri PL/SQL cu nume ce sunt stocate în baza de date, pot accepta parametri şi pot fi apelate pe baza numelui.

Server Oracle

Procedură şi funcţie stocată în aplicaţie

Blocuri PL/SQL cu nume care sunt stocate fie în aplicaţiile dezvoltate cu Developer/2000, fie în biblioteci şi care pot fi apelate pe baza numelui.

Modulele Developer/2000

Pachet Modul PL/SQL care grupează proceduri, funcţii sau identificatori.

Server Oracle, Developer/2000

Declanşator stocat în baza de date

Bloc PL/SQL asociat cu un tabel al bazei de date şi care se execută la fiecare apel al unei instrucţiuni LMD.

Server Oracle

Declanşator stocat în aplicaţie

Bloc PL/SQL asociat cu un eveniment al aplicaţiei şi care este lansat automat în momentul apariţiei respectivului eveniment.

Developer/2000

Utilizarea subprogramelor oferă o serie de avantaje, cum ar fi: • extensibilitate – orice set de instrucţiuni PL/SQL poate fi organizat sub forma unui

subprogram al cărui scop este de a deservi scopurilor utilizatorului; • modularizare – un program poate fi segmentat în unităţi logice de cod (module) uşor de gestionat • reutilizare - un subprogram validat poate fi utilizat în diverse aplicaţii. Deoarece schimbarea definiţiei sale afectează doar subprogramul (nu şi aplicaţiile apelante), este facilitată operaţia de gestiune. • abstractizare – utilizarea unui subprogram presupune ca utilizatorul să ştie ce face acel subprogram, fără a fi necesară cunoaşterea detaliilor de implementare. Asemenea blocurilor PL/SQL anonime, subprogramele sunt structurate în 3 părţi: 1. secţiunea declarativă (opţională) – conţine declaraţii de tipuri utilizator, cursori, constante,

variabile, excepţii şi subprograme 2. secţiunea executabilă (obligatorie) – conţine instrucţiuni SQL şi PL/SQL 3. secţiunea de tratare a excepţiilor (opţională) – conţine codul aferent tratării excepţiilor

generate pe parcursul execuţiei subprogramului

Proceduri PL/SQL O procedură este un subprogram ce execută o anumită acţiune şi este definită cu ajutorul următoarei sintaxe: [CREATE [OR REPLACE]] PROCEDURE nume_procedura[(parametru[, parametru]...)] {IS | AS} [declaratii locale] BEGIN instructiuni [EXCEPTION handler-e de exceptii] END [nume_procedura];

unde: - parametru respectă sintaxa nume_parametru mod tip_date [{:= | DEFAULT} expresie] - cuvântul cheie opţional CREATE permite crearea unei proceduri stocate în baza de date - mod poate avea una din valorile: IN, OUT sau IN OUT. Aceste trei tipuri de parametru formal

sunt discutate într-un subcapitol separat. O procedură este formată din două părţi: 1. specificaţia procedurii – defineşte numele şi parametrii acesteia; este cuprinsă între cuvântul

cheie PROCEDURE şi numele procedurii sau lista de parametri; 2. corpul procedurii – conţine blocul PL/SQL care este executat în momentul lansării în

execuţie a procedurii; este cuprins între cuvintele cheie IS / AS şi END.

Secţiunea declarativă a unei proceduri este cuprinsă între cuvintele cheie IS şi BEGIN. Spre deosebire de blocurile anonime, din această secţiune lipseşte cuvântul cheie DECLARE.

PROCEDURE marire_sal (emp_id INTEGER, suma REAL) IS -- sectiunea declarativa a procedurii -- de remarcat absenta din sintaxa a cuvintului cheie DECLARE sal_curent REAL; sal_null EXCEPTION; -- sectiunea executabila BEGIN SELECT sal INTO sal_curent FROM emp WHERE empno = emp_id; IF sal_curent IS NULL THEN RAISE sal_null; ELSE UPDATE emp SET sal = sal + suma WHERE empno = emp_id; END IF; -- sectiunea de tratare a exceptiilor EXCEPTION WHEN NO_DATA_FOUND THEN – daca SELECT nu returneaza nimic DBMS_OUTPUT.PUT_LINE(’Nu exista angajatul cu nr. ’ || emp_id); WHEN sal_null THEN -- daca sal=NULL se afiseaza un mesaj pe ecran DBMS_OUTPUT.PUT_LINE(’Salariul este NULL!’); END marire_sal;

În momentul apelării, procedura marire_sal acceptă ca argument un număr de angajat şi o valoare reprezentând mărirea salarială. Numărul angajatului este utilizat pentru a selecta salariul curent din tabelul emp. Dacă nu este găsit angajatul sau dacă salariul său este NULL se va genera o excepţie ce este tratată în secţiunea EXCEPTION. În caz contrar se actualizează salariul. Procedura declarată mai sus poate fi apelată dintr-un alt bloc PL/SQL astfel: DECLARE ... BEGIN ... marire_sal(emp_id, sal); -- apel procedura -- emp_id si sal sunt variabile -- declarate in sectiunea DECLARE ... -- si initializate END; Eliminarea unei proceduri stocate (creată cu instrucţiunea CREATE PROCEDURE) se face cu ajutorul instrucţiunii

DROP PROCEDURE nume_procedura_stocata. Funcţii PL/SQL Funcţiile sunt subprograme PL/SQL utilizate, în general, pentru a calcula o valoare. Structura unei funcţii este asemănătoare structurii unei proceduri, singura diferenţă constând în faptul că o funcţie trebuie să conţină o instrucţiune RETURN. Sintaxa ce permite crearea unei funcţii este următoarea: [CREATE [OR REPLACE]] FUNCTION nume_func[(parametru[, parametru]...)] RETURN tip_data {IS | AS} [declaratii locale] BEGIN instructiuni PL/SQL [EXCEPTION handler-e de exceptii] END [nume_func]; Clauza opţională CREATE permite crearea unei funcţii stocate în baza de date, caz în care instrucţiunea poate fi executată şi de la promptul SQL*Plus. Exemplu: Următoarea funcţie primeşte ca argumente un cod angajat, un salariu minim şi unul maxim. Pe baza codului este selectat salariul angajatului, iar dacă salariul se află între cele două limite (min_sal şi max_sal) atunci funcţia returnează TRUE. Altfel returnează FALSE.

FUNCTION check_sal (emp_id NUMBER, min_sal REAL, max_sal REAL) RETURN boolean IS --- sectiunea v_sal REAL; -| declarativa BEGIN --- sectiunea SELECT sal INTO v_sal -| executabila FROM emp -| WHERE empno = emp_id; -| RETURN (v_sal >= min_sal) AND (v_sal <= max_sal);-| END; --- Eliminarea unei proceduri stocate din baza de date se face cu instrucţiunea DROP FUNCTION nume_functie. Declararea subprogramelor Un subprogram poate fi declarat în orice bloc anonim, subprogram sau pachet. Singura constrângere este ca subprogramul să fie declarat după toate variabilele blocului părinte. Datorită faptului că PL/SQL cere ca un identificator să fie declarat înainte de utilizare, următorul bloc PL/SQL va genera eroare în momentul execuţiei deoarece procedura p1 apelează procedura p2, care nu a fost încă declarată. DECLARE ... PROCEDURE p1(...) IS BEGIN ... p2(...); ... END; PROCEDURE p2(...) IS BEGIN ... p1(...); ... END; BEGIN ... END; Problema nu se rezolvă dacă inversăm declaraţia procedurii p1 cu cea a procedurii p2, deoarece p2 apelează la rândul ei procedura p1. Soluţia constă în declararea în avans a procedurii p2 (specificaţia), urmând ca ulterior să fie declarată complet (specificaţie + corp).

DECLARE ... PROCEDURE p2(...); -- declaratia in avans a procedurii p2 -- contine doar specificatia procedurii PROCEDURE p1(...) IS ... PROCEDURE p2(...) IS ... ... Notaţia poziţională şi notaţia prin nume Numele parametrilor din declaraţia unui subprogram (procedură sau funcţie) sunt parametri formali, în timp ce variabilele plasate în lista de parametri a apelului unui subprogram sunt parametri actuali. La apelul unui subprogram, asocierea între parametrii actuali şi cei formali poate fi indicată prin utilizarea notaţiei poziţionale sau a notaţiei prin nume. Notaţia poziţională realizează corespondenţa între un parametru actual şi parametrul formal corespunzător prin poziţia acestuia din urmă în lista de parametri din specificaţia subprogramului. Pentru exemplificare să considerăm următoarea procedură stocată: CREATE OR REPLACE PROCEDURE test

(dept_no IN NUMBER DEFAULT NULL, out_str OUT VARCHAR2, run_date IN DATE DEFAULT SYSDATE) IS

BEGIN out_str := dept_no || ’, ’ || run_date; END;

Apelul test(12, v_mgr) utilizează notaţia poziţională, în sensul că valoarea 12 este asociată parametrului de intrare dept_no, variabila v_mgr este asociată parametrului de ieşire out_str, iar pentru parametrul run_date nu se specifică o valoare (în acest caz se va utiliza pentru parametrul run_date valoarea implicită SYSDATE – data curentă a sistemului). Notaţia prin nume permite stabilirea explicită a corespondenţei între parametrul actual şi cel formal. Astfel, apelul test(out_str => v_mgr, dept_no => 12) pune în corespondenţă parametrul out_str cu variabila v_mgr şi parametrul dept_no cu valoarea 12. De notat că parametrii actuali pot apare în orice ordine în apelul subprogramului dacă se utilizează notaţia prin nume. Avantajul acestei notaţii constă în aceea că programatorul poate specifica doar o listă parţială de parametri, omiţând parametrii ce au valori implicite. Oracle permite şi o notaţie combinată, singura regulă ce trebuie respectată este ca notaţia poziţională să o preceadă pe cea prin nume. Astfel, apelul test(out_str => v_mgr, 12) va genera eroare. Tipuri de parametri formali Un parametru de tip IN permite pasarea de valori către subprogramul apelat. Deoarece un parametru IN se comportă ca o constantă este interzisă atribuirea unei valori parametrului respectiv. De exemplu, următoarea atribuirea generează eroare de compilare:

PROCEDURE cont_debit(id_cont IN INTEGER, suma IN REAL) IS suma_min CONSTANT REAL DEFAULT 10.0; comision CONSTANT REAL DEFAULT 0.50; BEGIN

IF suma < suma_min THEN suma := suma + comision; -- se generează eroare de -- compilare deoarece tipul

END IF; -- param. suma este IN ...

END cont_debit; Parametrul actual corespunzător unui parametru formal IN poate fi o constantă, un literal, o variabilă iniţializată sau o expresie. Pentru un parametru IN se poate specifica o valoare implicită. Un parametru de tip OUT permite returnarea de valori către programul apelant şi se comportă asemeni unei variabile locale. Se poate modifica valoarea unui parametru OUT sau se poate referi parametrul în orice mod, aşa cum se observă şi din exemplul următor: PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS data_ang DATE; bonus_lipsa EXCEPTION; BEGIN SELECT sal * 0.10, hiredate INTO bonus, data_ang FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE bonus_lipsa; END IF; IF MONTHS_BETWEEN(SYSDATE, data_ang) > 60 THEN bonus := bonus + 500; END IF; ... EXCEPTION WHEN bonus_lipsa THEN ... END calc_bonus; Parametrul actual corespunzător unui parametru formal OUT trebuie să fie o variabilă; dacă este o constantă sau o expresie se generează eroare. Înainte de părăsirea subprogramului trebuie asignate explicit valori tuturor parametrilor OUT, în caz contrar aceştia vor avea valori NULL. Un parametru IN OUT permite pasarea de valori către subprogram şi returnarea de valori către programul apelant. Parametrul actual corespunzător unui parametru IN OUT trebuie să fie o variabilă; dacă este o constantă sau o expresie se generează eroare.

Proceduri şi funcţii stocate Procedurile stocate sunt create cu instrucţiunea CREATE PROCEDURE, iar funcţiile stocate cu instrucţiunea CREATE FUNCTION. Utilizarea instrucţiunii CREATE OR REPLACE are ca efect ştergerea subprogramului (dacă există deja) şi recrearea lui în baza de date. Ambele tipuri de subprograme permit aplicaţiilor să execute o parte din funcţii pe server, ceea ce conduce la îmbunătăţirea considerabilă a performanţelor. Diferenţa dintre un subprogram stocat şi un subprogram declarat într-un bloc PL/SQL (subprogram local) este că subprogramul stocat poate fi apelat din orice aplicaţie, în timp ce un subprogram local poate fi apelat doar in blocul părinte. Funcţiile şi procedurile trebuie folosite întotdeauna pentru a creşte modularitatea codului scris. Dacă există o porţiune de cod care se repetă în interiorul unui bloc PL/SQL trebuie avută în vedere posibilitatea includerii lui într-o funcţie sau procedură locală. Dacă aceasta trebuie să fie accesibilă altor aplicaţii trebuie creată în baza de date o procedură, respectiv funcţie stocată. Pachete Un pachet este o colecţie de obiecte corelate logic ce sunt stocate în baza de date şi partajează variabile comune, precum şi proceduri sau funcţii locale. Un pachet este alcătuit din două componente: 1. specificaţia pachetului – conţine informaţii despre conţinutul acestuia (definiţii de constante,

variabile, cursori, subprograme); definiţiile ce apar în specificaţia unui pachet sunt publice, ele fiind vizibile altor aplicaţii.

2. corpul pachetului - defineşte cursorii şi subprogramele definite în specificaţia pachetului, conţinând detalii de implementare şi declaraţii private ce nu pot fi accesate de alte aplicaţii. Corpul pachetului este opţional dacă în specificaţie nu sunt declarate subprograme sau cursori. Obiectele care nu sunt declarate în specificaţie sunt locale pachetului (obiecte private). După secţiunea declarativă poate exista o secţiunea executabilă, ce conţine cod de iniţializare care este executat când pachetul este referit pentru prima dată într-o sesiune.

Specificaţia şi corpul pachetului sunt compilate separat şi sunt stocate ca două obiecte distincte în dicţionarul de date. Sintaxa pentru crearea unui pachet este următoarea: CREATE [OR REPLACE] PACKAGE nume_pachet {IS | AS} [def_tip_colectie ...] [def_tip_inregistrare ...] [declaratie_colectie ...] [declaratie_constanta ...] [declaratie_exceptie ...] [declaratie_inregistrare ...] [declaratie_variabila ...] [specificatie_cursor ...] [specificatie_functie ...] [specificatie_procedura ...] END [nume_pachet];

[CREATE [OR REPLACE] PACKAGE BODY nume_pachet {IS | AS} [def_tip_colectie ...] [def_tip_Inregistrare ...] [declaratie_colectie ...] [declaratie_constanta ...] [declaratie_exceptie ...] [declaratie_inregistrare ...] [declaratie_variabila ...] [corp_cursor ...] [corp_functie ...] [corp_procedura ...] [BEGIN instructiuni] END [nume_pachet];] Prima instrucţiune creează specificaţia pachetului, iar a doua corpul pachetului. Exemplul următor creează pachetul my_pack. 1 CREATE OR REPLACE PACKAGE my_pack IS 2 PROCEDURE p(x NUMBER); 3 FUNCTION f RETURN VARCHAR2; 4 var1 NUMBER; 5 var2 VARCHAR2(20); 6 END my_pack; 7 / 8 CREATE OR REPLACE PACKAGE BODY my_pack IS 9 PROCEDURE p(x NUMBER) IS 10 BEGIN 11 var1 := x; 12 END p; 13 FUNCTION f RETURN VARCHAR2 IS 14 BEGIN 15 var2 := ’HELLO’; 16 RETURN var2 || ’ ’ || TO_CHAR(var1); 17 END f; 18 BEGIN 19 DBMS_OUTPUT.PUT_LINE(’Pachetul my_pack a fost referit!’); 20 END my_pack; 21 / Liniile 1 – 6 creează specificaţia pachetului, iar liniile 8 – 20 corpul pachetului. Deoarece în specificaţia pachetului sunt declarate două subprograme (procedura p şi funcţia f) şi două variabile (var1 şi var2), aceste componente sunt accesibile altor programe. Procedurile şi funcţiile declarate în specificaţie (liniile 2 şi 3) trebuie să apară în corpul pachetului (liniile 9–12, 13–17), în caz contrar se generează eroare. Linia 19 conţine codul ce va fi executat atunci când se va referi pentru prima dată pachetul my_pack.

Un apel la un pachet va fi executat de server în trei paşi: 1. se verifică dreptul de acces al utilizatorului – se confirmă că utilizatorul are dreptul să

execute subprogramul; 2. se verifică validitatea subprogramului apelat – se verifică în dicţionarul de date dacă

subprogramul este valid sau nu. Dacă obiectul este invalid, el este automat recompilat înainte de a fi executat;

3. se execută subprogramul. Pentru a face referire la subprogramele şi obiectele dintr-un pachet se utilizează notaţia cu punct: nume_pachet.nume_variabila nume_pachet.nume_subprogram

În exemplul următor este ilustrat modul de utilizare a pachetului my_pack: BEGIN my_pack.p(1); DBMS_OUTPUT.PUT_LINE(my_pack.f); END; /

Pachetul my_pack a fost referit! HELLO 1 PL/SQL procedure successfully completed Pentru a recompila un pachet se utilizează comanda ALTER PACKAGE împreună cu cuvântul cheie COMPILE. Această cerere explicită de recompilare elimină necesitatea recompilării implicite în timpul execuţiei şi previne apariţia oricărei erori de compilare asociate execuţiei, precum şi scăderea performanţelor. Prin recompilarea unui pachet se recompilează toate obiectele definite în cadrul său. Următoarele două comenzi recompilează corpul pachetului my_pack, respectiv întregul pachet. ALTER PACKAGE my_pack COMPILE BODY; ALTER PACKAGE my_pack COMPILE PACKAGE; Un pachet/subprogram se consideră invalid dacă, de la ultima compilare a pachetului, respectiv subprogramului: • unul sau mai multe obiecte referite în subprogram/pachet (tabel, vedere, subprogram) au fost

modificate sau eliminate; • utilizatorului proprietar i-a fost revocat un privilegiu necesar pachetului/subprogramului; Un pachet/subprogram este valid dacă nu a fost invalidat de una din operaţiile menţionate mai sus. La apelul unui subprogram valid, de sine stătător sau împachetat, se execută codul compilat. Dacă subprogramul este invalid, înainte de execuţie va fi recompilat automat. Afişarea informaţiilor referitoare la obiectele din baza de date Pentru a obţine informaţii despre subprogramele/pachetele create de un utilizator se pot interoga următoarele vederi din dicţionarul de date: USER_OBJECTS, USER_OBJECT_SIZE, USER_SOURCE. Coloana STATUS din vederea USER_OBJECTS precizează dacă un anumit

obiect este valid sau invalid. Pentru a vizualiza toate obiectele stocate în schema utilizatorului curent, precum şi mărimea acestora se interoghează vederea USER_OBJECT_SIZE. Coloana TEXT din vederea USER_SOURCE conţine un rând pentru fiecare linie de cod sursă din obiectul stocat, iar coloana LINE reprezintă numărul liniei. Vederea USER_ERRORS poate fi interogată pentru a găsi informaţii despre obiectele stocate a căror compilare a eşuat. O alternativă la interogarea acestei vederi constă în utilizarea comenzii show errors, care va afişa erorile generate la compilare. În toate vederile menţionate anterior (cu excepţia vederii USER_OBJECTS), coloana NAME reprezintă numele obiectului, iar coloana TYPE tipul său (procedură, funcţie, specificaţie pachet, corp pachet). În vederea USER_OBJECTS coloanele corespondente sunt denumite OBJECT_NAME, respectiv OBJECT_TYPE. Următoarea interogare afişează numele, tipul şi starea obiectelor stocate de tip procedură, funcţie şi pachet. SQL> SELECT object_name, object_type, status 2 FROM user_objects

3 WHERE object_type IN ('PROCEDURE','FUNCTION','PACKAGE', 'PACKAGE BODY');

OBJECT_NAME OBJECT_TYPE STATUS ---------------- --------------- ------- TEST PROCEDURE VALID CHECK_SAL FUNCTION VALID MY_PACK PACKAGE VALID MY_PACK PACKAGE BODY VALID MARIRE_SAL PROCEDURE VALID Probleme 1. Creaţi tabelul vid employee, având aceeaşi structură ca tabelul scott.emp. 2. Scrieţi un bloc PL/SQL anonim care să insereze în tabelul employee o înregistrare, valorile

atributelor fiind introduse de la tastatură. Operaţia de inserare în tabel va fi efectuată de o procedură locală.

3. Scrieţi un bloc PL/SQL anonim care să afişeze numărul de angajaţi din tabelul scott.emp ce lucrează într-un anumit departament, numărul departamentului fiind introdus de la tastatură. Va fi utilizată o funcţie locală care, primind un număr de departament, returnează numărul de angajaţi din acel departament.

4. Ştergeţi tabelul employee creat la punctul 1. Creaţi un nou tabel employee ce conţine aceleaşi înregistrări ca tabelul scott.emp. Creaţi tabelul jurnal ce va conţine număr angajat, nume angajat, salariu vechi, salariu nou şi data modificării salariului. Se vor alege nume şi tipuri de date convenabile pentru cele cinci atribute.

5. Scrieţi o procedură stocată care măreşte cu 20% salariile angajaţilor din tabelul employee ce lucrează în departamentul furnizat ca parametru. Pentru fiecare înregistrare modificată inseraţi în tabelul jurnal o înregistrare ce conţine numărul şi numele angajatului, salariul vechi, salariul nou şi data modificării salariului (cu ajutorul funcţiei SYSDATE). Testaţi procedura scrisă.

6. Scrieţi o funcţie stocată care, primind ca parametru un număr de departament şi o sumă, returnează numărul de angajaţi din departament ce au comisionul mai mare decât valoarea pasată ca parametru. Se vor utiliza datele din tabelul employee.

7. Implementaţi pachetul my_pack care conţine următoarele componente: • o procedură care afişează numele angajaţilor din tabelul employee având o anumită

funcţie, funcţia fiind dată de parametrul procedurii. Dacă nu este găsit nici un angajat se va afişa un mesaj corespunzător.

• o procedură care şterge o înregistrare din tabelul employee, împreună cu înregistrarea corespunzătoare (dacă aceasta există) din tabelul jurnal. Ştergerea se face pe baza numărului de înregistrare furnizat ca parametru. Dacă nu există angajatul precizat se va afişa un mesaj de eroare.

• o funcţie care să returneze numerele departamentelor din tabelul employee şi salariile medii pe fiecare departament;

• o variabilă care memorează numărul de apelări ale celor trei subprograme de mai sus. 8. Testaţi pachetul de la punctul 7.

Date post:	01-Dec-2015
Category:	Documents
Upload:	isciuc-adrian
View:	126 times
Download:	6 times

Baze de Date Indrumar de Laborator

Documents