set echo oncreate or replace package my_pkg as type refcursor_Type is ref cursor; procedure get_emps( p_ename in varchar2 default NULL, p_deptno in varchar2 default NULL, p_cursor in out refcursor_type ); end; /create or replace package body my_pkg as procedure get_emps( p_ename in varchar2 default NULL, p_deptno in varchar2 default NULL, p_cursor in out refcursor_type ) is l_query long; l_bind varchar2(30); begin l_query := 'select deptno, ename, job from emp'; if ( p_ename is not NULL ) then l_query := l_query || ' where ename like :x'; l_bind := '%' || upper(p_ename) || '%'; elsif ( p_deptno is not NULL ) then l_query := l_query || ' where deptno = to_number(:x)'; l_bind := p_deptno; else raise_application_error( -20001, 'Missing search criteria' ); end if; open p_cursor for l_query using l_bind; end; end; /variable C refcursor set autoprint on exec my_pkg.get_emps( p_ename => 'a', p_cursor => :C ) exec my_pkg.get_emps( p_deptno=> '10', p_cursor => :C )
没错我就是要返回结果集因为在javabean中太长了不好写也不好看
所以,
你能给个相关的例子吗?
type mycur is ref cursor;
my_sql varchar2(100);
begin
my_sql := 'select * from emp';
open mycur for my_sql;
end;
ERROR 位于第 6 行:
ORA-06550: 第 6 行, 第 7 列:
PLS-00330: 无效的类型名用法或子类型名用法
ORA-06550: 第 6 行, 第 2 列:
PL/SQL: Statement ignored
可是我如何有jsp中调用呢
或者说直截运行?
as
type refcursor_Type is ref cursor; procedure get_emps( p_ename in varchar2 default NULL,
p_deptno in varchar2 default NULL,
p_cursor in out refcursor_type );
end;
/create or replace package body my_pkg
as
procedure get_emps( p_ename in varchar2 default NULL,
p_deptno in varchar2 default NULL,
p_cursor in out refcursor_type )
is
l_query long;
l_bind varchar2(30);
begin
l_query := 'select deptno, ename, job from emp'; if ( p_ename is not NULL )
then
l_query := l_query || ' where ename like :x';
l_bind := '%' || upper(p_ename) || '%';
elsif ( p_deptno is not NULL )
then
l_query := l_query || ' where deptno = to_number(:x)';
l_bind := p_deptno;
else
raise_application_error( -20001, 'Missing search criteria' );
end if; open p_cursor for l_query using l_bind;
end;
end;
/variable C refcursor
set autoprint on
exec my_pkg.get_emps( p_ename => 'a', p_cursor => :C )
exec my_pkg.get_emps( p_deptno=> '10', p_cursor => :C )