AntDB 数据库Oracle兼容-存储过程6

tocata 2024-08-19

126

静态游标

可以设置一个游标来封装一个查询，然后每一次从查询结果集中读取一条记录。而不是一次执行整个查询过程，这种方式允许创建如下 SPL 程序逻辑，一次从结果集中读取一条记录，对这条记录的数据进行处理，然后读取下面一条记录，重复相同的过程。

游标通常在 FOR 或者 WHILE 循环中使用。在 SPL 程序逻辑中应该包含条件测试，来检测结果集末尾是否已达到，这样程序可以退出循环。

示例：

准备测试数据：

CREATE TABLE emp (
empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7319,'JACK','CLERK',7922,'18-DEC-80',800,null,20);

声明游标

游标必须在 SPL 程序的声明部分首先声明，然后才能使用。

语法：

CURSOR name IS query;

name 是一个标识符，用来在程序中引用的游标以及相关的结果集。query 是一条 SELECT 命令，用来决定游标能够取回的结果集。

下面是一些游标声明的示例：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	CURSOR emp_cur_1 IS SELECT * FROM emp;
	CURSOR emp_cur_2 IS SELECT empno, ename FROM emp;
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
...
END;

/
\set PLSQL_MODE off

打开一个游标

首先必须打开游标，才能用它获取记录。这个操作是由 OPEN 语句完成的。

语法：

OPEN name;

name 是一个标识符，表示在 SPL 程序中的声明部分已经声明好的一个游标。不应使用 OPEN 语句打开一个已经存在并已打开的游标。

下面显示了一个与游标声明相对应的 OPEN 语句：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
...
END;
/
\set PLSQL_MODE off

从游标中获取记录

当打开游标后，可以使用 FETCH 语句获取游标结果集中的记录。

语法：

FETCH name INTO { record | variable [, variable_2 ]... };

name 是一个已经打开游标的标识符。record 是已经定义好记录（例如，使用到了 table@ROWTYPE）的标识符。variable，variable_2...是从记录中得到字段数据的 SPL 程序变量。在记录中的字段或者 variable，variable_2...必须和游标声明中查询语句的 SELECT 列表字段的数量，顺序相匹配。SELECT 列表中字段的数据类型和记录中的字段或者 variable，variable2..的数据类型必须是匹配或者能够隐式地进行自动转换。

注意：这里另外有一种 FETCH INTO 语句的使用方法，在 FETCH INTO 语句中使用 BULKCOLLECT 子句，可以允许一次将多条记录返回到一个集合中。

下面是 FETCH 语句的使用方法：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_empno	NUMBER(4);
	v_ename	VARCHAR2(10);
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
	FETCH emp_cur_3 INTO v_empno, v_ename;
	...
END;
/
\set PLSQL_MODE off

可以在 SPL 程序中将变量的类型声明为 %TYPE，这样如果数据表中列的属性发生变化，就不需要在程序中显式地改变的目标变量的数据类型，因为 %TYPE 可以自动的获取指定列的数据类型。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_empno	emp.empno%TYPE;
	v_ename	emp.ename%TYPE;
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
	FETCH emp_cur_3 INTO v_empno, v_ename;
	...
END;

/
\set PLSQL_MODE off

如果表中的所有列都以在表中定义的顺序获取，那么可以用 %ROWTYPE 定义一个记录，这样 FETCH 语句可以将获取数据存放在这个记录中，可以使用'.'号来访问记录中的每个字段。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
	OPEN emp_cur_1;
	FETCH emp_cur_1 INTO v_emp_rec;
	DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
	DBMS_OUTPUT.PUT_LINE('Employee Name	: ' || v_emp_rec.ename);
	...
END;
/
\set PLSQL_MODE off

关闭游标

当所有需要的记录都从游标结果集中获取出来后，就必须关闭游标。游标关闭后，将无法访问结果集。

语法：

CLOSE name;

name 是当前打开的游标的标识符。关闭游标后，不应该再次进行相同的关闭操作。在关闭游标后，可以在这个游标上再次使用 OPEN 语句。当使用 FETCH 语句获取新结果集中记录后，查询结果集将重新创建。

下面的示例演示了 CLOSE 语句的使用方法：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cur_1;
	FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
	DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_emp_rec.ename);
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

下面是执行完上面的存储过程后的输出结果。在结果集中雇员编号为 7369，姓名是 SMITH 的记录是第一条记录：

antdb=# SELECT cursor_example();
NOTICE:  Employee Number: 7319
NOTICE:  Employee Name : JACK
 CURSOR_EXAMPLE
----------------

(1 row)

