PL- SQL

PL/SQL Introduction

PL/SQL (Procedural Language/Structured Query Language) is a block-structured

language developed by Oracle that allows developers to combine the power of SQL with
procedural programming constructs. The PL/SQL language enables efficient data
manipulation and control-flow logic, all within the Oracle Database.
Basics of PL/SQL

● PL/SQL stands for Procedural Language extensions to the Structured Query Language
(SQL).
● PL/SQL is a combination of SQL along with the procedural features of programming
languages.
● Oracle uses a PL/SQL engine to process the PL/SQL statements.
● PL/SQL includes procedural language elements like conditions and loops. It allows
declaration of constants and variables, procedures and functions, types and variable of those
types and triggers.
Features of PL/SQL

1. PL/SQL is basically a procedural language, which provides the functionality of decision-making, iteration,
and many more features of procedural programming languages.
2. PL/SQL can execute a number of queries in one block using single command.
3. One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored
in the database for reuse by applications.
4. PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as exception
handling block.
5. Applications written in PL/SQL are portable to computer hardware or operating system where Oracle is
operational.
6. PL/SQL Offers extensive error checking.
Differences Between SQL and PL/SQL

PL/SQL is a block of codes that used to

SQL is a single query that is used to
write the entire program blocks/
perform DML and DDL operations.
procedure/ function, etc.

It is declarative, that defines what needs

PL/SQL is procedural that defines how
to be done, rather than how things need
the things needs to be done.
to be done.

Execute as a single statement. Execute as a whole block.

Mainly used to manipulate data. Mainly used to create an application.

Structure of PL/SQL Block
Structure of PL/SQL Block

DECLARE

declaration statements;

BEGIN

executable statements

EXCEPTIONS

exception handling statements

END;
PL/SQL Identifiers

1. Variables: Like several other programming languages, variables in PL/SQL

must be declared prior to its use. They should have a valid name and data
type as well. Syntax for declaration of variables:

variable_name datatype [NOT NULL := value ];

1. Example to show how to declare variables in PL/SQL :

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
var1 INTEGER;
var2 REAL;
var3 varchar2(20) ;

BEGIN
null;
END;
/
2. Displaying Output:

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

var varchar2(40) := 'Welcome ' ;

BEGIN

dbms_output.put_line(var);

END;

/
1. Explanation:

1. Explanation:
● dbms_output.put_line : This command is used to direct the PL/SQL output to a screen.
2. Using Comments: Like in many other programming languages, in PL/SQL also, comments can
be put within the code which has no effect in the code. There are two syntaxes to create
comments in PL/SQL :
● Single Line Comment: To create a single line comment , the symbol – – is used.
● Multi Line Comment: To create comments that span over several lines, the symbol /*
and */ is used.
 1. Taking input from user: Just like in other programming languages, in PL/SQL also, we can take
input from the user and store it in a variable. Let us see an example to show how to take input from
users in PL/SQL:

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

-- taking input for variable a

a number := &a;

-- taking input for variable b

b varchar2(30) := &b;

BEGIN
null;

END;
/
 1. Output:

Enter value for a: 24

old 2: a number := &a;
new 2: a number := 24;
Enter value for b: 'Wel come'
old 3: b varchar2(30ks) := &b;
new 3: b varchar2(30) := 'Wel Come';

PL/SQL procedure successfully completed.

PL/SQL Data Types

Scalar Data Types in PL/SQL Numeric Data Types

Scalar data types are basic types that store only NUMBER:NUMBER(5, 2)
one value at a time.like numbers, characters, or 5 digits, with 2 of them after the decimal point.
even logical values each representing an BINARY_INTEGER/PLS_INTEGER:signed
individual value. integers

● Numeric Types: Character Types: FLOAT FLOAT(10)

Boolean Types: Datetime Types

Character Data Types Boolean Data Types

CHAR ,VARCHAR2 , STRING,LONG TRUE, FALSE
VARCHAR
PL/SQL Constants with Example

