Руководство Oracle PL/SQL

View more categories:

1- Введение

PL/SQL (Procedural Language/Structured Query Language) это процедурно-ориентированный язык программирования использующийся для Oracle SQL. Является расширением  Oracle SQL.
PL/SQL включает компоненты процедурно-ориентированного языка включая условие и цикл. Он позволяет объявлять константы и переменные, процедуры и функции, виды данных и переменные видов данных, и trigger. Он может обрабатывать исключения (ошибки времени запуска) Массив так же поддерживается для использования коллекций в PL/SQL. От версии 8 и далее он включает объектно-ориентированные функции. Может создать такие единицы  PL/SQL как процедуры, функции, пакеты, виды данных, triggers, которые хранятся в базе данных для переиспользования приложением, чтобы взаимодействовать с приложениями Oracle.
Примечание: В следующих изображениях я использую инструмент  PL/SQL Developer версии  8.x, но нет отличия если вы используете  PL/SQL Developer версии  10.x или другую версию.

2- Что нужно чтобы начать с PL/SQL?

Чтобы иметь быстрый доступ к  PL/SQL вам нужен инструмент программирования. По моему опыту работы, вы можете использовать  PL/SQL Developer, это визуальный инструмент для работы с  Oracle и программирования  PL/SQL.
Вы можете посмотреть инструкцию установки и конфигурации PL/SQL по ссылке:
В данной статье я использую  LearningSQL (Маленькая база данных для инструкции изучения  SQL в вебсайте  o7planning.org). Вы можете создать эту базу данных следуя следующей инструкции:

3- Обзор PL/SQL

Есть некоторые определения, которые вы должные четко знать при программировании с PL/SQL:
  1. Каждая команда SQL заканчивается точкой с запятой (;)
  2. Команды "языка определения данных" (Data Definition Language - DDL) не используются в PL/SQL
  3. Команда SELECT.. INTO возврщает много строк создающих exception ( > 1 строки).
  4. Команда SELECT .. INTO не возвращает строки создающие exception
  5. Команды "языка манипулирования данными" (Data Manipulation Language - DML) может подействовать на многие строки данных.
  6. Использовать оператор := чтобы дать значение переменной.
-- 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 организован по блокам команд. Один блок команды может содержать подблок команд внутри.
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- Базовые команды PL/SQL

Здесь я представляю обзор команд PL/SQL. Вы поймете больше через примеры в следующих частях.

4.1- Команда If-elsif-else

Синтаксис:
IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;
Пример:
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;

4.2- Не предопределенный цикл (LOOP)

Синтаксис:
LOOP
 -- Do something here
EXIT WHEN <Condition>;
END LOOP;
Пример:
x := 0;
Loop
 x := x + 1;
 y := y - x;
Exit When x > y;
End Loop;

4.3- Предопределенный цикл (FOR LOOP)

Синтаксис:
FOR v_Index IN <Min value> .. <Max value>
LOOP
 -- Do something here
END LOOP;
Пример:
x := 0;
For v_Idx In 1 .. 100 Loop
 x := x + 1;
End Loop;

4.4- Цикл while (WHILE)

Синтаксис:
WHILE <Condition> LOOP
 -- Do something here
END LOOP;
Пример:
v_Text Varchar2(100);
...

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

5- Начать с PL/SQL используя PL/SQL Developer

Для начала вам нужно открыть PL/SQL Developer, и войти как пользователь  learningsql:
В  PL/SQL Developer создать новое окно  SQL:
Написать простой код для суммы 2 чисел.
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;
Кликните на иконку    или нажмите  F8 для выполнения кода.
Результаты запуска примера:

6- Общие виды данных и объявления

6.1- Вид числовых данных

