Oracle数据库PL/SQL 存储过程与函数完全指南

-- 存储过程语法
create [or replace] procedure 过程名 (
    参数1 [模式] 数据类型,
    参数2 [模式] 数据类型
) [authid {definer | current_user}] -- 权限模型
is|as
    -- 声明部分（变量、游标、类型）
    变量声明;
begin
    -- 执行部分
    可执行语句;
exception
    -- 异常处理部分
    异常处理;
end [过程名];
/
-- 函数语法
create [or replace] function 函数名 (
    参数1 [模式] 数据类型
) return 返回数据类型
is|as
    变量声明;
begin
    return 返回值;
exception
    异常处理;
end [函数名];
/

-- 场景：调整员工薪水并记录日志
create or replace procedure adjust_employee_salary (
    p_emp_id in number,           -- 员工id（输入参数）
    p_percent in number,          -- 调整百分比（输入参数）
    p_new_salary out number,      -- 新薪水（输出参数）
    p_message out varchar2        -- 消息（输出参数）
) authid definer
is
    -- 声明变量
    v_old_salary employees.salary%type;
    v_emp_name varchar2(100);
begin
    -- 1. 查询当前薪水
    select salary, first_name || ' ' || last_name
    into v_old_salary, v_emp_name
    from employees
    where employee_id = p_emp_id;
    -- 2. 计算新薪水
    p_new_salary := v_old_salary * (1 + p_percent / 100);
    -- 3. 更新薪水
    update employees
    set salary = p_new_salary
    where employee_id = p_emp_id;
    -- 4. 记录日志
    insert into salary_log (emp_id, old_salary, new_salary, change_date)
    values (p_emp_id, v_old_salary, p_new_salary, sysdate);
    -- 5. 设置返回消息
    p_message := '员工 ' || v_emp_name || ' 薪水已从 ' || v_old_salary || ' 调整为 ' || p_new_salary;
    -- 6. 提交事务
    commit;
exception
    when no_data_found then
        p_message := '错误：员工id ' || p_emp_id || ' 不存在';
        rollback;
    when others then
        p_message := '错误：' || sqlerrm;
        rollback;
end adjust_employee_salary;
/

-- 方式1：匿名块调用
declare
    v_new_salary number;
    v_message varchar2(200);
begin
    adjust_employee_salary(p_emp_id => 101, 
                           p_percent => 10, 
                           p_new_salary => v_new_salary, 
                           p_message => v_message);
    dbms_output.put_line(v_message);
    dbms_output.put_line('新薪水：' || v_new_salary);
end;
/
-- 方式2：execute 命令（sql*plus）
variable new_salary number
variable message varchar2(200)
exec adjust_employee_salary(101, 10, :new_salary, :message);
print new_salary
print message
-- 方式3：jdbc 调用（java）
callablestatement cstmt = conn.preparecall("{call adjust_employee_salary(?, ?, ?, ?)}");
cstmt.setint(1, 101);
cstmt.setdouble(2, 10);
cstmt.registeroutparameter(3, types.numeric);
cstmt.registeroutparameter(4, types.varchar);
cstmt.execute();
double newsalary = cstmt.getdouble(3);
string message = cstmt.getstring(4);

-- 输入参数，只读
create procedure process_order (
    p_order_id in number,      -- 输入订单id
    p_status out varchar2
) is
begin
    -- p_order_id 可被读取，但不能被修改
    update orders set status = 'processing' where order_id = p_order_id;
    p_status := '处理完成';
end;
/

-- 输出参数，用于返回值
create procedure get_employee_info (
    p_emp_id in number,
    p_name out varchar2,
    p_salary out number,
    p_hire_date out date
) is
begin
    select first_name || ' ' || last_name, salary, hire_date
    into p_name, p_salary, p_hire_date
    from employees
    where employee_id = p_emp_id;
exception
    when no_data_found then
        p_name := null;
        p_salary := null;
        p_hire_date := null;
end;
/

-- 输入输出参数，既可读也可写
create procedure swap_values (
    p_value1 in out number,
    p_value2 in out number
) is
    v_temp number;
begin
    v_temp := p_value1;
    p_value1 := p_value2;
    p_value2 := v_temp;
end;
/
-- 调用
declare
    a number := 10;
    b number := 20;
