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

tocata 2024-08-19

REF CURSORS 和游标变量

本节讨论了另外一种类型的游标，与先前讨论的静态游标相比较，它能够提供更大的灵活性。

REF CURSOR 概述

一个游标型变量是一个游标，包含一个指向查询结果集的指针。通过执行游标变量的 OPEN FOR 语句，可以决定结果集的内容。

与静态游标不同的，游标型变量不与特定的 SQL 绑定。可以使用 OPEN FOR 语句多次打开同一个游标变量，每次与不同的查询语句绑定。这样每一次查询会创建一个新的结果集，然后通过游标变量在程序中生效。

REF CURSOR 类型可以通过参数方式传递，或者从存储过程和函数中返回。一个函数的返回类型可以是一个 REF CURSOR 类型，通过在程序之间游标类型变量的传递，提供了将游标上的操作模块化到不同程序中的能力。

声明游标变量

SPL 使用两种方式声明游标变量，方法是创建一个 REF CURSOR 的类型，然后使用这个类型声明游标变量。

声明一个用户定义的 REF CURSOR 型变量

为使用用户定义的 REF CURSOR 型变量，需要执行两个不同的步骤进行声明。

创建一个被引用的游标类型。
在这个游标类型的基础上声明一个实际游标变量。

语法：

TYPE cursor_type_name IS REF CURSOR [ RETURN return_type];

下面是一个声明游标型变量的示例：

IS
TYPE emp_cur_type IS REF CURSOR ;
my_rec emp_cur_type;
...
复制

打开游标变量

当声明一个游标型变量后，必须用一个与相关联的 SELECT 命令打开这个游标变量。OPEN FOR 语句指定了用于创建结果集的 SELECT 命令。

语法：

OPEN name FOR query;

name 是一个已定义好的游标型变量的标识符。query 是一条 SELECT 命令，用来确定结果集。当 OPEN FOR 语句执行后，使用游标变量的值标识了结果集。

在下面的示例中，结果集是一个由部门中雇员号，和名称组成的列表：

注意在 SELECT 命令中出现变量和参数的地方都可以使用表达式。在这种情况下，在等式中可以使用一个参数来测试是否与部门号相等。（emp 表格定义见【示例参考表格】）

CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno	emp.deptno%TYPE
)
IS
type emp_cur is ref cursor;
emp_refcur emp_cur;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
...
复制

从游标变量中取回记录

当打开游标型变量后，使用 FETCH 语句，从结果集中取出记录。

在下面的示例中，在上个示例中加上了 FETCH 语句，这样会将结果集返回到 2 个变量中，然后显示出来：

需要注意的是，用来确认静态游标状态的游标属性也可以在游标型变量中使用。

CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno	emp.deptno%TYPE
)
IS
type emp_cur is ref cursor;
emp_refcur emp_cur;
v_empno	emp.empno%TYPE;
v_ename	emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('EMPNO	ENAME');
DBMS_OUTPUT.PUT_LINE('-----	-------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || '	' || v_ename);
END LOOP;
...
复制

关闭一个游标型变量

CLOSE 语句可以用来释放一个结果集。

注意：与静态游标不同，游标型变量在重新打开使用前不是必须处于关闭状态的。游标型变量打开后，上一次打开时所产生的结果集会将会丢失。

在下面这个示例中加上了 CLOSE 语句（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE ref_cursor_test_1 (p_deptno NUMBER)
IS
	type emp_cur is ref cursor;
	emp_refcur emp_cur;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno ORDER BY 1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;

/
\set PLSQL_MODE off
复制

下面是这个存储过程的执行输出：

antdb=# SELECT ref_cursor_test_1(20);
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7329 AMY
NOTICE:  7369 SMITH
 REF_CURSOR_TEST_1
-------------------

(1 row)
复制

使用限制

下面是关于游标型变量使用时的一些限制：

不能使用比较类型操作符来测试游标类型是否相等，不等，空或者非空。
不能将空值分配给游标型变量。
不能在数据库列中存放游标型标量的值。
静态游标和游标变量不能互换使用。例如，静态游标不能在 OPEN FOR 语句中使用。

除此之外，下面的这个表根据在存储过程或者函数内可以对游标变量进行的操作，显示作为存储过程或者函数参数的游标变量所被允许使用的参数模式。

操作	输入参数	输入输出参数	输出参数
OPEN	不可以	可以	不可以
FETCH	可以	可以	不可以
CLOSE	可以	可以	不可以

例如，如果存储过程带有游标变量的形式参数，并且在游标型变量上执行了 OPEN FOR，FETCH 和 CLOSE 这三个操作，那么这个形式参数必须声明为 IN OUT 模式。

示例

下面是一些使用游标型变量的示例：

从函数中返回一个 REF CURSOR 型变量

预置测试数据：

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',1000,NULL,20);
INSERT INTO emp VALUES (7389,'JANE','CLERK',7912,'17-DEC-80',2000,NULL,40);
INSERT INTO emp VALUES (7319,'JACK','CLERK',7922,'18-DEC-80',5000,null,20);
INSERT INTO emp VALUES (7329,'AMY','MASTER',7722,'18-DEC-80',3000,null,20);
复制

在下面的示例中，使用将工作类型作为条件的查询语句打开了一个游标型变量来选择雇员相关信息。需要注意的是，游标变量是在这个函数返回语句中指定的，所以函数的调用者可以访问到游标变量的结果集。

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2)
RETURN REFCURSOR
IS
	type emp_cur is ref cursor;
	emp_refcur emp_cur;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
	RETURN emp_refcur;
