[Lone solitary Nine swords] Oracle Knowledge Point Grooming (ix) Common package of database common objects

Link navigation in this series:

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (a) table space, user

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (ii) connection of databases

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (iii) Import, export

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (iv) DML and DDL for SQL statements

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (v) table, View of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (vi) procedure, function and Sequence of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (vii) Cursor of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (eight) common exception

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (ix) database common objects of the package

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (10)%type and%rowtype and common functions

5.8, the package operation

Package is a combination of a set of related procedures, functions, variables, constants #sinaeditor_temp_fontname, types, and cursors, such as PL/SQL programming elements.

The package has an object-oriented design and is the encapsulation of these PL/SQL programming elements. A package consists of two separate parts:

1. Package Packages declaration or definition (also known as Baotou): The package Definition section is a specification for creating packages that declare elements such as data types, variables, constants, cursors, and so on in a package. It can be called externally only after it has been declared in the header.
2. Package Body Packpage Body: The package is a concrete implementation of the package definition section.
3. The objects in the package include stored procedures, functions, cursors, custom types, and variables that can be applied to the Pl_sql block.

Attention:
A) The name of the Baotou and the package must be the same;

b) When the header and the package are defined, there is no beginning keyword, unlike stored procedures and functions.

c) Declare in Baotou the storage process, functions, cursors, custom types and variables, etc., implemented in the package body.

d) Do not use declare when declaring constants, variables, type definitions, exceptions, and cursors within a package.

e) The definition of procedures and functions within the package do not create or replace statements.

f) The package declaration and the package body are separated.

5.8.1, defining the package

• Defining headers

1 Create or ReplacePackage Pkg_person2  is3Type reftable isRefcursor;4  　　 functionFun_getpersonname (argnameinch varchar2, Arggender outvarchar2)return varchar2;5  　　 procedureProc_getpersonlist (argnameinch varchar2, Arggenderinch varchar2, argresult out reftable);6 EndPkg_person;

• Defining the package Body

1 Create or ReplacePackage Pkg_person2  is3  　　 functionFun_getpersonname (argnameinch varchar2, Arggender outvarchar2)return varchar24  　　  as5Result Person.name%TYPE;6  　　 begin7  　　　　 SelectName,gender intoResult,arggender fromPersonwhereName=Argname andRowNum=1;8 　　　　 returnResult;--return value9  　　 EndFun_getpersonname;Ten  One  　　 procedureProc_getpersonlist (argnameinch varchar2, argmsg outvarchar2, Argresult out reftable) A  　　  as  -VNamevarchar2( -);--define the variable to set the length -  　　 begin theVName:=Argname;--assignment, with each statement ending with a semicolon -  　　　　 OpenArgresult is Select *  fromPersonwhereName likeVName||'%';--log query results to the return cursor -  　　　　 --of course, other complex judgments, conversions and other operations can be performed. -Argmsg:='Normal'; +  　　　　 Commit; -  +  　　　　 Exception A  　　　　　　  whenOthers ThenArgmsg='Exception'; at  　　　　　　 rollback;--If you have previously executed a transactional statement, it is necessary to keep the data secure -  -  　　 Endproc_getpersonlist; -  - EndPkg_person;

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (ix) Common package of database common objects