存储过程与存储关系
定义:指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数(相当于java中的方法)。在某种意义上来说存储过程与存储函数没什么区别,唯一的区别为存储过程没有返回值,而存储函数有返回值,但是在之后存储过程中可以传入out输出类型的参数之后,这两就机会没有区别了
存储过程
语法:create [or replace] PROCEDURE 过程名(参数列表)
AS
PLSQL子程序体;
调用存储过程
1、exec 存储过程名字();
2、begin
存储过程名字();
end;
/
例:给指定的员工涨100工资,并且打印涨前和涨后的薪
create or replace procedure raiseSalary(eno in[刘志跃1] number)
as
psal emp.sal%type;
begin
select sal into psal from emp where empno=eno;
update empset sal=sal+100 where empno=eno;[刘志跃2]
打印涨前和涨后的薪水
dbms_output.put_line('涨前:'||psal||' 涨后:'||(psal+100));
end;
/
存储函数
定义:存储函数(Function)为一命名的存储程序,可带参数,并返回一计算值。函数和过程的结构类似,但必须有一个RETURN子句,用于返回函数值。函数说明要指定函数名、结果值的类型,以及参数类型等。
语法:CREATE [OR REPLACE] FUNCTION函数名(参数列表)
RETURN 函数值类型
AS
PLSQL子程序体;
例:查询某个员工的年收入
create or replace function queryEmpIncome(enoin number)
return number[刘志跃4]
as
--月薪和奖金
psalemp.sal%type;
pcommemp.comm%type;
begin
--得到该员工的月薪和奖金
select sal,comm into psal,pcomm from emp where empno=eno;
returnpsal*12+nvl(pcomm,0);[刘志跃5]
end;
/
out类型的参数
什么时候用存储过程,什么时候用存储函数
建议如果只有一个返回值,用存储函数;否则如果没有返回值或者有多个返回值,就用存储过程,存储过程也可以返回,这时就用到out类型了。
例:查询并返回某个员工的姓名 月薪 职位
create or replace procedure queryEmpInfo(enoin number,
penameout varchar2[刘志跃6] ,
psal out number,
pjob out [刘志跃7] varchar2)
as
begin
--查询并返回某个员工的姓名 月薪 职位
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
JDBC调用存储过程和存储函数
public class TestOracle {
/*
* JDBC调用存储过程
create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
*/
@Test
public void testProcedure (){
String sql = "{call queryEmpInfo(?,?,?,?)} ";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnect();
call = conn.prepareCall(sql);
//赋值
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);
System.out.println(name, sal, job);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release (conn, call, null);
}
}
/*
* JDBC调用存储函数
* create or replace function queryEmpIncome(eno in number)
return number
*/
@Test
public void testFunction (){
String sql = "{?=call queryEmpIncome(?)} ";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnect();
call = conn.prepareCall(sql);
//out参数
call.registerOutParameter(1, OracleTypes.NUMBER);
//赋值
call.setInt(2, 7839);
call.execute();
//取出结果
System.out.println(call.getDouble(1));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
在out参数中使用游标(用来解决out参数太多)
步骤:1、申明程序包结构(包头只负责声明存储过程)
2、创建包体(包体只负责实现存储过程)
例:查询某个部门中所有员工的所有信息
注:在SQL Developer中的程序包中新建程序包,就出现包头了
包头(声明存储过程)
CREATE OR REPLACE PACKAGEMYPACKAGE AS
type empcursor is ref cursor;
procedure queryEmpList (dno in number,empList out empcursor);
END MYPACKAGE;
包体(在包上点击右键就会出现新建包体,并且会将所有的存储过程与函数写到包体中)
CREATE OR REPLACE PACKAGE BODYMYPACKAGE AS
procedure queryEmpList(dno in number,empList outempcursor ) AS
BEGIN
open empList for select * from emp where deptno=dno;
END queryEmpList;
END MYPACKAGE;
用java程序调用有光标的存储过程
@Test
public void testCursor(){
String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs =null;
try {
conn = JDBCUtils.getConnect();
call = conn.prepareCall(sql);
//赋值
call.setInt(1, 20);
//out参数
call.registerOutParameter(2, OracleTypes.CURSOR );
//执行
call.execute();
//取出集合
rs = ((OracleCallableStatement )call).getCursor(2);
while(rs.next()){
System.out.println(rs.getString("ename"));
System.out.println(rs.getString("empjob"));
System.out.println(rs.getDouble("sal"));
System.out.println("===================");
}
}catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs );
}
}
}