begin
    dbms_output.put_line('交换前：a=' || a || ', b=' || b);
    swap_values(a, b);
    dbms_output.put_line('交换后：a=' || a || ', b=' || b);
end;
/

create or replace procedure create_employee (
    p_first_name in varchar2,
    p_last_name in varchar2,
    p_salary in number default 5000,  -- 默认值
    p_department_id in number default 50
) is
begin
    insert into employees (employee_id, first_name, last_name, salary, department_id)
    values (emp_seq.nextval, p_first_name, p_last_name, p_salary, p_department_id);
    commit;
end;
/
-- 调用（使用默认值）
exec create_employee('john', 'doe');
-- 调用（覆盖默认值）
exec create_employee('jane', 'smith', 8000, 60);

-- 场景：根据员工id计算年薪（含奖金）
create or replace function calculate_annual_income (
    p_emp_id in number,
    p_include_bonus in boolean default true
) return number
is
    v_salary employees.salary%type;
    v_commission employees.commission_pct%type;
    v_annual_income number;
begin
    -- 查询薪水和提成比例
    select salary, commission_pct
    into v_salary, v_commission
    from employees
    where employee_id = p_emp_id;
    -- 计算年收入
    if p_include_bonus and v_commission is not null then
        v_annual_income := v_salary * 12 * (1 + v_commission);
    else
        v_annual_income := v_salary * 12;
    end if;
    return v_annual_income;
exception
    when no_data_found then
        return null;  -- 函数必须返回值
    when others then
        return -1;    -- 错误标识
end calculate_annual_income;
/

-- 方式1：在 sql 语句中调用（函数的核心优势）
select 
    employee_id,
    first_name,
    salary,
    calculate_annual_income(employee_id, true) as annual_income
from employees
where calculate_annual_income(employee_id) > 200000;
-- 方式2：在 pl/sql 块中调用
declare
    v_income number;
begin
    v_income := calculate_annual_income(101, false);
    dbms_output.put_line('年收入：' || v_income);
end;
/
-- 方式3：在 where 子句中调用
select * from employees
where calculate_annual_income(employee_id) > (select avg(salary*12) from employees);

-- 预定义异常
create or replace procedure safe_delete_employee (
    p_emp_id in number
) is
begin
    delete from employees where employee_id = p_emp_id;
    if sql%notfound then
        raise_application_error(-20001, '员工 ' || p_emp_id || ' 不存在');
    end if;
    commit;
exception
    when no_data_found then
        dbms_output.put_line('没有找到数据');
    when too_many_rows then
        dbms_output.put_line('返回多行，但期望单行');
    when others then
        dbms_output.put_line('错误代码：' || sqlcode);
        dbms_output.put_line('错误消息：' || sqlerrm);
        rollback;
end;
/
-- 自定义异常
create or replace procedure update_salary_check (
    p_emp_id in number,
    p_new_salary in number
) is
    e_salary_too_high exception;  -- 声明自定义异常
    pragma exception_init(e_salary_too_high, -20002);  -- 关联错误码
begin
    if p_new_salary > 20000 then
        raise e_salary_too_high;  -- 抛出自定义异常
    end if;
    update employees set salary = p_new_salary where employee_id = p_emp_id;
    commit;
exception
    when e_salary_too_high then
        dbms_output.put_line('错误：新薪资不能超过20000');
        rollback;
end;
/

-- 显式游标处理多行数据
create or replace procedure bulk_raise_salary (
    p_dept_id in number,
    p_percent in number
) is
    -- 声明游标
    cursor emp_cursor is
        select employee_id, salary from employees 
        where department_id = p_dept_id 
        for update;  -- 加锁防止并发修改
    -- 记录类型
    emp_rec emp_cursor%rowtype;
begin
    open emp_cursor;
    loop
        fetch emp_cursor into emp_rec;
        exit when emp_cursor%notfound;  -- 退出循环条件
        -- 更新薪水
        update employees 
        set salary = emp_rec.salary * (1 + p_percent/100)
        where current of emp_cursor;  -- 定位当前游标行
        dbms_output.put_line('员工 ' || emp_rec.employee_id || ' 已调整');
    end loop;
    close emp_cursor;
    commit;
