存储过程的调用

• 存储过程的调用

--oracle    
create or replace procedure queryempifo(no in number,pname out varchar2, psale out number, ptype out number)
as
begin
  select sname,sale,stype into pname,psale,ptype from emp  where empno=no;
end;
/

--java  
Connection con = null;
CallableStatement cst = null;
try {
    con = DB.getInstance().getConnection();
    cst = con.prepareCall("{call queryempifo(?,?,?,?)}");
    //输入参数cst.setInt(第几个参数，参数值);
    cst.setInt(1, 123);
    //输出参数
    cst.registerOutParameter(2, OracleTypes.VARCHAR);
    cst.registerOutParameter(3, OracleTypes.NUMBER);
    cst.registerOutParameter(4, OracleTypes.NUMBER);

    cst.execute();
    String  result = cst.getString(1);
    } catch (Exception e) {

    }finally{

    }

• 存储函数的调用

//存储函数
create or replace function queryprice(no in number)
return number
as
    psale emp.sale%type;
    pp emp.prize%type;
begin
  select sale,prize into psale,pp from emp where empno=no;
  return psale*12+pp;
end;

@Test
public void test(){
    Connection conn = null;
    CallableStatement cs = null;
    try {
        conn = ConnectionManager.getConnection();
        String sql = "{?=call queryprice(?)}";
        cs = conn.prepareCall(sql);
        cs.registerOutParameter(1, OracleTypes.NUMBER);
        cs.setInt(2, 1);
        cs.execute();

        double sale = cs.getDouble(1);
        System.out.println(sale);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        ConnectionManager.closeAll(conn, cs, null);
    }
}

• 调用包内的存储函数（返回一个结果集）

//创建包
create or replace package mypackage as
--定义empcursor为cursor类型
 type empcursor is ref cursor;
 procedure  queryemplist (ty in number,emplist out empcursor);
end mypackage;
//创建包体
create or replace package body mypackage is
  procedure  queryemplist (ty in number,emplist out empcursor) as
    begin
      open emplist for select * from emp where stype=ty;
    end queryemplist;
end mypackage;

Java代码调用
@Test
public void test(){
    Connection conn = null;
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
        conn = ConnectionManager.getConnection();
        cs = (CallableStatement) conn.prepareCall("{call mypackage.queryemplist(?,?)}");
        //设置输出参数的值
        cs.setInt(1, 1);
        cs.registerOutParameter(2, OracleTypes.CURSOR);
        cs.execute();
        //取数据
        rs = ((OracleCallableStatement)cs).getCursor(2);
        if (rs != null) {
            hile (rs.next()) {
                System.out.println(rs.getString("sale"));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        ConnectionManager.closeAll(conn, cs, null);
    }
}

• 本地cmd存储过程测试
  1.sqlplus连接数据库：conn user/pasword
  2.cls清除命令行中的内容: host cls
  3.打开屏幕的初始开关：set serveroutput on
  4.调用存储过程：
  –方法1
  exec sayhelloword();
  –方法2
  begin
  sayhelloword();
  end;
  /