create package test_age as type mycursor is ref cursor; end; / create procedure pro(p_rc out test_age.mycursor) as begin open p_rc for select * from tab; end; /
CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; PROCEDURE get (p_rc OUT myrctype); END pkg_test; /CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE get (p_rc OUT myrctype) IS OPEN p_rc FOR SELECT ID, NAME, sex, address, postcode, birthday FROM student; END get; END pkg_test; /
SQL> select * from aa; ID NAME ---------- -------- 1 猪八戒 2 孙悟空 CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; / create or replace PROCEDURE get(p_rc OUT pkg_test.myrctype) IS sqlstr VARCHAR2 (50); BEGIN sqlstr:='select * from aa'; OPEN p_rc FOR sqlstr; END get; / 在窗体上放一个ADOStoredproc 设置 procedurename=get prepared = true active = true 还有 connectstring 代码如下,好运! procedure TForm1.Button1Click(Sender: TObject); begin adostoredproc1.close; adostoredproc1.open; label1.caption:= ADOStoredProc1.fields[1].asstring; while not adostoredproc1.eof do begin label2.caption:= ADOStoredProc1.fields[1].asstring; adostoredproc1.Next; end; end;
我想问大家一个问题,这个cursor怎么关闭??:)
返回的游标是不需要手动关闭的,由Oracle自动关闭。
我这里收了一片,你看看吧 create or replace package pkg_test as type cur_test is ref cursor; -- 定義一個cursor的type end pkg_test; / create or replace procedure p_test ( v_cur out pkg_test.cur_test ) as v_sql varchar2(100); -- begin v_sql := 'select a1,a2 from test'; OPEN v_cur FOR v_sql; -- exception when others then DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm ); end p_test; / Java程式: …… CallableStatement call = conn.prepareCall("{ call p_test(?) }"); call.registerOutParameter(1, OracleTypes.CURSOR);// 註冊out參數的SQL數據類型 call.execute(); ResultSet rs=(ResultSet)call.getObject(1);// 取得得數据結果集合 while(rs.next()) ……
看这个例子. import java.sql.*; import java.util.Collection; import java.util.ArrayList; /** * * <p>Title: </p> * <p>Description:存储过程调用带参数,返回游标 </p> * <p>Copyright: Copyright (c) 2003</p> * <p>Company: </p> * @author not attributable * @version 1.0 */ public class Protest { private static Connection conn = null; private static oracle.jdbc.OracleCallableStatement call = null; private static ResultSet rs = null; private static String url = "jdbc:oracle:thin:@192.168.100.145:1521:kdc"; private static String name = "liuyi"; private static int cout = 0; public static void main(String[] args){ try{ Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url,"kdcerp2","123"); call = (oracle.jdbc.OracleCallableStatement)conn.prepareCall("{call mytest.zhbtest(?,?)}"); call.setString(1, "4050608006"); call.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); call.execute(); rs = call.getCursor(2); while(rs.next()){ System.out.println(rs.getString(1)); cout++; } System.out.println(cout); }catch(java.lang.ClassNotFoundException e){ e.printStackTrace(); }catch(SQLException e){ System.out.println(e.toString()); } } }下面是存储过程: CREATE OR REPLACE PACKAGE BODY MyTest IS PROCEDURE zhbtest(P_CUSTOMER_ID c_well.wellno %TYPE, Re_CURSOR OUT T_CURSOR) IS V_CURSOR T_CURSOR; BEGIN OPEN V_CURSOR FOR select wellname from c_well ; Re_CURSOR := V_CURSOR; END; END;
as
type mycursor is ref cursor;
end;
/
create procedure pro(p_rc out test_age.mycursor)
as
begin
open p_rc for select * from tab;
end;
/
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_rc OUT myrctype)
IS OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
END get;
END pkg_test;
/
要查询纪录好像只能直接用查询执行啊
PS:如果可以的话,请告诉我,谢谢
---------- --------
1 猪八戒
2 孙悟空
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/ create or replace PROCEDURE get(p_rc OUT pkg_test.myrctype)
IS
sqlstr VARCHAR2 (50);
BEGIN
sqlstr:='select * from aa';
OPEN p_rc FOR sqlstr;
END get;
/ 在窗体上放一个ADOStoredproc
设置 procedurename=get
prepared = true
active = true
还有 connectstring
代码如下,好运! procedure TForm1.Button1Click(Sender: TObject);
begin
adostoredproc1.close;
adostoredproc1.open;
label1.caption:= ADOStoredProc1.fields[1].asstring;
while not adostoredproc1.eof do
begin
label2.caption:= ADOStoredProc1.fields[1].asstring;
adostoredproc1.Next;
end;
end;
create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/
Java程式:
……
CallableStatement call = conn.prepareCall("{ call p_test(?) }");
call.registerOutParameter(1, OracleTypes.CURSOR);// 註冊out參數的SQL數據類型
call.execute();
ResultSet rs=(ResultSet)call.getObject(1);// 取得得數据結果集合
while(rs.next())
……
import java.sql.*;
import java.util.Collection;
import java.util.ArrayList;
/**
*
* <p>Title: </p>
* <p>Description:存储过程调用带参数,返回游标 </p>
* <p>Copyright: Copyright (c) 2003</p>
* <p>Company: </p>
* @author not attributable
* @version 1.0
*/
public class Protest {
private static Connection conn = null;
private static oracle.jdbc.OracleCallableStatement call = null;
private static ResultSet rs = null;
private static String url = "jdbc:oracle:thin:@192.168.100.145:1521:kdc";
private static String name = "liuyi";
private static int cout = 0; public static void main(String[] args){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url,"kdcerp2","123");
call = (oracle.jdbc.OracleCallableStatement)conn.prepareCall("{call mytest.zhbtest(?,?)}");
call.setString(1, "4050608006");
call.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
call.execute();
rs = call.getCursor(2);
while(rs.next()){
System.out.println(rs.getString(1));
cout++;
}
System.out.println(cout);
}catch(java.lang.ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
System.out.println(e.toString());
}
}
}下面是存储过程:
CREATE OR REPLACE PACKAGE BODY MyTest
IS PROCEDURE zhbtest(P_CUSTOMER_ID c_well.wellno %TYPE, Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
OPEN V_CURSOR FOR
select wellname from c_well ;
Re_CURSOR := V_CURSOR;
END;
END;
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
callst = conn.prepareCall("{call p_dtzjzhfb_getsql(?)}");
callst.registerOutParameter(1, java.sql.Types.VARCHAR);
rs = callst.executeQuery();
String temp = callst.getString(1);
System.out.println(temp);
}
catch (java.lang.ClassNotFoundException e) {
e.printStackTrace();
}
catch (SQLException e) {
System.out.println(e.toString());
}