oracle的存储过程学习

---

存储过程和存储函数是指存储在数据库中供所有用户程序调用的子程序。
区别：存储函数有return返回值

一、存储过程

1.创建存储过程

语法：
create [or replace] PROCEDURE 过程名(参数)
AS PLSQL子程序体；

演示：

--as is 在 存储过程 （ PROCEDURE ）和 函数 ( FUNCTION )中没有区别，在视图（VIEW）中只能用 AS 不能用IS，在 游标 ( CURSOR )中只能用IS不能用 AS 
create or replace procedure raisesalary(eno in number) is
 --定义一个变量保存涨工资前的薪水
 psal emp_01.sal%type;
begin
  --得到员工涨前的薪水
  select sal into psal from emp_01 where empno = eno;
  
  --涨薪
  update emp_01 set sal = sal + 100 where empno = eno;
  
  --存储过程一般让别人调用，正常情况下，为了不改变调用者的事务，
  --一般不在存储过程或者存储函数中进行commit和rollback操作。
  --commit;
  
  --打印前后薪水
  dbms_output.put_line('员工涨前：'|| psal || '  涨后：'||(psal+100));
  
end raisesalary;

调用：1、exec raisesalary(7869);
           2、存储过程中调用 beign raisesalary(7869);

2.调试存储过程

添加调试权限：

grant debug connect session to 用户;

二、存储函数

1.创建函数

区别于存储过程的是存储函数有返回值return。
示例：创建一个函数，查询某个员工的年收入总额：

create or replace function queryempincome(eno in number) 
return number 
is
  --定义变量保存员工的月薪和奖金
  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 queryempincome;

三、输入参数和输出参数 (in和out)

• 存储过程和存储函数都可以有多个out参数；
• 存储过程可以通过out参数来实现返回值；
• 可以利用out参数，在存储过程和函数中实现多个返回值。

原则：一般来说，如果只有一个返回参数，使用函数；否则，就用存储过程。

示例：c查询某个员工的姓名，月薪和职位

create or replace procedure queryempinfo(eno in number, 
                                         pename out varchar2,
                                         psal out number,
                                         pjob out varchar2) is
begin
  select ename,sal,job  into pename,psal,pjob from emp where empno = eno;
end queryempinfo;

调用：

declare
  pename varchar2(20);
  psal number;
  pjob varchar2(50);
begin
  -- Call the procedure
  queryempinfo(eno => 7839,
               pename => pename,
               psal => psal,
               pjob => pjob);
   dbms_output.put_line(pename || ' ' || psal || ' ' || pjob );
end;

--结果输出：KING 5000 PRESIDENT

四、JAVA程序调用

1.程序调用存储过程：

//连接数据库工具类
public class JDBCUtils {

    private static String driver = "oracle.jdbc.OracleDriver";
    private static String url = "jdbc:oracle:thin:@192.168.45.130:1521:orcl";
    private static String user = "scott";
    private static String password = "12345";

    //注册数据库驱动
    static{
        try {
            //注册驱动   或：DriverManager.registerDriver(driver);
            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 statement, ResultSet resultSet){
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                resultSet = null;
            }
        }

        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                statement = null;
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                conn = null;
            }
        }
    }
}

    public void testProcedure(){
        //调用存储过程queryempinfo
        String sql = "{call queryempinfo(?,?,?,?)}";
        Connection connection = null;
        CallableStatement call = null;


        try {
            connection = JDBCUtils.getConnection();
            call = connection.prepareCall(sql);

            //对于 in 参数
            call.setInt(1,7839);
            //对于 out 参数,先申明
            call.registerOutParameter(2, Types.VARCHAR);
            call.registerOutParameter(3, Types.NUMERIC);
            call.registerOutParameter(4, Types.VARCHAR);
            //执行调用
            call.execute();

            //取出结果
            String pename = call.getString(2);
            double psal = call.getDouble(3);
            String pjob = call.getString(4);

            System.out.println(pename + " " + psal + " " + pjob);

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection,call,null);
        }
    }
    
----结果：KING 5000.0 PRESIDENT

