【转】PLSQL中怎样获取未知结构的动态游标的字段名

PLSQL中怎样获取未知结构的动态游标的字段名 
 
对于使用过 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_curidINTEGER; l_cntNUMBER; 
l_desctabdbms_sql.desc_tab; l_sqltextVARCHAR2(2000); BEGIN 
l_sqltext := 'select owner,object_type,object_name 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 ..l_desctab.count LOOP 
dbms_output.put(lpad(l_desctab(i).col_name, 20)); ENDLOOP; 
dbms_output.new_line;  
dbms_sql.close_cursor(l_curId); END;  
运行结果如下: 
OWNER               OBJECT_TYPE         OBJECT_NAME         


注意,必须使用 DBMS_SQL.OPEN_CURSOR 打开游标,否则,就不是这种处理方法了。  
2、如果给的是一个REF CURSOR类型变量,而不知道SQL文本,该怎么办呢?这里分两种情况: 
1) 如果数据库版本是11g及以上,同样很容易:使用dbms_sql.to_cursor_number(cursor)
得到游标的ID,再使用dbms_sql.describe_columns即可得到游标字段名称。参考如下代码: 
DECLARE 
TYPEref_cursorISREFCURSOR; l_cursorref_cursor; l_curidNUMBER; l_col_cntNUMBER; 
l_desc_tab DBMS_SQL.DESC_TAB; BEGIN 
OPENl_cursorFOR'select owner,object_type,object_name from dba_objects where rownum<= 10'; 
l_curid := DBMS_SQL.to_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); ENDLOOP;  
  DBMS_SQL.CLOSE_CURSOR(l_cursor); END;  
2) 如果数据库版本低于11g,则PLSQL中仅有如下方法可以获取字段名称及字段值: 
DECLARE 
l_cursorSYS_REFCURSOR; iNUMBER := 0; 
CURSORget_columnsIS 
SELECT t2.column_value.getrootelement() NAME, EXTRACTVALUE(t2.column_value, 'node()') VALUE 
FROM   (SELECT * FROMTABLE(XMLSEQUENCE(l_cursor))) t1, 
TABLE(XMLSEQUENCE(EXTRACT(t1.column_value, '/ROW/node()'))) t2; BEGIN 
OPENl_cursorFOR'select owner,object_type,object_name from dba_objects where rownum<= 10';  
FOR rec_ INget_columns 
LOOP 
i := i + 1; 
DBMS_OUTPUT.put_line(i || ':' || rec_.name || ': ' || rec_.VALUE); ENDLOOP;  
CLOSEl_cursor; END; 
用这种方法,可以得到动态游标的所有数据,包括字段名称和字段值。但这种方法会遍历游标,即游标已经走到底了,不能再次使用了。而使用dbms_sql.describe_columns不会对游标的光标位置产生任何影响。两者优劣一目了然。 
  
对于JAVA、C等外部编程语言而言,要从游标中获取字段名称是比较容易的。可以用JAVA语言写一个获取游标字段名称的存储过程,并编译至数据库中,且做成PLSQL接口,这样就可以在PLSQL中调用了。有意者可以自己去研究下,或者向精通JAVA者请教。
展开阅读全文
博主设置当前文章不允许评论。

没有更多推荐了,返回首页