?http://www.2cto.com/database/201109/102884.html

1.先定義一個返回數組類型的方法
create or replace type my_table_type is table of varchar2(20);
?
create or replace function func
return my_table_type
is
i my_table_type:=my_table_type();
begin
??? select name bulk collect into i from emps;
??? return i;
end;
?
2.在JDBC中調用,如果返回的是table變量
?
Java代碼?
public void SelectAgus(String sql)?
??? {?
???????? OracleCallableStatement call = null;?
???????? try?
???????? {???
???????????? call = (OracleCallableStatement) con.prepareCall(sql);?
???????????? //如果返回的是table則用ARRAY類型,如果返回的是OBJECT的就用STRUCT?
???????????? //第三個參數是定義table的類型名?
???????????? call.registerOutParameter(1, OracleTypes.ARRAY,"MY_TABLE_TYPE");?
???????????? call.execute();?
???????????? //獲取第一個參數(這里即返回值)?
???????????? ARRAY array = call.getARRAY(1);?
???????????? //獲取表中的元素?
???????????? Datum[] dat = array.getOracleArray();?
???????????? //遍歷依次打印?
???????????? for(Datum d : dat)?
???????????? {?
???????????????? System.out.println(new String(d.getBytes()));?
???????????? }?
???????? }catch(Exception e)?
???????? {?
???????????? e.printStackTrace();?
???????? }?
??? }?
?? 2.如果定義的是嵌套表結構,
?? 如下定義:
?? create or replace type all_table is object(id number,name varchar2(20));
create or replace type emps_table_type is table of all_table;
--創建一個函數,返回類型為emps_table_type;
create or replace function funcc
return emps_table_type
is
i emps_table_type;
begin
?? --把emps中的ID,NAME屬性值全部讀取到i中
?? select all_table(id,name) bulk collect into i from emps;
?? return i;--返回table
end;

???
Java代碼?
public void SelectAgus(String sql)?
??? {?
??????? OracleCallableStatement call = null;?
??????? try?
??????? {?
??????????? call = (OracleCallableStatement) con.prepareCall(sql);?
??????????? call.registerOutParameter(1, OracleTypes.ARRAY,"EMPS_TABLE_TYPE");?
??????????? call.execute();?
??????????? ARRAY array = call.getARRAY(1);?
??????????? Datum[] dat = array.getOracleArray();?
??????????? for(Datum d : dat)?
??????????? {?? //獲取了行后,要獲取一行中的元素?
??????????????? STRUCT struct = (STRUCT)d;?
??????????????? //這里有可能會出現亂碼,所以我分別用了兩種方式獲取元素?
??????????????? Datum[] d1 = struct.getOracleAttributes();?
??????????????? Object[] d2 = struct.getAttributes();?
??????????????? System.out.println("ID="+d2[0]+"? "+"NAME="+?
??????????????????????? new String(d1[1].getBytes()));?
??????????? }?
?????????????
??????? }catch(Exception e){?
??????????? e.printStackTrace();?
??????? }?
??? }?

?

作者“aazham”

?

總結

以上是生活随笔為你收集整理的Oracle中table变量在JDBC中的运用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。