ResultSet rset = stmt.executeQuery ( // "select BANNER from SYS.V_$VERSION" "select '你好' from dual" );
while (rset.next()) System.out.println (rset.getString(1)); // Print col 1 // now we will test procddure CallableStatement cs = conn.prepareCall("{call TESTP.TEST(?,?,?,?)}"); cs.setString(1,"bbbbb"); cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//输出参数 cs.registerOutParameter(3,oracle.jdbc.OracleTypes.INTEGER);//输出参数 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR);//输出参数 boolean flag = cs.execute(); String x=cs.getString(2); int y=cs.getInt(3); System.out.println(flag+":"+x+":"+y); ResultSet rs = (ResultSet)cs.getObject(4); rs.next(); x=rs.getString(1); System.out.println(x); stmt.close();
} } ----------------------------------------------------------- 包: CREATE OR REPLACE package testp as TYPE s_ret IS REF CURSOR; PROCEDURE TEST( s_in IN VARCHAR2, s_out OUT VARCHAR2, i_out OUT INTEGER, c_out OUT s_ret ) ; end testp; / CREATE OR REPLACE package body testp as PROCEDURE TEST( s_in IN VARCHAR2, s_out OUT VARCHAR2, i_out OUT INTEGER, c_out OUT s_ret ) IS begin s_out := s_in; i_out := 1234; OPEN c_out FOR select 888 ttt from dual; END TEST; END testp; / ---------------------------------------------- 我是用java写的.改成jsp应该简单
具体怎么写忘记了,好像是:
CallableStatement cstmt = con.prepareCall("{call getTestData(?, ?)}");
然后设置一下参数是输入还是输出.
对于有return 值的存储过程应该是:
CallableStatement cstmt = con.prepareCall("{call ?=getTestData(?, ?)}");
a.setInt(1, para1);
a.setInt(2, para2);
a.setString(3, para3);
a.registerOutParameter(4, Types.INTEGER);//输出
http://community.csdn.net/Expert/topic/4346/4346212.xml?temp=.7704126
(输入/出都有了,自定义的数组)
package test1;import java.sql.*;
import oracle.jdbc.*;
public class oratest { /**
* @param args
*/
public static void main(String[] args) throws SQLException {
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@192.168.0.115:1521:ora8", "test", "test");
// or oci9 @Service, userid, password
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery (
// "select BANNER from SYS.V_$VERSION"
"select '你好' from dual"
);
while (rset.next())
System.out.println (rset.getString(1)); // Print col 1
// now we will test procddure
CallableStatement cs = conn.prepareCall("{call TESTP.TEST(?,?,?,?)}");
cs.setString(1,"bbbbb");
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//输出参数
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.INTEGER);//输出参数
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR);//输出参数
boolean flag = cs.execute();
String x=cs.getString(2);
int y=cs.getInt(3);
System.out.println(flag+":"+x+":"+y);
ResultSet rs = (ResultSet)cs.getObject(4);
rs.next();
x=rs.getString(1);
System.out.println(x);
stmt.close();
}
} -----------------------------------------------------------
包:
CREATE OR REPLACE package testp
as
TYPE s_ret IS REF CURSOR;
PROCEDURE TEST(
s_in IN VARCHAR2,
s_out OUT VARCHAR2,
i_out OUT INTEGER,
c_out OUT s_ret
) ;
end testp;
/
CREATE OR REPLACE package body testp
as
PROCEDURE TEST(
s_in IN VARCHAR2,
s_out OUT VARCHAR2,
i_out OUT INTEGER,
c_out OUT s_ret
)
IS
begin
s_out := s_in;
i_out := 1234;
OPEN c_out FOR select 888 ttt from dual;
END TEST;
END testp;
/
----------------------------------------------
我是用java写的.改成jsp应该简单