1. Giới thiệu

PL/SQL (Ngôn ngữ thủ tục/Ngôn ngữ truy vấn có cấu trúc) là ngôn ngữ lập trình thủ tục được sử dụng cho Oracle SQL. Nó là một phần mở rộng của Oracle SQL.
     
PL/SQL bao gồm các yếu tố ngôn ngữ thủ tục như điều kiện và vòng lặp. Nó cho phép khai báo các hằng và biến, thủ tục và hàm, kiểu và biến của các kiểu đó và kích hoạt. Nó có thể xử lý các trường hợp ngoại lệ (lỗi thời gian chạy). Mảng được hỗ trợ liên quan đến việc sử dụng các bộ sưu tập PL/SQL. Các triển khai từ phiên bản 8 của Cơ sở dữ liệu Oracle trở đi đã bao gồm các tính năng liên quan đến hướng đối tượng. Người ta có thể tạo các đơn vị PL/SQL như thủ tục, hàm, gói, loại và kích hoạt, được lưu trữ trong cơ sở dữ liệu để sử dụng lại bởi các ứng dụng sử dụng bất kỳ giao diện lập trình cơ sở dữ liệu Oracle nào.

Trong các hình ảnh minh họa sau đây, chúng ta sẽ sử dụng phiên bản 8.x của PL/SQL Developer. Tuy nhiên, không có sự khác biệt nếu bạn sử dụng phiên bản 10.x của PL/SQL Developer hoặc các phiên bản khác.

2. Điều gì là cần thiết để bắt đầu làm quen với PL/SQL?.

Để nhanh chóng truy cập vào PL/SQL, bạn cần một công cụ lập trình. Theo kinh nghiệm của tôi, bạn có thể sử dụng PL/SQL Developer, một công cụ trực quan để làm việc với Oracle và được sử dụng để lập trình PL/SQL.

Trong tài liệu này, tôi sử dụng cơ sở dữ liệu LearningQuery. Một cơ sở dữ liệu nhỏ được sử dụng để hướng dẫn cách học SQL trên trang web To view link, please Register or Login.

3. Tổng quan về PL/SQL.

Có một số định nghĩa cơ bản, các bạn cần nắm vững khi lập trình với PL/SQL:

   
• Mỗi câu lệnh SQL kết thúc bằng dấu chấm phẩy (;)
• Câu lệnh DDL (Ngôn ngữ định nghĩa dữ liệu) không được sử dụng trong PL/SQL.
• Câu lệnh SELECT..INTO đó trả về nhiều dòng có thể gây ra ngoại lệ (> 1 dòng).
• Câu lệnh SELECT..INTO không trả về bất kỳ dòng nào có thể gây ra ngoại lệ.
• Câu lệnh DML (Ngôn ngữ thao tác dữ liệu) có thể có hiệu lực trên nhiều dòng.
• Sử dụng toán tử: = để cấp giá trị cho một biến.

-- 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 được tổ chức bởi các khối câu lệnh. Một khối các câu lệnh có thể chứa khối con của các câu lệnh trong đó.

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. Các câu lệnh PL/SQL cơ bản.

Ở đây mình sẽ giới thiệu tổng quan về các câu lệnh cơ bản của PL/SQL. Bạn sẽ dễ dàng hiểu nó thông qua các ví dụ trong các phần tiếp theo.

4.1. If-elsif-else.

Cú pháp:

IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;

Ví dụ:

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.
   
Cú pháp:

LOOP
-- Do something here
EXIT WHEN <Condition>;
END LOOP;

Ví dụ:

x := 0;
Loop
x := x + 1;
y := y - x;
Exit When x > y;
End Loop;

4.3. FOR LOOP.

Cú pháp:

FOR v_Index IN <Min value> .. <Max value>
LOOP
-- Do something here
END LOOP;

Ví dụ:

x := 0;
For v_Idx In 1 .. 100 Loop
x := x + 1;
End Loop;

4.4. WHILE LOOP.
   
Cú pháp:

WHILE <Condition> LOOP
-- Do something here
END LOOP;

Ví dụ:

v_Text Varchar2(100);
...

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

5. Bắt đầu với PL/SQL bằng PL/SQL Developer.

   
• Trước tiên, bạn cần mở PL/SQL Developer và đăng nhập với tài khoản người dùng learning:

   
• Trong PL/SQL Developer, mở cửa sổ SQL mới.

   
• Viết một mã đơn giản để tổng hai số.

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;

   
• Nhấp vào biểu tượng bánh răng hoặc F8 để thực thi mã.

   
• Kết quả chạy trả về ví dụ.

6. Các kiểu dữ liệu và khai báo chung.

6.1. Kiểu dữ liệu số.

   
• Các kiểu dữ liệu số trong PL/SQL.

   
• Loại phổ biến nhất.

6.2. Kiểu dữ liệu văn bản.

   
• Loại phổ biến nhất.

6.3. Kiểu dữ liệu ngày/giờ.6.4. Kiểu dữ liệu %.

   
• Cột First_Name trong bảng Nhân viên chứa loại dữ liệu Varchar2 có độ dài 20 ký tự. Để khai báo một biến có thể chứa giá trị của cột này, bạn có thể khai báo bằng cách sử dụng cách sau:

