五月 22, 2023
Oracle 的 PL/SQL 包是一组相关子程序，与其使用的游标和变量一起，作为一个单元存储在数据库中。打包的子程序可以由应用程序或用户显式调用。
Oracle 示例
PL/SQL 包的创建分两个部分：包规范和包体。包规范声明包的所有公有结构，而包体定义包的所有（公有的和私有的）结构。
下面的示例显示了用于创建emp_actions包规范的语句的一部分，封装了用于管理雇员的几个子程序。包的每个部分用不同的语句创建。
CREATE PACKAGE emp_actions AS
/* Declare externally visible types, cursor, exception. */
TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
TYPE DeptRecTyp IS RECORD (dept_id INTEGER, location VARCHAR2);
CURSOR desc_salary RETURN EmpRecTyp;
salary_missing EXCEPTION;
/* Declare externally callable subprograms. */
FUNCTION hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) RETURN INTEGER;
PROCEDURE fire_employee (emp_id INTEGER);
PROCEDURE raise_salary (emp_id INTEGER, increase NUMBER);
FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp;
END emp_actions;
此规范声明了函数hire_employee、过程fire_employee和raise_salary、以及异常salary_missing。所有这些公有程序对象，对有权访问该包的用户都是可用的。
CREATE PACKAGE BODY命令定义在规范中声明的对象。包体必须在和包相同的模式中创建。在创建包之后，您就可以开发应用程序，并在其中调用这些公共过程或函数，或抛出包中的任何公共例外。
CREATE PACKAGE BODY emp_actions AS
number_hired INTEGER; -- visible only in this package
/* Fully define cursor specified in package. */
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
/* Fully define subprograms specified in package. */
FUNCTION hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) RETURN INTEGER IS
new_empno INTEGER;
BEGIN
SELECT empno_seq.NEXTVAL INTO new_empno FROM dual;
INSERT INTO emp VALUES (new_empno, ename, job,
mgr, SYSDATE, sal, comm, deptno);
number_hired := number_hired + 1;
RETURN new_empno;
END hire_employee;
PROCEDURE fire_employee (emp_id INTEGER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
PROCEDURE raise_salary (emp_id INTEGER, increase NUMBER) IS
current_salary NUMBER;
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current_salary IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE emp SET sal = sal + increase
WHERE empno = emp_id;
END IF;
END raise_salary;
FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
emp_rec EmpRecTyp;
BEGIN
OPEN desc_salary;
FOR i IN 1..n LOOP
FETCH desc_salary INTO emp_rec;
END LOOP;
CLOSE desc_salary;
RETURN emp_rec;
END nth_highest_salary;
/* Define local function, available only in package. */
FUNCTION rank (emp_id INTEGER, job_title VARCHAR2)
RETURN INTEGER IS
/* Return rank (highest = 1) of employee in a given
job classification based on performance rating. */
head_count INTEGER;
score NUMBER;
BEGIN
SELECT COUNT(*) INTO head_count FROM emp
WHERE job = job_title;
SELECT rating INTO score FROM reviews
WHERE empno = emp_id;
score := score / 100; -- maximum score is 100
RETURN (head_count + 1) - ROUND(head_count * score);
END rank;
BEGIN -- initialization part starts here
INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ACTIONS');
number_hired := 0;
END emp_actions;
上面的number_hired就是一个包变量。
PostgreSQL 中的会话级变量
下面是一个PL/pgSQL函数实现的示例，用于存储和检索临时表中的变量。通过在创建临时表时指定ON COMMIT PRESERVE ROWS，可以实现会话级变量。通过在创建临时表时指定ON COMMIT DELETE ROWS，可以实现事务级变量。
CREATE FUNCTION put_var(key TEXT, data TEXT) RETURNS VOID AS $$
BEGIN
CREATE TEMP TABLE IF NOT EXISTS session_vars
(name TEXT PRIMARY KEY, value TEXT);
UPDATE session_vars SET value = data WHERE name = key;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO session_vars(name,value) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION get_var(key TEXT) RETURNS TEXT AS $$
DECLARE
result TEXT;
BEGIN
CREATE TEMP TABLE IF NOT EXISTS session_vars
(name TEXT PRIMARY KEY, value TEXT);
SELECT value FROM session_vars where name = key INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION del_var(key TEXT) RETURNS VOID AS $$
BEGIN
CREATE TEMP TABLE IF NOT EXISTS session_vars
(name TEXT PRIMARY KEY, value TEXT);
DELETE FROM session_vars WHERE name = key;
END;
$$ LANGUAGE plpgsql;