PL/SQL

PROCEDURAL EXTENSION TO SQL:

 PL/SQL is an extension to SQL with design features of programming languages.
 Data manipulation and SQL query statements are included within procedural code units.
 PL/SQL offers modern software engineering features such as data encapsulation, exception
handling, information hiding, and object orientation, bringing state-of-the-art programming
to the Oracle Server and Toolset.

Benefits of PL/SQL:
 PL/SQL plays a central role to both the Oracle Serve (through stored procedures, stored
function, database triggers, and packages) and Oracle development tools (through Oracle
Developer component triggers).
 Oracle Developer applications make use of shared libraries that hold code (procedures and
functions) and can be accessed locally or remotely. Oracle Developer consists of Oracle
Forms. Oracle Reports, and Oracle Graphics.
 SQL datatypes can also be used in PL/SQL. Combined with the direct access that SQL
provides, these Shared datatypes integrate PL/SQL with the Oracle Server data dictionary.
PL/SQL bridges the gap between convenient access to database technology and the need for
procedural programming capabilities.

PL/SQL Blocks:
 The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which
can be nested within each other. Typically, each block performs a logical action in he
program. A block has the following structure:
DECLARE
/* Declarative section: variables, types, and local subprograms. */
BEGIN
/* Executable section: procedural and SQL statements go here. */
/* This is the only section of the block that is required. */
EXCEPTION
/* Exception handling section: error handling statements go here. */
END;
 Only the executable section is required. The other sections are optional. The only SQL
statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and
several other data manipulation statements plus some transaction control. However, the
SELECT statement has a special form in which a single tuple is placed in variables; more on
this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The
executable section also contains constructs such as assignments, branches, loops, procedure
calls, and triggers, which are all described below (except triggers). PL/SQL is not case
sensitive. C-style comments (/* ... */) may be used.
Fundamentals of PL/SQL Language:
1
PL/SQL

DATA TYPE:
Every constant, variable, and parameter has a datatype (or type), which specifies a storage
format, constraints, and valid range of values. PL/SQL provides a variety of predefined
datatypes. For instance, you can choose from integer, floating point, character, Boolean, date.

BOOLEAN:
You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL (a
missing, unknown, or inapplicable value). Only logic operations are allowed On BOOLEAN
variables.

The BOOLEAN datatype takes no parameters. Only the values TRUE, FALSE, and NULL can
be assigned to a BOOLEAN variable. You cannot insert the values TRUE and FALSE into a
database column. Also, you cannot select or fetch column values into a BOOLEAN variable.

CHAR:
You use the CHAR datatype to store fixed-length character data. How the data is represented
internally depends on the database character set. The CHAR datatype takes an optional
parameter that lets you specify a maximum size up to 32767 bytes. You can specify the size in
terms of bytes or characters, where each character contains one or more bytes, depending on the
character set encoding.
The syntax follows:
CHAR [(maximum_size [CHAR | BYTE])]
You cannot use a symbolic constant or variable to specify the maximum size; you must use an
integer literal in the range 1 .. 32767.
If you do not specify a maximum size, it defaults to 1.

NUMBER:
You use the NUMBER datatype to store fixed-point or floating-point numbers. Its magnitude
range is 1E-130 .. 10E125. If the value of an expression falls outside this range, you get a
numeric overflow or underflow error. You can specify precision, which is the total number of
digits, and scale, which is the number of digits to the right of the decimal point. The syntax
follows:
NUMBER [(precision, scale)]

To declare fixed-point numbers, for which you must specify scale, use the following form:
NUMBER (precision, scale)

To declare floating-point numbers, for which you cannot specify precision or scale because the
decimal point can "float" to any position, use the following form: NUMBER

2
PL/SQL

To declare integers, which have no decimal point, use this form:

NUMBER (precision) - same as NUMBER (precision,0)

You cannot use constants or variables to specify precision and scale; you must use integer
literals. The maximum precision of a NUMBER value is 38 decimal digits. If you do not specify
precision, it defaults to 38 or the maximum supported by your system, whichever is less.
Scale, which can range from -84 to 127.

