type.sql

学习PL/SQL时收集的一些经典的SQL源代码

create or replace type myScalarType as object
(   x     int,
    y    date,
    z    varchar2(25)
  );
/

create or replace type myTableType as table of myScalarType;
/


create or replace
 function demo_proc( p_start_row in number,
                      p_end_row in number )
 return myTableType
  as
      l_data            myTableType := myTableType();
     l_cnt              number default 0;
  begin
      for x in ( select * from emp order by sal desc )
      loop
          l_cnt := l_cnt + 1;
          if ( l_cnt >= p_start_row )
          then
              l_data.extend;
              l_data(l_data.count) :=
                      myScalarType( x.empno,
                                    x.hiredate,
                                    x.ename );
          end if;
          exit when l_cnt = p_end_row;
      end loop;
  
      return l_data;
  end;
/


select *
 from the ( select cast( demo_proc(2,6) as mytableType )
               from dual ) a
/

         X Y         Z
---------- --------- -------------------------
      7788 09-DEC-82 SCOTT
      7902 03-DEC-81 FORD
      7566 02-APR-81 JONES
      7698 01-MAY-81 BLAKE
      7782 09-JUN-81 CLARK

select *
 from the ( select cast( demo_proc(3,4) as mytableType )
               from dual ) a
/

         X Y                   Z
---------- ------------------- -------------------------
      7566 1981-04-02 00:00:00 JONES
      7698 1981-05-01 00:00:00 BLAKE