Amrutvahini Polytechnic Sangamner

Program – IF Class – IF4I

Course – Database Management Code – 22416

Staff – Navale N.D

 Chapter No 4
Unit-IV PL/SQL Programming (12)

• PL/SQL Introduction

•PL/SQL is a block structured language that

enables developers to combine the power of SQL
with procedural statements.All the statements of
a block are passed to oracle engine all at once
which increases processing speed and decreases
the traffic.
• Disadvantages of SQL:
• SQL doesn’t provide the programmers with a
technique of condition checking, looping and
branching.
• SQL statements are passed to Oracle engine one at a
time which increases traffic and decreases speed.
• SQL has no facility of error checking during
manipulation of data.
• Features of PL/SQL:
• PL/SQL is basically a procedural language, which provides the functionality
of decision making, iteration and many more features of procedural
programming languages.
• PL/SQL can execute a number of queries in one block using single
command.
• 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.
• PL/SQL provides a feature to handle the exception which occurs in PL/SQL
block known as exception handling block.
• Applications written in PL/SQL are portable to computer hardware or
operating system where Oracle is operational.
• PL/SQL Offers extensive error checking.
SQL PL/SQL

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

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

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

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

Cannot contain PL/SQL code in it. It is an extension of SQL, so it can contain SQL inside it.
 Structure of PL/SQL Block:
• DECLARE
• declaration statements;

• BEGIN
• executable statements

• EXCEPTIONS
• exception handling statements

• END;
• Declare section starts with DECLARE keyword in which variables,
constants, records as cursors can be declared which stores data
temporarily. It basically consists definition of PL/SQL identifiers.
This part of the code is optional.
• Execution section starts with BEGIN and ends
with END keyword.This is a mandatory section and here the
program logic is written to perform any task like loops and
conditional statements. It supports all DML commands, DDL
commands and SQL*PLUS built-in functions as well.
• Exception section starts with EXCEPTION keyword.This section is
optional which contains statements that are executed when a
run-time error occurs. Any exceptions can be handled in this
section.
 PL/SQL Scalar Data

• 1Numeric
• Numeric values on which arithmetic operations are performed.

• 2Character
• Alphanumeric values that represent single characters or strings of characters.

• 3Boolean
• Logical values on which logical operations are performed.

• 4Datetime
• Dates and times.
 PL/SQL Numeric Data
• 2BINARY_INTEGER
• Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
• 3BINARY_FLOAT
• Single-precision IEEE 754-format floating-point number
• 4BINARY_DOUBLE
• Double-precision IEEE 754-format floating-point number
• 5NUMBER(prec, scale)
• Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable
can also represent 0
• 7DECIMAL(prec, scale)
• IBM specific fixed-point type with maximum precision of 38 decimal digits
• 8NUMERIC(pre, secale)
• Floating type with maximum precision of 38 decimal digits
• 9DOUBLE PRECISION
• ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
• 10FLOAT
• ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
• 11INT
• ANSI specific integer type with maximum precision of 38 decimal digits
• 12INTEGER
• ANSI and IBM specific integer type with maximum precision of 38 decimal digits
• 14REAL
• Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)
 PL/SQL Character Data Types
• 1CHAR
• Fixed-length character string with maximum size of 32,767 bytes

• 2VARCHAR2
• Variable-length character string with maximum size of 32,767 bytes

• 3RAW
• Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL

• 4NCHAR
• Fixed-length national character string with maximum size of 32,767 bytes

• 5NVARCHAR2
• Variable-length national character string with maximum size of 32,767 bytes

• 6LONG
• Variable-length character string with maximum size of 32,760 bytes

• 7LONG RAW
• Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL
• 8
 PL/SQL Variables

• A variable is a reserved memory area for storing the data

of a particular datatype. It is an identifier which is
identifies memory locations where data is stored. This
memory is reserved at the time of declaration of a
variable which is done in the DECLARE section of any
PL/SQL block.
• Syntax for declaration of a variable:
• Variable_name datatype(size);
• Example
• roll_no NUMBER(2);
• assign some value to the variable at the time
of declaration itself, the syntax would be,
• Variable_name datatype(size) NOT
NULL:=value;Let's take a simple example for
this too,
• eid NUMBER(2) NOT NULL := 5;
 Rules for declaring a Variable in PL/SQL

• Following are some important rules to keep in mind while defining and
using a variable in PL/SQL:
• A variable name is user-defined. It should begin with a character and can be
followed by maximum of 29 characters.
• Keywords (i.e, reserved words ) of PL/SQL cannot be used as variable name.
• Multiple variables can be declared in a single line provided they must be
separated from each other by at least one space and comma.For eg: a,b,c
int;
• Variable names containing two words should not contain space between
them. It must be covered by underscore instead.For eg: Roll_no
• A variable name is case sensitive, which means a_var is not same as A_var
• set serveroutput on;
• DECLARE
• a NUMBER(2);
• b NUMBER(2) := 5;
• BEGIN a := b;
• dbms_output.put_line(a);
• END;
 PL/SQL Constants
