- PL/SQL 程序的基本单位是“块”（Block），所有 PL/SQL 代码都由一个或多个块组成。每个块都有明确的结构：
```
[DECLARE]
    -- 声明部分：定义变量、常量、游标、用户自定义类型等
    -- 可选部分
BEGIN
    -- 执行部分：必须存在，包含要执行的语句（SQL 或 PL/SQL）
EXCEPTION
    -- 异常处理部分：可选，用于捕获和处理运行时错误
[END;]
```
    

用于声明变量、常量、游标、记录类型等

 
```
DECLARE
    v_name VARCHAR2(50);                     -- 变量
    v_age  NUMBER := 25;                     -- 初始化赋值
    v_salary CONSTANT NUMBER := 8000;        -- 常量
    v_is_active BOOLEAN := TRUE;
    v_start_date DATE := SYSDATE;

    -- 游标声明
    CURSOR emp_cursor IS
        SELECT employee_id, first_name FROM employees WHERE department_id = 10;

    -- 记录类型（类似结构体）
    TYPE t_emp_rec IS RECORD (
        emp_id    employees.employee_id%TYPE,
        emp_name  employees.first_name%TYPE,
        hire_date employees.hire_date%TYPE
    );
    v_emp_record t_emp_rec;
```
- %TYPE 是一个非常有用的属性，表示“使用某列的数据类型”，避免硬编码类型。

这是块的核心，包含要执行的逻辑

 
```
BEGIN
    -- 赋值
    v_name := 'John Doe';

    -- SQL 操作
    SELECT first_name INTO v_name
    FROM employees
    WHERE employee_id = 100;

    -- 控制结构：IF 条件判断
    IF v_age > 18 THEN
        DBMS_OUTPUT.PUT_LINE('Adult');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Minor');
    END IF;

    -- 循环
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Loop ' || i);
    END LOOP;

    -- 插入数据
    INSERT INTO temp_log (msg, created_date)
    VALUES ('Process started', SYSDATE);

END;
```
- 在 PL/SQL 中，字符串连接用 ||

用于捕获和处理运行时异常（如除零、记录未找到等）

 
```
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('发生未知错误: ' || SQLERRM);
        RAISE;  -- 可选择重新抛出异常
END;

```
  

 
- 常见预定义异常：
    - NO_DATA_FOUND：SELECT INTO 没有返回任何行
    - TOO_MANY_ROWS：SELECT INTO 返回多行
    - DUP_VAL_ON_INDEX：唯一约束冲突
    - ZERO_DIVIDE：除以零
    - VALUE_ERROR：转换错误（如字符串转数字失败）

 
```
DECLARE
    v_employee_name VARCHAR2(100);
    v_salary        NUMBER := 5000;
    v_bonus         NUMBER;
BEGIN
    -- 根据薪资计算奖金
    IF v_salary > 8000 THEN
        v_bonus := v_salary * 0.2;
    ELSIF v_salary > 5000 THEN
        v_bonus := v_salary * 0.1;
    ELSE
        v_bonus := v_salary * 0.05;
    END IF;

    -- 查询员工姓名
    SELECT first_name || ' ' || last_name
    INTO v_employee_name
    FROM employees
    WHERE employee_id = 100;

    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('奖金: ' || v_bonus);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到该员工');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/
```

没有名称，不能存储在数据库中，一次性执行。

 
```
DECLARE
    -- 变量声明
    v_employee_name VARCHAR2(50);
    v_salary NUMBER(10,2);
    v_hire_date DATE := SYSDATE;
    v_bonus CONSTANT NUMBER := 1000; -- 常量
    
BEGIN
    -- 执行语句
    SELECT first_name, salary 
    INTO v_employee_name, v_salary 
    FROM employees 
    WHERE employee_id = 100;
    
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('薪资: ' || v_salary);
    DBMS_OUTPUT.PUT_LINE('入职日期: ' || v_hire_date);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('没有找到员工记录');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLERRM);
END;
/
```
    

 
```
DECLARE
    -- 基本数据类型
    v_number NUMBER(10,2) := 1000.50;
    v_varchar VARCHAR2(100) := 'Hello World';
    v_date DATE := SYSDATE;
    v_boolean BOOLEAN := TRUE;
    
    -- 使用 %TYPE 引用表列类型
    v_emp_name employees.first_name%TYPE;
    v_emp_salary employees.salary%TYPE;
    
    -- 使用 %ROWTYPE 引用整行类型
    v_employee employees%ROWTYPE;
    
    -- 记录类型
    TYPE t_employee IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(100),
        salary NUMBER
    );
    v_emp_record t_employee;
    
BEGIN
    -- 使用 %TYPE 变量
    SELECT first_name, salary 
    INTO v_emp_name, v_emp_salary
    FROM employees 
    WHERE employee_id = 100;
    
    -- 使用 %ROWTYPE 变量
    SELECT * INTO v_employee 
    FROM employees 
    WHERE employee_id = 100;
    
    -- 使用记录类型
    v_emp_record.emp_id := 100;
    v_emp_record.emp_name := 'John Doe';
    v_emp_record.salary := 5000;
    
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_employee.first_name);
END;
/
```
    

