oracle游标使用大全3

### Oracle游标使用详解 #### 一、Oracle游标简介 在Oracle数据库中，游标是一种用于处理查询结果集的强大工具。它允许用户通过循环结构逐行读取数据，并进行相应的处理。游标分为显式游标和隐式游标两种类型。 - **显式游标**：需要显式声明并在程序中明确打开、读取和关闭。 - **隐式游标**：由PL/SQL自动创建并管理，通常用于简单的SELECT INTO语句。 本文将详细介绍Oracle中显式游标的使用方法及其应用场景。 #### 二、显式游标基本语法 显式游标的定义和使用遵循以下步骤： 1. **声明游标**：定义游标及其查询语句。 2. **打开游标**：执行查询并准备读取结果。 3. **读取游标**：逐行读取查询结果。 4. **关闭游标**：释放资源。 #### 三、显式游标示例 下面通过一个具体的例子来展示如何使用显式游标： ```plsql DECLARE CURSOR c_dept IS SELECT deptno, dname FROM dept ORDER BY deptno; CURSOR c_emp(p_dept VARCHAR2) IS SELECT ename, salary FROM emp WHERE deptno = p_dept ORDER BY ename; v_tot_salary EMP.SALARY%TYPE; BEGIN FOR r_dept IN c_dept LOOP DBMS_OUTPUT.PUT_LINE('Department: ' || r_dept.deptno || '-' || r_dept.dname); v_tot_salary := 0; FOR r_emp IN c_emp(r_dept.deptno) LOOP DBMS_OUTPUT.PUT_LINE('Name: ' || r_emp.ename || ' Salary: ' || r_emp.salary); v_tot_salary := v_tot_salary + r_emp.salary; END LOOP; DBMS_OUTPUT.PUT_LINE('Total Salary for dept: ' || v_tot_salary); END LOOP; END; ``` 在这个示例中： - 定义了两个游标`c_dept`和`c_emp`，分别用于查询部门信息和员工信息。 - 使用`FOR`循环遍历部门信息，再对每个部门内部的员工信息进行遍历处理。 #### 四、使用FOR循环简化游标操作 除了传统的游标声明方式外，还可以使用更简洁的`FOR`循环来替代显式游标声明： ```plsql DECLARE v_tot_salary EMP.SALARY%TYPE; BEGIN FOR r_dept IN (SELECT deptno, dname FROM dept ORDER BY deptno) LOOP DBMS_OUTPUT.PUT_LINE('Department: ' || r_dept.deptno || '-' || r_dept.dname); v_tot_salary := 0; FOR r_emp IN (SELECT ename, salary FROM emp WHERE deptno = r_dept.deptno ORDER BY ename) LOOP DBMS_OUTPUT.PUT_LINE('Name: ' || r_emp.ename || ' Salary: ' || r_emp.salary); v_tot_salary := v_tot_salary + r_emp.salary; END LOOP; DBMS_OUTPUT.PUT_LINE('Total Salary for dept: ' || v_tot_salary); END LOOP; END; ``` 这种方式避免了显式声明游标，使得代码更加简洁。 #### 五、更新和删除游标中的数据 除了读取数据之外，游标还可以用于更新或删除数据。为了实现这一功能，需要使用`FOR UPDATE`子句锁定数据行。 例如，在下面的例子中，我们根据员工的薪资调整其佣金比例： ```plsql DECLARE CURSOR c1 IS SELECT empno, salary FROM emp WHERE comm IS NULL FOR UPDATE OF comm; v_comm NUMBER(10, 2); BEGIN FOR r1 IN c1 LOOP IF r1.salary < 500 THEN v_comm := r1.salary * 0.25; ELSIF r1.salary < 1000 THEN v_comm := r1.salary * 0.20; ELSIF r1.salary < 3000 THEN v_comm := r1.salary * 0.15; ELSE v_comm := r1.salary * 0.12; END IF; UPDATE emp SET comm = v_comm WHERE CURRENT OF c1; END LOOP; END; ``` 这里需要注意的是： - `FOR UPDATE OF comm`确保只有`comm`列被锁定，其他列不受影响。 - 使用`WHERE CURRENT OF c1`指定更新当前游标所指向的记录。 #### 六、总结 本文详细介绍了Oracle中游标的使用方法，包括基本语法、简化使用方式以及如何利用游标进行数据更新和删除等高级操作。通过这些知识点的学习，读者可以更好地掌握如何在实际开发中运用游标来提高数据处理效率和灵活性。