(13) PL/SQL Package

The PL/SQL package is a group of logically related PL/SQL types, variables, and subroutine pattern objects.
The package will have two mandatory sections:
1. Definition of packaging specification
2. Package body or definition

I. Definition OF PACKAGING specification
A specification is an interface to a package. It simply declares the type, variables, constants, exceptions, cursors, and subroutines that can be referenced externally from the wrapper. In other words, it contains all the information about the contents of the package, but does not include the code for the subroutine.
All objects placed in a specification are called public objects. Any subroutine does not have a package definition in the wrapper body, but the encoding is called a private object.

The following code snippet shows a single package specification definition. A global variable and multiple programs or functions that can be defined in a package.
CREATE Package Cust_sal as
PROCEDURE find_sal (c_id customers.id%type);
END cust_sal;
/
When the above code is executed at the SQL prompt, it produces the following results:
Package created.

second, the package body
The package body has been declared in the package definition and other private declarations of the various methods, which are hidden from code outside the package code.
The Create package body statement is used for creating packages. The following code snippet shows the Cust_sal package created above the package body declaration.
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;
/
when the above code is executed at the SQL prompt, it produces the following results:
Package body created.

Third, the use of package elements
The syntax for accessing package elements (variables, procedures, or functions) is as follows:
Package_name.element_name;
We have created the package in the database schema above, the following program is the Find_sal method that uses the Cust_sal package:
DECLARE
Code Customers.id%type: = &cc_id;
BEGIN
Cust_sal.find_sal (code);
END;
/
When the above code executes at the SQL prompt, it prompts for the customer ID, and when an ID is entered, it displays the corresponding compensation as follows:
Enter value for Cc_id:1
salary:3000

PL/SQL procedure successfully completed.

Iv. examples

//The following program provides a more complete scenario. We will use the following records from the Customers table stored in the database: Select* fromcustomers;+----+----------+-----+-----------+----------+| ID | NAME | Age | ADDRESS | SALARY |+----+----------+-----+-----------+----------+|1| Ramesh | +| Ahmedabad |3000.00||2| Khilan | -| Delhi |3000.00||3| Kaushik | at| Kota |3000.00||4| Chaitali | -| Mumbai |7500.00||5| Hardik | -| Bhopal |9500.00||6| Komal | A| MP |5500.00|+----+----------+-----+-----------+----------+1. Package specification Definition: CREATE OR REPLACE packages 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;/when the above code is executed at the SQL prompt, it creates the above package and displays the following result: Packages created.2. Create the body part of the package: creating OR REPLACE the Packages 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 isSELECT name from customers; TYPE c_list isTABLE 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;/when the above code is executed at the SQL prompt, it produces the following result: Package body created.3. Using packages: The following program uses claims and defines methods in package c_package. DECLARE Code customers.id%type:=8; BEGIN C_package.addcustomer (7,'Rajnish', -,'Chennai',3500); C_package.addcustomer (8,'Subham', +,'Delhi',7500);      C_package.listcustomer;      C_package.delcustomer (code); C_package.listcustomer; END;/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): Komalcustomer (7): Rajnishcustomer (8): Subhamcustomer (1): Ramesh Customer (2): Khilan Customer (3): Kaushik Customer (4): Chaitali Customer (5): Hardik Customer (6): Komalcustomer (7): RAJNISHPL/sql procedure successfully completed

(13) PL/SQL Package