PL/SQL游标与异常 #

游标类型
├── 隐式游标
│   └── SQL语句自动创建
├── 显式游标
│   └── 程序员定义和控制
└── 游标变量
    └── 动态游标，可传递

显式游标处理流程
├── 1. 声明游标（DECLARE）
├── 2. 打开游标（OPEN）
├── 3. 提取数据（FETCH）
├── 4. 处理数据
├── 5. 关闭游标（CLOSE）
└── 循环步骤3-5直到处理完所有数据

-- 隐式游标：由Oracle自动创建和管理
-- 每次执行SQL语句时，Oracle自动创建隐式游标
-- 隐式游标名称为SQL

-- 隐式游标属性
-- SQL%FOUND：是否找到数据
-- SQL%NOTFOUND：是否没找到数据
-- SQL%ROWCOUNT：影响的行数
-- SQL%ISOPEN：游标是否打开（始终为FALSE）

BEGIN
    UPDATE employees SET salary = salary * 1.1
    WHERE department_id = 10;
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No rows updated');
    END IF;
END;
/

-- SQL%FOUND
BEGIN
    UPDATE employees SET salary = salary * 1.1
    WHERE employee_id = 99999;
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Update successful');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No rows found');
    END IF;
END;
/

-- SQL%NOTFOUND
BEGIN
    UPDATE employees SET salary = salary * 1.1
    WHERE employee_id = 99999;
    
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No matching rows');
    END IF;
END;
/

-- SQL%ROWCOUNT
BEGIN
    DELETE FROM employees WHERE department_id = 99;
    
    DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows');
END;
/

-- SQL%ISOPEN
BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
    
    -- 隐式游标在语句执行后自动关闭
    DBMS_OUTPUT.PUT_LINE('Is open: ' || SQL%ISOPEN);  -- FALSE
END;
/

-- SELECT INTO使用隐式游标
DECLARE
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name INTO v_name
    FROM employees
    WHERE employee_id = 100;
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees found');
END;
/

-- 显式游标基本语法
DECLARE
    -- 声明游标
    CURSOR cursor_name IS select_statement;
    
    -- 声明变量
    variable_name datatype;
BEGIN
    -- 打开游标
    OPEN cursor_name;
    
    -- 循环提取数据
    LOOP
        FETCH cursor_name INTO variable_name;
        EXIT WHEN cursor_name%NOTFOUND;
        
        -- 处理数据
    END LOOP;
    
    -- 关闭游标
    CLOSE cursor_name;
END;

-- 基本显式游标
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 10;
    
    v_emp_id NUMBER;
    v_first_name VARCHAR2(50);
    v_last_name VARCHAR2(50);
    v_salary NUMBER;
BEGIN
    OPEN c_emp;
    
    LOOP
        FETCH c_emp INTO v_emp_id, v_first_name, v_last_name, v_salary;
        EXIT WHEN c_emp%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_first_name || ' ' || v_last_name || ' - ' || v_salary);
    END LOOP;
    
    CLOSE c_emp;
END;
/

-- 使用记录类型
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 10;
    
    v_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    
    LOOP
        FETCH c_emp INTO v_emp;
        EXIT WHEN c_emp%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(v_emp.employee_id || ': ' || v_emp.first_name || ' - ' || v_emp.salary);
    END LOOP;
    
    CLOSE c_emp;
END;
/

-- 显式游标属性
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name FROM employees;
    
    v_emp c_emp%ROWTYPE;
BEGIN
    -- %ISOPEN：游标是否打开
    DBMS_OUTPUT.PUT_LINE('Before open: ' || c_emp%ISOPEN);  -- FALSE
    
    OPEN c_emp;
    DBMS_OUTPUT.PUT_LINE('After open: ' || c_emp%ISOPEN);   -- TRUE
    
    -- %FOUND：是否找到数据
    FETCH c_emp INTO v_emp;
    DBMS_OUTPUT.PUT_LINE('After first fetch: ' || c_emp%FOUND);  -- TRUE
    
    -- %NOTFOUND：是否没找到数据
    LOOP
        FETCH c_emp INTO v_emp;
        EXIT WHEN c_emp%NOTFOUND;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('After last fetch: ' || c_emp%NOTFOUND);  -- TRUE
    
    -- %ROWCOUNT：已提取的行数
    DBMS_OUTPUT.PUT_LINE('Total rows fetched: ' || c_emp%ROWCOUNT);
    
    CLOSE c_emp;
END;
/