在游标中使用 %ROWTYPE

通过使用 %ROWTYPE 属性，可以定义一个记录，这个记录包含的字段与游标或者游标变量中所获取的所有列一一对应。每个字段的数据类型就是所对应列的数据类型。%ROWTYPE 属性以游标名或者游标型变量的名称为前缀。

语法：

record cursor%ROWTYPE;

record 是分配给变量的标识符。cursor 是在当前范围内显式声明的游标。

在下面的这个示例中演示了如何使用带有 %ROWTYPE 的游标获取雇员所在的部门：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_info()
IS
	CURSOR empcur IS SELECT ename, deptno FROM emp ORDER BY 2;
	myvar empcur%ROWTYPE;
BEGIN
	OPEN empcur;
	LOOP
		FETCH empcur INTO myvar.ename,myvar.deptno;
		EXIT WHEN empcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE( myvar.ename || ' works in department '|| myvar.deptno );
	END LOOP;
	CLOSE empcur;
END;
/
\set PLSQL_MODE off

下面是这个过程的输出结果：

antdb=# SELECT emp_info();
NOTICE:  SMITH works in department 20
NOTICE:  JACK works in department 30
NOTICE:  JANE works in department 40
 EMP_INFO
----------

(1 row)

游标属性

每个游标都有一个属性集，程序可以使用这些属性去检测游标的状态。这些属性是 %ISOPEN，%FOUND， %NOTFOUND 和 %ROWCOUNT，在下面的章节中将会详细介绍这些属性。

%ISOPEN

属性 %ISOPEN 用于测试游标是否处于打开状态。

语法：

cursor_name%ISOPEN

cursor_name 是游标的名称，如果游标的属性 %ISOPEN 返回为”true”，那么表示游标是处于打开状态，反之，表示该游标处于未打开状态。

下面是一个使用属性 %ISOPEN 的示例：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_1()
IS
	CURSOR empcur IS SELECT ename, deptno FROM emp ORDER BY 2;
BEGIN
	OPEN empcur;
	
	IF empcur%ISOPEN THEN
		DBMS_OUTPUT.PUT_LINE( 'empcur is open ');
	ELSE
		DBMS_OUTPUT.PUT_LINE( 'empcur is close ');
	END IF;
	
	CLOSE empcur;

END;
/
\set PLSQL_MODE off

%FOUND

当游标的 FETCH 操作执行完成后，可以使用属性 %FOUND 测试是否从游标的结果集中取出了一条记录。

语法：

cursor_name%FOUND

cursor_name 是游标的名称。如果这个 BOOLEAN 变量返回“true”，那么表示在 FETCH 操作后取出至少一条记录。

从结果集中取出最后一条记录后，下一次的 FETCH 操作时 %FOUND 返回”false”。如果第一次 FETCH 操作完成后，没有从结果集中返回记录，那么 %FOUND 也会返回”false”。

在游标打开前或者关闭后，引用属性 %FOUND 会产生一个 INVALID_CURSOR 异常。

如果打开游标后，没有执行 FETCH 操作，那么这时引用 %FOUND 将返回空值。

下面是一个使用属性 %FOUND 的示例：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_2()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	WHILE emp_cur_1%FOUND LOOP
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	END LOOP;
	CLOSE emp_cur_1;
END;

/
\set PLSQL_MODE off

上面这个存储过程的输出结果显示如下：

antdb=# SELECT cursor_test_2();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_2
---------------

(1 row)

%NOTFOUND

属性 %NOFOUND 与 %FOUND 在逻辑上是相反的。

语法：

cursor_name%NOTFOUND

cursor_name 是游标的名称。当 FETCH 操作从结果集中取出记录后，这个布尔型变量将返回”false”。

从结果集中取出最后一条记录后，下一次 FETCH 操作后，%NOFOUND 返回”true”，当第一次 FETCH 操作完成后，在结果集中没有记录，该变量也会返回”true”。

在一个未打开或者已经关闭的游标上引用属性 %NOFOUND，会产生一个 INVALID_CURSOR 异常。

如果一个游标已经打开，但是未执行 FETCH 操作。在这个游标上引用 %NOTFOUND，将返回 NULL。

在下面的示例中使用了属性 %NOTFOUND：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_3()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
		EXIT WHEN emp_cur_1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

这个存储过程的执行输出结果与上面示例中的相同：

antdb=# SELECT cursor_test_3();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_3
---------------

(1 row)

%ROWCOUNT

属性 %ROWCOUNT 用于返回一个整数值，显示迄今为止从游标结果集中获取记录的总数。

语法：

