PL/SQL包与包体 #

包结构
├── 包规范
│   ├── 公有类型声明
│   ├── 公有变量声明
│   ├── 公有异常声明
│   ├── 公有游标声明
│   └── 公有子程序声明
└── 包体
    ├── 私有类型声明
    ├── 私有变量声明
    ├── 私有异常声明
    ├── 私有游标声明
    ├── 私有子程序定义
    └── 公有子程序实现

包的优势
├── 模块化
│   └── 相关对象组织在一起
├── 信息隐藏
│   └── 公有和私有成员分离
├── 性能优化
│   └── 整个包一次性加载到内存
├── 会话状态
│   └── 包变量在会话期间保持
└── 重载
    └── 支持子程序重载

-- 创建包规范基本语法
CREATE [OR REPLACE] PACKAGE package_name
    [AUTHID DEFINER|CURRENT_USER]
AS|IS
    -- 公有类型声明
    -- 公有变量声明
    -- 公有异常声明
    -- 公有游标声明
    -- 公有子程序声明
END [package_name];

-- 创建包规范
CREATE OR REPLACE PACKAGE emp_pkg AS
    -- 公有常量
    c_max_salary CONSTANT NUMBER := 100000;
    c_min_salary CONSTANT NUMBER := 1000;
    
    -- 公有类型
    TYPE t_emp_record IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(100),
        salary NUMBER
    );
    
    -- 公有异常
    e_invalid_salary EXCEPTION;
    
    -- 公有游标
    CURSOR c_emp_by_dept(p_dept_id NUMBER) IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
    
    -- 公有过程声明
    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_salary(
        p_emp_id IN NUMBER
    ) RETURN NUMBER;
END emp_pkg;
/

-- 创建包体基本语法
CREATE [OR REPLACE] PACKAGE BODY package_name
AS|IS
    -- 私有类型声明
    -- 私有变量声明
    -- 私有异常声明
    -- 私有游标声明
    -- 私有子程序定义
    -- 公有子程序实现
END [package_name];

-- 创建包体
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
    -- 私有变量
    g_update_count NUMBER := 0;
    
    -- 私有函数
    FUNCTION validate_salary(p_salary IN NUMBER) RETURN BOOLEAN IS
    BEGIN
        RETURN p_salary BETWEEN c_min_salary AND c_max_salary;
    END;
    
    -- 私有过程
    PROCEDURE log_salary_change(
        p_emp_id IN NUMBER,
        p_old_salary IN NUMBER,
        p_new_salary IN NUMBER
    ) IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO salary_audit (
            employee_id, old_salary, new_salary, change_time
        ) VALUES (
            p_emp_id, p_old_salary, p_new_salary, SYSTIMESTAMP
        );
        COMMIT;
    END;
    
    -- 公有过程实现
    PROCEDURE update_salary(
        p_emp_id IN NUMBER,
        p_new_salary IN NUMBER
    ) IS
        v_old_salary NUMBER;
    BEGIN
        -- 验证薪资
        IF NOT validate_salary(p_new_salary) THEN
            RAISE e_invalid_salary;
        END IF;
        
        -- 获取旧薪资
        SELECT salary INTO v_old_salary
        FROM employees
        WHERE employee_id = p_emp_id;
        
        -- 更新薪资
        UPDATE employees
        SET salary = p_new_salary
        WHERE employee_id = p_emp_id;
        
        -- 记录变更
        log_salary_change(p_emp_id, v_old_salary, p_new_salary);
        
        -- 更新计数
        g_update_count := g_update_count + 1;
        
        COMMIT;
        
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
        WHEN e_invalid_salary THEN
            RAISE_APPLICATION_ERROR(-20002, 
                'Salary must be between ' || c_min_salary || ' and ' || c_max_salary);
    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 employee_id = p_emp_id;
        
        RETURN v_name;
        
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL;
    END get_employee_name;
    
    FUNCTION get_salary(
        p_emp_id IN NUMBER
    ) RETURN NUMBER IS
        v_salary NUMBER;
    BEGIN
        SELECT salary INTO v_salary
        FROM employees
        WHERE employee_id = p_emp_id;
        
        RETURN v_salary;
        
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL;
    END get_salary;
END emp_pkg;
/

-- 调用包中的常量
SELECT emp_pkg.c_max_salary FROM DUAL;

-- 调用包中的函数
SELECT emp_pkg.get_employee_name(100) FROM DUAL;
SELECT emp_pkg.get_salary(100) FROM DUAL;

-- 调用包中的过程
EXEC emp_pkg.update_salary(100, 15000);

-- 在PL/SQL中使用
DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    v_name := emp_pkg.get_employee_name(100);
    v_salary := emp_pkg.get_salary(100);
    
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
    
    emp_pkg.update_salary(100, v_salary * 1.1);
END;
/

-- 使用包中的游标
DECLARE
    v_emp emp_pkg.t_emp_record;
BEGIN
    FOR emp_rec IN emp_pkg.c_emp_by_dept(10) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name);
    END LOOP;
END;
/

-- 包变量在会话期间保持状态
CREATE OR REPLACE PACKAGE session_pkg AS
    g_counter NUMBER := 0;
    
    PROCEDURE increment_counter;
    PROCEDURE reset_counter;
    FUNCTION get_counter RETURN NUMBER;
END session_pkg;
/

CREATE OR REPLACE PACKAGE BODY session_pkg AS
    PROCEDURE increment_counter IS
    BEGIN
        g_counter := g_counter + 1;
    END;
    
    PROCEDURE reset_counter IS
    BEGIN
        g_counter := 0;
    END;
    
    FUNCTION get_counter RETURN NUMBER IS
    BEGIN
        RETURN g_counter;
    END;
END session_pkg;
/

-- 测试
EXEC session_pkg.increment_counter;
SELECT session_pkg.get_counter FROM DUAL;  -- 1

EXEC session_pkg.increment_counter;
SELECT session_pkg.get_counter FROM DUAL;  -- 2

EXEC session_pkg.reset_counter;
SELECT session_pkg.get_counter FROM DUAL;  -- 0

