create or replace package callable_stmt_demo
as
function get_emp_details_func( p_empno in number )
return sys_refcursor;
procedure get_emp_details_proc( p_empno in number,
p_emp_details_cursor out sys_refcursor );
procedure get_emps_with_high_sal( p_deptno in number,
p_sal_limit in number default 2000 ,
p_emp_details_cursor out sys_refcursor );
procedure give_raise( p_deptno in number );
end;
create or replace package body callable_stmt_demo
as
function get_emp_details_func( p_empno in number )
return sys_refcursor
is
l_emp_details_cursor sys_refcursor;
begin
open l_emp_details_cursor for
select empno, ename, job
from emp
where empno = p_empno; return l_emp_details_cursor;
end;
procedure get_emp_details_proc( p_empno in number,
p_emp_details_cursor out sys_refcursor )
is
begin
p_emp_details_cursor := get_emp_details_func(
p_empno => p_empno );
end;
procedure get_emps_with_high_sal( p_deptno in number,
p_sal_limit in number default 2000 ,
p_emp_details_cursor out sys_refcursor )
is
begin
open p_emp_details_cursor for
select empno, ename, job, sal
from emp
where deptno = p_deptno
and sal > p_sal_limit;
end;
procedure give_raise( p_deptno in number )
is
begin
update emp
set sal = sal * 1.5
where deptno = p_deptno;
end;
end;
是面是一些包及包体1:create or replace package body callable_stmt_demo
as
function get_emp_details_func( p_empno in number )
return sys_refcursor
为什么这里实现包体时,会有 return sys_refcursor 语句,是不是包头是什么,包体就全部照抄下来(包换返回语句)。2:get_emp_details_func过程
这里java 代码调用过程String oracleStyle =
"begin ? := callable_stmt_demo.get_emp_details_func(?); end;";
// create the CallableStatement object
cstmt = conn.prepareCall( oracleStyle );
// bind the input value
cstmt.setInt(2, inputEmpNo );
// register the output value
cstmt.registerOutParameter( 1, OracleTypes.CURSOR );
// execute the query
cstmt.execute();
rset = (ResultSet) cstmt.getObject( 1 );过程的参数顺序是什么的,我一直以为是按声明的顺序!但好像不是:比如上面的例子,为什么参数1是返回类型,而参数2是输入参数,我试着调换下参数顺序,结果执行会报类型错误
这个语句已经指明了,返回参数的位置是1,而输入参数为2,不能变更