DATE:
You use the DATE datatype to store fixed-length datetimes, which include the time of day in
seconds since midnight. The date portion defaults to the first day of the current month; the time
portion defaults to midnight.

VARCHAR2:
You use the VARCHAR2 datatype to store variable-length character data. How the data is
represented internally depends on the database character set. The VARCHAR datatype takes a
required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:
VARCHAR2(maximum_size [CHAR | BYTE])
You cannot use a symbolic constant or variable to specify the maximum size; you must use an
integer literal in the range 1 .. 32767.
Small VARCHARz variables are optimized for performance, and larger ones are optimized for
efficient memory use. The cutoff point is 2000 bytes.

VARIABLES:
These are placeholders that store the values that can change through the PL/SOL
Block.
The General Syntax to declare a variable is:
variable_ name datatype [NOT NULL: = value];
 variable_ name is the name of the variable.
 datatype is a valid PL/SQL datatype.
 NOT NULL is an optional specification on the variable.
 value or DEFAULT valueis also an optional specification, where you can initialize a
variable.
 Each variable declaration is a separate statement and must be terminated by a semicolon.

For example, if you want to store the current salary of an employee, you can use a variable.
DECLARE
salary number (6);
* "salary" is a variable of datatype number and of length 6.

3
PL/SQL

When a variable is specified as NOT NULL, you must initialize the variable when it is
declared.

The initial value of any variable, regardless of its type, is NULL. We can assign values to
variables, using the ":=" operator. The assignment can occur either immediately after the type of
the variable is declared, or anywhere in the executable portion of the program.
For example: The below example declares two variables, one of which is a not null.
DECLARE
salary number (4);
dept varchar2(10) NOT NULL : = "HR Dept";

CONSTANTS:
As the name implies, a constant is a value used in a PL/SQL Block that remains
unchanged throughout the program. A constant is a user-defined literal value. You can declare a
constant and use it instead of an actual value.

For example, if you want to write a program that increases the salary of the employees by 25%,
you can declare a constant and use it throughout the program. Next time you want to increase the
salary again, you can change the value of the constant, which will be easier than changing the
actual value throughout the program.
The General Syntax to declare a constant is:
constant _name CONSTANT datatype: = VALUE;
 Constant_name is the name of the constant i.e. similar to a variable name.
 The word CONSTANT is a reserved word and ensures that the value does not
change.
 VALUE: It is a value that must be assigned to a constant when it is declared. You
cannot assign a value later.
For example, to declare salary_ increase, you can write code as follows:
DECLARE
salary_ increase CONSTANT number (3) := 10;

You must assign a value to a constant at the time you declare it. If you do not assign a value to a
constant while declaring it and try to assign a value in the execution section, you will get an
error.

EXPRESSIONS:
Expressions are constructed using operands and operators. An operand is a variable, constant,
literal, or function call that contributes a value to an expression. An example of a simple
arithmetic expression follows:

4
PL/SQL

-X / 2 + 3
Unary operators such as the negation operator (-) operate on one operand; binary operators such
as the division operator (/) operate on two operands. PL/SQL has no ternary operators.
The simplest expressions consist of a single variable, directly yielding a value. PL/SQL evaluates
an expression by combining the values of the operands in ways specified by the operators. An
expression always returns to a single value. PL/SQL determines the datatype of this value by
examining the expression and the context in which it appears.

OPERATORS:

ARITHMETIC OPERATOR:
Oracle allows arithmetic operators to be used while viewing records from a table or performing
data manipulation operations such as INSERT, UPDATE, and DELETE.
+ Addition - Subtraction / Division * Multiplication
() - Enclosed operation has priority.

Example:
Select Sal + Comm 'Total Salary',
Sal * 12 'Annual Salary', Sal/25 'Per day salary', sal - 500
from emp;

RELATIONAL OPERATOR:
= Equal to > Greater than < Less than <>, !=, ^= Not Equal to
>= Greater than or Equal to <= Less Than or equal to

These all are known as comparison conditions also These ar used in conditions that compare one
expression to another value or expression. They are used in where clause and with conditional
statement. An alias name can not be used in WHERE clause.
Example:
Select * from emp where sal >= 1500;
Select * from emp where sal > 1500;

