Declarations

This section starts with the keyword DECLARE. It is an optional section and defines all variables,
cursors, subprograms, and other elements to be used in the program.
Executable Commands
This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It
consists of the executable PL/SQL statements of the program. It should have at least one executable
line of code, which may be just a NULL command to indicate that nothing should be executed.
Exception Handling
This section starts with the keyword EXCEPTION. This optional section contains exception(s) that
handle errors in the program.

DECLARE
<declarations section>
BEGIN <executable command(s)>
EXCEPTION <exception handling> END;
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;

• identifiers are not case-sensitive.

• cannot use a reserved keyword as an identifier.
The PL/SQL
Delimiter
Delimiters Description
+, -, *, / Addition, subtraction/negation, multiplication, division

% Attribute indicator

' Character string delimiter

. Component selector

(,) Expression or list delimiter

: Host variable indicator

, Item separator

" Quoted identifier delimiter

= Relational operator

@ Remote access indicator

; Statement terminator

:= Assignment operator

=> Association operator

|| Concatenation operator

** Exponentiation operator

<<, >> Label delimiter (begin and end)

The PL/SQL Comments
• The PL/SQL single-line comments start with the delimiter -- (double
hyphen) and multi-line comments are enclosed by /* and */.
DECLARE
-- variable declaration
message varchar2(20):= 'Hello, World!';
BEGIN
/* * PL/SQL executable statement(s) */
dbms_output.put_line(message);
END;
PL/SQL - Data Types

Scalar
Single values with no internal components, such as a NUMBER,
DATE, or BOOLEAN
Composite
Data items that have internal components that can be accessed
individually. For example, collections and records.
Reference
Pointers to other data items.
Variable Declaration in PL/SQL
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

DECLARE
Initializing Variables in PL/SQL :
a integer := 10;
b integer := 20;
The DEFAULT keyword
c integer;
The assignment operator
f real;
Example:
BEGIN
a:=0;
c := a + b;
B Varchar2(10) DEFAULT ‘Good Morning’
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
Variable Scope in PL/SQL
• Local variables − Variables declared in an inner block and not accessible
to outer blocks.
• Global variables − Variables declared in the outermost block or a
package. DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
BEGIN dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
PL/SQL - Operators

• Arithmetic operators

• Relational operators

• Comparison operators

• Logical operators

• String operators
Arithmetic Operators
Operator Description Example
+ Adds two operands A + B will give 15
- Subtracts second operand A - B will give 5
from the first

* Multiplies both operands A * B will give 50

/ Divides numerator by de- A / B will give 2

numerator
** Exponentiation operator, A ** B will give 100000
raises one operand to the
power of other
Relational Operators
Operator Description Example
Checks if the values of two operands are (A = B) is not true.
= equal or not, if yes then condition becomes
true.
!= Checks if the values of two operands are
<> equal or not, if values are not equal then (A != B) is true.
~= condition becomes true.
Checks if the value of left operand is (A > B) is not true.
> greater than the value of right operand, if
yes then condition becomes true.
Checks if the value of left operand is less (A < B) is true.
< than the value of right operand, if yes then
condition becomes true.
Checks if the value of left operand is (A >= B) is not true.
greater than or equal to the value of right
>=
operand, if yes then condition becomes
true.
Checks if the value of left operand is less (A <= B) is true
<= than or equal to the value of right operand,
if yes then condition becomes true.
Comparison Operators
Operator Description Example
The LIKE operator compares a character, If 'Zara Ali' like 'Z% A_i' returns a
string, or CLOB value to a pattern and Boolean true, whereas, 'Nuha Ali'
LIKE
returns TRUE if the value matches the like 'Z% A_i' returns a Boolean
pattern and FALSE if it does not. false.
The BETWEEN operator tests whether a If x = 10 then, x between 5 and 20
value lies in a specified range. x returns true, x between 5 and 10
BETWEEN
BETWEEN a AND b means that x >= a returns true, but x between 11 and
and x <= b. 20 returns false.
The IN operator tests set membership. x IN If x = 'm' then, x in ('a', 'b', 'c')
IN (set) means that x is equal to any member returns Boolean false but x in ('m',
of set. 'n', 'o') returns Boolean true.
The IS NULL operator returns the
BOOLEAN value TRUE if its operand is
If x = 'm', then 'x is null' returns
IS NULL NULL or FALSE if it is not NULL.
Boolean false.
Comparisons involving NULL values
always yield NULL.
Logical Operators

Operator Description Examples

Called the logical AND operator. If (A and B) is false.
and both the operands are true then
condition becomes true.

Called the logical OR Operator. If any (A or B) is true.

or of the two operands is true then
condition becomes true.
Called the logical NOT Operator. Used not (A and B) is true.
to reverse the logical state of its
not operand. If a condition is true then
Logical NOT operator will make it
false.
PL/SQL – Control Structures