END;
/
\set PLSQL_MODE off
复制

在下面存储过程中，首先通过将函数的返回值分配给已声明的游标型变量来调用上面示例中的函数，然后使游标变量取回查询结果集，最后关闭游标型变量。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE := 'CLERK';
	type emp_cur is ref cursor;
	v_emp_refcur emp_cur;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	v_emp_refcur := emp_by_job (v_job);
	LOOP
		FETCH v_emp_refcur INTO v_empno, v_ename;
		EXIT WHEN v_emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE v_emp_refcur;
END;
/
\set PLSQL_MODE off
复制

下面就是匿名代码块执行后的输出结果：

antdb=# SELECT test();
NOTICE:  EMPLOYEES WITH JOB CLERK
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
NOTICE:  7319 JACK
B019_TEST
-----------

(1 row)
复制

模块化游标操作

在下面的几个示例中演示了如何以模块化操作的方式将游标型变量上的不同操作放置在不同的程序中。

在下面这个存储过程中，通过用一条查询所有记录的 SELECT 命令打开游标变量：

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_all_emp (p_emp_refcur REFCURSOR) RETURN REFCURSOR
IS
BEGIN
	OPEN p_emp_refcur FOR SELECT empno, ename FROM emp;
	return p_emp_refcur;
END;
/
\set PLSQL_MODE off
复制

在下面这个示例中根据指定的部门编号，通过使用查询所有符合条件的记录的 SQL 命令来打开游标型变量：

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_emp_by_dept (p_emp_refcur REFCURSOR,p_deptno NUMBER)
IS
BEGIN
	OPEN p_emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
	return p_emp_refcur;
END;
/
\set PLSQL_MODE off
复制

在下面的示例中通过从另外一张数据表中取得所有记录的 SELECT 命令打开游标变量。注意，函数的返回值是一个已经打开的游标型变量（dept 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_dept (p_dept_refcur REFCURSOR) RETURN REFCURSOR
IS
	v_dept_refcur REFCURSOR;
BEGIN
	v_dept_refcur := p_dept_refcur;
	OPEN v_dept_refcur FOR SELECT deptno, dname FROM dept;
	RETURN v_dept_refcur;
END;
/
\set PLSQL_MODE off
复制

在下面的示例中使用存储过程取回并显示了一个由雇员号和名称组成的游标变量结果集：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE fetch_emp (p_emp_refcur REFCURSOR)
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
		FETCH p_emp_refcur INTO v_empno, v_ename;
		EXIT WHEN p_emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
END;
/
\set PLSQL_MODE off
复制

在下面这个示例中使用存储过程取回并显示了包含部门号和名称的游标变量结果集：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE fetch_dept ( p_dept_refcur REFCURSOR)
IS
	v_deptno dept.deptno%TYPE;
	v_dname dept.dname%TYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('DEPT DNAME');
	DBMS_OUTPUT.PUT_LINE('---- ---------');
LOOP
	FETCH p_dept_refcur INTO v_deptno, v_dname;
	EXIT WHEN p_dept_refcur%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(v_deptno || ' ' || v_dname);
END LOOP;
END;
/
\set PLSQL_MODE off
复制

下面的这个存储过程关闭了一个指定的游标变量：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE close_refcur (p_refcur REFCURSOR)
IS
BEGIN
	CLOSE p_refcur;