条件语句

 
- IF-THEN-ELSE
```
DECLARE
    v_salary NUMBER := 5000;
    v_grade VARCHAR2(10);
BEGIN
    IF v_salary > 10000 THEN
        v_grade := 'A';
    ELSIF v_salary > 5000 THEN
        v_grade := 'B';
    ELSE
        v_grade := 'C';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('薪资等级: ' || v_grade);
END;
/
```

 
- CASE 语句
```
DECLARE
    v_department_id NUMBER := 60;
    v_department_name VARCHAR2(50);
BEGIN
    CASE v_department_id
        WHEN 10 THEN v_department_name := 'Administration';
        WHEN 20 THEN v_department_name := 'Marketing';
        WHEN 50 THEN v_department_name := 'Shipping';
        WHEN 60 THEN v_department_name := 'IT';
        ELSE v_department_name := 'Other';
    END CASE;
    
    DBMS_OUTPUT.PUT_LINE('部门: ' || v_department_name);
END;
/
```

循环语句

 
- 基本 LOOP
```
DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('计数器: ' || v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 5;
    END LOOP;
END;
/
```

 
- WHILE LOOP
```
DECLARE
    v_counter NUMBER := 1;
BEGIN
    WHILE v_counter <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('计数器: ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;
/
```

 
- FOR LOOP
```
BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('循环次数: ' || i);
    END LOOP;
    
    -- 反向循环
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('反向循环: ' || i);
    END LOOP;
END;
/
```

隐式游标

 
- 游标是一个数据库对象，用于在 PL/SQL 中处理多行查询结果。
  - 它允许你逐行处理查询返回的数据。
```
DECLARE
    v_emp_name employees.first_name%TYPE;
    v_count NUMBER;
BEGIN
    -- 单行查询（隐式游标）
    SELECT first_name INTO v_emp_name 
    FROM employees WHERE employee_id = 100;
    
    -- DML 操作（隐式游标）
    UPDATE employees SET salary = salary * 1.1 
    WHERE department_id = 50;
    
    -- 获取受影响的行数
    v_count := SQL%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE('更新了 ' || v_count || ' 行');
END;
/
```

    

 
- SQL%FOUND：如果影响了至少一行返回 TRUE
- SQL%NOTFOUND：如果没有影响任何行返回 TRUE
- SQL%ROWCOUNT：返回受影响的行数
- SQL%ISOPEN：对于隐式游标总是返回 FALSE
    

声明游标

 
```
DECLARE
    CURSOR cursor_name IS
        SELECT column1, column2, ...
        FROM table_name
        WHERE conditions;
```
    

 
- 打开游标
OPEN cursor_name;

- 提取数据
FETCH cursor_name INTO variable1, variable2, ...;

- 关闭游标
CLOSE cursor_name;
    

显式游标的完整示例

 
```
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 50;
        
    v_emp_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    -- 打开游标
    OPEN emp_cursor;
    
    -- 循环提取数据
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;  -- 如果没有更多数据则退出
        
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || v_emp_id || 
            ', 姓名: ' || v_first_name || ' ' || v_last_name ||
            ', 工资: ' || v_salary
        );
    END LOOP;
    
    -- 关闭游标
    CLOSE emp_cursor;
END;
/
```
    

 
```
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 50;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || emp_rec.employee_id || 
            ', 姓名: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||
            ', 工资: ' || emp_rec.salary
        );
    END LOOP;
END;
/

```
  