DECLARE
pi CONSTANT NUMBER := 3.14159;
radius NUMBER := 5;
area NUMBER;
BEGIN
area := pi * radius * radius;
DBMS_OUTPUT.PUT_LINE('Area of the circle: ' || area);
END;
/
Types of PL/SQL Operators

DECLARE
num1 NUMBER := 20;
num2 NUMBER := 4;
result NUMBER;
BEGIN
result := num1 + num2;
DBMS_OUTPUT.PUT_LINE('Addition Result: ' || result);
result := num1 - num2;
DBMS_OUTPUT.PUT_LINE('Subtraction Result: ' || result);
result := num1 * num2;
DBMS_OUTPUT.PUT_LINE('Multiplication Result: ' || result);
result := num1 / num2;
DBMS_OUTPUT.PUT_LINE('Division Result: ' || result);
result := MOD(num1, num2);
DBMS_OUTPUT.PUT_LINE('Modulus Result: ' || result);
END;
2. Relational Operators

Operator Description
= Equal to

!= or <> Not Equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

Example:

DECLARE
age1 NUMBER := 25;
age2 NUMBER := 30;
BEGIN
IF age1 <> age2 THEN
DBMS_OUTPUT.PUT_LINE('Ages are not equal.');
END IF;
IF age1 < age2 THEN
DBMS_OUTPUT.PUT_LINE('Age1 is less than Age2.');
END IF;
END;
3. Logical Operators

Operator Description Examples

Returns TRUE if both

AND x > 5 AND y < 10
conditions are TRUE

Returns TRUE if at least

OR x > 5 OR y < 10
one condition is TRUE

NOT Negates a condition NOT (x > 5)

Example:

