存储过程与存储函数

一、序
1、基础
储过程、存储函数均是oracle数据库对象（表、视图、索引、序列、同义词均是）

2、概念
存储过程、存储函数指的是：存储在数据库中供所有用户程序调用的子程序（PL/SQL编写）

3、区别与共同
共同点：完成特定功能的程序

区别：存储函数可以用return语句返回语句值；而存储过程不能用return语句返回语句值。

注：除此之外，两者基本一样

二、存储过程
1、创建存储过程（语法）

创建：使用CREATE OR REPLACE PROCEDURE 命令
语法：
create [or replace] PROCEDURE 过程名（参数列表）
AS（后面跟说明部分）
PLSQL子程序体;

–注：只能创建或替换;AS相当于是declear，在创建存储过程或存储函数时，没有后说明部分，也不能省略。

2、分类及使用

不带参：
1.示例 – 打印Helloworld
create or replace procedure sayhelloworld
AS
– 说明部分
begin
dbms_output.put_line(“hello,world”);
end;
/

2.调用方式
1、execute sayhelloworld();/exec sayhelloworld();
2、在另外一个PLSQL程序中调用：
begin
sayhelloworld(); – 可以调用多次
end;
/

带参：
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;
–给该员工涨100
update emo set sal = sal + 100 where empno=eno;

–update后，存储过程/存储函数需要commit/rollback?
–注：一般不在存储过程或存储函数中commit/rollback，因为不能保证是否在同一事务中

–打印涨前和涨后
dbms_output.put_line(‘涨前：’||psal||’涨后：’||(psal+100));
end;
/

–注：IN/OUT：带参需要表明参数输入还是输出参数

2.调用
1、begin
raisesalary(1234);
raisesalary(5678)；
end;

3、调试存储过程

1.调试存储过程最好放到Oracle数据库所在的系统或虚拟机上，解压SQL developer ，双击运行。
2.为了确保存储过程或函数是可调试的，右键“以编译并进行调试”，点击红色按钮“调试”
3.利用已写好的调用函数进行调试

–注：调试的用户需要具备以下两者权限debug connect session , debug any procedure

4、在应用中访问存储过程（具体使用/引用）
public class TestProcedure{
public void testProcedure(){
String sql = “{call queryempinform(?,?,?,?)}”;
Connection conn=null;
CallableStatement call = null; //调用过程需要这个类
try{
//得到连接，JDBCUtil是一个已经写好的工具类
conn = JDBCUtil.getConnection(sql);
call = conn.prepareCall(sql); //创建statement
//对于in参数，赋值
call.setTnt(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{
        JDBCUtil.release(conn,call,null);
    }
}

}

三、存储函数
1、概念
函数为一命名的存储程序，可带参数，并返回一计算值；
函数和过程的结构相似，但必须有一个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;

–注：如果有一个参数为空，则返回的值为空;为有可能为空的参数加入nvl()方法中，如：nvl(pcomm,0)，如果为空，将其置为0。

4、在应用中访问存储函数
public class TestFunction{
public void testProcedure(){
String sql = “{?=call queryempincome(?)}”; //第一个?为返回值，第二个?为输入参数
Connection conn=null;
CallableStatement call = null; //调用过程需要这个类
try{
//得到连接，JDBCUtil是一个已经写好的工具类
conn = JDBCUtil.getConnection(sql);
call = conn.prepareCall(sql); //创建statement
//对于输出参数，申明
call.registerOutParameter(1,OracleTypes.NUMBER);

        //对于输入参数，赋值
        call.setInt(2,7839);

        //执行调用
        call.execute();
        //得到返回结果
        double income = call.getDouble(2);

        System.out.println("收入为：" + income);

    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtil.release(conn,call,null);
    }
}

}

四、IN和OUT参数
1、概念

一般，存储过程和存储函数的区别在于存储函数可以有一个返回值；而存储过程没有返回值。
存储过程和存储函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数实现返回多个值。
==》
1、存储过程和存储函数都可以有out参数；
2、存储过程和存储函数都可以有多个out参数；
3、存储过程可以通过out参数来实现返回值；

注：一般原则，如果只有一个返回值，用存储函数；否则，用存储过程；

2、示例
–out参数：查询某个员工姓名，月薪和职位
create or replace procedure queryempinform(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;
/

–延伸：1、如果返回员工的所有信息？ –> out参数太多？
2、查询某个部门中所有员工的所有信息 –> Out中返回集合？

3、在out参数中使用光标（则可以输出集合）
====可以解决：查询员工的所有信息
//创建包头(申明)：
create or replace package mypackage as
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
END mypackage;

//包体：需要实现包头中声明的所有方法(包括存储过程/函数)

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;

–注：可以在开发工具，如sql developer中的程序包下新建包体和包头

4、在应用中访问包中的存储过程
==注：需要带上包名

–需要返回的是多个值，一般以集合的方式：

public class TestCursor{
public void testCursor(){
String sql = “{call mypackage.queryEmpList(?,?)}”;
Connection conn=null;
CallableStatement call = null; //调用过程需要这个类
ResultSet rs = null; //得到返回的结果集
try{
//得到连接，JDBCUtil是一个已经写好的工具类
conn = JDBCUtil.getConnection(sql);
call = conn.prepareCall(sql); //创建statement

        //对于输入参数，赋值
        call.setInt(1,11);

        //对于输出参数，申明
        call.registerOutParameter(2,OracleTypes.CURSOR);



        //执行调用
        call.execute();
        //得到该部门中的所有员工信息
        rs = ((OracleCallableStatement)call).getCursor(2);
        while(rs.next()){
            //取出需要的员工信息
            //员工号、姓名、薪水、职位
            int empno = rs.getInt("empno");
            String name = rs.getString("ename");
            double sal = rs.getDouble("sal");
            String job = rs.getString("empjob");
            System.out.println("工号："+empno+"==="+"姓名："+name + "=="+"薪水："+sal+"=="+"职位："+job);
        }

    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtil.release(conn,call,rs);
    }
}

}