oracle 存储过程、存储函数、程序包

oracle数据库对象：表、视图、索引、序列、同义词、存储过程、存储函数等都是数据库对象

存储在数据库中供用户程序 调用的子程序叫：存储过程或者存储函数

  相同点：完成特定功能的程序

  区别：存储函数用return语句返回值

一般来讲：如果只有一个返回值，则用存储函数，否则（有多个或者没有返回值），使用存储过程

语法：

create or replace procedure 过程名(参数)

as

plsql子程序体;

*********************************************************************************************************************************

eg：
create or replace procedure sayhelle
as
  --说明部分
begin
  dbms_output.put_line('hello world');
end;
=============================================
/*
 调用存储过程：(两种方法)
1、exec sayhello();
2、begin
     sayhello();
   end;
*/
=============================================
带参数的存储过程：
create or replace procedure raisesalary(eno in number)
as
  --定义变量
  psal emp.salary%type
begin
  --程序体
  select salary into psal from emp where empno = eno;
  update emp set sal = sal + 100 where empno = eno;

  --注意：一般不在存储过程或者存储函数里面commit或者rollback

  --输出
  dbms_output.put_line('涨工资前薪水：'||psal||' 涨工资后薪水：'||(psal+100));
end;
=============================================
out参数：
create or replace procedure queryempinfo(eno in number,
                                         pname out varchar2(20),
                                         psal  out number, 
                                         pjob  out varchar2(20))
as
begin
  --程序体
  select ename,salary,ejob into pname,psal,pjob from emp where empno = eno;
end;
*********************************************************************************************************************************
存储函数：
语法：
create or replace function 函数名(参数)
return 函数值类型
as
plsql 子程序体;
=============================================
create or replace function(eno in number)
return number
as
  --定义变量
  psal   emp.salary%type;
  pcomm  emp.comm%type;
begin
  --程序体
  select sal,comm into psal,pcomm from emp where empno = eno;
  --返回值
  return psal * 12 + nvl(pcomm,0);
end;
/*
  注意：nvl（a,0） 表示如果a为空值，则让a等于0
*/
*********************************************************************************************************************************
JDBC 访问存储过程和存储函数：

需求jar包：ojdbc14.jar

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 passsword= "tiger";
=============================================
//访问存储过程示例
@Test
public void testProcedure(){

  String sql = "{call queryempinfo(?,?,?,?)}";--------------------------重点(一个输入参数，三个输出参数)

  Connection conn = null;
  CallableStatement call = null;        --------------------------重点（CallableStatement接口的应用）
  
  conn = DBUtil.getConnection();
  call = conn.prepareCall(sql);         --------------------------重点

  //对于输入参数，赋值
  call.setInt(1,7890);

  //对于输出参数，声明类型
  call.registerOutParameter(2,OracleTypes.VARCHAR);      --------------------------重点
  call.registerOutParameter(3,OracleTypes.NUMBER);
  call.registerOutParameter(4,OracleTypes.VARCHAR);

  //执行调用
  call.execute();

  //取出结果
  String name = call.getString(2);
  double salary = call.getDouble(3);
  String job = call.getString(4);
}
=============================================
//访问存储函数示例
@Test
public void testProcedure(){

  String sql = "{ ? = call queryempinfo(?)}";--------------------------重点（一个输入参数，一个返回值）

  Connection conn = null;
  CallableStatement call = null;        --------------------------重点（CallableStatement接口的应用）
  
  conn = DBUtil.getConnection();
  call = conn.prepareCall(sql);         --------------------------重点

  //对于输出参数，声明类型
  call.registerOutParameter(1,OracleTypes.NUMBER);      --------------------------重点

  //对于输入参数，赋值
  call.setInt(2,7890);

  //执行调用
  call.execute();

  //取出结果
  double salary = call.getDouble(1);
}
*********************************************************************************************************************************
在out参数中使用光标：
1、声明包结构
2、包头
3、包体
创建包：
create or replace package mypackage as

  type empcursor is ref cursor;       ------------自定义一个类型作为输出参数类型
  procedure queryempinfo(dno in number,emplist out empcursor);

end mypackage;

创建包体：
cteate or replace package body mypackage as

  procedure queryempinfo(dno in number,emplist out empcursor)
  as
  begin
  --打开光标
  open emplist for select * from emp where depno = dno;

  end queryempinfo;

end mypackage;
==========================================
在应用程序（java）中访问包下面的存储过程：
注意：需要带上包名

//访问包中的存储过程示例
@Test
public void testProcedure(){

  String sql = "{call mypackage.queryempinfo(?,?)}";--------------------------重点(一个输入参数，一个输出参数)

  Connection conn = null;
  CallableStatement call = null;        --------------------------重点（CallableStatement接口的应用）
  ResultSet rs = null;

  conn = DBUtil.getConnection();
  call = conn.prepareCall(sql);         --------------------------重点

  //对于输入参数，赋值
  call.setInt(1,7890);

  //对于输出参数，声明类型
  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");
  }
}