存储过程的调用

• 存储过程的调用

--oracle    create or replace procedure queryempifo(no in number,pname out varchar2, psale out number, ptype out number)asbegin  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 numberas    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;@Testpublic 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代码调用@Testpublic 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;
  /