PLSQL

PLSQL stands for “Procedural Language extensions to SQL“, and is an extension of SQL that is used in Oracle. PLSQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to SQL.

• Oracle is a relational database technology developed by Oracle.
• Integrating Programming Features with SQL.
• Programming Language +SQL=PL/SQL
• PL/SQL submits the request as a Block.

 

Recognize PL/SQL  Block

PL/SQL Block consists of

• SQL Statements
• PL features like
  • Conditional Statements
  • Loop Constructs
• Additional features like
  • Functions
  • Stored procedures
  • Triggers
  • Exceptions

Why PL/SQL?

• Connectivity exists between consecutive requests in PL/SQL.
• Logical implementation of user’s requirements is made possible with PL/SQL.

 

Where PL/SQL Stands?

 

 

PL/SQL Block

 
[Declare
Variable declarations  (if any) ]
BEGIN
SQL/PL-SQL Statements
[Exception]
….
END;
 

Anonymous Block

• PL/SQL block without any name for identification
• It has to be brought to the server’s memory every time it is required.
• It can’t be accessed/referred neither by oracle nor by user for execution.
• Has to be compiled every time we need that block of statements.
• Both compilation and execution are done at the server end

 

Sub-program

• A subprogramis a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the ‘Modular design’. A subprogram can be invoked by another subprogram or program which is called the calling program.
• A subprogram can be created:
  • At schema level
  • Inside a package
  • Inside a PL/SQL block
• A schema level subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE or CREATE FUNCTION statement. It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement.
• A subprogram created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement.
• PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms:
  • Functions: these subprograms return a single value, mainly used to compute and return a value.
  • Procedures: these subprograms do not return a value directly, mainly used to perform an action.

 

Stored Procedures

• Named PL/SQL block
• Stored as database object like tables, views, indexes, sequences, synonym.
• Stored in the ready to executable form.
• Can be referred by other PL/SQL block.
• One copy is brought to the buffer for sharing by multiple users.

 

Creating a Procedure

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, …])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
 

Procedure -Example

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line(‘Hello World!’);
END;
/
 

Executing a Standalone Procedure

• A standalone procedure can be called in two ways:
• Using the EXECUTE keyword
• Calling the name of the procedure from a PL/SQL block

EXECUTE greetings;
  BEGIN
     greetings;
  END;
 

Different Modes of Parameters (IN,OUT,IN OUT)

• IN Parameter
  • To send input values to the sub program

• OUT Parameter
  • Carry output values to the calling environment

• IN OUT Parameter
  • Used in both the directions to send and carry from the sub block to the calling block.

 

IN, OUT Parameter Example

DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(‘ Minimum of (23, 45) : ‘ || c);
END;
/
Result:
Minimum of (23, 45) : 23
PL/SQL procedure successfully completed.
 

IN & OUT Mode

DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 5;
squareNum(a);
dbms_output.put_line(‘ Square of (5): ‘ || a);
END;
/
Result :
Square of (5): 25
PL/SQL procedure successfully completed.
 

FUNCTIONS

• Named and stored PL/SQL Block
• Should return at least one value
• Used like Built-in functions
• Invoked as part of expression.
• A PL/SQL function is same as a procedure except that it returns a value.

Creating a Function

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, …])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
 

Return Statement

• RETURN data_type
  • Only type has to be specified and size specification is not allowed.
  • Can be scalar data type and composite data types.
  • Can return only one value
  • Should have at least one return statement in the PL/SQL block
• RETURN variable;

Procedure  Vs Function

 

 

Packages

PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.

• A package will have two mandatory parts:
  • Package specification
  • Package body or definition

Package Specification

• 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.
• 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.

Package Body

• The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package.
•   The CREATE PACKAGE BODY Statement is used for creating the package body.

Triggers

• The PL/SQL block which gets fired automatically whenever some specified event happens.
• The chain of actions to be performed can be written in trigger body for continual execution.
• Will be invoked by oracle automatically.

Components of Triggers

• Trigger Timing
  • When to fire trigger after the request or before the request.
• Triggering Event
  • For what event the trigger is to be fired
  • Is it insert/update/delete
• Table Name
  • Name of the table for which the trigger is
• Trigger Type
  • Statement level- Once per request
  • Row level- for each and every row got affected trigger is executed
• When clause
  • Prepares selected rows for trigger action
  • Allowed only for row level trigger
• Trigger body
  • Explains what to be done in the trigger end.

 

TRIGGER- Syntax

CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
— variable declarations
BEGIN
— trigger code
EXCEPTION
WHEN …
— exception handling
END;
 

When and Why Cursors ?

• Fetching more than one record is not possible with simple variables
• Can be done through cursors
• Cursor is the memory area with pointers
• Temporary memory area used to store data getting referred during
  • PL/SQL fetch operation
  • And DML statements
