-- 存儲(chǔ)過程語法
CREATE [OR REPLACE] PROCEDURE 過程名 (
    參數(shù)1 [模式] 數(shù)據(jù)類型,
    參數(shù)2 [模式] 數(shù)據(jù)類型
) [AUTHID {DEFINER | CURRENT_USER}] -- 權(quán)限模型
IS|AS
    -- 聲明部分（變量、游標(biāo)、類型）
    變量聲明;
BEGIN
    -- 執(zhí)行部分
    可執(zhí)行語句;
EXCEPTION
    -- 異常處理部分
    異常處理;
END [過程名];
/
-- 函數(shù)語法
CREATE [OR REPLACE] FUNCTION 函數(shù)名 (
    參數(shù)1 [模式] 數(shù)據(jù)類型
) RETURN 返回?cái)?shù)據(jù)類型
IS|AS
    變量聲明;
BEGIN
    RETURN 返回值;
EXCEPTION
    異常處理;
END [函數(shù)名];
/

-- 場(chǎng)景：調(diào)整員工薪水并記錄日志
CREATE OR REPLACE PROCEDURE adjust_employee_salary (
    p_emp_id IN NUMBER,           -- 員工ID（輸入?yún)?shù)）
    p_percent IN NUMBER,          -- 調(diào)整百分比（輸入?yún)?shù)）
    p_new_salary OUT NUMBER,      -- 新薪水（輸出參數(shù)）
    p_message OUT VARCHAR2        -- 消息（輸出參數(shù)）
) AUTHID DEFINER
IS
    -- 聲明變量
    v_old_salary employees.salary%TYPE;
    v_emp_name VARCHAR2(100);
BEGIN
    -- 1. 查詢當(dāng)前薪水
    SELECT salary, first_name || ' ' || last_name
    INTO v_old_salary, v_emp_name
    FROM employees
    WHERE employee_id = p_emp_id;
    -- 2. 計(jì)算新薪水
    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. 設(shè)置返回消息
    p_message := '員工 ' || v_emp_name || ' 薪水已從 ' || v_old_salary || ' 調(diào)整為 ' || p_new_salary;
    -- 6. 提交事務(wù)
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_message := '錯(cuò)誤：?jiǎn)T工ID ' || p_emp_id || ' 不存在';
        ROLLBACK;
    WHEN OTHERS THEN
        p_message := '錯(cuò)誤：' || SQLERRM;
        ROLLBACK;
END adjust_employee_salary;
/

-- 方式1：匿名塊調(diào)用
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 調(diào)用（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);

-- 輸入?yún)?shù)，只讀
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;
/

-- 輸出參數(shù)，用于返回值
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;
/

-- 輸入輸出參數(shù)，既可讀也可寫
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;
/
-- 調(diào)用
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,  -- 默認(rèn)值
    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;
/
-- 調(diào)用（使用默認(rèn)值）
EXEC create_employee('John', 'Doe');
-- 調(diào)用（覆蓋默認(rèn)值）
EXEC create_employee('Jane', 'Smith', 8000, 60);

-- 場(chǎng)景：根據(jù)員工ID計(jì)算年薪（含獎(jiǎng)金）
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;
    -- 計(jì)算年收入
    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;  -- 函數(shù)必須返回值
    WHEN OTHERS THEN
        RETURN -1;    -- 錯(cuò)誤標(biāo)識(shí)
END calculate_annual_income;
/

-- 方式1：在 SQL 語句中調(diào)用（函數(shù)的核心優(yōu)勢(shì)）
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 塊中調(diào)用
DECLARE
    v_income NUMBER;
BEGIN
    v_income := calculate_annual_income(101, FALSE);
    DBMS_OUTPUT.PUT_LINE('年收入：' || v_income);
END;
/
-- 方式3：在 WHERE 子句中調(diào)用
SELECT * FROM employees
WHERE calculate_annual_income(employee_id) > (SELECT AVG(salary*12) FROM employees);

