Use of packages in Oracle

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