PL/SQL基础 #

PL/SQL特点
├── 完整的编程语言特性
│   ├── 变量和数据类型
│   ├── 条件语句
│   ├── 循环结构
│   └── 异常处理
├── 与SQL集成
│   └── 可以执行SQL语句
├── 块结构
│   └── 匿名块和命名块
└── 高性能
    └── 减少网络往返

-- 1. 减少网络往返
-- 原始：每条INSERT语句一次网络往返
INSERT INTO employees VALUES (1, 'John');
INSERT INTO employees VALUES (2, 'Jane');
INSERT INTO employees VALUES (3, 'Bob');

-- PL/SQL：一次网络往返
BEGIN
    INSERT INTO employees VALUES (1, 'John');
    INSERT INTO employees VALUES (2, 'Jane');
    INSERT INTO employees VALUES (3, 'Bob');
END;
/

-- 2. 模块化开发
-- 可以创建过程、函数、包等

-- 3. 错误处理
-- 统一的异常处理机制

-- 基本匿名块
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;
/

-- 带声明的匿名块
DECLARE
    v_message VARCHAR2(100);
BEGIN
    v_message := 'Hello PL/SQL';
    DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

-- 带异常处理的匿名块
DECLARE
    v_num NUMBER;
BEGIN
    v_num := 10 / 0;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- 完整的PL/SQL块结构
[DECLARE
    -- 声明部分：声明变量、类型、游标、异常等
]
[BEGIN
    -- 执行部分：编写业务逻辑
]
[EXCEPTION
    -- 异常处理部分：处理异常
]
END;

-- 声明部分示例
DECLARE
    v_emp_id NUMBER := 100;
    v_emp_name VARCHAR2(50);
    v_salary employees.salary%TYPE;
    v_count NUMBER;
    c_tax_rate CONSTANT NUMBER := 0.03;

-- 执行部分
BEGIN
    -- 赋值
    v_emp_name := 'John Doe';
    
    -- SQL查询
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = v_emp_id;
    
    -- DML操作
    INSERT INTO employee_log (emp_id, action, log_date)
    VALUES (v_emp_id, 'Query', SYSDATE);
    
    -- 提交
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END;
/

-- 存储过程
CREATE OR REPLACE PROCEDURE update_salary(
    p_emp_id IN NUMBER,
    p_new_salary IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_emp_id;
    
    COMMIT;
END;
/

-- 调用存储过程
EXEC update_salary(100, 15000);

-- 或
BEGIN
    update_salary(p_emp_id => 100, p_new_salary => 15000);
END;
/

-- 存储函数
CREATE OR REPLACE FUNCTION get_employee_name(
    p_emp_id IN NUMBER
) RETURN VARCHAR2 AS
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM employees
    WHERE employee_id = p_emp_id;
    
    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- 调用函数
SELECT get_employee_name(100) FROM DUAL;

-- 基本变量声明
DECLARE
    v_name VARCHAR2(50);           -- 变长字符串
    v_age NUMBER(3);               -- 数值
    v_birth_date DATE;             -- 日期
    v_active BOOLEAN;              -- 布尔值（PL/SQL特有）
BEGIN
    v_name := 'John';
    v_age := 30;
    v_birth_date := TO_DATE('1990-01-01', 'YYYY-MM-DD');
    v_active := TRUE;
END;
/

-- 常量声明
DECLARE
    c_pi CONSTANT NUMBER := 3.14159;
    c_max_count CONSTANT NUMBER := 1000;
BEGIN
    NULL;
END;
/

-- 使用NOT NULL
DECLARE
    v_name VARCHAR2(50) NOT NULL := 'John';
    v_code NUMBER(10) NOT NULL := 100;
BEGIN
    NULL;
END;
/

-- %TYPE属性
DECLARE
    v_emp_id employees.employee_id%TYPE;
    v_emp_name employees.first_name%TYPE;
    v_emp_salary employees.salary%TYPE;
BEGIN
    NULL;
END;
/

-- %ROWTYPE属性
DECLARE
    v_emp_record employees%ROWTYPE;
BEGIN
    SELECT * INTO v_emp_record
    FROM employees
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name);
END;
/

