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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.