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

Oracle数据库对象：表、视图、索引、序列、同义词、存储过程、存储函数等都是数据库对象
存储在数据库中 供用户程序调用的子程序叫：存储过程或者存储函数
  相同点：完成特定功能的程序
  区别：存储函数用return语句返回值
  一般来讲：如果只有一个返回值，则用存储函数，否则（有多个或者没有返回值），使用存储过程
一、存储过程：
  1、创建存储过程语法：

    create or replace procedure 过程名(参数)    as    plsql子程序体;

  2、调用存储过程：(两种方法)
    1）、exec sayhello();
    2）、begin
                  sayhello();
              end;
=============================================
*创建带输入参数（in类型）的存储过程：

create or replace procedure raisesalary(eno in number)as  --定义变量  psal emp.salary%typebegin  --程序体  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;

=============================================
*创建带输入输出参数（in类型，out类型）的存储过程：

create or replace procedure queryempinfo(eno in number,                                         pname out varchar2(20),                                         psal  out number,                                          pjob  out varchar2(20))asbegin  --程序体  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 numberas  --定义变量  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";=============================================//访问存储过程示例@Testpublic 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);}

=============================================
//访问存储函数示例

@Testpublic 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）中访问包下面的存储过程：
注意：需要带上包名

//访问包中的存储过程示例@Testpublic 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");  }}