• Constants are those values which when declared
remain fixed throughout the PL/SQL block. For
declaring constants, a constant keyword is used.
• Syntax for declaring constants:
• Constant_Name constant Datatype(size) := value;
• Let's take a simple code example,
• school_name constant VARCHAR2(20) := "DPS";
• In the above code example, constant name is user
defined followed by a keyword constant and then
we have declared its value, which once declared
cannot be changed.
 PL/SQL Control Statements

• Conditional selection statements, which run different statements for different

data values.
• The conditional selection statements are IF and CASE.
• Loop statements, which run the same statements with a series of different data
values.
• The loop statements are the basic LOOP, FOR LOOP, and WHILE LOOP.
• The EXIT statement transfers control to the end of a loop.
The CONTINUE statement exits the current iteration of a loop and transfers
control to the next iteration. Both EXIT and CONTINUE have an
optional WHEN clause, where you can specify a condition.
• Sequential control statements, which are not crucial to PL/SQL programming.
• The sequential control statements are GOTO, which goes to a specified statement,
and NULL, which does nothing.
 Conditional Selection Statements

• IF THEN
• IF THEN ELSE
• IF THEN ELSIF
• CASE
 IF THEN Statement
• The IF THEN statement either runs or skips a sequence of one or more statements,
depending on a condition.
• SYNTAX:
• IF condition THEN
statements
• END IF;

• If the condition is true, the statements run; otherwise, the IF statement does nothing.

• IF new_balance < minimum_balance THEN

• overdrawn := TRUE;
• ELSE
• overdrawn := FALSE;
• END IF;
 IF THEN ELSE Statement

• The IF THEN ELSE statement has this structure:

• IF condition THEN
statements
• ELSE
else_statements
• END IF;

• If the value of condition is true, the statements run;

otherwise, the else_statements run.
• DECLARE
PROCEDURE p (
sales NUMBER,
quota NUMBER,
emp_id NUMBER )
IS bonus NUMBER := 0;
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
ELSE
bonus := 50;

END IF;
 IF ELSE IFLSE
• DECLARE
PROCEDURE p (
sales NUMBER,
quota NUMBER,
emp_id NUMBER )
IS bonus NUMBER := 0;
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
ELSE IF sales > quota THEN
bonus := 50;
ELSE bonus := 0;
END IF;
END IF;
 Iterative Statements in PL/SQL

• Iterative control Statements are used when

we want to repeat the execution of one or
more statements for specified number of
times.
There are three types of loops in PL/SQL:
• • Simple Loop
• While Loop
• For Loop
 Simple Loop

• A Simple Loop is used when a set of statements is to be executed

at least once before the loop terminates. An EXIT condition must
be specified in the loop, otherwise the loop will get into an
infinite number of iterations. When the EXIT condition is satisfied
the process exits from the loop.
• General Syntax
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;
 While Loop

• A WHILE LOOP is used when a set of statements has to

be executed as long as a condition is true. The condition
is evaluated at the beginning of each iteration. The
iteration continues until the condition becomes false.
• The General Syntax to write a WHILE LOOP is:
WHILE <condition>
LOOP
statements;
END LOOP;
 FOR Loop

• A FOR LOOP is used to execute a set of statements for a

predetermined number of times. Iteration occurs between the
start and end integer values given. The counter is always
incremented by 1. The loop exits when the counter reachs the
value of the end integer.
• The General Syntax to write a FOR LOOP is:

• FOR counter IN val1..val2

LOOP
statements;
END LOOP;
 Sequential Statement
• GOTO
• Null
 The GOTO Statement
• The GOTO statement performs unconditional branching to another executable
statement in the same execution section of a PL/SQL block.
• The general format for a GOTO statement is:
• GOTO label_name;
• where label_name is the name of a label identifying the target statement. This
GOTO label is defined in the program as follows:
• <<label_name>>
• When PL/SQL encounters a GOTO statement, it immediately shifts control to the
first executable statement following the label.
• EXAMPLE
• BEGIN
GOTO second_output;
DBMS_OUTPUT.PUT_LINE('This line will never execute.');
<<second_output>> DBMS_OUTPUT.PUT_LINE('We are here!');
END;
 The NULL Statement

• Usually when you write a statement in a

program, you want it to do something. There
are cases, however, when you want to tell
PL/SQL to do absolutely nothing, and that is
where the NULL statement comes in handy.
The NULL statement has the following format:
• NULL;
 PL/SQL - Exceptions