更简化的版本（直接在循环中声明）

 
```
BEGIN
    FOR emp_rec IN (
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 50
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || emp_rec.employee_id || 
            ', 姓名: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||
            ', 工资: ' || emp_rec.salary
        );
        
        -- 可以在这里进行业务逻辑处理
        IF emp_rec.salary < 3000 THEN
            DBMS_OUTPUT.PUT_LINE('需要调整工资');
        END IF;
    END LOOP;
END;
/

```
  

 
```
DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER, p_min_salary NUMBER) IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = p_dept_id
        AND salary >= p_min_salary;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- 部门 50，工资 >= 3000 ---');
    FOR emp_rec IN emp_cursor(50, 3000) LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp_rec.first_name || ' ' || emp_rec.last_name || 
            ' - 工资: ' || emp_rec.salary
        );
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('--- 部门 60，工资 >= 5000 ---');
    FOR emp_rec IN emp_cursor(60, 5000) LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp_rec.first_name || ' ' || emp_rec.last_name || 
            ' - 工资: ' || emp_rec.salary
        );
    END LOOP;
END;
/
```

 
```
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name FROM employees
        WHERE department_id = 50;
        
    v_emp_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_count NUMBER := 0;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('处理第 ' || v_count || ' 条记录: ' || v_first_name);
    END LOOP;
    
    -- 使用游标属性
    IF emp_cursor%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('游标已打开');
    ELSE
        DBMS_OUTPUT.PUT_LINE('游标已关闭');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('总共处理了 ' || emp_cursor%ROWCOUNT || ' 条记录');
    DBMS_OUTPUT.PUT_LINE('游标找到数据: ' || CASE WHEN emp_cursor%FOUND THEN '是' ELSE '否' END);
    
    CLOSE emp_cursor;
END;
/
```

 
- cursor_name%FOUND：如果最后一次 FETCH 返回一行则为 TRUE
- cursor_name%NOTFOUND：如果最后一次 FETCH 没有返回行则为 TRUE   
- cursor_name%ROWCOUNT：返回到目前为止提取的行数   
- cursor_name%ISOPEN：如果游标已打开则为 TRUE

FOR UPDATE 游标

 
```
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 50
        FOR UPDATE;  -- 锁定选中的行
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        -- 更新工资（增加10%）
        UPDATE employees
        SET salary = salary * 1.1
        WHERE CURRENT OF emp_cursor;  -- 更新当前游标指向的行
        
        DBMS_OUTPUT.PUT_LINE(
            '更新员工 ' || emp_rec.employee_id || 
            ' 的工资: ' || emp_rec.salary || ' -> ' || (emp_rec.salary * 1.1)
        );
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('所有更新已完成');
END;
/
```
- WHERE CURRENT OF 子句
  - 用于更新或删除游标当前指向的行。

强类型 REF CURSOR

 
```
DECLARE
    TYPE emp_ref_cursor IS REF CURSOR RETURN employees%ROWTYPE;
    emp_cursor emp_ref_cursor;
    emp_rec employees%ROWTYPE;
BEGIN
    OPEN emp_cursor FOR
        SELECT * FROM employees WHERE department_id = 50;
    
    LOOP
        FETCH emp_cursor INTO emp_rec;
        EXIT WHEN emp_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
    END LOOP;
    
    CLOSE emp_cursor;
END;
/
```

弱类型 REF CURSOR

 
```
DECLARE
    TYPE generic_ref_cursor IS REF CURSOR;
    my_cursor generic_ref_cursor;
    
    v_emp_id employees.employee_id%TYPE;
    v_emp_name employees.first_name%TYPE;
    v_dept_name departments.department_name%TYPE;
BEGIN
    -- 第一个查询
    OPEN my_cursor FOR
        SELECT employee_id, first_name FROM employees WHERE salary > 5000;
    
    DBMS_OUTPUT.PUT_LINE('--- 高工资员工 ---');
    LOOP
        FETCH my_cursor INTO v_emp_id, v_emp_name;
        EXIT WHEN my_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_emp_name);
    END LOOP;
    CLOSE my_cursor;
    
    -- 第二个查询（相同的游标变量）
    OPEN my_cursor FOR
        SELECT department_name FROM departments;
    
    DBMS_OUTPUT.PUT_LINE('--- 所有部门 ---');
    LOOP
        FETCH my_cursor INTO v_dept_name;
        EXIT WHEN my_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_dept_name);
    END LOOP;
    CLOSE my_cursor;
END;
/
```

BULK COLLECT

 
```
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 50;
    
    TYPE emp_table_type IS TABLE OF emp_cursor%ROWTYPE;
    emp_table emp_table_type;
BEGIN
    OPEN emp_cursor;
    
    -- 一次性获取所有数据
    FETCH emp_cursor BULK COLLECT INTO emp_table;
    CLOSE emp_cursor;
    
    -- 处理数据
    FOR i IN 1..emp_table.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp_table(i).first_name || ' ' || 
            emp_table(i).last_name || ' - ' || 
            emp_table(i).salary
        );
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('总共处理了 ' || emp_table.COUNT || ' 条记录');
END;
/
```