LOGICAL OPERATOR:
A logical condition combines the result of two component conditions to produce a single result
based on them or inverts the result of a single column. A row is returned only if the overall result
of the condition is true.
You can use several conditions in one WHERE clause using the AND and OR operator.

5
PL/SQL

AND:
The AND operator allows creating an SQL statement based on two or more conditions being
met. It can be used in any valid SQL statement, such as SELECT, INSERT, UPDATE, or
DELETE. The AND operator requires both conditions to be true.

OR:
The OR operator allows creating an SQL statement where records are returned when any one of
the conditions are met means either conditions to be true included in the result set.
It can be used in any valid SQL statement such as SELECT, INSERT, UPDATE or DELETE.

Example:
Select * from emp where sal >= 1500 AND sal <= 2000;
Select * from emp where design= 'Manager' OR design='Clerk';

NOT operator:
The Oracle engine processes all rows in a table and displays only those records that do not
satisfy the specified condition.
The initial value of any variable, regardless of its type, is NULL. We can assign values to
variables using the "=" operator. The assignment can occur immediately after the variable type is
declared, or anywhere in the executable portion of the program.
For example:
DECLARE
a NUMBER := 3;
BEGIN
a := a + 1;
END;

This program has no effect when run, because there are no changes to the database. Simple
programs in PL/SQL. The simplest form of a program has some declarations followed by an
executable section consisting of one or more SQL statements with which we are familiar. The
major nuance is that the form of the SELECT statement is different from its SQL form. After the
SELECT Clause, we must have an INTO clause listing variables, one for each attribute in the
SELECT clause, into which the components of the retrieved tuple must be placed.

Notice we said "tuple" rather than "tuples", since the SELECT statement in PL/SQL only works
if the result of the query contains a single tuple. If the query returns more than one tuple, you
need to use a cursor, as described in the next section. Here is an example:

6
PL/SQL

CREATE TABLE T1(

e INTEGER,
f INTEGER
);
DELETE FROM T1;
INSERT INTO T1 VALUES(1, 3);
INSERT INTO T1 VALUES(2, 4);
/* Above is plain SQL; below is the RL/SQL program. */
DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>;
INSERT INTO TI VALUES(b,a);
END;

CONTROL STRUCTURE:

Overview of PL/SQL Control Structures:

According to the structure theorem, any computer program can be written using the basic control
structures shown in fig. They can be combined in any way necessary to deal with a given
problem.

Control structure figure

The selection structure tests a condition, then executes one sequence of statements instead of
another, depending on whether the condition is true or false. A condition is any variable of
expression that returns a Boolean value (TRUE or FALSE). The iteration structure executes a
sequence of statements repeatedly if a condition holds true. The sequence structure simply
executes a sequence of statements in the order in which they occur.

Conditional Control: IF Statements

Depending on circumstances, alternative actions are often necessary. The IF statement lets you
execute a sequence of statements conditionally. That is, whether the sequence is executed or not
depends on the value of a condition. There are three forms of IF statements: IF-THEN, IF-
THEN-ELSE, and IF-THEN-ELSIF. The CASE statement is a compact way to evaluate a single
condition and choose between many alternative actions.

7
PL/SQL

IF-THEN Statement
The simplest form of IF statement associates a condition with a sequence of statements enclosed
by the keywords, THEN and END IF (not ENDIF), as follows:
IF condition THEN
sequence of statements
END IF;

The sequence of statements is executed only if the condition is true. If the condition is false or
null, the IF statement does nothing. In either case, control passes to the next statement. An
example:
IF sales > quota THEN compute_bonus(empid);
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;

You might want to place brief IF statements on a single line, as in

IF x > y THEN high := x; END IF;

IF-THEN-ELSE Statement:
The second form of the IF statement adds the keyword ELSE followed by an alternative
sequence of statements, as follows:
IF condition THEN
sequence_of_statements1
ELSE
sequence_of_statements2
END IF;

The sequence of statements in the ELSE clause is executed only if the condition is false or null.
Thus, the ELSE clause ensures that a sequence of statements is executed. In the following
example, the first UPDATE statement is executed when the condition is true, but the second
UPDATE statement is executed when the condition is false or null:

IF trans_type = 'CR' THEN

UPDATE accounts SET balance = balance + credit WHERE ...
ELSE
UPDATE accounts SET balance = balance - debit WHERE ...
END IF;

The THEN and ELSE clauses can include IF statements. That is, IF statements can be nested, as
the following example shows:

8
PL/SQL

IF trans _type = 'CR' THEN

UPDATE accounts SET balance = balanee + credit WHERE
ELSE
IF new balance >= minimum balance THEN
UPDATE accounts SET balance = balance - debit WHERE ...
ELSE
RAISE insufficient funds;
END IF;
END IF;

IF-THEN-ELSIF Statement:
Sometimes you want to select an action from several mutually exclusive alternatives. The third
form of IF statement uses the keyword ELSIF (not ELSEIF) to introduce additional conditions,
as follows:

IF condition THEN
sequence_of_statements1
ELSIF condition THEN
sequence of statements2
ELSE
sequence_of_statements3
END IF;

If the first condition is false or null, the ELSIF clause tests another condition. An IF statement
can have any number of ELSEIF clauses; the final ELSE clause is optional. Conditions are
evaluated one by one from top to bottom. If any condition is true, its associated sequence of
statements is executed, and control passes to the following statement. If all conditions are false
or null, the sequence in the ELSE clause is executed.

Consider the following example:

BEGIN
IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500;
ELSE
bonus := 100;
END IF;
INSERT INTO payroll VALUES (emp_id, bonus,..);
END;

9
PL/SQL

If the value of sales is larger than 50000, the first and second conditions are true.
Nevertheless, the bonus is assigned the proper value of 1500 because the second condition is
never tested. When the first condition is true, its associated statement is executed, and control
passes to the INSERT statement.

CASE:

CASE expression:
The PL/SOL CASE statement allows you to execute a sequence of statements based on a
selector. A selector can be anything, such as a variable, a function, or an expression, that the
CASE statement evaluates to a Boolean value. You can use almost any PL/SOL data type except
BLOB, BFILE and composite types as a selector. Unlike the PL/SOL IF statement, the PL/SOL
CASE statement uses a selector instead of a combination of multiple Boolean expressions. The
following illustrates the PL/SQL
CASE statement syntax:
[<<label name>>]
CASE (TRUE | selector]
WHEN expression1 THEN
sequence_of_statements1;
WHEN expression2 THEN
sequence_of_statements2;
.....
WHEN expressionN THEN
sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label name];
Followed by the keyword CASE is a selector. The PL/SQL CASE statement evaluates the
selector only once to decide which sequence of statements to execute. Followed by the selector is
any number of the WHEN clause. If the selector value is equal to the expression in the WHEN
clause, the corresponding sequence of statements sent after THEN keyword will be executed. If
the selector's value is not one of the choices covered by the WHEN clause, the sequence of
statements in the ELSE clause is executed. The ELSE clause is optional, so if you omit the ELSE
clause, PL/SQL will add the following implicit ELSE clause:
To compare the IF and CASE statements, consider the following code that outputs descriptions
of school grades:

IF grade = 'A' THEN