exception
    when others then
        close emp_cursor;
        rollback;
        raise;
end;
/
-- 游标 for 循环（简化）
create or replace procedure process_high_earners is
begin
    for emp_rec in (select employee_id, salary from employees where salary > 10000)
    loop
        insert into high_earner_log values (emp_rec.employee_id, emp_rec.salary, sysdate);
    end loop;
    commit;
end;
/

-- 日志记录不受主事务影响
create or replace procedure log_message (
    p_message in varchar2
) is
    pragma autonomous_transaction;  -- 声明自治事务
begin
    insert into message_log (message, log_time) values (p_message, sysdate);
    commit;  -- 独立提交，不影响主事务
end;
/
-- 主事务回滚，但日志已提交
create or replace procedure main_transaction is
begin
    insert into orders values (101, 5000);
    log_message('订单 101 已创建');  -- 自治事务已提交
    rollback;  -- 订单被回滚，但日志保留
end;
/

-- 场景：动态表名查询
create or replace function dynamic_query (
    p_table_name in varchar2,
    p_id in number
) return varchar2
is
    v_sql varchar2(1000);
    v_result varchar2(100);
begin
    v_sql := 'select name from ' || p_table_name || ' where id = :id';
    execute immediate v_sql
    into v_result
    using p_id;  -- 绑定变量防止 sql 注入
    return v_result;
exception
    when others then
        return '查询失败：' || sqlerrm;
end;
/
-- 动态 ddl
create or replace procedure create_log_table (p_table_name in varchar2) is
begin
    execute immediate 'create table ' || p_table_name || '_log (
        id number generated always as identity,
        message varchar2(200),
        log_date timestamp default current_timestamp
    )';
end;
/

-- 1. 包规范（接口定义）
create or replace package employee_mgmt as
    -- 常量
    c_max_salary constant number := 50000;
    -- 类型定义
    type emp_rec_type is record (
        emp_id number,
        emp_name varchar2(100),
        salary number
    );
    type emp_tab_type is table of emp_rec_type index by pls_integer;
    -- 函数声明
    function calculate_bonus (p_emp_id in number) return number;
    function get_employee_info (p_emp_id in number) return emp_rec_type;
    -- 过程声明
    procedure hire_employee (
        p_first_name in varchar2,
        p_last_name in varchar2,
        p_salary in number
    );
    procedure fire_employee (p_emp_id in number);
end employee_mgmt;
/
-- 2. 包主体（实现）
create or replace package body employee_mgmt as
    -- 私有函数（外部不可见）
    function validate_salary (p_salary in number) return boolean is
    begin
        return p_salary between 1000 and c_max_salary;
    end validate_salary;
    -- 公有函数实现
    function calculate_bonus (p_emp_id in number) return number is
        v_salary employees.salary%type;
    begin
        select salary into v_salary from employees where employee_id = p_emp_id;
        return v_salary * 0.1;  -- 奖金为薪水的10%
    exception
        when no_data_found then
            return 0;
    end calculate_bonus;
    -- 存储过程实现
    procedure hire_employee (
        p_first_name in varchar2,
        p_last_name in varchar2,
        p_salary in number
    ) is
    begin
        if not validate_salary(p_salary) then
            raise_application_error(-20003, '薪资超出范围');
        end if;
        insert into employees (employee_id, first_name, last_name, salary, hire_date)
        values (emp_seq.nextval, p_first_name, p_last_name, p_salary, sysdate);
        commit;
    end hire_employee;
end employee_mgmt;
/

-- 调用包函数
select employee_mgmt.calculate_bonus(101) from dual;
-- 调用包过程
declare
    v_emp_info employee_mgmt.emp_rec_type;
begin
    v_emp_info := employee_mgmt.get_employee_info(102);
    dbms_output.put_line('姓名：' || v_emp_info.emp_name);
    employee_mgmt.hire_employee('alice', 'smith', 7500);
end;
/

-- 错误：逐行处理（慢）
create or replace procedure slow_update is
begin
    for emp in (select employee_id, salary from employees where department_id = 80)
    loop
        update employees set salary = salary * 1.1 where employee_id = emp.employee_id;
    end loop;
    commit;
end;
-- 正确：批量处理
create or replace procedure fast_update is
    type num_tab is table of number index by pls_integer;
    v_emp_ids num_tab;
    v_salaries num_tab;