-- 游标FOR循环：简化游标处理
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 10;
BEGIN
    FOR emp_rec IN c_emp LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name || ' - ' || emp_rec.salary);
    END LOOP;
    -- 游标自动打开、提取、关闭
END;
/

-- 更简洁的写法
BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name || ' - ' || emp_rec.salary);
    END LOOP;
END;
/

-- 带参数的游标
DECLARE
    CURSOR c_emp(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
    -- 打开游标时传递参数
    FOR emp_rec IN c_emp(10, 5000) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name || ' - ' || emp_rec.salary);
    END LOOP;
END;
/

-- 默认参数
DECLARE
    CURSOR c_emp(p_dept_id NUMBER DEFAULT NULL, p_min_salary NUMBER DEFAULT 0) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE (p_dept_id IS NULL OR department_id = p_dept_id)
        AND salary >= p_min_salary;
BEGIN
    -- 使用默认值
    FOR emp_rec IN c_emp(p_min_salary => 10000) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' - ' || emp_rec.salary);
    END LOOP;
END;
/

-- 游标变量：动态游标
DECLARE
    -- 定义游标类型
    TYPE t_ref_cursor IS REF CURSOR;
    
    -- 声明游标变量
    v_cursor t_ref_cursor;
    
    -- 声明记录变量
    v_emp employees%ROWTYPE;
BEGIN
    -- 打开游标变量
    OPEN v_cursor FOR
        SELECT * FROM employees WHERE department_id = 10;
    
    -- 提取数据
    LOOP
        FETCH v_cursor INTO v_emp;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' - ' || v_emp.salary);
    END LOOP;
    
    CLOSE v_cursor;
END;
/

-- 强类型游标变量：指定返回类型
DECLARE
    TYPE t_emp_cursor IS REF CURSOR RETURN employees%ROWTYPE;
    
    v_cursor t_emp_cursor;
    v_emp employees%ROWTYPE;
BEGIN
    OPEN v_cursor FOR
        SELECT * FROM employees WHERE department_id = 10;
    
    LOOP
        FETCH v_cursor INTO v_emp;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name);
    END LOOP;
    
    CLOSE v_cursor;
END;
/

-- 游标变量作为参数传递
CREATE OR REPLACE PROCEDURE process_cursor(
    p_cursor IN OUT SYS_REFCURSOR
) AS
    v_emp_id NUMBER;
    v_name VARCHAR2(100);
BEGIN
    LOOP
        FETCH p_cursor INTO v_emp_id, v_name;
        EXIT WHEN p_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_name);
    END LOOP;
END;
/

-- 调用
DECLARE
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name || ' ' || last_name
        FROM employees;
    
    process_cursor(v_cursor);
    CLOSE v_cursor;
END;
/

-- FOR UPDATE：锁定行
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 10
        FOR UPDATE OF salary;
BEGIN
    FOR emp_rec IN c_emp LOOP
        UPDATE employees
        SET salary = emp_rec.salary * 1.1
        WHERE CURRENT OF c_emp;
    END LOOP;
    
    COMMIT;
END;
/

-- NOWAIT：不等待锁
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 10
        FOR UPDATE OF salary NOWAIT;
BEGIN
    FOR emp_rec IN c_emp LOOP
        UPDATE employees SET salary = emp_rec.salary * 1.1
        WHERE CURRENT OF c_emp;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Cannot acquire lock');
END;
/

-- SKIP LOCKED：跳过已锁定的行
DECLARE
    CURSOR c_emp IS
        SELECT employee_id
        FROM employees
        FOR UPDATE SKIP LOCKED;
BEGIN
    FOR emp_rec IN c_emp LOOP
        -- 处理未锁定的行
        NULL;
    END LOOP;
END;
/

-- WHERE CURRENT OF：更新当前行
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 10
        FOR UPDATE;
BEGIN
    FOR emp_rec IN c_emp LOOP
        IF emp_rec.salary < 5000 THEN
            UPDATE employees SET salary = 5000
            WHERE CURRENT OF c_emp;
        END IF;
    END LOOP;
    
    COMMIT;
END;
/

异常类型
├── 预定义异常
│   └── Oracle预定义的错误
├── 非预定义异常
│   └── 自定义错误码的异常
└── 用户定义异常
    └── 程序员定义的异常

-- 常见预定义异常
DECLARE
    v_name VARCHAR2(100);
    v_num NUMBER;