-- 子程序重载：同名但参数不同
CREATE OR REPLACE PACKAGE calc_pkg AS
    -- 重载函数
    FUNCTION calculate(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER;
    FUNCTION calculate(p_num1 NUMBER, p_num2 NUMBER, p_num3 NUMBER) RETURN NUMBER;
    FUNCTION calculate(p_nums SYS.ODCINUMBERLIST) RETURN NUMBER;
END calc_pkg;
/

CREATE OR REPLACE PACKAGE BODY calc_pkg AS
    FUNCTION calculate(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN p_num1 + p_num2;
    END;
    
    FUNCTION calculate(p_num1 NUMBER, p_num2 NUMBER, p_num3 NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN p_num1 + p_num2 + p_num3;
    END;
    
    FUNCTION calculate(p_nums SYS.ODCINUMBERLIST) RETURN NUMBER IS
        v_sum NUMBER := 0;
    BEGIN
        FOR i IN 1..p_nums.COUNT LOOP
            v_sum := v_sum + p_nums(i);
        END LOOP;
        RETURN v_sum;
    END;
END calc_pkg;
/

-- 调用
SELECT calc_pkg.calculate(1, 2) FROM DUAL;
SELECT calc_pkg.calculate(1, 2, 3) FROM DUAL;
SELECT calc_pkg.calculate(SYS.ODCINUMBERLIST(1, 2, 3, 4, 5)) FROM DUAL;

-- 包初始化部分
CREATE OR REPLACE PACKAGE init_pkg AS
    g_app_name VARCHAR2(100);
    g_start_time TIMESTAMP;
    
    PROCEDURE show_info;
END init_pkg;
/

CREATE OR REPLACE PACKAGE BODY init_pkg AS
    -- 初始化部分（在包首次使用时执行）
    BEGIN
        g_app_name := 'Employee Management System';
        g_start_time := SYSTIMESTAMP;
        
        DBMS_OUTPUT.PUT_LINE('Package initialized');
    END;
    
    PROCEDURE show_info IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Application: ' || g_app_name);
        DBMS_OUTPUT.PUT_LINE('Started at: ' || TO_CHAR(g_start_time, 'YYYY-MM-DD HH24:MI:SS'));
    END;
END init_pkg;
/

-- 首次调用时初始化
EXEC init_pkg.show_info;

-- 包中的游标
CREATE OR REPLACE PACKAGE cursor_pkg AS
    -- 定义游标规范
    CURSOR c_all_employees RETURN employees%ROWTYPE;
    
    -- 带参数的游标
    CURSOR c_emp_by_dept(p_dept_id NUMBER) RETURN employees%ROWTYPE;
    
    -- 游标变量类型
    TYPE t_emp_cursor IS REF CURSOR RETURN employees%ROWTYPE;
    
    -- 返回游标变量的函数
    FUNCTION get_emp_cursor(p_dept_id NUMBER) RETURN t_emp_cursor;
END cursor_pkg;
/

CREATE OR REPLACE PACKAGE BODY cursor_pkg AS
    -- 游标实现
    CURSOR c_all_employees RETURN employees%ROWTYPE IS
        SELECT * FROM employees;
    
    CURSOR c_emp_by_dept(p_dept_id NUMBER) RETURN employees%ROWTYPE IS
        SELECT * FROM employees WHERE department_id = p_dept_id;
    
    FUNCTION get_emp_cursor(p_dept_id NUMBER) RETURN t_emp_cursor IS
        v_cursor t_emp_cursor;
    BEGIN
        OPEN v_cursor FOR
            SELECT * FROM employees 
            WHERE department_id = p_dept_id;
        RETURN v_cursor;
    END;
END cursor_pkg;
/

-- 授予包执行权限
GRANT EXECUTE ON emp_pkg TO user_name;

-- 授予所有用户
GRANT EXECUTE ON emp_pkg TO PUBLIC;

-- 回收权限
REVOKE EXECUTE ON emp_pkg FROM user_name;

-- 定义者权限（默认）
CREATE OR REPLACE PACKAGE definer_pkg
    AUTHID DEFINER AS
    PROCEDURE show_user;
END definer_pkg;
/

CREATE OR REPLACE PACKAGE BODY definer_pkg AS
    PROCEDURE show_user IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Current user: ' || USER);
    END;
END definer_pkg;
/

-- 调用者权限
CREATE OR REPLACE PACKAGE invoker_pkg
    AUTHID CURRENT_USER AS
    PROCEDURE show_user;
END invoker_pkg;
/

CREATE OR REPLACE PACKAGE BODY invoker_pkg AS
    PROCEDURE show_user IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Current user: ' || USER);
    END;
END invoker_pkg;
/

-- 查看包规范
SELECT text FROM user_source 
WHERE name = 'EMP_PKG' AND type = 'PACKAGE'
ORDER BY line;

-- 查看包体
SELECT text FROM user_source 
WHERE name = 'EMP_PKG' AND type = 'PACKAGE BODY'
ORDER BY line;

-- 查看包信息
SELECT object_name, object_type, status, created
FROM user_objects
WHERE object_name = 'EMP_PKG';

-- 查看包中的过程和函数
SELECT object_name, procedure_name, argument_name, data_type, position
FROM user_arguments
WHERE package_name = 'EMP_PKG'
ORDER BY object_name, position;

-- 编译包规范
ALTER PACKAGE emp_pkg COMPILE PACKAGE;

-- 编译包规范
ALTER PACKAGE emp_pkg COMPILE SPECIFICATION;

-- 编译包体
ALTER PACKAGE emp_pkg COMPILE BODY;

-- 带调试信息编译
ALTER PACKAGE emp_pkg COMPILE DEBUG;

-- 删除整个包（规范和包体）
DROP PACKAGE emp_pkg;

-- 只删除包体
DROP PACKAGE BODY emp_pkg;

-- 删除不存在的包
DROP PACKAGE IF EXISTS emp_pkg;

-- DBMS_OUTPUT：输出信息
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
    DBMS_OUTPUT.PUT('Line 1');
    DBMS_OUTPUT.PUT('Line 2');
    DBMS_OUTPUT.NEW_LINE;
END;
/

-- 启用输出
SET SERVEROUTPUT ON SIZE UNLIMITED;

-- DBMS_SQL：动态SQL
DECLARE
    v_cursor NUMBER;
    v_sql VARCHAR2(1000);
    v_emp_id NUMBER := 100;
    v_name VARCHAR2(100);
    v_rows NUMBER;
BEGIN
    v_sql := 'SELECT first_name || '' '' || last_name FROM employees WHERE employee_id = :id';
    
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':id', v_emp_id);
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_name, 100);
    
    v_rows := DBMS_SQL.EXECUTE(v_cursor);
    
    IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_name);
        DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
    END IF;
    
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