END;
/
\set PLSQL_MODE off
复制

下面的匿名代码块执行了先前所描述的所有程序：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	gen_refcur REFCURSOR;
BEGIN
	DBMS_OUTPUT.PUT_LINE('ALL EMPLOYEES');
	gen_refcur:=open_all_emp(gen_refcur);
	fetch_emp(gen_refcur);
	DBMS_OUTPUT.PUT_LINE('****************');
	close_refcur(gen_refcur);
	
	DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #10');
	gen_refcur:=open_emp_by_dept(gen_refcur, 10);
	fetch_emp(gen_refcur);
	DBMS_OUTPUT.PUT_LINE('****************');
	close_refcur(gen_refcur);
	
	DBMS_OUTPUT.PUT_LINE('DEPARTMENTS');
	fetch_dept(open_dept(gen_refcur));
	DBMS_OUTPUT.PUT_LINE('*****************');

	close_refcur(gen_refcur);

END;
/
\set PLSQL_MODE off
复制

下面是匿名代码块的输出结果：

antdb=# SELECT test();
NOTICE:  ALL EMPLOYEES
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7369 SMITH
NOTICE:  7499 ALLEN
NOTICE:  7521 WARD
NOTICE:  7566 JONES
NOTICE:  7654 MARTIN
NOTICE:  7698 BLAKE
NOTICE:  7782 CLARK
NOTICE:  7788 SCOTT
NOTICE:  7839 KING
NOTICE:  7844 TURNER
NOTICE:  7876 ADAMS
NOTICE:  7900 JAMES
NOTICE:  7902 FORD
NOTICE:  7934 MILLER
NOTICE:  ****************
NOTICE:  EMPLOYEES IN DEPT #10
NOTICE:  7782 CLARK
NOTICE:  7839 KING
NOTICE:  7934 MILLER
NOTICE:  ****************
NOTICE:  DEPARTMENTS
NOTICE:  DEPT DNAME
NOTICE:  ---- ---------
NOTICE:  10 ACCOUNTING
NOTICE:  20 RESEARCH
NOTICE:  30 SALES
NOTICE:  40 OPERATIONS
NOTICE:  *****************
 TEST
------

(1 row)
复制

执行动态查询语句的 REF CURSORS

在 AntDB 中可以通过 OPEN FOR USING 语句来实现动态查询的功能。在 OPEN FOR USING 语句中，可以提供一个字符串常量或者字符串变量，来做为 SELECT 命令。

语法：

OPEN name FOR dynamic_string

[ USING bind_arg [, bind_arg_2 ] ...];

name 是一个已经声明的游标型变量。dynamic_string 是一个字符串常量或者变量包，它包含不以分号结束的 SELECT 命令。bind_arg，bind_arg_2... 是绑定参数。当打开游标变量后，这些参数会传递到 SELECT 命令中的对应占位符中。占位符就是前缀为冒号的标识符。

在下面的示例中，以字符串常量的方式实现了动态查询的功能（emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query()
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = 30' ||' AND sal >= 1500';
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;
/
\set PLSQL_MODE off
复制

下面是存储过程执行后的输出结果：

antdb=# SELECT dept_query();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7499 ALLEN
NOTICE:  7698 BLAKE
NOTICE:  7844 TURNER
DEPT_QUERY
------------

(1 row)
复制

在下个示例中，前面的那个查询被修改为通过绑定参数来传递查询参数（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query (p_deptno NUMBER,p_sal NUMBER)
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno AND sal >= p_sal;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;

/
\set PLSQL_MODE off
复制

下面是输出结果：

antdb=# SELECT dept_query(30,1500);
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7499 ALLEN
NOTICE:  7698 BLAKE
NOTICE:  7844 TURNER
DEPT_QUERY
------------

(1 row)
复制

在最后这个示例中，使用字符串变量来传递 SELECT 命令，这样就使程序具备了最大程度的灵活性（emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query (p_deptno NUMBER,p_sal NUMBER)
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	p_query_string VARCHAR2(100);
BEGIN
	p_query_string := 'SELECT empno, ename FROM emp WHERE ' ||'deptno = $1 AND sal >= $2';
	OPEN emp_refcur FOR p_query_string USING p_deptno, p_sal;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;
/
\set PLSQL_MODE off
复制

antdb

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

关注作者

关注

获得了2900次点赞

内容获得1113次评论

获得了321次收藏