begin
    -- 批量获取
    select employee_id, salary 
    bulk collect into v_emp_ids, v_salaries
    from employees where department_id = 80;
    -- 批量更新
    forall i in 1..v_emp_ids.count
        update employees set salary = v_salaries(i) * 1.1 
        where employee_id = v_emp_ids(i);
    commit;
end;

-- 对于大集合，使用 nocopy 避免拷贝
create or replace procedure process_large_collection (
    p_collection in out nocopy large_collection_type  -- nocopy 提示
) is
begin
    -- 直接操作原集合，不创建副本
    for i in 1..p_collection.count loop
        p_collection(i).status := 'processed';
    end loop;
end;

-- 错误：sql 和 pl/sql 频繁切换
create or replace function get_department_name (p_dept_id number) return varchar2 is
    v_name varchar2(100);
begin
    select department_name into v_name from departments where department_id = p_dept_id;
    return v_name;
end;
-- 查询时使用
select employee_id, get_department_name(department_id) from employees;  -- 低效
-- 正确：纯 sql 实现
select e.employee_id, d.department_name
from employees e join departments d on e.department_id = d.department_id;

set serveroutput on;  -- 开启输出
create or replace procedure debug_demo is
    v_counter number := 0;
begin
    for rec in (select employee_id from employees where rownum <= 5)
    loop
        v_counter := v_counter + 1;
        dbms_output.put_line('处理第 ' || v_counter || ' 个员工：' || rec.employee_id);
    end loop;
end;
/

-- 在 v$session 中显示进度
create or replace procedure long_running_task is
    v_total number;
begin
    select count(*) into v_total from employees;
    for rec in (select employee_id from employees)
    loop
        dbms_application_info.set_module(
            module_name => 'salary_update',
            action_name => 'processing ' || rec.employee_id
        );
        dbms_application_info.set_session_longops(
            rindex => dbms_application_info.set_session_longops_nohint,
            slno => 0,
            op_name => 'employee processing',
            sofar => rec.employee_id,
            totalwork => v_total,
            units => 'employees'
        );
        -- 业务逻辑
        update employees set salary = salary * 1.05 where employee_id = rec.employee_id;
    end loop;
end;
/
-- 监控查询
select sid, serial#, module, action from v$session where module = 'salary_update';
select * from v$session_longops where opname = 'employee processing';

-- 查看存储过程依赖的表
select referenced_owner, referenced_name, referenced_type
from all_dependencies
where owner = 'hr' 
and name = 'adjust_employee_salary'
and type = 'procedure'
order by referenced_type;
-- 查看哪些对象依赖该过程
select name, type
from all_dependencies
where referenced_owner = 'hr'
and referenced_name = 'adjust_employee_salary';

-- 前缀规范
- 存储过程：p_业务模块_操作（如 p_emp_update_salary）
- 函数：f_业务模块_计算（如 f_emp_calc_bonus）
- 包：pkg_业务模块（如 pkg_employee_mgmt）
- 参数：p_参数名（输入）、p_参数名_out（输出）、p_参数名_io（输入输出）
- 变量：v_变量名（局部）、g_变量名（全局包变量）

-- 1. 总是使用 authid 明确权限
create or replace procedure secure_proc(...) is
    authid current_user  -- 调用者权限
is
begin
    ...
end;
/
-- 2. 参数使用 %type 锚定
create or replace procedure update_emp (
    p_emp_id in employees.employee_id%type,  -- 类型自动同步
    p_salary in employees.salary%type
) is ...
-- 3. 使用显式游标而非隐式
-- 错误：隐式游标无法处理 no_data_found
select ... into ...;  -- 不推荐
-- 正确：显式游标控制
declare
    cursor c_emp is select ...;
begin
    open c_emp;
    loop
        fetch c_emp into ...;
        exit when c_emp%notfound;
    end loop;
    close c_emp;
end;
-- 4. 异常处理精细化
exception
    when no_data_found then
        -- 处理查询不到数据
    when dup_val_on_index then
        -- 处理唯一键冲突
    when others then
        -- 记录错误日志后重新抛出
        log_error(sqlcode, sqlerrm);
        raise;  -- 重新抛出，让上层调用者处理