-- RECORD类型
DECLARE
    TYPE t_emp_record IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(100),
        emp_salary NUMBER(10,2)
    );
    
    v_emp t_emp_record;
BEGIN
    v_emp.emp_id := 100;
    v_emp.emp_name := 'John';
    v_emp.emp_salary := 8000;
END;
/

-- 嵌套RECORD
DECLARE
    TYPE t_address IS RECORD (
        street VARCHAR2(100),
        city VARCHAR2(50),
        country VARCHAR2(50)
    );
    
    TYPE t_employee IS RECORD (
        emp_id NUMBER,
        name VARCHAR2(100),
        address t_address
    );
    
    v_emp t_employee;
BEGIN
    v_emp.emp_id := 100;
    v_emp.name := 'John';
    v_emp.address.city := 'Beijing';
END;
/

-- 使用%ROWTYPE简化
DECLARE
    v_emp employees%ROWTYPE;
    v_dept departments%ROWTYPE;
BEGIN
    SELECT * INTO v_emp FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(v_emp.first_name);
END;
/

-- VARRAY
DECLARE
    TYPE t_phone_list IS VARRAY(5) OF VARCHAR2(20);
    
    v_phones t_phone_list := t_phone_list();
BEGIN
    v_phones.EXTEND;
    v_phones(1) := '138-0000-0001';
    v_phones.EXTEND;
    v_phones(2) := '138-0000-0002';
END;
/

-- 嵌套表
DECLARE
    TYPE t_nested_table IS TABLE OF VARCHAR2(50);
    
    v_names t_nested_table := t_nested_table();
BEGIN
    v_names.EXTEND;
    v_names(1) := 'John';
    v_names.EXTEND;
    v_names(2) := 'Jane';
END;
/

-- 关联数组（INDEX BY）
DECLARE
    TYPE t_emp_list IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
    
    v_emps t_emp_list;
BEGIN
    v_emps(1) := 'John';
    v_emps(2) := 'Jane';
    v_emps(100) := 'Bob';
    
    DBMS_OUTPUT.PUT_LINE(v_emps(1));
END;
/

-- 字符串索引的关联数组
DECLARE
    TYPE t_emp_map IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
    
    v_map t_emp_map;
BEGIN
    v_map('CEO') := 'John';
    v_map('CTO') := 'Jane';
    
    DBMS_OUTPUT.PUT_LINE(v_map('CEO'));
END;
/

-- IF-THEN
DECLARE
    v_salary NUMBER := 8000;
BEGIN
    IF v_salary > 10000 THEN
        DBMS_OUTPUT.PUT_LINE('High salary');
    END IF;
END;
/

-- IF-THEN-ELSE
DECLARE
    v_salary NUMBER := 8000;
BEGIN
    IF v_salary > 10000 THEN
        DBMS_OUTPUT.PUT_LINE('High salary');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Normal salary');
    END IF;
END;
/

-- IF-THEN-ELSIF
DECLARE
    v_salary NUMBER := 8000;
BEGIN
    IF v_salary > 20000 THEN
        DBMS_OUTPUT.PUT_LINE('Very High');
    ELSIF v_salary > 10000 THEN
        DBMS_OUTPUT.PUT_LINE('High');
    ELSIF v_salary > 5000 THEN
        DBMS_OUTPUT.PUT_LINE('Medium');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Low');
    END IF;
END;
/

-- CASE语句
DECLARE
    v_dept_id NUMBER := 10;
BEGIN
    CASE v_dept_id
        WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('IT');
        WHEN 20 THEN DBMS_OUTPUT.PUT_LINE('Sales');
        WHEN 30 THEN DBMS_OUTPUT.PUT_LINE('HR');
        ELSE DBMS_OUTPUT.PUT_LINE('Other');
    END CASE;
END;
/

-- 搜索CASE
DECLARE
    v_salary NUMBER := 8000;
BEGIN
    CASE
        WHEN v_salary > 20000 THEN DBMS_OUTPUT.PUT_LINE('Very High');
        WHEN v_salary > 10000 THEN DBMS_OUTPUT.PUT_LINE('High');
        WHEN v_salary > 5000 THEN DBMS_OUTPUT.PUT_LINE('Medium');
        ELSE DBMS_OUTPUT.PUT_LINE('Low');
    END CASE;
