les Packages PL/SQL

Oracle PL-SQL : Utilisation des Packages

Un package permet de stocker dans le meme objet , un ensemble de procedure, fonction, curseur, triggers
afin de permettre une bonne gestion des objets d'un utilisateur.
dans cette exemple, je vous montre un package avec une fonction et une procedure
à l'éxecution, l'appel de la fonction ou bien de la procedure est indixé par le nom du package
Nompackage.nomFonctoin.

SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PACKAGE PkTeste IS TYPE Typ_rec IS RECORD (Nom employe.empnom%TYPE, Fonction employe.fonction%TYPE); -- declaration du type tableau pour les noms et fonctions TYPE TypeFonct IS TABLE OF Typ_rec INDEX BY BINARY_INTEGER; TabFonc TypeFonct; AucuneFonct EXCEPTION; --déclaration de la fonction qui retourne les fonctions des employés -- ayant un certain salaire fourni en paramétre FUNCTION FonctionEmp (Salaire NUMBER DEFAULT 1500) RETURN TypeFonct; PROCEDURE AffichageResFct; END PkTeste; / CREATE OR REPLACE PACKAGE BODY PkTeste IS FUNCTION FonctionEmp (Salaire NUMBER DEFAULT 1500) RETURN TypeFonct IS CURSOR CurFoncEmp IS SELECT empnom,fonction FROM employe WHERE sal=Salaire; Indice BINARY_INTEGER:=1; BEGIN FOR EnrCur in CurFoncEmp LOOP -- chargement du tableau à partir du curseur TabFonc(Indice).Nom:=EnrCur.empnom; TabFonc(Indice).Fonction:=EnrCur.fonction; Indice:=Indice+1; END LOOP; RETURN TabFonc; END FonctionEmp; PROCEDURE AffichageResFct IS BEGIN --appel de la fonction avec valeur par defaut TabFonc:=FonctionEmp; --affichage des noms et fonctions des employés IF TabFonc.COUNT=0 THEN RAISE AucuneFonct; ELSE DBMS_OUTPUT.PUT_LINE('Les employés touchant le salaire de 1500 occupent les fonctions'); DBMS_OUTPUT.PUT_LINE(' Nom '||' Fonction '); DBMS_OUTPUT.PUT_LINE('******************************'); FOR Ind IN 1 ..TabFonc.COUNT LOOP DBMS_OUTPUT.PUT_LINE(TabFonc(Ind).Nom||' '||TabFonc(Ind).Fonction); END LOOP; END IF; --appel de la fonction avec la valeur 2500 TabFonc:=FonctionEmp(2500); --affichage des fonctions des employés IF TabFonc.COUNT=0 THEN RAISE AucuneFonct; ELSE DBMS_OUTPUT.PUT_LINE('Les employes touchant le salaire de 2500 ' ||' occupent les fonctions'); DBMS_OUTPUT.PUT_LINE(' Nom '||' Fonction '); DBMS_OUTPUT.PUT_LINE('******************************'); FOR Ind IN 1 ..TabFonc.COUNT LOOP DBMS_OUTPUT.PUT_LINE(TabFonc(Ind).Nom||' '||TabFonc(Ind).Fonction); END LOOP; END IF; EXCEPTION WHEN AucuneFonct THEN DBMS_OUTPUT.PUT_LINE('Il n''y a aucun employé qui touche ce salaire '); END AffichageResFct; END PkTeste; PL/SQL successfully completed.

Oracle PL-SQL: Division par zero

Oracle PL/SQL Gestion des éxception Utilisateur avec le RAISE.

Definition d'une EXCEPTION non associee à une erreur Oracle ?

PL/SQL permet de definir ses propres EXCEPTIONS, avec la commande RAISE qui interrompt le programme et transfère au gestionnaire d'EXCEPTION.

La déclaration du nom de l'exception doit se trouver dans la partie déclarative.

Voici un exemple (avec et sans gestion des erreurs) ou PLSQL soulève :

? EXCEPTION UTILISATEUR e_dept_innexistant car le département40 n'existe pas dans la base.
On s’aperçoit que pour la même requête nous avons deux messages bien différent.

APPEL EXCEPTION UTILISATEUR avec RAISE.

SQL> SET SERVEROUTPUT ON; SQL> BEGIN 2 DELETE FROM emp WHERE deptno = 40; 3 COMMIT; 4 dbms_output.put_line('Lignes departement 40 supprimees'); 5 END; 6 / Lignes departement 40 supprimees PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON; SQL> DECLARE 2 dept_InnexistantEXCEPTION; 3 BEGIN 4 DELETE FROM emp WHERE deptno =40; 5 IF sql%NOTFOUND THEN 6 RAISE dept_Innexistant; 7 END IF; 8 COMMIT; 9 dbms_output.put_line('Lignes departement40 supprimees'); 10 11 EXCEPTION 12 WHEN dept_InnexistantTHEN 13 dbms_output.put_line('Departement40 innexistant'); 14 WHEN OTHERS THEN 15 dbms_output.put_line('Autres Erreurs'); 16 END; 17 / Departement 40 innexistant PL/SQL procedure successfully completed.

 

