Dynamic Cursor in plsql (2)

获取cursor行数：

当游标被打开后，％ROWCOUNT归零。第一次提取之前，cursor_name％ROWCOUNT返回0。此后，它返回的fetch到的行数。完成一次fetch操作后，count+1。

获取cursor 列数：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

--方法一： CREATE OR REPLACE FUNCTION count_sql( p_sql IN CLOB ) RETURN INTEGER AS          lv_cursor_id    INTEGER ;          lv_columns      DBMS_SQL.DESC_TAB;          lv_column_count INTEGER ; BEGIN          -- Open Cursor          lv_cursor_id := DBMS_SQL.OPEN_CURSOR;                    -- Parse Cursor          DBMS_SQL.PARSE          ( c             => lv_cursor_id          , statement     => p_sql          , language_flag => DBMS_SQL.NATIVE          );                    -- Describe Columns          DBMS_SQL.DESCRIBE_COLUMNS          ( c       => lv_cursor_id             , col_cnt => lv_column_count          , desc_t  => lv_columns          );                    -- Close Cursor          DBMS_SQL.CLOSE_CURSOR(lv_cursor_id);                    RETURN lv_column_count; END count_sql; /   SELECT count_sql( 'SELECT dummy, dummy, dummy, ' 'Y' ' FROM DUAL' ) FROM DUAL;   COUNT_SQL( 'SELECTDUMMY,DUMMY,DUMMY,' 'Y' 'FROMDUAL' ) --------------------------------------------------                                                   4 ================================================================ --方法二：   SQL> select column_value cnt from xmltable( 'count(distinct-values(for $i in ROW/* return name($i)))'    passing dbms_xmlgen.getxmltype( 'select ename, sal, hiredate, deptno from emp' ).extract( 'ROWSET/ROW' ) )   CNT  ----- 4    1 row selected. SQL> select column_value cnt from xmltable( 'count(distinct-values(for $i in ROW/* return name($i)))'    passing dbms_xmlgen.getxmltype( 'select sal, job, deptno from emp' ).extract( 'ROWSET/ROW' ) )   CNT  ----- 3    1 row selected.

动态执行sql语句的函数：

func_dynamic_fetch（l_sql);----l_sql 为sql语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117

CREATE OR REPLACE TYPE AnydataList FORCE IS VARRAY (2147483647) OF ANYDATA  ; / CREATE OR REPLACE TYPE Varchar2List FORCE IS VARRAY (2147483647) OF VARCHAR2 (32767) ; / create or replace function func_dynamic_fetch (   p_sql in varchar2,      p_no_of_cols integer : = 1,       p_return_multiple_row boolean := true )          return anydatalist is      lc_rs sys_refcursor;      strlist varchar2List := varchar2List();      n number := 3;      code varchar2(32767);      TYPE ref_cursor IS REF CURSOR ;      rc_         ref_cursor;       c_          NUMBER;      i_          NUMBER;      col_count_  NUMBER;      desc_tab_   DBMS_SQL.DESC_TAB2;      col_num  NUMBER;      n2 number;      l_any_list anydatalist;      l_types varchar2list;      l_add_stmts varchar2list;      l_fetch_type varchar2(32767);      c_date_type constant number := 12;      c_timestamp_type constant number := 180;                  function repeatStmt(n in integer , template in varchar2, seperator in varchar2 := '' , vals in varchar2list := null ) return varchar2 is          l_result varchar2(32767);          l_stmt varchar2(32767);      begin          for i in 1..n loop              l_stmt := template;              if vals is not null then                  l_stmt := replace (template, '__val__' , vals(i));              end if;              l_result := l_result || replace (l_stmt, '__$__' , i);              if i < n then                  l_result := l_result || seperator;              end if;          end loop;          return l_result;      end repeatStmt; begin       open rc_ for p_sql;       n2 := p_no_of_cols;      c_ := DBMS_SQL.to_cursor_number(rc_);      DBMS_SQL.DESCRIBE_COLUMNS2(c_, col_count_, desc_tab_);      col_num := desc_tab_. first ;      l_types := varchar2list();      l_add_stmts := varchar2list();      if col_num is not null then          loop              if desc_tab_(col_num).col_type = c_date_type or desc_tab_(col_num).col_type =c_timestamp_type then                  varchar2listAdd(l_types, 'date' );                  --to conform to jdbc date format                  varchar2listAdd(l_add_stmts, 'varchar2ListAdd(l_str_list, to_char(a__$__, ' 'yyyy-mm-dd hh:mi:ss' '));' );              else                  varchar2listAdd(l_types, 'varchar2(32767)' );                  varchar2listAdd(l_add_stmts, 'varchar2ListAdd(l_str_list, a__$__);' );              end if;              col_num:= desc_tab_. next (col_num);              exit when (col_num is null );          end loop;      end if;            n := col_count_;      if n2 > n then          n2 := n;      end if;      dbms_sql.close_cursor(c_);      code :=      ' declare          lc_rs sys_refcursor;          l_sql varchar2(32767);          l_str_list varchar2list;          l_counter integer;          l_any_list anydatalist; ' ||           repeatStmt(n, 'a__$__ __val__; ' ,  seperator => chr(10), vals => l_types) || '        begin          l_sql := :0;          --dbms_output.put_line(l_sql);          open lc_rs for l_sql;          l_any_list := anydatalist();          l_counter := 0;          loop              fetch lc_rs into ' || repeatStmt(n, 'a__$__' , ', ' ) || ';' || '              exit when lc_rs%notfound;              l_counter := l_counter + 1;              l_str_list := varchar2List();' ||              repeatStmt(n2, '__val__' , chr(10), vals => l_add_stmts) || '              anydataListAdd(l_any_list, anydata.convertCollection(l_str_list)); ' ||              ( case when p_return_multiple_row = false then              'exit when l_counter = 1;'              end ) || '          end loop;          if lc_rs%isopen then              close lc_rs;          end if;          :1 := l_any_list;        exception when others then           dbms_output.put_line(' ' func_dynamic_fetch exec immediate exception: ' ' || sqlerrm);          raise;          if lc_rs%isopen then close lc_rs; end if;        end;      ' ;      execute immediate code using p_sql, out l_any_list;      return l_any_list; exception when others then      if rc_%isopen then close rc_; end if;      dbms_output.put_line( 'func_dynamic_fetch: exception: ' || sqlerrm);      return anydatalist(); end ;   / show errors;

转载于:https://www.cnblogs.com/Jeffrey-xu/p/5057700.html