Oracle database Objects-Package package usage

A package is an attribute of Oracle Pl/sql, which is like a container or a namespace that combines a variety of logically related types, constants, variables, exceptions, and subroutines. Provides a good organizational unit when writing large, complex applications for developers.

Once the package has been defined, the application can access a variety of functional units through the packet, without worrying about the loose code of the program due to too many fragmented subroutines.

Simplify application design, improve application performance, realize information hiding, subroutine overload.

1. Does Oracle's package have other functions (benefits) Besides placing stored procedures in a pile of children?

Don't you think it's important to categorize stored procedures, and different package stored procedures can have duplicate names.

Package can not only classify stored procedures, but also define common variables/types in package, which facilitates programming and reduces the overhead of server compilation.

2. How to add existing stored procedures to the package?

Copy and pasty, but the call will take the package name.

3, in addition to using SQL Plus, is there any tool to do package?

There are also convenient third-party tools, but they have to find their own.

Use a third party tool, such as SQL Navigator.

4, using SQL Plus to compile package, is every time compiling package all the stored procedures?

is a package is also a kind of named Pl/sql block, and stored procedures, functions, are loaded into memory when the database is started. The size of the cost is difficult to judge, because you do not use the package, but to complete the package function, or to use Pl/sql to complete, the server has the same overhead. By contrast, there is less overhead in parsing and interpreting the SQL with fewer packets.

"The process is generally not more than 20 lines" that I rarely encountered. The key to the use of the process is to see if it is possible to define a reusable sub process, and the process of using the child is not inefficient.

What a package does: A package can put any statement that appears in a block declaration (procedure, functions, cursors, cursors, types, and variables are placed in a package, which is equivalent to a container. The benefit of putting a declaration statement into a package is that the user can reference it from another pl/sql block, so the package provides the whole variable for pl/sql.

The package is divided into two parts: Baotou and the package body.

How do I create a package?

1) Baotou:

Syntax format:

CREATE OR REPLACE packagepackage_name/* Header name * *

Is|as PL/SQL_PACKAGE_SPEC/* Defines procedures, functions, and return types, variables, constants, and data type definitions * *

The definition of Baotou should follow the following guidelines:

1 package element position can be arranged arbitrarily. In the declaration section, however, the object must be declared before the reference.

2 The header can not describe any type of element. For example, Baotou can take only procedure and function description statements without declaring any exceptions and types.

3 any declaration of a procedure or function must be described only by the child program and its parameters, without any description of the code, and the implementation of the code can only appear in the package body. It differs from block declarations, in which the Code of a procedure and a function can appear in the declaration section at the same time.

2. Package Body:

Syntax format:

CREATE OR REPLACE PACKAGE body package_name/* Package name must be the same as the package name of Baotou.

is | As Pl/sql_package_body/* cursors, functions, the specific definition of the process * *

Inclusion is independent from Baotou, the package body can be compiled only after the header has been compiled. The package body has a code snippet with the specific implementation of the subroutine described in the header. In addition, the package body can include additional declarations that have the properties of the package person's entire sentence, but these additional declarations are missing for the header.

A Pl/sql package consists of the following two parts:

• Package Specification: Mainly a package of some definition information, does not contain the specific code implementation part.
• Package Body: The package body is the implementation part of the subroutine declared in the package specification, the content of the package body is not visible to the external application, the package body is like a black box, it is the implementation of the package specification.

1. Basic Grammar of package specification

CREATE [OR REPLACE] PACKAGE package_name
{is | As}
Package_specification
End Package_name;

Example:

--/
CREATE OR REPLACE PACKAGE Pack_fun is
--Defines a function that returns two digits and
FUNCTION Fun_add (num1 in number, num2 in out number) return number;
--Defines a function that returns a difference of two digits
FUNCTION fun_sub (num1 in number, num2 in out number) return number;
--Define a stored procedure to display the 99 multiplication table
PROCEDURE proc_1;
End;
/

2, package Body Basic grammar

CREATE [OR REPLACE] PACKAGE body package_name
{is | As}
Package_body
End Package_name;

If a function in the package body has a modification, it must be recompiled.

--/
CREATE OR REPLACE PACKAGE body pack_fun be
FUNCTION fun_add (num1 in number, num2 in number) return number I S
    tmp number;
  BEGIN
    tmp: = Num1 + num2;
    RE TURN tmp;
  End Fun_add;

FUNCTION fun_sub (num1 in number, num2 in number) return number is
TMP number;
BEGIN
TMP: = num1-num2;
return TMP;
End Fun_sub;

PROCEDURE Proc_1 is
M number: = 0;
BEGIN
For I in 1. 9 LOOP
For J in 1. I LOOP
M: = I * J;
Dbms_output. Put (I | | '*' || J | | '=' || M | | ' ');
End LOOP;
Dbms_output. Put_Line (NULL);
End LOOP;
End Proc_1;

End Pack_fun;
/

3. Functions and stored procedures in the call package

Select Pack_fun.fun_add (9,8) from dual;
Select Pack_fun.fun_sub (9,8) from dual;

--/
BEGIN
Pack_fun.proc_1;
End;
/