Plsql 简明教程

PL/SQL - Packages

在本章中，我们将讨论 PL/SQL 中的包。包是将具有逻辑关连性的 PL/SQL 类型、变量和子程序进行分组的模式对象。

In this chapter, we will discuss the Packages in PL/SQL. Packages are schema objects that groups logically related PL/SQL types, variables, and subprograms.

一个包会有两个强制性部分：

A package will have two mandatory parts −

  1. Package specification

  2. Package body or definition

Package Specification

规范是对包的界面。它只是 DECLARES 可以从包外部进行引用的类型、变量、常量、异常、游标和子程序。换句话说，它包含有关包内容的所有信息，但不包括子程序的代码。

The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.

放在规范中的所有对象被称为 public 对象。未在包规范中但已在包正文中编码的任何子程序都被称为 private 对象。

All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called a private object.

以下代码段显示了一个具有单个过程的包规范。你可以在包中定义许多全局变量，以及多个过程或函数。

The following code snippet shows a package specification having a single procedure. You can have many global variables defined and multiple procedures or functions inside a package.

CREATE PACKAGE cust_sal AS
   PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Package created.

Package Body

包正文包含对包规范中声明的各种方法的代码，以及其他对包外部代码隐藏的私有声明。

The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from the code outside the package.

CREATE PACKAGE BODY 语句用于创建包正文。以下代码段显示了为上述所创建的 cust_sal 包的包正文声明。我假设我们已在数据库中创建 CUSTOMERS 表，如 PL/SQL - Variables 章所述。

The CREATE PACKAGE BODY Statement is used for creating the package body. The following code snippet shows the package body declaration for the cust_sal package created above. I assumed that we already have CUSTOMERS table created in our database as mentioned in the PL/SQL - Variables chapter.

CREATE OR REPLACE PACKAGE BODY cust_sal AS

   PROCEDURE find_sal(c_id customers.id%TYPE) IS
   c_sal customers.salary%TYPE;
   BEGIN
      SELECT salary INTO c_sal
      FROM customers
      WHERE id = c_id;
      dbms_output.put_line('Salary: '|| c_sal);
   END find_sal;
END cust_sal;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Package body created.

Using the Package Elements

包元素（变量、过程或函数）可使用以下语法进行访问：

The package elements (variables, procedures or functions) are accessed with the following syntax −

package_name.element_name;

考虑一下这一点，我们已在数据库架构中创建了上述包，以下程序使用 cust_sal 包的 find_sal 方法：

Consider, we already have created the above package in our database schema, the following program uses the find_sal method of the cust_sal package −

DECLARE
   code customers.id%type := &cc_id;
BEGIN
   cust_sal.find_sal(code);
END;
/

当在 SQL 提示符下执行上述代码时，它会提示输入客户 ID，并在输入 ID 时，它会显示相应的工资，如下所示：

When the above code is executed at the SQL prompt, it prompts to enter the customer ID and when you enter an ID, it displays the corresponding salary as follows −

Enter value for cc_id: 1
Salary: 3000

PL/SQL procedure successfully completed.

Example

以下程序提供了一个更完整的包。我们将使用存储在我们的数据库中的，具有以下记录的 CUSTOMERS 表：

The following program provides a more complete package. We will use the CUSTOMERS table stored in our database with the following records −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 |
|  2 | Khilan   |  25 | Delhi     |  3000.00 |
|  3 | kaushik  |  23 | Kota      |  3000.00 |
|  4 | Chaitali |  25 | Mumbai    |  7500.00 |
|  5 | Hardik   |  27 | Bhopal    |  9500.00 |
|  6 | Komal    |  22 | MP        |  5500.00 |
+----+----------+-----+-----------+----------+

The Package Specification

CREATE OR REPLACE PACKAGE c_package AS
   -- Adds a customer
   PROCEDURE addCustomer(c_id   customers.id%type,
   c_name customers.Name%type,
   c_age  customers.age%type,
   c_addr customers.address%type,
   c_sal  customers.salary%type);

   -- Removes a customer
   PROCEDURE delCustomer(c_id  customers.id%TYPE);
   --Lists all customers
   PROCEDURE listCustomer;

END c_package;
/

当在 SQL 提示符下执行上述代码时，它将创建上述包并显示以下结果：

When the above code is executed at the SQL prompt, it creates the above package and displays the following result −

Package created.

Creating the Package Body

CREATE OR REPLACE PACKAGE BODY c_package AS
   PROCEDURE addCustomer(c_id  customers.id%type,
      c_name customers.Name%type,
      c_age  customers.age%type,
      c_addr  customers.address%type,
      c_sal   customers.salary%type)
   IS
   BEGIN
      INSERT INTO customers (id,name,age,address,salary)
         VALUES(c_id, c_name, c_age, c_addr, c_sal);
   END addCustomer;

   PROCEDURE delCustomer(c_id   customers.id%type) IS
   BEGIN
      DELETE FROM customers
      WHERE id = c_id;
   END delCustomer;

   PROCEDURE listCustomer IS
   CURSOR c_customers is
      SELECT  name FROM customers;
   TYPE c_list is TABLE OF customers.Name%type;
   name_list c_list := c_list();
   counter integer :=0;
   BEGIN
      FOR n IN c_customers LOOP
      counter := counter +1;
      name_list.extend;
      name_list(counter) := n.name;
      dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
      END LOOP;
   END listCustomer;

END c_package;
/

上述示例使用了 nested table 。我们将在下一章中讨论嵌套表的概念。

The above example makes use of the nested table. We will discuss the concept of nested table in the next chapter.

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Package body created.

Using The Package

以下程序使用在软件包 c_package 中定义的 declared 和 defined 方法。

The following program uses the methods declared and defined in the package c_package.

DECLARE
   code customers.id%type:= 8;
BEGIN
   c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
   c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
   c_package.listcustomer;
   c_package.delcustomer(code);
   c_package.listcustomer;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish

PL/SQL procedure successfully completed