Hướng dẫn lập trình Oracle PL/SQL

-- Lệnh gán giá trị cho biến
x  := 1;

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

-- Bắt ngoại lệ:
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;

......

Declare
   -- Phần khai báo - Không bắt buộc-
   -- Khai báo các biến sử dụng trong phần thân
   v_Location Varchar2(100);
Begin
   -- Phần thân của khối lệnh   
   -- Đoạn lệnh thực hiện
   v_Location := 'Chicago';
   -- ....
Exception
   -- Phần xử lý lỗi - Không bắt buộc
   -- Bắt để sử lý các ngoại lệ khác nhau.
   When No_Data_Found Then
      -- Ngoại lệ khi câu lệnh SELECT .. INTO không trả về dòng nào
      -- (Không bắt buộc phải bắt)
      -- Sử lý gì đó tại đây.
      -- Hoặc để lệnh null nếu không cần sử lý.
      Null;
   When Too_Many_Rows Then
      -- Ngoại lệ khi câu lệnh SELECT .. INTO trả về nhiều dòng
      -- (Không bắt buộc phải bắt)
      Null;
   When Others Then
      -- Các ngoại lệ khác
      Null;
End;

IF <điều kiện 1> THEN
    Công việc 1;
[ELSIF <điều kiện 2> THEN
     Công việc 2;
]
[ELSE
     Công việc 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;

LOOP
 -- Làm gì đó tại đây.
EXIT WHEN <Điều kiện thoát khỏi vòng lặp>;
END LOOP;

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

FOR v_Index IN <Cận dưới> .. <Cận trên>
LOOP
 -- Làm gì đó tại đây.
END LOOP;

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

WHILE <Điều kiện> LOOP
 -- Làm gì đó tại đây
END LOOP;

v_Text Varchar2(100);
...

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

Declare
  v_Result Number;
  -- Khai báo một biến có giá trị 50
  v_a Number := 50;
  -- Khai báo một biến có giá trị 100
  v_b Number := 100;
Begin
  -- In ra màn hình Console
  Dbms_Output.Put_Line('v_a= ' || v_a);
  -- In ra màn hình Console
  Dbms_Output.Put_Line('v_b= ' || v_b);
  -- Tính tổng
  v_Result := v_a + v_b;
  -- In ra màn hình Console
  Dbms_Output.Put_Line('v_Result= ' || v_Result);
End;

-- Khai báo một biến varchar2 độ dài 20.
-- Biến này có thể lưu giá trị cho cột First_Name
v_First_Name Varchar2(20);

-- Gán giá trị vào biến v_First_Name lấy từ câu truy vấn.
-- Ngoại lệ có thể xẩy ra khi độ dài lưu trữ của biến nhỏ
-- hơn độ dài giá trị truyền vào.
Select Emp.First_Name
into v_First_Name
From Employee Emp
Where Emp.Emp_Id = 1;

-- Khai báo một biến Varchar2 độ dài 30
-- Biến này cũng có thể lưu giá trị cho cột First_Name
v_First_Name2 Varchar2(30);

-- Cách khai báo an toàn:
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
     -- Trường hợp câu lệnh SELECT .. INTO không trả về bản ghi nào
     Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;

-- Khai báo một biến.
-- Là kiểu dữ liệu một dòng.

v_Variable_name Table_Name%Rowtype;

-- Ví dụ:
v_Emp Employee%Rowtype;

Declare
  v_Emp_Id Employee.Emp_Id%Type := 1;
  -- Khai báo một biến
  -- Là kiểu dữ liệu 1 dòng của bảng Employee.
  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
     -- Trường hợp câu SELECT không trả về bản ghi nào
     Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;

-- Khai báo một kiểu dữ liệu của bạn.

TYPE Ten_kieu_Record IS
    RECORD  (
            Col1  Kieu_Du_Lieu1  [NOT NULL{:=|DEFAULT} Bieu_Thuc],
            Col2  Kieu_Du_Lieu2  [NOT NULL{:=|DEFAULT} Bieu_Thuc],
             ...
     );


-- Khai báo biến sử dụng kiểu dữ liệu trên:
Ten_Bien  Ten_kieu_Record;

Declare
  v_Emp_Id Employee.Emp_Id%Type := 1;
  -- Định nghĩa một kiểu Record có 3 cột.
  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));
  ----
  -- Định nghĩa một biến có kiểu dữ liệu 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
     -- Trường hợp câu SELECT không trả về bản ghi nào
     Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;

-- Khai báo một kiểu table.

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

-- Ví dụ:
-- Định nghĩa một kiểu TABLE chứa các phần tử kiểu Varchar2(50)
TYPE My_Tbl
IS TABLE OF Varchar2(50)
INDEX BY BINARY_INTEGER;

