EXO PLSQL 0
Boucle simple

-- le bloc
SET SERVEROUTPUT ON
DECLARE
n INT :=10;
s INT:=0;
BEGIN
FOR i IN 1..n
LOOP
S:=S+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('La somme est ; '|| to_char(S));
END;
.

-- la fonction

SET SERVEROUTPUT ON
CREATE OR REPLACE FUNCTION SOM(n INT)
RETURN INT
IS
s INT:=0;
BEGIN
FOR i IN 1..n
LOOP
S:=S+i;
END LOOP;
RETURN(s);
END;
.

SELECT SOM(100) FROM DUAL;

Suite de FIBONACCI

DECLARE
n INT :=20;
FN integer :=0;
FNm1 integer :=1;
FNm2 integer :=0;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Suite de Fibo: ‘);
FOR i IN 1..n
LOOP
FN:=FNm1+FNm2;
DBMS_OUTPUT.PUT_LINE(FN);
FNm2:=FNm1;
FNm1:=FN;
END LOOP;
END;

Exo PLSQL 0 bis
Calcul de PI avec fonction et boucle FOR

create or replace Function pee (n IN integer)
return float
is s float:=0;
begin
for i in 1..n loop
s := s+ 1/(cast (i as number) *i);
end loop;
return sqrt(6*s);
end;

EXO PLSQL 1
Affichage du salaire moyen d’un DEPT avec SELECT INTO

CREATE OR REPLACE PROCEDURE sal_moy (no_dep INT)
IS
salm NUMBER;
pas_dep EXception;
nb_dep INT;
BEGIN
SELECT count(*) INTO nb_dep FROM emp
WHERE DEPTNO= no_dep;
IF nb_dep=0 THEN
RAISE pas_dep;
END IF;
SELECT TRUNC(avg(SAL),2) INTO salm FROM EMP
WHERE DEPTNO=no_dep;

DBMS_OUTPUT.PUT_LINE(‘Le salaire moyen est:’|| salm);

EXCEPTION
WHEN pas_dep THEN DBMS_OUTPUT.PUT_LINE(‘Pas de dept avec ce no!’);
END;
.

EXO PLSQL 2
Création et remplissage d’une table EMP_FR avec INSERT sur Curseur FOR

create table emp_fr as select * from emp
WHERE 1=2;

— Remplissage de la table EMP_FR

DECLARE
n INT;
CURSOR employes IS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp;
employe employes%ROWTYPE;
newsal emp.sal%TYPE;
newcomm emp.comm%TYPE;
empvide EXCEPTION;

BEGIN
— Test table vide
SELECT COUNT(*) INTO n FROM emp;
IF n=0 THEN RAISE empvide;
END IF;
— Remplissage emp_fr
FOR employe IN employes LOOP
— Calculs
newsal:=employe.sal*0.9;
newsal:=newsal*1.1;
IF employe.comm IS NOT NULL THEN
newcomm:=employe.comm*6;
ELSE
newcomm:=NULL;
END IF;
— Insertion donnees
INSERT INTO emp_fr VALUES(employe.empno, employe.ename, employe.job,employe.mgr, employe.hiredate, newsal, newcomm, employe.deptno);
END LOOP;
COMMIT;

EXCEPTION
WHEN empvide THEN RAISE_APPLICATION_ERROR(-20501,’Pas d employe !’);
END;
.

EXO PLSQL 3 : simuler un AUTOINCREMENT avec Oracle

— vérifier l’existence de la contrainte

se connecter SYSTEM
aller dans la branche « autres utilisateurs’ dans le volet gauche, choisir SCOTT
puis afficher la table EMP dans le menu table et choisir l’onglet CONSTRAINTS

— la créer

SQL> alter table SCOTT.EMP
add constraint PK_EMPNO primary key (EMPNO);

— créer une sequence pour stocker l’increment

CREATE SEQUENCE S_empno
START WITH 1
INCREMENT BY 1
CACHE 10;

— créer un TRIGGER pour automatiser l’incrément

