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

前言
学习存储过程的前提是,了解PLSQL的语法和编写方式。
需要了解PLSQL,请查看之前的总结。

我们以下的表操作可能会基于以下两张表:
我们创建一个员工表和部门表:

员工信息表

[sql] view plain copy

  1. create table EMP(
  2.     EMPNO NUMBER,
  3.     ENAME VARCHAR2(10),
  4.     JOB VARCHAR2(9),
  5.     MGR NUMBER,
  6.     HIREDATE DATE,
  7.     SAL BINARY_DOUBLE,
  8.     COMM BINARY_DOUBLE,
  9.     DEPTNO NUMBER
  10. );

其中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

部门表

[sql] view plain copy

  1. create table dept(
  2.     DEPTNO NUMBER,
  3.     DNAME VARCHAR2(50)
  4. );

SQL> select * from dept t;

DEPTNO    DNAME
——–  ——–
20        管理部门
30        销售部门
40        后勤部门
50        金融部门

一、介绍
存储过程和存储函数
指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。

存储过程、存储函数的区别在于,前者没有返回值,后者有返回值。

二、存储过程
(1)创建存储过程
用create procedure命令建立存储过程。
语法:
create [or replace] procedure 过程名(参数列表)
as
PLSQL子程序体;

简单的例子,打印HelloWorld

[sql] view plain copy

  1. create or replace procedure sayHelloWorld
  2. as
  3. begin
  4.   dbms_output.put_line(‘HelloWorld!’);
  5. end;
  6. /

使用PLSQL-developer执行结果:

(2)调用存储过程
1.exec sayHelloWorld();
使用Command控制台使用此语句,结果:

2.在存储过程中调用存储过程

begin
……
sayHelloWorld();
end;
/

(3)例子
1.带参数的存储过程
需求:给指定的员工涨100的工资,并打印涨前和涨后的薪水。

[sql] view plain copy

  1. create or replace procedure raiseSalary(eno in number)
  2. as
  3.   –变量
  4.   psal emp.sal%type
  5. begin
  6.   –得到涨前薪水
  7.   select sal into psal from emp where empno=eno;
  8.   –涨工资
  9.   update emp set sal=sal+100 where empno=eno;
  10.   –打印
  11.   dbms_output.put_line(‘涨前’||psal||‘  涨后:’||(psal+100));
  12. end;
  13. /

执行的时候,可以直接执行exec raiseSalary(1110),给编号为1110的员工涨薪100元。
执行完之后别忘记commit。
也可以在存储过程中直接调用raiseSalary(1110)。

注意:不要在存储函数中进行commit或者rollback。

练习:为指定的员工增加指定额度的工资(传递多个参数)

[sql] view plain copy

  1. create or replace procedure raiseSalaryByCondition(eno in number,rsal in number)
  2. as
  3.   –变量
  4.   psal emp.sal%type;
  5. begin
  6.   –得到涨前薪水
  7.   select sal into psal from emp where empno=eno;
  8.   –涨工资
  9.   update emp set sal=sal+rsal where empno=eno;
  10.   –打印
  11.   dbms_output.put_line(‘涨前’||psal||‘  涨后:’||(psal+rsal));
  12. end;
  13. /

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

(2)语法
简历存储函数的语法:
create [or replace] function 函数名(参数列表)
return 函数值类型
as
PLSQL子程序体

(3)例子
查询某个员工的年收入(一年的月薪+奖金和)

[sql] view plain copy

  1. create or replace function queryEmpIncome(eno in number)
  2. return number
  3. as
  4.   –变量
  5.   psal emp.sal%type;
  6.   pcomm emp.comm%type;
  7. begin
  8.   –得到员工的月薪和奖金
  9.   select sal,comm into psal,pcomm from emp where empno=eno;
  10.   return psal*12+nvl(pcomm,0);
  11. end;
  12. /

在控制台编译运行,查询员工号为1110的年收入:

我们查出编号1110的员工的月薪为6820,奖金为0

6820*12+0=81840,说明我们的程序运行的结果是正确的。

注意,调用存储函数的方式只能在plsql语句中调用,而且必须要有变量去接受返回的参数。
(如果只做打印可以忽略)

(4)过程和函数中的in和out
一般来讲,过程和函数的区别在于函数可以有一个返回值,而过程没有返回值。

但过程和函数都可以通过out指定一个或多个输出函数。我们可以利用out参数,在
过程和函数中实现返回多个值。

例子:查询某个员工的姓名、月薪、和职位。
这里我们使用的是存储过程:

[sql] view plain copy

  1. create or replace procedure queryEmpInfo(eno in number,
  2.                                          pename out varchar2,
  3.                                          psal out number,
  4.                                          pjob out varchar2)
  5. as
  6. begin
  7.   select ename,sal,job into pename,psal,pjob from emp where empno=eno;
  8. end;
  9. /

我们来测试一下:
测试函数:

[sql] view plain copy

  1. set serveroutput on
  2. declare
  3.   eno number;
  4.   pename varchar2(200);
  5.   psal number;
  6.   pjob varchar2(200);
  7. begin
  8.   eno:=1110;
  9.   queryEmpInfo(
  10.       eno => eno,
  11.       pename => pename,
  12.       psal => psal,
  13.       pjob => pjob);
  14.    dbms_output.put_line(‘编号1110员工的姓名为:’||pename||‘薪水为:’||psal||‘职位为:’||pjob);
  15. end;
  16. /

测试结果:

与直接查询的结果一样:

证明我们的函数是正确的。

既然存储过程和存储函数可以实现一样的功能,为什么还需要两者共同存在呢?
因为老版本中既有存储过程和存储函数,但是新版本中可能只有两者之一,所以
要保证程序的兼容性,要保留两者。

什么时候使用存储过程/存储函数?
原则:如果只有一个返回值,用存储函数;否则,就用存储过程。

三、使用Java来调用存储过程
打开我们的Eclipse,创建一个JavaProject工程,名为“TestOracle”

我们在工程下创建一个lib文件夹,将oracle的数据库驱动jar包放进去,
并将其Add to BuildPath:

然后我们创建一个JDBCUtils类,用于获取数据库的连接:

内容:

[java] view plain copy

  1. package demo.util;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. public class JDBCUtils {
  8.     private static String driver=“oracle.jdbc.OracleDriver”;
  9.     private static String url=“jdbc:oracle:thin:@localhost:1521:XE”;
  10.     private static String user=“jack”;
  11.     private static String password=“1234”;
  12.     static{
  13.         try {
  14.             Class.forName(driver); //加载驱动
  15.         } catch (ClassNotFoundException e) {
  16.             e.printStackTrace();
  17.         }
  18.     }
  19.     public static Connection getConnection(){
  20.         try {
  21.             return DriverManager.getConnection(url,user,password);
  22.         } catch (SQLException e) {
  23.             e.printStackTrace();
  24.         }
  25.         return null;
  26.     }
  27.     public static void release(Connection conn,Statement st,ResultSet rs){
  28.         if(conn!=null){
  29.             try {
  30.                 conn.close();
  31.             } catch (SQLException e) {
  32.                 e.printStackTrace();
  33.             }finally{
  34.                 conn=null;//垃圾回收
  35.             }
  36.         }
  37.         if(st!=null){
  38.             try {
  39.                 st.close();
  40.             } catch (SQLException e) {
  41.                 e.printStackTrace();
  42.             }finally{
  43.                 st=null;//垃圾回收
  44.             }
  45.         }
  46.         if(rs!=null){
  47.             try {
  48.                 rs.close();
  49.             } catch (SQLException e) {
  50.                 e.printStackTrace();
  51.             }finally{
  52.                 rs=null;//垃圾回收
  53.             }
  54.         }
  55.     }
  56. }

创建并编写测试类:

内容