-- 預(yù)定義異常
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('沒有找到數(shù)據(jù)');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('返回多行，但期望單行');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('錯(cuò)誤代碼：' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('錯(cuò)誤消息：' || 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);  -- 關(guān)聯(lián)錯(cuò)誤碼
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('錯(cuò)誤：新薪資不能超過20000');
        ROLLBACK;
END;
/

-- 顯式游標(biāo)處理多行數(shù)據(jù)
CREATE OR REPLACE PROCEDURE bulk_raise_salary (
    p_dept_id IN NUMBER,
    p_percent IN NUMBER
) IS
    -- 聲明游標(biāo)
    CURSOR emp_cursor IS
        SELECT employee_id, salary FROM employees 
        WHERE department_id = p_dept_id 
        FOR UPDATE;  -- 加鎖防止并發(fā)修改
    -- 記錄類型
    emp_rec emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO emp_rec;
        EXIT WHEN emp_cursor%NOTFOUND;  -- 退出循環(huán)條件
        -- 更新薪水
        UPDATE employees 
        SET salary = emp_rec.salary * (1 + p_percent/100)
        WHERE CURRENT OF emp_cursor;  -- 定位當(dāng)前游標(biāo)行
        DBMS_OUTPUT.PUT_LINE('員工 ' || emp_rec.employee_id || ' 已調(diào)整');
    END LOOP;
    CLOSE emp_cursor;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        CLOSE emp_cursor;
        ROLLBACK;
        RAISE;
END;
/
-- 游標(biāo) FOR 循環(huán)（簡(jiǎn)化）
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;
/

-- 日志記錄不受主事務(wù)影響
CREATE OR REPLACE PROCEDURE log_message (
    p_message IN VARCHAR2
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 聲明自治事務(wù)
BEGIN
    INSERT INTO message_log (message, log_time) VALUES (p_message, SYSDATE);
    COMMIT;  -- 獨(dú)立提交，不影響主事務(wù)
END;
/
-- 主事務(wù)回滾，但日志已提交
CREATE OR REPLACE PROCEDURE main_transaction IS
BEGIN
    INSERT INTO orders VALUES (101, 5000);
    log_message('訂單 101 已創(chuàng)建');  -- 自治事務(wù)已提交
    ROLLBACK;  -- 訂單被回滾，但日志保留
END;
/

-- 場(chǎng)景：動(dòng)態(tài)表名查詢
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;
/
-- 動(dòng)態(tài) 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. 包規(guī)范（接口定義）
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;
    -- 函數(shù)聲明
    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. 包主體（實(shí)現(xiàn)）
CREATE OR REPLACE PACKAGE BODY employee_mgmt AS
    -- 私有函數(shù)（外部不可見）
    FUNCTION validate_salary (p_salary IN NUMBER) RETURN BOOLEAN IS
    BEGIN
        RETURN p_salary BETWEEN 1000 AND c_max_salary;
    END validate_salary;
    -- 公有函數(shù)實(shí)現(xiàn)
    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;  -- 獎(jiǎng)金為薪水的10%
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 0;
    END calculate_bonus;
    -- 存儲(chǔ)過程實(shí)現(xiàn)
    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;
/

-- 調(diào)用包函數(shù)
SELECT employee_mgmt.calculate_bonus(101) FROM dual;
-- 調(diào)用包過程
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;
/

-- 錯(cuò)誤：逐行處理（慢）
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;

-- 對(duì)于大集合，使用 NOCOPY 避免拷貝
CREATE OR REPLACE PROCEDURE process_large_collection (
    p_collection IN OUT NOCOPY large_collection_type  -- NOCOPY 提示
) IS
BEGIN
    -- 直接操作原集合，不創(chuàng)建副本
    FOR i IN 1..p_collection.COUNT LOOP
        p_collection(i).status := 'PROCESSED';
    END LOOP;
END;

-- 錯(cuò)誤：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;
-- 查詢時(shí)使用
SELECT employee_id, get_department_name(department_id) FROM employees;  -- 低效
-- 正確：純 SQL 實(shí)現(xiàn)
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 || ' 個(gè)員工：' || rec.employee_id);
    END LOOP;
END;
/

-- 在 V$SESSION 中顯示進(jìn)度
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'
        );
        -- 業(yè)務(wù)邏輯
        UPDATE employees SET salary = salary * 1.05 WHERE employee_id = rec.employee_id;
    END LOOP;
END;
/
-- 監(jiān)控查詢
SELECT sid, serial#, module, action FROM v$session WHERE module = 'SALARY_UPDATE';
SELECT * FROM v$session_longops WHERE opname = 'Employee Processing';

-- 查看存儲(chǔ)過程依賴的表
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;
-- 查看哪些對(duì)象依賴該過程
SELECT name, type
FROM all_dependencies
WHERE referenced_owner = 'HR'
AND referenced_name = 'ADJUST_EMPLOYEE_SALARY';

-- 前綴規(guī)范
- 存儲(chǔ)過程：p_業(yè)務(wù)模塊_操作（如 p_emp_update_salary）
- 函數(shù)：f_業(yè)務(wù)模塊_計(jì)算（如 f_emp_calc_bonus）
- 包：pkg_業(yè)務(wù)模塊（如 pkg_employee_mgmt）
- 參數(shù)：p_參數(shù)名（輸入）、p_參數(shù)名_out（輸出）、p_參數(shù)名_io（輸入輸出）
- 變量：v_變量名（局部）、g_變量名（全局包變量）

-- 1. 總是使用 AUTHID 明確權(quán)限
CREATE OR REPLACE PROCEDURE secure_proc(...) IS
    AUTHID CURRENT_USER  -- 調(diào)用者權(quán)限
IS
BEGIN
    ...
END;
/
-- 2. 參數(shù)使用 %TYPE 錨定
CREATE OR REPLACE PROCEDURE update_emp (
    p_emp_id IN employees.employee_id%TYPE,  -- 類型自動(dòng)同步
    p_salary IN employees.salary%TYPE
) IS ...
-- 3. 使用顯式游標(biāo)而非隱式
-- 錯(cuò)誤：隱式游標(biāo)無法處理 NO_DATA_FOUND
SELECT ... INTO ...;  -- 不推薦
-- 正確：顯式游標(biāo)控制
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. 異常處理精細(xì)化
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 處理查詢不到數(shù)據(jù)
    WHEN DUP_VAL_ON_INDEX THEN
        -- 處理唯一鍵沖突
    WHEN OTHERS THEN
        -- 記錄錯(cuò)誤日志后重新拋出
        log_error(SQLCODE, SQLERRM);
        RAISE;  -- 重新拋出，讓上層調(diào)用者處理