PL/SQL存储过程 #

存储过程特点
├── 命名存储
│   └── 存储在数据库中
├── 可重用
│   └── 多次调用
├── 参数传递
│   └── IN, OUT, IN OUT
├── 事务控制
│   └── COMMIT, ROLLBACK
└── 权限管理
    └── 可授权执行权限

-- 1. 减少网络流量
-- 应用程序只需调用过程名，而非发送大量SQL

-- 2. 代码重用
-- 多个应用可以共享同一过程

-- 3. 安全性
-- 可以限制用户只能通过过程访问数据

-- 4. 性能优化
-- 过程在数据库中编译，执行效率高

-- 5. 维护方便
-- 修改过程不影响应用程序

-- 创建存储过程基本语法
CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter1 [IN|OUT|IN OUT] datatype [, parameter2 ...])]
    [AUTHID DEFINER|CURRENT_USER]
AS|IS
    -- 声明部分
BEGIN
    -- 执行部分
EXCEPTION
    -- 异常处理
END [procedure_name];

-- 创建无参数过程
CREATE OR REPLACE PROCEDURE hello_world AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/

-- 调用过程
EXEC hello_world;

-- 或
BEGIN
    hello_world;
END;
/

-- 创建带注释的过程
CREATE OR REPLACE PROCEDURE hello_world AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World!');
END hello_world;
/

-- IN参数
CREATE OR REPLACE PROCEDURE greet_user(
    p_name IN VARCHAR2
) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;
/

-- 调用
EXEC greet_user('John');

-- OUT参数
CREATE OR REPLACE PROCEDURE get_employee_name(
    p_emp_id IN NUMBER,
    p_name OUT VARCHAR2
) AS
BEGIN
    SELECT first_name || ' ' || last_name
    INTO p_name
    FROM employees
    WHERE employee_id = p_emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name := NULL;
END;
/

-- 调用带OUT参数的过程
DECLARE
    v_name VARCHAR2(100);
BEGIN
    get_employee_name(100, v_name);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
/

-- IN OUT参数
CREATE OR REPLACE PROCEDURE double_value(
    p_num IN OUT NUMBER
) AS
BEGIN
    p_num := p_num * 2;
END;
/

-- 调用
DECLARE
    v_value NUMBER := 10;
BEGIN
    double_value(v_value);
    DBMS_OUTPUT.PUT_LINE('Doubled: ' || v_value);  -- 20
END;
/

-- 参数默认值
CREATE OR REPLACE PROCEDURE greet_user(
    p_name IN VARCHAR2,
    p_greeting IN VARCHAR2 DEFAULT 'Hello'
) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE(p_greeting || ', ' || p_name || '!');
END;
/

-- 调用方式
EXEC greet_user('John');                           -- Hello, John!
EXEC greet_user('John', 'Hi');                     -- Hi, John!
EXEC greet_user(p_name => 'John');                 -- Hello, John!
EXEC greet_user(p_name => 'John', p_greeting => 'Welcome');  -- Welcome, John!

-- IN参数：只读，不能在过程中修改
CREATE OR REPLACE PROCEDURE process_employee(
    p_emp_id IN NUMBER
) AS
    v_name VARCHAR2(100);
BEGIN
    -- p_emp_id := 100;  -- 错误：不能修改IN参数
    
    SELECT first_name INTO v_name
    FROM employees
    WHERE employee_id = p_emp_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END;
/

-- IN参数特点
-- 1. 默认模式
-- 2. 只读，不能修改
-- 3. 可以是字面值、变量或表达式

-- OUT参数：只写，用于返回值
CREATE OR REPLACE PROCEDURE get_employee_info(
    p_emp_id IN NUMBER,
    p_name OUT VARCHAR2,
    p_salary OUT NUMBER,
    p_dept_name OUT VARCHAR2
) AS
BEGIN
    SELECT 
        e.first_name || ' ' || e.last_name,
        e.salary,
        d.department_name
    INTO p_name, p_salary, p_dept_name
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    WHERE e.employee_id = p_emp_id;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name := NULL;
        p_salary := NULL;
        p_dept_name := NULL;
END;
/

-- 调用
DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
    v_dept_name VARCHAR2(50);
BEGIN
    get_employee_info(100, v_name, v_salary, v_dept_name);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
    DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name);
END;
/

-- OUT参数特点
-- 1. 初始值为NULL
-- 2. 必须是变量
-- 3. 用于返回值

