Oracle Package Overview (i) "Weber"

I. PL/SQL Package overview

1. What is a PL/SQL package:

Combinations of related components: PL/SQL types, variables, data structures, and expressions, subroutines: procedures and functions

2. Components of the package:

Consists of two parts:

Baotou

Inclusion

3. Benefits of the Package

Read multiple objects at once into memory

II. components of the PL/SQL package

Baotou: Public part: In-Package and out-of-package programs can be accessed

Inclusion: Private part: A variable or program defined by a package can only be called by a program within this package

1. Create a Baotou

Grammar:

CREATE [OR REPLACE] Package package_name is| as      Public type and variable declarations    subprogram specificationsend [package_name];

The OR replace option deletes and re-creates the package

The variable initialization value declared in the header defaults to NULL

All structures declared in Baotou are visible to all users granting the package permission

Baushong Example: comm_pkg

CREATE OR REPLACE package comm_pkg is  std_comm number:0.10;  0.10   PROCEDURE Reset_comm (new_comm number); END comm_pkg; /Std_comm is a global variable, initially 0. Ten Reset_comm The process of re-setting the bonus, which is defined in the package body

2. Create the package body

Grammar:

CREATE [OR REPLACE] Package BODY package_name is| as     Private type and variable declarations    subprogram bodies[begin initialization statements]end [package_name];

The OR replace option removes and rebuilds the package body

Identifiers defined in the package body are private and are not visible outside the package body

All private structures must be declared before they are referenced.

Public structures are visible in the package body

Create or Replace package comm_pkg isStd_comm Number:=0.10; Procedure Reset_comm (New_comm number) End;create or replace package body comm_pkg isfunction Validate (comm number)returnBoolean isMax_comm employees.commission_pct%type; BeginSelectMax (commission_pct) into Max_comm fromemployees; return(Comm between0and Max_comm);  End Validate; Procedure Reset_comm (new_comm number) isbeginifValidate (New_comm) then Std_comm:=New_comm; ElseRaise_application_error (-20210,'Bad Commission'); Endif; End Reset_comm;end;

Iii. calling subroutines in a package

Calling a subroutine within the same package:

CREATE OR REPLACE package BODY comm_pkg are ...  PROCEDURE Reset_comm (new_comm number) is the  BEGIN    IF Validate (New_comm) then      std_comm:= New_comm ;    ELSE ...     END IF;  END Reset_comm; END comm_pkg;

To invoke a procedure in a package in Sqlplus:

Sql> Execute Comm_pkg.reset_comm (0.15); SQLSet  serveroutput on; SQL> eddeclarev_std_comm comm_pkg.std_comm%type;beginv_std_comm:= Comm_pkg.std_comm ;d Bms_output.put_line (V_std_comm); end; / Result: SQL>/std_comm:.  the PL/sql procedure successfully completed

To invoke a procedure within a package in a different mode

Sql> Conn hr/Hrsql>Grant execute on comm_pkg to Scott;conn Scott/Tigersql> Execute Comm_pkg.reset_comm (0.36);P L/SQL procedure successfully completedsql>SQL>Declare2V_std_comm comm_pkg.std_comm%type; 3begin4V_std_comm: =Comm_pkg.std_comm; 5Dbms_output.put_line ('V_std_comm:'|| v_std_comm| |'Std_comm:'||Comm_pkg.std_comm); 6end; 7/V_std_comm:.36std_comm:. $PL/sql procedure successfully completed

Note: Do not write this in the SQL window, otherwise the execution is executed in SQL, the global variable exists in the SQL process, there is no SQL window, so the SQL window is always access to the initial value of Std_comm: 0.10

Iv. creation and use of non-body packages

 is   Mile_2_kilo  CONSTANT number:1.6093;  Kilo_2_mile  CONSTANT Number:0.6214;  Yard_2_meter CONSTANT Number:0.9144;  Meter_2_yard CONSTANT Number:1.0936; end;

A global variable, a subroutine outside the package, or an anonymous block can be used when a variable declared within a package is not in the body

Use in anonymous blocks:

BEGIN  Dbms_output. Put_Line (' * * * global_consts.mile_2_kilo | |                       ' km ' ); END;

Called in the subroutine:

CREATE FUNCTION Mtr2yrd (m number) RETURN number Isbegin   * Global_consts.meter_2_yard); END Mtr2yrd;begindbms_output. Put_Line (Mtr2yrd (1)); end;

V. View the information for a package in a data dictionary

View Baotou Information:

SELECT Text   ' comm_pkg ' ' Package';     

To view the package body information:

SELECT Text   ' comm_pkg ' ' PackageBODY';     

Six, the advantages of using the package

1. modularity: Package-related structures

2. Easier maintenance: Combine the relevant logic functions together

3. Make the application design easier: The compilation of the Baotou and the package is carried out separately

4. Hide information:

For an app, only the declaration part is visible

Private parts of the package are hidden and cannot be accessed by the application

All code in the package body is hidden

5. Improved performance:

When the package is first referenced, all content inside the package is loaded into memory

For all users, copy only once in memory

Simplifies dependencies

6. Overloading: Multiple sub-programs with the same name

Vii. Deleting a package

Use the following syntax to remove the header and package body:

Oracle Package Overview (i) "Weber"