Виды числовых данных в  PL/SQL
Вид данных Описание
PLS_INTEGER Натуральное число со знаком 32 bit в диапазоне примерно -2,147,483,648 до 2,147,483,647.
BINARY_INTEGER Натуральное число со знаком 32 bit в диапазоне примерно  -2,147,483,648 до 2,147,483,647.
BINARY_FLOAT Действительное число с плавающей точкой одинарной точности (Single-precision)
BINARY_DOUBLE Действительное число с плавающей точкой двойной точности (Double-precision)
NUMBER(prec, scale) Фиксированная точка (Fixed-point) с абсолютным значением от 1E-130 до(не включая) 1.0E126. Переменная NUMBER так же может описывать 0.
DEC(prec, scale) Фиксированная точка, стандарта ANSI с точностью максимум 38 десятичных цифр.
DECIMAL(prec, scale) Фиксированная точка, стандарта IBM с точностью максимум 38 десятичных цифр.
NUMERIC(pre, secale) Вид вещественных чисел (Floating type) с точностью максимум 38 десятичных цифр.
DOUBLE PRECISION Числа с плавающей точкой, действительные числа, стандарта ANSI с точностью 126 двоичных чисел (примерно 38  десятичных цифр).
FLOAT Числа с плавающей точкой, действительные числа, стандарта ANSI и IBM с точностью 126 двоичных чисел (примерно 38  десятичных цифр).
INT Целое число, стандарта ANSI с точностью максимум 38 десятичных цифр.
INTEGER Целое число, стандарта ANSI и IBM с точностью максимум 38 десятичных цифр.
SMALLINT Целое число -32768  --> 32767
REAL Числа с плавающей точкой, действительные числа, с точностью максимом 63 двоичных чисел (Примерно 18 десятичных цифр).
Самые распространенные виды:
Data type Declaration
Number v_Amount Number(15,2)
v_Salary Number;
Integer v_Age Integer;
Float v_Amount Float;
Double  
Real  

6.2- Вид текста

Самый распространенный вид:
Data type Declaration
Varchar2 v_First_Name Varchar2(32);
   

6.3- Вид Date/time

Data type Declaration
Date v_Birthday Date;
   

6.4- Вид данных столбца (%type)

Это структура таблицы  EMPLOYEE:
Столбец  First_Name в таблице  Employee с видом данных  Varchar2 с длиной 20 символов. Чтобы объявить переменную, которая может содержать значение данного столбца, вы можете объявить используя следующий способ:
-- 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;


 
Пример:
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;

6.5- Тип данных строки (%Rowtype)

Синтаксис:
-- Declare a variable.
-- Is row type

v_Variable_name Table_Name%Rowtype;

-- Example
v_Emp Employee%Rowtype;
Пример:
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;
Результаты запуска примера:

6.6- Вид данных Record

Вы можете дать определение виду данных Record, этот вид данных содержит несколько столбцов. Синтаксис:
-- 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;
Пример:
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;
Результаты запуска примера:

6.7- Вид данных Table

Мы можете дать определение новому виду данных, который может хранить много элементов, это вид  TABLE.
Характеристика вида  TABLE:
  • Вид данных TABLE похож на массив, но имеет бесконечное число элементов.
  • Индекс вида TABLE не обязательно должен быть последовательным. Например TABLE есть 3 элемента с индексами 1, 3, 5.
Синтаксис:
-- 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;
Пример:
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;
Результаты запуска примера:
Функции вида  TABLE:
Функция/Атрибут Значение Пример использования
• DELETE Удалить строки в таблице v_tbl.delete(3);
• EXISTS Возвращает TRUE если определенный элемент существует в Table. v_e:= v_tbl.exists(3);
• COUNT Возвращает число элементов в table. v_count:=v_tbl.count;
• FIRST Возвращает индекс первого элемента в table. v_first_idx:=v_tbl.first;
• LAST Возвращает индекс последнего элемента в table. v_last_idx:=v_tbl.last;
• NEXT Возвращает индекс следующего элемента в таблице, идущий после определенного элемента. v_idx:= v_tbl.next(2);
• PRIOR Возвращает индекс предыдущего элемента, идущий перед определенным элементом. v_idx:=v_tbl.prior(2);
Пример:
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;
Результаты запуска примера:

