Oracle基础知识3----存储过程、存储函数、out参数、包

测试数据来源：http://blog.csdn.net/ochangwen/article/details/51297893

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

二者 相同点：完成特定功能的程序 二者 区别：存储函数可以用return语句返回值，而存储过程不能。

一、存储过程

1-1.创建和使用存储过程

  用create procedure命令建立存储过程和存储函数   语法：create [or replace] procedure 过程名(参数列表)             as     plsql子程序体;

/*第一个存储过程：打印Hello World 调用存储过程： 1.exec sayhelloworld(); 2.再写一个存储过程，调用过程 begin sayhelloworld(); sayhelloworld(); end; */ create or replace procedure sayhelloworld as --说明部分 begin dbms_output.put_line('Hello World'); end; SQL> set serveroutput on; SQL> exec sayhelloworld(); Hello World SQL> begin 2 sayhelloworld(); 3 sayhelloworld(); 4 end; 5 / Hello World Hello World

1-2.带参数的存储过程

举例：为指定的员工，涨100块钱的工资；并且打印涨前和涨后的薪水。

drop table emp; create table emp(empno number(5),ename varchar2(10), sal number(10,2), deptno number(4) ); insert into emp(empno,ename,sal,deptno)values(1001,'Tom1',1000,10); insert into emp(empno,ename,sal,deptno)values(1002,'Tom2',2000,20); /* 如何调用： begin raiseSalry(1001); raiseSalry(1002); commit; //这时提交可以保证上面的操作是在同一个事务里。 end; */ --参数需要指定是输入(in)还是输出(out) create or replace procedure raiseSalry(eno in number) as --定义一个变量保存涨前的薪水 psal emp.sal%type; begin --得到员工涨前的薪水 select sal into psal from emp where empno=eno; --给该员工涨100 update emp set sal = sal +100 where empno=eno; --需不需要commit? 一般不在存储过程/函数中提交或回滚 DBMS_OUTPUT.PUT_LINE('涨工资前的薪水'||psal||'涨工资后的薪水'||(psal+100)); end; SQL> begin 2 raiseSalry(1001); 3 raiseSalry(1002); 4 commit; 5 end; 6 / 涨工资前的薪水1000涨工资后的薪水1100 涨工资前的薪水2000涨工资后的薪水2100至于如何调试，想看百度。

二、存储函数

  函数(Function)为一命名的存储程序，可带参数，并返回一计算值   函数和过程的结构类似，但必须有一个return子句，用于返回函数值 创建存储函数的语法： create [or replace] function 函数名(参数列表) return 函数值类型 as  plsql子程序体；

--存储函数：查询某个员工的年收入 create or replace function queryemp_income(eno in number) return number as --定义变量接收薪水和奖金 p_sal emp.sal%type; p_comm emp.comm%type; begin select sal,comm into p_sal,p_comm from emp where empno=eno; --nvl为遇空函数，如果p_comm为空则返回0 return nvl(p_comm,0)+p_sal*12; end;

三、其它知识点

3-1.in和out参数

  一般来讲，存储过程和存储函数的区别在于存储函数可以有一个返回值；而存储过程没有返回值。   存储过程和存储函数都可以有out参数和多个out参数   存储过程可以通过out参数来实现返回值 什么时候用存储过程/函数？ 原则：如果只有一个返回值，用存储函数；否则用存储过程。

--out参数：查询某个员工姓名，月薪和职位 create or replace procedure queryEmpInform (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; 思考：   1.查询某个员工的所有信息 --> out参数太多？   2.查询某个部门所有员工的所有信息 --> out中返回集合？

3-2.在应用程序中访问存储过程

下面一个实例：在java应用程序中访问存储过程/函数 1).首先先建一个java工程，将D:\oracle\product\10.2.0\db_3\jdbc\lib\ojdbc14.jar包build pat

工具类：

package demo.utils; 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:orcl"; private static String user = "scott"; private static String password = "tiger"; // 注册数据库的驱动 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(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { //回收 rs = null; } } if(st !=null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); }finally { st = null; } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }finally { conn = null; } } } } 在应用程序 中访问存储过程 package demo.oracle; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import org.junit.Test; import demo.utils.JDBCUtils; import oracle.jdbc.OracleTypes; public class TestProcedure { /** * --out参数：查询某个员工姓名，月薪和职位 * create or replace procedure queryEmpInform * (eno in number,pename out varchar2, psal out number, pjob out varchar2) */ @Test public void testProcedure() { String sql = "{call queryempinform(?,?,?,?)}"; Connection conn = null; CallableStatement call = null; try { //得到一个链接 conn = JDBCUtils.getConnection(); //通过链接创建出statment call = conn.prepareCall(sql); //对于in参数，赋值 call.setInt(1, 1002); //对于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(conn, call, null); } } }在应用程序 中访问存储函数 package demo.oracle; import java.sql.CallableStatement; import java.sql.Connection; import org.junit.Test; import demo.utils.JDBCUtils; import oracle.jdbc.OracleTypes; public class TestFunction { /** * --存储函数：查询某个员工的年收入 create or replace function queryemp_income(eno in number) return number */ @Test public void testFunction() throws Exception { String sql = "{?=call queryemp_income(?)}"; Connection conn = null; CallableStatement call=null; try{ //得到数据库连接 conn = JDBCUtils.getConnection(); // 基于连接创建statement call = conn.prepareCall(sql); //输出参数，申明 call.registerOutParameter(1, OracleTypes.NUMBER); //输入参数，赋值 call.setInt(2, 1001); call.execute(); double income = call.getDouble(1); System.out.println("编号为1001的员工的年收入是" + income); }catch(Exception e){ e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, null); } } }

3-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;在应用中访问包中的存储过程   注意，需要带上包名

package demo.oracle; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import org.junit.Test; import demo.utils.JDBCUtils; import oracle.jdbc.OracleTypes; import oracle.jdbc.driver.OracleCallableStatement; public class TestCursor { /** * --包头: * create or replace package mypackage as * type empcursor is ref cursor; * procedure queryEmpList(dno in number, emplist out empcursor); * end mypackage; */ @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); //对于in参数，赋值 call.setInt(1, 3); //对于out参数，申明 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"); String job = rs.getString("job"); System.out.println(empno+":" + name+":"+job); } }catch(Exception e){ e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, null); } } } 3:刘备备:MANAGER 6:张飞飞:CLERK 7:SCOTT:CLERK