一:无返回值的存储过程
存储过程为:
create or replace procedure testa(para1 in varchar2 para2 in varchar2) as
begin
insert into hyq.b_id (i_id i_name) values (para1 para2)
end testa
然后呢,在java里调用时就用下面的代码:
package com.hyq.src
import java.sql.
import java.sql.resultset
public class testprocedureone {
public testprocedureone() {
}
public static void main(string[] args ){
string driver = " oracle.jdbc.driver.oracledriver"
string strurl = " jdbc:oracle:thin: 127.0.0.1:1521: hyq "
statement stmt = null
resultset rs = null
connection conn = null
callablestatement cstmt = null
try {
class.forname(driver)
conn = drivermanager.getconnection(strurl " hyq " " hyq " )
callablestatement proc = null
proc = conn.preparecall(" { call hyq.testa(? ?) }" )
proc.setstring(1 " 100" )
proc.setstring(2 " testone" )
proc. ute()
}
catch (sqlexception ex2) {
ex2.printstacktrace()
}
catch (exception ex2) {
ex2.printstacktrace()
}
finally{
try {
if(rs != null){
rs.close()
if(stmt!=null){
stmt.close()
}
if(conn!=null){
conn.close()
}
}
}
catch (sqlexception ex1) {
}
}
}
}
当然了,这就先要求要建张表testtb 里面两个字段(i_id,i_name)。
二:有返回值的存储过程(非列表)
存储过程为:
create or replace procedure testb(para1 in varchar2 para2 out varchar2) as
begin
select into para2 from testtb where i_id= para1
end testb
在java里调用时就用下面的代码:
package com.hyq.src
public class testproceduretwo {
public testproceduretwo() {
}
public static void main(string[] args ){
string driver = " oracle.jdbc.driver.oracledriver"
string strurl = " jdbc:oracle:thin: 127.0.0.1:1521:hyq"
statement stmt = null
resultset rs = null
connection conn = null
try {
class.forname(driver)
conn = drivermanager.getconnection(strurl " hyq " " hyq " )
callablestatement proc = null
proc = conn.preparecall(" { call hyq.testb(? ?) }" )
proc.setstring(1 " 100" )
proc.registeroutparameter(2 types.varchar)
proc. ute()
string testprint = proc.getstring(2)
system.out.println(" =testprint=is=" +testprint)
}
catch (sqlexception ex2) {
ex2.printstacktrace()
}
catch (exception ex2) {
ex2.printstacktrace()
}
finally{
try {
if(rs != null){
rs.close()
if(stmt!=null){
stmt.close()
}
if(conn!=null){
conn.close()
}
}
}
catch (sqlexception ex1) {
}
}
}
}
}
注 意,这里的proc.getstring(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是 proc.getstring(1),如果是第三个位置,就是proc.getstring(3),当然也可以同时有多个返回值,那就是再多加几个out 参数了。
三:返回列表
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
1, 建一个程序包。如下:
create or replace package testpackage as
type test_cursor is ref cursor
end testpackage
2,建立存储过程,存储过程为:
create or replace procedure testc(p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select from hyq.testtb
end testc
可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
在java里调用时就用下面的代码:
package com.hyq.src
import java.sql.
import java.io.outputstream
import java.io.writer
import java.sql.preparedstatement
import java.sql.resultset
import oracle.jdbc.driver.
public class testprocedurethree {
public testprocedurethree() {
}
public static void main(string[] args ){
string driver = " oracle.jdbc.driver.oracledriver"
string strurl = " jdbc:oracle:thin: 127.0.0.1:1521:hyq"
statement stmt = null
resultset rs = null
connection conn = null
try {
class.forname(driver)
conn = drivermanager.getconnection(strurl " hyq" " hyq" )
callablestatement proc = null
proc = conn.preparecall(" { call hyq.testc(?) }" )
proc.registeroutparameter(1 oracle.jdbc.oracletypes.cursor)
proc. ute()
rs = (resultset)proc.getobject(1)
while(rs.next())
{
system.out.println(" < tr> < td> " + rs.getstring(1) + " < /td> < td> " +rs.getstring(2)+" < /td> < /tr> " )
}
}
catch (sqlexception ex2) {
ex2.printstacktrace()
}
catch (exception ex2) {
ex2.printstacktrace()
}
finally{
try {
if(rs != null){
rs.close()
if(stmt!=null){
stmt.close()
}
if(conn!=null){
conn.close()
}
}
}
catch (sqlexception ex1) {
}
}
}
}