[java] view plain copy

  1. package demo.test;
  2. import java.sql.CallableStatement;
  3. import java.sql.Connection;
  4. import java.sql.SQLException;
  5. import oracle.jdbc.driver.OracleTypes;
  6. import org.junit.Test;
  7. import demo.util.JDBCUtils;
  8. public class OracleTest {
  9.    /*create or replace procedure queryEmpInfo(eno in number,
  10.         pename out varchar2,
  11.         psal out number,
  12.         pjob out varchar2)
  13.     as
  14.     begin
  15.     select ename,sal,job into pename,psal,pjob from emp where empno=eno;
  16.     end;
  17.     /*/
  18.     @Test
  19.     public void testProcedure(){
  20.         //测试存储过程
  21.         //格式{call <procedure-name>[{<arg1>,<arg2>,…}]}
  22.         String sql=“{call queryEmpInfo(?,?,?,?)}”;
  23.         Connection conn=null;
  24.         //CallableStatement是用于执行SQL存储过程的接口
  25.         CallableStatement call=null;
  26.         try {
  27.             conn=JDBCUtils.getConnection();
  28.             call=conn.prepareCall(sql);
  29.             //赋值
  30.             call.setInt(11110);
  31.             //对于out参数,申明
  32.             call.registerOutParameter(2, OracleTypes.VARCHAR);
  33.             call.registerOutParameter(3, OracleTypes.NUMBER);
  34.             call.registerOutParameter(4, OracleTypes.VARCHAR);
  35.             //调用
  36.             call.execute();
  37.             //取出结果
  38.             String name = call.getString(2);
  39.             double sal = call.getDouble(3);
  40.             String job = call.getString(4);
  41.             System.out.println(“工号为1110的员工信息:”);
  42.             System.out.println(“姓名:”+name);
  43.             System.out.println(“薪水:”+sal);
  44.             System.out.println(“职位:”+job);
  45.         } catch (SQLException e) {
  46.             e.printStackTrace();
  47.         }finally{
  48.             JDBCUtils.release(conn, call, null);
  49.         }
  50.     }
  51.     /*create or replace function queryEmpIncome(eno in number)
  52.     return number
  53.     as
  54.       –变量
  55.       psal emp.sal%type;
  56.       pcomm emp.comm%type;
  57.     begin
  58.       –得到员工的月薪和奖金
  59.       select sal,comm into psal,pcomm from emp where empno=eno;
  60.       return psal*12+nvl(pcomm,0);
  61.     end;
  62.     /*/
  63.     @Test
  64.     public void testFunction(){
  65.         //测试存储函数
  66.         //格式{?= call <procedure-name>[{<arg1>,<arg2>,…}]}
  67.         String sql=“{?=call queryEmpIncome(?)}”;
  68.         Connection conn=null;
  69.         //CallableStatement是用于执行SQL存储过程的接口
  70.         CallableStatement call=null;
  71.         try {
  72.             conn=JDBCUtils.getConnection();
  73.             call=conn.prepareCall(sql);
  74.             //第一个问号是返回值,要申明一下
  75.             call.registerOutParameter(1, OracleTypes.NUMBER);
  76.             //赋值
  77.             call.setInt(21110);
  78.             //调用
  79.             call.execute();
  80.             //取出结果
  81.             double s_sal = call.getDouble(1);
  82.             System.out.println(“工号为1110的员工年薪:”);
  83.             System.out.println(s_sal);
  84.         } catch (SQLException e) {
  85.             e.printStackTrace();
  86.         }finally{
  87.             JDBCUtils.release(conn, call, null);
  88.         }
  89.     }
  90. }

注意这里要加入Junit4的环境。

首先测试testProcedure()方法,测试结果:

然后测试testFunction()方法,测试结果:

四、问题
我们的存储过程虽然有out,但是如果我们一条数据返回的字段特别多,就不能写那么多out参数了。
这就要求我们使用之前PLSQL中的光标来解决这个问题了。
即是:在out参数中使用光标。

(1)申明包结构
根据员工的部门号查询员工信息,要求返回该部门所有员工的所有信息

使用光标作为out参数
1.创建一个包:mypackage
2.在该包中定义一个自定义类型:empcursor 类型为光标
一个存储过程:queryemp

[sql] view plain copy

  1. CREATE OR REPLACE
  2. PACKAGE MYPACKAGE AS
  3.   type empcursor is ref cursor;
  4.   procedure queryEmpList(dno in number,empList out empcursor);
  5. END MYPACKAGE;

(2)创建包体
–实现包体

[sql] view plain copy

  1. CREATE OR REPLACE
  2. PACKAGE BODY MYPACKAGE AS
  3.   procedure queryEmpList(dno in number,empList out empcursor) AS
  4.   BEGIN
  5.       open empList for select * from emp where deptno=dno;
  6.   END queryEmpList;
  7. END MYPACKAGE;

我们把上述代码在oracle中编译后,我们在之前的Java工程中测试它:

[java] view plain copy

  1. @Test
  2. public void testCursor(){
  3.     String sql=“{call mypackage.queryEmpList(?,?)}”;
  4.     Connection conn=null;
  5.     CallableStatement call=null;
  6.     ResultSet rs=null;
  7.     try {
  8.         conn=JDBCUtils.getConnection();
  9.         call=conn.prepareCall(sql);
  10.         call.setInt(130);//查询部门号30的所有员工
  11.         call.registerOutParameter(2, OracleTypes.CURSOR);
  12.         //执行
  13.         call.execute();
  14.         //取出集合
  15.         rs=((OracleCallableStatement)call).getCursor(2);
  16.         while(rs.next()){
  17.             String name=rs.getString(“ename”);
  18.             String job=rs.getString(“job”);
  19.             System.out.println(name+“的工作是:”+job);
  20.         }
  21.     } catch (SQLException e) {
  22.         e.printStackTrace();
  23.     }finally{
  24.         JDBCUtils.release(conn, call, rs);
  25.     }
  26. }

测试结果:

未经允许不得转载:博客 » 【oracle学习】10.存储过程和存储函数

赞 (0)

评论 0

评论前必须登录!

登陆 注册