comenzi sql

29
Constrangeri de integritate Oracle permite constrangerilor de integritate sa fie definite pentru ta- bele si coloanelor sa forteze reguli sigure, inauntrul unei tabele sau intre tabele. Constrangerile sunt folosite: de serverul Oracle sa forteze reguli la nivelul tabelei oricand este inserata o linie, actualizata sau stearsa din acea tabela. Constrangerea trebuie sa fie satisfacuta pentru ca operatiile sa reuseasca. pentru a preveni stergerea unei tabele daca sunt posesiuni din alte tabele. prin unelte Oracle, ca Oracle Forms, pentru a furniza reguli pentru utilizarea intr-o aplicatie. Constrangerile sunt clasate dupa cum urmeaza: Constrangeri de tabela Acestea pot referi una sau mai multe coloane si sunt definite SEPARAT de definitiile coloanelor din tabela. Constrangeri de coloana Acestea refera o singura coloana si sunt definite INAUNTRUL specificatiei pentru coloana posesoare. Constrangerile pot fi adaugate unei tabele dupa crearea ei si deasemenea temporar dezactivate (vezi comanda ALTER TABLE in capitolul urmator).Toate detaliile despre constrangeri sunt stocate in Dictionarul de Date. Fiecarei constrangeri ii este repartizat un nume. Iti este mai usor sa suplimentezi una tu singur, astfel ca poate fi mai usor referita mai tarziu, dar daca nu, atunci un nume este generat automat pe forma: SYS_Cn unde n este un numar unic. Cuvantul cheie CONSTRAINT iti permite sa numesti o noua constrangere tu insuti. Tipuri de constrangeri Puteti defini urmatoarele tipuri de constrangeri: NULL/NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY(integritatea de referinta) CHECK

Upload: ciobanu2

Post on 12-Jun-2015

7.231 views

Category:

Documents


25 download

TRANSCRIPT

Page 1: Comenzi SQL

Constrangeri de integritate

Oracle permite constrangerilor de integritate sa fie definite pentru ta- bele si coloanelor sa forteze reguli sigure, inauntrul unei tabele sau intre tabele. Constrangerile sunt folosite:

de serverul Oracle sa forteze reguli la nivelul tabelei oricand este inserata o linie, actualizata sau stearsa din acea tabela. Constrangerea trebuie sa fie satisfacuta pentru ca operatiile sa reuseasca.

pentru a preveni stergerea unei tabele daca sunt posesiuni din alte tabele. prin unelte Oracle, ca Oracle Forms, pentru a furniza reguli pentru utilizarea intr-o

aplicatie. Constrangerile sunt clasate dupa cum urmeaza:

Constrangeri de tabela

Acestea pot referi una sau mai multe coloane si sunt definite SEPARAT de definitiile coloanelor din tabela.

Constrangeri de coloana

Acestea refera o singura coloana si sunt definite INAUNTRUL specificatiei pentru coloana posesoare.

Constrangerile pot fi adaugate unei tabele dupa crearea ei si deasemenea temporar dezactivate (vezi comanda ALTER TABLE in capitolul urmator).Toate detaliile despre constrangeri sunt stocate in Dictionarul de Date. Fiecarei constrangeri ii este repartizat un nume. Iti este mai usor sa suplimentezi una tu singur, astfel ca poate fi mai usor referita mai tarziu, dar daca nu, atunci un nume este generat automat pe forma: SYS_Cn

unde n este un numar unic. Cuvantul cheie CONSTRAINT iti permite sa numesti o noua constrangere tu insuti.

Tipuri de constrangeri

Puteti defini urmatoarele tipuri de constrangeri: NULL/NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY(integritatea de referinta) CHECK

Constrangerea UNIQUE

Aceasta desemneaza o coloana sau o combinatie de coloane ca o cheie uni- ca.Doua linii in aceeasi tabela nu pot avea aceeasi valoare pentru aceasta cheie.NULL-urile sunt permise daca cheia unica este bazata pe o singura co- loana. Sintaxa constrangerii de tabela : [CONSTRAINT nume constrangere] UNIQUE (Coloana, Coloana, ...)

Sintaxa constrangerii de coloana : [CONSTRAINT nume constrangere] UNIQUE

De exemplu, pentru a va asigura ca nu sunt 2 nume de departamente identice la o singura locatie:

CREATE TABLE DEPT (DEPTNO NUMBER, DNAME VARCHAR2(9), LOC VARCHAR2(10), CONSTRAINT UNQ_DEPT_LOC UNIQUE(DNAME,LOC))

Page 2: Comenzi SQL

In exemplul de mai sus, constrangerea UNQ_DEPT_LOC este o constrangere de ta- bela. Notati ca virgula precede detaliile. O constrangere unica il face pe ORACLE sa creeze un singur index pentru a manui regula. Indecsii sunt discu- tati mai tarziu.

Constrangere de cheie primara

Ca si la cheile unice, o cheie primara forteaza unicitatea unei coloane sau combinatii de coloane implicate si un index unic este creat pentru a conduce aceasta. Totusi poate fi o singura cheie primara pe o tabela, si aceasta este cunoscuta ca fiind cheia definitiva prin care liniile in tabela sunt i- dentificate individul. NULL-urile nu sunt permise in coloanele de chei primare. Sintaxa constrangerii de tabela : [CONSTRAINT nume constrangere] PRIMARY KEY (Coloana, Coloana, ...)

Sintaxa constrangerii de coloana : [CONSTRAINT nume constrangere] PRIMARY KEY

Notati ca aceeasi combinatie de coloane nu poate fi folosita si pentru o cheie primara si pentru una unica. Urmatorul exempludefineste DEPTNO ca o cheie primara folosind o constrangere de coloana:

CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT DEPT_PRIM PRIMARY KEY, ...)

Constrangere de cheie externa

Cheile externe furnizeaza reguli de integritate de referinta inauntrul unei tabele sau intre tabele. O cheie exeterna este folosita intr-o relatie cu fiecare cheie primara sau unica oriunde si poate fi folosita, de exemplu, pentru a preveni stergerea unui departament in DEPT daca angajatii exista cu acelasi numar de departament in EMP. Sintaxa constrangerii de tabela : [CONSTRAINT nume constrangere] FOREIGN KEY (Coloana, Coloana, ...) REFERENCES tabela (Coloana, Coloana, ...)

Sintaxa constrangerii de coloana : [CONSTRAINT nume constrangere] REFERENCES tabela (Coloana)

