This Is A FineDay

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  93 随笔 :: 0 文章 :: 69 评论 :: 0 Trackbacks
								
										
												
														SQL
														>
														create table test
														(
														name varchar2
														(
														4
														),
														text  varchar2
														(
														9
														));



SQL > create view test_view  as  select text from test where name = userenv ( 'client_info' );



SQL > insert into test values ( 'zxx' , 'boy' );

SQL > insert into test values ( 'taw' , 'gril' );

SQL > commit ;



SQL > exec dbms_application_info . set_client_info ( 'zxx' );

SQL > select  from test_view ;



TEXT

---------

boy

--





a
create or replace type emp_type
as object (
empno number(4),
ename varchar2(10)
);
/
create or replace type emp_type_table as table of emp_type;
/
create or replace function emp_of_dept(p_deptno in number)
return emp_type_table
pipelined as
l_emp_type emp_type;
begin
for l_myrow in (select empno, ename from emp
where deptno = p_deptno) loop
l_emp_type := emp_type(l_myrow.empno, l_myrow.ename);
pipe row (l_emp_type);
end loop;
return;
end emp_of_dept;
/

select * from table(emp_of_dept(10));
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER

select * from table(emp_of_dept(30));
EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES

Tom - what do you think about this? 

Followup: 
I think it is alot more code then:

create or replace 
procedure foo( p_deptno in number, p_result_set out sys_refcursor )
as
begin
open p_result_set for
select empno, ename from emp where deptno = p_deptno;
end;

and having the client (which is doing Oracle stuff anyway) issue 

begin foo( :n, :m ); end;


It'll be less performant as well. 
Neat "trick" but not best practice. 

create or replace function f_test(p_num in number)
return sys_refcursor
is
Result sys_refcursor;
begin
open Result for
select * from t1 t where t1.c1=p_num;
return(Result);
exception
when others then
raise;
end;

select f_test(1) from dual;
posted on 2008-04-20 15:00 Peter Pan 阅读(1797) 评论(0)  编辑  收藏 所属分类: DB

只有注册用户登录后才能发表评论。


网站导航: