动态SQL中使用Open for语句

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;