Declare
  -- Định nghĩa một kiểu TABLE.
  Type My_Tbl Is Table Of Varchar2(50) Index By Binary_Integer;
  -- Khai báo một biến sử dụng kiểu dữ liệu khai báo ở trên.
  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;

Declare
  -- Định nghĩa kiểu TABLE.
  Type t_City_Type Is Table Of Varchar2(30) Index By Binary_Integer;
  -- Khai báo biến sử dụng kiểu TABLE định nghĩa ở trên.
  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
     -- Kiểm tra xem có tồn tại phần tử có chỉ số 500 không.
     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;
     --
     -- Xóa đi phần tử tại chỉ số 101
     t_City.Delete(101);
     --
     -- Chỉ số đầu tiên
     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;
        -- Lấy chỉ số tiếp theo của chỉ số cho bởi tham số.
        v_City_Index := t_City.Next(v_City_Index);
     End Loop;
     -- Ném ra ngoại lệ No_data_found
     Raise No_Data_Found;
  Exception
     When No_Data_Found Then
        Dbms_Output.Put_Line('the Last City Has Been Reached.');
  End;
End;

-- Khai báo một kiểu mảng
TYPE <varray_type_name>
IS VARRAY(n)
OF <element_type>;

-- Ví dụ khai báo một mảng 5 phần tử, và phẩn tử có kiểu Varchar2(10);
TYPE cityarray
IS VARRAY(5)
OF Varchar2(10);

Declare
-- Định nghĩa kiểu dữ liệu Array
-- chứa các dữ liệu kiểu VARCHAR2(50)
Type Emp_Array Is Varray(5) Of Varchar2(50);
-- Định nghĩa kiểu dữ liệu Array chứa các số Integer.
Type Salary_Array Is Varray(5) Of Integer;
---
v_Names    Emp_Array;
v_Salaries Salary_Array;
v_Count    Integer;
Begin
-- Khởi tạo giá trị các phần tử của mảng.
v_Names := Emp_Array('KING'
                    ,'JONES'
                    ,'FORD'
                    ,'SMITH'
                    ,'BLAKE');
-- Khởi tạo giá trị các phần tử của mảng.
v_Salaries := Salary_Array(5000
                          ,2975
                          ,3000
                          ,800
                          ,2850);
-- Số phần tử.                            
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;

-- Khai báo con trỏ có không có tham số:
CURSOR <Cursor_Name>
IS
<Select_Statement>


-- Khai báo con trỏ có tham số.
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);

Declare
   -- Khai báo một Cursor có 2 tham số.
   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);
   ---
   -- Khai báo một kiểu ROWTYPE dựa trên Cursor vừa tạo.
   v_Emp Emp_Cur%Rowtype;
   ---
   v_Dept_Id   Number := 1;
   v_Branch_Id Number;
   ---
   v_Row  Integer := 0;
   v_Open Boolean;
Begin
   -- Kiểm tra xem cursor mở chưa
   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...');
   -- Mở Cursor (Truyền các giá trị tham số vào).
   Open Emp_Cur(v_Dept_Id, v_Branch_Id);
   -- Sử dụng vòng lặp.
   Loop
       -- Lấy dòng dữ liệu trên Cursor
       -- Mỗi lần Fetch con trỏ nhẩy lên 1 dòng.
       -- (Từ trên xuống dưới).
       Fetch Emp_Cur
           Into v_Emp;
       -- Điều kiện thoát khỏi vòng lặp.    
       Exit When Emp_Cur%Notfound;
       -- Sử lý dữ liệu.
       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...');
   -- Đóng Cursor.
   Close Emp_Cur;
End;

-- Dùng lệnh For duyệt trên con trỏ không tường minh.

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

   -- Statements..

END LOOP;

Declare
  -- Khai báo một Cursor có 2 tham số.
  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
  -- Kiểm tra xem cursor mở chưa
  If Emp_Cur%Isopen Then
     Dbms_Output.Put_Line('Cursor opened');
  Else
     Dbms_Output.Put_Line('Cursor not open');
  End If;
  --
  -- Sử dụng vòng lặp để duyệt cursor
  -- Không cần 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;

-- procedure_name:  Tên thủ tục
-- argument:  Tên tham số
-- mode:  Loại tham số: IN hoặc OUT hoặc IN OUT, mặc định là IN
-- datatype:  Kiểu dữ liệu của tham số

CREATE [OR REPLACE] PROCEDURE <procedure_name>
          [
           (argument1  [mode1]  datatype1,
            argument2  [mode2]  datatype2,
           ...)
          ]
     IS | AS
BEGIN
   -- PL/SQL Block;
END;

-- Ví dụ một thủ tục không tham số.
CREATE OR REPLACE Procedure Do_Something AS
   -- Khai báo biến tại đây.
Begin
  -- Làm gì đó tại đây.
End;

