【oracle学习】10.存储过程和存储函数

前言
学习存储过程的前提是，了解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 [{,,...}]}
        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 [{,,...}]}
        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);
    }

}

测试结果：

转载请注明出处：http://blog.csdn.net/acmman/article/details/52512884