dbms_output.put_line ('Excellent);
ELSIF grade = 'B' THEN

10
PL/SQL

dbms_output.put_line ('Very Good);

ELSIF grade = 'C' THEN
dbms_output.put_line (Good);
ELSIF grade = 'D' THEN
dbms_output.put_line (Fair');
ELSIF grade = 'F' THEN
dbms_output.put_line ('Poor');
ELSE
dbms_output.put_line (No such grade');
END IF;

Notice the five Boolean expressions. In each instance, we test whether the same variable, grade,
is equal to one of five values: 'A', 'B', 'C', 'D', or 'F'. Let us rewrite the preceding code using the
CASE statement, as follows:

CASE grade
WHEN 'A' THEN dbms_output.put _line('Excellent);
WHEN 'B' THEN dbms _output.put _line('Very Good');
WHEN 'C' THEN dbms_output.put_ line('Good');
WHEN 'D' THEN dbms_output.put_line(Fair');
WHEN 'F' THEN dbms_output.put _line('Poor');
ELSE dbms _output.put_line(No such grade');
END CASE;

The CASE statement is more readable and more efficient. So, when possible, rewrite lengthy IF -
THEN-ELSIF Statements as CASE statements.

ITERATIVE CONTROL WITH LOOPS:

LOOP statements let you run a sequence of statements multiple times. You place the-keyword
LOOP before the first statement in the sequence and the keywords END LOOP after the last
statement in the sequence.

FOR LOOP:
The FOR-LOOP statement lets you specify a range of integers, then runs a sequence of
statements once for each integer in the range. In Example: Using the FOR-LOOP in PL/SQL, the
loop displays the number and the square of the number for numbers 1 to 10. Note that you do not
have to declare or initialise the counter in the FoR-LOOP, and any valid identifier can be used
for the name, such as loop_counter.

Using the FOR-LOOP in PL/SQL

11
PL/SQL

BEGIN
-- use a FOR loop to process a series of numbers
FOR loop_counter IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(‘Number:’||TO_CHAR(loop _counter)
||’Square:’|| TO _CHAR (loop counter**2));
END LOOP;
END;

WHILE LOOP:
The WHILE-LOOP Statement associates a condition with a sequence of statements. Before each
iteration of the loop, the condition is evaluated. If the condition is true, the sequence of
statements is executed, and control resumes at the top of the loop. If the condition is false or null,
the loop is bypassed, and control passes to the next statement.

In Example: Using WHILE-LOOP for Control in PL/SQL, the loop displays the number and its
cube while the number is less than or equal to 10.

Using WHILE-LOOP for Control in PL/SQL

DECLARE
i NUMBER:= 1;
i_cubed NUMBER;
BEGIN
WHILE I <= 10 LOOP
I_cubed := i**3;
DBMS_OUTPUT.PUT_ LINE(‘Number:’ || TO_CHAR()
||’Cube: ‘ || TO_CHAR(i_cubed));
I := i+1;
END LOOP;
END;

SIMPLE LOOP WITH EXIT WHEN:

The EXIT -WHEN statement lets you complete a loop if further processing is impossible or
undesirable. When the EXIT statement is encountered, the condition in the WHEN clause is
evaluated. If the condition is proper, the loop is completed, and the control passes to the
following statement.
Using the EXIT-WHEN Statement in PL/SQL:
DECLARE
total NUMBER(9) := 0;
counter NUMBER(6) := 0;

12
PL/SQL

BEGIN
LOOP
counter := counter + 1;
total := total + counter * counter;
EXIT WHEN total > 25000;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Counter: ‘ || TO_CHAR(counter)
||’Total:’|| TO_CHAR(total));

END;

SEQUENTIAL CONTROL WITH GOTO:

The GOTO statement lets you branch to a label unconditionally; however, you usually try to
avoid existing a loop in this manner. The label, an undeclared identifier enclosed by double angle
brackets, must precede an executable statement or a PL/SQL block. When executed, the GOTO
statement transfers control to the labelled statement or block.
Example: Using the GOTO Statement in PL/SQL shows the use of the GOTO statement in a
loop that is testing for prime numbers. When a number can be divided into evenly (no
remainder), then it is not a prime and the loop is immediately exited. Note the use of the SQL
numeric function MOD to check for no (zero) remainder. See "Using Numeric Functions" for
information about SQL numeric functions.

Using the GOTO Statement in PL/SQL

DECLARE
p VARCHAR2(30);
n PLS INTEGER:=31;
BEGIN
FOR j in 2.ROUND(SQRT(n))
LOOP
IF n MOD j = 0 THEN
P := 'is NOT a prime number';
GOTO print_now;
END IF;
END LOOP;
P := 'is a prime number';
<<print_now>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);
END;

13
PL/SQL

SELECT... INTO Statement

The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle
database. The SELECT INTO is a standard SQL query where the SELECT INTO clause is used
to place the returned data into predefined variables.
Declare
enm emp.empname%type;
Begin
select empname into enm from emp
where empno = 7369;
dbms_output.put_line(‘Name:’ ||enm);
end;

Here, the empno was used to retrieve one employee's name and place it in the variable called
enm. The query can also retrieve an entire row as a record with SELECT INTO.

14