-- IN OUT参数：可读写
CREATE OR REPLACE PROCEDURE format_phone(
    p_phone IN OUT VARCHAR2
) AS
BEGIN
    -- 移除所有非数字字符
    p_phone := REGEXP_REPLACE(p_phone, '[^0-9]', '');
    
    -- 格式化
    IF LENGTH(p_phone) = 11 THEN
        p_phone := SUBSTR(p_phone, 1, 3) || '-' || 
                   SUBSTR(p_phone, 4, 4) || '-' || 
                   SUBSTR(p_phone, 8);
    END IF;
END;
/

-- 调用
DECLARE
    v_phone VARCHAR2(20) := '13812345678';
BEGIN
    format_phone(v_phone);
    DBMS_OUTPUT.PUT_LINE('Formatted: ' || v_phone);  -- 138-1234-5678
END;
/

-- IN OUT参数特点
-- 1. 既是输入也是输出
-- 2. 必须是变量
-- 3. 可以读取和修改

-- 1. EXEC命令（SQL*Plus）
EXEC hello_world;

-- 2. 匿名块
BEGIN
    hello_world;
END;
/

-- 3. CALL语句
CALL hello_world();

-- 4. 位置参数
EXEC greet_user('John', 'Hi');

-- 5. 命名参数
EXEC greet_user(p_name => 'John', p_greeting => 'Hi');
EXEC greet_user(p_greeting => 'Hi', p_name => 'John');  -- 顺序可变

-- 6. 混合参数
EXEC greet_user('John', p_greeting => 'Hi');

-- 在过程中调用其他过程
CREATE OR REPLACE PROCEDURE process_new_employee(
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2,
    p_department_id IN NUMBER
) AS
    v_emp_id NUMBER;
BEGIN
    -- 调用其他过程
    generate_employee_id(v_emp_id);
    
    -- 插入员工
    INSERT INTO employees (employee_id, first_name, last_name, department_id, hire_date)
    VALUES (v_emp_id, p_first_name, p_last_name, p_department_id, SYSDATE);
    
    -- 调用日志过程
    log_action('INSERT', 'employees', v_emp_id);
    
    COMMIT;
END;
/

-- 查看过程源代码
SELECT text FROM user_source 
WHERE name = 'HELLO_WORLD' AND type = 'PROCEDURE'
ORDER BY line;

-- 查看过程信息
SELECT object_name, status, created, last_ddl_time
FROM user_objects
WHERE object_type = 'PROCEDURE';

-- 查看过程参数
SELECT argument_name, position, data_type, in_out
FROM user_arguments
WHERE object_name = 'GET_EMPLOYEE_INFO'
ORDER BY position;

-- 使用CREATE OR REPLACE修改
CREATE OR REPLACE PROCEDURE hello_world AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello PL/SQL World!');
END;
/

-- 编译过程
ALTER PROCEDURE hello_world COMPILE;

-- 带调试信息编译
ALTER PROCEDURE hello_world COMPILE DEBUG;

-- 删除过程
DROP PROCEDURE hello_world;

-- 删除不存在的过程（避免错误）
DROP PROCEDURE IF EXISTS hello_world;

-- 查看过程依赖
SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'GET_EMPLOYEE_INFO' AND type = 'PROCEDURE';

-- 查看依赖过程的对象
SELECT name, type
FROM user_dependencies
WHERE referenced_name = 'EMPLOYEES';

-- 重新编译无效过程
SELECT object_name 
FROM user_objects 
WHERE object_type = 'PROCEDURE' AND status = 'INVALID';

-- 批量编译
BEGIN
    FOR rec IN (SELECT object_name FROM user_objects 
                WHERE object_type = 'PROCEDURE' AND status = 'INVALID') 
    LOOP
        EXECUTE IMMEDIATE 'ALTER PROCEDURE ' || rec.object_name || ' COMPILE';
    END LOOP;
END;
/

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

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

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

-- 查看授权
SELECT grantee, table_name, privilege
FROM user_tab_privs
WHERE table_name = 'HELLO_WORLD';

-- 定义者权限（默认）
-- 过程以定义者权限执行
CREATE OR REPLACE PROCEDURE definer_proc
    AUTHID DEFINER AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

-- 调用者权限
-- 过程以调用者权限执行
CREATE OR REPLACE PROCEDURE invoker_proc
    AUTHID CURRENT_USER AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

-- 区别
-- DEFINER：使用过程所有者的权限和模式
-- CURRENT_USER：使用调用者的权限和模式