• There are two types of cursors:
  • Implicit cursors
  • Explicit cursors

Implicit Cursors

• Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.
• Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

Cursor Attributes – Implicit

• SQL%ROWCOUNT
  • Number of rows affected by the most recent SQL statement
• SQL%FOUND
  • TRUE if the most recent SQL found any row.
  • Set to TRUE if SQL%ROWCOUNT<>0
• SQL%NOTFOUND
  • TRUE if the most recent SQL didn’t find any row
  • Set to TRUE if SQL%ROWCOUNT=0 or SQL%FOUND is FALSE
• SQL%ISOPEN
  • Always FALSE because control will be transferred to the user after closing the implicit cursor.

Explicit Cursors

• Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.
• The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

• Declaring the cursor for initializing in the memory
  • Declaring the cursor defines the cursor with a name and the associated SELECT statement.
• Opening the cursor for allocating memory
  • Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it
• Fetching the cursor for retrieving data
  • Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows
• Closing the cursor to release allocated memory.

 

Cursor Example

DECLARE
CURSOR c_emp_detail IS
SELECT employee_id,first_name,last_name,salary
FROM employees;
rec_emp_detail c_emp_detail%ROWTYPE;
/* A cursor based record is based on elements of pre-Defined cursor.
A cursor based record can be only declared after its corresponding
cursor, else an error occurs.*/
BEGIN
OPEN c_emp_detail;
LOOP
FETCH c_emp_detail INTO rec_emp_detail;
EXIT WHEN c_emp_detail%NOTFOUND; — cursor attribute to exit when no rows found to fetch.
DBMS_OUTPUT.PUT_LINE(‘Employees Details : ‘||’ ‘||rec_emp_detail.employee_id
||’ ‘||rec_emp_detail.first_name||’ ‘||rec_emp_detail.last_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total number of rows : ‘||c_emp_detail%ROWCOUNT);
— cursor attribute to find the total number of rows executed.
CLOSE c_emp_detail;
END;
 

Cursor – For Loop

declare
cursor emp_Cursor is
select empno,ename,basal,deptno from emp;
begin
/* While using cursor for loop no need to
open, fetch the data and close the cursor
Everything will be implicitly done by the pl/sql
engine */
for ret_emp_rec in emp_Cursor
loop
/* Using Cursor for loop variable we can fetch
the data by specifying the column value */
dbms_output.put_line(ret_emp_rec.empno);
dbms_output.put_line(ret_emp_rec.basal);
end loop;
end;
 

Parameterized Cursor

DECLARE
CURSOR  par_cur(p_deptno emp.deptno%type) IS
SELECT  empno,ename  FROM  emp
WHERE  deptno=p_deptno;
 
v_DeptNo emp.deptno%type := &dno;
emp_rec emp%rowtype;
BEGIN
open par_cur(v_DeptNo)
loop
fetch par_cur into emp_rec;
exit when par_cur%notfound;
DBMS_OUTPUT.PUT_LINE(‘Empno      :’ || emp_record.empno);
DBMS_OUTPUT.PUT_LINE(‘Ename       :’|| emp_record.ename);
END LOOP;
close par_cur
/*
FOR  emp_record  IN  par_cur(v_DeptNo);
LOOP
DBMS_OUTPUT.PUT_LINE(‘Empno      :’ || emp_record.empno);
DBMS_OUTPUT.PUT_LINE(‘Ename       :’|| emp_record.ename);
END LOOP;
*/
END;
 

EXCEPTION

An error condition during a program execution is called an exception in PL/SQL. PL/SQL supports programmers to catch such conditions 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 command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2  THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
……..
WHEN others THEN
exception3-handling-statements
END;
 

SYSTEM EXCEPTIONS

Exception	Oracle Error	SQLCODE	Description
DUP_VAL_ON_INDEX	00001	-1	It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR	01001	-1001	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	01722	-1722	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
NO_DATA_FOUND	01403	+100	It is raised when a SELECT INTO statement returns no rows.
STORAGE_ERROR	06500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS	01422	-1422	It is raised when s SELECT INTO statement returns more than one row.
VALUE_ERROR	06502	-6502	It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE	01476	1476	It is raised when an attempt is made to divide a number by zero.

 

User Defined Exception Example

DECLARE
c_id customers.id%type := &cc_id;
c_name  customers.name%type;
c_addr customers.address%type;
— user defined exception
ex_invalid_id  EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT  name, address INTO  c_name, c_addr
FROM customers
WHERE id = c_id;
 
DBMS_OUTPUT.PUT_LINE (‘Name: ‘||  c_name);
DBMS_OUTPUT.PUT_LINE (‘Address: ‘ || c_addr);
END IF;
EXCEPTION
WHEN ex_invalid_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 others THEN
dbms_output.put_line(‘Error!’);
END;