Thứ Sáu, 30 tháng 6, 2023

Hướng dẫn lập trình Oracle PL/SQL từ A-Z

  1. Giới thiệu
  2. Bắt đầu với PL/SQL cần những gì?
  3. Tổng quan về PL/SQL
  4. Các lệnh PL/SQL cơ bản
    1. Lệnh If-elsif-else
    2. Vòng lặp không định trước (LOOP)
    3. Vòng lặp có định trước (FOR LOOP)
    4. Vòng lặp while (WHILE)
  5. Bắt đầu với PL/SQL sử dụng PL/SQL Developer
  6. Các kiểu dữ liệu thông dụng và khai báo
    1. Kiểu dữ liệu số
    2. Kiểu text
    3. Kiểu Date/time
    4. Kiểu dữ liệu một cột (%type)
    5. Kiểu dữ liệu một dòng (%Rowtype)
    6. Kiểu dữ liệu Record
    7. Kiểu dữ liệu Table
    8. Kiểu dữ liệu mảng (Array)
  7. Con trỏ (Cursor)
    1. Con trỏ là gì?
    2. Con trỏ tường minh (Explicit Cursor)
    3. Con trỏ không tường minh (Implicit Cursor)
  8. Thủ tục (Procedure)
    1. Test thủ tục trên PL/SQL Developer
    2. Debug thủ tục trên PL/SQL Developer
  9. Hàm (Function)
  10. Package
    1. Tạo package trên PL/SQL Developer
    2. Test Package
  11. Oracle Application Express là gì?

1- Giới thiệu

PL/SQL (Procedural Language/Structured Query Language) là một ngôn ngữ lập trình lập trình hướng thủ tục sử dụng cho Oracle SQL. Nó là một mở rộng của Oracle SQL.
PL/SQL bao gồm các thành phần ngôn ngữ hướng thủ tục bao gồm điều kiện và vòng lặp. Nó cho phép khai báo hằng số và biến, thủ tục và các hàm, kiểu dữ liệu và biến của các kiểu dữ liệu, và các trigger. Nó có thể sử lý các ngoại lệ (lỗi tại thời gian chạy). Mảng cũng được hỗ trợ nâng cấp để sử dụng cho các tập hợp trong PL/SQL. Từ phiên bản 8 trở đi nó bao gồm thêm các tính năng hướng đối tượng. Nó có thể tạo một đơn vị PL/SQL như thủ tục, hàm, package, kiểu dữ liệu, triggers, những thứ được lưu trữ trong database được tái sử dụng bởi các ứng dụng bất kỳ giao tiếp với ứng dụng Oracle.

Chú ý: Các hình minh họa dưới đây tôi sử dụng công cụ PL/SQL Developer phiên bản 8.x, tuy nhiên cũng không có khác biệt nếu bạn sử dụng PL/SQL Developer phiên bản 10.x hoặc một phiên bản khác.

2- Bắt đầu với PL/SQL cần những gì?

Để có thể tiếp cận nhanh với PL/SQL bạn cần có một công cụ lập trình. Theo kinh nghiệm làm việc của tôi bạn có thể sử dụng PL/SQL Developer, đây là một công cụ trực quan làm việc với Oracle và để lập trình PL/SQL.
Bạn có thể xem hướng dẫn cài đặt và cấu hình PL/SQL tại:
  • Cài đặt PL/SQL Developer trên Windows
Trong tài liệu này tôi sử dụng LearningSQL (Một database nhỏ dùng để hướng dẫn học SQL trong website o7planning.org). Bạn có thể tạo database này theo hướng dẫn dưới đây:
  • Cơ sở dữ liệu Oracle mẫu để học SQL

3- Tổng quan về PL/SQL

Có một số khái niệm cần phải nắm vững khi lập trình với PL/SQL:
  1. Mỗi lệnh SQL kềt thúc bằng dấu chấm phẩy (;)
  2. Các lệnh thuộc "ngôn ngữ định nghĩa dữ liệu" (Data Definition Language - DDL) không được sử dụng trong PL/SQL
  3. Lệnh SELECT.. INTO trả về nhiều dòng có thể gây ra exception ( > 1 dòng).
  4. Lệnh SELECT .. INTO không trả về dòng nào có thể gây ra exception
  5. Các lệnh thuộc "ngôn ngữ thao tác trên dữ liệu" (Data Manipulation Language - DML) có thể tác động trên nhiều dòng dữ liệu.
  6. Sử dụng toán tử := để giá giá trị cho một biến.

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

