CREATE PACKAGE BODY文法與樣本-雲原生資料庫 PolarDB-阿里雲

CREATE PACKAGE BODY定義一個包體。CREATE OR REPLACE PACKAGE BODY可以建立新的包體或替換現有的包體。包是組織和封裝資料庫應用程式邏輯的重要工具。通過合理設計和使用包，可以提高代碼的可維護性、重用性和效能。

簡介

使用CREATE PACKAGE BODY語句建立包的主體，該主體是資料庫中的相關過程、儲存函數和其他程式對象的封裝集合。包體定義了這些對象。在前面的CREATE PACKAGE語句中定義的包規範聲明了這些對象。

文法

CREATE [ OR REPLACE ] PACKAGE BODY <package_name>
{ IS | AS }
  [ declaration; ] | [ forward_declaration ] [, ...]
  [ { PROCEDURE <proc_name>
      [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...]) ]
      [ STRICT ]
      [ LEAKPROOF ]
      [ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
      [ COST <execution_cost> ]
      [ ROWS <result_rows> ]
      [ SET <config_param> { TO <value> | = <value> | FROM CURRENT } ]
    { IS | AS }
        <program_body>
      END [ <proc_name> ];
    |
      FUNCTION <func_name>
      [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...]) ]
      RETURN <rettype> [ DETERMINISTIC ]
      [ STRICT ]
      [ LEAKPROOF ]
      [ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
      [ COST <execution_cost> ]
      [ ROWS <result_rows> ]
      [ SET <config_param> { TO <value> | = <value> | FROM CURRENT } ]
    { IS | AS }
        <program_body>
      END [ <func_name> ];
    }
  ] [, ...]
  [ BEGIN
      <statement>; [, ...] ]
  END [ <name> ]

Where forward_declaration:=

[ { PROCEDURE <proc_name>
  [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...])
  ] ;
|
    FUNCTION <func_name>
  [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...])
  ]
  RETURN <rettype> [ DETERMINISTIC ]; }]

參數說明

參數	描述
package_name	已建立的包名稱。
declaration	私人變數、類型、遊標或`REF CURSOR`聲明。
forward_declaration	前向聲明，用於在實際定義之前聲明一個過程或函數。在一個模組中，您可以建立多個子程式。如果這些子程式相互調用，則每個子程式均需要一個前向聲明。必須先聲明子程式，然後才能進行調用。您可以通過前向聲明來聲明子程式，而無需進行具體定義。前向聲明及其相應的定義必須位於同一代碼塊中。
proc_name	公用預存程序名稱。
func_name	公用函數名稱。
STRICT	使用`STRICT`關鍵字，指定在使用`NULL`參數調用時不執行的函數。相反，該函數返回`NULL`。
LEAKPROOF	使用`LEAKPROOF`關鍵字，指定除了傳回值之外，不顯示有關參數的任何資訊。
PARALLEL { UNSAFE \| RESTRICTED \| SAFE }	`PARALLEL`子句允許使用並行順序掃描（並行模式）。與串列順序掃描不同，並行順序掃描在查詢過程中使用多個`worker`並行掃描關係。取值範圍如下： UNSAFE（預設值）：預存程序或函數不能以並行模式執行。存在此類預存程序或函數時，會強制執行串列執行計畫。 RESTRICTED：預存程序或函數可以按並行模式執行，但執行限制為並行組中的前幾個。如果任何特定關係的限定條件具有存在並行限制的任何內容，則不會為並存執行選擇該關係。 SAFE：預存程序或函數可以按並行模式執行，沒有任何限制。
execution_cost	execution_cost指定一個正數，提供函數的估計執行成本，單位為cpu_operator_cost（CPU操作成本）。如果函數返回一個集合，則這是每個返回行的成本。預設值為0.0025。
result_rows	result_rows是查詢計劃程式預計函數返回的估計行數。預設值為1000。
SET	使用SET子句為函數的期間指定參數值： config_param：指定參數名稱。 value：指定參數值。 FROM CURRENT：確保在函數結束時還原參數值。
program_body	構成函數或過程主體的`pragma`、聲明和`PL/SQL`語句。聲明可以包括變數、類型、`REF CURSOR`或子程式聲明。
argname	參數的名稱。
IN \| IN OUT \| OUT	參數模式。
argtype	程式參數的資料類型。
DEFAULT value	輸入參數的預設值。
rettype	返回資料類型。
DETERMINISTIC	使用DETERMINISTIC以指定在提供相同參數值時，函數將始終返回相同結果。DETERMINISTIC函數不可修改資料庫。
statement	`PL/SQL`程式語句。包初始化部分中的語句在第一次引用包時每個會話執行一次。

