在sqlplus中建立如下的內容：1、程序包
SQL> create or replace package types 2 as 3 type cursorType is ref cursor; 4 end; 5 /
程序包已創建。
2、函數SQL> create or replace function sp_ListEmp return types.cursortype 2 as 3 l_cursor types.cursorType; 4 begin 5 open l_cursor for select id, title from cf_news order by id;--表的名字 6 return l_cursor; 7 end; 8 /
函數已創建。
3、過程
SQL> create or replace procedure getemps( p_cursor in out types.cursorType ) 2 as 3 begin 4 open p_cursor for select id, title from cf_news order by id;--表的名字 5 end; 6 /
過程已創建。
4、建立一個可執行的Java控制台程序
import java.sql.*; import Java.io.*; import Oracle.jdbc.driver.*;
class GetValues { public static void main (String args []) throws SQLException, ClassNotFoundException { String driver_class = "oracle.jdbc.driver.OracleDriver"; String connect_string = "jdbc:Oracle:thin:@127.0.0.1:1521:database";
String query = "begin :1 := sp_listEmp; end;"; //此處調用前面建立的函數！ Connection conn;
Class.forName(driver_class); conn = DriverManager.getConnection(connect_string, "scott", "tiger");
CallableStatement cstmt = conn.prepareCall(query); cstmt.registerOutParameter(1,OracleTypes.CURSOR); cstmt.execute(); ResultSet rset = (ResultSet)cstmt.getObject(1);
while (rset.next ()) System.out.println( rset.getString (1) ); cstmt.close(); } }