......
PL/SQL được tổ chức theo từng khối lệnh, Một khối lệnh có thể có các khối lệnh con bên trong nó.

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;

4- Các lệnh PL/SQL cơ bản

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

4.1- Lệnh If-elsif-else

Cú pháp:

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;
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- Vòng lặp không định trước (LOOP)

Cú pháp:

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

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

4.3- Vòng lặp có định trước (FOR LOOP)

Cú pháp:

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

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

4.4- Vòng lặp while (WHILE)

Cú pháp:

WHILE <Điều kiện> LOOP
 -- Làm gì đó tại đây
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 sử dụng PL/SQL Developer

Trước hết bạn cần mở PL/SQL Developer, và đăng nhập vào user learningsql:
Trên PL/SQL Developer tạo mới một cửa sổ SQL:
Viết một đoạn code đơn giản tính tổng 2 số.

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;
Nhấn biểu tượng  hoặc nhấn F8 để thực thi đoạn code.
Kết quả chạy ví dụ:

6- Các kiểu dữ liệu thông dụng và khai báo

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

Các kiểu dữ liệu số trong PL/SQL
Data TypeDescription
PLS_INTEGERSố tự nhiên có dấu 32 bit nằm trong khoảng -2,147,483,648 tới 2,147,483,647.
BINARY_INTEGERSố tự nhiên có dấu 32 bit nằm trong khoảng -2,147,483,648 tới 2,147,483,647.
BINARY_FLOATKiểu dấu chấm động số thực với độ chính xác đơn (Single-precision)
BINARY_DOUBLEKiểu dấu chấm động số thực  với độ chính xác gấp đôi (Double-precision)
NUMBER(prec, scale)Kiểu dấu chấm cố định (Fixed-point) với giá trị tuyệt đối từ 1E-130 tới (không bao gồm) 1.0E126. Một biến NUMBER cũng có thể mô tả 0.
DEC(prec, scale)Kiểu dấu chấm cố định, tiêu chuẩn ANSI với độ chính xác tối đa 38 số thâp phân.
DECIMAL(prec, scale)Kiểu dấu chấm cố định, tiêu chuẩn IBM với độ chính xác tối đa 38 số thập phân.
NUMERIC(pre, secale)Loại số thực (Floating type) với độ chính xác tối đa 38 số thập phân.
DOUBLE PRECISIONLoại số chấm động, số thực, tiêu chuẩn ANSI với độ chính xác 126 số nhị phân (khoảng 38 số thập phân).
FLOATLoại số chấm động, số thực tiêu chuẩn ANSI và IBM với độ chính xác tối đa 126 số nhị phân (khoảng 38 số thập phân).
INTKiểu số nguyên, tiêu chuẩn ANSI với độ chính xác tối đa 38 chữ số thập phân
INTEGERKiểu số nguyên, tiêu chuẩn ANSI và IBM với độ chính xác 38 chữ số thập phân
SMALLINTSố nguyên từ -32768  --> 32767
REALKiểu số chấm động, số thực, với độ chính xác tối đa 63 số nhị phân (Khoảng  18 số thập phân).
Các kiểu số thông dụng nhất:
Kiểu sốKhai báo sử dụng
Numberv_Amount Number(15,2)
v_Salary Number;
Integerv_Age Integer;
Floatv_Amount Float;
Double 
Real 

6.2- Kiểu text

Thông dụng nhất:
Kiểu textKhai báo sử dụng
Varchar2v_First_Name Varchar2(32);
  

6.3- Kiểu Date/time

Kiểu dữ liệuKhai báo sử dụng
Datev_Birthday Date;
  

6.4- Kiểu dữ liệu một cột (%type)

Đây là cấu trúc bảng EMPLOYEE:
Cột First_Name trong bảng Employee có kiểu dữ liệu Varchar2 và 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 theo cách dưới đây:

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