END;
/

-- 简单LOOP
DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_counter);
        v_counter := v_counter + 1;
        
        IF v_counter > 5 THEN
            EXIT;
        END IF;
    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;
END;
/

-- FOR LOOP（反向）
BEGIN
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

-- 游标FOR循环
BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name);
    END LOOP;
END;
/

-- CONTINUE（Oracle 11g+）
BEGIN
    FOR i IN 1..5 LOOP
        IF i = 3 THEN
            CONTINUE;
        END IF;
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

-- GOTO语句（谨慎使用）
BEGIN
    GOTO second_line;
    
    DBMS_OUTPUT.PUT_LINE('First line');
    
    <<second_line>>
    DBMS_OUTPUT.PUT_LINE('Second line');
END;
/

-- NULL语句
BEGIN
    IF v_salary > 10000 THEN
        DBMS_OUTPUT.PUT_LINE('High');
    ELSE
        NULL;  -- 什么都不做
    END IF;
END;
/

-- 单行查询
DECLARE
    v_emp_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    SELECT first_name || ' ' || last_name, salary
    INTO v_emp_name, v_salary
    FROM employees
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ': ' || v_salary);
END;
/

-- 使用%ROWTYPE
DECLARE
    v_emp employees%ROWTYPE;
BEGIN
    SELECT * INTO v_emp
    FROM employees
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_emp.first_name);
END;
/

-- 处理异常
DECLARE
    v_emp_name VARCHAR2(100);
BEGIN
    SELECT first_name INTO v_emp_name
    FROM employees
    WHERE employee_id = 99999;  -- 不存在的员工
    
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
    v_count NUMBER;
    v_avg_salary NUMBER;
BEGIN
    SELECT COUNT(*), AVG(salary)
    INTO v_count, v_avg_salary
    FROM employees
    WHERE department_id = 10;
    
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count || ', Avg: ' || v_avg_salary);
END;
/

-- INSERT
DECLARE
    v_emp_id NUMBER := 1000;
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, hire_date, job_id)
    VALUES (v_emp_id, 'John', 'Doe', SYSDATE, 'IT_PROG');
    
    COMMIT;
END;
/

-- UPDATE
DECLARE
    v_new_salary NUMBER := 15000;
BEGIN
    UPDATE employees
    SET salary = v_new_salary
    WHERE employee_id = 100;
    
    IF SQL%ROWCOUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No rows updated');
    ELSE
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
    END IF;
    
    COMMIT;
END;
/

-- DELETE
DECLARE
    v_emp_id NUMBER := 1000;
BEGIN
    DELETE FROM employees
    WHERE employee_id = v_emp_id;
    
    COMMIT;
END;
/

-- RETURNING子句
DECLARE
    v_emp_name VARCHAR2(100);
    v_new_id NUMBER;
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, hire_date, job_id)
    VALUES (seq_emp.NEXTVAL, 'Jane', 'Smith', SYSDATE, 'IT_PROG')
    RETURNING employee_id, first_name || ' ' || last_name 
    INTO v_new_id, v_emp_name;
    
    DBMS_OUTPUT.PUT_LINE('Inserted: ' || v_new_id || ' - ' || v_emp_name);
    COMMIT;
END;
/

-- 事务控制
BEGIN
    -- 业务操作1
    UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
    
    -- 业务操作2
    UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
    
    -- 提交
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        -- 回滚
        ROLLBACK;
        RAISE;
END;
/

-- 保存点
BEGIN
    UPDATE employees SET salary = salary + 1000 WHERE department_id = 10;
    SAVEPOINT sp1;
    
    UPDATE employees SET salary = salary + 500 WHERE department_id = 20;
    
    -- 回滚到保存点
    ROLLBACK TO SAVEPOINT sp1;
    
    COMMIT;
END;
/

-- 常见预定义异常
DECLARE
    v_name VARCHAR2(100);
BEGIN
    -- NO_DATA_FOUND
    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');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Too many rows');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- 其他预定义异常