BEGIN
    -- NO_DATA_FOUND
    BEGIN
        SELECT first_name INTO v_name
        FROM employees WHERE employee_id = 99999;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No data found');
    END;
    
    -- TOO_MANY_ROWS
    BEGIN
        SELECT first_name INTO v_name
        FROM employees WHERE department_id = 10;
    EXCEPTION
        WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('Too many rows');
    END;
    
    -- VALUE_ERROR
    BEGIN
        v_num := TO_NUMBER('ABC');
    EXCEPTION
        WHEN VALUE_ERROR THEN
            DBMS_OUTPUT.PUT_LINE('Value error');
    END;
    
    -- ZERO_DIVIDE
    BEGIN
        v_num := 10 / 0;
    EXCEPTION
        WHEN ZERO_DIVIDE THEN
            DBMS_OUTPUT.PUT_LINE('Division by zero');
    END;
    
    -- DUP_VAL_ON_INDEX
    BEGIN
        INSERT INTO employees (employee_id) VALUES (100);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('Duplicate value');
    END;
END;
/

-- 非预定义异常：使用PRAGMA EXCEPTION_INIT
DECLARE
    e_custom_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_custom_error, -20001);
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
EXCEPTION
    WHEN e_custom_error THEN
        DBMS_OUTPUT.PUT_LINE('Caught custom error: ' || SQLERRM);
END;
/

-- 关联Oracle错误码
DECLARE
    e_integrity_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_integrity_error, -2292);  -- ORA-02292
BEGIN
    DELETE FROM departments WHERE department_id = 10;
EXCEPTION
    WHEN e_integrity_error THEN
        DBMS_OUTPUT.PUT_LINE('Integrity constraint violated');
END;
/

-- 用户定义异常
DECLARE
    e_invalid_salary EXCEPTION;
    v_salary NUMBER := -100;
BEGIN
    IF v_salary < 0 THEN
        RAISE e_invalid_salary;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('Salary is valid');
    
EXCEPTION
    WHEN e_invalid_salary THEN
        DBMS_OUTPUT.PUT_LINE('Invalid salary: cannot be negative');
END;
/

-- 使用RAISE_APPLICATION_ERROR
DECLARE
    v_salary NUMBER := -100;
BEGIN
    IF v_salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;
END;
/

-- 异常传播
DECLARE
    e_inner EXCEPTION;
BEGIN
    BEGIN
        RAISE e_inner;
    EXCEPTION
        WHEN e_inner THEN
            DBMS_OUTPUT.PUT_LINE('Inner block caught');
            RAISE;  -- 重新抛出异常
    END;
    
    DBMS_OUTPUT.PUT_LINE('This will not execute');
    
EXCEPTION
    WHEN e_inner THEN
        DBMS_OUTPUT.PUT_LINE('Outer block caught');
END;
/

-- 异常传播到调用者
CREATE OR REPLACE PROCEDURE inner_proc AS
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Error in inner_proc');
END;
/

CREATE OR REPLACE PROCEDURE outer_proc AS
BEGIN
    inner_proc;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Caught: ' || SQLERRM);
END;
/

-- SQLCODE和SQLERRM
DECLARE
    v_code NUMBER;
    v_msg VARCHAR2(4000);
BEGIN
    INSERT INTO employees (employee_id) VALUES (100);
EXCEPTION
    WHEN OTHERS THEN
        v_code := SQLCODE;
        v_msg := SQLERRM;
        
        DBMS_OUTPUT.PUT_LINE('Error Code: ' || v_code);
        DBMS_OUTPUT.PUT_LINE('Error Message: ' || v_msg);
        
        -- 记录错误日志
        INSERT INTO error_log (error_code, error_message, error_time)
        VALUES (v_code, v_msg, SYSTIMESTAMP);
END;
/

-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
DECLARE
BEGIN
    INSERT INTO employees (employee_id) VALUES (100);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

-- 游标异常处理
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name FROM employees;
    
    v_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    
    BEGIN
        LOOP
            FETCH c_emp INTO v_emp;
            EXIT WHEN c_emp%NOTFOUND;
            
            -- 处理数据
            DBMS_OUTPUT.PUT_LINE(v_emp.employee_id);
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    END;
    
    -- 确保关闭游标
    IF c_emp%ISOPEN THEN
        CLOSE c_emp;
    END IF;
END;
/

-- 使用嵌套块处理异常
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary FROM employees;
BEGIN
    FOR emp_rec IN c_emp LOOP
        BEGIN
            -- 可能出错的操作
            IF emp_rec.salary < 0 THEN
                RAISE_APPLICATION_ERROR(-20001, 'Negative salary');
            END IF;
            
            DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.salary);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error processing employee ' || emp_rec.employee_id || ': ' || SQLERRM);
        END;
    END LOOP;
END;
/