6.5- Kiểu dữ liệu một dòng (%Rowtype)

Cú pháp:

-- 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;
Ví dụ:

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;
Kết quả chạy ví dụ:

6.6- Kiểu dữ liệu Record

Bạn có thể định nghĩa ra kiểu dữ liệu Record, kiểu dữ liệu này chứa một vài cột. Cú pháp:

-- 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;
Ví dụ:

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;
Kết quả chạy ví dụ:

6.7- Kiểu dữ liệu Table

Bạn có thể định nghĩa một kiểu dữ liệu mới, nó có thể lưu trữ nhiều phần tử, đó là kiểu TABLE.
Các đặc điểm của kiểu TABLE:
  • Kiểu dữ liệu TABLE giống như một mảng, nhưng có số phần tử không giới hạn.
  • Chỉ số của kiểu TABLE không nhất thiết liên tục. Ví dụ TABLE có 3 phần tử tại chỉ số 1, 3, 5.
Cú pháp:

-- 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;
Ví dụ:

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;
Kết quả chạy ví dụ:
Các hàm của kiểu TABLE:
Tên hàm/Thuộc tínhÝ nghĩaVí dụ sử dụng
• DELETEXóa các dòng trong bảngv_tbl.delete(3);
• EXISTSTrả về TRUE nếu tồn tại phần tử chỉ định trong Table.v_e:= v_tbl.exists(3);
• COUNTTrả về số lượng phần tử trong table.v_count:=v_tbl.count;
• FIRSTTrả về chỉ số của phần tử đầu tiên trong table.v_first_idx:=v_tbl.first;
• LASTTrả về chỉ số phần tử cuối cùng trong table.v_last_idx:=v_tbl.last;
• NEXTTrả về chỉ số của phần tử tiếp theo trong bảng so với chỉ số được chỉ định.v_idx:= v_tbl.next(2);
• PRIORTrả về chỉ số phần tử đứng trước so với phần tử được chỉ định.v_idx:=v_tbl.prior(2);
Ví dụ:

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;
Kết quả chạy ví dụ:

6.8- Kiểu dữ liệu mảng (Array)

Cú pháp:

-- 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);
Một mảng có N phần tử. Các phần tử của mảng được đánh chỉ số liên tục bắt đầu từ 1 đến N.
Ví dụ:

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;
Kết quả chạy ví dụ:

7- Con trỏ (Cursor)

7.1- Con trỏ là gì?

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

-- 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>
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ỏ (Cursor):
  1. Con trỏ tường minh
  2. Con trỏ không tường minh.
Khái niệm tường minh ở đây có nghĩa là, 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ở.
Các thuộc tính của Con trỏ:
Thuộc tínhÝ nghĩa
%isopen trả lại giá trị True nếu cursor đang mở
%notfound trả lại giá trị true nếu không còn dòng tiếp theo
%found trả lại giá tri true nếu vẫn còn dòng tiếp theo.
%rowcounttrả lại số row đã duyệt qua.

7.2- Con trỏ tường minh (Explicit Cursor)

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

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;
Kết quả chạy ví dụ:

7.3- Con trỏ không tường minh (Implicit Cursor)

Con trỏ không tường minh bạn có thể không cần viết lệnh mở/đóng nó một cách rõ ràng.
Sử dụng lệnh For để duyệt trên con trỏ theo cú pháp:

-- 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;
Ví dụ:

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;

8- Thủ tục (Procedure)

Một nhóm các lệnh thực hiện chức năng nào đó có thể được gom lại trong một thủ tục (procedure) nhằm làm tăng khả năng xử lý,khả năng sử dụng chung, tăng tính bảo mật và an toàn dữ liệu,tiện ích trong phát triển.
Thủ tục có thể được lưu giữ ngay trong database như một đối tượng của database, sẵn sàng cho việc tái sử dụng. Thủ tục lúc này được gọi là Stored procedure. Với các thủ tục, ngay khi lưu giữ (save), chúng đã được biên dịch thành dạng p-code vì thế có thể nâng cao khả năng thực hiện.
Thủ tục không trả về giá trị trực tiếp như hàm.
Cú pháp tạo một thủ tục:

