Oracle中定義package以及預存程序的使用

Oracle中定義package以及預存程序的使用

Oracle中定義package以及預存程序的使用

使用scott賬戶下的dept表；

select * from dept order by deptno;

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

為了示範方便，插入一條資料：

insert into dept(deptno, dname, loc) values(50,'SYSTEM', 'NEW YORK');

新插入的記錄為：50 SYSTEM NEW YORK

 

我們主要示範在package中預存程序的傳回型別為pipelined，cursor 和 value三種。

1.傳回型別為pipelined。　　

create or REPLACE type dept_obj is OBJECT( DEPTNO NUMBER(2,0), DNAME VARCHAR2(14 BYTE) );

create or REPLACE type dept_obj_type AS table of dept_obj;

2.定義package 和package body。

------------------------------------------------------

create or replace package SPTest
is
/*return a pipelined demo start*/
type dept_data_rec_type is RECORD(
  DEPTNO NUMBER(2,0),
    DNAME VARCHAR2(14)
);

type dept_ref_type is REF CURSOR;

function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined;
/*return a pipelined demo end*/

/*return a cursor demo start*/
FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) RETURN dept_ref_type;
/*return a cursor demo end*/

/* return a varchar value start */
function getName(in_deptno in number) RETURN VARCHAR2;
/* return a varchar value end */
end SPTest;
/
-----------------------------------------------------------------------------------------------
create or replace package body SPTest
is
  /*return a pipelined demo start*/
  function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined is
    l_dept_obj dept_obj :=dept_obj(null, null);
    dept_ref_type_cursor dept_ref_type;
    dept_data_rec        dept_data_rec_type;
  begin
    open dept_ref_type_cursor
    for select deptno, dname from dept where loc = in_loc;
 
    loop
    fetch dept_ref_type_cursor into dept_data_rec;
    exit when dept_ref_type_cursor%NOTFOUND;
    l_dept_obj.DEPTNO := dept_data_rec.DEPTNO;
    l_dept_obj.DNAME := dept_data_rec.DNAME;
 
    pipe row(l_dept_obj);
    end loop;
    close dept_ref_type_cursor;
    RETURN ;
  end getDept;
  /*return a pipelined demo end*/
 
  /*return a cursor demo start*/
  FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) RETURN dept_ref_type
  AS
          dept_ref_type_cursor dept_ref_type;       
    BEGIN
   
    OPEN dept_ref_type_cursor FOR
          SELECT deptno, dname, loc FROM dept where deptno = in_deptno;
   
    RETURN dept_ref_type_cursor;
   
    END getDeptInfo;
  /*return a cursor demo end*/
 
  /* return a varchar value start */
  function getName(in_deptno in number) RETURN VARCHAR2
  as rtn_deptname VARCHAR2(100);
  begin
    select dname into rtn_deptname from dept where deptno = in_deptno;
    RETURN rtn_deptname;
  end getName;
  /* return a varchar value start */
 
end SPTest;
/

------------------------------------------------------

最後，執行預存程序。

/*返回pipelined table */

select deptno, dname from table(SPTest.getDept('NEW YORK')) order by deptno;

/*返回cursor*/

select SPTest.getDeptInfo(10) from dual;

/*返回具體值*/ 

select SPTest.getName(50) from dual;