樣本

以下是一個完整的包建立和使用樣本。

建立測試表。

CREATE TABLE employees (
    emp_id     NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name  VARCHAR2(50),
    salary     NUMBER,
    department VARCHAR2(50)
);

建立包頭。

CREATE OR REPLACE PACKAGE Employee_Pkg AS
    -- 常量
    MAX_SALARY CONSTANT NUMBER := 100000;

    -- 過程聲明
    PROCEDURE Add_Employee(
        p_emp_id     IN NUMBER,
        p_first_name IN VARCHAR2,
        p_last_name  IN VARCHAR2,
        p_salary     IN NUMBER
    );

    PROCEDURE Update_Salary(
        p_emp_id     IN NUMBER,
        p_new_salary IN NUMBER
    );

    -- 函式宣告
    FUNCTION Get_Employee_Name(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2;

    FUNCTION Get_Employee_Department(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2;
END Employee_Pkg;

建立包體。

CREATE OR REPLACE PACKAGE BODY Employee_Pkg AS

    -- 私人變數
    g_total_employees NUMBER := 0;

    -- 過程實現
    PROCEDURE Add_Employee(
        p_emp_id     IN NUMBER,
        p_first_name IN VARCHAR2,
        p_last_name  IN VARCHAR2,
        p_salary     IN NUMBER
    ) IS
    BEGIN
        INSERT INTO employees (emp_id, first_name, last_name, salary)
        VALUES (p_emp_id, p_first_name, p_last_name, p_salary);
        g_total_employees := g_total_employees + 1;
    END Add_Employee;

    PROCEDURE Update_Salary(
        p_emp_id     IN NUMBER,
        p_new_salary IN NUMBER
    ) IS
    BEGIN
        IF p_new_salary > MAX_SALARY THEN
            RAISE_APPLICATION_ERROR(-20001, 'Salary exceeds maximum allowed.');
        END IF;
        UPDATE employees
        SET salary = p_new_salary
        WHERE emp_id = p_emp_id;
    END Update_Salary;

    -- 函數實現
    FUNCTION Get_Employee_Name(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2 IS
        v_name VARCHAR2(100);
    BEGIN
        SELECT first_name || ' ' || last_name
        INTO v_name
        FROM employees
        WHERE emp_id = p_emp_id;
        RETURN v_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 'Employee Not Found';
    END Get_Employee_Name;

    FUNCTION Get_Employee_Department(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2 IS
        v_department VARCHAR2(100);
    BEGIN
        SELECT department
        INTO v_department
        FROM employees
        WHERE emp_id = p_emp_id;
        RETURN v_department;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 'Not Assigned';
    END Get_Employee_Department;

END Employee_Pkg;

使用包。

DECLARE
    v_name       VARCHAR2(100);
    v_department VARCHAR2(100);
BEGIN
    Employee_Pkg.Add_Employee(
        p_emp_id     => 1001,
        p_first_name => 'Jane',
        p_last_name  => 'Smith',
        p_salary     => 60000
    );

    Employee_Pkg.Add_Employee(
        p_emp_id     => 1002,
        p_first_name => 'John',
        p_last_name  => 'Doe',
        p_salary     => 55000
    );

    Employee_Pkg.Update_Salary(
        p_emp_id     => 1001,
        p_new_salary => 65000
    );

    v_name := Employee_Pkg.Get_Employee_Name(1001);
    v_department := Employee_Pkg.Get_Employee_Department(1001);
    DBMS_OUTPUT.PUT_LINE('NAME: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT: ' || v_department);
END;