-- 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ụ:

-- 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 thủ tục (drop procedure):

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

DROP PROCEDURE <Procedure_Name>
Các bước thực hiện một thủ tục:
Ví dụ tạo một thủ tục:
Đây là một ví dụ tạo một thủ tục đầu tiên trên PL/SQL Developer. ví dụ này sẽ là khuôn mẫu giúp bạn vừa lập trình vừa kiểm tra lỗi nếu có vấn đề xẩy ra.
  1. Tạo một thủ tục (Procedure)
  2. Biên dịch thủ tục này
  3. Chạy thủ tục
  4. Debug thủ tục bằng PL/SQL Developer để xem chương trình chạy thế nào.
Tạo mới một thủ tục (Procedure):
Nhập vào tên của thủ tục, các tham số sẽ được viết sau:
  • Get_Employee_Infos
Thủ tục đã được PL/SQL Developer tạo ra. Tuy nhiên bạn cần sửa lại danh sách tham số, và viết code cho thủ tục này.
Sửa thủ tục của bạn như sau:

-- 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;
Nhấn vào biểu tượng Execute  hoặc F8 để biên dịch thủ tục. Trong trường hợp có lỗi code, PL/SQL Developer sẽ thông báo cho bạn.

8.1- Test thủ tục trên PL/SQL Developer

Nhấn phải chuột vào thủ tục Get_Employee_Infos chọn Test:
Nhập tham số đầu vào, ví dụ:
  • p_Emp_Id = 1
Kết quả thực thi thủ tục:
Xem trên màn hình Console:
Test trường hợp khác với các giá trị:
  • p_Emp_Id = 9999
Xem trên Console:

8.2- Debug thủ tục trên PL/SQL Developer

Debug trên PL/SQL Developer cho phép bạn xem một thủ tục, hàm đã được chạy thế nào, theo từng lệnh. Giúp bạn dễ dàng tìm ra các vị trí phát sinh lỗi. Bạn có thể xem hướng dẫn tại:
  • TODO

9- Hàm (Function)

Tương tự như thủ tục, hàm (function) cũng là nhóm các lệnh PL/SQL thực hiện chức năng nào đó. Khác với thủ tục, các hàm sẽ trả về một giá trị ngay tại lời gọi của nó.
Hàm cũng có thể được lưu giữ ngay trên database dưới dạng Stored procedure.
Với việc sử dụng hàm, trong một số trường hợp bạn có thể thấy được các lợi điểm như sau:
  1. Cho phép thực hiện các thao tác phức tạp(các phép tìm kiếm, so sánh phứctạp) ngay trong mệnh đề của câu lệnh SQL mà nếu không sử dụng hàm bạn sẽ không thể nào thực hiện được
  2. Tăng tính độc lập của dữ liệu do việc phân tích và xử lý dữ liệu được thực hiện ngay trên Server thay vì trả về dữ liệu trực tiếp cho ứng dụng dưới Client để chúng tiếp tục xử lý.
  3. Tăng tính hiệu quả của câu lệnh truy vấn bằng việc gọi các hàm ngay trong câu lệnh SQL
  4. Bạn có thể sử dụng hàm để thao tác trên các kiểu dữ liệu tự tạo. Cho phép thực hiện đồng thời các câu lệnh truy vấn
Một số hạn chế khi sử dụng hàm trong câu lệnh SQL
  1. Chỉ các hàm do người dùng định nghĩa được lưu trên database mới có thể sử dụng được cho câu lệnh SQL.
  2. Các hàm do người dùng định nghĩa chỉ được áp dụng cho điều kiện thực hiện trên các dòng dữ liệu (mệnh đề WHERE), không thể áp dụng cho các điều kiện thực hiện trên nhóm (mệnh đề GROUP).
  3. Tham số sử dụng trong hàm chỉ có thể là loại IN, không chấp nhận giá trị OUT hay giá trị IN OUT.
  4. Kiểu dữ liệu trả về của các hàm phải là kiểu dữ liệu DATE, NUMBER, NUMBER. Không cho phép hàm trả về kiểu dữ liệu như BOOLEAN, RECORD, TABLE. Kiểu dữ liệu trả về này phải tương thích với các kiểu dữ liệu bên trong Oracle Server .
