上节介绍了PLSQL,但是在Java中我们不能直接调用PLSQL,而是调用存储过程和存储函数(就是用PLSQL写的)。
存储过程和存储函数区别:
存储函数可以通过return返回值,而存储过程则不行。
存储过程语法:
-- 无参的
create or replace procedure 存储过程名
as
--说明部分
begin
--语句;
end;
/
-- 有参的
-- 给指定的员工涨100,并且打印涨前和涨后的薪水
create or replace procedure raiseSalary(eno in number)
as
--定义变量保存涨前的薪水
psal emp.sal%type;
begin
select sal into psal from emp where empno=eno;
update emp set sal = sal+100 where empno=eno;
--要不要commit? 一般不要
dbms_output.put_line('涨前:'||psal||' 涨后:'||(psal+100));
end;
/
存储函数demo
--查询某个员工的年收入
create or replace function queryempincome(eno in number)
return number
as
--月薪和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
--返回年收入
return psal*12+nvl(pcomm,0);
end;
/
存储函数的功能可以完全通过存储过程+out输出替代,所以一般来说,存储过程即可满足功能。
--查询某个员工的姓名 月薪和职位
create or replace procedure queryempinfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
Java调用存储过程demo
String sql = "{call queryempinfo(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//对于in参数,赋值
call.setInt(1, 7839);
//对于out参数,声明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
//调用
call.execute();
//取出结果
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
从上面的demo中,我们可以看到,有三个输出参数。如果有三百个输出参数,我们不能声明三百次,此时我们可以用游标来解决。
游标cursor,类似于Resultset。想使用游标,我们要引入包(package)。在包中写PLSQL。
demo
首先在数据库中建package,先建立包头
--包头
CREATE OR REPLACE PACKAGE MYPACKAGE
AS
-- 声明类 empcursor 是cursor类型
type empcursor is ref cursor;
-- PLSQL 输出参数是empcursor类型
procedure queryEmpList(dno in number,empList out empcursor);
END MYPACKAGE;
在包体中实现这个PLSQL
-- 包体
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS
-- 查询某部门所有员工
procedure queryEmpList(dno in number,empList out empcursor) AS
BEGIN
open empList for select * from emp where deptno=dno;
END queryEmpList;
END MYPACKAGE;
Java中调用此包
public void testCursor(){
String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//设置部门号
call.setInt(1, 20);
//声明out
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行
call.execute();
//取出结果,要将call 强转成 oracle类型的
rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
String name = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(name+"\t"+sal);
}
}catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs);
}
}