pl/sql 存储过程和函数小结

其实存储过程和函数可以类比于java中的方法，

--创建oracle存储过程(无参)
create or replace procedure sayhelloworld
--说明部分，类似于declare,但as在没有声明时不可省略，declare可以省略
as
begin
 dbms_output.put_line('HELLO WORLD');
end;
/
--oracle数据库存储数据对象.默认采用大写，所以创建的存储过程名为SAYHELLOWORLD
--调用存储过程的方法
--1：
 exec sayhelloworld();
--2:
 begin
   sayhelloworld();
 end;
/

--创建带参数的存储过程
create or replace procedure raisesalary(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 || '涨后' ||(pasl+100));
end;
/
--注意：一般不在存储过程或存储函数中，commit和rollback.(可以，但不这样做)

create or replace function queryempincome（eno in number）
return number
as
--定义变量
 psal emp.sal%type;
 pcomm emp.comm%type;
begin
 select sal,comm into psal,pcomm from where empno=eno;
 --要记得有null的情况
 return psal*12+nvl(pcomm,0)
end；
/

create or replace function queryempinform（eno in number,
                                           pename out varchar2）
                                           psal out number
                                           pjob out varchar2)
as
begin
 select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/

//在java程序中访问存储过程
public void testProcedure(){
    String sql ="{call queryempinform(?,?,?,?)}";

    Connection conn = null;
    CallableStatement call = null;
    try{
        conn = JDBCUtils.getConnection();
        call = conn.prepareCall(sql);
        //对于in参数，赋值
        call.setInt(1,7839);
        //对于out参数，声明
        call,registerOutParameter(2,OracleTypes.VARCHAR);
        call,registerOutParameter(3,OracleTypes.NUMBER);
        call.registerOutParameter(4,OracleTypes.VARCHAR)
        //执行调用
        call.excute();
        //取出结果
        String name = call.getString(2);
        double sal = call.getDouble(3);
        String job = call.getString(4);
        System.out.println(name+"\t"+sal+"\t"+job);
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JBDCUtils.release(conn,call,null);
    }

}

//在java中访问存储函数
public void testFunction(){
    //存储函数有返回值，这里用？号接收
    String sql = "{?=call queryempincome(?)}";
    Connection conn = null;
        CallableStatement call = null;
    try{
        conn = JDBCUtils.getConnection();
        call = conn.prepareCall(sql);
        //对返回值声明
        call,registerOutParameter(1,OracleTypes.NUMBER);
        //对输入参数赋值
        call.setInt(2,7839);
        //执行调用
        call.excute();
        //取出结果
        double income = call.getDouble(1);
        System.out.println("该员工的年收入是:"+income);
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JBDCUtils.release(conn,call,null);
    }
}

包结构包括包头和包体

open 游标 for select ...
打开游标并将select查询的结果赋给游标  

//访问包结构下的存储过程（out 参数是游标）
public void testCursor(){
    //要加上包名
    String sql ="{call MYPACKAGE.queryeEmpList(?,?)}";
    Connection conn = null;
    CallableStatement call = null;
    ResultSet rs = null;
    try{
        conn = JDBCUtils.getConnection();
        call = conn.prepareCall(sql);
        //对于in参数，赋值
        call.setInt(1,10);
        //对于out参数，声明
        call,registerOutParameter(2,OracleTypes.CURSOR);
        //执行调用
        call.excute();
        //取出结果，要将call转化为OracleCallableStatement类型
        rs = ((OracleCallableStatement)call).getCursor(2);
        while(rs.next()){
            int empno = rs.getInt("empno");
            String name = rs.getString("ename");
            doble salary = rs.getDouble("sal");
            String job = rs.getString("empjob");
            System.out.println(empno+"\t"+name+"\t"+salary+"\t"+job);
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JBDCUtils.release(conn,call,null);
    }
}