PL/SQL - Packages

A Package is a schema object that groups logically related PL/SQL types, items and subprograms like procedures and functions. Package can hold other constructs too, such as exceptions, variables, cursors and type declarations.

Package have two parts:

• Package specification
• Package body

Advantages of packages:

• Modularity: packages let us encapsulate logically related types, items  and subprogrmas in a named PL/SQL module.
• Easier application design : when designing an application, initially need to mention only interface information in the package specs, we can define the detailed body code later.
• Information hiding : with packages, we can specify which types , items and subprograms are public or private.
• Better pefformance: when we call a packaged subprogram for the first time, the whole package is loaded into the memory. So, later calls to related subprograms in the package require no disk I/O.
• Added functionality: packaged public variables and cursors persist for the duration of a session so, they cab be shared by all the subprograms that execute in the environment.

Package Specification:

The package specification tells a user of the package what it cam do rather than how it will do it. The spec contains only the header of the program units rather any executable code. Its kind of like a declaration section for program units.

Syntax:

CREATE OR REPLACE PACKAGE package_name

AS

Program1_header;

Program2_header;

Program3_header;

END package_name;

/

Example:

CREATE OR REPLACE PACKAGE CUSTOMER_PACKAGE

AS

PROCEDURE ADD_CUSTOMER

(

c_id IN NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

);

PROCEDURE DISPLAY_NAMES;

PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

);

PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

);

FUNCTION get_names

(

custid IN NUMBER

)

RETURN SYS_REFCURSOR;

FUNCTION find_salescount

(

p_sales_date IN date

)

RETURN NUMBER;

END CUSTOMER_PACKAGE;

Package Body:

The package body contains the program unit bodies - the executable statements that correspond with the headers in the package specification. 

Syntax:

CREATE OR REPLACE PACKAGE BODY package_name

AS

-- complete subprogram code

program1_code;

program2_code;

program3_code;

END package_name;

/

Example:

CREATE OR REPLACE PACKAGE BODY customer_package

AS

PROCEDURE ADD_CUSTOMER

