oracle的DBMS_SQL中动态游标的使用资料搜集

资料一“

CREATE OR REPLACE PROCEDURE do_query_2 (sql_stmt VARCHAR2) IS
TYPE curtype IS REF CURSOR;
src_cur curtype;
curid    NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt   NUMBER;
namevar VARCHAR2(50);
numvar   NUMBER;
datevar DATE;
empno    NUMBER := 100;
BEGIN
-- sql_stmt := SELECT ... FROM employees WHERE employee_id = :b1';

-- Open REF CURSOR variable:
--OPEN src_cur FOR sql_stmt USING empno;
OPEN src_cur FOR sql_stmt ;

-- Switch from native dynamic SQL to DBMS_SQL package:
curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);

-- Define columns:
FOR i IN 1 .. colcnt LOOP
    IF desctab(i).col_type = 2 THEN
      DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
    ELSIF desctab(i).col_type = 12 THEN
      DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
      -- statements
    ELSE
      DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);
    END IF;
END LOOP;

-- Fetch rows with DBMS_SQL package:
WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
    FOR i IN 1 .. colcnt LOOP
      IF (desctab(i).col_type = 1) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
   DBMS_OUTPUT.PUT(namevar);
      ELSIF (desctab(i).col_type = 2) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
   DBMS_OUTPUT.PUT(numvar);
      ELSIF (desctab(i).col_type = 12) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
   DBMS_OUTPUT.PUT(datevar);
        -- statements
      END IF;
  
    END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;

DBMS_SQL.CLOSE_CURSOR(curid);
END;
/

资料二：

dbms_sql 动态输出列
create or replace procedure dump_query( p_query in varchar2 )
is

l_columnValue varchar2(4000);

l_status integer;

l_colCnt number default 0;

l_cnt number default 0;

l_line long;



/* We'll be using this to see how many columns

* we have to fetch so we can define them and

* then retrieve their values.

*/

l_descTbl dbms_sql.desc_tab;





/* Step 1 - open cursor. */

l_theCursor integer default dbms_sql.open_cursor;
begin



/* Step 2 - parse the input query so we can describe it. */

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );



/* Step 3 - now, describe the outputs of the query. */

dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );



/* Step 4 - we do not use in this example, no BINDING needed.

* Step 5 - for each column, we need to define it, tell the database

* what we will fetch into. In this case, all data is going

* to be fetched into a single varchar2(4000) variable.

*/

for i in 1 .. l_colCnt

loop

dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );

end loop;



/* Step 6 - execute the statement. */

l_status := dbms_sql.execute(l_theCursor);



/* Step 7 - fetch all rows. */

while ( dbms_sql.fetch_rows(l_theCursor) > 0 )

loop

/* Build up a big output line, this is more efficient than calling

* DBMS_OUTPUT.PUT_LINE inside the loop.

*/

l_cnt := l_cnt+1;

l_line := l_cnt;

/* Step 8 - get and process the column data. */

for i in 1 .. l_colCnt loop

dbms_sql.column_value( l_theCursor, i, l_columnValue );

l_line := l_line || ',' || l_columnValue;

end loop;



/* Now print out this line. */

dbms_output.put_line( l_line );

end loop;



/* Step 9 - close cursor to free up resources.. */

dbms_sql.close_cursor(l_theCursor);
exception

when others then

dbms_sql.close_cursor( l_theCursor );

raise;
end dump_query;

资料三：

以上只是11g的方法，如果不是，可以检索时将所有字段连接在一起，然后SPLIT