Cú pháp tạo Hàm.

-- 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;
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;
Hủy Function (Drop function):

-- Hủy Function

DROP FUNCTION <function_name>;
Gọi hàm.

-- 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);
Ví dụ tạo một hàm.

-- 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;
Các hàm không có tham số OUT, có thể tham gia vào câu lệnh SQL, ví dụ:

Select Emp.Emp_Id
     ,Get_Emp_First_Name(Emp.Emp_Id) Emp_First_Name
From   Employee Emp;
Kết quả chạy câu lệnh SQL trên:

10- Package

Package là một tập hợp các kiểu dữ liệu, biến lưu giữ giá trị và các thủ tục,hàm có cùng một mối liên hệ với nhau, được gộp chung lại. Đặc điểm nổi bật nhất của package là khi một phần tử trong package được gọi tới thì toàn bộ nội dung của package sẽ được nạp vào trong hệ thống. Do đó, việc gọi tới các phần tử khác trong package sau này sẽ không phải mất thời gian nạp vào hệ thống nữa. Từ đó, nâng cao tốc độ thực hiện lệnh của toàn bộ hàm, thủ tục có trong package.
Cấu trúc của Package:
Một package được cấu trúc làm hai phần. Phần mô tả (specification) định nghĩa các giao tiếp có thể có của package với bên ngoài. Phần thân (body) là các cài đặt cho các giao tiếp có trong phần mô tả ở trên.
Trong cấu trúc của package bao gồm 5 thành phần:
  1. Public variable (biến công cộng): là biến mà các ứng dụng bên ngoài có thể tham chiếu tới được (sử dụng được).
  2. Public procedure (thủ tục công cộng): bao gồm các hàm, thủ tục của package có thể gọi từ các ứng dụng bên ngoài.
  3. Private procedure (thủ tục riêng tư): là các hàm, thủ tục có trong package và chỉ có thể được gọi bởi các hàm hay thủ tục khác trong package đó mà thôi.
  4. Global variable (biến tổng thể): là biến được khai báo dùng trong toàn bộ package, ứng dụng bên ngoài tham chiếu được tới biến này .
  5. Private variable (biến riêng tư): là biến được khai báo trong một hàm, thủ tục thuộc package.Nó chỉ có thể được sử dụng trong nội bộ hàm hay thủ tục đó.
Khai báo Package:

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

10.1- Tạo package trên PL/SQL Developer

PL/SQL Developer giúp bạn tạo nhanh package spec & package body.
Package đã được PL/SQL Developer tạo ra, với các gợi ý để viết package được tự động sinh ra. Bạn có thể xóa hết chúng đi.
Bạn có thể xóa hết các code tự tạo ra tự động bởi PL/SQL Developer để có một package rỗng:
  • PKG_EMP (Package Spec)

--
-- Đâ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;
  • PKG_EMP (Package Body)

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

10.2- Test Package

Cũng giống như thủ tục và hàm, bạn cũng có thể test các thủ tục/hàm trên Package, điều này giúp bạn phát hiện ra các lỗi trong quá trình lập trình.
Kết quả test:

11- Oracle Application 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ác cơ sở dữ liệu Oracle. Chỉ sử dụng một trình duyệt web và kinh nghiệm lập trình không cần nhiều, bạn có thể phát triển và triển khai các ứng dụng chuyên nghiệp mà cả hai nhanh chóng và bảo mật. Oracle Application Express kết hợp những phẩm chất của một cơ sở dữ liệu cá nhân, năng suất, dễ sử dụng, và tính linh hoạt với những phẩm chất của một cơ sở dữ liệu doanh nghiệp, bảo mật, toàn vẹn, khả năng mở rộng, tính sẵn có và 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à các môi trường phát triển ứng dụng cũng thuận tiện dựa trên web.
Oracle APEX chỉ đòi hỏi bạn có một kỹ năng về Oracle PL/SQL. Bạn có thể xem thêm Oracle Application Express là gì tại:
=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
Hướng dẫn lập trình Oracle PL/SQL từ A-Z, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels:

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master