游标最佳实践

- 尽量使用 FOR 循环：自动处理打开、关闭和异常
- 及时关闭游标：避免资源泄露
- 使用 BULK COLLECT：处理大量数据时提高性能
- 使用参数化游标：提高代码复用性
- 合理使用 FOR UPDATE：只在需要更新数据时使用；

性能优化示例

```
DECLARE
    CURSOR emp_cursor IS SELECT * FROM employees;
    TYPE emp_array IS TABLE OF employees%ROWTYPE;
    emp_data emp_array;
BEGIN
    OPEN emp_cursor;
    
    -- 每次处理100条记录
    LOOP
        FETCH emp_cursor BULK COLLECT INTO emp_data LIMIT 100;
        EXIT WHEN emp_data.COUNT = 0;
        
        -- 批量处理逻辑
        FOR i IN 1..emp_data.COUNT LOOP
            -- 处理每条记录
            NULL;
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('处理了 ' || emp_data.COUNT || ' 条记录');
    END LOOP;
    
    CLOSE emp_cursor;
END;
/
```

使用游标处理多行数据

```
CREATE OR REPLACE PROCEDURE process_employees(
    p_department_id IN employees.department_id%TYPE
)
IS
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = p_department_id;
        
    v_bonus NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('处理部门 ' || p_department_id || ' 的员工:');
    
    FOR emp_rec IN emp_cursor LOOP
        -- 计算奖金（工资的10%）
        v_bonus := emp_rec.salary * 0.1;
        
        DBMS_OUTPUT.PUT_LINE(
            emp_rec.first_name || ' ' || emp_rec.last_name || 
            ' - 工资: ' || emp_rec.salary || 
            ', 奖金: ' || v_bonus
        );
        
        -- 可以在这里添加更新操作等
        -- UPDATE employees SET bonus = v_bonus WHERE employee_id = emp_rec.employee_id;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('处理完成');
END process_employees;
/
```

存储过程是命名的、可重复使用的 PL/SQL 块，存储在数据库中，可以通过应用程序调用

 
```
CREATE [OR REPLACE] PROCEDURE procedure_name (
    parameter1 IN/OUT/IN OUT datatype [DEFAULT value],
    parameter2 IN/OUT/IN OUT datatype [DEFAULT value],
    ...
) IS
    -- 局部声明（变量、游标等）
BEGIN
    -- 执行逻辑
EXCEPTION
    -- 异常处理
END procedure_name;
```
    

 

- IN	    
  - 输入参数，默认模式，只能读取，不能修改
- OUT	    
  - 输出参数，用于返回值给调用者
- IN OUT	
  - 既可输入又可输出


    

 
```
CREATE OR REPLACE PROCEDURE raise_salary (
    p_employee_id IN employees.employee_id%TYPE,
    p_percent     IN NUMBER DEFAULT 10  -- 默认加薪 10%
) IS
    v_current_salary employees.salary%TYPE;
BEGIN
    -- 获取当前薪资
    SELECT salary INTO v_current_salary
    FROM employees
    WHERE employee_id = p_employee_id
      FOR UPDATE;  -- 锁定该行，防止并发修改

    -- 更新薪资
    UPDATE employees
    SET salary = salary * (1 + p_percent / 100)
    WHERE employee_id = p_employee_id;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('员工 ' || p_employee_id || ' 薪资已上调 ' || p_percent || '%');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('员工不存在');
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END raise_salary;
/
```
    

调用

 
```
-- 方法1：EXEC（适用于无 OUT 参数）
EXEC raise_salary(100, 15);

-- 方法2：BEGIN...END;
BEGIN
    raise_salary(p_employee_id => 100, p_percent => 15);
END;
/

```
    

 
```
CREATE OR REPLACE PROCEDURE get_employee_info (
    p_employee_id   IN  employees.employee_id%TYPE,
    p_full_name     OUT VARCHAR2,
    p_dept_name     OUT VARCHAR2,
    p_hire_date     OUT DATE
) IS
BEGIN
    SELECT e.first_name || ' ' || e.last_name,
           d.department_name,
           e.hire_date
    INTO p_full_name, p_dept_name, p_hire_date
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE e.employee_id = p_employee_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_full_name := NULL;
        p_dept_name := NULL;
        p_hire_date := NULL;
END get_employee_info;
/
```

