In this part, we will examine the Packages in PL/SQL. Bundles are outline protests that bunches sensibly related PL/SQL types, factors, and subprograms.
A bundle will have two compulsory parts −
- Bundle determination
- Bundle body or definition
Package Specification
The determination is the interface to the bundle. It simply DECLARES the sorts, factors, constants, exemptions, cursors, and subprograms that can be referred to from outside the bundle. As such, it contains all data about the substance of the bundle, yet bars the code for the subprograms.
All items put in the determination are called public articles. Any subprogram not in the bundle determination but rather coded in the bundle body is known as a private item.
The accompanying code bit shows a bundle determination having a solitary methodology. You can have numerous worldwide factors characterized and various systems or capacities inside a bundle.
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Package created.
Package Body
The bundle body has the codes for different strategies proclaimed in the bundle detail and other private presentations, which are stowed away from the code outside the bundle.
The CREATE PACKAGE BODY Statement is utilized for making the bundle body. The accompanying code scrap shows the bundle body statement for the cust_sal bundle made previously. I expected that we as of now have CUSTOMERS table made in our information base as referenced in the PL/SQL - Variables part.
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;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Package body created.
Using the Package Elements
The bundle components (factors, methods or capacities) are gotten to with the accompanying linguistic structure −
package_name.element_name;
Consider, we as of now have made the above bundle in our information base blueprint, the accompanying system utilizes the find_sal strategy for the cust_sal bundle −
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
At the point when the above code is executed at the SQL brief, it prompts to enter the client ID and when you enter an ID, it shows the relating pay as follows −
Enter value for cc_id: 1
Salary: 3000
PL/SQL procedure successfully completed.
Example
The accompanying project gives a more complete bundle. We will utilize the CUSTOMERS table put away in our information base with the accompanying 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.No.ame%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;
/
At the point when the above code is executed at the SQL brief, it makes the above bundle and shows the accompanying outcome −
Package created.
Creating the Package Body
CREATE OR REPLACE PACKAGE BODY c_package AS
PROCEDURE addCustomer(c_id customers.id%type,
c_name customerS.No.ame%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;
/
The above model utilizes the settled table. We will examine the idea of settled table in the following section.
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Package body created.
Using The Package
The accompanying system utilizes the techniques pronounced and characterized in the bundle 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;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
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