How to use Oracle Middle Pack (PACKAGE)

1. Definition

Package: (1) A collection of related objects that are grouped together, and the package is loaded into memory when any function or stored procedure in the package is invoked.

(2) The subroutine access speed of any function or stored procedure in the package will be greatly accelerated.

(3) The package consists of two parts: Baotou and the package body, describing variables, constants, cursors, and subroutines in the header.

(4) The package body consists of a complete subroutine, a cursor definition.

2. Create Package

Baotou:

CREATE OR REPLACE PACKAGE test_pkg is

PROCEDURE update_sal (e_name varchar2,newsal number);

FUNCTION ann_income (e_name VARCHAR2) return number;

End;

--Defines a function and a process

Inclusion

CREATE OR REPLACE PACKAGE body test_pkg is

PROCEDURE update_sal (e_name varchar2,newsal number)

Is

BEGIN

UPDATE emp1 SET sal=newsal WHERE ename=e_name;

End;

FUNCTION ann_income (E_name VARCHAR2)

Return number is

Annsal number;

BEGIN

SELECT SAL*12+NVL (comm,0) into Annsal from EMP1

WHERE Ename=e_name;

return annsal;

End;

End;

--Implement the above functions and procedures

3. Call Package

The invocation format for common elements within the package is: package name, element name.

Call to a procedure inside a package

sql> exec test_pkg.update_sal (' SCOTT ', 1200);

To call a function within a package

DECLARE

V_annsal number (7,2);

BEGIN

V_annsal:=test_pkg.ann_income (' SCOTT ');

Dbms_output.put_line (' Annual salary is: ' | | V_annsal);

End;

4, delete the package

We can drop the PACKAGE command to delete unwanted packages as follows:

DROP PACKAGE [Body[user.] Package_name;

Author: 51cto Oracle small-mixed son

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/