调用方式（需声明变量接收 OUT 值）

 
```
DECLARE
    v_name     VARCHAR2(100);
    v_dept     VARCHAR2(50);
    v_hiredate DATE;
BEGIN
    get_employee_info(100, v_name, v_dept, v_hiredate);
    
    DBMS_OUTPUT.PUT_LINE('姓名: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('部门: ' || v_dept);
    DBMS_OUTPUT.PUT_LINE('入职日期: ' || v_hiredate);
END;
/

```

 
- 用于输出调试信息（类似 print）：
```
DBMS_OUTPUT.PUT_LINE('当前值: ' || v_counter);
```
  
- DBMS_LOCK.SLEEP(seconds) —— 延迟
```
DBMS_LOCK.SLEEP(2); -- 暂停2秒
```

重新编译存储过程

 
```
ALTER PROCEDURE proc_create_trans COMPILE;
```

检查状态

 
```
-- 再次检查状态
SELECT object_name, status 
FROM user_objects 
WHERE object_name = 'PROC_CREATE_TRANS';
```

诊断存储过程状态的方法

 
```
-- 1. 检查存储过程状态
SELECT object_name, object_type, status, created, last_ddl_time
FROM user_objects 
WHERE object_name = 'PROC_CREATE_TRANS';

-- 2. 查看编译错误
SELECT line, position, text
FROM user_errors
WHERE name = 'PROC_CREATE_TRANS'
ORDER BY line;

-- 3. 重新编译
ALTER PROCEDURE proc_create_trans COMPILE;

-- 4. 授予必要权限（如果需要）
GRANT CREATE ANY TABLE TO AI_AML_DEV;
GRANT DROP ANY TABLE TO AI_AML_DEV;

```

 
- DROP PROCEDURE [schema.]procedure_name;

- 删除当前用户下的存储过程
  - DROP PROCEDURE proc_create_trans;

```
-- Oracle 18c 及以上版本支持
DROP PROCEDURE IF EXISTS proc_create_trans;
```

在 Oracle 18c 之前的版本中处理不存在的情况

 
```
-- 方法1：使用匿名块处理异常
BEGIN
    EXECUTE IMMEDIATE 'DROP PROCEDURE proc_create_trans';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -4043 THEN  -- -4043: 对象不存在
            RAISE;
        END IF;
END;
/

-- 方法2：先检查是否存在再删除
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM user_objects
    WHERE object_type = 'PROCEDURE'
    AND object_name = 'PROC_CREATE_TRANS';
    
    IF v_count > 0 THEN
        EXECUTE IMMEDIATE 'DROP PROCEDURE proc_create_trans';
        DBMS_OUTPUT.PUT_LINE('存储过程已删除');
    ELSE
        DBMS_OUTPUT.PUT_LINE('存储过程不存在');
    END IF;
END;
/
```

 
```
-- 步骤1：确认存储过程存在
SELECT object_name, object_type, status, created
FROM user_objects
WHERE object_type = 'PROCEDURE'
AND object_name = 'PROC_CREATE_TRANS';

-- 步骤2：查看存储过程源代码（可选，用于备份）
SELECT text
FROM user_source
WHERE name = 'PROC_CREATE_TRANS'
ORDER BY line;

-- 步骤3：删除存储过程
DROP PROCEDURE proc_create_trans;

-- 步骤4：确认已删除
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'PROCEDURE'
AND object_name = 'PROC_CREATE_TRANS';

```

删除存储过程前的依赖检查

 
```
-- 检查哪些对象依赖于此存储过程
SELECT name, type
FROM user_dependencies
WHERE referenced_name = 'PROC_CREATE_TRANS'
AND referenced_type = 'PROCEDURE';

-- 完整的依赖分析
SELECT 
    d.name as dependent_object,
    d.type as object_type,
    o.status
FROM user_dependencies d
LEFT JOIN user_objects o ON d.name = o.object_name AND d.type = o.object_type
WHERE d.referenced_name = 'PROC_CREATE_TRANS'
ORDER BY d.type, d.name;

```

权限要求

 
- 要删除存储过程，您需要：
  - 是该存储过程的所有者，或者
  - 具有 DROP ANY PROCEDURE 系统权限

```
-- 检查当前用户的权限
SELECT * FROM user_sys_privs WHERE privilege LIKE '%PROCEDURE%';
```

