DBMS_SQL执行查询示例

database

通常情况下,需要动态执行查询语句尽量使用语法更简洁的 OPEN {SYS_REFCURSOR} FOR ... 或 EXECUTE IMMEDIATE ...

但当查询语句的列或绑定变量无法确定数量或类型时,还是需要使用更加灵活的 DBMS_SQL 包,下面是使用 DBMS_SQL 包执行列数量可变的查询示例;一个典型的应用场景就是报表的生成,因为我们可能无法事先知道这个报表有多少列。

DECLARE
  -- 可变列数的报表查询编程示例
  l_Cursor_Id INTEGER;
  l_Col_Count INTEGER;
  l_Desc_Tbl2 Dbms_Sql.Desc_Tab2;
  l_Ret       INTEGER;
  l_Row_Count BINARY_INTEGER:=0;
  l_Row_Index BINARY_INTEGER:=1;
  c_Bulk_Size CONSTANTBINARY_INTEGER:=2;-- 每次提取的记录数
  -- 报表的每一列最终都是 VARCHAR2 类型
  TYPE Col_Val_Tbl_Type ISTABLEOF Dbms_Sql.Varchar2_Table INDEXBYPLS_INTEGER;
  l_Col_Val_Tbl Col_Val_Tbl_Type;
BEGIN
  -- 打开并解析查询语句
  l_Cursor_Id := Dbms_Sql.Open_Cursor;
  Dbms_Sql.Parse(l_Cursor_Id,
                 "SELECT ""X"" M, ""Y"" M FROM DUAL WHERE DUMMY = :T_DUMMY
UNION ALL SELECT ""C"", ""D"" FROM DUAL
UNION ALL SELECT ""E"", ""F"" FROM DUAL",
                 Dbms_Sql.Native);
  -- 根据查询语句的列取出所有值
  Dbms_Sql.Describe_Columns2(l_Cursor_Id, l_Col_Count, l_Desc_Tbl2);
  FOR i IN1 .. l_Col_Count LOOP
    l_Col_Val_Tbl(i)(0):=NULL;
    Dbms_Sql.Define_Array(c           => l_Cursor_Id,
                          Position    => i,
                          c_Tab       => l_Col_Val_Tbl(i),
                          Cnt         => c_Bulk_Size,
                          Lower_Bound => l_Row_Index);
  ENDLOOP;
  -- 绑定变量并执行
  Dbms_Sql.Bind_Variable(l_Cursor_Id,":T_DUMMY","X");
  l_Ret := Dbms_Sql.Execute(l_Cursor_Id);-- 返回 DML 语句的修改行数,此处不使用
  -- 遍历查询结果
  LOOP
    l_Row_Count := l_Row_Count + Dbms_Sql.Fetch_Rows(l_Cursor_Id);
    EXITWHEN l_Row_Count < l_Row_Index;-- 取出记录后总行数未增加
    -- Dbms_Output.Put_Line("FETCH: " || (l_Row_Count - l_Row_Index + 1));
    -- 取每一列的值
    FOR j IN1 .. l_Col_Count LOOP
      Dbms_Sql.Column_Value(l_Cursor_Id, j, l_Col_Val_Tbl(j));
    ENDLOOP;
    -- 遍历每一行
    FOR i IN l_Row_Index .. l_Row_Count LOOP
      FOR j IN1 .. l_Col_Count LOOP
        -- 遍历每一列
        Dbms_Output.Put(l_Col_Val_Tbl(j)(i)||" ");
      ENDLOOP;
      Dbms_Output.Put_Line("");
    ENDLOOP;
    EXITWHEN l_Row_Count - l_Row_Index +1< c_Bulk_Size;-- 本次取出的行数小于指定的值,不进行下次取值
    l_Row_Index := l_Row_Count +1;
  ENDLOOP;
  -- 关闭
  IF Dbms_Sql.Is_Open(l_Cursor_Id)THEN
    Dbms_Sql.Close_Cursor(l_Cursor_Id);
  ENDIF;
END;

DBMS_SQL 包还提供了两个很棒的函数用于在 DBMS_SQL 的 CURSOR_NUMBER 和 SYS_REFCURSOR 之间切换,我们可以充分利用二者的长处:CURSOR_NUMBER 更灵活,SYS_REFCURSOR 更简洁。

例如上述示例在绑定变量上没有不确定性,只需要动态变化查询到的列,那么可以先使用语法简洁的 SYS_REFCURSOR 打开查询,然后切到 CURSOR_NUMBER 对列进行分析。

这两个函数的定义及说明如下:

function to_refcursor(cursor_number in out integer) return sys_refcursor;

CURSOR_NUMBER 必须是一个已经执行过(调用过 DBMS_SQL.EXECUTE)的查询,调用后游标的控制权转移到返回的 SYS_REFCURSOR 变量上,后续应且仅应 CLOSE SYS_REFCURSOR;

function to_cursor_number(rc in out sys_refcursor) return integer;

SYS_REFCURSOR 必须是一个已经打开(执行过 OPEN ... FOR ..)的查询,调用后游标的控制权转移到返回的 CURSOR_NUMBER 变量上,后续应且仅应 DBMS_SQL.CLOSE_CURSOR(CURSOR_NUMBER);

以上是 DBMS_SQL执行查询示例 的全部内容, 来源链接: utcz.com/z/534238.html

回到顶部