cursor_name%ROWCOUNT

cursor_name 是游标的名称，%ROWCOUNT 返回迄今为止通过游标所获取记录的总数。当取出一条记录后，在游标关闭之前，%ROWCOUNT 保持取出记录的总数。当游标关闭后，再次引用属性 %ROWCOUNT，那么会产生 INVALID_CURSOR 异常。

当一个游标未打开或已关闭，这时引用 %ROWCOUNT，会产生一个 INVALID_CURSOR 异常。

当游标已经打开，但尚未执行 FETCH 操作，这时引用 %ROWCOUNT，那么该属性返回 0。同样，如果在第一次执行 FETCH 后，在结果集中没有记录返回，那么这个属性同样会返回"0"。

下面的示例使用了属性 %ROWCOUNT：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_4()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
		EXIT WHEN emp_cur_1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('**********************');
	DBMS_OUTPUT.PUT_LINE(emp_cur_1%ROWCOUNT || ' rows were retrieved');
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

下面这个存储过程在雇员列表的最后部分打印出了所获取记录的总数：

antdb=# SELECT cursor_test_4();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
NOTICE:  **********************
NOTICE:  0 rows were retrieved
 CURSOR_TEST_4
---------------

(1 row)

游标状态和属性的总结

下面的表总结了所有游标状态和游标属性的返回值：

游标状态	%ISOPEN	%FOUND	%NOFOUND	%ROWCOUNT
打开前	是	INVALID_CURSOR 异常	INVALID_CURSOR 异常	INVALID_CURSOR 异常
游标打开后在执行第一次FETCH 前	是	空值	空值	0
第一次执行 FETCH成功后	是	是	否	1
成功执行完n次操作FECTH后（包括最后一行）	是	是	否	n
取出最后一行记录，再次执行 FETCH操作	是	否	是	n
关闭游标后	否	INVALID_CURSOR 异常	INVALID_CURSOR 异常	INVALID_CURSOR异常

用于游标的循环语句

在迄今所出现关于使用游标的示例中，处理游标结果集的程序逻辑都是按照如下顺序进行：打开一个游标，用循环结构获取结果集中的每一条记录，测试确认结果集中已经没有记录，最后使用语关闭游标。可以使用循环结构 cursor FOR loop，这样可以不用上面列出的语句单独编写代码。

cursor FOR loop 打开一个已经声明的游标，将结果集中的所有记录取出，然后关闭游标。

语法：

FOR record IN cursor

LOOP

statements

END LOOP;

record 是一个标识符，分配给隐式声明且带有 cursor%ROWTYPE 定义的记录。cursor 是已声明游标的名称。statements 是 SPL 语句。在循环中必须至少有一个语句。

在下面的示例中，使用了 cursor FOR loop：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_5()
IS
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	FOR v_emp_rec IN emp_cur_1 LOOP
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
END;
/
\set PLSQL_MODE off

如下所示，修改存储过程的执行结果相同：

antdb=# SELECT cursor_test_5();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_5
---------------

(1 row)

参数化游标

用户可以声明一个用于接收参数的静态游标。这样当打开游标时，可以为游标的这些参数传递数值。在下面的示例中，创建了一个参数化游标。这个游标首先接收一个指定值作为参数，然后显示了从表 emp 中所有薪水小于参数值的雇员姓名和薪水。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_6(v_sal in number)
IS
	my_record emp%ROWTYPE;
	CURSOR c1 (max_wage NUMBER) IS SELECT ename,sal FROM emp WHERE sal < max_wage;
BEGIN
	OPEN c1(v_sal);
	LOOP
		FETCH c1 INTO my_record.ename,my_record.sal;
		EXIT WHEN c1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('Name = ' || my_record.ename || ', salary = '|| my_record.sal);
	END LOOP;
	CLOSE c1;
END;
/
\set PLSQL_MODE off

例如，传递了数值 2000 作为了最大工资，那么就只能显示所有薪水少于 2000 的所有雇员的姓名和薪水。

下面是上面查询的执行输出结果：

antdb=# SELECT cursor_test_6(2001);
NOTICE:  Name = SMITH, salary = 1000
NOTICE:  Name = JANE, salary = 2000
 CURSOR_TEST_6
---------------

(1 row)

antdb

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

AntDB 数据库Oracle兼容-存储过程6

静态游标

声明游标

打开一个游标

从游标中获取记录

关闭游标

在游标中使用 %ROWTYPE

游标属性

%ISOPEN

%FOUND

%NOTFOUND

%ROWCOUNT

游标状态和属性的总结

用于游标的循环语句

参数化游标

评论