示例

 
```
-- 场景：安全地删除存储过程并记录日志
SET SERVEROUTPUT ON;

DECLARE
    v_procedure_exists NUMBER;
BEGIN
    -- 检查存储过程是否存在
    SELECT COUNT(*)
    INTO v_procedure_exists
    FROM user_objects
    WHERE object_type = 'PROCEDURE'
    AND object_name = 'PROC_CREATE_TRANS';
    
    -- 如果存在则删除
    IF v_procedure_exists > 0 THEN
        -- 记录删除前的信息
        DBMS_OUTPUT.PUT_LINE('正在删除存储过程: PROC_CREATE_TRANS');
        DBMS_OUTPUT.PUT_LINE('删除时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
        
        -- 执行删除
        EXECUTE IMMEDIATE 'DROP PROCEDURE PROC_CREATE_TRANS';
        
        DBMS_OUTPUT.PUT_LINE('存储过程删除成功');
    ELSE
        DBMS_OUTPUT.PUT_LINE('存储过程 PROC_CREATE_TRANS 不存在');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('删除失败: ' || SQLERRM);
END;
/
```

 
```
-- 步骤1：确认存储过程存在
SELECT object_name, object_type, status, created
FROM user_objects
WHERE object_type = 'PROCEDURE'
AND object_name = 'PROC_CREATE_TRANS';

-- 步骤2：查看存储过程源代码（可选，用于备份）
SELECT text
FROM user_source
WHERE name = 'PROC_CREATE_TRANS'
ORDER BY line;

-- 步骤3：删除存储过程
DROP PROCEDURE proc_create_trans;

-- 步骤4：确认已删除
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'PROCEDURE'
AND object_name = 'PROC_CREATE_TRANS';
```

 
```
-- 删除名称符合特定模式的所有存储过程
BEGIN
    FOR rec IN (
        SELECT object_name
        FROM user_objects
        WHERE object_type = 'PROCEDURE'
        AND object_name LIKE 'PROC_%'
    ) LOOP
        EXECUTE IMMEDIATE 'DROP PROCEDURE ' || rec.object_name;
        DBMS_OUTPUT.PUT_LINE('已删除: ' || rec.object_name);
    END LOOP;
END;
/

-- 删除所有无效的存储过程
BEGIN
    FOR rec IN (
        SELECT object_name
        FROM user_objects
        WHERE object_type = 'PROCEDURE'
        AND status = 'INVALID'
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'DROP PROCEDURE ' || rec.object_name;
            DBMS_OUTPUT.PUT_LINE('已删除无效存储过程: ' || rec.object_name);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('删除失败: ' || rec.object_name || ' - ' || SQLERRM);
        END;
    END LOOP;
END;
/
```

 
```
CREATE OR REPLACE PROCEDURE drop_procedure_safely(
    p_procedure_name IN VARCHAR2
)
IS
    v_procedure_exists NUMBER;
    v_upper_procedure_name VARCHAR2(100);
    v_drop_sql VARCHAR2(500);
BEGIN
    -- 统一转换为大写处理
    v_upper_procedure_name := UPPER(TRIM(p_procedure_name));
    
    -- 验证参数是否为空
    IF v_upper_procedure_name IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('错误: 存储过程名称不能为空');
        RETURN;
    END IF;
    
    -- 检查存储过程是否存在
    SELECT COUNT(*)
    INTO v_procedure_exists
    FROM user_objects
    WHERE object_type = 'PROCEDURE'
    AND object_name = v_upper_procedure_name;
    
    -- 如果存在则删除
    IF v_procedure_exists > 0 THEN
        -- 记录删除前的信息
        DBMS_OUTPUT.PUT_LINE('正在删除存储过程: ' || v_upper_procedure_name);
        DBMS_OUTPUT.PUT_LINE('删除时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
        
        -- 构建并执行删除语句
        v_drop_sql := 'DROP PROCEDURE ' || v_upper_procedure_name;
        DBMS_OUTPUT.PUT_LINE('执行SQL: ' || v_drop_sql);
        
        EXECUTE IMMEDIATE v_drop_sql;
        
        DBMS_OUTPUT.PUT_LINE('存储过程删除成功');
    ELSE
        DBMS_OUTPUT.PUT_LINE('存储过程 ' || v_upper_procedure_name || ' 不存在');
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('删除失败: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE);
END drop_procedure_safely;
/

```

 
```
-- 启用输出
SET SERVEROUTPUT ON;

-- 测试各种大小写情况
BEGIN
    drop_procedure_safely('proc_create_trans');      -- 小写
    drop_procedure_safely('PROC_CREATE_TRANS');      -- 大写
    drop_procedure_safely('Proc_Create_Trans');      -- 混合大小写
    drop_procedure_safely('  proc_create_trans  ');  -- 带空格
END;
/

```

 
```
CREATE OR REPLACE FUNCTION calculate_annual_salary (
    p_monthly_salary IN NUMBER
) RETURN NUMBER
IS
    v_annual_salary NUMBER;
BEGIN
    v_annual_salary := p_monthly_salary * 12;
    RETURN v_annual_salary;
    
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END calculate_annual_salary;
/
```
    

