oracle存储过程

过程用于执行特定的操作，当建立过程时，即可以指定输入参数(in),也可以指定输出参数(out).通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传

递到应用环境。使用create or replace procedure 过程名 is 来建立存储过程。
create or replace procedure sp_pro_sal(spName varchar2,newSal number) is
--参数spName默认是in输入类型，可以指定输出类型的参数
--spName varchar2等同于spName in varchar2属于输入型参数，spName out varchar2属于输出型参数。
begin
update emp set sal=newSal where ename=spName;
end;
--过程声明的时候不用declare，直接写在is和begin之间;
java中调用存储过程：
String name = "SMITH";
int sal = 7788;
Connection ct = null;
CallableStatement cs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:lwb", "scott", "tiger");
cs = ct.prepareCall("{call sp_pro_sal(?,?)}");
cs.setString(1, name);
cs.setInt(2, sal);
cs.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
cs.close();
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
无返回值存储过程:
create or replace procedure sp_pro_inout
(bookId in number,bookName in varchar2,publishHouse in varchar2) is
begin
insert into testbook values(bookId,bookName,publishHouse);
end;
java调用该存储过程：
Connection ct = null;
CallableStatement cs = null;
int bookId=1;
String bookName = "Spring In Action";
String publish = "China Public";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:lwb", "SCOTT", "TIGER");
cs = ct.prepareCall("{call sp_pro_inout(?,?,?)}");
cs.setInt(1, bookId);
cs.setString(2, bookName);
cs.setString(3, publish);
cs.execute();
} catch(Exception ex) {
ex.printStackTrace();
} finally {
try {
cs.close();
ct.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
有返回值存储过程:
create or replace procedure sp_pro_haveval
(p_empno in number,p_ename out varchar2) is
begin
select ename into p_ename from emp where empno=p_empno;
end;
java调用该存储过程：
Connection ct = null;
CallableStatement cs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:lwb", "SCOTT", "TIGER");
cs = ct.prepareCall("{call sp_pro_haveval(?,?)}");//过程有几个参数，就写几个问号，并且后面也必须注册类型;
cs.setInt(1, 7788);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
String name = cs.getString(2);
System.out.println(name);
} catch(Exception ex) {
ex.printStackTrace();
} finally {
try {
cs.close();
ct.close();
} catch (SQLException e) {
// TODO Auto-generated catch :block
e.printStackTrace();
}
}
}
有返回值的存储过程(列表[结果集])
分为两步:
第一步：建一个包,在包中定义一个游标类型;
create or replace package testpackage AS
TYPE test_cursor is ref cursor;
end testpackage;
第二步：建立存储过程
create or replace procedure sp_pro_packagefen(p_deptno in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=p_deptno;
close p_cursor;--关闭游标;
end;
第三步：在java中调用该过程。
Connection ct = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:lwb", "SCOTT", "TIGER");
cs = ct.prepareCall("{call sp_pro_packagefen(?,?)}");
cs.setInt(1, 10);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//先执行再得到结果集;
cs.execute();
rs = (ResultSet) cs.getObject(2);
while(rs.next()) {
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
} catch(Exception ex) {
ex.printStackTrace();
} finally {
try {
rs.close();
cs.close();
ct.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
分页的存储过程：
第一步：定义三个输入参数(表名，页记录数量，当前页)，三个输出参数(总记录数,总页数，结果集)
注意在查找表的时候，表不是固定的，所以用连接字符串的方式去执行。
create or replace procedure pro_fenye
(p_tableName in varchar2,
p_pageSize in number,
p_pageNow in number,
p_rowCount out number,
p_pageCount out number,
p_result out testpackage.test_cursor
) is
v_begin number:=p_pageSize*(p_pageNow-1)+1;
v_end number:=p_pageNow*p_pageSize;
v_sql varchar2(1000);
begin
v_sql:='select count(*) from '||p_tableName;
execute immediate v_sql into p_rowCount;
if mod(p_rowCount,p_pageSize)=0 then
p_pageCount:=p_rowCount/p_pageSize;
else
p_pageCount:=p_rowCount/p_pageSize+1;
end if;
v_sql:='select * from
(select t1.*,rownum rn from (select * from '||p_tableName||') t1
where rownum <='||v_end||')
where rn>='||v_begin;
open p_result for v_sql;
--close p_result;
end;
第二步:java调用过程
Connection ct = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:lwb", "scott", "tiger");
cs = ct.prepareCall("{call pro_fenye(?,?,?,?,?,?)}");
cs.setString(1, "testemp");
cs.setInt(2, 20);
cs.setInt(3, 100001);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
int totalRows = cs.getInt(4);
int totalPages = cs.getInt(5);
rs = (ResultSet) cs.getObject(6);
System.out.println("总记录数："+totalRows);
System.out.println("总页数量："+totalPages);
while(rs.next()) {
System.out.println("用户名："+rs.getString(2)+" 工资:"+rs.getDouble(6));
}
} catch(Exception ex) {
ex.printStackTrace();
} finally {
try {
rs.close();
cs.close();
ct.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}