Chủ Nhật, 5 tháng 7, 2015

Procedure, Function và Package trong Oracle PL/SQL


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à Store procedure. Với các Store procedure, ngay khi lưu giữ Store procedure, 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:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 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ụ:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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:
?
1
2
3
-- 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.
Nếu bạn mới bắt đầu với PL/SQL bạn hãy xem chi tiết mục này, nó sẽ giúp bạn dễ dàng hiểu các vấn đề tiếp theo được đề cập trong tài liệu này.
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:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 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.
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:
  • p_Emp_Id = 9999
Xem trên Console:
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
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 Store 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.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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ụ:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 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:
?
1
2
3
-- Hủy Function
DROP FUNCTION <function_name>;
Gọi hàm.
?
1
2
3
4
5
6
7
8
-- 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.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 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ụ:
?
1
2
3
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:
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 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 05 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.
  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ể triệugọi từ các ứngdụngbên ngoài.
  3. Private procedure (thủ tục riêng phần): là các hàm, thủ tục có trong package và chỉ có thể được triệu 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 phần): là biến được khai báo trong một hàm, thủ tục thuộc package.Nó chỉ có thể được tham chiếu đến trong bản thân hàm hay thủ tục đó.
Khai báo Package:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 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>;
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)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--
-- Đâ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)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
--
-- Đâ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;
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:
Người đăng: vào lúc
Nhãn: , , , , , ,
Google Account Video Purchases Hà Noi, Hoàn Kiếm, Hà Nội, Việt Nam

2 nhận xét:

Đăng ký: Đăng Nhận xét (Atom)