Notati ca, cuvintele 'FOREIGN KEY' nu sunt folosite versiunea constrangerii de coloana a sintaxei. ------------ Exemplul 1 | DEPT | (parinte) ------------ | | /|\ DEPTNO ------------ | EMP | (fiu) ------------

Pentru a stabili relatia dintre EMP si DEPT astfel incat EMP.DEPTNO este cheia externa, si fiecare angajat trebuie sa aiba un numar valid de departament care este cunoscut in DEPT:

CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)

Optiunea ON DELETE CASCADE

Ca rezultat al constrangerii de tabela de mai sus, (care ar fi putut la fel de bine sa fie definita ca o constrangere de coloana), un departament in DEPT nu ar fi putut fi sters daca liniile exista in EMP cu aceeasi valoare DEPTNO. Alternativ, puteti cere ca angajatii corespunzatori sa fie stersi a- utomat daca departamentul parinte in DEPT este sters. Aceasta este realizata adaugand clauza ON DELETE CASCADE.

Page 3: Comenzi SQL

CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) ON DELETE CASCADE

Exemplul 2 -------------- EMPNO | EMP | . . -------------- . \|/ . . . . . MGR . . . .

Pentru a va asigura ca fiecarei linii de angajat in EMP ii este dat un numar de manager (MGR) pentru un angajat existent valid:

CREATE TABLE EMP (EMPNO NUMBER(4) PRIMARY KEY, ... MGR NUMBER(4) CONSTRAINT EMP_MGR REFERENCES EMP(EMPNO), ...

Constrangerea de verificare (CHECK)

Constrangerea CHECK defineste explicit o conditie pe care fiecare linie trebuie sa o satisfaca(sau sa o faca necunoscuta datorita unui NULL). Condi- tia poate folosi aceleasi constructii ca acelea intr-o restrictie de cerere, cu urmatoarele exceptii:

subcererile nu sunt permise referirile la pseudo-coloane ca SYSDATE nu sunt permise

Sintaxa: [CONSTRAINT nume constrangere] CHECK (conditie)

Alte optiuni ale constrangerilor

DISABLE Adaugand DISABLE unei definitii de constrangere inseamna ca ORACLE nu o forteaza. Constrangerea poate fi inca citita de uneltele ORACLE pentru a construi reguli intr-o aplicatie si puteti face posibila constrangerea mai tarziu prin comanda ALTER TABLE. CREATE TABLE EMP ( . . . . . , ENAME VARCHAR2(10) CONSTRAINT CHK_UPP_NAM CHECK(ENAME-UPPER(ENAME)) DISABLE, . . . . . . . ) ;

EXCEPTIONS Identifica o tabela existenta unde este plasata INTO nume tabela informatia despre liniile care incalca constrangerea.

CREATE TABLE EMP ( . . . . . , ENAME VARCHAR2(10) CONSTRAINT CHK_UPP_NAM CHECK(ENAME-UPPER(ENAME)) EXCEPTIONS INTO CON_VIOLATE, . . . . . . . ) ;

In final, notati ca tabelele referite intr-o constrangere trebuie sa existe in aceeasi baza de date. Daca ele apartin unui utilizator diferit atunci po- sesorul trebuie specificat ca un prefix. Iata un exemplu complet al constructiei tabelei EMP cu constrangeri :

CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT EMP_PRIM PRIMARY KEY,

Page 4: Comenzi SQL

ENAME VARCHAR2(10) CONSTRAINT ENAME_CONS CHECK(ENAME=UPPER(ENAME)), JOB VARCHAR2(10), MGR NUMBER(4) CONSTRAINT EMP_MGR REFERENCES EMP(EMPNO), HIREDATE DATE DEFAULT SYSDATE, SAL NUMBER(7,2) CONSTRAINT SAL_CONS NOT NULL, COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT DEPTNO_CONS NOT NULL, CONSTRAINT EMP_DEPT FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO))

Crearea unei tabele cu linii din alta tabelaExista o a doua forma a declaratiei CREATE TABLE in care tabela este creata cu linii potrivite, derivate din alta tabela:

CREATE TABLE DEPT [(nume-coloana ,. . . .)] AS SELECT declaratie

Tabela va fi creata cu coloane specificate si linii recuperate din de- claratia SELECT inserata.

Numai constrangerile NULL/NOT NULL sunt mostenite din tabela selecta- ta.

Daca toata coloanele in declaratia SELECT au nume bine definite (nu sunt expresii s.a.m.d.) specificatiile coloanei pot fi omise.

Daca sunt date specificatiile coloanei, atunci numarul de coloane tre- buie sa fie egal cu numarul de articole in lista SELECT.

Pentru a crea o tabela DEPT30 care tine numerele angajatilor, nume, job-uri si salariile angajatilor din departamentul 30, introduceti:

CREATE TABLE DEPT30 AS SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE DEPTNO = 30;

Table created. Pentru a vedea descrierea lui DEPT30, introduceti:

DESC DEPT30

Pentru a crea o tabela tinand numele angajatului, salariul si detalii de grad, introduceti:

CREATE TABLE EMP_SALS (NAME,SALARY,GRADE) AS SELECT ENAME, SAL, GRADE FROM EMP, SALGRADE WHERE EMP.SAL BETWEEN LOSAL AND HISAL;

Table created.

DESC EMP_SALS;

Pentru a afisa continutul tabelei EMP SALS, introduceti:

Page 5: Comenzi SQL

SELECT * FROM EMP_SALS;

Exercitii

1. Creati o tabela numita PROJECTS, cu coloanele specificate ca mai

jos.Pe langa aceasta , definiti PROJID ca, coloana de CHEIE PRIMARA , si asigurati-va ca datele P_END_DATE nu sunt mai recente decat datele P_START_DATE.

2. Creati o a doua tabela, ASSIGNMENTS, ca cea de mai jos. Definiti-i coloana PROJID ca o cheie externa care refera tabela PROJECTS. Coloana EMPNO a tabelei dumneavoastra este o viitoare cheie externa a lui EMP. Aceste doua coloane nu ar trebui sa permita valori NULL (PROJID si EMPNO).

Sunt alte constrangeri pe care ar trebui sa le angajam in aceste tabele.

3. Folositi comanda DESCRIBE pentru a verifica definitiile coloanelor.

Solutii Fiecare din constrangerile aratate mai jos ar fi putut fi definite ca orica- re constrangere de tabela sau constrangere de coloana.