使用函数

 
```
-- 在 SQL 中使用
SELECT employee_id, first_name, salary,
       calculate_annual_salary(salary) as annual_salary
FROM employees
WHERE department_id = 50;

-- 在 PL/SQL 中使用
DECLARE
    v_annual_salary NUMBER;
BEGIN
    v_annual_salary := calculate_annual_salary(5000);
    DBMS_OUTPUT.PUT_LINE('年薪: ' || v_annual_salary);
END;
/
```
    

 
```
CREATE OR REPLACE PACKAGE employee_pkg IS
    -- 常量
    g_company_name CONSTANT VARCHAR2(50) := 'ABC Company';
    
    -- 异常
    e_employee_not_found EXCEPTION;
    
    -- 函数
    FUNCTION get_employee_count(p_dept_id NUMBER) RETURN NUMBER;
    
    -- 过程
    PROCEDURE update_employee_salary(
        p_employee_id IN NUMBER,
        p_new_salary IN NUMBER
    );
    
    PROCEDURE get_employee_details(
        p_employee_id IN NUMBER,
        p_employee_name OUT VARCHAR2,
        p_department_name OUT VARCHAR2
    );
    
END employee_pkg;
/
```
    

 
```
CREATE OR REPLACE PACKAGE BODY employee_pkg IS
    
    -- 私有变量（只在包内可见）
    v_last_updated DATE;
    
    -- 实现函数
    FUNCTION get_employee_count(p_dept_id NUMBER) RETURN NUMBER IS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*)
        INTO v_count
        FROM employees
        WHERE department_id = p_dept_id;
        
        RETURN v_count;
        
    EXCEPTION
        WHEN OTHERS THEN
            RETURN 0;
    END get_employee_count;
    
    -- 实现过程
    PROCEDURE update_employee_salary(
        p_employee_id IN NUMBER,
        p_new_salary IN NUMBER
    ) IS
    BEGIN
        UPDATE employees
        SET salary = p_new_salary
        WHERE employee_id = p_employee_id;
        
        IF SQL%ROWCOUNT = 0 THEN
            RAISE e_employee_not_found;
        END IF;
        
        v_last_updated := SYSDATE;
        COMMIT;
        
    EXCEPTION
        WHEN e_employee_not_found THEN
            RAISE_APPLICATION_ERROR(-20001, '员工不存在');
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END update_employee_salary;
    
    PROCEDURE get_employee_details(
        p_employee_id IN NUMBER,
        p_employee_name OUT VARCHAR2,
        p_department_name OUT VARCHAR2
    ) IS
    BEGIN
        SELECT e.first_name || ' ' || e.last_name, d.department_name
        INTO p_employee_name, p_department_name
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id
        WHERE e.employee_id = p_employee_id;
        
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            p_employee_name := '未找到';
            p_department_name := '未找到';
    END get_employee_details;
    
END employee_pkg;
/
```
    

 
```
-- 调用包中的过程和函数
DECLARE
    v_count NUMBER;
    v_name VARCHAR2(100);
    v_dept VARCHAR2(50);
BEGIN
    -- 调用函数
    v_count := employee_pkg.get_employee_count(50);
    DBMS_OUTPUT.PUT_LINE('部门50员工数: ' || v_count);
    
    -- 调用过程
    employee_pkg.get_employee_details(100, v_name, v_dept);
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_name || ', 部门: ' || v_dept);
    
    -- 调用更新过程
    employee_pkg.update_employee_salary(100, 15000);
    DBMS_OUTPUT.PUT_LINE('薪资更新成功');
END;
/
```

 
```
CREATE OR REPLACE TRIGGER employees_audit_trigger
    BEFORE INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
DECLARE
    v_operation VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_operation := 'INSERT';
        DBMS_OUTPUT.PUT_LINE('新增员工: ' || :NEW.first_name);
    ELSIF UPDATING THEN
        v_operation := 'UPDATE';
        DBMS_OUTPUT.PUT_LINE(
            '更新员工: ' || :OLD.first_name || 
            ', 旧薪资: ' || :OLD.salary || 
            ', 新薪资: ' || :NEW.salary
        );
    ELSIF DELETING THEN
        v_operation := 'DELETE';
        DBMS_OUTPUT.PUT_LINE('删除员工: ' || :OLD.first_name);
    END IF;
    
    -- 可以插入审计表
    INSERT INTO employees_audit (
        employee_id, operation, change_date, old_salary, new_salary
    ) VALUES (
        :NEW.employee_id, v_operation, SYSDATE, :OLD.salary, :NEW.salary
    );
    
END employees_audit_trigger;
/
```

    

 
```
CREATE OR REPLACE PROCEDURE dynamic_table_operation (
    p_table_name IN VARCHAR2,
    p_operation IN VARCHAR2
) IS
    v_sql VARCHAR2(1000);
BEGIN
    IF p_operation = 'COUNT' THEN
        v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
        EXECUTE IMMEDIATE v_sql;
        
    ELSIF p_operation = 'DROP' THEN
        v_sql := 'DROP TABLE ' || p_table_name;
        EXECUTE IMMEDIATE v_sql;
        DBMS_OUTPUT.PUT_LINE('表 ' || p_table_name || ' 已删除');
        
    ELSE
        DBMS_OUTPUT.PUT_LINE('不支持的操作: ' || p_operation);
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END dynamic_table_operation;
/
```
    

 
```
-- 使用 DBMS_OUTPUT 调试
CREATE OR REPLACE PROCEDURE debug_demo IS
    v_counter NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('开始执行过程');
    
    FOR i IN 1..5 LOOP
        v_counter := v_counter + i;
        DBMS_OUTPUT.PUT_LINE('循环 ' || i || ', 计数器: ' || v_counter);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('执行完成，最终计数: ' || v_counter);
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误发生在: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;
END debug_demo;
/
```
    

 
```
from tpf.db import OracleDb
db = OracleDb(name="aml")

# 执行带表名参数的 PL/SQL（方案1：使用动态SQL）
sql = """
declare
    v_count number;
    v_sql varchar2(1000);
begin
    v_sql := 'select count(*) from ' || :table_name||' where rownum<3 ';
    execute immediate v_sql into v_count;
    dbms_output.put_line('Count: ' || v_count);
end;
"""
db.exec_plsql(sql,in_params={'table_name': 'bb11_trans'})
```
    

 
```
{'success': True,
 'execution_time': 0.386,
 'output': ['Count: 2'],
 'error': None}
```
    

 
```
from tpf.db import OracleDb
import cx_Oracle
db = OracleDb(name="aml")

sql = """
declare
    v_count number;
    v_sql varchar2(1000);
begin
    v_sql := 'select count(*) from ' || :table_name ;
    if :where_clause is not null then
        v_sql := v_sql || ' where ' || :where_clause;
    end if;
    execute immediate v_sql into v_count;
    dbms_output.put_line('Total count: ' || v_count);
    :out_count := v_count;
end;
"""
result = db.exec_with_output_params(sql, 
    in_params={
        'table_name': 'bb11_trans',
        'where_clause': 'rownum<3 '}, 
    out_params={'out_count': cx_Oracle.NUMBER})
result
```
    

 
```
{'success': True,
 'output_params': {'out_count': 2.0},
 'execution_time': 0.253,
 'error': None,
 'dbms_output': ['Total count: 2']}
```
    

 
```
from tpf.db import OracleDb
db = OracleDb(name="aml")
db.exec_proc("proc_create_trans",
    in_params={'p_start_date': '2025-07-01', 'p_end_date': '2025-07-31'},
    out_params={})
```

 
```
from tpf.db import OracleDb
db = OracleDb(name="aml")

p_file_path="/ai/wks/sql/v1_ora/p01_create_trans.sql"
db.drop_proc('proc_create_trans',first_exec=True)
db.exec_sql_file(sql_file=p_file_path)
db.show_proc_status('proc_create_trans')
```
    

 
- 使用sqlplus可以在end;的后面加/ 
- 而使用python调用sqlplus时，end;后面不能加/

 
```
from tpf.db import OracleDb
db = OracleDb(name="aml")

db.exec_proc("proc_create_trans",
    in_params={'p_start_date': '2025-07-01', 'p_end_date': '2025-07-31'},
    out_params={})
```

 
- 第一次执行删除存储过程时，会先创建一个临时存储过程，专用用于删除存储过程
- 如果是第二次执行，则只需要名称匹配，即可删除
- 如此，就不用每次删除存储过程时，就得再创建一次临时存储过程
```
db.drop_proc('proc_create_trans',first_exec=True)
db.drop_proc('proc_create_trans')
```