DECLARE
score1 NUMBER := 80;
score2 NUMBER := 70;
BEGIN
IF score1 >= 75 AND score2 >= 75 THEN
DBMS_OUTPUT.PUT_LINE('Both scores are above average.');
ELSIF score1 >= 75 OR score2 >= 75 THEN
DBMS_OUTPUT.PUT_LINE('At least one score is above
average.');
ELSE
DBMS_OUTPUT.PUT_LINE('Both scores are below average.');
END IF;
END;
4. Comparison Operators

Operator Description

BETWEEN Checks if a value is within a range

LIKE Matches a value against a pattern

IN Checks if a value exists in a list

IS NULL Checks if a value is NULL

Comparison Operators

DECLARE
student_name VARCHAR2(50) := 'Alice';
student_age NUMBER := 19;
BEGIN
IF student_name LIKE 'A%' THEN
DBMS_OUTPUT.PUT_LINE('Student name starts with "A".');
END IF;
IF student_age BETWEEN 18 AND 22 THEN
DBMS_OUTPUT.PUT_LINE('Student is in college age range.');
END IF;
END;
IN and IS NULL Operator
EX. SELECT emp_name
FROM employees
WHERE dept_id IN (10, 20);

Ex SELECT first_name, last_name, email

FROM employees
WHERE phone_number IS NULL;
PL/SQL Control Flow Control Flow refers to the logical order in which statements are executed

1. IF THEN Syntax:
2. IF THEN ELSE
if condition then
3. NESTED-IF-THEN
4. IF THEN ELSIF-THEN-ELSE Ladder -- do something
1. IF THEN end if;
if then the statement is the most simple decision-making
statement. It is used to decide whether a certain
statement or block of statements will be executed
or not i.e if a certain condition is true then a block of
statement is executed otherwise not.
Example
declare
num1 number:= 10;
num2 number:= 20;

begin

if num1 > num2 then

dbms_output.put_line('num1 small');
end if;

dbms_output.put_line('I am Not in if');

end;
2. IF THEN ELSE

declare
num1 number:= 10;
num2 number:= 20;
begin

if num1 < num2 then

dbms_output.put_line('i am in if block');

ELSE
dbms_output.put_line('i am in else Block');
end if;

dbms_output.put_line('i am not in if or else Block');

end;
3. NESTED-IF-THEN

declare
num1 number:= 10;
num2 number:= 20;
num3 number:= 20;

begin
if num1 < num2 then
dbms_output.put_line('num1 small num2');

if num1 < num3 then

dbms_output.put_line('num1 small num3 also');
end if;

end if;

dbms_output.put_line('after end if');

end;
4. IF THEN ELSIF-THEN-ELSE Ladder

-- pl/sql program to illustrate if-then-elif-then-else ladder

declare
num1 number:= 10;
num2 number:= 20;

begin

if num1 < num2 then

dbms_output.put_line('num1 small');

ELSIF num1 = num2 then

dbms_output.put_line('both equal');

ELSE
dbms_output.put_line('num2 greater');
end if;

dbms_output.put_line('after end if');

end;
LOOP Statement in PL/SQL
Syntax

LOOP
-- Code block to be executed
repeatedly
END LOOP;
LOOP
-- Code block
IF condition THEN
EXIT;
END IF;
END LOOP;
Example

DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('This is iteration number ' || counter);
IF counter = 3 THEN
EXIT;
END IF;
counter := counter + 1;
END LOOP;
END;
/
EXIT WHEN Statement

DECLARE
counter NUMBER := 1; -- Initialization of the counter variable

BEGIN
-- Loop that prints "Welcome" five times
LOOP
DBMS_OUTPUT.PUT_LINE('Welcome');

counter := counter + 1; -- Increment the counter

EXIT WHEN counter > 5; -- Exit the loop when counter exceeds 5
END LOOP;
END;
/
Nested Loops

Syntax

-- Outer Loop
LOOP
-- Code block
-- Inner Loop
LOOP
-- Inner loop code block
EXIT WHEN inner_condition;
END LOOP;
EXIT WHEN outer_condition;
END LOOP;
Nested FOR Loop
DECLARE
outer_counter NUMBER := 1;
inner_counter NUMBER := 1;
BEGIN
FOR outer_counter IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE('Outer Loop - Iteration ' || outer_counter);

FOR inner_counter IN 1..2 LOOP

DBMS_OUTPUT.PUT_LINE('Inner Loop - Iteration ' || inner_counter);
END LOOP;
END LOOP;
END;
/
Nested FOR Loop O/P

Statement processed.
Outer Loop - Iteration 1
Inner Loop - Iteration 1
Inner Loop - Iteration 2
Outer Loop - Iteration 2
Inner Loop - Iteration 1
Inner Loop - Iteration 2
Outer Loop - Iteration 3
Inner Loop - Iteration 1
Inner Loop - Iteration 2
FOR LOOP in PL/SQL

Syntax

DECLARE
--declare loop variable and provide its datatype
loop_varaible datatype;
BEGIN
--for loop with start and end value
FOR loop_variable in start_value .. end_value LOOP
set of statements
END LOOP;
END;
/
FOR LOOP in PL/SQL

SET SERVEROUTPUT ON;

DECLARE
counter NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('PL/SQL FOR LOOP EXECUTION');
FOR counter IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('COUNTER VALUE: '|| counter);
END LOOP;
END;
/
Using Nested FOR Loops to Print a Pattern

SET SERVEROUTPUT ON;

BEGIN
DBMS_OUTPUT.PUT_LINE('PL/SQL NESTED FOR LOOP EXECUTION');
FOR counter IN 1..3 LOOP
FOR counter1 IN 1..3 LOOP
DBMS_OUTPUT.PUT( counter1);
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
/
Using the REVERSE Keyword in a PL/SQL FOR Loop

BEGIN
FOR loop_variable IN REVERSE start_value .. end_value LOOP
set_of_statements
END LOOP;
END;
/
Print Number From 5 to 1 Using the REVERSE Keyword

SET SERVEROUTPUT ON;

DECLARE
counter NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('FOR LOOP WITH REVERSE KEYWORD');
FOR counter IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('REVERSE VALUE: '|| counter);
END LOOP;
END;
/
PL/SQL While Loop

Syntax:
WHILE condition
LOOP
-- Statements to be executed as long as the
condition is true
END LOOP;
Example 1: Using PL/SQL WHILE Loop for Iterative Execution

DECLARE
counter NUMBER := 1; -- Initialize a counter variable
BEGIN
-- Start the WHILE loop
WHILE counter <= 5 -- Condition to check
LOOP
-- Statements to be executed as long as the condition is true
DBMS_OUTPUT.PUT_LINE('Counter value: ' || counter);

-- Increment the counter

counter := counter + 1;
END LOOP; -- End of the loop
END;
/
Example 2: Using EXIT WHEN for Loop Termination
DECLARE
total_sum NUMBER := 0; -- Initialize a variable to store the sum
current_number NUMBER := 1; -- Initialize a variable for the loop
BEGIN
-- Start the WHILE loop with EXIT WHEN statement
WHILE total_sum < 10
LOOP
-- Add the current number to the total sum
total_sum := total_sum + current_number;

-- Display the current state

DBMS_OUTPUT.PUT_LINE('Current Number: ' || current_number);
DBMS_OUTPUT.PUT_LINE('Total Sum: ' || total_sum);

-- Increment the current number

current_number := current_number + 1;

-- Exit the loop when the total sum exceeds or equals 10

EXIT WHEN total_sum >= 10;
END LOOP; -- End of the loop
END;
/
CONTINUE Statement in PL/SQL Syntax: CONTINUE;

DECLARE
i NUMBER := 1;
BEGIN
LOOP
IF i = 3 THEN
-- Skip the rest of the loop for i = 3
i := i + 1;
CONTINUE;
END IF;

-- Process other statements inside the loop

DBMS_OUTPUT.PUT_LINE('Current Value of i: ' || i);

i := i + 1;

EXIT WHEN i > 5; -- Exit the loop when i exceeds 5

END LOOP;
END;
/
Example 3: Using IF-CONTINUE in a Nested Loop

DECLARE
i NUMBER := 1;
j NUMBER := 1;
BEGIN
FOR i IN 1..3 LOOP
FOR j IN 1..3 LOOP
IF j = 2 THEN
-- Skip the rest of the inner loop for j = 2
CONTINUE;
END IF;

DBMS_OUTPUT.PUT_LINE('i: ' || i || ', j: ' || j);

END LOOP;
END LOOP;
END;
/
Procedures in PL/SQL

PL/SQL procedures are reusable code blocks that perform specific actions or logic within a database
environment.

1. Procedure Header

● The procedure header includes the procedure name and optional parameter list.
● It is the first part of the procedure and specifies the name and parameters

2. Procedure Body

● The procedure body contains the executable statements that implement the specific business logic.
● It can include declarative statements, executable statements, and exception-handling statements
Create Procedures in PL/SQL
Syntax

CREATE [OR REPLACE] PROCEDURE

procedure_name(parameterName [parameterType(IN,OUT,INOUT])
parameterDatatype ]

IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Example:

CREATE OR REPLACE PROCEDURE display AS

BEGIN

dbms_output.put_line('This is a procedure demo...!');

END;

/
Using IN Parameter in Procedure:

CREATE OR REPLACE PROCEDURE Display_Msg (name IN VARCHAR2)

IS

BEGIN
dbms_output.put_line (‘Welcome '|| name);

END;
–Execute procedure and display output using following statement:
sql> EXECUTE Display_Msg(‘Aarushi’);
Welcome Aarushi
PL/SQL procedure successfully completed.
Executing Procedure:

A standalone procedure can be

executed by using two ways 2. Calling the name of the procedure
1. Using the EXECUTE keyword: The from a PL/SQL block: The
above procedure procedure can also be called from
named 'display' can be executed using another PL/SQL block
EXECUTE keyword with the Example :
following output BEGIN
display;
SQL> EXECUTE display; END;
This is a procedure demo...! The above call will display the output:
PL/SQL procedure successfully This is a procedure demo...!
completed. PL/SQL procedure successfully
completed.
Deleting a Standalone Procedure:

DROP PROCEDURE syntax is:

DROP PROCEDURE procedure_name

Parameters in Procedures

● IN Parameters: Pass values into the procedure and are

read-only within the procedure.
● OUT Parameters: Return values from the procedure to the
calling program and are read-write within the procedure.
● IN OUT Parameters: Allow both passing values into and
returning values from the procedure and are read-write within
the procedure.
Advantages of Procedures

● They result in performance improvement of the application. If a

procedure is being called frequently in an application in a single
connection, then the compiled version of the procedure is delivered.
● They reduce the traffic between the database and the application
since the lengthy statements are already fed into the database and
need not be sent again and again via the application.
● They add to code reusability, similar to how functions and methods
work in other languages such as C/C++ and Java.
Example:
CREATE OR REPLACE PROCEDURE display AS

BEGIN

dbms_output.put_line('This is a procedure demo...!');

END;

/
Executing Procedure:

1. Using the EXECUTE keyword: 2. Calling the name of the procedure from a
PL/SQL block:
SQL> EXECUTE display;
This is a procedure demo...! BEGIN
display;
END;
Example 2: Using OUT Parameter in Procedure:

Create a procedure Calling a procedure in Execute the PL/SQL block

PLSQL block
Create or Replace
PROCEDURE getMax(x IN DECLARE BEGIN
number, y IN number, z OUT a number; getMax(a,b,c)
number) IS b number; END;
BEGIN c number;
IF x < y THEN BEGIN
z:= x;
ELSE a:= &a;
z:= y; b:= &b;
END IF; getMax(a, b, c);
END; dbms_output.put_line(' Max
/ Number is : ' || c);
END;
/
Error and Exception Handling
An error occurred during the execution (run time) of program is called exception

in PL/SQL.

Syntax:
Declare
< Declaration Section>
Begin
<Statements>
Exception
When condition Then
Handler statements
End;
Exception Example

Example:
DECLARE
Salary number;
BEGIN
Salary:=&Salary;

EXCEPTION
WHEN Salary<=0 THEN

DBMS_OUTPUT.PUT_LINE('Error:: Salary must be a positive

number..');
END;
Types of Exception

1. Predefined / System Defined Exception :Predefined exception

is raised automatically whenever there is a violation of
Oracle coding rules. Predefined exceptions are those like
ZERO_DIVIDE, which
is raised automatically when we try to divide a number by zero.
1. Predefined / System Defined Exception

Exception Raised when

DUP_VAL_ON_INDEX When you try to insert a duplicate value into a unique column.

INVALID_CURSOR It occurs when we try accessing an invalid

INVALID_NUMBER On usage of something other than number in place of number

value.

LOGIN_DENIED At the time when user login is denied.

TOO_MANY_ROWS When a select query returns more than one row and
the destination variable can take only single
1. Predefined / System Defined Exception

VALUE_ERROR When an arithmetic, value conversion, truncation, or constraint

error occurs.

CURSOR_ALREADY_OPEN Raised when we try to open an already open

ZERO_DIVIDE, Raised automatically when we try to divide a number by zero.

NO_DATA_FOUND Raise when no data is fetched in SELECT statement.

OTHER Raise when no exception is matched

Example:
DECLARE
c_id customers.id%type := 5;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
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);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
2. User-defined Exceptions:

PL/SQL allows you to define your Syntax:

own exceptions according to the DECLARE
need of your program. A exception_name EXCEPTION;
user-defined exception must be BEGIN
declared and then raised explicitly, <Execution block>
using either a RAISE statement or RAISE <exception_name>
the procedure EXCEPTION
DBMS_STANDARD.RAISE_APPLI WHEN <exception_name> THEN
CATION_ERROR <Handler>
END;
Steps to create User-Defined Exception:
1. Declare User-defined exception: 3. Handling exception: Once the
Exception must be declared before it exception is raised then control
get used. transfers to the appropriate
exception block, checks the name of
Example: the exception within exception block,
exception_name EXCEPTION; if found then executes the error
message or exception handling
2. Raise Exception: Raise the code.
exception when error condition occurs. Example :
EXCEPTION
Example: WHEN exception_nameTHEN
RAISE exception_name; <Exception handling code>
Example :Create an exception Negative_No and raise the exception when entered number is negative.
Declare
Negative_No Exception;
Num number;
Begin
Num:=&Num;
if Num<0 then
RAISE Negative_No;
else
dbms_output.put_line('Num='||Num);
End If;
Exception
When Negative_No then
dbms_output.put_line('error:: Enter positive number...!');
End;/
Function

The PL/SQL Function is very similar to PL/SQL Procedure. The

main difference between procedure and a function is, a function
must always return a value, and on the other hand a procedure
may or may not return a value. Except this, all the other things of
PL/SQL procedure are similar for PL/SQL function.
Syntax to create a function:
CREATE [OR REPLACE] FUNCTION function_name [parameters]

[(parameter_name [IN | OUT | IN OUT] datatype [, ...])]

RETURN return_datatype

{IS | AS}

BEGIN

< function_body >

END [function_name];
Functions are of two types:

1. Function with Parameter

2. Function without Parameter
As like procedure function can also use IN, OUT and INOUT parameter.
Steps for using function in PLSQL:
1. Create a function
2. Write a PL SQL block to call the function and execute block Or execute function
using select statement.
Example 1: Function without using parameter to calculate
total bill of specific table.

Step 1: Creating Function

REATE OR REPLACE FUNCTION disptotal
RETURN number IS
tot number(5) := 0;
BEGIN
SELECT sum(amount) into tot FROM Bill where tno=5;
RETURN tot;
END;
/
Step 2: Executing function using PLSQL block

DECLARE OR

t number(2); Executing function using select

statement
BEGIN
Select disptotal() from dual;
t := disptotal();

dbms_output.put_line('Total Bill: '

|| t);

END;

/
Example 2: Function with using IN parameter for addition
of two numbers.

Step 1: Creating Function Step 2: Executing function using PLSQL

CREATE or REPLACE FUNCTION block
add(n1 in number, n2 in number) DECLARE
return number is A number(5)
n3 number(5); B number(5);
BEGIN C number(5);
n3 :=n1+n2; BEGIN
return n3; A:=&A;
END; / B:=&B;
C := add(A,B);
dbms_output.put_line('Addition is: ' || C);
END;
Cursors
A cursor is a pointer that points to a result of a query. PL/SQL controls the
context area through a cursor. A cursor holds the rows (one or more) returned
by a SQL statement. The set of rows cursor holds is referred to as the active
set.PL/SQL has two types of cursors: Implicit cursors and Explicit cursors.

1. Implicit Cursor: The implicit cursors are Explicit Cursor: Explicit cursors are created by
automatically generated byOracle while an SQL user. These cursors should be defined in the
statement is executed. These are created by declaration section of the PL/SQL block. It is
default to process the statements when DML created on a SELECT statement which returns
statements like INSERT, UPDATE, and more than one row.
DELETE etc. are executed. Oracle provides Steps for creating Explicit cursor:
some attributes known as Implicit cursor's 1. Declare the cursor
attributes to check the status of DML 2. Open the cursor
operations. Some of 3. Fetch the cursor to retrieve data.
them are: %FOUND, %NOTFOUND, 4. Close the cursor to release allocated
%ROWCOUNT and %ISOPEN. memory.
Steps for creating Explicit cursor:
1. Declare the cursor to initialize in the memory: Declaring the cursor
defines the cursor with a name and the associated SELECT statement.
For example:
CURSOR Stud IS SELECT rno, name, per FROM Student;

Here Stud is name of the cursor

2. Open the cursor: Opening the cursor allocates the memory for the cursor
and makes it ready for fetching the rows returned by the SQL statement into
it.
For example, we will open the above defined cursor as follows
open Stud;
3.Fetch the cursor to retrieve data: Fetching the cursor involves accessing
one row at a time. For example, we will fetch rows from the above-opened
cursor as follows:
FETCH Stud INTO s_rno, s_name, s_per;

4. Close the cursor: Closing the cursor means releasing the allocated memory.
For example, we will close the above-opened cursor as follows
Example1 : Implicit Cursor Example 1: PLSQL block to display student
PL/SQL block to update employee table salary information using explicit cursor:
by adding 2000 amount in the existing salary. DECLARE
DECLARE s_rno Student.rno%TYPE;
total_rows number(2); s_name Student.name% TYPE;
BEGIN s_per Student.per% TYPE;
UPDATE employee SET salary = salary + CURSOR stud IS SELECT rno, name, per
2000; FROM Student;
IF sql%notfound THEN BEGIN
dbms_output.put_line('No Employee OPEN Stud;
updated..!); LOOP
ELSIF sql%found THEN FETCH stud into s_rno, s_name, s_per;
total_rows := sql%rowcount; EXIT WHEN stud%notfound;
dbms_output.put_line( total_rows || ' Table dbms_output.put_line(s_rno || ' ' || s_name || ' '
updated '); || s_per);
END IF; END LOOP;
END; CLOSE stud; END; /
/
Triggers

A trigger is a stored procedure in database which automatically invokes whenever a special

event in the database occurs. For example, a trigger can be invoked when a row is inserted
into a specified table or when certain table columns are being updated or deleted.

A trigger is fired when a DML statements like Insert, Delete, Update is executed

on a database table.

A trigger is triggered automatically when an associated DML statement is executed.

Triggers
Syntax: Where,
CREATE or REPLACE trigger • Create or Replace trigger [trigger_name]: Creates
[trigger_name] or replaces an existing trigger with the trigger_name.
[before | after] • [before | after]: This specifies when the trigger will be
{insert | update | delete} executed. Before or
on [table_name] after query execution.
[for each row] • {insert | update | delete}: This specifies the DML
WHEN (condition) operations performed by
DECLARE the trigger.
Declaration-statements • on [table_name]: This specifies the name of the table
BEGIN associated with the
Executable-statements trigger.
EXCEPTION • [for each row]: This specifies a row-level trigger, i.e.,
Exception-handling-statements the trigger will be
END; executed for each row being affected.
• [trigger_body]: This provides the operation to be
performed as trigger is
fired. This is written in Declare, Begin, Exception and
End block.
Types of Trigger
1. Row-Level Trigger: A row-level trigger fires once for each row that is affected by a
triggering event.
For example, if Delete operation is defined as a triggering event for a
particular table, and a single DELETE statement deletes five rows from that
table, the trigger fires five times, once for each row.
2. Statement-Level Trigger: A statement-level trigger fires only once for each
statement. Using the previous example, if deletion is defined as a triggering event for a
particular table, and a single DELETE statement deletes five rows from that table, the
trigger fires once.
3. BEFORE Trigger: BEFORE trigger executes before the DML statement (INSERT,
UPDATE, DELETE) execute.
4. AFTER Trigger: AFTER trigger executes after the DML statement (INSERT,
UPDATE, DELETE) executed.
5. Combination Trigger: Combination trigger are combination of two trigger type,
Before Statement Trigger, Before Row Trigger, After Statement Trigger, After Row
Trigger
Variables used in trigger

1. NEW: New refer to the new value for column. It is represented as

:NEW.columnName, where columnName is the name of a column in the
table on which the trigger is defined.
2. OLD: Old refer to the old value for the column. It is represented as
:OLD.columnName, where columnName is the name of a column in the
table on which the trigger is defined.
Error statement in trigger is written using
Raise_application_error(Error Number,’Error Message’);
eg: Raise_application_error(-20003,'Error:: Salary must be a positive
number');
Example:1) Before Update: This trigger will raise before
salary is updated on “Employee” table
Create or replace trigger emp_update before SQL> update employee set salary=-4567;
update on employee for each update employee set salary=-4567
row *
ERROR at line 1:
Declare ORA-20003: Error:: Salary must be a positive
invalid_salary Exception; number
Begin ORA-06512: at "MVT.EMP_UPDATE", line 9
if(:new.salary<=0) then ORA-04088: error during execution of trigger
raise invalid_salary; 'MVT.EMP_UPDATE'
end if;
Exception
when invalid_salary then
raise_application_error(-20003,'Error:: Salary
must be a positive
number');
End;
2) Before Insert or update: This trigger will raise before
salary is inserted or updated on Employee” table
IPUT FOR TRIGGER old 1: insert into Example
employee Create or replace trigger emp_insert before
values(&eno,'&ename','&addr','&design',&salar insert or update on employee for
y) each row
new 1: insert into employee Declare
values(5,'EEE','baner','clerk',0) invalid_salary Exception;
Begin
insert into employee if(:new.salary<=0) then
values(5,'EEE','baner','clerk',0) raise invalid_salary;
* end if;
ERROR at line 1: Exception
ORA-20002: Error:: Salary must be a positive when invalid_salary then
number raise_application_error(-20002,'Error:: Salary
ORA-06512: at "MVT.EMP_INSERT", line 9 must be a positive number');
ORA-04088: error during execution of trigger End;
'MVT.EMP_INSERT'
3) Before Delete: This trigger will raise before salary is
deleted on “Employee” table
Create or replace trigger del_Emp before Output:
delete on employee for each row delete from employee where eno=3;
Declare delete from employee where eno=3
del_Emp Exception; *
Begin ERROR at line 1:
raise del_Emp; ORA-20001: Error:: Record can not be deleted
Exception ORA-06512: at "MVT.DEL_EMP", line 9
when del_Emp then ORA-04088: error during execution of trigger
raise_application_error(-20001,'Error:: Record 'MVT.DEL_EMP'
can not be deleted');
End;
4) After Insert: This trigger will raise after record is inserted
and mobile number is not with in range on “Employee”
table.
create or replace trigger emp_insert after insert Output:
on employee for each row insert into employee
Declare values(7,'Mr.Verma','pimpri','Programmer',45678
invalid_mobile Exception; ,9822645);
len number; insert into employee
Begin values(7,'Mr.Verma','pimpri','Programmer',45678
len:=length(:new.mobile); ,9822645)*
if(len<10 or len>10) then ERROR at line 1:
raise invalid_mobile; ORA-20006: Error:: Mobile number must be 10
end if; digit....!
Exception ORA-06512: at "MVT.EMP_INSERT", line 11
when invalid_mobile then ORA-04088: error during execution of trigger
raise_application_error(-20006,'Error:: Mobile 'MVT.EMP_INSERT'
number must be 10
digit....!');
End;
Characteristics of PL/SQL Packages: Public and Private Elements:
Elements declared in the package
Encapsulation: specification are public and can be accessed
Packages allow you to group related PL/SQL by other PL/SQL programs.
elements (procedures, functions, variables, types, Elements declared in the package body but
etc.) into a single, named unit. not in the specification are private and can
This promotes modularity and helps to keep code only be accessed by other elements within
organized. Structure: the package.
A package consists of two parts: Persistence:
Package Specification: This defines the public Packages are stored in the database, so
interface of the package, declaring the elements that their code is persistent and can be reused by
are accessible to other PL/SQL programs. multiple applications.
Package Body: This contains the implementation of Initialization:
the procedures and functions declared in the Packages can have an optional initialization
specification, as well as any private elements. section, which is executed only once when
the package is first referenced in a session.
Packages

Advantages of PL/SQL Packages:

Modularity:
Packages promote modular programming, making code easier to develop, maintain, and
reuse.
Encapsulation and Information Hiding:
Packages allow you to hide the implementation details of your code, exposing only the
necessary interface. This improves code security and reduces the risk of unintended
modifications.
Improved Performance:
Packages are stored in compiled form in the database, which can improve performance
compared to executing individual PL/SQL blocks. Also because all related functions and
procedures are stored together, the database can process them more efficiently.
Code Reusability:
Packages allow you to create reusable code components that can be shared by multiple
applications. This reduces code duplication and development time.
Better Organization:
Packages help to organize PL/SQL code into logical units, making it easier to manage and
THANKS