前言
学习存储过程的前提是，了解PLSQL的语法和编写方式。
需要了解PLSQL，请查看之前的总结。
我们以下的表操作可能会基于以下两张表:
我们创建一个员工表和部门表:
员工信息表
create table EMP(
EMPNO NUMBER,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER,
HIREDATE DATE,
SAL BINARY_DOUBLE,
COMM BINARY_DOUBLE,
DEPTNO NUMBER
);
其中job是职位，mgr是该员工的上司的id，sal是工资，comm是提成，deptno是所属部门。
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
1110 张三 主管 1110 12-3月 -14 5200 0 20
1111 李四 销售 1116 03-11月-15 3400 500 30
1112 王五 销售 1116 25-4月 -12 4400 800 30
1113 赵二 后勤 1110 30-5月 -11 3450 0 40
1114 李磊磊 会计 1110 22-12月-15 2500 0 50
1115 张少丽 销售 1110 11-3月 -16 2400 1400 30
1116 林建国 主管 1116 22-1月 -16 5700 0 20
1117 马富邦 后勤 1116 22-7月 -13 2800 0 40
1118 沈倩 会计 1116 06-5月 -10 2100 0 50
部门表
create table dept(
DEPTNO NUMBER,
DNAME VARCHAR2(50)
);
SQL> select * from dept t;
DEPTNO DNAME
-------- --------
20 管理部门
30 销售部门
40 后勤部门
50 金融部门
一、介绍
存储过程和存储函数
指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
存储过程、存储函数的区别在于，前者没有返回值，后者有返回值。
二、存储过程
(1)创建存储过程
用create procedure命令建立存储过程。
语法:
create [or replace] procedure 过程名(参数列表)
as
PLSQL子程序体;
简单的例子，打印HelloWorld
create or replace procedure sayHelloWorld
as
begin
dbms_output.put_line('HelloWorld!');
end;
/
使用PLSQL-developer执行结果:
(2)调用存储过程
1.exec sayHelloWorld();
使用Command控制台使用此语句，结果:
2.在存储过程中调用存储过程
如
begin
......
sayHelloWorld();
end;
/
(3)例子
1.带参数的存储过程
需求:给指定的员工涨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;
--打印
dbms_output.put_line('涨前'||psal||' 涨后:'||(psal+100));
end;
/
执行的时候，可以直接执行exec raiseSalary(1110)，给编号为1110的员工涨薪100元。
执行完之后别忘记commit。
也可以在存储过程中直接调用raiseSalary(1110)。
注意:不要在存储函数中进行commit或者rollback。
练习:为指定的员工增加指定额度的工资(传递多个参数)
create or replace procedure raiseSalaryByCondition(eno in number,rsal in number)
as
--变量
psal emp.sal%type;
begin
--得到涨前薪水
select sal into psal from emp where empno=eno;
--涨工资
update emp set sal=sal+rsal where empno=eno;
--打印
dbms_output.put_line('涨前'||psal||' 涨后:'||(psal+rsal));
end;
/
二、存储函数
(1)介绍
函数(Function)为一命名的存储程序，可带参数，并返回一计算值。函数
和过程的结构类似，但必须有一个return子句，用于返回函数值。函数说明
要指定函数名、结果值的类型，以及参数类型等。
(2)语法
简历存储函数的语法:
create [or replace] function 函数名(参数列表)
return 函数值类型
as
PLSQL子程序体
(3)例子
查询某个员工的年收入(一年的月薪+奖金和)
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;
/
在控制台编译运行，查询员工号为1110的年收入:
我们查出编号1110的员工的月薪为6820，奖金为0
6820*12+0=81840，说明我们的程序运行的结果是正确的。
注意，调用存储函数的方式只能在plsql语句中调用，而且必须要有变量去接受返回的参数。
(如果只做打印可以忽略)
(4)过程和函数中的in和out
一般来讲，过程和函数的区别在于函数可以有一个返回值，而过程没有返回值。
但过程和函数都可以通过out指定一个或多个输出函数。我们可以利用out参数，在
过程和函数中实现返回多个值。
例子:查询某个员工的姓名、月薪、和职位。
这里我们使用的是存储过程:
create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,job into pename,psal,pjob from emp where empno=eno;
end;
/
我们来测试一下:
测试函数:
set serveroutput on
declare
eno number;
pename varchar2(200);
psal number;
pjob varchar2(200);
begin
eno:=1110;
queryEmpInfo(
eno => eno,
pename => pename,
psal => psal,
pjob => pjob);
dbms_output.put_line('编号1110员工的姓名为:'||pename||'薪水为:'||psal||'职位为:'||pjob);
end;
/
测试结果:
与直接查询的结果一样:
证明我们的函数是正确的。
既然存储过程和存储函数可以实现一样的功能，为什么还需要两者共同存在呢？
因为老版本中既有存储过程和存储函数，但是新版本中可能只有两者之一，所以
要保证程序的兼容性，要保留两者。
什么时候使用存储过程/存储函数？
原则:如果只有一个返回值，用存储函数；否则，就用存储过程。
三、使用Java来调用存储过程
打开我们的Eclipse，创建一个JavaProject工程，名为“TestOracle”
我们在工程下创建一个lib文件夹，将oracle的数据库驱动jar包放进去，
并将其Add to BuildPath：
然后我们创建一个JDBCUtils类，用于获取数据库的连接:
内容:
package demo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver="oracle.jdbc.OracleDriver";
private static String url="jdbc:oracle:thin:@localhost:1521:XE";
private static String user="jack";
private static String password="1234";
static{
try {
Class.forName(driver); //加载驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void release(Connection conn,Statement st,ResultSet rs){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn=null;//垃圾回收
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st=null;//垃圾回收
}
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs=null;//垃圾回收
}
}
}
}
创建并编写测试类:
内容
package demo.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleTypes;
import org.junit.Test;
import demo.util.JDBCUtils;
public class OracleTest {
/*create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,job into pename,psal,pjob from emp where empno=eno;
end;
/*/
@Test
public void testProcedure(){
//测试存储过程
//格式{call <procedure-name>[{<arg1>,<arg2>,...}]}
String sql="{call queryEmpInfo(?,?,?,?)}";
Connection conn=null;
//CallableStatement是用于执行SQL存储过程的接口
CallableStatement call=null;
try {
conn=JDBCUtils.getConnection();
call=conn.prepareCall(sql);
//赋值
call.setInt(1, 1110);
//对于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("工号为1110的员工信息:");
System.out.println("姓名:"+name);
System.out.println("薪水:"+sal);
System.out.println("职位:"+job);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
/*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;
/*/
@Test
public void testFunction(){
//测试存储函数
//格式{?= call <procedure-name>[{<arg1>,<arg2>,...}]}
String sql="{?=call queryEmpIncome(?)}";
Connection conn=null;
//CallableStatement是用于执行SQL存储过程的接口
CallableStatement call=null;
try {
conn=JDBCUtils.getConnection();
call=conn.prepareCall(sql);
//第一个问号是返回值,要申明一下
call.registerOutParameter(1, OracleTypes.NUMBER);
//赋值
call.setInt(2, 1110);
//调用
call.execute();
//取出结果
double s_sal = call.getDouble(1);
System.out.println("工号为1110的员工年薪:");
System.out.println(s_sal);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
}
注意这里要加入Junit4的环境。
首先测试testProcedure()方法，测试结果:
然后测试testFunction()方法，测试结果:
四、问题
我们的存储过程虽然有out，但是如果我们一条数据返回的字段特别多，就不能写那么多out参数了。
这就要求我们使用之前PLSQL中的光标来解决这个问题了。
即是:在out参数中使用光标。
(1)申明包结构
根据员工的部门号查询员工信息，要求返回该部门所有员工的所有信息
使用光标作为out参数
1.创建一个包:mypackage
2.在该包中定义一个自定义类型:empcursor 类型为光标
一个存储过程:queryemp
CREATE OR REPLACE
PACKAGE MYPACKAGE AS
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
END MYPACKAGE;
(2)创建包体
--实现包体
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;
我们把上述代码在oracle中编译后，我们在之前的Java工程中测试它：
@Test
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, 30);//查询部门号30的所有员工
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行
call.execute();
//取出集合
rs=((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
String name=rs.getString("ename");
String job=rs.getString("job");
System.out.println(name+"的工作是:"+job);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs);
}
}
测试结果：
