LES CURSEURS PARAMETRESIl est possible de paramtrer un curseur pour une utilisation commune plusieurs traitements.Syntaxe dclarationDECLARECURSOR (p1 type_p1 [,p2 type_p2 [, ]) ISSELECT FROM WHERE ;Utilisation du curseur paramtrBEGINOPEN (val1,[,val2 [,]);FETCH INTO ;CLOSE ;END;

LES CURSEURS PARAMETRESParcours d'un curseur paramtrFOR compteur IN (val1 [,val2, ])LOOP END LOOPExempleDECLARECURSOR c_emp(p_titre varchar2) ISSELECT nom , salaire , titreFROM e_empWHERE titre=p_titre;BEGINFOR enr IN c_emp('&v_titre')LOOPDBMS_OUTPUT.PUT_LINE('Employ : '||enr.nom||' Salaire : '||enr.salaire);END LOOPEND;

LES CURSEURS EXPLICITES - OPTIONSClause CURRENT OFRfrencer une ligne d'un curseur pour UPDATE ou DELETERserver une ligne lors de la dclaration du curseur par le positionnement d'un verrou.Le verrou concerne toutes les colonnes de la ligneLes verrous utiliss sont FOR UPDATE [ OF col1,col2, ]FOR DELETE

LES CURSEURS EXPLICITES - OPTIONSClause CURRENT OF - ExempleDECLARE CURSOR c_emp ISSELECT * FROM emp WHERE salaire < 2000FOR UPDATE OF salaire;enr c_emp%ROWTYPE;BEGINOPEN c_emp;LOOPFETCH c_emp INTO enr ;EXIT WHEN c_emp%NOTFOUND;UPDATE e_empSET salaire=salaire*1.1WHERE CURRENT OF c_emp;END LOOP;END;

LES VARIABLES DE TYPE CURSEURVariable curseurDisponible depuis la version 7.3 d'OracleElle est dynamique, car elle n'est lie aucune requteSyntaxe1. TYPE IS REF CURSOR [RETURN type_retour];2. ;

une ligne d'une tabletype_retour un type recordExempleDECLARETYPE emptype IS REF CURSOR;emp_vc emptype;

LES VARIABLES DE TYPE CURSEUROPEN FOR ;

COMMANDES UTILISEESPOUR VARIABLES CURSEURS

FETCH INTO ;CLOSE

LES VARIABLES DE TYPE CURSEURExempleDECLARE TYPE r_type IS RECORD (num client.cnum%TYPE,nom client.cnom%TYPE);enr r_type;TYPE varcur IS REF CURSOR RETURN enr%TYPE ;choix number(1) :=&choix ;BEGINIF choix=1 THENOPEN varcur FOR SELECT no,nom FROM client ;ELSIF choix=2 THEN OPEN varcur FOR SELECT no,nom FROM service ; ELSIF choix=3 THEN OPEN varcur FOR SELECT no,nom FROM produit ;

END;

GESTION DES TRANSACTIONSTRANSACTIONEnsemble de mises jour effectues sur une base locale ou distante.GESTIONCOMMITValider une transac.ROLLBACKAnnuler une transac.SAVEPOINTDbuter une sous transac.ROLLBACK TOAnnnuler une sous transac.LOCK TABLEVerrouiller une table

GESTION DES TRANSACTIONSCONNECT username/passwordUPDATE table ; -- dbut transaction 1INSERT INTO table ;COMMIT ; -- fin de la transaction 1 avec validation

INSERT INTO table ; -- dbut transaction 2UPDATE table ;ROLLBACK ;-- fin transaction 2 avec annulation

INSERT INTO table ; -- dbut transaction 3UPDATE table ;SAVEPOINT sp1 ; -- dbut sous transaction 3.1DELETE FROM table ;ROLLBACK TO sp1 ; -- annulation de la sous transaction 3.1UPDATE table ;COMMIT ; -- fin transaction 3 avec validation

GESTION DES TRANSACTIONSDans un bloc PL/SQL, la gestion des transactions se fait entre BEGIN et END.

DECLARE

BEGINUPDATE e_service -- Pose d'un verrou sur les donnesSET nom='Marketing'WHERE no=32;COMMIT;-- Validation et suppression du verrouEND;/

LES TRAITEMENTS STOCKESUne entit stocke dans la basePhysiquement, le stockage se fait dans les tables du dictionnaireOBJET STOCKEPROCEDURE

Rle est d'effectuer un traitementFONCTION

Rle est d'effectuer un traitement pour renvoyer une valeurPACKAGE

Regroupement d'un ensemble de procdures et /ou fonctionsTRIGGER

Traitement dclench suite une mise jour

Intrts desobjets stocksAnalyse des requtes des BL PLS dj effectueLES TRAITEMENTS STOCKESDiminution du trafic Client/Serveur sur le rseauGestion centralise de la cohrence des donnes de la basePossibilits de surcharge des procdures et des fonctions.

APPEL FONCTION/PROCEDUREMode InteractifTriggerD'autres procdures et fonctionsApplication FORMS/REPORTSProgramme htePRO*LES TRAITEMENTS STOCKES

LES PROCEDURESSyntaxe

CREATE [ OR REPLACE ] PROCEDURE [].(arg1 IN type1 [DEFAULT val_initiale [, arg2 OUT type2 [, arg3 IN OUT type3, ])AS

[ Dclarations des variables locales ]

BEGIN

Contenu du bloc PLS

END [];

LES PROCEDURES

LES PROCEDURES - ExempleExemple Compter le nombre d'employs pour un dpartement donn.

CREATE OR REPLACE PROCEDURE proc_dept (p_no IN dept.deptno%TYPE) ASv_no NUMBER;BEGINSELECT COUNT(no)INTO v_noFROM empWHERE deptno=no;DBMS_OUTPUT.PUT_LINE('Nombre d'employs : '||' '||v_no);END;/

LES PROCEDURES - Oprations de baseExcution Mode SQLPLUS EXECUTE [owner.] (liste arguments rels);

Appel dans un bloc PLS [owner.] (liste arguments rels;

SuppressionMode SQLPLUS

DROP PROCEDURE [owner.] ;

LES FONCTIONSSyntaxe

CREATE [ OR REPLACE ] FUNCTION [].(arg1 IN type1 [DEFAULT val_initiale [, arg2 IN type2, ]) RETURN type_retour AS

[ Dclarations des variables locales ]

BEGINContenu du bloc PLSRETURN (var_retour );

END [];/

LES FONCTIONS - ExempleExemple Compter le nombre d'employs pour un dpartement donn.

CREATE OR REPLACE FUNCTION proc_dept (p_no IN dept.deptno%TYPE) RETURN NUMBER ASv_no NUMBER;BEGINSELECT COUNT(no)INTO v_noFROM empWHERE deptno=no;RETRUN (v_no);END;/

LES FONCTIONS - Oprations de baseExcution EXECUTE :var_externe := [owner.] (liste arguments rels); SELECT [owner.] (liste arguments rels FROM DUAL;SuppressionMode SQLPLUS

DROP FUNCTION [owner.] ;

BEGIN: var_PLS := [owner.] (liste arguments rels);END;

LES PACKAGESProcdureFonctionExceptionVariables, constantes, curseursTypes de variables. Package = Encapsulation SPECIFICATIONS

Partie publique d'une package Dclarations des objets Accs avec le droit EXECUTE BODY

Partie prive d'un package Dfinition des objets Peut contenir une partie initialisation des objets.

LES PACKAGESProcdure P1

Procdure P1Traitement P1 : Appel F1 Variable P1_V2Procdure P2Traitement P2 : Appel F1 Appel P3 Curseur P1_C1Variable P1_V1Procdure P1

Curseur P1_C1Variable P1_V1Fonction F1Traitement F1 : RETURN (F1_VAL) Spcifications"Public"Body"Private"

LES PACKAGES - Cration / ModificationSyntaxe - Spcifications

CREATE [ OR REPLACE ] PACKAGE []. ASnom_excep EXCEPTION ;TYPE type_rec IS RECORD () ;TYPE type_tab IS TABLE OF ;nom_var type_var ;CURSOR nom_cur1 IS SELECT ;CURSOR nom_cur2(p_no type, ) RETURN [type_retour] ;TYPE type_cur IS REF CURSOR RETURN [type_retour] ;FUNCTION (arg1 IN type1 [DEFAULT val_initiale ]) RETURN type_retour;PROCEDURE (arg1 IN type1 [DEFAULT val_initiale [, arg2 OUT type2 [, arg3 IN OUT type3, ]) ;END [];/

LES PACKAGES - Cration / ModificationSyntaxe - Body

CREATE [ OR REPLACE ] PACKAGE BODY []. ASCURSOR nom_cur2(p_no type, ) RETURN [type_retour] IS SELECT ;

FUNCTION (arg1 IN type1 [DEFAULT val_initiale ]) RETURN type_retourAS -- variables localesBEGINEND;PROCEDURE (arg1 IN type1 [DEFAULT val_initiale [, arg2 OUT type2 [, arg3 IN OUT type3, ]) ;AS -- variables localesBEGINEND;END [];

LES PACKAGES - OprationsRecherche d'un package dans la base

SELECT object_name FROM user_objetsWHERE object_type='PACKAGE'; (resp 'PACKAGE BODY');

Recherche du code source d'un package dans la base

SELECT textFROM user_sourceWHERE name = 'PACKAGE'-- Spcifications du package = 'PACKAGE BODY'-- Corps du package

LES PACKAGES - OprationsExcution FonctionsEXECUTE :var_externe :=. (liste arguments rels);

SELECT . (liste arguments rels) FROM DUAL;

ProcduresEXECUTE . (liste arguments rels);

Variables

SELECT . := ;

Bloc PLS: := .(liste arguments);