Where The Streets Have No Name

How to write this function using pipelined method? 본문

Developement/DataBase

How to write this function using pipelined method?

highheat 2008. 1. 19. 22:15

출처 : http://forums.oracle.com/forums/thread.jspa?threadID=385558

create or replace function fewcols(n in number,t in varchar2)
return sys_refcursor is
str varchar2(1000);
opt  sys_refcursor;
begin
for i in (select column_name from all_tab_cols
            where table_name = t and column_id<=n) loop
str:=str || i.column_name || ',';
end loop;
open opt for 'select ' || substr(str,1,length(str)-1) ||' from ' || t;
return opt;
end;

 
Function created.
 
SQL> select fewcols(4,'EMP') from dual;
 
FEWCOLS(4,'EMP')
--------------------
CURSOR STATEMENT : 1
 
CURSOR STATEMENT : 1
 
  EMPNO ENAME      JOB           MGR
------- ---------- --------- -------
   7369 SMITH      CLERK        7902
   7499 ALLEN      SALESMAN     7698
   7521 WARD       SALESMAN     7698
   7566 JONES      MANAGER      7839
   7654 MARTIN     SALESMAN     7698
   7698 BLAKE      MANAGER      7839
   7782 CLARK      MANAGER      7839
   7788 SCOTT      ANALYST      7566
   7839 KING       PRESIDENT
   7844 TURNER     SALESMAN     7698
   7876 ADAMS      CLERK        7788
   7900 JAMES      CLERK        7698
   7902 FORD       ANALYST      7566
   7934 MILLER     CLERK        7782
 
14 rows selected.