oracle - pl / sql (procedural language / structured query language)

Download Oracle -  PL / SQL (Procedural Language / Structured Query Language)

Post on 10-Jan-2016

30 views

Category:

Documents

0 download

Embed Size (px)

DESCRIPTION

Oracle - PL / SQL (Procedural Language / Structured Query Language). Problématique posée par SQL. SQL est un langage non procédural, répondant à la question QUOI ? Mais pas au COMMENT ?. Les développeurs ont besoin d’assembler des données et de les traiter ligne par ligne. - PowerPoint PPT Presentation

TRANSCRIPT

  • Oracle - PL / SQL

    (Procedural Language / Structured Query Language)

  • Problmatique pose par SQLMise en place dun langage de traitement procdural qui facilite la tche des dveloppeurs et qui permet de pallier aux carences du langage SQL : PL/SQL.Les dveloppeurs ont besoin dassembler des donnes et de les traiter ligne par ligne.Solution apporte par Oracle partir de la version 6SQL est un langage non procdural, rpondant la question QUOI ? Mais pas au COMMENT ?

  • Prsentation du langage PL/SQLPL/SQL

  • Prsentation du langage PL/SQLPL/SQLSQL STATEMENT EXECUTORIntgr au noyau RDBMS.PROCEDURAL STATEMENT EXECUTORIntgr au noyau et aux outils (Forms, Rports, OAS, ) Dclarations possibles des variables.

    Intgration de la majorit des ordres SQL..

    Traitements squentiels.

    Excution conditionnelle de blocs dinstructions.

    Traitements rptitifs (boucles).

    Gestion des exceptions.REGLES

  • SQL & PL/SQLLIDTRANSACTIONLMDFONCTIONSSELECTINSERTUPDATEDELETELOCK TABLECOMMITROLLBACKSAVEPOINTSET TRANSACTIONSUMDECODEMODUPPERSYSDATETO_CHAR IntgrsNon intgrsEXPLAIN PLANCREATEALTERDRPOGRANTREVOKE

    ALTER SESSIONSET ROLEALTER SYSTEM

  • STRUCRURE DUN BLOC PL/SQLBLOC PL/SQLPartie dclarative (optionnelle)Partie exception (optionnelle)Partie excutable (obligatoire)

    Chaque instruction se termine par un point virgule.

    Les commentaires sont possibles /* */.

    Possibilit dimbrication des blocs.

    DECLAREEXCEPTIONBEGIN END

  • PARTIE DECLARATIVE BLOC PL/SQL

    LES VARIABLES & LES TYPES

    Les variables locales : Elles ne sont pas visibles dans tous les blocs. Elles peuvent tre simples ou structures.

    Les variables globales : elles sont dclares dans la partie commune tous les blocs. Elles peuvent tre simples ou structures.

    Les variables externes (bind variables) : elles sont externes tous les blocs. (variables sql+, variables pro*, champs dans les forms).

    RemarqueLes variables externes sont prcdes de :.

  • PARTIE DECLARATIVE BLOC PL/SQLTypes classiques

  • PARTIE DECLARATIVE BLOC PL/SQLTypes propres PL/SQL RemarquesLes types BINARY_INTEGER et PLS_INTEGER requirent moins despaces que les types NUMBER.

  • PARTIE DECLARATIVE BLOC PL/SQLConversion de types Conversion implicite : effectue par PL/SQL Conversion explicites par les fonctions.Conversion explicites : TO_CHAR, TO_NUMBER, TO_DATE, TO_LOB, CHARTOROWID TO_SINGLE_BYTE,

  • PARTIE DECLARATIVE BLOC PL/SQLDclaration de variablesInitialisationDans la partie DECLARESyntaxe1 : ;Avec oprateur daffectation :=Par le mot cl DEFAULT ( la place de :=)Par la clause INTO de lordre SELECT Par le biais dun curseurSyntaxe2 : %TYPE ;Syntaxe2 : %TYPE ;

  • PARTIE DECLARATIVE BLOC PL/SQLExemples de dclarations-- Exemple 1DECLARE num_emp NUMBER(10);date_jour DATE :=SYSDATE; -- Initialise la date du jourlogiqueBOOLEAN;trouve logique%TYPE DEFAULT FALSE;nNUMBER :=-5;carreNUMBER :=n**2;

    -- Exemple 2DECLAREnom_empVARCHAR2(35) NOT NULL :="BENOIT";chaine VARCHAR2(25) DEFAULT 6; -- Conversion implicitenombreNUMBER(3) :=5; -- Conversion implicitepiCONSTANT NUMBER :=3.14; -- Dclaration d'une constante

    -- Exemple 3 DECLARE sal employe.salaire%TYPE :=0;-- Mme type que la colonne salaire de la table employe.

    Remarque La contrainte NOT NULL dune colonne ne sapplique sur la variable.

  • PARTIE DECLARATIVE BLOC PL/SQLLes variables rfrences une table de la base

    Elles sont lies des tables au niveau de la base. On les dclare par lattribut : %ROWTYPE

    Exemples DECLARE agent employe%ROWTYPE-- employe est la table employe de la base.

    Au niveau traitement, on pourra crire :

    BEGINSELECT *-- Slection de tous les champsINTO agentFROM employeWHERE nom=DUMAS;END;Ou BEGINSELECT nom,dt_entree -- Slection de certains champsINTO agent.nom, agent.dt_entreeFROM employeWHERE nom=DUMAS';END;

  • PARTIE DECLARATIVE BLOC PL/SQLLES TYPES STRUCTURES - RECORDSyntaxe de dclaration du typeTYPE IS RECORD ( , , );Utilisation. := ;

    Dclaration des variables de type record

    , ;

    ExempleDECLARETYPE emp_type_rec IS RECORD (num employe.empno%TYPE,service e_service.nom%TYPE,salaire NUMBER(11,2),date_jour DATE :=SYSDATE);

    var_emp1 emp_type_rec;var_emp2 emp_type_rec;

    v_emp1.num:=2550;v_emp1.nom:='LAROUSSI'v_emp2:=v_emp1;

    1234

  • PARTIE DECLARATIVE BLOC PL/SQLLES TYPES STRUCTURES - TABLEAUXSyntaxe de dclaration du type

    TYPE IS TABLE OF | | INDEX BY BINARY_INTEGER ;

    Accs aux donnes

    () := ;

    Dclaration des variables de type tableau

    ;

    Remarques

    La valeur de l'indice varie de : -2.147.483.647 + 2.147.483.647.

    Les lignes intermdiaires existent potentiellement mais n'occupent aucun espace.

    123

  • PARTIE DECLARATIVE BLOC PL/SQLLES TYPES STRUCTURES - TABLEAUXExemple

    DECLARE TYPE t_tab_salaire TABLE OF NUMBER(11,3);tab1 t_tab_salaire;

    BEGINtab1(-100) := 2500;tab1(-50) := 1500;tab1(15) :=2000;END;

  • PARTIE DECLARATIVE BLOC PL/SQLTABLEAUX Primitives de manipulation des indices -

  • PARTIE EXECUTABLEOprateursRemarques

    Un systme de priorit est respect pour l'valuation des expressions ( ) , ** , * / , + - .

  • PARTIE EXECUTABLESTRUCTURES DE CONTROLESTRUCTURES CONDITIONNELLESSTRUCTURES REPETITIVESIF THEN END IFIF THEN ELSE END IFIF THEN ELSIF ELSE END IFLOOP END LOOPWHILE LOOP END LOOP FOR LOOP END LOOP

  • PARTIE EXECUTABLE

    IF THEN-- Squence1 instructions;

    ELSIF THEN-- Squence2 Instructions;

    ELSIF THEN-- Squence3 Instructions;ELSIF THEN-- SquenceN Instructions;

    ELSE-- Squence_else Instructions;

    END IF;

    IF THEN -- Squence1 instructions;ELSE -- Squence2 instructions; END IF;

    IF THEN -- Squence1 instructions;END IF;

    STRUCTURES CONDITIONNELLES

  • PARTIE EXECUTABLE

    FOR IN [REVERSE] b_inf..b_supLOOP ;END LOOP;

    Compteur est dclare implicitement. Interdiction de modification de compteur dans la boucle. La notion de pas n'existe pas (pas=1 ou -1).

    WHILE LOOP ;END LOOP ;

    [

  • PARTIE EXECUTABLELES CURSEURS Une zone mmoire de taille fixe contenant le rsultat d'une requte.

    Utilise pour interprter et analyser les ordres SQL.

    Le nombre de curseurs ouverts simultanment est dfini par le paramtre OPEN_CURSORS . dans le PFILE de la base.

    CURSEURSIMPLICITES

    gnrs et grs par le noyau Oracle pour chaque ordre SQLEXPLICITES

    Dclar par l'user dans la section DECLARE d'un bloc PLS

  • LES CURSEURS EXPLICITEStapes de cration d'un CURSEUR expliciteDECLARATION DU CURSEUR

    CURSOR ISOUVERTURE DU CURSEUR

    OPENACCES AU CURSEUR

    FETCH INTOFERMETURE DU CURSEUR

    CLOSE

  • DECLARATION DU CURSEUR

    CURSOR ISLES CURSEURS EXPLICITESSyntaxe

    DECLARE

    CURSOR ISSELECT col1, col2, col3, FROM tab1, tab2, tab3, WHERE ; ;

    Exemple

    DECLARE

    CURSOR c_employe ISSELECT empno, ename, salaireFROM employeWHERE salaire BETWEEN 1000 AND 2500 ;

  • OUVERTURE D'UN CURSEUR

    OPEN LES CURSEURS EXPLICITESSyntaxe

    BEGIN ;OPEN ; ;END;ExempleDECLARE

    CURSOR c_employe ISSELECT empno, ename, salaireFROM employeWHERE salaire BETWEEN 1000 AND 2500 ;

    BEGINOPEN c_employe; ;END;

  • TRAITEMENT DES LIGNES DU CURSEUR

    FETCH INTOLES CURSEURS EXPLICITESSyntaxe

    BEGIN ;FETCH INTO { |}; ;END;ExempleDECLARE no employe.empno%TYPE;-- Variables programmes name employe.ename%TYPE;CURSOR c_employe IS-- CurseurSELECT empno, enameFROM employeWHERE salaire BETWEEN 1000 AND 2500 ; BEGINOPEN c_employe;FETCH c_employe INTO no,ename;-- Accs aux lignes du cruseurDBMS_OUTPUT.PUT_LINE(no,ename) ;END;

  • FERMETURE D'UN CURSEUR

    CLOSE LES CURSEURS EXPLICITESSyntaxe

    BEGIN ;CLOSE ; ;END;ExempleDECLARE

    CURSOR c_employe ISSELECT empno, ename, salaireFROM employeWHERE salaire BETWEEN 1000 AND 2500 ;

    BEGINOPEN c_employe; ;CLOSE c_employe;END;

  • ATTRIBUTS DES CURSEURSLES CURSEURS EXPLICITES

  • LES CURSEURS EXPLICITESParcours des lignes d'un curseurEn passant par la boucle

    LOOP END LOOP

    BEGINLOOPFETCH ;INTO ;IF %FOUND THENELSEEXIT ;END IFEND LOOP;

    En utilisant la boucle

    FOR enreg IN LOOP

    END LOOP;Exemple

    FOR enreg IN c_employeLOOP

    END LOOP;

  • LES CURSEURS PARAMETRESIl est possible de paramtrer un curseur pour une utilisation commune plusieurs traitements.

    Syntaxe dclaration

    DECLARECURSOR (p1 type_p1 [,p2 type_p2 [, ]) ISSELECT FROM WHERE

    ;

    Utilisation du curseur paramtrerBEGINOPEN (val1,[,val2 [,]);FETCH INTO ;CLOSE ;END;