devstory

Le Tutoriel de programmation Oracle PL/SQL

  1. Introduction
  2. Qu'est-ce qu'il faut pour commencer avec PL/SQL?
  3. Apercu de PL/SQL
  4. Les instructions PL/SQL de base
  5. Commencer avec PL/SQL en utilisant PL/SQL Developer
  6. Les types communs de données et les déclarations
  7. Curseur (Cursor)
  8. Procédure (Procedure)
  9. Fonction (Function)
  10. Package
  11. Qu'est-ce que Oracle Application Express ?

1. Introduction

PL/SQL (Procedural Language/Structured Query Language) est un langage de programmation procédural utilisé pour Oracle SQL. Il est une extension de Oracle SQL.
PL/SQL comprend des éléments de langage procédural tels que des conditions et des boucles. Il permet de déclarer de constantes et de variables, de procédures et de fonctions, de types et de variables de ces types et de déclencheurs. Il peut gérer des exceptions (des erreurs d'exécution). Les tableaux sont pris en charge impliquant l'utilisation de collections PL/SQL. Les implémentations à partir de la version 8 d'Oracle Database ont inclus des fonctionnalités associées à l'orientation des objets. On peut créer des unités PL/SQL telles que des procédures, des fonctions, des paquets, des types et des déclencheurs, qui sont stockés dans la base de données pour être réutilisés par des applications qui utilisent l'une des interfaces programmatiques Oracle Database.

Remarque: Dans les images de l'illustration suivant, j'utilise l'outil PL/SQL Developer de la version 8.x. Cependant, il n'y a aucune différence si vous utilisez PL/SQL Developer de la version 10.x ou d'autres versions.

2. Qu'est-ce qu'il faut pour commencer avec PL/SQL?

Pour accéder rapidement à PL/SQL vous avez besoin d'un outil de la programmation. Après mes expériences, vous pouvez utiliser PL/SQL Developer, qui est un outil virtuel travaillant avec Oracle et étant utilisé pour programmer PL/SQL.
Vous pouvez voir les instructions de l'installation et de la configuration de PL/SQL à:
In this document, I use LearningSQL database( A small database is used to instruct how to learn SQL in o7planning.org website). You can create this database as the following instruction:

3. Apercu de PL/SQL

Il y a quelques définitions que vous devriez maitriser lors de la programmation avec PL/SQL:
  • Chaque instruction SQL se termine par un point-virgule (;)
  • L'instruction "Langage de définition de données" (Data Definition Language - DDL) n'est pas utilisée dans PL/SQL
  • L'instruction SELECT.. INTO renvoie de nombreuses lignes peut provoquer exception ( > 1 ligne).
  • L'instruction SELECT .. INTO ne renvoie aucune ligne qui peut provoquer une exception
  • Les instructions "Langage de manipulation de données" (Data Manipulation Language - DML)peut avoir un effet sur plusieurs lignes.
  • Utilisez l'opérateur := pour donner une valeur à une variable.
-- assign value to variable
x  := 1;

-- Insert:
Insert into Department (Dept_Id, Dept_No, Dept_Name, Location)
 values (1, 'D1', 'HR', 'Chicago');

-- Catch exception:
Begin
  Select Dept.Dept_Id
  into v_Dept_Id
  from Department Dep;
Exception when too_many_rows then
  Dbms_Output.put_line('Error:'||Sqlerrm);
End;

......
PL/SQL est organisé par des blocs des instructions. Un bloc des instructions peut contenir le sous- bloc des instructions à l'intérieur.
Declare
-- The declaration block - (Optional)
-- Declare the variables used in the body
v_Location Varchar2(100);
Begin
-- Body of block
-- Statements ...
v_Location := 'Chicago';
-- ....
Exception
-- Catch exception block - Optional
-- Catching the various exceptions.
When No_Data_Found Then
   -- Exception when SELECT ..INTO statement return nothing
   -- (Optional)
   -- Do something here.
   -- Or null if do nothing
   Null;
When Too_Many_Rows Then
   -- Exception when the SELECT .. INTO statement returns multiple records ( > 1)
   -- (Optional)
   Null;
When Others Then
   -- Other exceptions
   Null;
End;

4. Les instructions PL/SQL de base

Ici, je présente l'aperçu des instructions de base de PL/SQL. Vous le comprendrez par des exemples dans les sections suivantes.
If-elsif-else
Syntaxe:
IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;
Exemple:
If v_Option = 1 Then
   v_Action := 'Run';
Elsif v_Option = 2 Then
   v_Action := 'Backup';
Elsif v_Option = 3 Then
   v_Action := 'Stop';
Else
   v_Action := 'Invalid';
End If;
Boucle (LOOP)
Syntaxe:
LOOP
 -- Do something here
EXIT WHEN <Condition>;
END LOOP;
Exemple:
x := 0;
Loop
 x := x + 1;
 y := y - x;
Exit When x > y;
End Loop;
Boucle (FOR LOOP)
Syntaxe:
FOR v_Index IN <Min value> .. <Max value>
LOOP
 -- Do something here
END LOOP;
Exemple:
x := 0;
For v_Idx In 1 .. 100 Loop
 x := x + 1;
End Loop;
Boucle while (WHILE)
Syntaxe:
WHILE <Condition> LOOP
 -- Do something here
END LOOP;
Exemple:
v_Text Varchar2(100);
...

While Length(v_Text) < 50 Loop
   v_Text := v_Text || '00';
End Loop

5. Commencer avec PL/SQL en utilisant PL/SQL Developer

Tout d'abord, vous devez ouvrir PL/SQL Developer,et vous connecter au utilisateur learningsql:
Dans PL/SQL Developer, créez une nouvelle fenêtre SQL:
Écrivez un code simple pour additionner deux nombres.
Declare
  v_Result Number;
  -- Declare a variable of type Number with value of 50
  v_a Number := 50;
  -- Declare a variable of type Number with value 100
  v_b Number := 100;
Begin
  -- Print out Console Screen
  Dbms_Output.Put_Line('v_a= ' || v_a);
  -- Print out Console Screen
  Dbms_Output.Put_Line('v_b= ' || v_b);
  -- Sum
  v_Result := v_a + v_b;
  -- Print out Console Screen
  Dbms_Output.Put_Line('v_Result= ' || v_Result);
End;
Cliquez sur l'icône ou pressez le bouton F8 pour exécuter le code.
Des résultats de l'exécution de l'exemple:

6. Les types communs de données et les déclarations

Type des données numériques
Les types de données numériques dans PL/SQL
Data Type
Description
PLS_INTEGER
Le nombre naturel signé compris entre -2,147,483,648 et 2,147,483,647 représenté sur 32 bits.
BINARY_INTEGER
Le nombre naturel signé compris entre -2,147,483,648 et 2,147,483,647 représenté sur 32 bits.
BINARY_FLOAT
Le nombre à virgule flottante au format IEEE 754 à une précision (Single-precision)
BINARY_DOUBLE
Le nombre à virgule flottante au format IEEE 754 double précision (Double-precision)
NUMBER(prec, scale)
Le nombre à virgule fixe (Fixed-point) ou à virgule flottante avec valeur absolue comprise entre 1E-130 et 1.0E126. Une variable NUMBER peut également représenter 0.
DEC(prec, scale)
Le type à virgule fixe spécifique ANSI avec une précision maximale de 38 chiffres décimaux.
DECIMAL(prec, scale)
Le type à virgule fixe spécifique à IBM avec une précision maximale de 38 chiffres décimaux.
NUMERIC(pre, secale)
Le type flottant (Floating type) avec une précision maximale de 38 chiffres décimaux.
DOUBLE PRECISION
Le type à virgule flottante spécifique ANSI avec une précision maximale de 126 chiffres binaires (environ 38 chiffres décimaux)
FLOAT
Le type à virgule flottante spécifique ANSI et IBM avec une précision maximale de 126 chiffres binaires (environ 38 chiffres décimaux)
INT
Le type entier entier ANSI avec une précision maximale de 38 chiffres décimaux
INTEGER
Le type entier spécifique à ANSI et IBM avec une précision maximale de 38 chiffres décimaux
SMALLINT
Le nombre entier de -32768 à 32767
REAL
Le type à virgule flottante avec une précision maximale de 63 chiffres binaires (environ 18 chiffres décimaux)
Le type le plus populaire:
Data type
Declaration
Number
v_Amount Number(15,2)
v_Salary Number;
Integer
v_Age Integer;
Float
v_Amount Float;
Double
Real
Des types de données text
Le type le plus courant:
Data type
Declaration
Varchar2
v_First_Name Varchar2(32);
Des types de données Date/time
Data type
Declaration
Date
v_Birthday Date;
Le type de données (%type)
Voici est la structure du tableau EMPLOYEE:
La colonne First_Name dans le tableau Employee a le type de données Varchar2 qui a 20 caractères de long. Pour déclarer une variable qui peut contenir la valeur de cette colonne, vous pouvez déclarer par l'utilisation de la manière suivante:
-- Declaring a varchar2 variable, length 20.
-- This variable can store value for column FIRST_NAME (of EMPLOYEE table)
v_First_Name Varchar2(20);

-- Assign values to v_First_Name, taken from the query.
-- Exception may occur when length of variable < length of string value
Select Emp.First_Name
into v_First_Name
From Employee Emp
Where Emp.Emp_Id = 1;

-- Declaring a varchar2 variable, length 30.
-- This variable can also store value for the column FIRST_NAME
v_First_Name2 Varchar2(30);

-- Safe:
v_First_Name Employee.First_Name%Type;


 
Exemple:
Declare
  v_Emp_Id     Employee.Emp_Id%Type := 1;
  v_First_Name Employee.First_Name%Type;
Begin
  Select Emp.First_Name
  Into   v_First_Name
  From   Employee Emp
  Where  Emp.Emp_Id = v_Emp_Id;
  ----
  Dbms_Output.Put_Line('v_First_Name= ' || v_First_Name);
Exception
  When No_Data_Found Then
     -- When SELECT .. INTO returns nothing.
     Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;
Type de données (%Rowtype)
Syntaxe:
-- Declare a variable.
-- Is row type

v_Variable_name Table_Name%Rowtype;

-- Example
v_Emp Employee%Rowtype;
Exemple:
Declare
  v_Emp_Id Employee.Emp_Id%Type := 1;
  -- Declare a variable
  -- Is row type of Employee table.
  v_Emp Employee%Rowtype;
Begin
  Select * Into v_Emp From Employee Emp Where Emp.Emp_Id = v_Emp_Id;
  ----
  Dbms_Output.Put_Line(' First_Name= ' || v_Emp.First_Name);
  Dbms_Output.Put_Line(' Last_Name= ' || v_Emp.Last_Name);
Exception
  When No_Data_Found Then
     -- When SELECT .. INTO statement returns nothing.
     Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;
Les résultats:
Type de données Record
Vous pouvez définir le type de données Record, ce type de données contient certaines colonnes. Syntaxe:
-- Declare your data type.

TYPE Type_Record_Name IS
    RECORD  (
            Col1  Datatype1  [NOT NULL{:=|DEFAULT} expression],
            Col2  Datatype2  [NOT NULL{:=|DEFAULT} expression],
             ...
     );


-- Variable declaration using data type:
variable_name  Type_Record_Name;
Exemple:
Declare
 v_Emp_Id Employee.Emp_Id%Type := 1;
 -- Define Record data type has 3 column.
 Type Emp_Name_Type Is Record(
     Emp_First_Name Employee.First_Name%Type
    ,Emp_Last_Name  Employee.Last_Name%Type
    ,Emp_Full_Name  Varchar2(50));
 ----
 -- Define a variable of type Emp_Name_Type
 v_Emp Emp_Name_Type;
Begin
 Select Emp.First_Name
       ,Emp.Last_Name
       ,Emp.First_Name || ' ' || Emp.Last_Name
 Into   v_Emp
 From   Employee Emp
 Where  Emp.Emp_Id = v_Emp_Id;
 ----
 Dbms_Output.Put_Line(' First_Name= ' || v_Emp.Emp_First_Name);
 Dbms_Output.Put_Line(' Last_Name= ' || v_Emp.Emp_Last_Name);
 Dbms_Output.Put_Line(' Full_Name= ' || v_Emp.Emp_Full_Name);
Exception
 When No_Data_Found Then
    -- When SELECT .. INTO statement return nothing.
    Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;
Les résultats de l'exécution de l'exemple:
Type de données Table
Vous pouvez définir un nouveau type de données qui peut stocker quelques éléments. Voici est le type TABLE.
Des caractérisyiques du type TABLE:
  • Le type de données TABLE est similaire comme un tableau, mais il a des éléments innombrables.
  • Les indices de type TABLE sont inutilement consécutifs. Par exemple, TABLE a trois éléments avec des index de 1, 3, 5.
Syntaxe:
-- Syntax:

TYPE  <Table_Name>
IS TABLE OF  <Data_Type>   [NOT NULL]
INDEX BY BINARY_INTEGER;

-- Example
-- Define TABLE data type, contains elements of type VARCHAR2 (50)
TYPE My_Tbl
IS TABLE OF Varchar2(50)
INDEX BY BINARY_INTEGER;
Exemple:
Declare
  -- Define TABLE data type:
  Type My_Tbl Is Table Of Varchar2(50) Index By Binary_Integer;
  -- Define varable of type My_Tbl.
  v_Emps My_Tbl;
Begin
  v_Emps(1) := 'One';
  v_Emps(2) := 'Two';
  v_Emps(3) := 'Three';
  ----
  Dbms_Output.Put_Line('Element Count = ' || v_Emps.Count);
  ---
  For i In v_Emps.First .. v_Emps.Last Loop
     Dbms_Output.Put_Line('Element at ' || i || ' = ' || v_Emps(i));
  End Loop;
End;
Les résultats de l'exécution de l'exemple:
Les fonctions du type de données TABLE:
Nom de fonction/Attribut
Définition
Exemple
• DELETE
Supprime des lignes dans un tableau
v_tbl.delete(3);
• EXISTS
Renvoie TRUE si l'entrée spécifiée existe dans le tableau.
v_e:= v_tbl.exists(3);
• COUNT
Renvoie le nombre de lignes dans le tableau.
v_count:=v_tbl.count;
• FIRST
Renvoie l'index de la première ligne dans le tableau.
v_first_idx:=v_tbl.first;
• LAST
Renvoie l'index de la dernière ligne dans le tableau.
v_last_idx:=v_tbl.last;
• NEXT
Renvoie l'index de la ligne suivante dans le tableau après la ligne spécifiée.
v_idx:= v_tbl.next(2);
• PRIOR
Renvoie l'index de la ligne précédente dans le tableau avant.
v_idx:=v_tbl.prior(2);
Exemple:
Declare
  -- Define TABLE data type.
  Type t_City_Type Is Table Of Varchar2(30) Index By Binary_Integer;
  -- Declare a variable of type T_City_Type.
  t_City       t_City_Type;
  v_City_Index Binary_Integer;
  v_e          Boolean;
Begin
  t_City(100) := 'Chicago';
  t_City(101) := 'Chicago';
  t_City(200) := 'Hanoi';
  t_City(301) := 'Tokyo';
  Begin
     -- Check exists element at index 500.
     v_e := t_City.Exists(500);
     If v_e Then
        Dbms_Output.Put_Line('Exists element At 500');
     Else
        Dbms_Output.Put_Line('Not Exists element At 500');
     End If;
     --
     -- Delete element at index 101
     t_City.Delete(101);
     --
     -- First index
     v_City_Index := t_City.First;
     Dbms_Output.Put_Line('First= ' || v_City_Index);
     --
     Loop
        Dbms_Output.Put_Line('Element at ' || v_City_Index || ' = ' ||
                             t_City(v_City_Index));
        Exit When v_City_Index = t_City.Last;
        -- Returns the index of the next row in the table after the specified row.
        v_City_Index := t_City.Next(v_City_Index);
     End Loop;
     -- Raise No_Data_Found exception
     Raise No_Data_Found;
  Exception
     When No_Data_Found Then
        Dbms_Output.Put_Line('the Last City Has Been Reached.');
  End;
End;
Les résultats de l'exécution de l'exemple:
Type de données de tableau (Array)
Syntaxe:
-- Define Array data type
TYPE <varray_type_name>
IS VARRAY(n)
OF <element_type>;

-- Example, declare an array, declare an array of 5 elements, and elements of type VARCHAR2(10);
TYPE cityarray
IS VARRAY(5)
OF Varchar2(10);
 
Un tableau a N éléments. Ses éléments sont numérés consécutivement de 1 à N.
Exemple:
Declare
 -- Define Array data type.
 -- containing data type of VARCHAR2 (50)
 Type Emp_Array Is Varray(5) Of Varchar2(50);
 -- Define Array data type, containing data type of Integer
 Type Salary_Array Is Varray(5) Of Integer;
 ---
 v_Names    Emp_Array;
 v_Salaries Salary_Array;
 v_Count    Integer;
Begin
 -- Initialize the value of array elements.
 v_Names := Emp_Array('KING'
                     ,'JONES'
                     ,'FORD'
                     ,'SMITH'
                     ,'BLAKE');
 -- Initialize the value of array elements.
 v_Salaries := Salary_Array(5000
                           ,2975
                           ,3000
                           ,800
                           ,2850);
 -- Element count.                            
 v_Count := v_Names.Count;
 Dbms_Output.Put_Line('Count = ' || v_Count);
 ---
 For i In 1 .. v_Count Loop
    Dbms_Output.Put_Line('Employee = ' || v_Names(i) || ', Salary = ' ||
                         v_Salaries(i));
 End Loop;
End;
Les résultats de l'exécution de l'exemple:

7. Curseur (Cursor)

Qu'est-ce le Curseur?
Cursor est un type de variable structurée qui nous permet de traiter des données avec différentes lignes. Le nombre de lignes dépend de l'instruction d'interrogation de données après la ligne. Pendant le traitement, nous manipulons Cursor dans chaque ligne de données. Cette ligne de données est spécifiée par un curseur. En déplaçant le curseur, nous pouvons extraire toute la donnée d'une ligne actuelle.
La syntaxe de la déclaration du curseur:
-- Cursor declaration has no parameters:
CURSOR <Cursor_Name>
IS
<Select_Statement>


-- Cursor declaration has parameters:
CURSOR <Cursor_Name>(<Parameter_List>)
IS
<Select_Statement>


 
Exemple:
-- Cursor declaration has no parameters:
Cursor Emp_Cur Is
 Select Emp.Emp_Id
       ,Emp.First_Name
       ,Emp.Last_Name
 From   Employee Emp;

-- Cursor declaration has parameters:
Cursor Emp_Cur(p_Dept_Id   Number
        ,p_Branch_Id Number)
Is
Select Emp.Emp_Id
   ,Emp.First_Name
   ,Emp.Last_Name
   ,Emp.Assigned_Branch_Id
   ,Emp.Dept_Id
From   Employee Emp
Where  (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
And    (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
Il y a deux types de curseur (Cursor):
  1. Curseur explicit
  2. Curseur implicit.
Le concept d'explicit signifie lors de l'utilisation vous devez écrire distinctement l'instruction du curseur d'ouverture et de fermeture. Pour le curseur implicit, vous n'avez pas besoin d'écrire des déclarations d'ouverture et de fermeture.

Les propriétés du Curseur:
Attribut
Signification
%isopen
renvoie la valeur True si cursor s'ouvre
%notfound
renvoie la valeur true s'il n'y a plus la prochaine ligne
%found
renvoie la valeur true s'il existe une ligne suivante.
%rowcount
renvoie le nombre row a été récupéré.
Curseur explicit (Explicit Cursor)
Les étapes de la déclaration et de l'utilisation du curseur explicit:
Exemple:
Declare
    -- Declaring a Cursor with 2 parameters.
    Cursor Emp_Cur
    (
        p_Dept_Id   Number
      ,p_Branch_Id Number
    ) Is
        Select Emp.Emp_Id
                ,Emp.First_Name
                ,Emp.Last_Name
                ,Emp.Assigned_Branch_Id
                ,Emp.Dept_Id
        From   Employee Emp
        Where  (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
        And    (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
    ---
    -- Declare a variable type of ROWTYPE, based on the newly created Cursor.
    v_Emp Emp_Cur%Rowtype;
    ---
    v_Dept_Id   Number := 1;
    v_Branch_Id Number;
    ---
    v_Row  Integer := 0;
    v_Open Boolean;
Begin
    -- Check cursor is openning?
    If Emp_Cur%Isopen Then
        Dbms_Output.Put_Line('Cursor opened');
    Else
        Dbms_Output.Put_Line('Cursor not open');
    End If;
    --
    Dbms_Output.Put_Line('Opening cursor...');
    -- Open Cursor (Pass input parameters).
    Open Emp_Cur(v_Dept_Id, v_Branch_Id);
    -- Using loop
    Loop
        -- Get row of data from Cursor
        -- Each time Fetch cursor moves one line
        -- (Downward).
        Fetch Emp_Cur
            Into v_Emp;
        -- Conditions to exit the loop
        Exit When Emp_Cur%Notfound;
        -- Process the data
        v_Row := v_Row + 1;
        Dbms_Output.Put_Line(v_Row || ' - First_Name: ' || v_Emp.First_Name || ' - Last_Name: ' || v_Emp.Last_Name);
    End Loop;
    --
    Dbms_Output.Put_Line('Closing cursor...');
    -- Close Cursor.
    Close Emp_Cur;
End;
Les résultats de l'exécution de l'exemple:
Curseur implicit (Implicit Cursor)
Pour le curseur implicit, vous n'avez pas besoin d'écrire clairement l'instruction d'ouverture / de fermeture.
Utilisez l'instruction For pour récupérer le curseur, avec la syntaxe:
-- Use the 'for loop' to fetch the Implicit cursor:

FOR <v_Record> in <cursor_name>(<Parameter_values>) LOOP

   -- Statements..

END LOOP;
Exemple:
Declare
  -- Declaring a Cursor with 2 parameters.
  Cursor Emp_Cur(p_Dept_Id   Number
                ,p_Branch_Id Number) Is
     Select Emp.Emp_Id
           ,Emp.First_Name
           ,Emp.Last_Name
           ,Emp.Assigned_Branch_Id
           ,Emp.Dept_Id
     From   Employee Emp
     Where  (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
     And    (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
  ---
  ---
  v_Dept_Id   Number := 1;
  v_Branch_Id Number;
  ---
  v_Row Integer := 0;
Begin
  -- Check cursor is open
  If Emp_Cur%Isopen Then
     Dbms_Output.Put_Line('Cursor opened');
  Else
     Dbms_Output.Put_Line('Cursor not open');
  End If;
  --
  -- Using loops to fetch cursor
  -- No need: open/close/fetch.
  --
  For v_Emp In Emp_Cur(v_Dept_Id
                      ,v_Branch_Id) Loop
     --
     v_Row := v_Row + 1;
     Dbms_Output.Put_Line(v_Row || ' - First_Name: ' || v_Emp.First_Name ||
                          ' - Last_Name: ' || v_Emp.Last_Name);
  End Loop;
End;

8. Procédure (Procedure)

Un groupe de déclarations effectuant une fonction peut être rassemblé dans une procédure (procedure) visant à augmenter la capacité de manipulation, de réutilisation, de sécurité, de sécurité des données et d'utilité dans le développement.
La procédure peut être sauvegardée sur la base de données comme objet de base de données et prête à être réutilisée. La procédure est ensuite appelée Stored procedure. Pour la procédure, après la procédure est sauvegardée (save), elle est traduite en p-code pour que sa capacité d'exécution soit augmentée.
La procédure ne renvoie pas directement la valeur comme la fonction.
La syntaxe pour créer une procédure:
-- procedure_name:  Name of procedure
-- argument:  
-- mode:  IN or OUT or IN OUT, default is IN
-- datatype:  Data type

CREATE [OR REPLACE] PROCEDURE <procedure_name>
          [
           (argument1  [mode1]  datatype1,
            argument2  [mode2]  datatype2,
           ...)
          ]
     IS | AS
BEGIN
   -- PL/SQL Block;
END;
Exemple:
-- For example a procedure with no parameters.
CREATE OR REPLACE Procedure Do_Something AS
   -- Declare variables here
Begin
  -- Do something here.
End;

-- For example a procedure with parameters
-- input parameters, and output parameters.
CREATE OR REPLACE Procedure Do_Something(p_Param1 Varchar2,
                                                                 v_Param Out Varchar2)
AS
   -- Declare variables here
Begin
  -- Do something here.
End;
Procédure de renoncement (drop procedure):
-- Drop procedure

DROP PROCEDURE <Procedure_Name>
Les étapes pour une procédure:
L'exemple de création d'un exemple:
Voici est un exemple de la création la première procédure sur PL/SQL Developer. Cet exemple sera un modèle qui vous aide à la fois de la programmation et de la vérification d'erreur s'il y a des problèmes.
  • Créer une procédure (Procedure)
  • Compiler cette procédure
  • Exécuter la procédure
  • Debug cette procédure en utilisant PL/SQL Developer pour vérifier comment le programme exécute.
Créez une procédure (Procedure):
Saisissez le nom de procédure et des paramètres seront écrits plus tard:
  • Get_Employee_Infos
Une procédure est créée par PL/SQL Developer. Cependant, vous devez correcter la liste des paramètres et écrire le code pour cette procédure.
Modify your procedure:
-- Input parameter: p_Emp_Id
-- Output parameters: v_First_Name, v_Last_Name, v_Dept_Id.
Create Or Replace Procedure Get_Employee_Infos(p_Emp_Id     Number
                                             ,v_First_Name Out Varchar2
                                             ,v_Last_Name  Out Varchar2
                                             ,v_Dept_Id    Out Number) Is
Begin
  -- Print out console.
  -- Log - use for programmers
  Dbms_Output.Put_Line('Parameter p_Emp_Id = ' || p_Emp_Id);
  --
  -- If SELECT .. INTO statement return nothing
  -- it will throw an Exception - NO_DATA_FOUND:
  --
  --
  Select Emp.First_Name
        ,Emp.Last_Name
        ,Emp.Dept_Id
  Into   v_First_Name
        ,v_Last_Name
        ,v_Dept_Id
  From   Employee Emp
  Where  Emp.Emp_Id = p_Emp_Id;
  --
  -- Print out Console.
  --
  Dbms_Output.Put_Line('Found Record!');
  Dbms_Output.Put_Line(' v_First_Name= ' || v_First_Name);
  Dbms_Output.Put_Line(' v_Last_Name= ' || v_Last_Name);
  Dbms_Output.Put_Line(' v_Dept_Id= ' || v_Dept_Id);
Exception
  When No_Data_Found Then
     -- Print out Console.
     Dbms_Output.Put_Line('No Record found with p_Emp_Id = ' || p_Emp_Id);
End Get_Employee_Infos;
Cliquez sur l'icône Execute ou F8 pour compiler la procédure. Dans le cas où vous écrivez un code erroné, PL/SQL Developer vous informera.
Test procedures in PL/SQL Developer
Cliquez sur le bouton droit sur la procédure Get_Employee_Infos et sélectionnez Test:
Saisissez des paramètres d'entrée, par exemple:
  • p_Emp_Id = 1
Les résultats de l'exécution de la procédure:
View on Screen Console:
Essayez de différent cas avec:
  • p_Emp_Id = 9999
Voyez sur le Console:
Debug procedures in PL/SQL Developer
Deboguer sur PL/SQL Developer vous permet de voir une procédure, comment la fonction exécute, par chaque instruction. Cela permet de trouver facilement les positions des erreurs qui se produisent. Vous pouvez voir les instructions à:
  • TODO

9. Fonction (Function)

Comme la procédure, la fonction (function) est également un groupe des instructions PL/SQL qui réalise thực hiện chức năng nào đó. qui jouent un rôle. Contrairement à la procédure, une fonction renverra une valeur directement sur son appel.
Les fonctions sont également enregistrées sur la base de données sous forme de Stored procedure.
La syntaxe crée la fonction.
-- function_name:  Name of function
-- argument:  
-- mode: IN or OUT or IN OUT, default is IN
-- datatype:  Data type

CREATE [OR REPLACE] FUNCTION <function_name>
                [
                 (argument1  [mode1]  datatype1,
                  argument2  [mode2]  datatype2,
                  ...)
               ]
              RETURN  datatype
     IS | AS
BEGIN
   -- PL/SQL Block;
END;
Exemple:
-- Function has 1 parameter:
CREATE OR REPLACE FUNCTION Sum(a Integer, b Integer)
RETURN Integer
AS
Begin
  return a + b;
End;

-- A function with no parameters:
CREATE OR REPLACE FUNCTION Get_Current_Datetime
RETURN Date
AS
Begin
  return sysdate;
End;
Renoncer à la Fonction (Drop function):
-- Drop Function

DROP FUNCTION <function_name>;
Appeler la fonction.
-- When calling the function, must declare a variable

-- Declare variable c.
c Integer;
....

-- Call function
c := Sum(10, 100);
Exemple de la fonction.
-- Function with input p_Emp_ID
-- And return First_Name of Employee.

Create Or Replace Function Get_Emp_First_Name(p_Emp_Id Number)
  Return Varchar2 As
  -- Declare variable v_Emp_First_Name
  v_Emp_First_Name Employee.First_Name%Type;
Begin
  Begin
     Select Emp.First_Name
     Into   v_Emp_First_Name
     From   Employee Emp
     Where  Emp.Emp_Id = p_Emp_Id;
  Exception
     When No_Data_Found Then
        -- Assigning null in case not found Employee
        v_Emp_First_Name := Null;
  End;
  --
  Return v_Emp_First_Name;
End;
La fonction sans paramètres OUT, peut participer au instruction SQL, par exemple:
Select Emp.Emp_Id
     ,Get_Emp_First_Name(Emp.Emp_Id) Emp_First_Name
From   Employee Emp;
Les résultats de l'exécution de cette instruction SQL ci-dessus:

10. Package

Package est une collection de type de données, des variables stockées des valeurs et des procédures, des fonctions ayant la même relation entre eux, qui sont regroupés. Les traits les plus marquants du package est lorsqu'un élément dans le package est appelé, tout le contenu du package sera déposé dans le système. Par conséquent, l'appel autres éléments dans le package ne prend plus de temps de déposition au système. À part de ca, la vitesse d'effectuer sera améliorée lorsqu'il exécute les fonctions, les procédures existantes dans le package.
La structure de Package:
Un package est structuré de 2 parties. La partie spécification (specification) définit les interactions potentielles entre le package avec les extérieurs. La partie Corps (body) a les codes pour diverses méthodes déclarées dans la spécification du package et d'autres déclarations privées, qui sont cachées du code en dehors du package.
La structure du package comprend 5 composants:
  • Public variable (Variable publique): est une variable à laquelle les applications externes peuvent faire référence.
  • Public procedure (procédure publique): inclut des fonctions et des procédures du package qui peuvent être appelées par des applications extérieures.
  • Private procedure (Procédure privée): sont des fonctions et des procédures dans le package et elles ne sont qu'appelées par les autres fonctions ou les autres procédures dans ledit package.
  • Global variable (Variable globale): est une variable déclarée et utilisée dans l'ensemble du package, des applications extérieures peuvent faire référence à cette variable.
  • Private variable (Variable privée): est une variable déclarée dans une fonction ou une procédure du package. Seules ces fonctions ou procédures peuvent y faire référence.
Déclaration Package:
-- Spec Package declaration:

CREATE [OR REPLACE] PACKAGE <package_name>
IS| AS
       -- public type and item declarations subprogram specifications
END <package_name>;

-- Body Package declaration:
CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS | AS


      -- private type and item declarations 
      -- subprogram bodies

END <package_name>;
Tạo package trên PL/SQL Developer
PL/SQL Developer vous aide à créer package spec & package body rapidement.
Package a été créé par PL/SQL Developer et des conseils d'écriture package sont générés automatiquement. Vous pouvez tous les supprimer.
Vous pouvez supprimer tous les codes qui sont générés automatiquement par PL/SQL Developer lui-même afin d'avoir un package vide:
  • PKG_EMP (Package Spec)
--
-- This is Package Spec of Package PKG_EMP
-- It declared two functions (While Package Body has 2 functions and 1 procedure).
-- The function or procedure is not declared on the Spec Package, meant only for use within the package.
--
Create Or Replace Package Pkg_Emp Is

 -- Function returns First_Name
 Function Get_First_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Employee.First_Name%Type;

 -- Function returns department name of employee
 Function Get_Dept_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Department.Name%Type;

End Pkg_Emp;
  • PKG_EMP (Package Body)
--
-- This is Package Body of Package PKG_EMP
--
Create Or Replace Package Body Pkg_Emp Is

 -- =====================================================
 -- The procedure returns the employee information
 -- Includes 2 output parameters v_First_Name, v_Last_Name
 -- =====================================================
 Procedure Get_Emp_Infos(p_Emp_Id     Employee.Emp_Id%Type
                        ,v_First_Name Out Employee.Emp_Id%Type
                        ,v_Last_Name  Out Employee.Last_Name%Type) As
 Begin
    Begin
       Select Emp.First_Name
             ,Emp.Last_Name
       Into   v_First_Name
             ,v_Last_Name
       From   Employee Emp
       Where  Emp.Emp_Id = p_Emp_Id;
    Exception
       -- Not found employee with p_Emp_Id
       When No_Data_Found Then
          v_First_Name := Null;
          v_Last_Name  := Null;
    End;
 End;

 -- =====================================================
 -- Function returns First_Name for Emp_ID
 -- =====================================================
 Function Get_First_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Employee.First_Name%Type As
    -- Declare a variable.
    v_First_Name Employee.First_Name%Type;
    v_Last_Name  Employee.Last_Name%Type;
 Begin
    -- Call procedure Get_Emp_Infos
    Get_Emp_Infos(p_Emp_Id
                 ,v_First_Name -- Out
                 ,v_Last_Name -- Out
                  );
    --
    Return v_First_Name;
 End;

 -- =====================================================
 -- Function returns Dept_Name for Emp_ID.
 -- (Department name of Employee)
 -- =====================================================
 Function Get_Dept_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Department.Name%Type As
    -- Declare a variable.
    v_Dept_Name Department.Name%Type;
 Begin
    Begin
       Select Dept.Name
       Into   v_Dept_Name
       From   Employee   Emp
             ,Department Dept
       Where  Emp.Dept_Id = Dept.Dept_Id
       And    Emp.Emp_Id = p_Emp_Id;
    Exception
       When No_Data_Found Then
          v_Dept_Name := Null;
    End;
    --
    Return v_Dept_Name;
 End;

End Pkg_Emp;
Test Package
Comme la procédure et la fonction, vous pouvez tester la procédure / la fonction du package. Cela vous aide à détecter les erreurs pendant le processus de programmation.
Test results:

11. Qu'est-ce que Oracle Application Express ?

Oracle Application Express (Oracle APEX), anciennement appelé HTML DB, est un outil de développement d'applications web rapide pour la base de données Oracle. En utilisant uniquement un navigateur Web et une expérience de programmation limitée, vous pouvez développer et déployer des applications professionnelles à la fois rapides et sécurisées. Oracle Application Express combine les qualités d'une base de données personnelle, la productivité, la facilité d'utilisation et la flexibilité avec les qualités d'une base de données d'entreprise, la sécurité, l'intégrité, l'évolutivité, la disponibilité et construit pour le web. Application Express est un outil permettant de créer des applications web et l'environnement de développement d'applications est également pratique sur le web.
Oracle APEX vous demande seulement des compétences de Oracle PL/SQL. Vous pouvez voir ce qu'est Oracle Application Express à: