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 sayhelloworldas  --说明部分begin  dbms_output.put_line('Hello World');end;SQL> set serveroutput on;SQL> exec sayhelloworld();Hello WorldSQL>  begin  2      sayhelloworld();  3      sayhelloworld();  4   end;  5  /Hello WorldHello 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 numberas  --定义变量接收薪水和奖金  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)asbegin  --得到该员工的姓名，月薪和职位  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) */@Testpublic void testProcedure() {String sql = "{call queryempinform(?,?,?,?)}";Connection conn = null;CallableStatement call = null;try {//得到一个链接conn = JDBCUtils.getConnection();//通过链接创建出statmentcall = 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 */@Testpublic void testFunction() throws Exception {String sql = "{?=call queryemp_income(?)}";Connection conn = null;CallableStatement call=null;try{//得到数据库连接conn = JDBCUtils.getConnection();// 基于连接创建statementcall = 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; */@Testpublic 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