ORACLE中使⽤DBMS_SQL获取动态SQL执⾏结果中的列名和值1.获取动态SQL中的列名及类型
DECLARE
l_curid INTEGER;
l_cnt NUMBER;
l_desctab dbms_sql.desc_tab;
l_sqltext VARCHAR2(2000);
BEGIN
l_sqltext :='select *
from dba_objects where rownum<= 10'; --可以是任意有效的查询sql⽂本
l_curid := dbms_sql.open_cursor();
dbms_sql.parse(l_curid, l_sqltext, dbms_sql.native);
dbms_sql.describe_columns(l_curid, l_cnt, l_desctab);
FOR i IN1 .. unt LOOP
dbms_output.put_line(rpad(l_desctab(i).col_name, 30)||rpad(l_desctab(i).col_type, 3));
END LOOP;
dbms_sql.close_cursor(l_curid);
END;
查询结果
OWNER 1
OBJECT_NAME 1
SUBOBJECT_NAME 1
OBJECT_ID 2
DATA_OBJECT_ID 2
OBJECT_TYPE 1
CREATED 12
LAST_DDL_TIME 12
TIMESTAMP 1
STATUS 1
TEMPORARY 1
GENERATED 1
SECONDARY 1
NAMESPACE 2
EDITION_NAME 1
SHARING 1
EDITIONABLE 1
ORACLE_MAINTAINED 1
col_type 1:VARCAHR2,2:NUMBER,12:DATE
2.使⽤USING⽅式绑定动态SQL，获取列名及对应的值
-- Created on 2017/10/11 by ADMINISTRATOR
DECLARE
TYPE typecursor IS REF CURSOR;
cursrc typecursor;
curid NUMBER;
desctab dbms_sql.desc_tab;
colcnt NUMBER;
vname VARCHAR2(50);
vnum NUMBER;
vdate DATE;
rownumber NUMBER :=5;
sqlstmt VARCHAR2(2000);
BEGIN
sqlstmt :='SELECT * FROM fnd_user WHERE rownum < :rownumber';
-- 打开光标
OPEN cursrc FOR sqlstmt
USING rownumber;
-
- 从本地动态SQL转换为DBMS_SQL
curid := _cursor_number(cursrc);
--获取游标⾥⾯的数据列项数和每个数据列的属性，⽐如列名，类型，长度等
dbms_sql.describe_columns(curid, colcnt, desctab);
-- 定义列
FOR i IN1 .. colcnt LOOP
--此处是定义游标中列的读取类型，可以定义为字符，数字和⽇期类型，
IF desctab(i).col_type =2THEN
dbms_sql.define_column(curid, i, vnum);
ELSIF desctab(i).col_type =12THEN
dbms_sql.define_column(curid, i, vdate);
ELSE
dbms_sql.define_column(curid, i, vname, 50);
END IF;
END LOOP;
-- DBMS_SQL包获取⾏
--从游标中把数据检索到缓存区（BUFFER）中，缓冲区的值只能被函数COULUMN_VALUE()所读取
WHILE dbms_sql.fetch_rows(curid) >0 LOOP
--函数column_value()把缓冲区的列的值读⼊相应变量中。
FOR i IN1 .. colcnt LOOP
IF (desctab(i).col_type =1) THEN
lumn_value(curid, i, vname);
dbms_output.put_line(desctab(i).col_name||''|| vname ||', ');
ELSIF (desctab(i).col_type =2) THEN
lumn_value(curid, i, vnum);
dbms_output.put_line(desctab(i).col_name||''|| vnum ||', ');
ELSIF (desctab(i).col_type =12) THEN
lumn_value(curid, i, vdate);
dbms_output.put_line(desctab(i).col_name||''||
to_char(vdate, 'YYYY-MM-DD HH24:MI:SS') ||', ');
END IF;
END LOOP;
END LOOP;
dbms_sql.close_cursor(curid);
END;
3.使⽤DBMS_SQL.BIND_VARIABLE⽅式传递参数，获取列名及对应的值
DECLARE
v_cursor NUMBER;
v_stat NUMBER;
v_row NUMBER;
v_id NUMBER;
v_no VARCHAR(100);
v_date DATE;
v_sql VARCHAR(200);
s_id NUMBER;
s_date DATE;
BEGIN
s_id :=1131;
s_date := SYSDATE;
v_sql :='SELECT fu.user_id, fu.user_name, fu.CREATION_DATE FROM fnd_user fu where fu.user_id = :userId';
v_cursor := dbms_sql.open_cursor; --打开游标；
dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析动态SQL语句；
dbms_sql.bind_variable(v_cursor, ':userId', s_id); --绑定输⼊参数；
dbms_sql.define_column(v_cursor, 1, v_id); --定义列
dbms_sql.define_column(v_cursor, 2, v_no, 100);
dbms_sql.define_column(v_cursor, 3, v_date);
v_stat := ute(v_cursor); --执⾏动态SQL语句。
LOOP
EXIT WHEN dbms_sql.fetch_rows(v_cursor) <=0; --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。
lumn_value(v_cursor, 1, v_id); --将当前⾏的查询结果写⼊上⾯定义的列中。
lumn_value(v_cursor, 2, v_no);
lumn_value(v_cursor, 3, v_date);
dbms_output.put_line(v_id ||';'|| v_no ||';'|| v_date);
END LOOP;
dbms_sql.close_cursor(v_cursor); --关闭游标。
END;
参考：
最后⼀份来⾃百度⽂库的不好复制，现整理如下
对于使⽤过 ORACLE PLSQL 中的动态游标的⼈来说，我相信有不少⼈都会有这样的想法：如果对于任意⼀个给定的未知结构的游标（REF CURSOR），我们都能够在PLSQL中获取它的所有字段的名称，那该多好啊！不知道你是否有这样的想法，反正我早就有这样的想法了，也百度了多次，但结果不尽⼈意。曾经⼀度以为，这是不可能的。但是PLSQL Developer中的test 窗⼝中，可以打开任意游标并得到字段名及值。很显然，还是有办法得到未知结构的动态游标的字段名的，只是我不知道⽅法⽽已。
今天早上⼼⾎来潮，⼜想到这个事情，于是google了⼀下（⽤英⽂查询：how to get column names from oracle cursor），发现还真有办法获取未知结构的动态游标！看来在这⽅⾯百度还是太弱啊！技术问题还是得问google。
整理之后，结论如下：
1、如果给的是⼀个查询SQL⽂本，那么事情很容易（对于9i及以上版本），只要使⽤dbms_sql.open_cursor打开游标，再使⽤
dbms_sql.describe_columns即可得到游标的所有字段名称及类型等数据，存储在⼀个集合类型变量中（具体请看dbms_sql.desc_tab）。请参考如下PLSQL代码：
DECLARE
l_curid INTEGER;
l_cnt NUMBER;
l_desctab dbms_sql.desc_tab;
l_sqltext VARCHAR2(2000);
BEGIN
l_sqltext :='select *
from dba_objects where rownum<= 10'; --可以是任意有效的查询sql⽂本
l_curid := dbms_sql.open_cursor();
oracle游标的使用dbms_sql.parse(l_curid, l_sqltext, dbms_sql.native);
dbms_sql.describe_columns(l_curid, l_cnt, l_desctab);
FOR i IN1 .. unt LOOP
dbms_output.put_line(rpad(l_desctab(i).col_name, 30)||rpad(l_desctab(i).col_type, 3));
END LOOP;
dbms_sql.close_cursor(l_curid);
END;
注意，必须使⽤ DBMS_SQL.OPEN_CURSOR 打开游标，否则，就不是这种处理⽅法了。
2、如果给的是⼀个REF CURSOR类型变量，⽽不知道SQL⽂本，该怎么办呢？这⾥分两种情况：
1) 如果数据库版本是11g及以上，同样很容易：使⽤_cursor_number(cursor) 得到游标的ID，再使⽤
dbms_sql.describe_columns即可得到游标字段名称。参考如
下代码：
DECLARE
TYPE ref_cursor IS REF CURSOR;
l_cursor ref_cursor;
l_curid NUMBER;
l_col_cnt NUMBER;
l_desc_tab dbms_sql.desc_tab;
BEGIN
OPEN l_cursor FOR'select owner,object_type,object_name from dba_objects where rownum<= 10';
l_curid := _cursor_number(l_cursor);
dbms_sql.describe_columns(l_curid, l_col_cnt, l_desc_tab);
FOR i IN1 .. l_col_cnt LOOP
dbms_output.put_line(l_desc_tab(i).col_name);
END LOOP;
dbms_sql.close_cursor(l_curid);
END;
2) 如果数据库版本低于11g，则PLSQL中仅有如下⽅法可以获取字段名称及字段值：
DECLARE
l_cursor SYS_REFCURSOR;
i NUMBER :=0;
CURSOR get_columns IS
telement() NAME,
lumn_value, 'node()') VALUE
FROM (SELECT*FROM TABLE(xmlsequence(l_cursor))) t1,
TABLE(xmlsequence(lumn_value, '/ROW/node()'))) t2;
BEGIN
OPEN l_cursor FOR'select owner,object_type,object_name from dba_objects where rownum<= 10';
FOR rec_ IN get_columns LOOP
i := i +1;
dbms_output.put_line(rpad(i,2) ||':'|| rpad(rec_.name,15) ||': '|| lpad(rec_.value,15));
END LOOP;
CLOSE l_cursor;
END;
⽤这种⽅法，可以得到动态游标的所有数据，包括字段名称和字段值。但这种⽅法会遍历游标，即游
标已经⾛到底了，不能再次使⽤了。⽽使⽤dbms_sql.describe_columns不会对游标的光标位置产⽣任何影响。两者优劣⼀⽬了然。
对于JAVA、C等外部编程语⾔⽽⾔，要从游标中获取字段名称是⽐较容易的。可以⽤JAVA语⾔写⼀个获取游标字段名称的存储过程，并编译⾄数据库中，且做成PLSQL接⼝，这样就可以在PLSQL中调⽤了。有意者可以⾃⼰去研究下，或者向精通JAVA者请教。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论