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'); ELSE 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
< 21

Case 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: 36

DECLARE
   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
Ionescu

Collections – 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: 2

The 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;