2.调用存储函数：

 public void testFunction(){
        //调用存储过程queryempinfo
        String sql = "{?=call queryempincome(?)}";
        Connection connection = null;
        CallableStatement call = null;

        try {
            connection = JDBCUtils.getConnection();
            call = connection.prepareCall(sql);

            //out 出参申明 return number
            call.registerOutParameter(1,Types.NUMERIC);
            //输入参数 in    （eno in number）
            call.setInt(2, 7839);
            //执行调用
            call.execute();

            double salByYear = call.getDouble(1);

            System.out.println("员工一年工资为："  + salByYear);

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection,call,null);
        }
    }
--输出结果： 员工一年工资为：60000.0

mybatis写法：

   <select id="callQueryempinfo" parameterType="java.util.Map" statementType="CALLABLE">
        CALL
            queryempinfo(
                #{eno, mode=IN, jdbcType=INTEGER},
                #{pename, mode=OUT, jdbcType=VARCHAR},
                #{psal, mode=OUT, jdbcType=INTEGER},
                #{pjob, mode=OUT, jdbcType=VARCHAR}
            )
   </select>
   
   <!--这里有个奇怪的现象，call写大写执行报错？？？不知道为啥--> 
   <select id="callQueryempincome" parameterType="java.util.Map" statementType="CALLABLE">
        {
         #{yearAllsal,mode=OUT,jdbcType=INTEGER} = call queryempincome(
                #{eno,mode=IN,jdbcType=INTEGER} )
        }
    </select>

    public void getInfoByEno(HashMap<String, Object> params) {

        //存储过程
        empInfoDao.callQueryempinfo(params);
        System.out.println(params.get("pename") + " " + params.get("psal") + " " + params.get("pjob"));


        //调用存储函数
        empInfoDao.callQueryempincome(params);
        Integer pasl = (Integer) params.get("yearAllsal");
        System.out.println(pasl);

    }

3.out参数中光标的使用

回看上面的例子，假如现在需要查询的是某个部门的所有员工的信息，该怎么实现?
申明包结构（包头 包体）

申明包头 （类比于java中的接口）：

create or replace package myPackage is
  
 --自定义类型empcursor为光标类型  应用输入参数empList为该类型
 type empcursor is ref cursor;
 procedure queryEmpList(dno in number, empList out empcursor);

end myPackage;

申明包体（类比于java中的接口的实现，需要实现包头中申明的存储过程或函数）：

create or replace package body myPackage is

 procedure queryEmpList(dno in number, empList out empcursor) is
   begin
     
    --打开光标
    open empList for select * from emp where deptno = dno;  
   
   end queryEmpList;

end myPackage;

调用：

 public void testCursor(){
        String sql = "{call myPackage.queryEmpList(?,?)}";

        Connection connection = null;
        CallableStatement call = null;

        try {
            connection = JDBCUtils.getConnection();
            call = connection.prepareCall(sql);

            call.setInt(1, 20);
            //cursor类型   Types.REF_CURSOR
            //这种报错？ call.registerOutParameter(2, Types.REF_CURSOR);
            call.registerOutParameter(2, OracleTypes.CURSOR);
            call.execute();
            //取出所有的员工信息 返回的是结果集
            ResultSet rs = ((OracleCallableStatement) call).getCursor(2);


            while(rs.next()){
                //员工号  姓名 薪水
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                System.out.println(empno + " " + ename + " " + sal);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection,call,null);
        }
    }

mybatis写法：将调用存储过程参数放入map中,由于返回的游标中包含很多参数，所以再写一个resultmap与之对应,类型为hashmap。设置返回的jdbcType=CURSOR,resultMap设置为id对应的值。最后游标中的值就输出到list中。

 	<resultMap type="java.util.HashMap" id="cursorMap"></resultMap>
    <select id="callMyPackage" parameterType="java.util.Map" statementType="CALLABLE">
         call myPackage.queryEmpList(
                #{dno ,mode=IN,jdbcType=INTEGER},
                #{empList ,mode=OUT,jdbcType=CURSOR,resultMap=cursorMap}
                )
    </select>

demo码云地址：git@gitee.com:xurun/demo.git