• An exception is an error condition during a

program execution. 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;
 Example
• DECLARE
• c_id customers.id%type := 8;
• 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;
• /
 User-defined Exceptions

• PL/SQL allows you to define your own exceptions according

to the need of your program. A user-defined exception
must be declared and then raised explicitly, using either a
RAISE statement or the procedure
DBMS_STANDARD.RAISE_APPLICATION_ERROR.

• The syntax for declaring an exception is −

• DECLARE
• my-exception EXCEPTION;
DECLARE
c_id customers.id%type := &c_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;
 cursor
• Oracle creates a memory area, known as the context area, for processing an
SQL statement, which contains all the information needed for processing the
statement; for example, the number of rows processed, etc.

• 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.
• 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.
 Attribute
S.No Attribute & Description

%FOUND
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more
1
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
2
DELETE statement affected no rows, or a SELECT INTO statement returned no
rows. Otherwise, it returns FALSE.
%ISOPEN
3 Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor
automatically after executing its associated SQL statement.
%ROWCOUNT
4 Returns the number of rows affected by an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT INTO statement.
• DECLARE
• total_rows number(2);
• BEGIN
• UPDATE customers SET salary = salary + 500;
• IF sql%notfound THEN
• dbms_output.put_line('no customers selected');
• ELSIF sql%found THEN
• total_rows := sql%rowcount;
• dbms_output.put_line( total_rows || ' customers selected ');
• END IF;
• END;
• /
 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
• CURSOR c_customers IS SELECT id, name, address FROM customers;

• Opening the Cursor

• OPEN c_customers;

• Fetching the Cursor

• FETCH c_customers INTO c_id, c_name, c_addr;

• Closing the Cursor

• CLOSE c_customers;
 Example
• DECLARE
• c_id customers.id%type;
• c_name customers.name%type;
• c_addr customers.address%type;
• CURSOR c_customers is SELECT id, name, address FROM customers;
• BEGIN
• OPEN c_customers;
• LOOP
• FETCH c_customers into c_id, c_name, c_addr;
• EXIT WHEN c_customers%notfound;
• dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
• END LOOP;
• CLOSE c_customers;
• END;
 PL/SQL - Triggers
• Triggers are stored programs, which are automatically
executed or fired when some events occur. Triggers are, in
fact, written to be executed in response to any of the
following events −

• A database manipulation (DML) statement (DELETE,

INSERT, or UPDATE)

• A database definition (DDL) statement (CREATE, ALTER, or

DROP).

• A database operation (SERVERERROR, LOGON, LOGOFF,

STARTUP, or SHUTDOWN).
 Syntax
• CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER |
INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF
col_name] ON table_name
• [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN
(condition)
• DECLARE
• Declaration-statements
• BEGIN
• Executable-statements
• EXCEPTION
• Exception-handling-statements
• END;
• CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.

• {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is
used for creating trigger on a view.

• {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.

• [OF col_name] − This specifies the column name that will be updated.

• [ON table_name] − This specifies the name of the table associated with the trigger.

• [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements,
such as INSERT, UPDATE, and DELETE.

• [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being
affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a
table level trigger.

• WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only
for row-level triggers.
• CREATE OR REPLACE TRIGGER display_salary_changes BEFORE
DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW
WHEN (ID > 0)
• DECLARE
• sal_diff number;
• BEGIN
• sal_diff := :NEW.salary - :OLD.salary;
• dbms_output.put_line('Old salary: ' || :OLD.salary);
• dbms_output.put_line('New salary: ' || :NEW.salary);
• dbms_output.put_line('Salary difference: ' || sal_diff);
• END;
 PL/SQL - Procedures

• The PL/SQL stored procedure or simply a

procedure is a PL/SQL block which performs
one or more specific tasks.
• It is just like procedures in other programming
languages.
• Procedure does not returns any values.
 syntax
• CREATE [OR REPLACE] PROCEDURE
procedure_name [(parameter_name [IN |
OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
 Example
• DECLARE
• a number;
• b number;
• c number;
Create or repalce 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;
 PL/SQL 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 true for PL/SQL function too.
 Syntax
• CREATE [OR REPLACE] FUNCTION
function_name [(parameter_name [IN | OUT |
IN OUT] type [, ...])] RETURN return_datatype
{IS | AS}
• BEGIN
• < function_body >
• END [function_name];
 Example
• CREATE OR REPLACE FUNCTION
totalCustomers RETURN number IS
total number(2) := 0;
• BEGIN
SELECT count(*) into total FROM customers;
• RETURN total;
• END;
• DECLARE
• a number;
• b number;
• c number;
create or repalce 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;