金仓数据库Oracle兼容性体验报告

-- 测试复杂存储过程：员工薪资管理
CREATE OR REPLACE PACKAGE employee_mgmt AS
    -- 类型定义
    TYPE emp_record IS RECORD (
        emp_id employees.employee_id%TYPE,
        full_name VARCHAR2(100),
        current_salary employees.salary%TYPE,
        dept_name departments.department_name%TYPE
    );
    
    TYPE emp_table IS TABLE OF emp_record;
    
    -- 过程声明
    PROCEDURE update_employee_salary (
        p_emp_id IN employees.employee_id%TYPE,
        p_increase_pct IN NUMBER DEFAULT 10
    );
    
    FUNCTION get_department_employees (
        p_dept_id IN departments.department_id%TYPE
    ) RETURN emp_table;
    
END employee_mgmt;
/

CREATE OR REPLACE PACKAGE BODY employee_mgmt AS
    
    PROCEDURE update_employee_salary (
        p_emp_id IN employees.employee_id%TYPE,
        p_increase_pct IN NUMBER DEFAULT 10
    ) AS
        v_old_salary employees.salary%TYPE;
        v_new_salary employees.salary%TYPE;
        v_emp_name VARCHAR2(100);
    BEGIN
        -- 获取当前薪资
        SELECT first_name || ' ' || last_name, salary
        INTO v_emp_name, v_old_salary
        FROM employees
        WHERE employee_id = p_emp_id
        FOR UPDATE;
        
        -- 计算新薪资
        v_new_salary := v_old_salary * (1 + p_increase_pct/100);
        
        -- 更新薪资
        UPDATE employees
        SET salary = v_new_salary,
            last_update_date = SYSDATE
        WHERE employee_id = p_emp_id;
        
        -- 记录日志
        INSERT INTO salary_log (employee_id, old_salary, new_salary, 
                               update_date, update_reason)
        VALUES (p_emp_id, v_old_salary, v_new_salary, SYSDATE, 
                'Annual increase ' || p_increase_pct || '%');
        
        COMMIT;
        
        DBMS_OUTPUT.PUT_LINE('员工 ' || v_emp_name || 
                           ' 薪资更新完成：' || v_old_salary || 
                           ' -> ' || v_new_salary);
        
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('错误：员工ID ' || p_emp_id || ' 不存在');
            RAISE;
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('错误：' || SQLERRM);
            RAISE;
    END update_employee_salary;
    
    FUNCTION get_department_employees (
        p_dept_id IN departments.department_id%TYPE
    ) RETURN emp_table AS
        v_emp_table emp_table := emp_table();
    BEGIN
        SELECT emp_record(e.employee_id, 
                         e.first_name || ' ' || e.last_name,
                         e.salary,
                         d.department_name)
        BULK COLLECT INTO v_emp_table
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id
        WHERE e.department_id = p_dept_id
        ORDER BY e.salary DESC;
        
        RETURN v_emp_table;
        
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('查询错误：' || SQLERRM);
            RETURN emp_table();
    END get_department_employees;
    
END employee_mgmt;
/