Oracle PL/SQL 使用指南

PL/SQL(Procedural Language extensions to SQL)是Oracle数据库的过程化编程语言，它结合了SQL的数据操作能力和过程化语言的流程控制能力。

基本结构

DECLARE
    -- 声明部分(可选)
    -- 变量、常量、游标等声明
BEGIN
    -- 执行部分(必需)
    -- PL/SQL语句和SQL语句
EXCEPTION
    -- 异常处理部分(可选)
    -- 异常处理代码
END;

变量声明与使用

DECLARE
    v_emp_name VARCHAR2(100);
    v_salary NUMBER(10,2) := 0;
    v_hiredate DATE;
    c_tax_rate CONSTANT NUMBER(3,2) := 0.05;
BEGIN
    SELECT ename, sal, hiredate 
    INTO v_emp_name, v_salary, v_hiredate
    FROM emp 
    WHERE empno = 7369;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
    DBMS_OUTPUT.PUT_LINE('Tax: ' || v_salary * c_tax_rate);
END;
/

流程控制

IF-THEN-ELSE

DECLARE
    v_score NUMBER := 85;
BEGIN
    IF v_score >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('优秀');
    ELSIF v_score >= 80 THEN
        DBMS_OUTPUT.PUT_LINE('良好');
    ELSIF v_score >= 60 THEN
        DBMS_OUTPUT.PUT_LINE('及格');
    ELSE
        DBMS_OUTPUT.PUT_LINE('不及格');
    END IF;
END;
/

CASE语句

DECLARE
    v_grade CHAR(1) := 'B';
BEGIN
    CASE v_grade
        WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('优秀');
        WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('良好');
        WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('及格');
        ELSE DBMS_OUTPUT.PUT_LINE('不及格');
    END CASE;
END;
/

循环结构

基本循环

DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 5;
    END LOOP;
END;
/

WHILE循环

DECLARE
    v_counter NUMBER := 1;
BEGIN
    WHILE v_counter <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;
/

FOR循环

BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Counter: ' || i);
    END LOOP;
END;
/

游标(Cursor)使用

显式游标

DECLARE
    CURSOR emp_cursor IS
        SELECT empno, ename, sal FROM emp WHERE deptno = 10;
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_sal emp.sal%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_sal;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename || ' ' || v_sal);
    END LOOP;
    CLOSE emp_cursor;
END;
/

隐式游标

BEGIN
    FOR emp_rec IN (SELECT empno, ename, sal FROM emp WHERE deptno = 20) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.empno || ' ' || emp_rec.ename || ' ' || emp_rec.sal);
    END LOOP;
END;
/

存储过程和函数

存储过程

CREATE OR REPLACE PROCEDURE update_salary (
    p_empno IN emp.empno%TYPE,
    p_increase IN NUMBER
) AS
    v_current_sal emp.sal%TYPE;
BEGIN
    -- 获取当前工资
    SELECT sal INTO v_current_sal FROM emp WHERE empno = p_empno;

    -- 更新工资
    UPDATE emp SET sal = sal + p_increase WHERE empno = p_empno;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Salary updated from ' || v_current_sal || 
                         ' to ' || (v_current_sal + p_increase));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END update_salary;
/

-- 调用存储过程
EXEC update_salary(7369, 500);

函数

CREATE OR REPLACE FUNCTION calculate_tax (
    p_salary IN NUMBER
) RETURN NUMBER IS
    v_tax NUMBER;
BEGIN
    IF p_salary <= 5000 THEN
        v_tax := 0;
    ELSIF p_salary <= 10000 THEN
        v_tax := (p_salary - 5000) * 0.1;
    ELSE
        v_tax := 5000 * 0.1 + (p_salary - 10000) * 0.2;
    END IF;

    RETURN v_tax;
END calculate_tax;
/

-- 调用函数
DECLARE
    v_tax NUMBER;
BEGIN
    v_tax := calculate_tax(15000);
    DBMS_OUTPUT.PUT_LINE('Tax: ' || v_tax);
END;
/

触发器

CREATE OR REPLACE TRIGGER audit_emp_changes
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSE
        v_action := 'DELETE';
    END IF;

    INSERT INTO emp_audit (
        audit_id, 
        empno, 
        action, 
        change_date, 
        old_sal, 
        new_sal
    ) VALUES (
        emp_audit_seq.NEXTVAL,
        :NEW.empno,
        v_action,
        SYSDATE,
        :OLD.sal,
        :NEW.sal
    );
END;
/

异常处理

DECLARE
    v_empno emp.empno%TYPE := 9999; -- 不存在的员工号
    v_ename emp.ename%TYPE;
BEGIN
    SELECT ename INTO v_ename FROM emp WHERE empno = v_empno;
    DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_ename);
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');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/

包(Package)

包规范

CREATE OR REPLACE PACKAGE emp_pkg AS
    -- 增加工资
    PROCEDURE increase_salary(p_empno IN NUMBER, p_amount IN NUMBER);

    -- 获取员工信息
    FUNCTION get_employee(p_empno IN NUMBER) RETURN VARCHAR2;

    -- 获取部门员工数
    FUNCTION get_dept_count(p_deptno IN NUMBER) RETURN NUMBER;
END emp_pkg;
/

包体

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
    -- 增加工资
    PROCEDURE increase_salary(p_empno IN NUMBER, p_amount IN NUMBER) IS
    BEGIN
        UPDATE emp SET sal = sal + p_amount WHERE empno = p_empno;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END increase_salary;

    -- 获取员工信息
    FUNCTION get_employee(p_empno IN NUMBER) RETURN VARCHAR2 IS
        v_result VARCHAR2(200);
    BEGIN
        SELECT ename || ', ' || job || ', $' || sal INTO v_result
        FROM emp WHERE empno = p_empno;

        RETURN v_result;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 'Employee not found';
        WHEN OTHERS THEN
            RETURN 'Error: ' || SQLERRM;
    END get_employee;

    -- 获取部门员工数
    FUNCTION get_dept_count(p_deptno IN NUMBER) RETURN NUMBER IS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count FROM emp WHERE deptno = p_deptno;
        RETURN v_count;
    END get_dept_count;
END emp_pkg;
/

-- 使用包
BEGIN
    emp_pkg.increase_salary(7369, 500);
    DBMS_OUTPUT.PUT_LINE(emp_pkg.get_employee(7369));
    DBMS_OUTPUT.PUT_LINE('Department count: ' || emp_pkg.get_dept_count(20));
END;
/

动态SQL

DECLARE
    v_sql VARCHAR2(200);
    v_empno emp.empno%TYPE := 7369;
    v_ename emp.ename%TYPE;
BEGIN
    -- 动态SQL语句
    v_sql := 'SELECT ename FROM emp WHERE empno = :empno';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql INTO v_ename USING v_empno;

    DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_ename);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

最佳实践

1. 始终进行异常处理
2. 使用绑定变量而非字符串连接来防止SQL注入
3. 合理使用游标，处理完及时关闭
4. 对于复杂业务逻辑，考虑使用包来组织代码
5. 在存储过程和函数中明确参数模式(IN, OUT, IN OUT)
6. 使用%TYPE和%ROWTYPE属性使代码更健壮
7. 避免在触发器中进行耗时操作
8. 合理使用COMMIT和ROLLBACK

PL/SQL是Oracle数据库的强大工具，熟练掌握它可以大大提高数据库应用的开发效率和性能。