----------------------------------------------------------------------------------- ------------------------------------------------------------------------------------

Oracle PL/SQL ERROR CURSOR ZERO_DIVIDE EXCEPTIONS.

Comment gérer l'érreur Oracle  ORA-01476: divisor is equal to zero  dans un bloc EXCEPTION PLSQL ?

PL/SQL dispose d'un mécanisme de gestion des érreurs qui permet de traiter ces évènements dans le BLOC EXCEPTION.

Ce curseur c_emp renvoit des enregistrements dont le champ v_emp.comm qui renvoit des valeurs égales à 0. Céla provoque une division par zéro.

Voici un exemple (avec et sans gestion des érreurs) ou PLSQL soulève :

? EXCEPTION prédéfinie ZERO_DIVIDE si v_emp.comm=0.

EXCEPTION prédéfinie ZERO_DIVIDE.

SQL> SET SERVEROUTPUT ON; SQL> DECLARE 2 v_eval_prime emp.comm%type; 3 v_emp emp%rowtype; 4 CURSOR c_emp IS 5 SELECT ename, job, comm, sal 6 FROM emp 7 WHERE deptno = 20; 8 BEGIN 9 OPEN c_emp; 10 LOOP 11 FETCH c_emp INTO v_emp.ename, v_emp.job, v_emp.comm, v_emp.sal; 12 EXIT WHEN c_emp%NOTFOUND; 13 v_eval_prime := v_emp.comm + (v_emp.sal/v_emp.comm); 14 dbms_output.put_line('Name = '||v_emp.ename || ' Job = ' || v_emp.job || 15 ' Nouvelle Prime = ' ||v_eval_prime); 16 END LOOP; 17 CLOSE c_emp; 18 END; 19 / Name = ALLEN Job = SALESMAN Nouvelle Prime = 305.33 Name = WARD Job = SALESMAN Nouvelle Prime = 480.5 Name = MARTIN Job = SALESMAN Nouvelle Prime = 1400.89 Name = BLAKE Job = MANAGER Nouvelle Prime = DECLARE * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 13

SQL> SET SERVEROUTPUT ON; SQL> DECLARE 2 v_eval_prime emp.comm%type; 3 v_emp emp%rowtype; 4 CURSOR c_emp IS 5 SELECT ename, job, comm, sal 6 FROM scott.emp 7 WHERE deptno = 30; 8 BEGIN 9 OPEN c_emp; 10 LOOP 11 FETCH c_emp INTO v_emp.ename, v_emp.job, v_emp.comm, v_emp.sal; 12 EXIT WHEN c_emp%NOTFOUND; 13 v_eval_prime := v_emp.comm + (v_emp.sal/v_emp.comm); 14 dbms_output.put_line('Name = '||v_emp.ename || ' Job = ' || v_emp.job || 15 ' Re-evaluation Prime = ' ||v_eval_prime); 16 END LOOP; 17 CLOSE c_emp; 18 EXCEPTION 19 WHEN ZERO_DIVIDE THEN 20 dbms_output.put_line(SQLERRM(SQLCODE)||' SALARIE SANS PRIME !!'); 21 WHEN OTHERS THEN 22 dbms_output.put_line('Autres Erreurs'); 23 END; 24 / Name = ALLEN Job = SALESMAN Re-evaluation Prime = 305.33 Name = WARD Job = SALESMAN Re-evaluation Prime = 480.5 Name = MARTIN Job = SALESMAN Re-evaluation Prime = 1400.89 Name = BLAKE Job = MANAGER Re-evaluation Prime = ORA-01476: divisor is equal to zero SALARIE SANS PRIME !! PL/SQL procedure successfully completed.

 

Quand le programme prend en compte l’érreur dans une entrée WHEN, les instructions de cette entrée sont exécutées et le programme se termine.

Erreur ORA-12560

Oracleb ERROR: ORA-12560

1- Vérifier que vous votre Listner est dans le répertoire (C:\oracle\ora92\network).

2-demarrez le service du listner et de la base de donnée s'il sont en arrêt.

Fixez la variable d'environnement relative a votre base de donnée.

définissez une variable d'environnement de type Base de donnée oracle dans votre PATH:

pour procédez sous Windows 7

allez sur: Démarer(Start) -->Panneau de configuration(Control Pannel)

