在 PL/SQL 中将参数传递给游标

2025年4月6日 | 阅读 3 分钟

在本文中，我们将了解如何在 PL/SQL 中向游标传递参数。

让我们举个例子

CURSOR dept10_cur IS
Select eno, name, job from Emp_data
Where dtno = 10;

此 dept10_cur 游标的活动数据集将仅包含 dtno = 10 的那些行。现在假设有人想要检索 dtno = 10，而另一个人要求 dtno = 30。因此您必须为每个部门编号编写单独的游标，这会增加开销。这就是需要参数化游标的地方。

声明参数化游标的语法是

CURSOR <cursorname> [(var_namesdatatype)] IS
{SELECT statement};

您可以在游标中传递多个参数。声明后，您可以打开参数化游标，如下所示

参数化游标示例

示例 1：编写一个 PL/SQL 程序来打印姓名及其对应的员工信息。

SET SERVEROUTPUT ON;
DECLARE
   CURSOR dept_cursor (N NUMBER) IS
      SELECT eno, name from Emp_data WHERE deptno >N;
      Name Emp_data.Ename%TYPE;
        Ecode Emp_data.Empno%TYPE;
        Num Number;
BEGIN
  Num := &Num;
   OPEN dept_cursor (Num);
   LOOP
      FETCH dept_cursor INTO Ecode, Name;
      EXIT WHEN dept_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Ecode || '  ' || Name);
   END LOOP;
   CLOSE dept_cursor;
END;
/

说明

执行上述代码后，它将提示您输入参数 Num 的值。然后，此值将作为参数传递到 dept_cursor 的 OPEN 语句中，该语句将替换游标声明中的 n。然后根据传递的参数值获取记录。现在您可以运行时输入 deptno 的值。

优选将参数传递给游标，因为

使游标更可重用： 无需在程序中更改查询的 WHERE 子句中的值来选择特定信息。您可以使用一个参数，然后每次打开游标时将不同的值传递给 WHERE 子句。
避免作用域问题： 如果您的程序具有嵌套块，则可以在顶层块中定义游标，并在任何子块中使用它，这些子块在那些局部块中定义了变量。

示例 2：编写一个 PL/SQL 程序以打印部门名称及其对应的员工信息。确保不显示带有每条记录的部门名称。

SET SERVEROUTPUT ON;
DECLARE
   CURSOR dept_cur IS
      SELECT deptno, dname from dept;
   CURSOR Emp_cur(Dno number) IS
      SELECT * from emp where deptno = Dno;
        Dcode dept.deptno%TYPE;
        Name dept.dname%TYPE;
BEGIN
     OPEN dept_cur;
   LOOP
      FETCH dept_cur INTO dcode, Name;
      EXIT WHEN dept_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Name);
DBMS_OUTPUT.PUT_LINE('  ');
For E in Emp_cur (Dcode) LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(E.EName, 10) || '  ' || RPAD(E.JOB,10) ||  '  ' E.sal);
   END LOOP;
   END LOOP;
   CLOSE dept_cur;
END;
/

说明

执行上述代码后，它将显示使用 Dept 表声明的每个部门的员工姓名和工资，并使用 Emp 表声明了“Emp_cur”游标。在“Emp_cur”游标中声明了 number 数据类型的变量“Dno”。属于特定部门编号的员工信息，因此从 Dept_cur 游标获取的 Deptno 将作为参数传递到“Emp_cur”游标的 Dno 变量中。这将获取与部门编号匹配的记录。 RPAD 函数有助于保留 Ename、Job 等列的空间，从而使显示更清晰。

示例 3：编写一个 PL/SQL 程序以列出前 n 个薪水及其对应的员工姓名。考虑多个员工拥有相同薪水的情况。如果列出一个人的名字，那么所有具有相同薪水的人都应该被列出。

SET SERVEROUTPUT ON;
DECLARE
   CURSOR sal_cur IS
      SELECT distinct sal from emp order by sal desc;
   CURSOR Emp_cur(S number) IS
      SELECT ename from emp where sal = S;
        Name  Emp.ename%TYPE;
        Salary Emp.sal%TYPE;
        N number;
       BEGIN
       N : = &N;
      OPEN sal_cur;
   LOOP
      FETCH sal_cur INTO salary;
      EXIT WHEN sal_cur %ROWCOUNT>N OR sal_cur%NOTFOUND;
   OPEN Emp_cur(Salary);
    LOOP
      FETCH Emp_cur INTO  Name;
      EXIT WHEN Emp_cur % NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(RPAD(Name, 10) || '  ' ||  Salary);
END LOOP;
   CLOSE Emp_cur;
   END LOOP;
   CLOSE sal_cur;
END;
/

说明

执行上述代码后，它将提示您输入参数 N 的值。首先打开 sal_cur，然后执行循环，然后打开“Emp_cur”游标，它将显示前 N 个员工的薪水。如果几个员工的薪水相同，则将全部列出。最后，游标关闭，程序结束。

下一个主题PL/SQL 中的空语句