(

c_id IN OUT NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

SELECT COUNT(1) INTO c_id FROM CUSTOMER;

END ADD_CUSTOMER;

PROCEDURE DISPLAY_NAMES

IS

C_REC SYS_REFCURSOR;

FNAME VARCHAR2(50);

LNAME VARCHAR2(50);

BEGIN

C_REC := GET_NAMES(10);

LOOP

FETCH C_REC INTO FNAME , LANME;

EXIT WHEN C_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(FNAME);

DBMS_OUTPUT.PUT_LINE(LNAME);

END LOOP;

CLOSE C_REC;

END;

PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

ex_customer_id EXCEPTION;

BEGIN

IF c_id <=0 THEN

RAISE ex_customer_id;

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN ex_customer_id THEN

dbms_output.put_line('ID must be greater than zero!');

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

Update customer set row =customer_in where customer_id =customer_in.customer_id;

COMMIT;

END;

FUNCTION find_salescount

( p_sales_date IN date

) RETURN NUMBER

AS

no_of_sales number := 0;

BEGIN

SELECT count(*) INTO no_of_sales from sales where sales_date = p_sales_date;

RETURN no_of_sales;

END find_salescount;

FUNCTION get_names

(

custid IN NUMBER

)

RETURN SYS_REFCURSOR;

IS

l_return SYS_REFCURSOR;

BEGIN

OPEN l_return FOR

SELECT first_name, last_name FROM customer WHERE customer_id = custid;

RETURN l_return;

END get_names;

END customer_package;

How to execute sub programs present in the packages:

execute <package_name>.<resource_name>

Example:

execute.customer_package.display_names;

execute customer_package.get_customer(16);

PL/SQL - Exceptions

Exceptions:

An error condition during a program excution is called an exception in PL/SQL. We can catch such errors using EXCEPTION block in the program and an appropriate action is taken against the error condition.

There are two types of exceptions:

• System defined exceptions
• User defined exceptions

Syntax:

DECLARE

<declarations section>

BEGIN

<executable commands>

EXCEPTION

WHEN exception1 THEN

exception1-handling-statements

WHEN exception2 THEN

exception2-handling-statements

WHEN exception3 THEN

exception3-handling-statements

.......

WHEN others THEN

exception-handling-statements

END;

Exceptions example:

Fetch customer details based on customer id. There are 3 possibilities to get the responses.

1. Successfully fetched 1 customer 

2. Error with no customer 

3. Many customers records found.

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

BEGIN

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

Run the procedure:

Execute get_customer(10);

User defined exceptions:

PL/SQL allows us to define our own exceptions according to the need of our program. A user defined exception must be declared and then raised explicitly, using either a RAISE.

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

ex_customer_id EXCEPTION;

BEGIN

IF c_id <=0 THEN

RAISE ex_customer_id;

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN ex_customer_id THEN

dbms_output.put_line('ID must be greater than zero!');

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

System defined Exception List:

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system dependent limit. 

PL/SQL declares predefined exceptions globally in package STANDARD  which defines the PL/SQL environment. So we need not to declare them again and just to reuse them.

Few of them are below:

ZERO_DIVIDE : when your program attempts to divide a number by zero

VALUE_ERROR : when an arithmatic, conversion, truncation or size-constraint error occurs.

STORAGE_ERROR: PL/SQL runs out of memory ot memory has been corrupted.

ROWTYPE_MISMATCH : the host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types.

PL/SQL - Different types of Blocks

Anonymous Blocks:

An anonymous block is a PL/SQL block that has no name attached to it. They must be generated and utilized in the same session because they will not be saved as database objects on the server. 

If using anonymous blockes were the only way we could organize our statements, it would be hard to use PL/SQL to build robust, large and complex application. Instead, PL/SQL supports the definition of named blocks of code called procedures and Functions etc.

Example of anonymous block:

DECLARE

c_id number:=10;

c_name varchar2(50);

c_addr varchar2(50);

BEGIN

SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;

DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

END;

/

What are Procedures?

A procedure is a group of PL/SQL statements that you can call by name. It can accepts values as input, process the data and return output if required.

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name

(parameter1 MODE DATATYPE [ DEFAULT EXPTESSION] , parameter2 MODE DATATYPE [ DEFAULT EXPTESSION], ...)

AS

[ variable1 DATATYPE;variable2 DATATYPE; ...]

BEGIN

executable_statements

[ EXCEPTION

WHEN

exception_name

THEN

executable_statements ]

END;

/

MODE:

Mode is usually one of the following : IN , OUT, or IN OUT.

IN: the caller supplies the value of the parameter.

OUT: the Procedure sets the value of the parameter  and the calling program can read it as response.

IN OUT: It means that the calling program may pass the argument and the stored procedure can modifiy the INOUT parameter and pass the new value back to the calling program.

Creating a Procedure:

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER

(

c_id IN NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

END ADD_CUSTOMER;

/

Output: Procedure ADD_CUSTOMER compiled. >> this means we just saved this code in the server database.

Calling a Procedure:

Way1:

BEGIN

ADD_CUSTOMER(1,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE);

END;

Way2: order of parameters are not important.

BEGIN

ADD_CUSTOMER

(

c_id => 1,

c_fname => 'Sri',

c_lname => 'Das',

c_addr => '12 AE salt lake kolkata',

c_date_added => SYSDATE

);

END;

Procedure with OUT mode:

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER

(

c_id IN NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE,

total_count OUT NUMBER

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

SELECT COUNT(1) INTO total_count FROM CUSTOMER;

END ADD_CUSTOMER;

/

DECLARE

tcount NUMBER(10);

BEGIN

ADD_CUSTOMER(1,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE, tcount);

dbms_output.put_line('Total Records: ' || tcount);

END;

Procedure with IN OUT mode:

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER

(

c_id IN OUT NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

SELECT COUNT(1) INTO c_id FROM CUSTOMER;

END ADD_CUSTOMER;

/

DECLARE

tcount NUMBER(10):=120;

BEGIN

ADD_CUSTOMER(tcount,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE,);

dbms_output.put_line('Total Records: ' || tcount);

END;

What are functions?

• A stored function also called a user function or user defined function is a set of PL/SQL statements you can call by name.
• Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name

(parameter1 MODE DATATYPE [ DEFAULT EXPTESSION] , parameter2 MODE DATATYPE [ DEFAULT EXPTESSION], ...)

RETURN DATATYPE

AS

[ variable1 DATATYPE;variable2 DATATYPE; ...]

BEGIN

executable_statements

RETURN expression;

[ EXCEPTION

WHEN

exception_name

THEN

executable_statements ]

END;

/

Example:

CREATE OR REPLACE FUNCTION find_salescount

( p_sales_date IN date

) RETURN NUMBER

AS

no_of_sales number := 0;

BEGIN

SELECT count(*) INTO no_of_sales from sales where sales_date = p_sales_date;

RETURN no_of_sales;

END find_salescount;

Calling a function

Way1:

SELECT find_salescount(to_date('01-jan-2023','dd-mon-yyyy')) from dual

Way2:

DECLARE

salescount number := 0;

BEGIN

salescount := find_salescount(to_date('01-jan-2023','dd-mon-yyyy'));

dbms_output.put_line(salescount);

END;

PL/SQL - Reading data from databse and Inserting data into database

Reading data from database:

We can use the "SELECT INTO" statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list , there must be a corresponding, type compatible variable in the INTO list.

Usecase: fetch the customer name and address from customer table for customer id =10

DECLARE

c_id number:=10;

c_name varchar2(50);

c_addr varchar2(50);

BEGIN

SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;

DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

END;

/

Whats is %type:

DECLARE

c_id customer.customer_id%type:=10;

c_name customer.first_name%type;

c_addr customer.address%type;

BEGIN

SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;

DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

END;

/

Inserting data into Database:

We use "INSERT INTO" statement to insert data into database table.

DECLARE

c_id customer.customer_id%type :=1;

c_fname customer.first_name%type := 'Sri';

c_lname customer.last_name%type :='Das';

c_addr customer.address%type := ' 22 AE block Salt Lake kolkata';

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address) VALUES (c_id,c_fname,c_lname,c_address);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

END;

/

Note: we can also perform update a record and delete a record in the database table.

PL/SQL Links

PL/SQL topics covered:

1. PL/SQL basics
   
2. PLSQL - Fetch data from database | Insert data into a database table
   
3. PLSQL - Different types of Blocks | Create a Procedure | Calling a procedure | Create a Function | Calling a function | Anonymous block
   
4. PLSQL - Exceptions
   
5. PL/SQL - Packages
   
6. PL/SQL - Working with Records.
   
7. PL/SQL - Cursors
   
8. PL/SQL - Collections
   
9. PL/SQL - Triggers
   
10. plsql-bulk-processing
    
11. PL/SQL - Large Objects
    

PL/SQL Basic

What is PLSQL:

• PL/SQL is the Oracle Procedural Language extension of SQL.
• A PL/SQL program can have both SQL statements and Ptocedural statements.
• In the PL/SQL program, the SQL is a popular language for both querying and updating data in a relational database management systems(RDBMS) while the procedural statements are used to process individual piece of data and control the program flow.
• PL/SQL is a highly structured and readable language.
• PL/SQL is a standard and portable language for Oracle Database development. If you develop a program that executes on an Oracle database, you can quickly move it to another compatablr Oracle database without any changes.

**Procedural refers to a series of ordered steps that the computer should follow to produce a result.

Why SQL alone is not enough?

• SQL does not support looping and condition statements etc.
• In SQL, you can not execute more than one statements at a time, so running more than one statements increases the network traffic.
• SQL always gives system defined error messages   when user perform any wrong transactions, no place for custom exceptions.
• SQL does not support procedural language features such as code reusability and modularity and some other features of OOPS.

To overcome all the above limitation of SQL, We use PL/SQL.

PL/SQL Advantages:

PL/SQL is a poweful, completely portable, high-performance transaction processing language that offers the following advantages:

• Support for SQL.
• Support for object oriented programming
• Better performance
• Higher Productivity
• Full Portability
• Tight integration with Oracle
• Tight Security

PL/SQL Structure:

PL/SQL is a block structured language, meaning that PL/SQL programs are divided and written in logical blocks of code.

Each block consists of three sub parts:

DECLARE

<declaration section>

BEGIN

<executable commands(s)>

EXCEPTION

<exception handling>

END;

First example:

DECLARE

BEGIN

dbms_output.put_line('Welcome to the world');

END;

** to view the output, SQL developer >> View tab >> Dbms Output >> click on plus button >> select the database where you will run the plsql code >> ok >> now execute the code and see the output.

Declaring Variable:

DECLARE section is used to declare all our variables.

Syntax:

<Variable Name> <Type>;

Example:

ordernumber number := 1001; 

Code:

DECLARE

ordernumber number :=1001;

orderid number default 1002;

customername varchar2(20):='John';

BEGIN

dbms_output.put_line(ordernumber);

dbms_output.put_line(orderid);

dbms_output.put_line(customername);

END;

output: 

1001

1002

John

**This is we declare a variable and assign 1001 value to it. We can also assign a value using default keyword like below

Orderid number default 1002;

** if we use constant keyword with a variable that means that variable cant be used as an assignment target.

ordernumber constant number:=1001;

Comments in PL/SQL:

Single line comments:

-- variable creation

multi line comments:

/* this is a multi line comment

We can write about what we are doing in the program

*/

Scope of variables:

Global variable vs Local variable

• The variable which is declared under the main block and is visible within the entire program is called Global variable.
• The variable which is declared under the sub block and is only visible within the sub block is called Local variable.

Example:

DECLARE

-- Global Variable

num1 number:=20;

BEGIN

dbms_output.put_line(' Outer variable num1: ' || num1)

DECLARE

-- Local Variable

num2 number:=10;

BEGIN

dbms_output.put_line(' Inner variable num1: ' || num1)

dbms_output.put_line(' Inner variable num2: ' || num2)

END;

END;

Output:

Outer variable num1: 20

Inner variable num1: 20

Inner variable: 10

IF then ELSE statement:

This is needed when we want output or action based on some condition. There are folowing 3 types of decision making statements:

Type1:

If <condition> 

then <action>

end if;

Case: if total amount > 100, give 10% discount.

DECLARE

total_amount number:= 102;

discount number:=0;

BEGIN

If total_amount > 100

Then discount := total_amount * .1;

End if;

dbms_output.put_line(discount);

end;

Output: 10.2

Type2:

If <condition>

Then <action>

else

<action>

end if;

Case : if total_amount > 100, give 10% else give 5 % discount

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

If total_amount > 100

Then discount := total_amount * .1;

Else

Discount:= total_amount * .05;

End if;

dbms_output.put_line(discount);

end;

Output : 5

type3:

If <condition>

Then <action>

elsif <condition>

Then <action>

Else

<action>

end if;

Case: if total_amount >200, give 20%, if total_amount >=100 and total_amount <=200 give 10%, else give 5% discount.

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

If total_amount > 200

Then discount := total_amount * .2;

Elsif total_amount >=100 and total_amount <=200

Then discount:= total_amount * .1;

Else

discount :=total_amount*.05;

End if;

dbms_output.put_line(discount);

end;

Output: 10

CASE statement:

CASE selector 
   WHEN 'value1' THEN S1; 
   WHEN 'value2' THEN S2; 
   WHEN 'value3' THEN S3; 
   ... 
   ELSE Sn;  -- default case 
END CASE;

Case: if total_amount >200, give 20%, if total_amount >=100 and total_amount <=200 give 10%, else give 5% discount.

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

CASE

When total_amount > 200

Then discount := total_amount * .2;

When total_amount >=100 and total_amount <=200

Then discount:= total_amount * .1;

Else

discount :=total_amount*.05;

End CASE;

dbms_output.put_line(discount);

end;

Output: 10

WHILE Loop:

While loop is used to execute statements till a particular condition is met.

DECLARE

counter numer(2) := 10;

BEGIN

WHILE counter < 20

LOOP

dbms_output.put_line('value of counter: ' || counter);

counter := counter + 1;

END LOOP;

END;

/

Output:

value of counter : 10

value of counter : 11

value of counter : 12

value of counter : 13

value of counter : 14

value of counter : 15

value of counter : 16

value of counter : 17

value of counter : 18

value of counter : 19

FOR LOOP:

For loop allows us to execute code repeatedly for a fixed number of times.

DECLARE

counter numer(2) := 10;

BEGIN

FOR counter IN 10..20

LOOP

dbms_output.put_line('value of counter: ' || counter);

END LOOP;

END;

/

Output:

value of counter : 10

value of counter : 11

value of counter : 12

value of counter : 13

value of counter : 14

value of counter : 15

value of counter : 16

Value of counter : 17

value of counter : 18

value of counter : 19

value of counter : 20

To reverse the loop order:

DECLARE

counter numer(2) := 10;

BEGIN

FOR counter IN REVERSE 10..20

LOOP

dbms_output.put_line('value of counter: ' || counter);

END LOOP;

END;

/

OIC - Oracle Integration Gen 3 | what's new for the Oracle Integration 3

Reference:

https://docs.oracle.com/en/cloud/paas/application-integration/whats-new/index.html