6) Package 1. basic concept package (Package) is used to organize logical PL/SQL blocks or elements (variables, constants, custom data types, exceptions, processes, functions, and cursors, the package is stored as a complete unit in the database and identified by name. The package consists of two independent parts: The description part and the package body part, which are stored independently in the data dictionary. The changes in the description section of the package need to re-compile the called application, and the changes in the package body do not need to re-compile the called application. 2. Create a package 1) create a package description section. Create packag <package name> is variable, constant, and data type definition; cursor definition header; function, process definition, parameter list, and return type; end <package name>; create package my_packageis man_num number; -- defines two global variables woman_num number; cursor teacher_cur; -- defines a cursor create function f_count_num (in_sex in teachers. sex % Type) return number; -- defines a function create procedure p_count_num (in_sex in teachers. sex % type, out_num out number); -- defines an end my package; 2) create package body <package name> As cursor, function, and process definition; end <package name>; create package body my_package ascursor teacher_cur is -- the cursor defines the specific select tid, tname, title, sex from teachers where TID <117; function f_count_num -- Function Definition (in_sex in teachers. sex % Type) return numberas out_num number; begin if in_sex = 'M' then select count (sex) into out_num from teachers where sex = 'M'; else select count (sex) into out_num from teachers where sex = 'F'; end if; Return (out_num); End f_count_num; Procedure p_count_num -- specific process definition (in_sex in teachers. sex % type, out_num out number) asbeginif in_sex = 'M' thenselect count (sex) into out_numfrom teacherswhere sex = 'M'; elseselect count (sex) into out_numfrom teacherswhere sex = 'F'; end if; end p_count_num; end my_package; -- end of package Definition 3. the name of the called package. variable name (constant name) package name. the name of the cursor package. function Name (process name) SQL> variable man_num numbersql> execute man_num: = my_package.f_count_num ('M') 4. delete package drop package my_package