CREATE OR REPLACE TRIGGER T_empno
BEFORE INSERT
ON emp
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
if(:new.EMPNO is null) then
SELECT S_empno.nextval
INTO :new.empno
FROM dual;
end if;
END;
/

ALTER TRIGGER T_empno ENABLE;

Exo PLSQL 4
Utilisation de packages prédéfinis pour lire un fichier

CREATE TABLE cclient (
id Number(3),  nom varchar(25), ville varchar(35), datec date , ca number (6,2),
CONSTRAINT client_pk PRIMARY KEY (id)
);

create or replace procedure file_insert is
infile utl_file.file_type;
buffer varchar(90);
chaine varchar(90);
cid number(3);
cnom varchar(25);
cville varchar (35);
cdatec date;
cca number(6,2);
begin
infile := utl_file.fopen(‘MY_DIR’, ‘fic.csv’, ‘r’);
if utl_file.is_open(infile) then
utl_file.get_line(infile,buffer); — on skippe la ligne d’entete
loop
begin
utl_file.get_line(infile, buffer);
chaine := buffer;
cid:=to_number(substr(chaine,0,instr(chaine,’;’)-1));
cnom:= substr(chaine,instr(chaine,’;’,1,1)+1,(instr(chaine,’;’,1,2)-instr(chaine,’;’,1,1))-1);
cville:= substr(chaine,instr(chaine,’;’,1,2)+1,(instr(chaine,’;’,1,3)-instr(chaine,’;’,1,2))-1);
cdatec:= to_date(substr(chaine,instr(chaine,’;’,1,3)+1,(instr(chaine,’;’,1,4)-   instr(chaine,’;’,1,3))-1));
cca:= to_number(substr(chaine,instr(chaine,’;’,1,4)+1,1000));
dbms_output.put_line(cid);
dbms_output.put_line(cnom);
dbms_output.put_line(cville);
dbms_output.put_line(cdatec);
dbms_output.put_line(cca);
insert into cclient(id,nom,ville,datec,ca)
values(cid,cnom,cville,cdatec,cca);

EXCEPTION
WHEN no_data_found THEN
EXIT;
end;
end loop;
COMMIT;
end if;

utl_file.fclose(infile);
end;
/

Autre exemple utl_file: lit fic et insert table

CREATE OR REPLACE PROCEDURE APPS.load_data
AS
v_line VARCHAR2(2000); — Data line read from input filev_file UTL_FILE.FILE_TYPE; — Data file handle
v_dir VARCHAR2(250); — Directory containing the data file
v_filename VARCHAR2(50); — Data filename
v_1st_Comma number;
v_per_id xx_person_details.per_id%type;
v_per_name xx_person_details.per_name%type;
BEGIN
v_dir := ‘/erp/tmp’;
v_filename := ‘xx_sample.dat’;
v_file := UTL_FILE.FOPEN(v_dir, v_filename, ‘r’);
— ——————————————————–
— Loop over the file, reading in each line. GET_LINE will
— raise NO_DATA_FOUND when it is done, so we use that as
— the exit condition for the loop.
— ——————————————————–
LOOP
BEGIN
UTL_FILE.GET_LINE(v_file, v_line);
EXCEPTION
WHEN no_data_found THEN
exit;
END;
— ———————————————————-
— Each field in the input record is delimited by commas. We
— need to find the locations of the two commas in the line,
— and use these locations to get the fields from v_line.
— ———————————————————-
v_1st_Comma := INSTR(v_line, ‘,’ ,1 , 1);
v_per_name := SUBSTR(v_line, 1, v_1st_Comma-1);
v_per_id := SUBSTR(v_line, v_1st_Comma+1);
DBMS_OUTPUT.PUT_LINE(v_per_name ||’ ‘|| v_per_id);
— ——————————————
— Insert the new record into the DEPT table.
— ——————————————
INSERT INTO xx_person_details
VALUES (v_per_name,v_per_id);
END LOOP;
UTL_FILE.FCLOSE(v_file);
COMMIT;
END;
/