【Oracle入门到删库跑路-05】核心技能：PL/SQL编程基础1.1 PL/SQL概述 PL/SQL（Procedu

1.1 PL/SQL概述

PL/SQL（Procedural Language/SQL）是Oracle数据库的过程化SQL语言扩展，它结合了SQL的数据操作能力和过程化编程的控制结构。

PL/SQL的特点

块结构：代码组织成逻辑块
过程化控制：支持条件判断、循环等控制结构
错误处理：提供异常处理机制
数据类型丰富：支持多种数据类型
面向对象：支持面向对象编程特性

PL/SQL块结构

DECLARE
  -- 声明部分（可选）
  variable_name datatype [:= initial_value];
BEGIN
  -- 执行部分（必需）
  -- SQL语句和PL/SQL语句
EXCEPTION
  -- 异常处理部分（可选）
  WHEN exception_name THEN
    -- 异常处理代码
END;
/

1.2 变量和数据类型

声明变量

DECLARE
  -- 基本数据类型
  v_employee_id NUMBER(6) := 100;
  v_first_name VARCHAR2(20) := 'Steven';
  v_last_name VARCHAR2(25);
  v_salary NUMBER(8,2) DEFAULT 10000;
  v_hire_date DATE := SYSDATE;
  
  -- 使用%TYPE属性
  v_emp_salary employees.salary%TYPE;
  v_emp_name employees.first_name%TYPE;
  
  -- 使用%ROWTYPE属性
  v_employee employees%ROWTYPE;
BEGIN
  -- 给变量赋值
  v_last_name := 'King';
  v_emp_salary := 25000;
  
  -- 输出变量值
  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_first_name || ' ' || v_last_name);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_emp_salary);
END;
/

常量声明

DECLARE
  -- 声明常量
  c_tax_rate CONSTANT NUMBER := 0.08;
  c_company_name CONSTANT VARCHAR2(50) := 'Oracle Corporation';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Tax Rate: ' || c_tax_rate);
  DBMS_OUTPUT.PUT_LINE('Company: ' || c_company_name);
END;
/

LOB数据类型

DECLARE
  v_clob_data CLOB;
  v_blob_data BLOB;
BEGIN
  -- LOB操作示例
  SELECT resume INTO v_clob_data FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('Resume Length: ' || DBMS_LOB.GETLENGTH(v_clob_data));
END;
/

1.3 控制结构

条件控制

IF语句

DECLARE
  v_salary NUMBER := 15000;
  v_grade CHAR(1);
BEGIN
  -- 简单IF语句
  IF v_salary > 20000 THEN
    DBMS_OUTPUT.PUT_LINE('High Salary');
  END IF;
  
  -- IF-ELSE语句
  IF v_salary > 20000 THEN
    v_grade := 'A';
  ELSE
    v_grade := 'B';
  END IF;
  DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
  
  -- IF-ELSIF-ELSE语句
  IF v_salary > 20000 THEN
    v_grade := 'A';
  ELSIF v_salary > 15000 THEN
    v_grade := 'B';
  ELSIF v_salary > 10000 THEN
    v_grade := 'C';
  ELSE
    v_grade := 'D';
  END IF;
  DBMS_OUTPUT.PUT_LINE('Final Grade: ' || v_grade);
END;
/

CASE语句

DECLARE
  v_job_id VARCHAR2(10) := 'IT_PROG';
  v_department VARCHAR2(30);
BEGIN
  -- 简单CASE语句
  CASE v_job_id
    WHEN 'IT_PROG' THEN
      v_department := 'Information Technology';
    WHEN 'SA_REP' THEN
      v_department := 'Sales';
    WHEN 'HR_REP' THEN
      v_department := 'Human Resources';
    ELSE
      v_department := 'Other';
  END CASE;
  DBMS_OUTPUT.PUT_LINE('Department: ' || v_department);
  
  -- 搜索CASE语句
  CASE
    WHEN v_job_id LIKE 'IT%' THEN
      v_department := 'Information Technology';
    WHEN v_job_id LIKE 'SA%' THEN
      v_department := 'Sales';
    WHEN v_job_id LIKE 'HR%' THEN
      v_department := 'Human Resources';
    ELSE
      v_department := 'Other';
  END CASE;
  DBMS_OUTPUT.PUT_LINE('Search Department: ' || v_department);