• IF-THEN

Syntax for IF-THEN statement is −

IF condition THEN S; END IF;

• IF-THEN-ELSE statement

IF condition
THEN S1;
ELSE S2;
END IF;

IF-THEN-ELSE Statement IF(boolean_expression 1)

THEN S1; -- Executes when the boolean expression 1 is true
ELSIF( boolean_expression 2) THEN S2; -- Executes when the boolean expression 2 is true
ELSIF( boolean_expression 3) THEN S3; -- Executes when the boolean expression 3 is true
ELSE S4; -- executes when the none of the above condition is true
END IF;
Loops
• WHILE LOOP

Syntax
DECLARE
WHILE condition LOOP
a number(2) := 10;
sequence_of_statements
BEGIN
END LOOP;
WHILE a < 20 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;
FOR LOOP
Syntax
FOR counter IN initial_value .. final_value LOOP
sequence_of_statements;
DECLARE
END LOOP;
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
VARRAY
• Which can store a fixed-size sequential collection of elements of the
same type.
• All varrays consist of contiguous memory locations.
• The lowest address corresponds to the first element and the highest
address to the last element.
Creating a Varray Type
• A varray type is created with the CREATE TYPE statement.
• You must specify the maximum size and the type of elements stored in the
varray.

• The basic syntax for creating a VARRAY type within a PL/SQL block is −

TYPE varray_type_name IS VARRAY(n) of <element_type>

Where,
• varray_type_name is a valid attribute name,
• n is the number of elements (maximum) in the varray,
• element_type is the data type of the elements of the array.

• For example −
TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;
• Example:
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER; Output:
names namesarray;
marks grades; Total 5 Students
total integer;
Student: Kavita Marks: 98
BEGIN
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); Student: Pritam Marks: 97
marks:= grades(98, 97, 78, 87, 92);
total := names.count; Student: Ayan Marks: 78
dbms_output.put_line('Total '|| total || ' Students');
Student: Rishav Marks: 87
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || ' Marks: ' || marks(i)); Student: Aziz Marks: 92
END LOOP;
END;
PL/SQL - Procedures
• A subprogram is 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.

Declarative Part
• It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE
keyword.
• It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms.
• These items are local to the subprogram and cease to exist when the subprogram completes execution.

Executable Part
• This is a mandatory part and contains statements that perform the designated action.

Exception-handling
• This is again an optional part. It contains the code that handles run-time errors.
Creating a Procedure

• A procedure is created with the CREATE OR REPLACE PROCEDURE statement.

• The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows--

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS}

BEGIN

< procedure_body >

END procedure_name;
• procedure-name specifies the name of the procedure.

• [OR REPLACE] option allows the modification of an existing procedure.

• The optional parameter list contains name, mode and types of the parameters.

• IN represents the value that will be passed from outside and OUT represents
the parameter that will be used to return a value outside of the procedure.

• procedure-body contains the executable part.

• The AS keyword is used instead of the IS keyword for creating a standalone

procedure.
Example

CREATE OR REPLACE PROCEDURE CSE

AS

BEGIN

dbms_output.put_line(‘hai');

END;
Executing a Procedure

• A procedure can be called in two ways −

• Using the EXECUTE keyword
• Calling the name of the procedure from a PL/SQL block
• The above procedure named ‘CSM' can be called with the EXECUTE keyword as
−
• EXECUTE CSM;
• The above call will display −
Hai
• PL/SQL procedure successfully completed.
Deleting a Standalone Procedure

• A procedure is deleted with the DROP PROCEDURE statement.

• Syntax for deleting a procedure is −
• DROP PROCEDURE procedure-name;
• You can drop the CSM procedure by using the following statement −
• DROP PROCEDURE CSM;
Parameter Modes in PL/SQL Subprograms
IN
An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN
parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or
expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the
subprogram call. It is the default mode of parameter passing. Parameters are passed by reference.

OUT
An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable.
You can change its value and reference the value after assigning it. The actual parameter must be variable and it is
passed by value.
IN OUT
An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be
assigned a value and the value can be read.
The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression.
Formal parameter must be assigned a value. Actual parameter is passed by value.
IN & OUT Mode Example

• This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT
parameters.
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) OUTPUT: Minimum of (23, 45) : 23
IS
PL/SQL procedure successfully completed.
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);
Methods for Passing Parameters

• Actual parameters can be passed in three ways −

• Positional notation
• Named notation
• Mixed notation
Positional Notation :
• In positional notation, you can call the procedure as −
• findMin(a, b, c, d);
• In positional notation, the first actual parameter is substituted for the
first formal parameter; the second actual parameter is substituted for the
second formal parameter, and so on. So, a is substituted for x, b is
substituted for y, c is substituted for z and d is substituted for m.
• Named Notation :
• In named notation, the actual parameter is associated with the formal
parameter using the arrow symbol ( => ). The procedure call will be like
the following −
• findMin(x => a, y => b, z => c, m => d);
• Mixed Notation :
• In mixed notation, you can mix both notations in procedure call;
• however, the positional notation should precede the named notation.
• The following call is legal −
findMin(a, b, c, m => d);
• However, this is not legal:
findMin(x => a, b, c, d);
PL/SQL - Functions

