Mysql番外篇_存储过程和存储函数

一，存储函数和存储过程的概念

1，什么事子程序？ 
* 已命名的 PL/SQL 块，编译并存储在数据库中。 
* 子程序的各个部分：

声明部分 
可执行部分 
引用块内容 
异常处理部分(可选)

2，子程序的分类：

过程 － 执行操作 
函数 － 执行操作并返回值

• 存储过程和存储函数 
  指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
• 存储过程和存储函数的区别？ 
  存储函数：可以通过return 语句返回函数值。 
  存储过程：不能 
  除此之外我们可以认为他们是完全一样的。

二，存储过程

1、创建存储过程

用create procedure命令简历存储过程。 
语法：

create [or replace] procedure 过程名（参数列表）as|is    PLSQL子程序体；
1
2
3

2、调用存储过程方法：

1、exec 过程名();2、begin            过程名()；            过程名()；      end;       /
1
2
3
4
5
6

案例1：

–打印Hello World

create or replace procedure sayhelloworldas   --说明部分begin   dbms_output.put_line('Hello World');end;/   
1
2
3
4
5
6
7

调用：

Exec raiseSalary();
1

或者

BeginraiseSalary();raiseSalary();Commit;End;/
1
2
3
4
5
6

补充过程参数模式

• 过程参数的三种模式：
• IN 
  用于接受调用程序的值 
  默认的参数模式
• OUT 
  用于向调用程序返回值
• IN OUT 
  用于接受调用程序的值，并向调用程序返回更新的值

案例2：带参数的存储过程

–给指定的员工涨100，并且打印涨前和涨后的薪水

/*    @kate     1)命令行中输入：desc dbms_output  查看用什么表示输入和输出*/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;  --要不要commit？ 一般不要在存储过程和存储函数中提交  一般在谁调用谁提交回滚 当然可以在这里提交  exception     when no_data_found then         dbms_output.put_line('没有您要找的员工');  dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));end;/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

调用：

BeginraiseSalary(7856);raiseSalary(7999);Commit;End;/
1
2
3
4
5
6

练习题(2个参数)

–给指定员工帐指定比例的工资

create or replace procedure raiseSalary2(eno in number,rate in number)as  --定义变量保存涨前的薪水  psal emp.sal%type;Begin  ---查询该员工的工资  select sal into psal from emp where empno=eno;  ---给该员工张工资  update emp set sal = sal*rate where empno=eno;    dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));end;/
1
2
3
4
5
6
7
8
9
10
11
12
13
14

案例3 （in out参数)

接收用户输入的两个数，实现交换

create or replace procedure  Swap(num1 in out number,num2 in out number)  is  temp number;  begin     temp:=num1;     num1:=num2;     num2:=temp;  end;
1
2
3
4
5
6
7
8
9

调用：

declare     num1 number:=10;     num2 number:=20;  begin     dbms_output.put_line('num1='||num1);     dbms_output.put_line('num2='||num2);     Swap(num1,num2);     dbms_output.put_line('调用过程后');     dbms_output.put_line('num1='||num1);     dbms_output.put_line('num2='||num2);  end;
1
2
3
4
5
6
7
8
9
10
11

3，存储过程调试

1，点击存储过程 
2，设置断点 
3，点击甲壳虫 
4，报错缺少权限，授予权限 
5，右键编译已进行调试存储过程 
6，再次点击甲壳虫 
7，【有参数设置参数】 
8，【有输出日志 打开输出日志】

三，存储函数

函数（function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个return子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

• 存储函数语法：

create[or replace] functiion 函数名（参数列表） return函数值类型as|is    PLSQL子程序体；
1
2
3
4

• 函数是可以返回值的已命名的 PL/SQL 子程序。
• 参数与返回值类型不用指定长度

案例1：查询员工年收入

–查询某个员工的年收入

create or replace function queryempincome(eno in number)return numberas  --月薪和奖金  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;/
1
2
3
4
5
6
7
8
9
10
11

运行：

• 右键存储函数运行 指定参数

• 结合查询或作为Where条件

select FSayLove from dual; 无参数select FSayLove(‘100’) from dual;有参数
1
2

• 返回值给变量

  declare          str varchar2(20);    begin         str:=FSayLove;         dbms_output.put_line(str);    end;    /
