Open for本是为了支持游标变量,现在用它实现多行动态查询。OPEN FOR的语法如下:
OPEN{cursor_variable | :host_cursor_viable}FOR SQL_string
[USING bind_argument [, bind_argument]…];
解释:
Cursor_variable是一种弱类型的游标变量。
:host_cursor_variable是在PL/SQL宿主环境下声明的游标变量,如Oracle调用接口程序。
SQL_string包含动态执行的SELECT语句。
USING子句与EXECUTE IMMEDIATE语句遵循相同的规则。
使用OPEN FOR语句打开动态查询的例子:
PROCEDURE show_parts_inventory(
parts_table IN VARCHAR2, where_in IN VARCHAR2)
IS
TYPE query_curtype IS REF CURSOR;
dyncur query_curtype;
BEGIN
OPEN dyncur FOR
'SELECT * FROM' || parts_table || 'WHERE' || where_in;
。。。
执行OPEN FOR语句时,PL/SQL运行引擎操作如下:
1、 将游标变量与在查询字符串找到的查询相关联。
2、 对绑定参数求值,并用这些值替换查询字符串内的占位符。
3、 执行查询。
4、 识别结果集。
5、 将游标放在结果集第一行。
6、 把由%ROWCOUNT返回的行计数值归零。
注意,查询中任何绑定参数(由USING子句提供),仅当游标变量打开时才能求值。这意味着如果我们想对相同的动态查询使用不同的绑定参数值,
就必须使用该参数再执行一次OPEN FOR语句。
执行多行查询需遵循以下步骤:
1、 声明一个REFCURSOR类型(或使用Oracle定义的SYS_REFCURSOR弱CURSOR类型)。
2、 基于这个REF CURSOR类型声明一个游标变量。
3、 用这个游标变量打开查询字符串。
4、 使用FETCH语句提供查询确认的一行或多行结果集。
5、 必要时检查游标属性(%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN)。
6、 使用标准CLOSE语句关闭游标变量。
/*显示任何一个表中由WHERE子句所选出的行的指定列的内容(对数字、日期和字符串列有效)*/
/*参数说明:tab:表名、col:列名、whr:条件*/
PROCEDURE showcol(tab IN VARCHAR2,
col IN Varchar2,
whr IN VARCHAR2:=NULL)
IS
cv SYS_REFCURSOR;
val VARCHAR2(32767);
BEGIN
OPEN cv FOR
--注意字符串之间的空格
'SELECT ' || col ||
' FROM ' || tab ||
' WHERE ' || NVL(whr, '1 = 1');
LOOP
--取cv的值给val,如果找不到就退出,和显示游标相同
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
--如果取到第一行,显示头部的信息
IFcv%ROWCOUNT = 1
THEN
Dbms_Output.put_line(RPAD('_',60,'_'));
Dbms_Output.put_line(
'Contents of ' || UPPER(tab)|| '.' || UPPER(col));
Dbms_Output.put_line(RPAD('_',60,'_'));
END IF;
Dbms_Output.put_line(val);
END LOOP;
--记得关闭游标
CLOSE cv;
END;
/*升级版showcol程序,显示带有一个时间列,并且时间列在一定范围数值内的所有列的信息*/
PROCEDURE showcol(tab VARCHAR2,
col VARCHAR2,
dtcol VARCHAR2,
dt1 DATE,
dt2 DATE := NULL)
IS
cvSYS_REFCURSOR;
val VARCHAR2(32767);
BEGIN
OPEN cv FOR
--注意空格
'SELECT ' || col ||
' FROM ' || tab ||
' WHERE ' || dtcol ||
' BETWEEN TRUNC (:startdt) AND TRUNC (:enddt)'
USING dt1, NVL (dt2, dt1+1);
LOOP
--取cv的值给val,如果找不到就退出
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
--如果取到第一行,显示信息
IF cv%ROWCOUNT = 1
THEN
Dbms_Output.put_line(
'Contents of ' || UPPER(tab)|| '.' || UPPER(col) ||
' for ' || UPPER(dtcol) ||
' between ' || dt1 || ' AND' || NVL (dt2, dt1+1));
END IF;
Dbms_Output.put_line(val);
ENDLOOP;
CLOSE cv;
END;