-- 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;

Ví dụ:

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. Kiểu dữ liệu Rowtype.

-- Declare a variable.
-- Is row type

v_Variable_name Table_Name%Rowtype;

-- Example
v_Emp Employee%Rowtype;

Ví dụ:

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;

Kết quả sẽ như sau.
6.6. Kiểu dữ liệu 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;

Ví dụ:

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;

Các kết quả chạy ví dụ:6.7. Kiểu dữ liệu Bảng.

Bạn có thể xác định một loại dữ liệu mới có thể lưu trữ nhiều yếu tố. Đây là loại Bảng.
Các tính năng của loại Bảng:

   
• Kiểu dữ liệu của Bảng giống như một mảng, nhưng nó có vô số phần tử.
• Các chỉ mục của loại Bảng liên tiếp không cần thiết. Ví dụ, Bảng có ba phần tử với các chỉ mục là 1, 3, 5.
• Bạn có thể xác định loại dữ liệu Ghi có chứa một số cột.
• Đây là cấu trúc của bảng NHÂN VIÊN:

Cú pháp:

-- 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;

Ví dụ:

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;

Các kết quả chạy ví dụ:Các chức năng của kiểu dữ liệu Bảng:Ví dụ:

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;

Các kết quả chạy ví dụ:6.8. Kiểu dữ liệu mảng.

Cú pháp:
-- 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);

Một mảng có n phần tử. Các phần tử của nó được đánh số liên tiếp từ 1 đến n.Ví dụ:

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;

Các kết quả chạy ví dụ:7. Con trỏ.

7.1. Con trỏ là gì?.

Con trỏ là một loại biến có cấu trúc cho phép chúng ta xử lý dữ liệu với nhiều dòng khác nhau. Số lượng dòng phụ thuộc vào câu lệnh truy vấn dữ liệu sau dòng. Trong quá trình xử lý, chúng tôi thao tác con trỏ qua từng dòng dữ liệu. Dòng dữ liệu này được chỉ định bởi một con trỏ. Bằng cách di chuyển con trỏ, chúng ta có thể trích xuất toàn bộ dữ liệu của một dòng hiện tại.
   
Cú pháp khai báo con trỏ:

-- Cursor declaration has no parameters:
CURSOR <Cursor_Name>
IS
<Select_Statement>

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

Ví dụ:

-- 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);

Có hai loại con trỏ:

   
• Con trỏ rõ ràng
• Con trỏ ngầm định.

Khái niệm về phương tiện rõ ràng khi sử dụng nó bạn cần viết câu lệnh mở và đóng con trỏ một cách rõ ràng. Để ẩn con trỏ, bạn không cần phải viết câu mở và đóng.
   
Các thuộc tính của Con trỏ:7.2. Con trỏ rõ ràng.

Các bước khai báo và sử dụng con trỏ:Ví dụ:

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;

Các kết quả chạy ví dụ:7.3. Con trỏ tiềm ẩn.

   
• Để ẩn con trỏ, bạn không cần phải viết câu lệnh đóng/mở rõ ràng.
• Sử dụng 'for loop' để tìm nạp con trỏ, với cú pháp:

-- Use the 'for loop' to fetch the Implicit cursor:

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

   -- Statements..

END LOOP;

ví dụ:

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. Thủ tục.

Một nhóm các câu lệnh thực hiện một số chức năng có thể được tập hợp trong một thủ tục nhằm mục đích tăng khả năng xử lý, tái sử dụng, bảo mật, an toàn dữ liệu và tiện ích trong phát triển.

Thủ tục có thể được lưu trên cơ sở dữ liệu như một đối tượng của cơ sở dữ liệu và sẵn sàng để sử dụng lại. Thủ tục sau đó được gọi là thủ tục lưu trữ. Đối với thủ tục, sau khi thủ tục được lưu, nó được dịch thành mã p để khả năng thực hiện của nó được nâng lên.   

   
• Thủ tục không trực tiếp trả về giá trị như hàm.
• Cú pháp tạo thủ tục:

-- 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;

ví dụ:

-- 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 Thủ tục.

-- Drop procedure

DROP PROCEDURE <Procedure_Name>

Các bước cho một thủ tục:Ví dụ về thủ tục:

Đây là một ví dụ về việc tạo thủ tục đầu tiên trên PL / SQL Developer. Ví dụ này sẽ là mô hình giúp bạn lập trình và kiểm tra lỗi nếu có bất kỳ vấn đề nào.

   
• Tạo một thủ tục

   
• Nhập tên của thủ tục và các tham số sẽ được viết sau:

Get_Employee_Infos

   
• Một thủ tục được tạo bởi PL/SQL Developer. Tuy nhiên, bạn cần sửa danh sách các tham số và viết mã cho quy trình này.

   
• Sửa đổi thủ tục của bạn:

-- 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;