-->Mon ordinateur(My computer)--->proprieties-->Option avancées

--->variable environnement--->clique sur Ajouter

Dans la fenêtre qui apparait :

Dans la première zone de saisie du haut Tapez: Oracle_sid

Dans la deuxième zone de saisie, Tapez: Le nom de votre BDD (exemple ORCL)

Oracle PL/SQL NO_DATA_FOUND et TOO_MANY_ROWS EXCEPTIONS.

Oracle PL/SQL NO_DATA_FOUND et TOO_MANY_ROWS EXCEPTIONS.

PL/SQL a un mécanisme de gestion des erreurs qui permet de traiter ces évènements dans le BLOC EXCEPTION.
Cette requête SELECT INTO doit renvoyer impérativement 1 seul enregistrement sinon une erreur est declenchée.
Voici deux exemples (avec et sans gestion des erreurs) ou PLSQL soulève :
/-- EXCEPTION prédéfinie NO_DATA_FOUND si aucune ligne n'est retournée (empno = 889 introuvable).
? EXCEPTION prédéfinie TOO_MANY_ROWS si plusieurs lignes sont retournées (job = 'ANALYSTE' 4 enreg. retournés).
I-  EXCEPTION prédéfinie NO_DATA_FOUND.

 

    SQL> SET SERVEROUTPUT ON


    SQL> DECLARE


  2         v_ename      emp.ename%type;

  3         v_job        emp.job%type;

  4         v_sal        emp.sal%type;

  5    BEGIN

  6         SELECT    ename, job,  sal

  7             INTO   v_ename, v_job,v_sal

  8          FROM  emp

  9            WHERE empno = 889;

  10    dbms_output.put_line('Name = '||v_ename || '   Job = ' || v_job||'Salaire: '||v_sal);

  11 END;

   /

DECLARE
*

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6

Traitement de l'erreur :Introduire la clause EXCEPTION

 

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

   2    v_ename   emp.ename%type;

   3    v_job     emp.job%type;

   4	v_sal   emp.sal%type;

   4 BEGIN

   5    SELECT ename, job,sal

   6       INTO v_ename, v_job,sal

   7       FROM emp

   8      WHERE empno = 889;

   9      dbms_output.put_line('Name = '||v_ename || '   Job = ' || v_job||'Salaire: '||v_sal);

  10 EXCEPTION

  11     WHEN NO_DATA_FOUND THEN

  12        dbms_output.put_line(SQLERRM(SQLCODE)||'  SALARIE INNEXISTANT !!');

  13    WHEN OTHERS THEN

  14         dbms_output.put_line('Autres Erreurs');

  15  END;

   
   /

   

ORA-01403: no data found SALARIE INNEXISTANT !!
PL/SQL procedure successfully completed.

II-   EXCEPTION prédéfinie TOO_MANY_ROWS.

 

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

   2     v_ename emp.ename%type;

   3     v_job   emp.job%type;

   4     v_sal   emp.sal%type;

   5 BEGIN

   6     SELECT ename, job, sal

   7      INTO v_ename, v_job,v_sal

   8      FROM emp

   9     WHERE job = 'ANALYSTE';

   10      dbms_output.put_line('Nom = '||v_ename || '   Job = ' || v_job||'Salaire: '||v_sal);

 
  11  END;

   /
 

DECLARE
*

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6

Traitement de l'erreur :Introduire la clause EXCEPTION

 

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

   2     v_ename emp.ename%type;

   3     v_job   emp.job%type;

   4     v_sal  emp.job%type;

   5   BEGIN

   6     SELECT ename, job

   7       INTO v_ename, v_job

   8       FROM scott.emp

   9     WHERE job = 'ANALYSTE';

   10     dbms_output.put_line('Name = '||v_ename || '   Job = ' || v_job||'Salaire: '||v_sal);

  11 EXCEPTION

  12    WHEN TOO_MANY_ROWS  THEN

  13         dbms_output.put_line(SQLERRM(SQLCODE)||'  Ce Job n''est pas Unique !!');

  14    WHEN OTHERS THEN

  15         dbms_output.put_line('Autres Erreurs');

  16 END;

  17 /
 

>ORA-01422: exact fetch returns more than requested number of rows Ce Job n'est pas Unique !!

PL/SQL procedure successfully completed.
Lorsque le traitement est transferé dans le BLOC EXCEPTION, il n'est plus possible de revenir dans le corps du bloc BEGIN (sauf utilisation de blocs imbriquées).
Quand le programme prend en compte l’erreur dans une entrée WHEN, les instructions de cette entrée sont exécutées et le programme se termine.
pour remedier au problemme de TO_MANY_ROWS: utilsez des Curseur ou bien des TYPES Tables