-- INVALID_NUMBER：无效数字
-- DUP_VAL_ON_INDEX：唯一约束冲突
-- VALUE_ERROR：值错误
-- ZERO_DIVIDE：除零错误
-- LOGIN_DENIED：登录失败
-- NOT_LOGGED_ON：未登录

-- 声明和使用自定义异常
DECLARE
    e_invalid_salary EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);
    
    v_salary NUMBER := -100;
BEGIN
    IF v_salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;
    
    UPDATE employees SET salary = v_salary WHERE employee_id = 100;
    COMMIT;
EXCEPTION
    WHEN e_invalid_salary THEN
        DBMS_OUTPUT.PUT_LINE('Invalid salary');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END;
/

-- 使用RAISE
DECLARE
    e_not_found EXCEPTION;
BEGIN
    RAISE e_not_found;
    
    DBMS_OUTPUT.PUT_LINE('This will not execute');
EXCEPTION
    WHEN e_not_found THEN
        DBMS_OUTPUT.PUT_LINE('Exception raised');
END;
/

-- 异常传播
DECLARE
    v_name VARCHAR2(100);
BEGIN
    BEGIN
        SELECT first_name INTO v_name
        FROM employees
        WHERE employee_id = 99999;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Inner exception caught');
            RAISE;  -- 重新抛出
    END;
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Outer exception caught: ' || SQLERRM);
END;
/

-- SQLCODE和SQLERRM
DECLARE
    v_error_code NUMBER;
    v_error_message VARCHAR2(1000);
BEGIN
    SELECT first_name FROM employees WHERE 1=0;
    
EXCEPTION
    WHEN OTHERS THEN
        v_error_code := SQLCODE;
        v_error_message := SQLERRM;
        
        DBMS_OUTPUT.PUT_LINE('Error Code: ' || v_error_code);
        DBMS_OUTPUT.PUT_LINE('Error Message: ' || v_error_message);
END;
/

DECLARE
    v_num NUMBER := 123.456;
BEGIN
    DBMS_OUTPUT.PUT_LINE('ABS: ' || ABS(-10));
    DBMS_OUTPUT.PUT_LINE('ROUND: ' || ROUND(123.456, 2));
    DBMS_OUTPUT.PUT_LINE('TRUNC: ' || TRUNC(123.456, 1));
    DBMS_OUTPUT.PUT_LINE('MOD: ' || MOD(10, 3));
    DBMS_OUTPUT.PUT_LINE('POWER: ' || POWER(2, 3));
    DBMS_OUTPUT.PUT_LINE('SQRT: ' || SQRT(16));
END;
/

DECLARE
    v_str VARCHAR2(50) := '  Hello Oracle  ';
BEGIN
    DBMS_OUTPUT.PUT_LINE('LENGTH: ' || LENGTH(v_str));
    DBMS_OUTPUT.PUT_LINE('UPPER: ' || UPPER(v_str));
    DBMS_OUTPUT.PUT_LINE('LOWER: ' || LOWER(v_str));
    DBMS_OUTPUT.PUT_LINE('TRIM: ' || TRIM(v_str));
    DBMS_OUTPUT.PUT_LINE('SUBSTR: ' || SUBSTR(v_str, 1, 5));
    DBMS_OUTPUT.PUT_LINE('INSTR: ' || INSTR(v_str, 'Oracle'));
    DBMS_OUTPUT.PUT_LINE('REPLACE: ' || REPLACE(v_str, 'Oracle', 'PL/SQL'));
END;
/

DECLARE
    v_date DATE := SYSDATE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('SYSDATE: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD'));
    DBMS_OUTPUT.PUT_LINE('ADD_MONTHS: ' || TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYY-MM-DD'));
    DBMS_OUTPUT.PUT_LINE('MONTHS_BETWEEN: ' || MONTHS_BETWEEN(SYSDATE, SYSDATE - 30));
    DBMS_OUTPUT.PUT_LINE('TRUNC: ' || TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD'));
    DBMS_OUTPUT.PUT_LINE('NEXT_DAY: ' || TO_CHAR(NEXT_DAY(SYSDATE, 'MONDAY'), 'YYYY-MM-DD'));
END;
/