- De facut
- exemplu GO TO
- Ex
Comparare cod specialisation
DECLARE v_nr number(2); BEGIN SELECT cods INTO v_nr FROM spec WHERE domeniu = 'STIINTE EXACTE'; dbms_output.put_line('Code specialisation: ' || v_nr); IF (v_nr = 21) then dbms_output.put_line('Valeur = 21'); ELSIF v_nr < 21 then dbms_output.put_line(' 21'); END IF; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('Absent'); WHEN too_many_rows THEN dbms_output.put_line('Plusieurs'); WHEN others THEN dbms_output.put_line('Autre erreur'); END;
Statement processed.
Code specialisation: 11
< 21Case 1
DECLARE codjudet VARCHAR2(2) := 'CJ'; numejudet VARCHAR2(20); BEGIN numejudet := CASE codjudet WHEN 'MM' THEN 'Maramures' WHEN 'CT' THEN 'Constanta' WHEN 'CJ' THEN 'Cluj' ELSE 'Alt judet' END; dbms_output.put_line(numejudet); END;
Case 2
DECLARE codjud VARCHAR2(2) := 'CJ'; numejud VARCHAR2(20); BEGIN numejud := CASE WHEN codjud = 'MM' THEN 'Maramures' WHEN codjud ='CT' THEN 'Constanta' WHEN codjud ='CJ' THEN 'Cluj' WHEN codjud LIKE 'A%' THEN 'Incepe cu A' WHEN codjud IS NULL THEN 'Cod judet NULL' ELSE 'Alt judet' END; dbms_output.put_line(numejud); END;
DECLARE v_dom varchar2(20); BEGIN SELECT domeniu INTO v_dom FROM spec WHERE cods = 21; CASE v_dom WHEN 'UMANIST' THEN dbms_output.put_line('UMAN'); WHEN 'STIINTE EXACTE' THEN dbms_output.put_line('REAL'); ELSE dbms_output.put_line('ALTCEVA'); END CASE; END;
verif cod specializare
DECLARE v_contor number(2) :=6; v_dom varchar2(20); v_cods number; v_nume varchar2(10); BEGIN LOOP BEGIN SELECT cods, nume, domeniu INTO v_cods, v_nume, v_dom FROM spec WHERE cods=v_contor; dbms_output.put_line('Code '|| v_contor|| ' nom '||v_nume||' domaine '||v_dom); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('Code absent: '|| v_contor); END; -- sous-bloc v_contor := v_contor + 5; EXIT when v_contor > 40; END LOOP; EXCEPTION WHEN others THEN dbms_output.put_line('Exception'); END;
Code absent: 6
Code 11 nom MATEMATICA domaine STIINTE EXACTE
Code absent: 16
Code 21 nom GEOGRAFIE domaine UMANIST
Code absent: 26
Code absent: 31
Code absent: 36DECLARE v_contor number(2); v_cods number; v_nume varchar2(10); v_dom varchar2(20); BEGIN FOR v_contor IN 20..25 LOOP BEGIN SELECT cods, nume, domeniu INTO v_cods, v_nume, v_dom FROM spec WHERE cods=v_contor; dbms_output.put_line('Code '|| v_contor||' nom '||v_nume||' domaine '||v_dom); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('Code absent: '||v_contor); END; -- sous-bloc END LOOP; END;
DECLARE v_contor number(2) :=6; v_cods number; v_nume varchar2(10); v_dom varchar2(20); BEGIN WHILE v_contor <=40 LOOP BEGIN SELECT cods, nume, domeniu INTO v_cods, v_nume, v_dom FROM spec WHERE cods=v_contor; dbms_output.put_line('Code '|| v_contor||' nom '||v_nume||' domaine '||v_dom); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('Code absent: '|| v_contor); END; -- sous-bloc v_contor := v_contor + 5; END LOOP; EXCEPTION WHEN others THEN dbms_output.put_line('Exception'); END;
BOOLEAN , comparare string lipsa
DECLARE str varchar2(20) := 'Zara Ali'; v BOOLEAN := 'Zara Ali' like 'Z% A_i'; BEGIN if(v = TRUE) then dbms_output.put_line('true'); else dbms_output.put_line('false'); end if; END;
Exceptions
avec nom
DECLARE v_montant NUMBER; montant_null EXCEPTION; PRAGMA EXCEPTION_INIT(montant_null, -20021); BEGIN SELECT suma INTO v_montant FROM bursa WHERE pmin < 100; IF v_montant IS NULL THEN RAISE_APPLICATION_ERROR(-20021, 'Le montant est null'); END IF; EXCEPTION WHEN montant_null THEN dbms_output.put_line(SQLERRM); END;
sans nom
DECLARE v_montant NUMBER; v_codeerreur NUMBER; BEGIN SELECT suma INTO v_montant FROM bursa WHERE pmin < 100; IF v_montant IS NULL THEN RAISE_APPLICATION_ERROR(-20021, 'Le montant est null'); END IF; EXCEPTION WHEN OTHERS THEN v_codeerreur := SQLCODE; IF (v_codeerreur = -20021) THEN dbms_output.put_line(SQLERRM); END IF; END;
Struct -> salveaza linia unui tabel
select * from stud
MATR NUME AN GRUPA DATAN LOC TUTOR PUNCTAJ CODS 1456 GEORGE 4 1141A 12-MAR-82 BUCURESTI – 2890 11 1325 VASILE 2 1122A 05-OCT-84 PITESTI 1456 390 11 1645 MARIA 3 1131B 17-JUN-83 PLOIESTI – 1400 11 3145 ION 1 2112B 24-JAN-85 PLOIESTI 3251 1670 21 2146 STANCA 4 2141A 15-MAY-82 BUCURESTI – 620 21 3251 ALEX 5 2153B 07-NOV-81 BRASOV – 1570 21 2215 ELENA 2 2122A 29-AUG-84 BUCURESTI 2146 890 21 4311 ADRIAN 3 2431A 31-JUL-83 BUCURESTI – 450 24 3514 FLOREA 5 2452B 03-FEB-81 BRASOV – 3230 24 1925 OANA 2 2421A 20-DEC-84 BUCURESTI 4311 760 24 2101 MARIUS 1 2412B 02-SEP-85 PITESTI 3514 310 24 4705 VOICU 2 2421B 19-APR-84 BRASOV 4311 1290 24 DECLARE v_student stud % ROWTYPE; BEGIN SELECT * INTO v_student FROM stud WHERE matr = 1456; dbms_output.put_line(v_student.nume); END;
INDEX BY
Declare TYPE t_varsta IS TABLE OF NUMBER INDEX BY VARCHAR2(30); TYPE t_nrstud IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE t_stud IS TABLE OF stud % ROWTYPE INDEX BY BINARY_INTEGER; v_varsta t_varsta; v_nrstud t_nrstud; v_stud t_stud; v_numar NUMBER; BEGIN v_varsta('Ionescu') := 23; v_varsta('Badea') := 25; v_varsta('Avramescu') := 27; dbms_output.put_line(v_varsta.FIRST); -- Avramescu dbms_output.put_line(v_varsta.LAST); -- Ionescu v_nrstud(1211) := 48; v_nrstud(1221) := 29; v_nrstud(1231) := 20; v_nrstud(1241) := 21; v_nrstud(1251) := 17; -- Indice ne demarre pas v_stud(4875).nume := 'MARCEL'; v_stud(3456).an := 1; v_stud(3456).punctaj := 1400;-- 2 composants END;
Avramescu
IonescuCollections – Index-by tables or Associative array
Declare TYPE t_nrstud IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE t_stud IS TABLE OF stud % ROWTYPE INDEX BY BINARY_INTEGER; v_nrstud t_nrstud; v_stud t_stud; BEGIN v_nrstud(1211) := 48; -- Cles ne commencent a 1 v_nrstud(1221) := 29; v_nrstud(1231) := 20; v_nrstud(1241) := 21; v_nrstud(1251) := 17; -- 5 composants dbms_output.put_line('FIRST: ' || v_nrstud.FIRST); dbms_output.put_line('LAST: ' || v_nrstud.LAST); dbms_output.put_line('COUNT:' || v_nrstud.COUNT); if v_nrstud.EXISTS(1200) then dbms_output.put_line('Cle 1200 existe'); else dbms_output.put_line('Cle 1200 absente'); end if; if v_nrstud.EXISTS(1231) then dbms_output.put_line('Cle 1231 existe'); else dbms_output.put_line('Cle 1231 absente'); end if; dbms_output.put_line( 'PRIOR(1241):' || v_nrstud.PRIOR(1241) ); dbms_output.put_line( 'PRIOR(1211): ' || v_nrstud.PRIOR(1211) ); -- valoarea lui PRIOR este NULL dbms_output.put_line( 'NEXT(1241):' || v_nrstud.NEXT(1241) ); dbms_output.put_line( 'NEXT(1251): ' || v_nrstud.NEXT(1251) ); -- valoarea lui NEXT este NULL v_nrstud.DELETE(1); -- cle absente dbms_output.put_line( 'Cle 1 eliminee. On a:' || v_nrstud.COUNT || ' elements' ); v_nrstud.DELETE(1241); -- cle presente dbms_output.put_line( 'On a: ' || v_nrstud.COUNT || ' elements' ); v_nrstud.DELETE(1220, 1300); -- Cles efacees dbms_output.put_line( 'On a:' || v_nrstud.COUNT || ' elements' ); v_stud(4875).nume := 'MARCEL'; v_stud(3456).an := 2; v_stud(3456).punctaj := 2400; -- 2 ches dbms_output.put_line('COUNT: ' || v_stud.COUNT); END;
FIRST: 1211
LAST: 1251
COUNT:5
Cle 1200 absente
Cle 1231 existe
PRIOR(1241):1231
PRIOR(1211):
NEXT(1241):1251
NEXT(1251):
Cle 1 eliminee. On a:5 elements
On a: 4 elements
On a:1 elements
COUNT: 2The following example demonstrates using the
SELECT INTO
statement to query a single value into a PL/SQL variable, entire columns into PL/SQL collections, or entire rows into a PL/SQL collection of records:DECLARE howmany NUMBER; some_first employees.first_name % TYPE; some_last employees.last_name % TYPE; some_employee employees % ROWTYPE; TYPE first_typ IS TABLE OF employees.first_name % TYPE INDEX BY PLS_INTEGER; TYPE last_typ IS TABLE OF employees.first_name % TYPE INDEX BY PLS_INTEGER; first_names first_typ; last_names last_typ; CURSOR c1 IS SELECT first_name, last_name FROM employees; TYPE name_typ IS TABLE OF c1 % ROWTYPE INDEX BY PLS_INTEGER; all_names name_typ; TYPE emp_typ IS TABLE OF employees % ROWTYPE INDEX BY PLS_INTEGER; all_employees emp_typ; BEGIN -- Query a single value and store it in a variable. SELECT COUNT(*) INTO howmany FROM user_tables; dbms_output.put_line( 'This schema owns ' || howmany || ' tables.' ); -- Query multiple columns from one row, and store them in variables. SELECT first_name, last_name INTO some_first, some_last FROM employees WHERE ROWNUM < 2; dbms_output.put_line( 'Random employee: ' || some_first || ' ' || some_last ); -- Query a single row and store it in a record. SELECT INTO some_employee FROM employees WHERE ROWNUM < 2; -- Query multiple columns from multiple rows, and store them in a collection -- of records. SELECT first_name, last_name BULK COLLECT INTO all_names FROM EMPLOYEES; -- Query multiple columns from multiple rows, and store them in separate -- collections. (Generally less useful than a single collection of records.) SELECT first_name, last_name BULK COLLECT INTO first_names, last_names FROM EMPLOYEES; -- Query an entire (small!) table and store the rows -- in a collection of records. Now you can manipulate the data -- in-memory without any more I/O. SELECT BULK COLLECT INTO all_employees FROM employees; END;
DISPLAY_STUD
CREATE OR REPLACE PROCEDURE DISPLAY_STUD IS -- inceput procedura BEGIN DECLARE TYPE t_stud IS TABLE OF stud % ROWTYPE INDEX BY PLS_INTEGER; v_stud t_stud; BEGIN SELECT BULK COLLECT INTO v_stud FROM STUD; dbms_output.put_line( 'COUNT studenti: ' || v_stud.count ); FOR i IN v_stud.first..v_stud.last loop DBMS_OUTPUT.PUT_LINE(i || ' nume: ' || v_stud(i).nume || ' punctaj:' || v_stud(i).punctaj); END LOOP; END; END DISPLAY_STUD;
Procedure
DECLARE a number; b number; c number; PROCEDURE findMin(x IN number, y IN number, z OUT number) IS BEGIN IF x < y THEN z := x; ELSE z := y; END IF; END; BEGIN a := 23; b := 45; findMin(a, b, c); dbms_output.put_line(' Minimum of (23, 45) : ' || c); END;
DECLARE a number; PROCEDURE squareNum(x IN OUT number) IS BEGIN x := x * x; END; BEGIN a := 23; squareNum(a); dbms_output.put_line(' Square of (23): ' || a); END;
VARRAY și CURSOR
DECLARE CURSOR c_customers is SELECT name FROM customers; type c_list is varray (6) of customers.name % type; name_list c_list := c_list(); counter integer := 0; BEGIN FOR n IN c_customers LOOP counter := counter + 1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line( 'Customer(' || counter || '):' || name_list(counter) ); END LOOP; END;
EXCEPTION
DECLARE c_id customers.id % type := & cc_id; c_name customerS.Name % type; c_addr customers.address % type; -- user defined exception ex_invalid_id EXCEPTION; BEGIN IF c_id <= 0 THEN RAISE ex_invalid_id; ELSE SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; DBMS_OUTPUT.PUT_LINE ('Name: ' || c_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); END IF; EXCEPTION WHEN ex_invalid_id THEN dbms_output.put_line( 'ID must be greater than zero!' ); WHEN no_data_found THEN dbms_output.put_line('No such customer!'); WHEN others THEN dbms_output.put_line('Error!'); END;
DISPLAY, CURSOR, IS TABLE
DECLARE CURSOR c_customers is select name from customers; TYPE c_list IS TABLE of customers.Name % type INDEX BY binary_integer; name_list c_list; counter integer := 0; BEGIN FOR n IN c_customers LOOP counter := counter + 1; name_list(counter) := n.name; dbms_output.put_line( 'Customer(' || counter || '):' || name_lis t(counter) ); END LOOP; END;
- Create Table
CREATE TABLE spec ( cods NUMBER(2), nume VARCHAR2(10), domeniu VARCHAR2(15) ); INSERT INTO spec VALUES ( 11, 'MATEMATICA', 'STIINTE EXACTE' ); INSERT INTO spec VALUES (21, 'GEOGRAFIE', 'UMANIST'); INSERT INTO spec VALUES (24, 'ISTORIE', 'UMANIST');
CREATE TABLE stud ( matr NUMBER(4), nume VARCHAR2(10), an NUMBER(1), grupa VARCHAR2(6), datan DATE, loc VARCHAR2(10), tutor NUMBER(4), punctaj NUMBER(4), cods NUMBER(2) ); INSERT INTO stud VALUES ( 1456, 'GEORGE', 4, '1141A', '12-MAR-82', 'BUCURESTI', NULL, 2890, 11 ); INSERT INTO stud VALUES ( 1325, 'VASILE', 2, '1122A', '05-OCT-84', 'PITESTI', 1456, 390, 11 ); INSERT INTO stud VALUES ( 1645, 'MARIA', 3, '1131B', '17-JUN-83', 'PLOIESTI', NULL, 1400, 11 ); INSERT INTO stud VALUES ( 3145, 'ION', 1, '2112B', '24-JAN-85', 'PLOIESTI', 3251, 1670, 21 ); INSERT INTO stud VALUES ( 2146, 'STANCA', 4, '2141A', '15-MAY-82', 'BUCURESTI', NULL, 620, 21 ); INSERT INTO stud VALUES ( 3251, 'ALEX', 5, '2153B', '07-NOV-81', 'BRASOV', NULL, 1570, 21 ); INSERT INTO stud VALUES ( 2215, 'ELENA', 2, '2122A', '29-AUG-84', 'BUCURESTI', 2146, 890, 21 ); INSERT INTO stud VALUES ( 4311, 'ADRIAN', 3, '2431A', '31-JUL-83', 'BUCURESTI', NULL, 450, 24 ); INSERT INTO stud VALUES ( 3514, 'FLOREA', 5, '2452B', '03-FEB-81', 'BRASOV', NULL, 3230, 24 ); INSERT INTO stud VALUES ( 1925, 'OANA', 2, '2421A', '20-DEC-84', 'BUCURESTI', 4311, 760, 24 ); INSERT INTO stud VALUES ( 2101, 'MARIUS', 1, '2412B', '02-SEP-85', 'PITESTI', 3514, 310, 24 ); INSERT INTO stud VALUES ( 4705, 'VOICU', 2, '2421B', '19-APR-84', 'BRASOV', 4311, 1290, 24 );
CREATE TABLE bursa ( tip VARCHAR2(20), pmin NUMBER(4), pmax NUMBER(4), suma NUMBER(4) ); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES (0, 399, 'FARA BURSA', NULL); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES (400, 899, 'BURSA SOCIALA', 100); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES (900, 1799, 'BURSA DE STUDIU', 150); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES (1800, 2499, 'BURSA DE MERIT', 200); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES ( 2500, 9999, 'BURSA DE EXCEPTIE', 300 );
- L7
L7 Ex 1
Écrivez un bloc PL / SQL contenant une procédure qui modifie la table STUD comme suit:
– affiche le nombre d’ étudiants du tableau STUD
– supprime les étudiants de la spécialisation avec le code 21 (DELETE).
– affiche le nombre d’ étudiants présents dans le tableau.
DECLARE -- declaratii bloc PROCEDURE sterg21 IS -- inceput procedura numar NUMBER; -- declaratii procedura BEGIN SELECT COUNT(*) INTO numar FROM stud; DBMS_OUTPUT.PUT_LINE('Inainte: ' || numar); DELETE FROM STUD WHERE CODS = 21;
SELECT COUNT(*) INTO numar FROM stud;
DBMS_OUTPUT.PUT_LINE('Dupa: ' || numar);
ROLLBACK; -- altfel se modifica baza de date
END sterg21; -- sfarsit procedura BEGIN -- inceput parte executabila bloc sterg21(); END; -- sfarsit blocL7 Ex 2
Écrivez une procédure stockée qui supprime de la table STUD les étudiants de la spécialisation avec le code 21 (DELETE). Cette procédure sera utilisée par un bloc PL / SQL qui:
– affiche le nombre d’ étudiants du tableau
– appelle la procédure stockée pour supprimer les étudiants de la spécialisation avec le code 21.
– affiche le nombre d’élèves présents dans le tableau.
CREATE OR REPLACE PROCEDURE sterg21 IS -- inceput procedura BEGIN DELETE FROM STUD WHERE CODS = 21; END sterg21; -- sfarsit procedura
DECLARE numar NUMBER; -- declaratii bloc BEGIN SELECT COUNT() INTO numar FROM stud; DBMS_OUTPUT.PUT_LINE('Inainte: ' || numar); DELETE FROM STUD WHERE CODS = 21; sterg21(); SELECT COUNT() INTO numar FROM stud; DBMS_OUTPUT.PUT_LINE('Dupa: ' || numar); ROLLBACK; -- altfel se modifica baza de date END; -- sfarsit bloc
L7 Ex 3
Modifier le bloc de 2 pour qu’il contienne une fonction qui affiche le nombre d’étudiants d’une spécialisation reçus en paramètre (son code)
L7 Ex 4
Modifiez le bloc de 3 en supprimant la fonction et en la transformant en fonction stockée
L7 Ex 5
Modifier la procédure de 2 pour que le nombre initial et final d’étudiants soit calculé par la procédure et retournés en 2 paramètres de type OUT, les valeurs étant affichées par le bloc qui appelle la procédure.
L7 Ex 6?
Identique à 5 mais les deux paramètres sont de type IN OUT.
Specializare
CREATE OR REPLACE FUNCTION SPECIALIZAREA (cod_stud INTEGER) RETURN VARCHAR2 IS V_SPEC SPEC.NUME % TYPE; BEGIN SELECT SPEC.NUME INTO V_SPEC FROM STUD, SPEC WHERE STUD.CODS = SPEC.CODS AND STUD.MATR = cod_stud; RETURN V_SPEC; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Etudiant absent'; WHEN OTHERS THEN RETURN 'Exception'; END SPECIALIZAREA;
SELECT NUME, SPECIALIZAREA(MATR) FROM STUD;
NUME SPECIALIZAREA(MATR) GEORGE MATEMATICA VASILE MATEMATICA MARIA MATEMATICA ION GEOGRAFIE STANCA GEOGRAFIE ALEX GEOGRAFIE ELENA GEOGRAFIE ADRIAN ISTORIE FLOREA ISTORIE OANA ISTORIE MARIUS ISTORIE VOICU ISTORIE - L6
L6 Ex 1, 2, 3
- Écrivez un bloc PL / SQL qui utilise une collectionTABLE pour stocker les noms des étudiants du tableau STUD.
La collection est remplie à l’aide d’un curseur qui renvoie les noms des étudiants d’une spécialisationdont le code (CODS) est stocké dans une constante.
Après le téléchargement des données, le contenu de la collection est affiché avec une boucle FOR. Le nombre d’éléments est renvoyé par la méthode COUNT.
2. As 1 mais le contenu est affiché en utilisant une boucle LOOP et un compteur numérique.
3. Comme 2 mais la navigation se fait à l’aide des méthodes associées (FIRST, NEXT, …)
DECLARE TYPE TabNoms IS TABLE OF stud.nume%TYPE; noms TabNoms := TabNoms(); spec number := 11; i number; CURSOR c IS SELECT nume FROM stud where cods = spec; BEGIN i :=0; FOR v IN c LOOP i := i + 1; noms.extend(1); noms(i) := v.nume; END LOOP; dbms_output.put_line(i || ' ' || noms.count); -- EX 1 FOR i IN 1..noms.count loop dbms_output.put_line(i || ' ' || noms(i)); END LOOP; -- EX 2 i := 1; LOOP dbms_output.put_line(i || ' - ' || noms(i)); i := i+1; EXIT WHEN i > noms.count; END LOOP; -- EX 3 i := noms.FIRST; LOOP dbms_output.put_line(i || ' - ' || noms(i)); i := noms.NEXT(i); EXIT WHEN i IS NULL; END LOOP; -- END;
L6 Ex 4
Comme 1 mais utilisez BULK_COLLECT pour lire dans la variable le résultat renvoyé par le curseur avec une seule instruction
DECLARE TYPE TabNoms IS TABLE OF stud.nume%TYPE; noms TabNoms := TabNoms(); spec number := 11; i number; CURSOR c IS SELECT nume FROM stud where cods = spec; BEGIN -- EX 4 OPEN c; FETCH c BULK COLLECT INTO noms; dbms_output.put_line(i || ' ' || noms.count); -- EX 1 FOR i IN 1..noms.count loop dbms_output.put_line(i || ' ' || noms(i)); END LOOP; END;
L6 Ex 5
Comme 4 mais seulement 2 lignes (LIMIT 2) sont chargées à chaque itération (transparent 38-39)
DECLARE TYPE TabNoms IS TABLE OF stud.nume%TYPE; noms TabNoms := TabNoms(); spec number := 24; i number; CURSOR c IS SELECT nume FROM stud where cods = spec; BEGIN -- EX 5 OPEN c; LOOP FETCH c BULK COLLECT INTO noms LIMIT 3; EXIT WHEN c%NOTFOUND; FOR i IN 1..noms.count loop dbms_output.put_line(i || ' ' || noms(i)); END LOOP; -- EXIT WHEN c%NOTFOUND; END LOOP; END;
L6 Ex 6
Prendre dans un TABLEAU les codes destuteurs, pour augmenter de 10% leur score à l’aide d’un FORALL.
update stud set punctaj = 1000
DECLARE TYPE TabCodes IS VARRAY(10) OF NUMBER; TYPE TabNoms IS TABLE OF stud.nume%TYPE; codes TabCodes; noms TabNoms; k number; BEGIN SELECT tutor, nume BULK COLLECT INTO codes, noms FROM stud WHERE TUTOR IS NOT NULL; dbms_output.put_line('No etudiants = ' || codes.count); FORALL i IN 1 ..codes.count UPDATE stud SET punctaj = punctaj * 1.10 WHERE matr = codes(i); FOR k IN 1..codes.count loop dbms_output.put_line('Iteration, on a modifie ' || SQL%BULK_ROWCOUNT(k) || ' lignes pour le tuteur de ' || noms(k)); END LOOP; END;
SELECT nume, punctaj FROM stud
Pour chacun des exercices ci-dessus, écrivez la version qui utilise un VARRAY et non un TABLE.
- L5
L5 Ex1
Ecrivez un bloc PL / SQL qui efface les étudiants de la spécialisation avec le code 21 (DELETE). Après DELETE les valeurs de ROWCOUNT, FOUND, et NOTFOUND sont affichées. Après avoir affiché, la commande DELETE est annulée avec ROLLBACK.
-- EX1 BEGIN DELETE FROM STUD1 WHERE CODS = 21; dbms_output.put_line('On a modifie '|| SQL%ROWCOUNT || ' lignes'); IF sql%found THEN dbms_output.put_line('Found TRUE'); ELSE dbms_output.put_line('Found FALSE'); END IF; IF sql%notfound THEN dbms_output.put_line('NOTFound TRUE'); ELSE dbms_output.put_line('NOTFound FALSE'); END IF; ROLLBACK; END;
L5 Ex 2
Ajouter l’affichage de la valeur d’ISOPEN. Les valeurs possibles: NULL, FALSE, TRUE ou exception.
-- EX2 BEGIN UPDATE FROM STUD1 WHERE CODS = 21; IF sql%isopen THEN dbms_output.put_line('Open TRUE'); ELSE dbms_output.put_line('Open FALSE'); END IF; ROLLBACK; END;
Update punctaj studenti
DECLARE total_rows number(2); TYPE t_stud IS TABLE OF stud % ROWTYPE INDEX BY PLS_INTEGER; v_stud t_stud; BEGIN UPDATE stud SET punctaj = punctaj + 1000; IF sql % notfound THEN dbms_output.put_line('no stud selected'); ELSIF sql % found THEN total_rows := sql % rowcount; dbms_output.put_line( total_rows || ' customers selected ' ); END IF; SELECT BULK COLLECT INTO v_stud FROM STUD WHERE punctaj is not null; dbms_output.put_line( 'COUNT studenti: ' || v_stud.count ); FOR i IN v_stud.first..v_stud.last loop DBMS_OUTPUT.PUT_LINE( i || ' nume: ' || v_stud(i).nume || ' punctaj:' || v_stud(i).punctaj ); END LOOP; ROLLBACK; END;
12 customers selected
COUNT studenti: 12
1 nume: GEORGE punctaj:3890
2 nume: VASILE punctaj:1390
3 nume: MARIA punctaj:2400
4 nume: ION punctaj:2670
5 nume: STANCA punctaj:1620
6 nume: ALEX punctaj:2570
7 nume: ELENA punctaj:1890
8 nume: ADRIAN punctaj:1450
9 nume: FLOREA punctaj:4230
10 nume: OANA punctaj:1760
11 nume: MARIUS punctaj:1310
12 nume: VOICU punctaj:2290Custom / Explicit Cursor
DECLARE TYPE t_stud IS TABLE OF stud % ROWTYPE INDEX BY PLS_INTEGER; v_stud t_stud; c_name stud.nume % type; c_punct stud.punctaj % type; CURSOR c_stud is SELECT nume, punctaj FROM stud; BEGIN OPEN c_stud; LOOP FETCH c_stud into c_name, c_punct; EXIT WHEN c_stud % notfound; dbms_output.put_line(c_name || ' ' || c_punct); END LOOP; CLOSE c_stud; END;
GEORGE 2890
VASILE 390
MARIA 1400
ION 1670
STANCA 620
ALEX 1570
ELENA 890
ADRIAN 450
FLOREA 3230
OANA 760
MARIUS 310
VOICU 1290L5 Ex 3
Comme 2, mais au lieu de DELETE on ajoute de nouveau les étudiants de la spécialisation 21 par INSERT de SELECT.
L5 Ex 4
Ecrivez un bloc PL / SQL qui utilise un curseur implicite et une boucle FOR pour les curseurs (les diapositives 35-36 du cours) pour afficher le nom et le numéro de points pour les étudiants de la spécialisation avec le code 21.
-- ex4 BEGIN FOR v_stud IN (select nume, punctaj from STUD1 where cods=21) LOOP dbms_output.put_line('Etudiant: ' || v_stud.nume || ' puncte: ' || v_stud.punctaj); END LOOP; END;
L5 Ex 5
Ecrivez un bloc PL / SQL qui utilise un curseur explicite et une boucle LOOP – EXIT WHEN pour afficher le nom et la date de naissance pour les étudiants de la spécialisation avec le code 21. Ils sont numérotés en utilisant ROWCOUNT. Les deux valeurs sont stockées en deux variables.
--EX5 DECLARE CURSOR studenti IS SELECT * FROM STUD1 WHERE CODS =21; v_stud studenti%rowtype; BEGIN OPEN studenti; LOOP FETCH studenti INTO v_stud; EXIT WHEN studenti%notfound; dbms_output.put_line(studenti%rowcount || 'Etudiant: ' || v_stud.nume || ', avec la date de nasissance: ' || v_stud.datan); END LOOP; CLOSE studenti; END;
1Etudiant: ION, avec la date de nasissance: 24-JAN-85
2Etudiant: STANCA, avec la date de nasissance: 15-MAY-82
3Etudiant: ALEX, avec la date de nasissance: 07-NOV-81
4Etudiant: ELENA, avec la date de nasissance: 29-AUG-84L5 Ex 6
Comme 5, mais on utilise une variable enregistrement pour FETCH. (Sauvegardez le bloc pour l’exercice 13).
--ex6 BEGIN OPEN studenti; FETCH studenti INTO v_stud; WHILE studenti%found LOOP dbms_output.put_line(studenti%rowcount|| ' Etudiant: ' || v_stud.nume||' score '||v_stud.punctaj); FETCH studenti INTO v_stud; END LOOP;
L5 Ex 7 Comme 6 mais utilisez une boucle WHILE
--EX7 DECLARE CURSOR studenti IS SELECT * FROM STUD1 WHERE CODS=21; v_stud studenti%rowtype; n number; i number; BEGIN SELECT COUNT(*) INTO n FROM STUD1 WHERE CODS=21; dbms output.put_line('NrStud='||n); OPEN studenti; FOR i IN 1..n LOOP FETCH studenti INTO v_stud; dbms_output.put_line(studenti%rowcount|| ' Etudiant: ' || v_stud.nume||' score '||v_stud.punctaj) END LOOP; CLOSE studenti; END;
L5 Ex 8
Comme 7 mais utilisez une boucle FOR classique (pas FOR pour les curseurs).
DECLARE CURSOR studenti IS SELECT * FROM STUD WHERE CODS = 11; BEGIN FOR v_stud IN studenti LOOP dbms_output.put_line(studenti%rowcount || ' Etudiant: ' || v_stud.nume || ', avec la date de naissance: ' || v_stud.datan); END LOOP; END;
L5 Ex 9
Comme 8, mais utilisez une boucle FOR pour les curseurs.
--ex9 DECLARE CURSOR studenti IS SELECT * FROM STUD1 WHERE CODS=21; BEGIN FOR v_stud IN studenti LOOP dbms_output.put_line(studenti%rowcount|| ' Etudiant: ' || v_stud.nume||' avec la date de naissance '||v_stud.datan); END LOOP; END;
L5 Ex 10
Comme 9 mais le curseur a un paramètre – le code de spécialisation – qui est dans une constante. La valeur par défaut est 11.
L5 Ex 11
Comme 10, mais le curseur a deux paramètres – le code de spécialisation et l’année d’étude. Les valeurs par défaut sont 11 et 1.
L5 Ex 12
Comme 11, mais l’appel est effectué avec le premier paramètre de default et le deuxième paramètre égale à une constante.
L5 Ex 13
Comme 6 mais utilisez une variable de type REF_CURSOR pour définir le curseur.
L5 Toate EX
-- EX1 -- BEGIN -- DELETE FROM STUD1 -- WHERE CODS = 21; -- dbms_output.put_line('On a modifie '|| SQL%ROWCOUNT || ' lignes'); -- IF sql%found THEN -- dbms_output.put_line('Found TRUE'); -- ELSE -- dbms_output.put_line('Found FALSE'); -- END IF; -- IF sql%notfound THEN -- dbms_output.put_line('NOTFound TRUE'); -- ELSE -- dbms_output.put_line('NOTFound FALSE'); -- END IF; -- ROLLBACK; -- END; -- EX2 -- BEGIN -- UPDATE FROM STUD1 -- WHERE CODS = 21; -- IF sql%isopen THEN -- dbms_output.put_line('Open TRUE'); -- ELSE -- dbms_output.put_line('Open FALSE'); -- END IF; -- ROLLBACK; -- END; -- ex4 -- BEGIN -- FOR v_stud IN (select nume, punctaj from STUD1 where cods=21) LOOP -- dbms_output.put_line('Etudiant: ' || v_stud.nume || 'puncte: ' || v_stud.punctaj); -- END LOOP; -- END; --EX5 -- DECLARE -- CURSOR studenti IS SELECT * FROM STUD1 WHERE CODS =21; -- v_stud studenti%rowtype; -- BEGIN -- OPEN studenti; -- LOOP -- FETCH studenti INTO v_stud; -- EXIT WHEN studenti%notfound; -- dbms_output.put_line(studenti%rowcount || 'Etudiant: ' || v_stud.nume || ', avec la date de nasissance: ' || v_stud.datan); -- END LOOP; -- CLOSE studenti; -- END; --ex6 -- BEGIN -- OPEN studenti; -- FETCH studenti INTO v_stud; -- WHILE studenti%found LOOP -- dbms_output.put_line(studenti%rowcount|| ' Etudiant: ' || v_stud.nume||' score '||v_stud.punctaj); -- FETCH studenti INTO v_stud; -- END LOOP; --EX7 DECLARE CURSOR studenti IS SELECT * FROM STUD1 WHERE CODS=21; v_stud studenti%rowtype; n number; i number; BEGIN SELECT COUNT(*) INTO n FROM STUD1 WHERE CODS=21; dbms output.put_line('NrStud='||n); OPEN studenti; FOR i IN 1..n LOOP FETCH studenti INTO v_stud; dbms_output.put_line(studenti%rowcount|| ' Etudiant: ' || v_stud.nume||' score '||v_stud.punctaj) END LOOP; CLOSE studenti; END; --ex9 DECLARE CURSOR studenti IS SELECT * FROM STUD1 WHERE CODS=21; BEGIN FOR v_stud IN studenti LOOP dbms_output.put_line(studenti%rowcount|| ' Etudiant: ' || v_stud.nume||' avec la date de naissance '||v_stud.datan); END LOOP; END;
- L4
L4 Ex 1
Ecrivez un bloc PL / SQL qui utilise un enregistrement (en utilisant TYPE) pour lire et afficher le nom et le score (PUNCTAJ) pour l’étudiant avec MATR=4705 en utilisant SELECT INTO.
DECLARE TYPE UNSTUD IS RECORD (NUME VARCHAR2(10), SCOR NUMBER(4)); S UNSTUD; BEGIN SELECT NUME, PUNCTAJ INTO S FROM STUD1 WHERE MATR=4705; DBMS_OUTPUT.PUT_LINE(S.NUME || 'ARE PUNCTAJUL ' || S.SCOR); END;
L4 Ex 2
Comme 1, mais l’enregistrement sera définiavec ROWTYPE
DECLARE student_rec stud%rowtype; BEGIN SELECT * INTO student_rec FROM STUD1 WHERE matr = 4705; DBMS_OUTPUT.PUT_LINE('Nume: '|| student_rec.nume || ', Punctaj: ' || student_rec.punctaj); END;
L4 Ex 3
Comme 2, mais le numéro d’étudiant (MATR) est dans une constante et si l’étudiant n’existe pas il est inséré (en utilisant des valeurs nulles pour les autres colonnes)
DECLARE v_stud stud%rowtype;
BEGIN
…. .EXCEPTION …. .
Insert into stud values v_stud; END;
DECLARE S stud%rowtype; M constant number(4) := 1200; BEGIN SELECT * INTO S FROM STUD1 WHERE MATR=M; DBMS_OUTPUT.PUT_LINE(S.NUME || ' ARE PUNCTAJUL ' || S.PUNCTAJ); EXCEPTION WHEN NO_DATA_FOUND THEN S.MATR := M; insert into STUD1 values S; DBMS_OUTPUT.PUT_LINE('Insert ' || M); END;
Daca exista il afiseaza, altfel il insereaza dupa S, var. de inregistrare.
L4 Ex4
Ecrivez un bloc PL / SQL qui utilise un tableau associatif S pour stocker les informations pour 3 étudiants avec avec des numéros d’inscription (MATR) donnés à l’initialisation dans un autre associative M. Pour S la clé est le numéro d’étudiant et la valeur est l’enregistrement de l’étudiant du tableau STUD. Pour M les clés sont 1, 2 et 3, et les valeurs sont les numéros d’étudiant. Au final le bloc affiche le nombre d’étudiants du S (0, 1, 2 ou 3) en fonction de la validité des numéros lus en M.
DECLARE TYPE t_stud IS TABLE OF stud%ROWTYPE INDEX
BY BINARY_INTEGER; S t_stud;
….
L4 Ex 5
Comme 4 mais au final sont affichés aussi les éléments du tableau S, en utilisant NEXT.
L4 Ex 6
Comme 5 mais au final sont affichés aussi le premier élément et le dernier élément de S et puis la liste de ses éléments dans l’ordre inverse en utilisant PRIOR.
L4 Ex 7
Comme 6, mais en plus, en utilisant DELETE(n), on doit supprimer les éléments ayant come clé la première et la dernière valeur de M, mais seulement après qu’on a teste que les éléments avec ces clés existent dans S.
- L3
L3 ex1
1. Ecrire un bloc PL / SQL qui contient une constante avec un code d’étudiant (MATR) et affiche le nombre de ses disciples:
0 – aucun disciple
1-1 disciple
> 1 – plus de disciplesen utilisant la requête suivante et les exceptions NO_DATA_FOUND et TOO_MANY_ROWS:
SELECT NUME INTO V_NOM FROM STUD WHERE TUTOR = codeetudiant -- constante
DECLARE codeetudiant constant number(4) :=1200; -- codeetudiant constant number(4) :=1456; -- codeetudiant constant number(4) :=4311; v_nom stud.nume%type; BEGIN SELECT NUME INTO v_nom FROM STUD WHERE TUTOR = codeetudiant; DBMS_OUTPUT.PUT_LINE('UNUL'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('ZERO'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('MULTI'); END;
DECLARE TYPE t_stud IS TABLE OF stud.nume % type INDEX BY PLS_INTEGER; v_stud t_stud; -- codeetudiant constant number(4) :=1200; -- codeetudiant constant number(4) :=1456; codeetudiant constant number(4) := 4311; v_nom stud.nume % type; BEGIN -- varianta care genereaza exceptie -- SELECT NUME INTO v_nom FROM STUD WHERE TUTOR = codeetudiant; -- DBMS_OUTPUT.PUT_LINE('UNUL'); -- varianta care preia mai multe valori intr-o variabila SELECT NUME BULK COLLECT INTO v_stud FROM STUD WHERE TUTOR = codeetudiant; dbms_output.put_line('COUNT: ' || v_stud.count); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('ZERO'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('MULTI'); END;
DECLARE TYPE t_stud IS TABLE OF stud.nume % type INDEX BY PLS_INTEGER; v_stud t_stud; -- codeetudiant constant number(4) :=1200; -- codeetudiant constant number(4) :=1456; codeetudiant constant number(4) := 4311; v_nom stud.nume % type; BEGIN -- varianta care preia mai multe valori intr-o variabila SELECT NUME BULK COLLECT INTO v_stud FROM STUD WHERE TUTOR = codeetudiant; dbms_output.put_line('COUNT studenti tutorelui: ' || v_stud.count); FOR i IN v_stud.first..v_stud.last loop DBMS_OUTPUT.PUT_LINE(i || ' ' || v_stud(i)); END LOOP; END;
%RowType luam toata linia, nu numai numele
DECLARE TYPE t_stud IS TABLE OF stud % ROWTYPE INDEX BY PLS_INTEGER; v_stud t_stud; -- codeetudiant constant number(4) :=1200; -- codeetudiant constant number(4) :=1456; codeetudiant constant number(4) := 4311; BEGIN -- varianta care preia mai multe valori intr-o variabila SELECT * BULK COLLECT INTO v_stud FROM STUD WHERE TUTOR = codeetudiant; dbms_output.put_line( 'COUNT studenti tutorelui: ' || v_stud.count ); FOR i IN v_stud.first..v_stud.last loop DBMS_OUTPUT.PUT_LINE(i || ' nume: ' || v_stud(i).nume || ' anul:' || v_stud(i).an || ' punctaj:' || v_stud(i).punctaj); END LOOP; END;
L3 ex2
Le même exercice mais en utilisant les codes d’erreur numériques et WHEN OTHERS avec IF THEN ELSIF ELSE
Les codes sont:
NO_DATA_FOUND code +100 (SQL: -1403) TOO_MANY_ROWS code -1422DECLARE codeetudiant constant number(4) :=1200; -- codeetudiant constant number(4) :=1456; -- codeetudiant constant number(4) :=4311; v_nom stud.nume%type; BEGIN SELECT NUME INTO v_nom FROM STUD WHERE TUTOR = codeetudiant; DBMS_OUTPUT.PUT_LINE('UNUL'); EXCEPTION WHEN OTHERS THEN IF SQLCODE = 100 THEN DBMS_OUTPUT.PUT_LINE('ZERO'); DBMS_OUTPUT.PUT_LINE(SQLCODE); ELSIF SQLCODE = -1422 THEN DBMS_OUTPUT.PUT_LINE('MULTI'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END IF; END;
L3 ex3
Le même exercice mais en utilisant les codes d’erreur numériques et WHEN OTHERS avec CASE
Les codes sont:
NO_DATA_FOUND code +100 (SQL: -1403) TOO_MANY_ROWS code -1422DECLARE codeetudiant constant number(4) :=1200; -- codeetudiant constant number(4) :=1456; -- codeetudiant constant number(4) :=4311; v_nom stud.nume%type; code number; BEGIN SELECT NUME INTO v_nom FROM STUD WHERE TUTOR = codeetudiant; DBMS_OUTPUT.PUT_LINE('UNUL'); EXCEPTION WHEN OTHERS THEN code := SQLCODE; CASE code WHEN 100 THEN DBMS_OUTPUT.PUT_LINE('ZERO'); WHEN -1422 THEN DBMS_OUTPUT.PUT_LINE('MULTI'); END CASE; END;
L3 ex4
Ecrire un bloc PL / SQL qui contient une constante avec un code d’étudiant (MATR) et affiche le nombre de ses disciples:
0 – aucun disciple
1-1 disciple
> 1 – plus de disciplesen utilisant la requête suivante:
SELECT COUNT(*)
INTO V_NR
FROM STUD
WHERE TUTOR = codeetudiant — constanteet trois exception definites par l’utilisateur: E0, E1 et EM (on peut utiliser IF ou CASE)
DECLARE -- codeetudiant constant number(4) :=1200; -- codeetudiant constant number(4) :=1456; codeetudiant constant number(4) :=4311; v_nom stud.nume%type; code number; V_NR number; E0 EXCEPTION; E1 EXCEPTION; E2 EXCEPTION; BEGIN SELECT COUNT(*) INTO V_NR FROM STUD WHERE TUTOR = codeetudiant; -- constante IF (V_NR = 0) THEN RAISE E0; ELSIF (V_NR = 1) THEN RAISE E1; ELSE RAISE E2; END IF; EXCEPTION WHEN E0 THEN DBMS_OUTPUT.PUT_LINE('0'); WHEN E1 THEN DBMS_OUTPUT.PUT_LINE('1'); WHEN E2 THEN DBMS_OUTPUT.PUT_LINE('>1'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Autre exception'); END;
L3 ex5
Le même exercice (comme 4) mais en utilisant RAISE_APPLICATION_ERROR
Sans des noms pour les exceptions (seulement des codes entre -20000 et – 20999)
Avec des noms pour les exceptions: E0, E1 et EM
DECLARE -- codeetudiant constant number(4) :=1200; codeetudiant constant number(4) :=1456; -- codeetudiant constant number(4) :=4311; v_nom stud.nume%type; code number; V_NR number; E0 EXCEPTION; PRAGMA EXCEPTION_INIT(E0, -20000); E1 EXCEPTION; PRAGMA EXCEPTION_INIT(E1, -20001); E2 EXCEPTION; PRAGMA EXCEPTION_INIT(E2, -20002); BEGIN SELECT COUNT(*) INTO V_NR FROM STUD WHERE TUTOR = codeetudiant; -- constante IF (V_NR = 0) THEN RAISE_APPLICATION_ERROR(-20000, '0'); ELSIF (V_NR = 1) THEN RAISE_APPLICATION_ERROR(-20001, '1'); ELSE RAISE_APPLICATION_ERROR(-20002, '>1'); END IF; EXCEPTION WHEN E0 THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); WHEN E1 THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); WHEN E2 THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Autre exception'); END;
L3 ex6
Ecrire un bloc PL / SQL qui contient la declaration suivante:
sal_min CONSTANT NUMBER(3) := 1200;
Et le traitement pour elle
BEGIN DECLARE sal_min CONSTANT NUMBER(3) := 1200; BEGIN DBMS_OUTPUT.PUT_LINE('INSTR SUBBLOC'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('TRATARE ERORI SUBBLOC'); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('TRATARE ERORI BLOC'); END;
Ex 7
Ecrire un bloc PL / SQL qui contient la déclaration suivante:
sal_min NUMBER(3);
Et l’instruction (après BEGIN):
sal_min := 1200;
Et le traitement pour elle
Ex 8
Le même exercice (comme 7) mais l’exception se propage vers un bloc qui entoure le bloc contenant l’exception.
- L2
Create Database
CREATE TABLE spec ( cods NUMBER(2), nume VARCHAR2(10), domeniu VARCHAR2(15) ); INSERT INTO spec VALUES ( 11, 'MATEMATICA', 'STIINTE EXACTE' ); INSERT INTO spec VALUES (21, 'GEOGRAFIE', 'UMANIST'); INSERT INTO spec VALUES (24, 'ISTORIE', 'UMANIST'); CREATE TABLE stud ( matr NUMBER(4), nume VARCHAR2(10), an NUMBER(1), grupa VARCHAR2(6), datan DATE, loc VARCHAR2(10), tutor NUMBER(4), punctaj NUMBER(4), cods NUMBER(2) ); INSERT INTO stud VALUES ( 1456, 'GEORGE', 4, '1141A', '12-MAR-82', 'BUCURESTI', NULL, 2890, 11 ); INSERT INTO stud VALUES ( 1325, 'VASILE', 2, '1122A', '05-OCT-84', 'PITESTI', 1456, 390, 11 ); INSERT INTO stud VALUES ( 1645, 'MARIA', 3, '1131B', '17-JUN-83', 'PLOIESTI', NULL, 1400, 11 ); INSERT INTO stud VALUES ( 3145, 'ION', 1, '2112B', '24-JAN-85', 'PLOIESTI', 3251, 1670, 21 ); INSERT INTO stud VALUES ( 2146, 'STANCA', 4, '2141A', '15-MAY-82', 'BUCURESTI', NULL, 620, 21 ); INSERT INTO stud VALUES ( 3251, 'ALEX', 5, '2153B', '07-NOV-81', 'BRASOV', NULL, 1570, 21 ); INSERT INTO stud VALUES ( 2215, 'ELENA', 2, '2122A', '29-AUG-84', 'BUCURESTI', 2146, 890, 21 ); INSERT INTO stud VALUES ( 4311, 'ADRIAN', 3, '2431A', '31-JUL-83', 'BUCURESTI', NULL, 450, 24 ); INSERT INTO stud VALUES ( 3514, 'FLOREA', 5, '2452B', '03-FEB-81', 'BRASOV', NULL, 3230, 24 ); INSERT INTO stud VALUES ( 1925, 'OANA', 2, '2421A', '20-DEC-84', 'BUCURESTI', 4311, 760, 24 ); INSERT INTO stud VALUES ( 2101, 'MARIUS', 1, '2412B', '02-SEP-85', 'PITESTI', 3514, 310, 24 ); INSERT INTO stud VALUES ( 4705, 'VOICU', 2, '2421B', '19-APR-84', 'BRASOV', 4311, 1290, 24 ); CREATE TABLE bursa ( tip VARCHAR2(20), pmin NUMBER(4), pmax NUMBER(4), suma NUMBER(4) ); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES (0, 399, 'FARA BURSA', NULL); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES (400, 899, 'BURSA SOCIALA', 100); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES (900, 1799, 'BURSA DE STUDIU', 150); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES (1800, 2499, 'BURSA DE MERIT', 200); INSERT INTO bursa (pmin, pmax, tip, suma) VALUES ( 2500, 9999, 'BURSA DE EXCEPTIE', 300 );
1. Ecrire un bloc PL / SQL qui affiche le nom de l’étudiant avec le numero matricole 2215 avec une requete (SELECT … INTO et affichage):
a. En utilisant une variable VARCHAR2 (10)
DECLARE v_nume VARCHAR2(10); BEGIN SELECT NUME INTO v_nume FROM SPEC WHERE CODS > 11; DBMS_OUTPUT.PUT_LINE('Spécialisation: ' || v_nume); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Erreur ou autre exception'); END;
b. En utilisant une variable déclarée avec TYPE%
DECLARE v_nume stud.nume % type; v_matr number(4); BEGIN v_matr := 1456; SELECT NUME INTO v_nume FROM STUD WHERE MATR = v_matr; dbms_output.put_line(v_nume); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('ABSENT'); WHEN too_many_rows THEN dbms_output.put_line('PLUSIEURS'); WHEN others THEN dbms_output.put_line('Autre erreur'); END;
DECLARE v_nume stud.nume % type; v_matr number(4); v_nr VARCHAR(10); BEGIN SELECT MATR INTO v_matr FROM STUD WHERE ROWNUM = 1; dbms_output.put_line(v_matr); SELECT NUME INTO v_nr FROM STUD WHERE MATR = v_matr; dbms_output.put_line(v_nr); end;
c. En utilisant un numero matricole lu par SQL * Plus (avec &nom_var_sqlplus)
DECLARE nume_stud stud.nume % type; BEGIN SELECT NUME INTO nume_stud FROM STUD WHERE MATR = 2215; DBMS_OUTPUT.PUT_LINE( 'Nom de student avec numero matricol 2215: ' || nume_stud ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Erreur ou autre exception'); END; -- c) DECLARE v_nume stud.nume % type; v_matr number(4); v_nr VARCHAR(10); BEGIN SELECT MATR INTO v_matr FROM STUD WHERE ROWNUM = 1; dbms_output.put_line(v_matr); SELECT NUME INTO v_nr FROM STUD WHERE MATR = v_matr; dbms_output.put_line(v_nr); end;
DECLARE v_nr stud.nume % type; v_matr NUMBER := 1456; v_var NUMBER; BEGIN SELECT count(*) INTO v_var FROM STUD WHERE MATR = v_matr; IF (v_var = 0) THEN dbms_output.put_line('INEXISTENT'); ELSE SELECT MATR INTO v_matr FROM STUD WHERE ROWNUM = 1; dbms_output.put_line(v_matr); SELECT NUME INTO v_nr FROM STUD WHERE MATR = v_matr; dbms_output.put_line(v_nr); END IF; END;
DECLARE v_nr stud.nume % type; v_matr NUMBER := 1455; v_var NUMBER; BEGIN SELECT count(*) INTO v_var FROM STUD WHERE MATR = v_matr; IF (v_var = 0) THEN dbms_output.put_line('INEXISTENT'); ELSE SELECT MATR INTO v_matr FROM STUD WHERE ROWNUM = 1; dbms_output.put_line(v_matr); SELECT NUME INTO v_nr FROM STUD WHERE MATR = v_matr; dbms_output.put_line(v_nr); END IF; END;
- Ex 2019
CREATE TABLE SPEC( CODS NUMBER(2), NUME VARCHAR2(10), DOMENIU VARCHAR2(15)
);
INSERT INTO SPEC VALUES(11, ‘MATEMATICA’,’STIINTE EXACTE’);
INSERT INTO SPEC VALUES(21, ‘GEOGRAFIE’, ‘UMANIST’);
INSERT INTO SPEC VALUES(24, ‘ISTORIE’, ‘UMANIST’);
CREATE TABLE STUD( MATR NUMBER(4), NUME VARCHAR2(10), AN NUMBER(1), GRUPA VARCHAR2(6), DATAN DATE,
LOC VARCHAR2(10), TUTOR NUMBER(4), PUNCTAJ NUMBER(4), CODS NUMBER(2)
);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘1456′,’GEORGE’,’4′,’1141A’,to_date(’12- MAR-82′,’DD-MON- YY’),’BUCURESTI’,”,’2890′,’11’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘1325′,’VASILE’,’2′,’1122A’,to_date(’05- OCT-84′,’DD-MON- YY’),’PITESTI’,’1456′,’390′,’11’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘1645′,’MARIA’,’3′,’1131B’,to_date(’17-JUN- 83′,’DD-MON-YY’),’PLOIESTI’,”,’1400′,’11’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘3145′,’ION’,’1′,’2112B’,to_date(’24-JAN- 85′,’DD-MON- YY’),’PLOIESTI’,’3251′,’1670′,’21’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘2146′,’STANICA’,’4′,’2141A’,to_date(’15- MAY-82′,’DD-MON- YY’),’BUCURESTI’,”,’620′,’21’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘3251′,’ALEX’,’5′,’2153B’,to_date(’07-NOV- 81′,’DD-MON-YY’),’BRASOV’,”,’1570′,’21’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘2215′,’ELENA’,’2′,’2122A’,to_date(’29- AUG-84′,’DD-MON- YY’),’BUCURESTI’,’2146′,’890′,’21’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘4311′,’ADRIAN’,’3′,’2431A’,to_date(’31- JUL-83′,’DD-MON- YY’),’BUCURESTI’,”,’450′,’24’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘3514′,’FLOREA’,’5′,’2452B’,to_date(’03- FEB-81′,’DD-MON- YY’),’BRASOV’,”,’3230′,’24’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘1925′,’OANA’,’2′,’2421A’,to_date(’20- DEC-84′,’DD-MON- YY’),’BUCURESTI’,’4311′,’760′,’24’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘2101′,’MARIUS’,’1′,’2412B’,to_date(’02- SEP-85′,’DD-MON- YY’),’PITESTI’,’3514′,’310′,’24’);
insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values
(‘4705′,’VOICU’,’2′,’2412B’,to_date(’19- APR-84′,’DD-MON- YY’),’BRASOV’,’4311′,’1290′,’24’);
CREATE TABLE BURSA( TIP VARCHAR2(20), PMIN NUMBER(4), PMAX NUMBER(4), SUMA NUMBER(4)
);
INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(0, 399, ‘FARA BURSA’, NULL);
INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(400, 899, ‘BURSA SOCIALA’, 100);
INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(900, 1799, ‘BURSA DE STUDIU’, 150);
INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(1800, 2499, ‘BURSA DE MERIT’, 200);
INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(2500, 9999, ‘BURSA DE EXCEPTIE’, 300);
select MATR||’-‘||NUME STUDENT, ‘ESTE IN’, GRUPA
FROM STUD;STUDENT GRUPA
1456-GEORGE 1141A
select DISTINCT CODS FROM STUD;
CODS 11 21 24
'ESTE IN' ESTE IN
select DISTINCT CODS,NUME FROM STUD ORDER BY CODS DESC;
CODS NUME
24 ADRIAN
24 FLOREA
24 MARIUS
24 OANA
24 VOICU
21
….
ALEX
select DISTINCT CODS,NUME FROM STUD
ORDER BY NUME, CODS DESC;CODS NUME
24 ADRIAN 21 ALEX
21 ELENA 24 FLOREA 11 GEORGE 21 ION
11 MARIA 24 MARIUS 24 OANA
21 STANICA 11 VASILE 24 VOICUselect DISTINCT CODS,NUME FROM STUD
WHERE CODS = 11
ORDER BY NUME DESC;CODS NUME
11 VASILE 11 MARIA
11 GEORGESELECT NUME, PUNCTAJ
FROM STUD
WHERE PUNCTAJ BETWEEN 1000 AND 2000;NUME PUNCTAJ MARIA 1400 ION 1670 ALEX 1570 VOICU 1290
SELECT NUME, PUNCTAJ, CODS COD_STUDENT
FROM STUD
WHERE CODS IN (11,21);NUME PUNCTAJ COD_STUDENT GEORGE 2890 11
VASILE
MARIA
ION
STANICA 620 21 ALEX 1570 21 ELENA 890 21390 11 1400 11 1670 21
SELECT NUME, PUNCTAJ, CODS COD_STUDENT
FROM STUD
WHERE CODS LIKE ‘2%’;NUME PUNCTAJ COD_STUDENT ION 1670 21
STANICA 620 21
ALEX 1570 21ELENA 890 21 ADRIAN 450 24 FLOREA 3230 24 OANA 760 24 MARIUS 310 24 VOICU 1290 24
Simbol Reprezentare ------ --
% secventa de zero sau mai multe caractere
- un singur caracter oarecare
SELECT NUME, PUNCTAJ, CODS COD_STUDENT
FROM STUD
WHERE NUME LIKE ‘____’;NUME PUNCTAJ COD_STUDENT ALEX 1570 21
OANA 760 24SELECT NUME
FROM STUD
WHERE TUTOR IS NULL;NUME GEORGE MARIA STANICA ALEX ADRIAN FLOREA
Urmatorii operatori fac teste de negatie:
Operator Descriere
--------- ----------
!= diferit de(VAX,UNIX,PC)
^= diferit de(IBM)
diferit de(toate o/s)
NOT NUMECOL= diferit de
NOT NUMECOL> mai mic sau egal
Operatori SQL
Operator Descriere
-------- ---------
NOT BETWEEN nu se afla intre doua valori
date
NOT INnu se afla intr-o lista data de valori
NOT LIKE diferit de sirul
IS NOT NULL nu este o valoare nula
SELECT NUME, CODS FROM STUD WHERE CODS NOT LIKE '2%';
NUME CODS GEORGE 11 VASILE 11 MARIA 11
SELECT NUME, TUTOR FROM STUD
WHERE TUTOR IS NOT NULL;NUME TUTOR VASILE 1456 ION 3251 ELENA 2146 OANA 4311 MARIUS 3514 VOICU 4311
SELECT NUME, PUNCTAJ, TUTOR FROM STUD WHERE PUNCTAJ BETWEEN 1000 AND 2000
OR TUTOR = 3514 ORDER BY PUNCTAJ DESC;
NUME PUNCTAJ TUTOR ION 1670 3251 ALEX 1570 – MARIA 1400 – VOICU 1290 4311 MARIUS 310 3514
SELECT NUME
FROM STUD
WHERE NUME LIKE ‘%AN%’; // PT ‘%AN’ VA AFISA DOAR ADRIANNUME STANICA ADRIAN OANA
SELECT NUME, DATAN „DATA DE NASTERE”
FROM STUD
WHERE DATAN LIKE ‘%85’;NUME DATA DE NASTERE ION 24-JAN-85 MARIUS 02-SEP-85
SELECT NUME, DATAN „DATA DE NASTERE”
FROM STUD
WHERE DATAN LIKE ‘%JU%’;———-
orice
NUME MARIA ADRIAN
DATA DE NASTERE 17-JUN-83 31-JUL-83
SELECT TIP, SUMA, (SUMA+20)*1.1 „BURSA MARITA”
FROM BURSA;
TIP
FARA BURSA BURSA SOCIALA
BURSA DE STUDIU
SUMA BURSA MARITA
– – 100 132
150 187 BURSA DE MERIT 200 242
BURSA DE EXCEPTIE
300 352
SELECT NUME, LOC, DATAN FROM STUD WHERE LOC IN (‘BUCURESTI’, ‘PLOIESTI’) OR DATAN IN (’02-SEP-85′, ’19-APR-84′, ’29-AUG-84′);
NUME LOC DATAN GEORGE BUCURESTI 12-MAR-82 MARIA PLOIESTI 17-JUN-83 ION PLOIESTI 24-JAN-85 STANICA BUCURESTI 15-MAY-82 ELENA BUCURESTI 29-AUG-84 ADRIAN BUCURESTI 31-JUL-83 OANA BUCURESTI 20-DEC-84
SELECT NUME||’_’||DOMENIU AS „NUME SI DOMENIU”
FROM SPEC
WHERE NUME||’_’||DOMENIU LIKE ‘%\_U%’ ESCAPE ‘\’;SAU
SELECT NUME||’_’||DOMENIU AS „NUME SI DOMENIU”
FROM SPEC
WHERE NUME||’_’||DOMENIU LIKE ‘%U%’;NUME SI DOMENIU GEOGRAFIE_UMANIST ISTORIE_UMANIST
SELECT CODS, NUME, PUNCTAJ, (PUNCTAJ+20) PMARIT
FROM STUD
WHERE CODS=11ORDER BY PMARIT;
CODS NUME
11 VASILE
11 MARIA
11 GEORGE 2890 2910PUNCTAJ PMARIT 390 410 1400 1420
SELECT CODS, NUME, PUNCTAJ, (PUNCTAJ+20)*1.1 PMARIT, (PUNCTAJ- (PUNCTAJ+20)*1.1) „CRITERIU ORDONARE” FROM STUD
WHERE CODS=11 ORDER BY PUNCTAJ- PMARIT;
CODS NUME PUNCTAJ PMARIT CRITER ORDON
11 GEORGE 2890 11 MARIA 1400 11 VASILE 390
3201 -311 1562 -162 451 -61
!0 “0 #$% ORDER BY 3 DESC, 2;
MATR NUME AN 3251 ALEX 5 3514 FLOREA 5 1456 GEORGE 4 2146 STANICA 4 4311 ADRIAN 3 1645 MARIA 3 2215 ELENA 2 1925 OANA 2 1325 VASILE 2 4705 VOICU 2 3145 ION 1 2101 MARIUS 1
SELECT DISTINCT STUD.NUME, DATAN, DOMENIU, SPEC.NUME, STUD.CODS FROM STUD, SPEC
WHERE STUD.CODS=SPEC.CODS ORDER BY STUD.NUME;NUME DATAN DOMENIU NUME
31-
ADRIAN JUL- UMANIST ISTORIE83
07-
ALEX NOV- UMANIST GEOGRAFIE81
29-
ELENA AUG- UMANIST GEOGRAFIE84
03-
FLOREA FEB- UMANISTISTORIE81
12- STIINTE
GEORGE MAR- EXACTE MATEMATIC82
24-
ION JAN- UMANIST GEOGRAFIE85
17- STIINTE
MARIA JUN- EXACTE MATEMATIC83
02-
MARIUS SEP- UMANISTISTORIE85
20-
OANA DEC- UMANIST ISTORIE84
15-
STANICA MAY- UMANISTGEOGRAFIE82
05- STIINTE
VASILE OCT- EXACTE MATEMATIC84
19-
VOICU APR- UMANIST ISTORIE84
SELECT S1.NUME „STUDENT 1”, S2.NUME „STUDENT 2”,S2.TUTOR „TUTOR”
FROM STUD S1, STUD S2
WHERE S1.TUTOR=S2.TUTORAND S1.MATR > S2.MATR; // DACA NU AVEAM CONDITIA AR FI AFISAT
SELECT MATR, NUME, AN
FROM STUD
STUDENT 1 VOICU
STUDENT 1 VASILE ION ELENA OANA VOICU MARIUS OANA VOICU
STUDENT 2 TUTOR OANA 4311
STUDENT 2 TUTOR VASILE 1456 ION 3251 ELENA 2146 OANA 4311 OANA 4311 MARIUS 3514 VOICU 4311 VOICU 4311
SELECT S.NUME „NUME STUD”, S.AN „AN STUD”, T.NUME „NUME TUTOR”, T.AN „AN TUTOR”
FROM STUD S, STUD TWHERE S.CODS = 11 AND S.TUTOR=T.MATR(+); // PENTRU A ADAUGA ȘI STUDENTII FARA TUTOR
NUME AN NUME AN
I
A
A
A