-- 过程命名规范
-- 1. 使用动词开头
-- 2. 描述性命名
-- 3. 使用下划线分隔

-- 好的命名
CREATE OR REPLACE PROCEDURE insert_employee(...) AS
CREATE OR REPLACE PROCEDURE update_employee_salary(...) AS
CREATE OR REPLACE PROCEDURE delete_employee(...) AS
CREATE OR REPLACE PROCEDURE get_employee_info(...) AS
CREATE OR REPLACE PROCEDURE calculate_bonus(...) AS

-- 参数命名
-- p_ 前缀表示参数
CREATE OR REPLACE PROCEDURE update_salary(
    p_emp_id IN NUMBER,
    p_new_salary IN NUMBER,
    p_old_salary OUT NUMBER
) AS

-- 完善的错误处理
CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_account IN NUMBER,
    p_to_account IN NUMBER,
    p_amount IN NUMBER
) AS
    v_balance NUMBER;
    e_insufficient_funds EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_insufficient_funds, -20001);
BEGIN
    -- 检查余额
    SELECT balance INTO v_balance
    FROM accounts
    WHERE account_id = p_from_account;
    
    IF v_balance < p_amount THEN
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
    END IF;
    
    -- 转账
    UPDATE accounts SET balance = balance - p_amount
    WHERE account_id = p_from_account;
    
    UPDATE accounts SET balance = balance + p_amount
    WHERE account_id = p_to_account;
    
    COMMIT;
    
EXCEPTION
    WHEN e_insufficient_funds THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20002, 'Account not found');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20999, 'Transfer failed: ' || SQLERRM);
END;
/

-- 过程日志记录
CREATE OR REPLACE PROCEDURE log_procedure_call(
    p_proc_name IN VARCHAR2,
    p_params IN VARCHAR2,
    p_status IN VARCHAR2,
    p_message IN VARCHAR2 DEFAULT NULL
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO procedure_log (
        log_id, procedure_name, parameters, status, message, log_time
    ) VALUES (
        seq_log_id.NEXTVAL, p_proc_name, p_params, p_status, p_message, SYSTIMESTAMP
    );
    COMMIT;
END;
/

-- 使用日志
CREATE OR REPLACE PROCEDURE update_employee_salary(
    p_emp_id IN NUMBER,
    p_new_salary IN NUMBER
) AS
BEGIN
    log_procedure_call('update_employee_salary', 
                       'emp_id=' || p_emp_id || ',salary=' || p_new_salary, 
                       'START');
    
    UPDATE employees SET salary = p_new_salary
    WHERE employee_id = p_emp_id;
    
    COMMIT;
    
    log_procedure_call('update_employee_salary', 
                       'emp_id=' || p_emp_id, 
                       'SUCCESS');
EXCEPTION
    WHEN OTHERS THEN
        log_procedure_call('update_employee_salary', 
                           'emp_id=' || p_emp_id, 
                           'ERROR', SQLERRM);
        RAISE;
END;
/

-- 批量更新薪资
CREATE OR REPLACE PROCEDURE batch_update_salary(
    p_dept_id IN NUMBER,
    p_increase_pct IN NUMBER
) AS
    CURSOR c_emp IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = p_dept_id
        FOR UPDATE;
    
    v_count NUMBER := 0;
BEGIN
    FOR emp_rec IN c_emp LOOP
        UPDATE employees
        SET salary = salary * (1 + p_increase_pct / 100)
        WHERE CURRENT OF c_emp;
        
        v_count := v_count + 1;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE(v_count || ' employees updated');
END;
/

-- 数据验证过程
CREATE OR REPLACE PROCEDURE validate_employee(
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2,
    p_email IN VARCHAR2,
    p_salary IN NUMBER,
    p_valid OUT BOOLEAN,
    p_message OUT VARCHAR2
) AS
BEGIN
    p_valid := TRUE;
    p_message := NULL;
    
    -- 验证姓名
    IF p_first_name IS NULL OR LENGTH(p_first_name) < 2 THEN
        p_valid := FALSE;
        p_message := 'Invalid first name';
        RETURN;
    END IF;
    
    -- 验证邮箱
    IF NOT REGEXP_LIKE(p_email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') THEN
        p_valid := FALSE;
        p_message := 'Invalid email format';
        RETURN;
    END IF;
    
    -- 验证薪资
    IF p_salary < 0 THEN
        p_valid := FALSE;
        p_message := 'Salary cannot be negative';
        RETURN;
    END IF;
END;
/