-- Ví dụ một thủ tục có tham số
-- Vừa tham số đầu vào, vừa tham số đầu ra.
CREATE OR REPLACE Procedure Do_Something(p_Param1 Varchar2,
                                                                 v_Param Out Varchar2)
AS
   -- Khai báo biến tại đây.
Begin
  -- Làm gì đó tại đây.
End;

-- Hủy một thủ tục:

DROP PROCEDURE <Procedure_Name>

-- Thủ tục truyền vào p_Emp_Id
-- Và trả về 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
  -- Ghi ra màn hình console.
  -- Dành cho người lập trình biết chương trình chạy thế nào.
  Dbms_Output.Put_Line('Parameter p_Emp_Id = ' || p_Emp_Id);
  --
  -- Nếu câu lệnh Select này nếu không có bản ghi nào
  -- nó sẽ ném ra Exception NO_DATA_FOUND:
  --
  -- Câu lệnh Select ở này sẽ không trả về
  -- nhiều hơn 1 bản ghi vì Emp_Id là duy nhất
  -- trong bảng EMPLOYEE.
  -- Do vậy không xẩy ra ngoại lệ TOO_MANY_ROWS
  --
  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;
  --
  -- Ghi ra màn hình 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
     -- Ghi ra màn hình Console.
     Dbms_Output.Put_Line('No Record found with p_Emp_Id = ' || p_Emp_Id);
End Get_Employee_Infos;

-- function_name:  Tên hàm
-- argument:  Tên tham số
-- mode:  Loại tham số: IN hoặc OUT hoặc IN OUT, mặc định là IN
-- datatype:  Kiểu dữ liệu của tham số

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;

-- Hủy Function

DROP FUNCTION <function_name>;

-- Khi gọi hàm phải khai báo một biến trả về

-- Khai báo một biến c.
c Integer;
....

-- Gọi hàm.
c := Sum(10, 100);

-- Hàm truyền vào mã hệ thống của nhân viên p_Emp_ID
-- Và trả về First_Name của nhân viên.

Create Or Replace Function Get_Emp_First_Name(p_Emp_Id Number)
  Return Varchar2 As
  -- Khai báo một biến 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
        -- Gán null trong trường hợp không tìm thấy Employee
        -- ứng với p_Emp_ID
        v_Emp_First_Name := Null;
  End;
  --
  Return v_Emp_First_Name;
End;

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

-- Khai báo Package Spec:

CREATE [OR REPLACE] PACKAGE <package_name>
IS| AS
       -- Khai báo các kiểu (sẽ được sử dụng công khai)
       -- và các hàm thủ tục.
END <package_name>;

-- Khai báo phần Body Package:
CREATE [OR REPLACE] PACKAGE BODY <package_name>
 IS | AS


      -- Khai báo các kiểu chỉ sử dụng riêng trong package
      -- Triển khai nội dung của các hàm, thủ tục khai báo trong Package Spec

END <package_name>;

--
-- Đây là Package Spec của Package PKG_EMP
-- Nó khai báo 2 hàm (Trong khi Package Body có 2 hàm và 1 thủ tục).
-- Những hàm hoặc thủ tục không được khai báo trên Package Spec
-- nghĩa là chỉ được sử dụng trong nội bộ package.
--
Create Or Replace Package Pkg_Emp Is

 -- Hàm trả về First_Name
 Function Get_First_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Employee.First_Name%Type;

 -- Hàm trả về tên phòng ban của nhân viên.
 Function Get_Dept_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Department.Name%Type;

End Pkg_Emp;

--
-- Đây là Package Body của Package PKG_EMP
--
Create Or Replace Package Body Pkg_Emp Is

 -- =====================================================
 -- Thủ tục trả về thông tin nhân viên
 -- Gồm 2 tham số đầu ra 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
       -- Không tìm thấy nhân viên ứng với p_Emp_Id
       When No_Data_Found Then
          v_First_Name := Null;
          v_Last_Name  := Null;
    End;
 End;

 -- =====================================================
 -- Hàm trả về First_Name ứng với Emp_ID cho bởi tham số.
 -- =====================================================
 Function Get_First_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Employee.First_Name%Type As
    -- Khai báo một biến.
    v_First_Name Employee.First_Name%Type;
    v_Last_Name  Employee.Last_Name%Type;
 Begin
    -- Gọi sử dụng thủ tục Get_Emp_Infos
    Get_Emp_Infos(p_Emp_Id
                 ,v_First_Name -- Out
                 ,v_Last_Name -- Out
                  );
    --
    Return v_First_Name;
 End;

 -- =====================================================
 -- Hàm trả về Dept_Name ứng với Emp_ID.
 -- (Trả về tên phòng ban của nhân viên)
 -- =====================================================
 Function Get_Dept_Name(p_Emp_Id Employee.Emp_Id%Type)
    Return Department.Name%Type As
    -- Khai báo một biến.
    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;