Oracle存储过程，存储函数，包头，包体。

存储过程和存储函数最大的区别就是函数可以通过return来结束，过程不可以。

--------------------------存储过程-----------------------
----------in模式下的参数不用赋值，out方式下的参数必须赋值-------
------------无参的存储过程-------------
create or replace procedure pro
is
begin
  dbms_output.put_line(123456);
end;

begin
  pro();
end;
-----------------in模式下的存储过程---------------
create or replace procedure pro1(p1 in number,p2 in number)
as
begin
  dbms_output.put_line(p1+p2);
  dbms_output.put_line(p1*p2);
end;

declare 
 a number:=10;
 b number:=11;
begin
  pro1(a,b);
end;

------------------out模式下的存储过程-----------------------
create or replace procedure pro2(p1 out number)
as
begin
  p1:=33;
end;
declare
  c number:=30;
begin
  dbms_output.put_line('调用前的值：'|| c);
  pro2(c);
  dbms_output.put_line('调用后的值：'|| c);
end;

-----------------in out 存储过程------------------
create or replace procedure pro3(p1 in out number)
as
begin
  p1:=444;
end;
declare
 c number:=400;
begin
  dbms_output.put_line('存储过程调用前的值'||c);
  pro3(c);
  dbms_output.put_line('存储过程调用后的值'||c);
end;

案例1.创建带参数的存储过程，给指定的员工涨工资

--创建带参数的存储过程，给指定的员工涨工资

create or replace procedure saises(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;
  
  --打印涨前的工资和涨后的工资
  dbms_output.put_line('涨前的工资为：'||psal||' 涨后的工资：'||(psal+100));
end;
--调用存储过程--
begin
  saises(7369);
  saises(7499);commit;
end;

案例2.存储函数,查询某个员工的年薪

--存储函数,查询某个员工的年薪--
create or replace function queryenpincome(eno in number)
return number
as
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;

案例3.存储过程查询某个员工的姓名，职位薪水 in out 

create or replace procedure psal(eno in number,
                                pename out varchar2,
                                pjob out varchar2,
                                psal out number)
as
begin
  select ename,job,sal into pename,pjob,psal from emp where empno=eno; 
  dbms_output.put_line(eno||pename||pjob||psal);
end;

案例4.包头，包体

--包头相当于接口--包体实现
--在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;

案例5.用Java jdbc调用案例4包头，案例3存储过程，案例2函数

（1）写一个jdbc连接数据库的工具类，需要导入的包ojdbc14.jar

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class OracleUtil {
	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) {
			System.out.println("加载数据库驱动失败");
			e.printStackTrace();
		}
	}
	//获取数据库连接
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//表示没有获取到数据库连接
		return null;
	}
	//需要清空的对象
	public static void release(Connection conn,Statement st,ResultSet rs) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				rs=null;
			}
		}
		if(st!=null) {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				st=null;
			}
		}
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				conn=null;
			}
		}
	}
	
}

（2）测试存储过程

package test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleTypes;
import util.OracleUtil;

public class ProcedureTest {
	public static void main(String[] args) {
		String sql= "{call psal(?,?,?,?)}";
		Connection conn=null;
		CallableStatement st=null;
		
		
		try {
			//获取到数据库连接
			conn=OracleUtil.getConnection();
			//创建 statement对象
			st=conn.prepareCall(sql);
			//对于in参数 赋值
			st.setInt(1, 7369);
			
			//对于out参数 声明
			st.registerOutParameter(2, OracleTypes.VARCHAR);
			st.registerOutParameter(3, OracleTypes.VARCHAR);
			st.registerOutParameter(4, OracleTypes.NUMBER);
			//操作数据库
			st.execute();
			//获取name，job，sal
			String name=st.getString(2);
			String job=st.getString(3);
			int sal=st.getInt(4);
			System.out.println(name+"	"+job+"	"+sal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			OracleUtil.release(conn, st, null);
		}
		
	}
}

（3）测试存储函数

package test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;
import util.OracleUtil;

public class FunctionTest {
	public static void main(String[] args) {
		String sql="{?=call queryenpincome(?)}";
		Connection conn=null;
		CallableStatement call=null;
		//得到数据库连接
		conn=OracleUtil.getConnection();
		try {
			//创建statement
			call=conn.prepareCall(sql);
			
			//对输出参数进行声明
			call.registerOutParameter(1, OracleTypes.NUMBER);
			
			//对输入参数赋值
			call.setInt(2, 7369);
			//执行操作
			call.execute();
			
			double income=call.getDouble(1);
			System.out.println(income);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			OracleUtil.release(conn, call, null);;
		}
	}
}

（4）测试包头，包体

package test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import util.OracleUtil;

public class PackageCursor {
	public static void main(String[] args) {
		String sql="{call mypackage.queryEmpList(?,?)}";
		Connection conn=null;
		CallableStatement call=null;
		ResultSet re=null;
		try {
			//获取连接
			conn=OracleUtil.getConnection();
			//创建statement
			call=conn.prepareCall(sql);
			//对于in参数赋值
			call.setInt(1, 10);
			//对于out参数声明
			call.registerOutParameter(2, OracleTypes.CURSOR);
			//执行
			call.execute();
			re=((OracleCallableStatement) call).getCursor(2);
			while(re.next()) {
				int empno=re.getInt("empno");
				String ename=re.getString("ename");
				double sal=re.getDouble("sal");
				String job=re.getString("job");
				System.out.println(empno+"\t"+ename+"\t"+sal+"\t"+job);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			OracleUtil.release(conn, call, re);
		}
	}
}