END;
/

循环控制

LOOP循环

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('While Counter: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;
END;
/

FOR循环

BEGIN
  -- 基本FOR循环
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('For Counter: ' || i);
  END LOOP;
  
  -- 反向FOR循环
  FOR i IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Reverse Counter: ' || i);
  END LOOP;
  
  -- 使用游标FOR循环
  FOR emp_rec IN (SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 60) LOOP
    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;
END;
/

1.4 游标

显式游标

DECLARE
  -- 声明游标
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = 60;
    
  -- 声明记录变量
  v_emp_record emp_cursor%ROWTYPE;
BEGIN
  -- 打开游标
  OPEN emp_cursor;
  
  -- 获取数据
  LOOP
    FETCH emp_cursor INTO v_emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_record.first_name || ' ' || 
                         v_emp_record.last_name || ', Salary: ' || v_emp_record.salary);
  END LOOP;
  
  -- 关闭游标
  CLOSE emp_cursor;
END;
/

游标FOR循环

DECLARE
  CURSOR dept_cursor IS
    SELECT department_id, department_name
    FROM departments
    WHERE department_id IN (60, 90, 100);
BEGIN
  -- 使用游标FOR循环
  FOR dept_rec IN dept_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('Department: ' || dept_rec.department_name);
    
    -- 嵌套游标
    FOR emp_rec IN (SELECT first_name, last_name FROM employees WHERE department_id = dept_rec.department_id) LOOP
      DBMS_OUTPUT.PUT_LINE('  Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
    END LOOP;
  END LOOP;
END;
/

带参数的游标

DECLARE
  CURSOR emp_cursor(p_dept_id NUMBER) IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id;
BEGIN
  -- 使用带参数的游标
  FOR emp_rec IN emp_cursor(60) LOOP
    DBMS_OUTPUT.PUT_LINE('IT Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;
  
  FOR emp_rec IN emp_cursor(90) LOOP
    DBMS_OUTPUT.PUT_LINE('Executive: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;
END;
/

1.5 异常处理

预定义异常

DECLARE
  v_employee_id NUMBER := 9999;  -- 不存在的员工ID
  v_salary NUMBER;
BEGIN
  SELECT salary INTO v_salary
  FROM employees
  WHERE employee_id = v_employee_id;
  
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || v_employee_id);
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Too many rows returned');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

用户定义异常

DECLARE
  v_salary NUMBER := -5000;
  e_invalid_salary EXCEPTION;  -- 声明自定义异常
BEGIN
  IF v_salary < 0 THEN
    RAISE e_invalid_salary;  -- 抛出自定义异常
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('Valid salary: ' || v_salary);
  
EXCEPTION
  WHEN e_invalid_salary THEN
    DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be negative');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

RAISE_APPLICATION_ERROR

CREATE OR REPLACE PROCEDURE update_salary(
  p_employee_id NUMBER,
  p_new_salary NUMBER
) IS
  v_current_salary NUMBER;
BEGIN
  -- 检查员工是否存在
  SELECT salary INTO v_current_salary
  FROM employees
  WHERE employee_id = p_employee_id;
  
  -- 验证薪资
  IF p_new_salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
  END IF;
  
  IF p_new_salary > 50000 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Salary exceeds maximum limit');
  END IF;
  
  -- 更新薪资
  UPDATE employees
  SET salary = p_new_salary
  WHERE employee_id = p_employee_id;
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Salary updated successfully');
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20003, 'Employee not found');
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

1.6 本章小结

本章介绍了PL/SQL编程的基础知识，包括PL/SQL块结构、变量声明、控制结构、游标使用和异常处理等内容。掌握这些基础知识是进行更复杂PL/SQL编程的前提。

练习题

编写一个PL/SQL块，声明几个不同类型的变量并赋值，然后输出这些变量的值
使用IF-ELSIF-ELSE语句根据员工薪资等级输出不同的评价
编写一个循环程序，计算1到100的和
创建一个游标，遍历employees表中的数据并输出员工信息
编写一个带有异常处理的程序，处理NO_DATA_FOUND和TOO_MANY_ROWS异常
创建一个存储过程，根据员工ID更新薪资，并添加适当的异常处理