• A function is same as a procedure except that it returns a value..

• Creating a Function
• A function is created using the CREATE FUNCTION statement.
• The simplified syntax for the CREATE OR REPLACE
PROCEDURE statement is as follows −
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name
[IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
• Where,
• function-name specifies the name of the function.
• [OR REPLACE] option allows the modification of an existing function.
• The optional parameter list contains name, mode and types of the parameters. IN
represents the value that will be passed from outside and OUT represents the
parameter that will be used to return a value outside of the procedure.
• The function must contain a return statement.
• The RETURN clause specifies the data type you are going to return from the
function.
• function-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone
function.
Calling a Function
• While creating a function, you give a definition of what the function
has to do. To use a function, you will have to call that function to
perform the defined task. When a program calls a function, the program
control is transferred to the called function.
• A called function performs the defined task and when its return
statement is executed or when the last end statement is reached, it
returns the program control back to the main program.
• To call a function, you simply need to pass the required parameters
along with the function name and if the function returns a value, then
you can store the returned value. Following program calls the
function totalCustomers from an anonymous block −
Example:
DECLARE
a number
; b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number IS z number;
BEGIN
IF x > y THEN
z:= x;
ELSE Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
Output:
Maximum of (23,45): 45
PL/SQL procedure successfully completed.
PL/SQL Recursive Functions
• We have seen that a program or subprogram may call another
subprogram. When a subprogram calls itself, it is referred to as a
recursive call and the process is known as recursion.
Example :

DECLARE
num number;
factorial number;
FUNCTION fact(x number)
RETURN number IS f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE f := x * fact(x-1);
END IF;
RETURN f;
END;
BEGIN
num:= 6;
factorial := fact(num);
O N;
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); P UT
O UT
END;
VER
SER
OUTPUT: SET
Factorial 5 is 120
PL/SQL - Cursors

• A cursor is a pointer to this context area. 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 the cursor holds is referred to as the active set.

• You can name a cursor so that it could be referred to in a program to fetch and process

the rows returned by the SQL statement, one at a time. 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.

• In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always
has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The
SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS,
designed for use with the FORALL statement.
Attribute & Description

%FOUND
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a
SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

%NOTFOUND
The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE
statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it
returns FALSE.
%ISOPEN
Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically
after executing its associated SQL statement.
%ROWCOUNT
Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or
returned by a SELECT INTO statement.
• CUSTOMERS table we had created.

ID Name Age Address Salary

1 Ramesh 32 Ahmedabad 2000
2 Ravi 25 Delhi 1500
3 Kumar 23 Kota 2000
4 Sai 25 Mumbai 6500
5 Ram 27 Bhopla 8500
6 Sita 28 MP 4500
The following program will update the table and increase the salary of each customer by 500 and
use the SQL%ROWCOUNT ID Name Age Address Salary
attribute to determine the number of rows affected −
1 Ramesh 32 Ahmedabad 2500
DECLARE
• number(2);
total_rows 2 Ravi 25 Delhi 2000
BEGIN 3 Kumar 23 Kota 2500
UPDATE customers SET salary = salary + 500;
IF sql%notfound THEN 4 Sai 25 Mumbai 7000
dbms_output.put_line('no customers selected'); 5 Ram 27 Bhopla 9000
ELSIF sql%found THEN 6 Sita 28 MP 5000
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
result −
6 customers selected PL/SQL procedure successfully completed.
If you check the records in customers table, you will find that the rows have been updated…

Select * from customer;

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 includes the following steps −

•Declaring the cursor for initializing the memory
•Opening the cursor for allocating the memory
•Fetching the cursor for retrieving the data
•Closing the cursor to release the allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement.
For example −

CURSOR c_customers IS
SELECT id, name, address FROM customers;

Opening 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 c_customers;
Fetching the Cursor
• Fetching the cursor involves accessing one row at a time.

• For example, we will fetch rows from the above-opened cursor as follows −

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor:

• Closing the cursor means releasing the allocated memory.

• For example, we will close the above-opened cursor as follows −

CLOSE c_customers;
DECLARE
c_id customers.id%type; 1 Ramesh Ahmedabad
c_name customers.name%type;
c_addr customers.address%type; 2 Ravi Delhi
CURSOR c_customers is
SELECT id, name, address FROM customers; 3 kumar Kota
BEGIN OPEN c_customers;
LOOP 4 Sai Mumbai
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound; 5Ram Bhopal
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP; 6 SitaMP
CLOSE c_customers;
END;