6.8- Вид данных массива (Array)

Синтаксис:
-- 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);
 
Массов имеет  N элементов. Элементы массива индексированы последовательно от 1 до  N.
Пример:
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;
Результаты запуска примера:

7- Курсор (Cursor)

7.1- Что такое курсор?

Cursor это структурная переменная, позволяющая вам обрабатывать данные с несколькими строками. Количество строк зависит от команды запроса данных после нее. В процессе обработки, вы можете манипулировать с  Cursor через каждую строку данных. Эта строка данных определена курсором. Перемещая курсор, вы можете получить все данные текущей строки.
Синтаксис объявления курсора:
-- Cursor declaration has no parameters:
CURSOR <Cursor_Name>
IS
<Select_Statement>


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


 
Пример:
-- 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);
Есть 2 вида курсора (Cursor):
  1. Явный курсор
  2. Неявный курсор.
Понятие явный здесь означает, при использовании вам нужно написать команду открыть курсор, и написать команду закрыть курсор после использования. В случае неявного курсора, вам не нужно писатьkhi sử dụng nó bạn cần phải viết lệnh mở con trỏ, và viết lệnh đóng con trỏ sau khi đã sử dụng xong một cách rõ ràng. Với trường hợp con trỏ không tường minh bạn có thể không cần viết lệnh đóng mở.
Атрибуты курсора:
Атрибут Значение
%isopen  возвращает значение True если cursor открыт
%notfound  возвращает значение true если отстутствует следующая строка
%found  возвращает значение true если присутствует следующая строка.
%rowcount возвращает число просмотренных row.

7.2- Explicit Cursor

Шаги объявления и использования явного курсора:
Например:
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;
Результаты запуска примера:

7.3- Неявный курсор (Implicit Cursor)

С неявным курсором вы можете не писать ясную окрытую/закрытую команду. 
Использовать команду  For для получения курсора с синтаксисом:
-- Use the 'for loop' to fetch the Implicit cursor:

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

   -- Statements..

END LOOP;
Пример:
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- Процедура (Procedure)

Группа команд выполняющих определенную функцию могут быть собраны в одну процедуру (procedure) чтобы увеличить способность обработки, спосообность общего пользования, повысить конфиденциальность и безопасность данных, и утилитарность в развитии.
Процедура может быть сохранена в базе данных как объект базы данных, готовый для повтоного использования. Данная процедура называется  Stored procedure. Для процедур, сразу после сохранения (save), они были компилированы в p-code поэтому может улучшить способность выполнения.
Процедура не возвращает значение напрямую как функция.
Синтаксис создания процедуры:
-- 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;
Пример:
-- 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;
Отменить процедуру (drop procedure):
-- Drop procedure

DROP PROCEDURE <Procedure_Name>
Шаги выполнения процедуры:

Пример создания процедуры:

Это пример создания первой процедуры на PL/SQL Developer. Данный пример будет моделью, помогающей вам одновременно программировать и проверять ошибки, если возникает проблема.
  1. Создать процедуру (Procedure)
  2. Компилировать данную процедуру
  3. Запустить процедуру
  4. Debug процедуру используя PL/SQL Developer чтобы проверить как работает программа.
Создать новую процедуру (Procedure):
Ввести название процедуры, и параметры будут написаны позже:
  • Get_Employee_Infos
Процедура создана с помощью  PL/SQL Developer. Но вам нужно поправить список параметров и написать код для данной процедуры.
Изменить вашу процедуру:
-- 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;
Нажать на иконку  Execute или  F8 чтобы компилировать процедуру. В случае ошибки кода,  PL/SQL Developer оповестит вас.

8.1- Test procedures in PL/SQL Developer

Нажать на правую кнопку мыши на процедуру  Get_Employee_Infos выбрать  Test:
Ввести параметры, например:
  • p_Emp_Id = 1
