一、通过PL/SQL工具连接上Oracle数据库，创建表zx_test_procedure
二、创建存储过程
①无返回值的存储过程
存储过程为：
create or replace procedure testa
(para1 in varchar2,para2 in varchar2) as
begin
insert into zx_test_procedure(i_id,i_name) values(para1,para2);
end testa;
Java调用的代码：
import java.sql.*;
public class TestProcedureOne {
public TestProcedureOne() {}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@10.10.1.29:1521:testdb";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
//CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "neb", "testneb");
CallableStatement proc = null; //创建执行存储过程的对象
proc = conn.prepareCall("{call TESTA(?,?) }"); //设置存储过程 call为关键字.
proc.setString(1, "400"); //设置第一个输入参数
proc.setString(2, "TestFour");//设置第二个输入参数
proc.execute();//执行
}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) {
}
}
}
} 结果如下：
②有返回值的存储过程（非列表）
存储过程为：
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT i_name INTO PARA2 FROM zx_test_procedure WHERE I_ID= PARA1;
END TESTB;Java调用代码如下：
import java.sql.*;
public class TestProcedureTwo {
public TestProcedureTwo() {}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@10.10.1.29:1521:testdb";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
//CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "neb", "testneb");
CallableStatement proc = null; //创建执行存储过程的对象
proc = conn.prepareCall("{call TESTB(?,?) }"); //设置存储过程 call为关键字.
proc.setString(1, "300"); //设置第一个输入参数
proc.registerOutParameter(2, Types.VARCHAR); //第二个参数输出参数，是varchar类型的
proc.execute();//执行
String test = proc.getString(2);//获得输出参数
System.out.println(test);
}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) {
}
}
}
}
结果如下：
TestThree
注意，这里的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 zx_test_procedure;
end testc;
可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。
Java调用代码：
import java.sql.*;
public class TestProcedureThree {
public TestProcedureThree() {}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@10.10.1.29:1521:testdb";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
//CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "neb", "testneb");
CallableStatement proc = null; //创建执行存储过程的对象
proc = conn.prepareCall("{call TESTC(?) }"); //设置存储过程 call为关键字.
//设置输出参数是一个游标.第一个参数,游标类型
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet) proc.getObject(1);
while(rs.next()) {
System.out.println(rs.getString(1) + ",,," + rs.getString(2));
}
}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) {
}
}
}
}结果如下：
100,,,TestOne
200,,,TestTwo
300,,,TestThree
400,,,TestFour
参考资料：
SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
关于oracle存储过程的若干问题备忘
1.在oracle中，数据表别名不能加as，如：
select a.appname from appinfo a;
--
正确
select a.appname
from appinfo
as a;
--
错误 也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。
select af.keynode into kn
from APPFOUNDATION af
where af.appid
=aid
and af.foundationid
=fid;
--
有into，正确编译
select af.keynode from APPFOUNDATION af
where af.appid
=aid
and af.foundationid
=fid;
--
没有into，编译报错，提示：Compilation
Error: PLS - 00428: an INTO clause is expected
in this
SELECT statement 3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。
select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...
4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错
select keynode into kn from APPFOUNDATION
where appid
=aid
and foundationid
=fid;
--
正确运行
select af.keynode into kn from APPFOUNDATION af
where
af.appid
=
appid
and
af.foundationid
=
foundationid;
--
运行阶段报错，提示
ORA - 01422:exact fetch returns more than requested number
of rows
5.在存储过程中，关于出现null的问题
假设有一个表A，定义如下：
create table A(
id varchar2( 50) primary key not null,
vcount number( 8) not null,
bid varchar2( 50) not null -- 外键
); 如果在存储过程中，使用如下语句：
select sum(vcount) into fcount from A where 如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
if fcount is null then
fcount:=0;
end if; 这样就一切ok了。
6.Hibernate调用oracle存储过程
this.pnumberManager.getHibernateTemplate().execute(
new HibernateCallback() ... {
public Object doInHibernate(Session session)
throws HibernateException, SQLException ...{
CallableStatement cs = session
.connection()
.prepareCall("{call modifyapppnumber_remain(?)}");
cs.setString(1, foundationid);
cs.execute();
return null;
}
});本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
