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

测试数据来源：http://blog.youkuaiyun.com/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