一无返回值的存储过程
存储过程为
CREATE OR REPLACE PROCEDURE TESTA(PARA IN VARCHARPARA IN VARCHAR) AS
BEGIN
INSERT INTO HYQB_ID (I_IDI_NAME) VALUES (PARA PARA)
END TESTA
然后呢在java里调用时就用下面的代码
package comhyqsrc
import javasql*
import javasqlResultSet
public class TestProcedureOne {
public TestProcedureOne() {
}
public static void main(String[] args ){
String driver = oraclejdbcdriverOracleDriver
String strUrl = jdbcoraclethin@ hyq
Statement stmt = null
ResultSet rs = null
Connection conn = null
CallableStatement cstmt = null
try {
ClassforName(driver)
conn = DriverManagergetConnection(strUrl hyq hyq )
CallableStatement proc = null
proc = connprepareCall({ call HYQTESTA(??) })
procsetString( )
procsetString( TestOne)
procexecute()
}
catch (SQLException ex) {
exprintStackTrace()
}
catch (Exception ex) {
exprintStackTrace()
}
finally{
try {
if(rs != null){
rsclose()
if(stmt!=null){
stmtclose()
}
if(conn!=null){
connclose()
}
}
}
catch (SQLException ex) {
}
}
}
}
当然了这就先要求要建张表TESTTB里面两个字段(I_IDI_NAME)
二有返回值的存储过程(非列表)
存储过程为
CREATE OR REPLACE PROCEDURE TESTB(PARA IN VARCHARPARA OUT VARCHAR) AS
BEGIN
SELECT INTO PARA FROM TESTTB WHERE I_ID= PARA
END TESTB
在java里调用时就用下面的代码
package comhyqsrc
public class TestProcedureTWO {
public TestProcedureTWO() {
}
public static void main(String[] args ){
String driver = oraclejdbcdriverOracleDriver
String strUrl = jdbcoraclethin@hyq
Statement stmt = null
ResultSet rs = null
Connection conn = null
try {
ClassforName(driver)
conn = DriverManagergetConnection(strUrl hyq hyq )
CallableStatement proc = null
proc = connprepareCall({ call HYQTESTB(??) })
procsetString( )
procregisterOutParameter( TypesVARCHAR)
procexecute()
String testPrint = procgetString()
Systemoutprintln(=testPrint=is=+testPrint)
}
catch (SQLException ex) {
exprintStackTrace()
}
catch (Exception ex) {
exprintStackTrace()
}
finally{
try {
if(rs != null){
rsclose()
if(stmt!=null){
stmtclose()
}
if(conn!=null){
connclose()
}
}
}
catch (SQLException ex) {
}
}
}
}
}
注意这里的procgetString()中的数值并非任意的而是和存储过程中的out列对应的如果out是在第一个位置那就是procgetString()如果是第三个位置就是procgetString()当然也可以同时有多个返回值那就是再多加几个out参数了
三返回列表
由于oracle存储过程没有返回值它的所有返回值都是通过out参数来替代的列表同样也不例外但由于是集合所以不能用一般的参数必须要用pagkage了所以要分两部分
建一个程序包如下
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR
end TESTPACKAGE
建立存储过程存储过程为
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGETest_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQTESTTB
END TESTC
可以看到它是把游标(可以理解为一个指针)作为一个out 参数来返回值的
在java里调用时就用下面的代码
package comhyqsrc
import javasql*
import javaioOutputStream
import javaioWriter
import javasqlPreparedStatement
import javasqlResultSet
import oraclejdbcdriver*
public class TestProcedureTHREE {
public TestProcedureTHREE() {
}
public static void main(String[] args ){
String driver = oraclejdbcdriverOracleDriver
String strUrl = jdbcoraclethin@hyq
Statement stmt = null
ResultSet rs = null
Connection conn = null
try {
ClassforName(driver)
conn = DriverManagergetConnection(strUrl hyq hyq)
CallableStatement proc = null
proc = connprepareCall({ call hyqtestc(?) })
procregisterOutParameter(oraclejdbcOracleTypesCURSOR)
procexecute()
rs = (ResultSet)procgetObject()
while(rsnext())
{
Systemoutprintln(<tr><td> + rsgetString() + </td><td>+rsgetString()+</td></tr>)
}
}
catch (SQLException ex) {
exprintStackTrace()
}
catch (Exception ex) {
exprintStackTrace()
}
finally{
try {
if(rs != null){
rsclose()
if(stmt!=null){
stmtclose()
}
if(conn!=null){
connclose()
}
}
}
catch (SQLException ex) {
}
}
}
}
取得存储过程返回的值
CallableStatement cs = connprepareCall({call proc_fbquery(???)}) //调用存储过程cssetString(mem)cssetInt(n)csregisterOutParameter(oraclejdbcOracleTypesCURSOR)csexecute()rs=(ResultSet)csgetObject()
对存储过程赋值时
CallableStatement cs= connprepareCall({call proc_fbquery(?)}) //调用存储过程csregisterOutParameter(oraclejdbcOracleTypesCURSOR)cssetCursorName(cusorName) //提供result的名称cssetString(rs)rs=csexecuteQuery()rs =(ResultSet)csgetObject()