Nhấp vào bánh răng để Thực thi hoặc F8 để biên dịch thủ tục. Trong trường hợp bạn viết mã sai, PL / SQL Developer sẽ thông báo cho bạn.8.1. Quy trình thử nghiệm trong PL/SQL Developer.

Bấm chuột phải vào thủ tục Get_Employee_Infos và chọn Test:

   
• Nhập tham số đầu vào, ví dụ: p_Emp_Id = 1.

   
• Kết quả thực hiện thủ tục:

   
• Xem trên Bảng điều khiển màn hình.

Kiểm tra trường hợp khác nhau với: p_Emp_Id = 9999.

   
• Xem trên bảng điều khiển.

8.2. Quy trình gỡ lỗi trong PL/SQL Developer.

Gỡ lỗi trên PL/SQL Developer cho phép bạn xem một quy trình, cách chức năng chạy, theo từng câu lệnh. Điều này làm cho nó dễ dàng tìm ra vị trí của các lỗi phát sinh.

9. Các hàn Function.

   
• Giống như thủ tục, hàm cũng là một nhóm các câu lệnh PL / SQL đóng vai trò nào đó. Không giống như thủ tục, một hàm sẽ trả về một giá trị ngay trong lệnh gọi của nó.
• Các chức năng cũng được lưu trên cơ sở dữ liệu dưới dạng thủ tục Lưu trữ.
• Cú pháp tạo hàm.

-- 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;

ví dụ:

-- 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;

Xóa Function:

-- Drop Function

DROP FUNCTION <function_name>;

Gọi function:

-- When calling the function, must declare a variable

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

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

Ví dụ về hàm:

-- 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;

-- 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;[/quote]

Hàm không có tham số OUT, có thể tham gia vào câu lệnh SQL, ví dụ:

[quote]Select Emp.Emp_Id
     ,Get_Emp_First_Name(Emp.Emp_Id) Emp_First_Name
From   Employee Emp;

Những kết quả: 10. Gói.

Các gói PL/SQL là các đối tượng lược đồ nhóm các loại, biến và chương trình con PL/SQL có liên quan về mặt logic.
Cấu trúc gói:

   
• Một gói được cấu trúc với hai phần. Phần đặc tả xác định các tương tác tiềm năng giữa các gói với bên ngoài. Phần Body có mã cho các phương thức khác nhau được khai báo trong đặc tả gói và các khai báo riêng khác, được ẩn khỏi mã bên ngoài gói.

Cấu trúc của gói bao gồm 5 thành phần:

   
• Biến công khai: là một biến mà các ứng dụng bên ngoài có thể tham chiếu đến.
• Thủ tục công khai: bao gồm các chức năng và quy trình của gói có thể được gọi bởi các ứng dụng bên ngoài.
• Thủ tục riêng: là các chức năng và thủ tục có sẵn trong gói và chúng chỉ có thể được gọi bởi các chức năng hoặc thủ tục khác trong gói.
• Biến toàn cục: là biến được khai báo và sử dụng trong toàn bộ gói; các ứng dụng bên ngoài có thể làm tham chiếu đến biến này.
• Biến riêng: là biến được khai báo trong hàm hoặc thủ tục của gói. Chỉ những chức năng hoặc thủ tục này có thể làm cho tham chiếu đến nó.

Gói khai báo:

-- 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. Tạo một gói với PL/SQL Developer.

PL/SQL Developer giúp bạn nhanh chóng tạo gói.

   
• Gói đã được PL/SQL Developer tạo và các gợi ý về gói viết được tạo tự động. Bạn có thể xóa tất cả chúng.

   
• Bạn có thể xóa tất cả các mã được tạo bởi chính PL/SQL Developer để có gói trống.

PKG_EMP (Thông số gói)

--
-- 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;[/quote]

[b]PKG_EMP (Package Body)[/b]

[quote]--
-- 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. Kiểm tra gói.

Giống như thủ tục và hàm, bạn có thể kiểm tra thủ tục/hàm của gói. Điều này giúp bạn phát hiện lỗi trong quá trình lập trình.Kết quả kiểm tra:11. Ứng dụng Oracle Express là gì?.Oracle Application Express (Oracle APEX), trước đây gọi là HTML DB, là một công cụ phát triển ứng dụng web nhanh chóng cho cơ sở dữ liệu Oracle. Chỉ sử dụng trình duyệt web và trải nghiệm lập trình hạn chế, bạn có thể phát triển và triển khai các ứng dụng chuyên nghiệp vừa nhanh và an toàn. Ứng dụng Oracle express kết hợp các phẩm chất của cơ sở dữ liệu cá nhân, năng suất, dễ sử dụng và tính linh hoạt với các phẩm chất của cơ sở dữ liệu doanh nghiệp, bảo mật, tính toàn vẹn, khả năng mở rộng, tính sẵn sàng và được xây dựng cho web. Application Express là một công cụ để xây dựng các ứng dụng dựa trên web và môi trường phát triển ứng dụng cũng dựa trên web một cách thuận tiệnOracle APEX chỉ yêu cầu các kỹ năng về Oracle PL/SQL. Hãy đón xem tiếp phần sau Ứng dụng Oracle Express là gì?.