1.獲取動態SQL中的列名及類型
DECLAREl_curidINTEGER;
l_cntNUMBER;
l_desctab dbms_sql.desc_tab;
l_sqltextVARCHAR2(2000);BEGINl_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 IN 1 .. l_desctab.countLOOP
dbms_output.put_line(rpad(l_desctab(i).col_name, 30)||rpad(l_desctab(i).col_type, 3));ENDLOOP;
dbms_sql.close_cursor(l_curid);END;
查詢結果
OWNER 1OBJECT_NAME1SUBOBJECT_NAME1OBJECT_ID2DATA_OBJECT_ID2OBJECT_TYPE1CREATED12LAST_DDL_TIME12TIMESTAMP1STATUS1TEMPORARY1GENERATED1SECONDARY1NAMESPACE2EDITION_NAME1SHARING1EDITIONABLE1ORACLE_MAINTAINED1
col_type 1:VARCAHR2,2:NUMBER,12:DATE
2.使用USING方式綁定動態SQL,獲取列名及對應的值
--Created on 2017/10/11 by ADMINISTRATOR
DECLARETYPE typecursorIS REF CURSOR;
cursrc typecursor;
curidNUMBER;
desctab dbms_sql.desc_tab;
colcntNUMBER;
vnameVARCHAR2(50);
vnumNUMBER;
vdate DATE;
rownumberNUMBER := 5;
sqlstmtVARCHAR2(2000);BEGINsqlstmt := 'SELECT * FROM fnd_user WHERE rownum < :rownumber';--打開光標
OPEN cursrc FORsqlstmt
USING rownumber;--從本地動態SQL轉換為DBMS_SQL
curid :=dbms_sql.to_cursor_number(cursrc);
--獲取游標里面的數據列項數和每個數據列的屬性,比如列名,類型,長度等
dbms_sql.describe_columns(curid, colcnt, desctab);--定義列
FOR i IN 1.. colcnt LOOP
--此處是定義游標中列的讀取類型,可以定義為字符,數字和日期類型,IF desctab(i).col_type = 2 THENdbms_sql.define_column(curid, i, vnum);
ELSIF desctab(i).col_type= 12 THENdbms_sql.define_column(curid, i, vdate);ELSEdbms_sql.define_column(curid, i, vname,50);END IF;ENDLOOP;--DBMS_SQL包獲取行
--從游標中把數據檢索到緩存區(BUFFER)中,緩沖區 的值只能被函數COULUMN_VALUE()所讀取
WHILE dbms_sql.fetch_rows(curid) > 0LOOP
--函數column_value()把緩沖區的列的值讀入相應變量中。FOR i IN 1.. colcnt LOOPIF (desctab(i).col_type = 1) THENdbms_sql.column_value(curid, i, vname);
dbms_output.put_line(desctab(i).col_name || ' ' || vname || ',');
ELSIF (desctab(i).col_type= 2) THENdbms_sql.column_value(curid, i, vnum);
dbms_output.put_line(desctab(i).col_name || ' ' || vnum || ',');
ELSIF (desctab(i).col_type= 12) THENdbms_sql.column_value(curid, i, vdate);
dbms_output.put_line(desctab(i).col_name || ' ' ||to_char(vdate,'YYYY-MM-DD HH24:MI:SS') || ',');END IF;ENDLOOP;ENDLOOP;
dbms_sql.close_cursor(curid);END;
3.使用DBMS_SQL.BIND_VARIABLE方式傳遞參數,獲取列名及對應的值
DECLAREv_cursorNUMBER;
v_statNUMBER;
v_rowNUMBER;
v_idNUMBER;
v_noVARCHAR(100);
v_date DATE;
v_sqlVARCHAR(200);
s_idNUMBER;
s_date DATE;BEGINs_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 := dbms_sql.execute(v_cursor); --執行動態SQL語句。
LOOPEXIT WHEN dbms_sql.fetch_rows(v_cursor) <= 0; --fetch_rows在結果集中移動游標,如果未抵達末尾,返回1。
dbms_sql.column_value(v_cursor, 1, v_id); --將當前行的查詢結果寫入上面定義的列中。
dbms_sql.column_value(v_cursor, 2, v_no);
dbms_sql.column_value(v_cursor,3, v_date);
dbms_output.put_line(v_id|| ';' || v_no || ';' ||v_date);ENDLOOP;
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代碼:
DECLAREl_curidINTEGER;
l_cntNUMBER;
l_desctab dbms_sql.desc_tab;
l_sqltextVARCHAR2(2000);BEGINl_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 IN 1 .. l_desctab.countLOOP
dbms_output.put_line(rpad(l_desctab(i).col_name, 30)||rpad(l_desctab(i).col_type, 3));ENDLOOP;
dbms_sql.close_cursor(l_curid);END;
注意,必須使用 DBMS_SQL.OPEN_CURSOR 打開游標,否則,就不是這種處理方法了。
2、如果給的是一個REF CURSOR類型變量,而不知道SQL文本,該怎么辦呢?這里分兩種情況:
1) 如果數據庫版本是11g及以上,同樣很容易:使用dbms_sql.to_cursor_number(cursor) 得到游標的ID,再使用dbms_sql.describe_columns即可得到游標字段名稱。參考如
下代碼:
DECLARETYPE ref_cursorIS REF CURSOR;
l_cursor ref_cursor;
l_curidNUMBER;
l_col_cntNUMBER;
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 :=dbms_sql.to_cursor_number(l_cursor);
dbms_sql.describe_columns(l_curid, l_col_cnt, l_desc_tab);FOR i IN 1.. l_col_cnt LOOP
dbms_output.put_line(l_desc_tab(i).col_name);ENDLOOP;
dbms_sql.close_cursor(l_curid);END;
2) 如果數據庫版本低於11g,則PLSQL中僅有如下方法可以獲取字段名稱及字段值:
DECLAREl_cursor SYS_REFCURSOR;
iNUMBER := 0;CURSOR get_columns IS
SELECTt2.column_value.getrootelement() NAME,
extractvalue(t2.column_value,'node()') VALUEFROM (SELECT * FROM TABLE(xmlsequence(l_cursor))) t1,TABLE(xmlsequence(extract(t1.column_value, '/ROW/node()'))) t2;BEGIN
OPEN l_cursor FOR 'select owner,object_type,object_name from dba_objects where rownum<= 10';FOR rec_ INget_columns LOOP
i := i + 1;
dbms_output.put_line(rpad(i,2) || ':' || rpad(rec_.name,15) || ':' || lpad(rec_.value,15));ENDLOOP;CLOSEl_cursor;END;
用這種方法,可以得到動態游標的所有數據,包括字段名稱和字段值。但這種方法會遍歷游標,即游標已經走到底了,不能再次使用了。而使用dbms_sql.describe_columns不會對游標的光標位置產生任何影響。兩者優劣一目了然。
對於JAVA、C等外部編程語言而言,要從游標中獲取字段名稱是比較容易的。可以用JAVA語言寫一個獲取游標字段名稱的存儲過程,並編譯至數據庫中,且做成PLSQL接口,這樣就可以在PLSQL中調用了。有意者可以自己去研究下,或者向精通JAVA者請教。