-- DBMS_SCHEDULER：作业调度
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'daily_backup',
        job_type => 'STORED_PROCEDURE',
        job_action => 'backup_proc',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2',
        enabled => TRUE
    );
END;
/

-- UTL_FILE：文件操作
DECLARE
    v_file UTL_FILE.FILE_TYPE;
    v_line VARCHAR2(1000);
BEGIN
    v_file := UTL_FILE.FOPEN('DATA_DIR', 'output.txt', 'W');
    
    FOR emp_rec IN (SELECT first_name, last_name FROM employees) LOOP
        UTL_FILE.PUT_LINE(v_file, emp_rec.first_name || ' ' || emp_rec.last_name);
    END LOOP;
    
    UTL_FILE.FCLOSE(v_file);
END;
/

-- 员工管理包
CREATE OR REPLACE PACKAGE employee_mgmt AS
    -- 类型定义
    TYPE t_emp_list IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
    
    -- 异常定义
    e_employee_not_found EXCEPTION;
    e_invalid_operation EXCEPTION;
    
    -- 过程
    PROCEDURE add_employee(
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_email IN VARCHAR2,
        p_department_id IN NUMBER,
        p_salary IN NUMBER
    );
    
    PROCEDURE transfer_department(
        p_emp_id IN NUMBER,
        p_new_dept_id IN NUMBER
    );
    
    PROCEDURE terminate_employee(
        p_emp_id IN NUMBER
    );
    
    -- 函数
    FUNCTION get_employee(p_emp_id IN NUMBER) RETURN employees%ROWTYPE;
    FUNCTION get_department_employees(p_dept_id IN NUMBER) RETURN t_emp_list;
    FUNCTION calculate_annual_cost(p_dept_id IN NUMBER) RETURN NUMBER;
END employee_mgmt;
/

-- 工具函数包
CREATE OR REPLACE PACKAGE utils AS
    -- 字符串处理
    FUNCTION format_phone(p_phone VARCHAR2) RETURN VARCHAR2;
    FUNCTION mask_email(p_email VARCHAR2) RETURN VARCHAR2;
    
    -- 日期处理
    FUNCTION get_age(p_birth_date DATE) RETURN NUMBER;
    FUNCTION get_quarter(p_date DATE) RETURN NUMBER;
    
    -- 验证函数
    FUNCTION is_valid_email(p_email VARCHAR2) RETURN BOOLEAN;
    FUNCTION is_valid_phone(p_phone VARCHAR2) RETURN BOOLEAN;
END utils;
/

CREATE OR REPLACE PACKAGE BODY utils AS
    FUNCTION format_phone(p_phone VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
        RETURN REGEXP_REPLACE(
            REGEXP_REPLACE(p_phone, '[^0-9]', ''),
            '([0-9]{3})([0-9]{4})([0-9]{4})',
            '\1-\2-\3'
        );
    END;
    
    FUNCTION mask_email(p_email VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
        RETURN REGEXP_REPLACE(p_email, '(.)[^@]*(@)', '\1***\2');
    END;
    
    FUNCTION get_age(p_birth_date DATE) RETURN NUMBER IS
    BEGIN
        RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, p_birth_date) / 12);
    END;
    
    FUNCTION get_quarter(p_date DATE) RETURN NUMBER IS
    BEGIN
        RETURN TO_NUMBER(TO_CHAR(p_date, 'Q'));
    END;
    
    FUNCTION is_valid_email(p_email VARCHAR2) RETURN BOOLEAN IS
    BEGIN
        RETURN REGEXP_LIKE(p_email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');
    END;
    
    FUNCTION is_valid_phone(p_phone VARCHAR2) RETURN BOOLEAN IS
    BEGIN
        RETURN REGEXP_LIKE(p_phone, '^[0-9]{11}$');
    END;
END utils;
/