[PLSQL] use of the package, plsqlpackage

[PLSQL] use of the package, plsqlpackage
**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************Package

A package is a PLSQL structure that stores related objects together. A Package contains two components: specification package declaration and body (Program Implementation in the declaration, Package body ). Each part is stored separately in the data dictionary. When the package is declared, an operation interface is used to indicate that it is visible. The package body is a black box that hides implementation details from the application.

Package composition includes:

Process, function, variable, cursor (defining an SQL statement), type (defining conforming type), constant, exception

Package advantages:

--- Facilitates the process and function organization and resolves naming conflicts.

--- Facilitates the process and function organization: changes the package body without changing the definition of the package declaration;

--- Restrict process and function dependencies

--- When the package body is implemented, other programs can phishing and rebuild their own programs;

--- Convenient security management of processes and functions: Only one-time authorization is required for package Access Authorization to distinguish between public and private processes;

--- Improve performance: when the package is called for the first time, the entire package is put into the memory to reduce the disk IO for multiple calls;

3.8.1 anonymous Block

-- The process and function can only be called in this anonymous block, for example;

Declare

V_n1 number: = 1;

Function fun1 (p_in number) return number ls

Begin

Return p_in

End;

Procedure proc1 ls

Begin

Dbms_output.put_lin (fun1 (v_n1 ));

End;

Begin

Proc1;

End;

3.8.2 package syntax

Baotou statement:

Create or replace package pkg_name {ls |}

Definition of public variables (varibale;

Definition of Public type;

Definition of a public cursor (cursor;

Function description;

Process description;

End;

-- Package body declaration syntax;

Create or replace package body pkg_name

Ls |

-- Call once for execution

Function implementation

-- Call once for execution

Process Implementation

Begin

-- Initialization code

-- Execute any object in the first call package once

End;

3.8.3 Package usage

Package Declaration

Create or replace packeage pkg1

Ls

-- Public type

Type t_rec is record

(M1 number, m2 varchar2 (10 ));

-- Public variables

V_rec t_rec;

-- Public process

Procedure proc1;

-- Public Functions

Function fun1 (p_in number) return number;

End

Package body implementation

Create or replace packeage pkg1

Ls

-- Implementation process

Procedure proc1

Ls

Begin

Dbms_outpurt.put_lin (v_rec.m1 );

End;

-- Implement Functions

Function fun1 (p_in number) return number

Ls

Begin

Return p_in

End;

-- Initialization code

Begin

V_rec.m1: = 100;

End;

Call package

Begin

Pkg1.v _ rec. m1: = pkg1.fun1 (10 );

Pkg1.proc1;

End;

**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************