Oracle Database PL/SQL package

Oracle Database PL/SQL package 6) package www.2cto.com 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