1. CREATE TABLE PROJECTS ( PROJID NUMBER(4) CONSTRAINT PROJ_PRIM PRIMARY KEY, P_DESC VARCHAR2(20), P_START_DATE DATE, P_END_DATE DATE, BUDGET_AMOUNT NUMBER(7,2), MAX_NO_STAFF NUMBER(2), CONSTRAINT P_DATE_RULE CHECK(P_START_DATE <= P_END_DATE))

2. CREATE TABLE ASSIGNMENTS (PROJID NUMBER(4) NOT NULL REFERENCES PROJECTS (PROJID), EMPNO NUMBER(4) NOT NULL REFERENCES EMP (EMPNO), A_START_DATE DATE, A_END_DATE DATE, BILL_RATE NUMBER(4,2), ASSIGN_TYPE VARCHAR2(2))

Management-ul de tabela si Dictionarul de Date

In continuare vom vedea comenzile pentru modificarea si mutarea tabelelor si constrangerilor lor. Deasemenea, vom vedea cum informatii despre baza de date pot fi extrase din Dictionarul de Date.

Modificarea unei tabeleFolositi comanda ALTER TABLE pentru a schimba definitia unei tabele. Sintaxa: ALTER TABLE nume-tabela

Page 6: Comenzi SQL

[ ADD ](specificator coloana[constrangere de coloana])[ENABLE clauza ] [MODIFY ][DISABLE clauza] [DROP optiuni]

Clauza ADD

Folositi cuvantul cheie ADD pentru a adauga o coloana si/sau constrangeri pentru o tabela existenta. Pentru a adauga o coloana tabelei EMP care va tine numele sotului angajatului, introduceti:

ALTER TABLE EMP ADD (SPOUSES_NAME CHAR (10));

Table altered. Pentru a vedea descrierea tabelei revizuite, introduceti:

DESCRIBE EMP;

Pentru a adauga o constrangere de tabela unei tabele existente, care specifi- ca daca salariul lunar nu trebuie sa depaseasca 5000$, introduceti:

ALTER TABLE EMP ADD(CHECK(SAL <=5000));

Clauza MODIFY

Folositi cuvantul cheie MODIFY pentru a modifica definitia unei coloane existente.

ALTER TABLE nume MODIFY (tip coloana [NULL])Pentru a schimba lungimea lui ENAME la 25 de caractere, introduceti :

ALTER TABLE EMP MODIFY (ENAME CHAR (25));Table altered.

DESCRIBE EMP;

Sunt 4 schimbari pe care nu le puteti face

1. Nu puteti schimba o coloana continand null-uri din NULL in NOT NULL. 2. Nu puteti adauga o noua coloana care este NOT NULL. Faceti-o null,

umpleti-o complet si apoi schimbati-o in NOT NULL. (de verificat!)3. Nu puteti sa scadeti dimensiunea unei coloane sau sa-i schimbati tipul

de date, numai daca nu contine date. (de verificat!)4. Nu puteti folosi optiunea MODIFY pentru a defini constrangeri pe o

coloana exceptand NULL/NOT NULL. Pentru a modifica alte constrangeri trebuie sa le eliminati si apoi sa le a-

daugati specificand modificarile.

Clauza DROPFolositi clauza DROP pentru a muta o constrangere din alta tabela. Sintaxa: ALTER TABLE nume tabela DROP [CONSTRAINT nume constrangere ] [CASCADE] [PRIMARY KEY ] [UNIQUE (coloana, coloana, ...)]De exemplu:

ALTER TABLE EMP DROP CONSTRAINT EMP_MGR;

Page 7: Comenzi SQL

ALTER TABLE EMP DROP PRIMARY KEY;

Optiunea CASCADE (in clauza DROP)

Optiunea CASCADE a clauzei DROP face ca orice constrangere dependenta sa fie deasemenea eliminata. De exemplu:

ALTER TABLE DEPT DROP PRIMARY KEY CASCADE;

ar putea deasemenea sa faca ca constrangerea chaii exetrne din EMP>DEPTNO sa fie eliminata.

Clauza ENABLE/DISABLEAceasta clauza a comenzii ALTER TABLE permite constrangerilor sa fie fa- cute posibile sau dezactivate fara a le elimina sau recrea. Sintaxa: [DISABLE] [ UNIQUE (coloana, coloana, ...) ] [CASCADE] [ENABLE ] [ PRIMARY KEY ] [ CONSTRAINT nume constrangere ]Ca si la clauza DROP, adaugarea cuvantului cheie CASCADE semnifica ca con- strangerile dependente sunt deasemenea afectate. De exemplu:

ALTER TABLE DEPT DISABLE CONSTRAINT DEPT_PRIM CASCADE;

Alte comenzi LDD

Eliminarea unei tabelePentru a sterge definitia unei tabele ORACLE, folositi comanda DROP TABLE. Sintaxa: DROP TABLE nume tabela [CASCADE CONSTRAINTS]De exemplu:

DROP TABLE EMP;Stergerea unei tabele duce la pierderea tuturor datelor din ea si a tu- turor indecsilor asociati ei. Optiunea CASCADE CONSTRAINTS va sterge deaseme- nea referirile la constrangerile de integritate dependente.

Note:

Toate datele vor fi deasemenea sterse din tabela. Orice VIEWS sau SYNONYMS vor ramane, dar vor deveni invalide. Orice tranzactii nerezolvate sunt realizate. Numai creatorul tabelei sau DBA poate sa o stearga.

Comanda COMMENTFolositi comanda COMMENT pentru a insera un comentariu pana la 255 de caractere, despre o tabela sau coloana, in dictionarul de date. Pentru a adauga un comentariu unei tabele numita EMP, introduceti:

COMMENT ON TABLE EMP IS 'Employee Information';Pentru a adauga un comentariu pe coloana EMPNO in tabela EMP, introduceti:

COMMENT ON COLUMN EMP.EMPNO IS 'Unique employee number';Pentru a sterge un comentariu, emiteti comanda COMMENT fara un comentariu:

COMMENT ON COLUMN EMP.EMPNO IS '';,/pre>

Page 8: Comenzi SQL

Pentru a vedea comentariul, selectati coloana COMMENTS din una din vederile dictionarului:ALL_COL_COMMENTS sau USER_COL_COMMENTS. Dictionarul de Date este acoperit mai tarziu in acest capitol.

Comanda RENAME

Comanda RENAME este folosita de creatorul lui TABLES, VIEWS si SYNONYMS pentru a scimba numele obiectului bazei de date.

Pentru a redenumi un obiect al bazei de date sintaxa este:

RENAME vechi TO nou;Pentru a redenumi tabela EMP in EMPLOYEE, introduceti: RENAME EMP TO EMPLOYEE;Pentru a redenumi tabela SALGRADE, introduceti: RENAME SALGRADE TO EMPLOYEE_GRADES;Este important de notat ca orice aplicatii/programe/rapoarte care se refera la obiecte ce au fost redenumite, trebuie amendate.

Comanda TRUNCATE TABLEAceasta comanda va permite sa stergeti toate liniile dintr-o tabela. Notati ca comanda DELETE poate deasemenea realiza aceasta ca o parte a tran- zactiei de manipulare a datelor (acoperita in capitolul urmator), dar comanda TRUNCATE TABLE umple o tabela mai eficient ca o actiune de definire de date, pana cand nu este retinuta nici o informatie de rollback. Sintaxa: TRUNCATE TABLE nume tabela [REUSE STORAGE]

Optiunea REUSE STORAGE pastreaza spatiul din liniile sterse pentru reutiliza- rea tabelei. Implicit, acest spatiu este eliberat.

Limbajul de manipulare a datelor

In continuare vom explica cum se fac schimbari liniilor intr-o tabela, cum se adauga noi linii sau cum se sterg.

Inserarea de noi linii intr-o tabelaComanda INSERT este folosita pentru a adauga linii unei tabele Sintaxa comenzii INSERT este: INSERT INTO nume tabela [ (coloana,coloana,....)] VALUES (valoare,valoare,....);

Este posibila inserarea unei noi linii cu valori in fiecare coloana, in care caz lista de coloane nu este ceruta. Este recomandat ca COLUMN LIST sa fie intotdeauna specificata. Daca lista nu este specificata, software-ul va cere modificari oriunde definitia tabelei este modificata. Pentru a insera un nou departament, introduceti:

INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (50,'MARKETING','SAN JOSE');

Nota aceasta comanda adauga o singura linie unei tabele. Pentru a intra intr-un departament nou, omitand numele departamentului, lista de coloane trebuie specificata:

INSERT INTO DEPT (DEPTNO,LOC) VALUES (50,'SAN JOSE');

Alternativ, daca numele departamentului nu este cunoscut, un NULL ar putea fi specificat:

Page 9: Comenzi SQL

INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (50,NULL,'SAN JOSE');

Valorile CHARACTER si DATE trebuie puse in ghilimele simple.

Folosirea Variabilelor de Substitutie pentru a insera linii

Dupa cum am mentionat anterior, INSERT este o comanda pentru o singura lini- e. Folosind variabile de substitutie este posibil sa se mareasca viteza de intrare.

INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (&D_NUMBER, '&D_NAME', '&LOCATION');

Cand comanda este rulata, valorile sunt afisate de fiecare data.

Inserarea informatiilor de data si timp

Cand se insereaza o valoare DATE, formatul DD-MON-YY este de obicei folosit. Data deasemenea contine informatii de timp, care daca nu sunt specificate, implicit devin miezul noptii (00:00:00). Daca o data trebuie introdusa in alt secol si un timp specific este cerut deasemenea, folositi functia TO_DATE:

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7658, 'MASON', 'ANALYST', 7566, TO_DATE('24/06/2084 9:30','DD/MM/YYYY HH:MI'), 3000, NULL, 20);

Copierea de linii din alta tabela

INSERT INTO tabela [(coloana, coloana, ....)] SELECT lista-select FROM tabela(e)

Aceasta forma a declaratiei INSERT va permite sa inserati cateva linii intr-o tabela unde valorile sunt derivate din continutul tabelelor existente in baza de date. Pentru a copia toate informatiile din departamentul 10 in tabela D10HISTORY, introduceti:

