色五月丁香六月欧美综合,国产午夜激无码av毛片不卡

一個包將有兩個強制性的部分 -

包規(guī)范/格式
包體或定義

包規(guī)范

規(guī)范是包的接口。它只是聲明可以從包外部引用的類型，變量，常量，異常，游標和子程序。換句話說，它包含有關包的內(nèi)容的所有信息，但不包括子程序的代碼。

所有放置在規(guī)范中的對象被稱為公共對象。任何不在包規(guī)范中但在包體中編碼的子程序稱為私有對象。

以下代碼片段顯示了包含單個過程的包規(guī)范?？梢栽谝粋€包中定義許多全局變量和多個過程或函數(shù)。

SET SERVEROUTPUT ON SIZE 99999;
CREATE PACKAGE cust_sal AS 
   PROCEDURE find_sal(c_id customers.id%type); 
END cust_sal; 
/
SQL

當上面的代碼在SQL提示符下執(zhí)行時，它會產(chǎn)生以下結果 -

包體

包體具有包規(guī)范中聲明的各種方法代碼和其他私有聲明，這些聲明對包之外的代碼是隱藏的。

CREATE PACKAGE BODY語句用于創(chuàng)建包體。以下代碼片段顯示了上面創(chuàng)建cust_sal包的包體聲明。假定已經(jīng)在數(shù)據(jù)庫中創(chuàng)建了CUSTOMERS表，有關customers表的結構和數(shù)據(jù)，可參考以下SQL語句 -

CREATE TABLE CUSTOMERS( 
   ID   INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);  

-- 數(shù)據(jù)
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
SQL

基于上述customers表，創(chuàng)建一個簡單的包體 -

SET SERVEROUTPUT ON SIZE 99999;
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

當上面的代碼在SQL提示符下執(zhí)行時，它會產(chǎn)生以下結果 -

使用包元素

包元素(變量，過程或函數(shù))是用下面的語法來訪問的 -

package_name.element_name;
SQL

考慮一下，假設已經(jīng)在數(shù)據(jù)庫模式中創(chuàng)建了上面的包，下面的程序中需要調(diào)用cust_sal包趾的find_sal方法 -

SET SERVEROUTPUT ON SIZE 9999;
DECLARE 
   code customers.id%type := &cc_id; 
BEGIN 
   cust_sal.find_sal(code); 
END; 
/
SQL

當上述代碼在SQL提示符下執(zhí)行時，它會提示輸入客戶ID，當輸入一個ID時，顯示相應的工資如下 -

示例

以下程序提供了一個更完整的包體。使用存儲在數(shù)據(jù)庫中的CUSTOMERS表和以下記錄 -

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 | 
+----+----------+-----+-----------+----------+
SQL

包規(guī)范

SET SERVEROUTPUT ON SIZE 99999;
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

當上面的代碼在SQL提示符下執(zhí)行時，它會創(chuàng)建上面的包并顯示以下結果 -

創(chuàng)建程序包體

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; 
/
SQL

上面的例子使用了嵌套表，我們將在下一章討論嵌套表的概念。

當上面的代碼在SQL提示符下執(zhí)行時，它會產(chǎn)生以下結果 -

程序包體已創(chuàng)建。
SQL

使用程序包

以下程序使用程序包：c_package 中聲明和定義的方法。

SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   code customers.id%type:= 8; 
BEGIN 
   c_package.addcustomer(7, 'Andy Liu', 25, 'Chennai', 3500); 
   c_package.addcustomer(8, 'Kobe Bryant', 32, 'Delhi', 7500); 
   c_package.listcustomer; 
   c_package.delcustomer(code); 
   c_package.listcustomer; 
END; 
/
SQL

當上面的代碼在SQL提示符下執(zhí)行時，它會產(chǎn)生以下結果 -

Old salary:
New salary: 3500
Salary difference:
Old salary:
New salary: 7500
Salary difference:
Customer(1)Ramesh
Customer(2)Khilan
Customer(3)kaushik
Customer(4)Chaitali
Customer(5)Hardik
Customer(6)Komal
Customer(7)Andy Liu
Customer(8)Kobe Bryant
Customer(1)Ramesh
Customer(2)Khilan
Customer(3)kaushik
Customer(4)Chaitali
Customer(5)Hardik
Customer(6)Komal
Customer(7)Andy Liu

PL/SQL 過程已成功完成。

PL/SQL 包

包規(guī)范

包體

使用包元素