1
2
3
4
5
6
7

四，过程和函数中的in 和out

一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。 
但过程和函数都可以通过out指定一个或多个输出参数，我们可以利用out参数，在过程和函数中实现返回多个值。

1.过程参数的三种模式：

IN

用于接受调用程序的值

默认的参数模式

OUT

用于向调用程序返回值

IN OUT

接受调用程序的值，并向调用程序返回更新的值

什么时候用存储过程/存储函数？ 
原则（不是必须的）： 
如果只有一个返回值，用存储函数；否则，就用存储过程。(存储过程+out可以不返回 或者返回多个参数 但是oracle中还是保留了存储函数？历史版本的兼容 新版本不支持 老版本不能兼容) 
案例：查询某个员工的名字，薪水，和职位 
有多个返回值 使用存储过程

create or replace procedure queryEmpInfo(eno in number,                                         pname out varchar2,                                         psal  out number,                                         pjob  out varchar2)as begin  select ename,sal,empjob into pname,psal,pjob from emp where empno=eno;end;
1
2
3
4
5

五，使用java程序调用存储过程

/*     * 存储过程     * create or replace procedure queryEmpInfo(eno in number,     *                                     pename out varchar2,     *                                     psal out number,     *                                     pjob out varchar2)     */    @Test    public void testProcedure() {       // {call <procedure-name>[(<arg1>,<arg2>, ...)]}       String sql = "{call queryEmpInfo(?,?,?,?)}";       CallableStatement call = null;       Connection connection = JDBCUtils.getConnection();       try {           call = connection.prepareCall(sql);           //对于in参数，赋值           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 + "\t" + sal + "\t" + job);       } catch (SQLException e) {           e.printStackTrace();       }finally{           JDBCUtils.release(connection, call, null);       }    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

六，使用java程序调用存储函数

/*     * 存储函数     * create or replace function queryEmpIncome(eno in number)        return number     */    @Test    public void testFunction() {       // {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}       String sql = "{?=call queryEmpIncome(?)}";       Connection conn = null;       CallableStatement call = null;       try {           conn = JDBCUtils.getConnection();           call = conn.prepareCall(sql);           //对于out参数，赋值           call.registerOutParameter(1, OracleTypes.NUMBER);           //对于in参数，赋值           call.setInt(2, 7839);           //执行                      call.execute();           //取出数据           double income = call.getDouble(1);           System.out.println(income);       } catch (Exception e) {           e.printStackTrace();       } finally {           JDBCUtils.release(conn, call, null);       }    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

6.7.在out参数中使用光标

案例：查询某个部门中所有员工的所有信息(有80列就要写80个out??) 
问题： 
1）查询某个部门中所有员工的所有信息—-out参数太多 
2）查询某个部门中所有员工的所有信息—->返回集合

6.7.1.1、申明包结构

CREATE OR REPLACE PACKAGE MYPACKAGE AS   type empcursor is ref cursor;--声明游标类型  --创建存储过程，输出参数为自定义类型  procedure queryEmpList(dno in number,empList out empcursor);END MYPACKAGE;
1
2
3
4
5

6.7.2.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;
1
2
3
4
5
6
7
8

6.7.3.使用java调用带包的存储过程

public void testCursor() {       // {call <procedure-name>[(<arg1>,<arg2>, ...)]}             String sql = "{call MYPACKAGE.queryEmpList(?,?)}";       Connection conn = null;       CallableStatement call = null;       ResultSet rs = null;       try {           conn = JDBCUtils.getConnection();           call = conn.prepareCall(sql);               //对于in参数，赋值ֵ           call.setInt(1, 20);           //对于out参数，赋值           call.registerOutParameter(2, OracleTypes.CURSOR);           //执行                      call.execute();           // 取出结果           rs = ((OracleCallableStatement)call).getCursor(2);           while(rs.next()){              String name = rs.getString("ename");              double sal = rs.getDouble("sal");              System.out.println(name+"\t"+sal);           }       } catch (Exception e) {           e.printStackTrace();       } finally {           JDBCUtils.release(conn, call, rs);       }    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

此案例光标没有关闭，原因：当resultSet关闭的时候 光标就close了