Oracle Packages and ref cursor____oracle

First of all, we need to rearrange the relevant concepts, functions in an Oracle database are invoked only by others, stored procedures can be executed separately, and a stored procedure can be invoked, and the package's action package can place any statements (procedures, functions, cursors, cursors, types, variables) that appear in the block in the package. Equivalent to a container. The advantage of putting a declaration statement into a package is that the user can refer to it from other pl/sql blocks, so the package provides a global variable for pl/sql, which is divided into headers and packages, and header is compiled before the package can be compiled.
The creation of Baotou:
1) Baotou:
Syntax format:
CREATE OR REPLACE PACKAGE package_name/* Header name * *
Is|as
PL/SQL_PACKAGE_SPEC/* Defines procedures, functions, and return types, variables, constants, and data type definitions * *
The definition of Baotou should follow the following guidelines:
1 package element position can be arranged arbitrarily. In the declaration section, however, the object must be declared before the reference.
2) Baotou can not describe any type of element. For example, Baotou can take only procedure and function description statements without declaring any exceptions and types.
3 any declaration of a procedure or function must be described only by the child program and its parameters, without any description of the code, and the implementation of the code can only appear in the package body. It differs from the block declaration, in which the procedure and the Code of the function can appear at the same time in the declaration section.
2. Package Body:
Syntax format:
CREATE OR REPLACE PACKAGE body package_name/* Package name must be the same as the package name of Baotou.
is | As
Pl/sql_package_body/* cursors, functions, specific definitions of processes * *
Inclusion is independent of the Baotou, the package can only be compiled after the completion of the Baotou compilation. A code snippet in the package body with a specific implementation of the subroutine described in the header. In addition, the package body can include additional declarations that have the properties of the package person's entire sentence, but these additional declarations are not visible to the header.
Demo:
****************
* Package
****************
Create or Replace package Mypack
As
Type MyType is REF CURSOR return emp%rowtype; --Declaring a 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;--declaring REF CURSOR type variable
Begin
Open eee for SELECT * from EMP where deptno=dno;
return EEE;
End Myemp;
End Mypack;
Analysis:
The type MyType is REF CURSOR return Emp%rowtype appears in the header.
There is a concept of REF cursor, and a REF cursor is a temporary object that dynamically associates the result set. That is, the dynamic decision to execute the query at run time, his main function is to implement the function of passing the result set between programs
① declaring ref Cursors
⑴ strongly type REF cursor: Specifies that the type of the Retrun TYPE,REF cursor variable must be the same as the return type.
Syntax: type REF CURSOR name is REF CURSOR return result set returns record type;
⑵ weakly type REF CURSOR: Does not specify return type, can match any type of cursor variable to get any result set.
Syntax: Type REF CURSOR name is REF CURSOR;
② declares a REF cursor type variable;
Syntax: variable name declared REF CURSOR type;

③ opens the REF cursor, associating the result set;
Syntax: Open REF cursor type variable for query statement returns result set;

④ obtain records, Operation Records;
Syntax: Fetch REF CURSOR into temporary record type variable or attribute type variable list;

⑤ closes the cursor, releasing the resource completely;
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;