Результаты выполнения процедуры:
Смотреть на экране Console:
Тестировать случаи отличающиеся от значений:
  • p_Emp_Id = 9999
Смотреть на  Console:

8.2- Debug procedures in PL/SQL Developer

Debug на  PL/SQL Developer позволяет вам просматривать процедуру, как работает функция, по каждой команде. Помогает вам легко определить место происхождения ошибки. Вы можете посмотреть инструкцию по ссылке:
  • TODO

9- Функция (Function)

Как и процедура, функция (function) так же является группой команд  PL/SQL выполняющих определенную роль. В отличии от процедуры, функции возвращают значение сразу при вызове.
Функция тоже хранится в базе данных в виде Stored procedure.
Синтаксис создания функции.
-- 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;
Пример:
-- 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;
Отмена функции (Drop function):
-- Drop Function

DROP FUNCTION <function_name>;
Вызов функции.
-- When calling the function, must declare a variable

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

-- Call function
c := Sum(10, 100);

Пример создания функции.

-- 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;
Функции без параметра  OUT, могут участвовать в команде  SQL, например:
Select Emp.Emp_Id
     ,Get_Emp_First_Name(Emp.Emp_Id) Emp_First_Name
From   Employee Emp;
Результаты запуска команды  SQL выше:

10- Package

Package это набор связанных видов данных, переменных хранящих значения и процедуры, функции, собранные в одно. Главная особенность  package это когда вызывается элемент в package то все содержание  package будет загружено в систему. Поэтому, вызов других элементов в package в будущем, не потребует много времени для загрузки в систему. Следует повышение скорости выполнения команды всей функции, процедуры имеющейся в  package.
Структура Package:
Один  package структурирован с двумя частями. Часть описания (specification) определяет потенциальные интеракции package и снаружи. Часть корпуса (body) это установки  для коммуникации в частях описанных выше.
В структуре  package имеется 5 компонентов:
  1. Public variable (публичная переменнеая): это переменная к которой могут ссылаться (использовать) внешние приложения.
  2. Public procedure (публичная процедура): включает функции и процедуры package которые могут быть вызваны внешними приложениями.
  3. Private procedure (частная процедура): это функции, процедуры имеющиеся в package и могут быть вызваны только другими функциями и процедурами в том package.
  4. Global variable (глобальные переменные): это перменные объявленные и использованные в целом package, внешние приложения могут ссылаться на эту переменную.
  5. Private variable (частные переменные): это переменные объявленные в функции, процедуре в package. Которые могут использоваться только внутри той функции или процедуры.
Объявление 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>;

10.1- Создать package на PL/SQL Developer

PL/SQL Developer помогает вам быстро создать  package spec & package body.
Package создан с помощью  PL/SQL Developer, с подсказками чтобы написать автоматически генерированные  package. Вы можете удалить их все.
Вы можете удалить все автоматически генерированные коды  PL/SQL Developer чтобы получить пустой  package:
  • 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;

10.2- Test Package

Так же как процедура и функция, вы можете протестировать процедуру/функцию в Package, это помогает вам обнаруживать ошибки в процессе программирования.
Test results:

11- Что такое Oracle Application Express?

Oracle Application Express (Oracle APEX), раньше назывался  HTML DB, это быстрый инструмент разработки веб приложений для баз данных Oracle. Используя только веб браузер и не нужно много мастерства в программировании, вы можете разработать и развернуть профессиональные приложения быстро и безопасно. Oracle Application Express комбинирует характеристики персональной базы данных, продуктивность, простота использования, и гибкость с качествами корпоративной базы данных, безопастность, целостность, масштабируемость, готовые свойства и построение для web. Application Express это инструмент для разработки приложений основанные на веб и среда разработки приложений так же удобна для веб. 
Oracle APEX только требует от вас навыки в   Oracle PL/SQL. Вы можете посмотреть что такое  Oracle Application Express по ссылке:

View more categories: