Oracle package)

1. Why use a package?

A: In a large project, there may be many modules, and each module has its own processes and functions. These processes and functions are put together by default (for example, in PL/SQL, the process is put together by default, that is, procedures), which is very inconvenient for query and maintenance, an accidental deletion event may even occur. Therefore, you can use packages to classify management processes and functions.
You can also customize the type in the package, so that you can directly use the custom variable in the process and function. The concept in Oracle is similar to that in Java, but in Java, packages are for classification management, but all keywords are for package.
The package is divided into two parts,Package specificationAndPackage body.

2. Use of packages

(1) define the package specification, which can exist independently.

-- Define package specifications
Create or replace package p_stu
As
-- Define struct
Type re_stu is record (
Rname student. name % type,
Rage student. Age % Type
);
-- Define a cursor
Type c_stu is ref cursor;
-- Define a function
Function numadd (num1 number, num2 number) return number;
-- Define the process
Procedure getstulist (CID in varchar2, c_st out c_stu );
End;

(2) implement the package specification, that is, the package body, the name must be consistent, the same cursor definition cannot appear, but the struct can, and methods and procedures must be implemented.

-- Implements the package body with the same name.
Create or replace package body p_stu
As
-- Cursors and struct, which have been declared in the package specification and are directly used without being declared in the package body.
    
-- Implementation Method
Function numadd (num1 number, num2 number) return number
As
Num number;
Begin
Num: = num1 + num2;
Return num;
End;

-- Implementation process
Procedure getstulist (CID varchar2, c_st out c_stu)
As
R_stu re_stu; -- directly use the structure in the package specification
Begin
Open c_st for select name, age from student where classid = CID;
-- If the cursor has been traversed in the process, there will be no value in the block used in this process.
-- Loop
-- Fetch c_st into r_stu;
-- Exit when c_st % notfound;
-- Dbms_output.put_line ('name = '| r_stu.rname );
-- End loop;
End;
End;

(3) Use

Declare
C_stu p_stu.c_stu; -- defines the cursor variable in the package
R_stu p_stu.re_stu; -- defines the struct variable in the package.
Num number;
Begin
-- Use and traverse the returned result set during the package process
P_stu.getstulist ('c001', c_stu );
Loop
Fetch c_stu into r_stu;
Exit when c_stu % notfound;
Dbms_output.put_line ('name = '| r_stu.rname );
End loop;

-- Use methods in the package
Select p_stu.numadd (5, 6) into num from dual;
Dbms_output.put_line ('num = '| num );
End;