Oracle package and REFCURSOR

First, you need to repeat the related concepts. Functions in oracle databases can only be called by others. stored procedures can be executed separately and stored procedures can be called,

First, you need to repeat the related concepts. Functions in oracle databases can only be called by others. stored procedures can be executed separately and stored procedures can be called,

First, you need to repeat the related concepts. Functions in Oracle databases can only be called by others. stored procedures can be executed separately and stored procedures can be called, the role package can place any statement (process, function, cursor, cursor, type, variable) that appears in the block declaration in the package, which is equivalent to a container. the benefit of putting a declaration statement into a package is that you can reference it from other PL/SQL blocks. Therefore, the package provides global variables for PL/SQL. The packages are divided into headers and packages, the header must be compiled before the package body can be compiled.

Baotou creation:

1) Baotou:
Syntax format:
Create or replace package package_name/* header name */
IS |
Pl/SQL _package_spec/* defines the process, function, return type, variable, constant, and data type */
The following principles should be observed when defining headers:
1) The position of the package element can be arbitrarily arranged. However, in the declaration part, the object must be declared before reference.
2) the header may not describe any type of elements. For example, the header can only contain process and function description statements without declaring any exceptions and types.
3) Any declaration of procedures and functions must only describe the subroutine and its parameters, and there cannot be any code description. The implementation of code can only appear in the package body. it is different from block Declaration. In block declaration, the code of the process and function can appear in the declaration part at the same time.
2. Package body:
Syntax format:
Create or replace package body package_name/* The PACKAGE name must be the same as the PACKAGE name in the Baotou */
IS |
Pl/SQL _package_body/* cursor, function, specific process definition */
The package body is independent from the header. It can only be compiled after the header is compiled. the specific implementation code segment of the subroutine with the header in the package. in addition, the package body can include additional declarations with the full-sentence attributes of the package body, but these additional declarations are invisible to the header.
Demo:
****************
* Package
****************
Create or replace package mypack
As
Type mytype is ref cursor return emp % rowtype; -- declare REF cursor
Function myemp (dno number) return mytype;
End;
****************
* Body
****************
Create or replace package body mypack
As
Function myemp (dno number) return mytype
As
Eee mytype; -- declare the Ref cursor Type Variable
Begin
Open eee for select * from emp where deptno = dno;
Return eee;
End myemp;
End mypack;
Analysis:
Type mytype is ref cursor return emp % rowtype appears in the header;
There is a concept of ref cursor in it. The ref cursor is a temporary object of the result set dynamically associated. That is, when running, the query is dynamically decided. Its main function is to implement the function of passing the result set between programs.
① Declare a REF cursor
(1) strong type REF cursor: Specify the retrun type. The type of the REF cursor variable must be the same as the return type.
Syntax: Type REF cursor name IS ref cursor Return result set Return record Type;
(2) weak type ref cursor: return type is not specified and can match any type of CURSOR variable to obtain any result set.
Syntax: Type REF cursor name IS ref cursor;
② Declare the Ref cursor type variable;
Syntax: The variable name has declared the Ref cursor type;

③ Open the REF cursor and associate the result set;
Syntax: return result set of Open Ref cursor type variable For query statement;

④ Retrieve records and operation records;
Syntax: fetch REF cursor name InTo temporary record type variable or attribute Type Variable list;

⑤ Close the cursor and completely release the resource;
Syntax: Close REF cursor name;
Attached test code:
Create or replace package mypack
As
Type mytype is ref cursor return emp % rowtype;
Function myemp (dno number) return mytype;
End;
Create or replace package body mypack
As
Function myemp (dno number) return mytype
As
Eee mytype;
Begin
Open eee for select * from emp where deptno = dno;
Return eee;
End myemp;
End mypack;
Drop table emp;
Create table emp
(
DEPTNO number,
COMM number,
SAL number
);
Delete from emp Where deptno = 10;
Delete from emp Where deptno = 11;
Insert Into emp (deptno, comm, sal) Values (10, 1, 2 );
Insert Into emp (deptno, comm, sal) Values (11,2, 3 );
Commit;