PL/SQL语句中的函数、游标、及存储过程的应用

在PL/SQL（ProceduralLanguage/SQL）中，游标、函数和存储过程是重要的编程结构，能够极大地增强Oracle数据库的处理能力。

下面分别介绍它们的语法和应用。

---

1.游标（Cursor）

游标简介 游标用于在PL/SQL代码中逐行处理SQL查询结果集。游标有两种类型：显式游标和隐式游标。

1.1 显式游标

显式游标语法及示例显式游标需要手动声明、打开、提取数据、关闭。

-- 声明游标
CURSOR cursor_name IS
  SELECT column1, column2
  FROM table_name
  WHERE condition;

-- 示例
DECLARE
  CURSOR emp_cursor IS
    SELECT emp_id, emp_name
    FROM employees
    WHERE department_id = 10;
  emp_record emp_cursor%ROWTYPE;
BEGIN
  -- 打开游标
  OPEN emp_cursor;
  LOOP
    -- 提取数据到emp_record
    FETCH emp_cursor INTO emp_record;
    -- 退出循环条件
    EXIT WHEN emp_cursor%NOTFOUND;
    -- 处理数据
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.emp_id || ', Name: ' || emp_record.emp_name);
  END LOOP;
  -- 关闭游标
  CLOSE emp_cursor;
END;
/

1.2隐式游标

隐式游标由Oracle自动处理，主要用于SELECT INTO语句和DML操作。

DECLARE
  emp_name employees.emp_name%TYPE;
BEGIN
  SELECT emp_name
  INTO emp_name
  FROM employees
  WHERE emp_id = 100;

  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found with ID 100');
END;
/

1.3游标for循环

在PL/SQL中，FOR循环是一种简便的方式来遍历游标返回的结果集。使用FOR循环可以自动处理游标的打开、提取和关闭操作，无需显式地编写这些步骤。这种方式简化了代码，提高了可读性和可维护性。

基本语法：

FOR record IN cursor_name LOOP
  -- 处理record
END LOOP;

示例：

假设我们有一个employees表，包含员工的ID和名字。我们希望通过一个游标循环来遍历所有员工，并输出他们的ID和名字。

DECLARE
  -- 声明游标
  CURSOR emp_cursor IS
    SELECT emp_id, emp_name
    FROM employees;
BEGIN
  -- 使用游标FOR循环
  FOR emp_record IN emp_cursor LOOP
    -- 处理游标返回的每一行
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.emp_id || ', Name: ' || emp_record.emp_name);
  END LOOP;
END;
/

在这个示例中，emp_cursor游标定义了一个查询，选择employees表中的emp_id和emp_name。FOR循环会自动打开游标，逐行提取数据并将每一行的数据放入emp_record中。循环体内的代码可以直接访问emp_record中的各个字段。

使用隐式游标 FOR 循环

在某些情况下，可以省略游标的显式声明，直接在FOR循环中使用一个SELECT语句。这种方式称为隐式游标FOR循环。

BEGIN
  -- 直接在FOR循环中使用SELECT语句
  FOR emp_record IN (SELECT emp_id, emp_name FROM employees) LOOP
    -- 处理每一行数据
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.emp_id || ', Name: ' || emp_record.emp_name);
  END LOOP;
END;
/

在这个示例中，FOR循环直接包含一个SELECT查询。每次循环迭代时，emp_record会自动获取查询结果集中的一行数据。

游标 FOR 循环的优点

1. 简化代码：自动处理游标的打开、提取和关闭操作，无需显式地编写这些步骤。

2. 提高可读性：减少了代码量，使程序更简洁易读。

3. 避免错误：通过自动管理游标生命周期，减少了由于忘记关闭游标而引发的资源泄漏问题。

   完整示例

   为了更全面地展示游标 FOR 循环的用法，下面是一个稍微复杂的示例，包含更多业务逻辑。

   假设我们有一个任务是将所有部门ID为10的员工的工资提高10%。

   DECLARE
     -- 声明游标
     CURSOR emp_cursor IS
       SELECT emp_id, salary
       FROM employees
       WHERE department_id = 10;
   BEGIN
     -- 使用游标FOR循环
     FOR emp_record IN emp_cursor LOOP
       -- 更新每个员工的工资
       UPDATE employees
       SET salary = salary * 1.10
       WHERE emp_id = emp_record.emp_id;
       
       -- 输出更新信息
       DBMS_OUTPUT.PUT_LINE('Updated Salary for Employee ID: ' || emp_record.emp_id);
     END LOOP;
     
     -- 提交事务
     COMMIT;
   END;
   /
   
   

   在这个示例中，我们通过游标 FOR 循环遍历所有部门ID为10的员工记录，并更新每个员工的工资。循环体内的UPDATE语句根据emp_record中的emp_id进行更新，并输出更新信息。最后，我们提交事务以保存更改。

游标 FOR 循环的语法和示例

2. 函数（Function）

   函数是PL/SQL中的子程序，用于执行特定任务并返回一个值。函数可以在SQL语句中调用。

函数语法及示例

-- 创建函数
CREATE OR REPLACE FUNCTION function_name (param1 datatype, param2 datatype)
RETURN return_datatype 
IS
  -- 声明部分
  variable_name datatype;
BEGIN
  -- 执行部分
  -- 逻辑处理
  RETURN result_value;
EXCEPTION
  -- 异常处理
  WHEN exception_name THEN
    -- 处理代码
END function_name;
/

-- 示例
CREATE OR REPLACE FUNCTION get_employee_name (p_emp_id NUMBER)
RETURN VARCHAR2 
IS
  v_emp_name employees.emp_name%TYPE;
BEGIN
  SELECT emp_name INTO v_emp_name
  FROM employees
  WHERE emp_id = p_emp_id;

  RETURN v_emp_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'No employee found';
END get_employee_name;
/

-- 调用函数
DECLARE
  v_name VARCHAR2(100);
BEGIN
  v_name := get_employee_name(100);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
/

---

存储过程

存储过程是PL/SQL中的子程序，用于执行一系列任务，但不返回值。存储过程可以包含输入、输出和输入输出参数。

--语法

-- 创建存储过程
CREATE OR REPLACE PROCEDURE procedure_name (param1 IN datatype, param2 OUT datatype, param3 IN OUT datatype) --默认为输入参数
IS
  -- 声明部分
  variable_name datatype;
BEGIN
  -- 执行部分
  -- 逻辑处理
EXCEPTION
  -- 异常处理
  WHEN exception_name THEN
    -- 处理代码
END procedure_name;
/

-- 示例
CREATE OR REPLACE PROCEDURE update_employee_salary (
  p_emp_id IN employees.emp_id%TYPE,
  p_new_salary IN employees.salary%TYPE
) IS

BEGIN
  UPDATE employees
  SET salary = p_new_salary
  WHERE emp_id = p_emp_id;

  IF SQL%ROWCOUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No employee found with ID ' || p_emp_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salary updated for employee ID ' || p_emp_id);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END update_employee_salary;
/

-- 调用存储过程
BEGIN
  update_employee_salary(100, 8000);
END;
/

---

应用场景

游标：适用于需要逐行处理查询结果集的场景，如批量数据处理、数据迁移等。

函数：适用于需要返回计算结果或查询结果的场景，如计算公式、数据查询等。函数可以嵌入在SQL语句中使用。

存储过程：适用于执行一系列数据库操作的场景，如批量更新、数据导入导出等。存储过程可以包含复杂的业务逻辑，并且可以接受参数进行动态处理。

通过使用游标、函数和存储过程，可以提高PL/SQL程序的灵活性、可维护性和性能。