INSERT INTO D10HISTORY (EMPNO,ENAME,SAL,JOB,HIREDATE FROM EMP WHERE DEPTNO=10;

Notati ca cuvantul cheie 'VALUES' nu este folosit aici.

Actualizarea liniilor

Declaratia UPDATE va permite sa schimbati valori in liniile unei tabele. UPDATE tabela[alias] SET coloana [, coloana...] = {expresie, subcerere} [WHERE conditie];

De exemplu: Pentru a actualiza linia lui Scott, introduceti:

UPDATE EMP SET JOB='SALESMAN', HIREDATE = SYSDATE,

Page 10: Comenzi SQL

SAL = SAL*1.1 WHERE ENAME = 'SCOTT';

1 record updated.

Daca clauza WHERE este omisa, toate liniile din tabela vor fi actualizate. Este posibil sa folositi subcereri inlantuite si subcereri corelate in decla- ratia UPDATE. Sa presupunem ca ati avut o cifra noua de comisioane pentru angajati siguri. De exemplu, tabela COMMISSION de mai jos este folosita pentru a actualiza li- nii sigure ale tabelei EMP: COMMISSION EMP

EMPNO COMM EMPNO COMM ------ ---- ----- ----- 7499 1100 7499 300 7654 500 7654 1400 7844 3500 7844 0 7844 2000 7844 1500

Schimbarile listate in tabela COMMISSION pot fi aplicate tabelei EMP, folosind o subcerere corelata si o subcerere inlantuita, ca mai jos: Exemplul 1:

UPDATE EMP SET COMM = (SELECT COMM FROM COMMISSION C WHERE C.EMPNO = EMP.EMPNO) WHERE EMPNO IN (SELECT EMPNO FROM COMMISSION);

3 records updated.

Tabela COMMISSION poate contine mai mult decat o intrare pentru fiecare angajat, ca in exemplul de mai jos : COMISSION EMPNO COMM ----- ---- 7499 1100 7654 500 7654 100 7844 2000 7844 1500

Daca doriti sa inlocuiti (REPLACE) valorile din tabela EMP pentru comision cu comisionul TOTAL pentru fiecare angajat listat in tabela COMISSION, atunci puteti utiliza urmatorul SQL : Exemplul 2:

UPDATE EMP SET COMM = ( SELECT SUM(COMM) FROM COMISSION C WHERE C.EMPNO = EMP.EMPNO) WHERE EMPNO IN (SELECT EMPNO FROM COMISSION);

3 inregistrari modificate.

Tabela EMP reflecta comisioanele modificate :

EMP EMPNO COMM ----- ---- 7499 1100 7654 600 7844 3500

Page 11: Comenzi SQL

O alta posibilitate este cea de a adauga (ADD) la valorile comisionului in tabela COMISSION la comisioanele existente in tabela EMP mai mult decat inlocuirea lor. Exemplul 3 realizeaza acest lucru : Exemplul 3:

UPDATE EMP SET COMM = ( SELECT SUM(COMM) + EMP.COMM FROM COMISSION C WHERE C.EMPNO = EMP.EMPNO) WHERE EMPNO IN (SELECT EMPNO FROM COMISSION);

Tabela EMP reflecta comisioanele schimbate :

EMP EMPNO COMM ----- ---- 7844 3500 7499 1400 7654 2000

Stergerea Coloanelor dintr-o Tabela

Comanda DELETE permite stergerea unei sau mai multor linii dintr-o tabela.

DELETE FROM tabela [WHERE conditie];

Pentru a sterge toate informatiile despre departamentul 10 din tabela EMP, introduceti :

DELETE FROM EMP WHERE DEPTNO = 10;

Daca clauza WHERE este omisa, atunci toate liniile vor fi sterse.

IMAGINI (VIEWS)Ce este o Imagine ?

O imagine este ca o fereastra prin intermediul careia datele din tebele pot fi vizualizate sau schimbate.

O imagine este derivata dintr-o alta imagine sau tabela la care este referita ca tabela de baza a imaginii - o tabela 'reala' cu date care sunt stocate fizic.

O imagine este stocata doar ca o secventa SELECT. Este o tabela virtuala - adica este o tabela care nu exista fizic, dar ii apare utilizatorului ca si cum ar exista.

O imagine nu are date proprii. Ea manipuleaza datele din tabela de baza asociata.

Imaginile sunt utile pentru urmatoarele scopuri :

Ingradirea accesului la o baza de date. Selectand dintr-o imagine (VIEW) putem afisa o parte restransa din baza de date.

Permite utilizatorilor sa faca cereri simple pentru obtinerea de rezultate din cereri complicate. De exemplu, imaginile permit utili- zatorilor sa selecteze informatii din tabele multiple fara cunoas- terea mecanismelor instructiunii JOIN.

Furnizeaza independenta datelor pentru useri ad-hoc si programele aplicatiilor. O imagine poate fi utilizata pentru a extrage trans- parent date din mai multe tebele. De asemenea imaginile permit ca aceleasi date sa fie vazute de utilizatori diferiti in feluri dife- rite.

Page 12: Comenzi SQL

Imagini Simple vs. Imagini Complexe

Clasificarea unei imagini simple : furnizeaza date dintr-o singura tabela nu contine functii sau grupuri (GROUP) de date.

O imagine complexa poate fi compusa din date care sunt : furnizate din mai multe tabele contine functii sau grupuri de date.

Comanda CREATE VIEW

CREATE [ OR REPLACE ] [FORCE] VIEW nume-view [(coloana1, coloana2, ...)] AS SELECT secventa-select [WITH CHECK OPTION [CONSTRAINT nume_constr]]

Coloana1, coloana2, etc. sunt numele date coloanelor in imaginesi trebiue sa corespunda elementelor din lista selectata. Pentru a crea o imagine simpla numita D10EMP, care contine unele detalii ale angajatilor din Departamentul 10, introduceti :

CREATE VIEW D10EMP AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO = 10;

Cand o imagine este creata, un mesaj este afisat : "View created." Imaginea poate fi utilizata ca orice tabela :

SELECT * FROM D10EMP ODRER BY ENAME;

Pentru a crea o imagine complexa numita DEPT_SUMMARY, care contine functii de grup si date din doua tabele :

CREATE VIEW DEPT_SUMMARY ( NAME, MINSAL, MAXSAL, AVSAL ) AS SELECT DNAME, MIN(SAL), MAX(SAL), AVG(SAL) FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME;

De notat faptul ca numele coloanelor alternative au fost specificate pentru imagine. Aceasta este o cerinta pentru cazul cand elementele SELECTate nu sunt conforme cu conventia denumirilor din tabele / coloane, sau daca orice coloana din imagine este derivata dintr-o functie sau expresie. De notat si faptul ca daca o coloana este utilizata intr-o cerere (ANNSAL), o imagine a unui alias al coloanei ( nume alternativ pentru coloana in imagine ) nu e necesar. De exemplu :

CREATE VIEW DEPT20 AS SELECT ENAME, SAL*12 ANNSAL FROM EMP WHERE DEPTNO = 20;

Page 13: Comenzi SQL

O imagine nu poate contine clauza ORDER BY. Clauza ORDER BY este specificata cand se executa SELECT din imagine.

Optiunea OR REPLACE

Aceasta optiune permite ca o imagine sa fie creata chiar daca exista una cu acelasi nume, astfel inlociundu-se vechea varianta a imaginii pentru proprietarul ei. Aceasta inseamna ca o imagine poate fi alterata fara a fi abandonata, recreandu-se si redand privilegiile obiectului.

Optiunea FORCE

Aceasta optiune creaza o imagine chiar daca tabela de baza nu exista, sau sunt prea putine drepturi pentru tabela respectiva. Oricum, tabela trebiue sa existe inainte ca imaginea sa poata fi utilizata.

Utilizarea unei Imagini cu Operatii DML

Imaginile sunt facilitati puternice deoarece ele ne permit sa executam verificari de integritate referentiale asupra datelor modificate prin utilizarea imaginilor. Clauza WITH CHECK OPTION specifica faptul ca INSERT si UPDATE executate pein intermediul imaginii nu permit crearea de linii noi pe care imaginea nu le poate selecta, si de aceea mermite constrangerile de integritate si vreificarile validarilor de date sa fie fortate asupra datelor ce vor fi inserate sau actualizate. In urmatorul exeplu, o imagine numita D10EMP este creata cu WITH CHECK OPTION. Aceasta inseamna ca doar linii care au DEPTNO = 10 pot fi inserate, actualizate sau sterse. Adica nici o secventa DML nu poate sa adreseze imaginea decat daca conditiile clauzei imaginii sunt indepli- nite.

CREATE VIEW D10EMP AS SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE DEPTNO = 10 WITH CHECK OPTION;

Daca exista o incercare de a executa actiuni DML asupra liniilor pe care imaginea nu le poate selecta intr-o clauza WHERE, se afiseaza eroare :

"ORA_01402 VIEW WITH CHECK OPTION WHERE_Clause voilation."

Urmatorul exemplu va valida intratea oricarei comenzi INSERT/UPDATE astfel incat numarul SAL sa fie intre 1000 si 2000, MGR va trebui sa aibe campul EMPNO valid, si DEPTNO sa existe in tabela DEPT.

CREATE VIEW EMP_DATA AS SELECT EMPNO, ENAME, JOB, MGR, SAL, DEPTNO FROM EMP WHERE SAL BETWEEN 1000 AND 2000 AND MGR IN ( SELECT DISTINCT EMPNO FROM EMP ) AND DEPTNO IN ( SELECT DEPTNO FROM DEPT ) WITH CHECK OPTION;

Pentru a crea o imagine care sa restranga accesul la detaliile personale ale unui angajat valid in tabela EMP, si timpul de acces la orele de servici, atunci introduceti urmatoarea comanda :

CREATE VIEW EMP_DETAILS

Page 14: Comenzi SQL

AS SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE ENAME = USER AND TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6 WITH CHECK OPTION;

Cand creati o imagine cu WITH CHECK OPTION, trebiue s-i atribuiti un nume restrictiei WITH CHECK OPTION.

CREATE VIEW EMPLOYEES( ID_NUMBER, NAME, DEPARTMENT) AS SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE DEPTNO IN ( SELECT DISTINCT DEPTNO FROM DEPT) WITH CHECK OPTION CONSTRAINT DEPT_CHECK;

Aceasta restrictie previne inserarea unei coloane in tabela EMP cu un numar care nu exista in tabela DEPT. Cand comanda CREATE VIEW este executata in seventa SELECT care urmeaza AS nu e executata; in loc este doar depozitata in dictionarul de date. Cand accesati date, via o imagine, ORACLE executa urmatoarele operatii :

recupereaza definitia imaginii din dictionarul de date verifica drepturile de acces converteste cererea imaginii intr-o operatie echivalenta asupra tabelei sau tabelelor

asociate. Cu alte cuvinte, datele sunt recuperate din, sau o actualizare este facuta asupra, tabelei(lor) de baza.

Odata ca imaginea a fost creata, trebuie sa se execute o cerere la Tabela Dictionarului de Date numita USER_VIEWS ca sa se vada conanda view. Acesti parametrii SETabili vor influienta afisarea imaginii text :

Maxdata Arraysize Long

Imaginea text este memorata intr-o coloana de tipul de date Long si ar trebui setat ARRAYSIZE la o valoare mica ( de ex. 1 ) pentru a vizualiza textul.

Modificarea Datelor prin Imagini

Dupa cum am discutat, restrictiile pot fi impuse cu ajutirul clauzei WITH CHECK OPTION cand o imagine este creata. Mai sunt si alte constrangeri de care trebiue sa fiti atenti. DELETE este interzis cand o imagine contine :

5. conditie Join 6. functii de grup 7. clauza GROUP BY 8. comanda DISTINCT 9. ROWNUM (nr de ordine al randului dupa ce acesta a fost adus din baza de date).

UPDATE este interzis cand : 4. Oricare de mai sus 5. Coloane definite prin expresii ( de ex. SAL*12 )

INSERT este interzis cand : 3. Oricare de mai sus 4. Orice NOT NULL coloane nu sunt selectate de imagine ( ex. EMPNO )

Page 15: Comenzi SQL

Comanda DROP VIEW

Utilizati comanda DROP pentru a sterge o imagine. Aceasta comanda elimina definitia imaginii din baza de date. Coloanele si liniile nu sunt afectate deoarece sunt stocate in tabelele de unde a derivat fiecare ima- gine. Imaginile sau alte aplicatii bazate pe o imagine stearsa devin in- valide. De notat ca o imagine poate fi derivata dintr-o tabela sau de fapt dintr-o alta imagine ! Sintaxa DROP este : DROP VIEW nume_imagine;Doar creatorul imaginii poate sa o stearga.

Exercitii - Crearea si Utilizarea Imaginilor

1. Definiti o imagine care sa produca urmatoarea iesire cand este referita intr-o cerere. Rulati o cerere pentru a testa imaginea. DEPTNO AVERAGE MAXIMUM MINIMUM SUM NO_SALS NO_COMMS------ ---------- ------- ------- ----- -------- --------10 2196.66667 5000 1300 8750 3 020 2175 3000 800 10875 5 030 1566.66667 2850 950 9400 6 4

2. Utilizand imaginea de mai sus, extrageti urmatoarea informatie. Nuarul angajatului trebuie introdus la rulare. EMPNO ENAME JOB SAL HIREDATE MINIMUM MAXIMUM AVERAGE----- --------- ------- ---- --------- ------- ------- ------- 7902 FORD ANALYST 3000 05-DEC-83 800 3000 2175

3. Creati o imagine care sa impuna urmatoarele restrictii la insrearea datelor in tabela ASSIGNMENTS.

1. Projid trebuie sa fie mai mic de 2000. 2. Data de sfarsit trebiue sa fie dupa data de inceput. 3. Tiputile valide pentru Assign_type sunt PF, WT sau ED. 4. Bill_rate trebuie sa fie mai mic decat 50.00 pentru Assign_type PF, mai mic de

60.00 pentru WT si mai mic de 70.00 pentru ED. 5. EMPNO trebuie sa fie valid. 6. Retineti clauza WITH CKECK OPTION.

SOLUTII1.

CREATE VIEW AGGREGATES ( DEPTNO, AVERAGE, MAXIMUM, MINIMUM, SUM, NO_SALS, NO_COMMS ) AS SELECT DEPTNO, AVG(SAL), MAX(SAL), MIN(SAL), COUNT(SAL), COUNT(COMM), FROM EMP GROUP BY DEPTNO;

2. SELECT EMP.DEPTNO, ENAME, JOB, SAL, HIREDATE, MINIMUM,

Page 16: Comenzi SQL

MAXIMUM, AVERAGE FROM EMP, AGGREGATES AGG WHERE EMP.DEPTNO = AGG.DEPTNO AND EMP.EMPNO = & EMPNO;

3. CREATE VIEW ASG_VAL AS SELECT PROJID, EMPNO, A_START_DATE, A_END_DATE, BILL_RATE, ASSIGN_TYPE, HOURS, FROM ASSIGNMENTS WHERE A_START_DATE < A_END_DATE AND PROJID < 2000 AND BILL_RATE <= DECODE( ASSIGN_TYPE,'PF',50,'WT',60,70) AND EMPNO IN ( SELECT EMPNO FROM EMP ) WITH CHECK OPTION;

Procedurile stocate

In urmatoarele randuri vom trece in revista "filozofia" si avantajele utilizarii procedurilor stocate pe serverul de baze de date. De asemenea, vom prezenta o serie de elemente privind modalitatile de folosire si respectiv privind integrarea procedurilor stocate in cadrul diverselor aplicatii realizate. In plus vor mai fi expuse cateva exemple practice de utilizare si implementare a acestei tehnologii. Dezvoltarea aplicatiilor de baze de date presupune gasirea de solutii care sa permita constructia eficienta si posibilitatea de modificare a procedurilor care realizeaza operatiunile asupra informatiei incluse in bazele de date. Procedurile stocate pe serverul de baze de date sunt solutia principala pentru crearea unor aplicatii performante, constituind in acelasi timp modalitatea cea mai eficienta de implementare a operatiunilor asupra bazelor de date in sistemele client/server.

Ce sunt procedurile stocate? Procedurile stocate reprezinta o colectie precompilata de instructiuni SQL salvate pe serverul de baze de date sub un nume si procesate unitar. Ele permit utilizatorului sa realizeze operatiuni complexe asupra bazei de date prin intermediul unui singur apel transmis de aplicatia sursa. De asemenea, utilizatorul are posibilitatea de a folosi variabilele declarate, instructiunile conditionale si alte elemente puternice de programare. Procedurile stocate sunt o componenta omniprezenta in cazul sistemelor de baze de date relationale bazate pe tehnologia client/server (MS SQL Server sau Oracle).

Avantajele utilizarii procedurilor stocate:Prezentam cateva dintre avantajele fundamentale care rezulta din folosirea acestei tehnologii: - Procedurile stocate sunt salvate intr-o forma compilata, deci prezinta o viteza de executie

mai mare decat a unor interogari SQL realizate ad-hoc. - Exista posibilitatea de a executa o succesiune de instructiuni SQL in cadrul unei singure

proceduri stocate.

Page 17: Comenzi SQL

- In cadrul unei proceduri stocate exista posibilitatea de a face referinta la alte proceduri stocate, ceea ce determina posibilitatea de executie inlantuita a acestora.

- Facilitatea de a folosi un set suplimentar de instructiuni fata de standardul SQL, determina cresterea flexibilitatii acestui instrument. De asemenea, instructiunile conditionale sau repetitive permit constructia unor adevarate programe de manipulare a bazelor de date.

- Posibilitatea de utilizare a variabilelor determina, printre altele, facilitatea de a construi interogari SQL parametrizabile.

- Asupra procedurilor stocate pot fi setate drepturi de acces, independent de cele care au fost asupra celorlalte elemente ale bazei de date (tabele, indecsi, vizualizari etc.).

- - De asemenea, prin intermediul procedurilor stocate este posibila "ruperea" nivelului aplicatiei de baze de date si a elementelor de interfata ale acesteia de nivelul propriu-zis al bazei de date. In acest mod, modificarile ce vor afecta procedurile de manipulare a datelor se vor realiza direct la nivelul bazei de date, neafectand sursele propriu-zise ale aplicatiile (aplicatia nu va include decat instructiunile de executie a procedurilor stocate).

Modul de constructie a procedurilor stocate: Sa creem tabela:CREATE TABLE repere (cod number(5) primary key, denumire varchar2(15), explicatii varchar2(25))Inserati citeva inregistrari in acesta tabela.

Exemplu 1:Dorim sa creem o procedura stocata care sa stearga un anumit articol din tabela repere:CREATE OR REPLACE PROCEDURE stergrepere (codrep IN NUMBER) ISBEGINDelete From repere Where cod = codrep;End stergrepere;

Exemplu 2:

Dorim sa creem o procedura care sa insereze un anumit numar de articole in aceasta tabela:

PROCEDURE inserez_produse (numar in number) ASBEGINdeclaredenumire varchar2(15);beginfor cnt in 1 .. numar loop denumire := 'prod'||to_char(cnt,'99999'); insert into produse values (cnt,denumire,’’);end loop;END;end;

Putem folosi declaratia EXECUTE pentru a rula o procedura stocata. Procedurile stocate sunt diferite de functii deoarece nu pot returna valori in locul numelui lor si nu pot fi folosite direct intr-o expresie.

Pentru a insera 150 de articole in tabela produse vom scrie:1.In SQL*Plus:EXECUTE inserez_produse(150);

Page 18: Comenzi SQL

2. In SQL Navigator:begininserez_produse(150);end;

Modificati acesta procedura in sensul inserarii unui anumit numar de articole incepand cu un cod initial.

Exemplul 3:In continuare dorim sa actualizam campul explicatii cu valoarea ‘nou’ la toate articolele cu un cod mai mare decat o valoare data:

PROCEDURE actualizez_produse (numar in number) ASBEGINdeclarecodp number;denumire varchar2(15);explicatii varchar2(25);cursor mycur is select cod, denumire, explicatii from repere where cod>=numar;beginopen mycur;loopfetch mycur into codp, denumire, explicatii;exit when mycur%notfound;if mycur%found then update repere set explicatii='nou' where cod=codp; else null; end if;end loop;close mycur;end;end;

Exemplul 4:PROCEDURE acordare_bonus (sel_marca NUMBER) IS bonus REAL; eroare EXCEPTION; BEGIN SELECT sal * 0.15 INTO bonus FROM emp WHERE marca = sel_marca; IF bonus IS NULL THEN RAISE eroare; ELSE UPDATE plati SET plati = plati + bonus WHERE marca = sel_marca; END IF; EXCEPTION WHEN eroare THEN dbms_output.put_line('marca inexistenta in baza de date’); END acordare_bonus;

Procedura acordare_bonus primeste ca argument la rulare marca salariatului pentru care se aplica bonificatia. Daca in urma rularii instructiunii Select nu este selectata nici o inregistrare

Page 19: Comenzi SQL

din baza de date, executia codului va fi transmisa in sectiunea exceptiilor, afisandu-se un mesaj de eroare. In caz contrar se aplica instructiunea Update asupra tabelei Plati.

Pentru acordarea drepturilor de executie a unei proceduri stocate se va utiliza instructiunea GRANT: Grant Execute on procedure_name to Public/User Instructiunea GRANT poate asigna dreptul de executie pentru procedura stocata, identificata prin intermediul Procedure_name, unui utilizator sau tuturor utilizatorilor bazei de date (identificatorul Public).

Concluzii:Procedurile stocate constituie incontestabil, o cale pentru eficientizarea operatiunilor realizate asupra bazelor de date. Acest lucru se impune din cel putin doua elemente esentiale: primul consta in forma compilata a acestora, fapt ce determina rularea mai rapida; iar cel de-al doilea priveste elementele de programare in cazul aplicatiilor de baze de date, procedurile stocate determinand in acest sens o integrare mai fireasca a elementelor de limbaj SQL.

Declansatorii (Triggeri)

Declansatorii (Triggers) sunt un tip special de proceduri stocate care permit realizarea automata a unor actiuni (proceduri SQL) atunci cand datele sunt modificate prin intermediul unuia dintre operatorii standardului SQL: Update, Insert sau Delete. Declansatorii pot fi utilizati pentru constructia unor reguli complexe la nivelul bazei de date sau pentru pastrarea integritatii referentiale a acesteia. Printre avantajele utilizarii declansatorilor mentionam: - Declansatorii opereaza automat, imediat dupa ce au fost realizate modificarile asupra bazei de date. - Prin intermediul acestui instrument exista posibilitatea de a efectua modificari in cascada asupra mai multor tabele ale bazei de date. - Declansatorii permit constructia unor reguli stricte la nivelul bazei de date utilizand facilitatile oferite de limbajul SQL. Un element important care este necesar sa-l subliniem in legatura cu declansatorii il constituie faptul ca acestia nu pot fi aplicati decat la nivelul tabelelor bazei de date. Cand o instructiune SQL satisface conditia triggerului, Oracle automat executa corpul triggerului.Sintaxa este urmatoarea:

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name {BEFORE event | AFTER event | INSTEAD OF event} referencing_clause WHEN (condition) pl_sql_block

unde event poate fi unul din urmatoarele (sau mai multe separate prin OR): DELETE event_ref INSERT event_ref UPDATE event_ref UPDATE OF column, column... event_ref ddl_statement ON [schema.] {table|view} ddl_statement ON DATABASE SERVERERROR LOGON LOGOFF STARTUP SHUTDOWN

event_ref:

Page 20: Comenzi SQL

ON [schema.]table ON [schema.]view ON [NESTED TABLE nested_table_column OF] [schema.]view

referencing_clause: FOR EACH ROW REFERENCING OLD [AS] old [FOR EACH ROW] REFERENCING NEW [AS] new [FOR EACH ROW] REFERENCING PARENT [AS] parent [FOR EACH ROW]

Sintaxa intr-o forma mai simplificata arata astfel:

CREATE [OR REPLACE] TRIGGER name { BEFORE | AFTER } { event [OR ...] } ON table FOR EACH { ROW | STATEMENT } EXECUTE PROCEDURE func ( arguments )

Exemplul 1:

Pentru a crea un trigger care pentru fiecare client sters din tabela Customer creaza un articol in tabela CustomerHistory se procedeaza astfel:

CREATE OR REPLACE TRIGGER DeleteCustomer

BEFORE DELETE ON Customer

FOR EACH ROW

BEGIN

INSERT INTO CustomerHistory

VALUES(:Old.CUSTOMERID, :Old.LastName, :Old.FirstName);

END DeleteCustomer;

Observatii: Daca printr-o instructiune SQL se sterg mai multi clienti, triggerul actioneaza la fiecare client sters.In corpul triggerului new si old trebuie precedate de “:”.

Exemplul 2: Dorim sa inseram clienti in tabela Customer folosind un trigger si o secventa care sa genereze automat codul clientului (customerid):create sequence secventa_customer start with 1 increment by 1 nomaxvalue;

Daca in tabela de clienti avem deja 120 de intrari (cu customerid 1-120) putem porni secventa folosind: start with 121Daca dorim ca numerele de clienti sa fie din 2 in 2 folosim:increment by 2Acum se poate crea triggerul ce va insera automat urmatorul numar din secventa in coloana customerid:create trigger customer_triggerbefore insert on customerfor each rowbeginselect secventa_customer.nextval into :new.customerid from dual;end;/Modificarea unui trigger (in sensul dezactivarii sau a reactivarii) se face astfel:alter trigger nume_trigger disable;alter trigger nume_trigger enable;

Stergerea unui trigger se face astfel:drop trigger nume_trigger

Page 21: Comenzi SQL

FunctiiO clasa speciala de proceduri stocata este formata din functiile prin intermediul carora poate fi returnata o valoare. O functie stocata prezinta in plus fata de o procedura o clauza speciala Return, prin intermediul careia se realizeaza aceasta operatiune. Iata cum cream o functie simpla:

create or replace function first_func (var) return varchar2 as begin return var ||’Parametru’; end;

Exemplu2 : Pentru a crea o functie care sa primeasca ca parametru 2 numere si sa returneze –1 daca primul numar este mai mic, 0 daca sunt egale si 1 daca primul numar este mai mare se procedeaza astfel:

CREATE or REPLACE function modul (x in number, y in number)return numberisbeginif (x < y) then return (-1); elsif ( x = y) then return (0); else return (1); end if;end;

O functie care retuneaza pozitia de la care incepe un sir in alt sir:CREATE FUNCTION instr(s1 in VARCHAR,s2 in VARCHAR) RETURNS INTEGER IS 'DECLARE pos integer; BEGIN pos:= instr(s1,s2,1); RETURN pos; END;

PL/SQLExemple:

DECLARE cantitate_in_stoc NUMBER(5); BEGIN SELECT quantity INTO cantitate_ in_stoc FROM stocuri WHERE product = 'MINGI DE TENIS’ FOR UPDATE OF quantity; IF cantitate_in_stoc > 0 THEN UPDATE stocuri SET quantity = quantity - 1 WHERE product = 'MINGI DE TENIS’; INSERT INTO achizitii_record VALUES ('Mingi de tenis achizitionate’, SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Stocul epuizat’, SYSDATE); END IF;

Page 22: Comenzi SQL

COMMIT; END;

Procedura defineste o variabila (cantitate_in_stoc) care va prelua din baza de date stocul disponibil pentru un articol de stoc, urmand ca prin intermediul instructiunii IF sa se realizeze fie diminuarea stocului, fie includerea in tabela purchase_record a unui mesaj de avertizare privind epuizarea stocului.

Concluzii: PL/SQL este limbajul care permite manipularea eficienta a elementelor bazei de date. Adaugarea elementelor procedurale alaturi de limbajul SQL constituie "cheia" care permite constructia unor proceduri complexe de prelucrare a bazei de date. Constructia procedurilor si functiilor stocate la nivelul bazei de date prezinta alaturi de viteza mare de executie (consecinta compilarii acestora), avantajul gestionarii unitare prin intermediul unor utilitare tip Schema Manager. Aceste instrumente constituie o modalitate de constructie, vizualizare sau modificare a ansamblului de elemente si obiecte ce compune baza de date Oracle.