Define the package and usage of stored procedures in Oracle

Use the dept table under the scott account to define the package and stored procedure in Oracle; select * fromdeptorderbydeptno; 10 ACCOUNTINGNEWYO

Use the dept table under the scott account to define the package and stored procedure in Oracle; select * from dept order by deptno; 10 ACCOUNTING NEW YO

Define the package and usage of stored procedures in Oracle

Use the dept table under the scott account;

Select * from dept order by deptno;

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

To facilitate the demonstration, insert a data entry:

Insert into dept (deptno, dname, loc) values (50, 'system', 'New YORK ');

The newly inserted record is: 50 system new york.

We mainly demonstrate that the returned types of stored procedures in a package are pipelined, cursor, and value.

1. The return type is 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. Define the package and 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;
/
Bytes -----------------------------------------------------------------------------------------------
Create or replace package body SPTest
Is
/* Return a pipelined demo start */
Function getDept (in_loc IN VARCHAR2) return dept_obj_type pipelined is
Rochelle 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;
Rochelle dept_obj.deptno: = dept_data_rec.DEPTNO;
Rochelle 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
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;
/

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

Finally, execute the stored procedure.

/* Return pipelined table */

Select deptno, dname from table (SPTest. getDept ('New YORK ') order by deptno;

/* Return cursor */

Select SPTest. getDeptInfo (10) from dual;

/* Return the specific value */

Select SPTest. getName (50) from dual;