Lecture Notes Unit5 chapter 15 PL/SQL Programming

RDBMS - Unit V
Chapter 15
PL/SQL
Prepared By
Dr. S.Murugan, Associate Professor
Department of Computer Science,
Alagappa Government Arts College, Karaikudi.
(Affiliated by Alagappa University)
Mailid: muruganjit@gmail.com
Reference Book:
LEARN ORACLE 8i, JOSE A RAMALHO

PL/SQL
➢ PL/SQL is an extension of the SQL language.
➢ It is a procedural language.
➢ PL/SQL combines the SQL language’s ease of data
manipulation and the procedural language’s ease of
programming.
➢ With the PL/SQL language, we can create schema
objects, including Stored procedures and functions,
Packages, Triggers, Cursors.

PL/SQL
➢ Stored procedures and functions. A stored procedure
is a PL/SQL program that can be enabled by an
application, a trigger, or an Oracle tool.
➢ The basic difference between a procedure and a
function is that a procedure executes its commands
and a function executes commands and returns a
result.
➢ Packages. A package is a file that contains a group of
functions, cursors, stored procedures, and variables in
one place.

PL/SQL
➢ Triggers. A trigger is a PL/SQL program that is stored
in the database and executed immediately before or
after the INSERT, UPDATE, and DELETE
commands.
➢ Cursors. Oracle uses workspaces to execute the SQL
commands. Through PL/SQL cursors, it is possible to
name the workspace and access its information.

PL/SQL BLOCK
➢ The basic structure of PL/SQL block is given below:

PL/SQL BLOCK
There are three parts to the PL/SQL block:
➢ Declaration section/Declare (optional), in which all
the objects are declared.
➢ Execution section/Begin, in which the PL/SQL
commands are placed.
➢ Exception section/Exception (optional), in which the
errors are handled.

PL/SQL BLOCK - Declaration Section
In the declaration section, the developer can perform the
following:
➢ Declare the name of an identifier - PI.
➢ Declare the type of identifier (constant or variable).
➢ Declare the data type of the identifier. - REAL
➢ Assign (initialize) contents to the identifier. – 3.14
Ex: PI CONSTANT REAL := 3.14

PL/SQL BLOCK - Variables
➢ The variables can contain any data type that is valid
for SQL and Oracle (such as char, number, long,
varchar2, and date) in addition to these types:
➢ Boolean Can be assigned the values True, False, or
NULL.
➢ Binary_integer Accepts numbers between
-2,147,483,647 and 2,147,483,647.
➢ Positive Accepts numbers from 1 to 2,147,483,647.
➢ Natural Accepts numbers from 0 to 2,147,483,647.

➢ %type Assigns to the variable that is being created the
same data types used by the column that is being used.
To access the field of deptno from dept. table and
assigned to deptp.
deptp := dept.deptno%type;
➢ %rowtype Declares a composed variable that is
equivalent to the row of the table.
➢ After the variable is created, the fields of the table can
be accessed, using the name of this variable followed
by a period and the name of the field:
Empline := emp%rowtype

➢ After the variable is created, you can use the following
expression to assign a column to another variable:
Newvar : = empline.ename;

➢ There are two ways to assign values to a variable. The
first is to use the assignment operator “:=”:
tot := price * margin;
increase:= sal * 1.10;
➢ The second way to assign values to variables is to use
the SELECT command to assign the contents of the
fields of a table to a variable:
SELECT sal * 0.10 INTO increased FROM
emp WHERE empno = emp_id;

PL/SQL BLOCK - Constant
➢ The declaration of a constant is similar to the
declaration of a variable, except to add the keyword
Constant after the name of the constant:
PI CONSTANT REAL := 3.14;
➢ Each variable or constant must be specified with its
name, type, and, optionally, initial value. All the rows
must end with a semicolon:
DECLARE
Cust_name varchar2 (20);
Credit number (5,2) : = 100;

PL/SQL BLOCK - Execution Section
➢ This section begins with the Begin declaration.
➢ This section can contain SQL commands, logical
control commands, and assignment commands, as well
as other commands.
➢ All commands are always end with a semicolon.

PL/SQL BLOCK - Exception
➢ In this section, the developer can use commands to
handle an error that occurs during the execution of a
PL/SQL program.

How PL/SQL Works
➢ PL/SQL is an engine that makes up part of the Oracle
server.
➢ It executes the procedural commands and passes the
SQL commands for the Oracle server to process.
➢ PL/SQL blocks can be created with any of the
processing editors. (Ex: Windows Notepad)
➢ To execute a PL/SQL program or script, you can use
SQL*Plus, which allows creating, storing, and
executing PL/SQL blocks.

Example Program
➢ Step 1: Type the program in editor and execute using
@ symbol.
➢ SQL> set serveroutput on;

Example Program for variable and constant

Control Structures
➢ PL/SQL has some commands to control the execution
flow of the program. They are responsible for decision
making inside the application.
➢ control structures in PL/SQL, which can be divided
into condition control structures (selection), sequence
structures, and repetition or iteration structures.
➢ Different types of structure explained in the figure
15.3.

The IF..THEN Command
➢ The IF..THEN command evaluates a condition and
executes one or more rows of commands only if the
analyzed condition is true.
➢ It has two variations as follows:

➢ In syntax1, the commands that appear between the
THEN and END IF clauses will be executed only if
the condition is true.
Example:
declare
sal number(7,2);
begin
select salary into sal from emp2 where eno=101;
IF SAL>= 5000 THEN
UPDATE EMP2 SET salary = salary*1.2;
END IF;
dbms_output.put_line(sal);
end;
/

In syntax2, more than one condition can be analyzed
and, therefore, several actions can be executed:
1. create table emp2(eno number(3), salary number(7,2))
2. insert into emp2 values (102, 1500)
3. Type the following program and execute:
declare
sal number(7,2);
begin
select salary into sal from emp2 where eno=101;
IF sal<2000 THEN
UPDATE EMP2 SET salary = salary * 1.2;
ELSIF sal <3000 THEN
ELSE
END IF;
dbms_output.put_line(sal);
end;

➢ If the main condition is true, the following commands
will be executed until another ELSIF or ELSE clause
is found.
➢ If the first ELSIF condition is false, the program tests
the second condition, and so on.
➢ When the first true condition is found, its commands
are executed, and the program jumps to the row after
the END IF command.

The LOOP Command
➢ The LOOP command initializes a group of commands
indefinitely, or until a condition forces a “break” in the
loop and the execution of the program to another
place.
➢ The EXIT command is responsible for interrupting the
loop execution:

The LOOP Command - Example
LOOP with IF Loop with WHEN
declare
I number(3);
begin
I := 1;
Loop
I := I+1;
DBMS_OUTPUT.PUT_LINE(I);
IF I>= 30 THEN
EXIT;
END IF;
END LOOP;
END;
/
declare
I number(3);
begin
I := 1;
Loop
I := I+1;
DBMS_OUTPUT.PUT_LINE(I);
EXIT WHEN I>= 30;
END LOOP;
END;
/
➢ The following loop repeated until the counter variable
I become 30.

The FOR..LOOP Command
➢ The FOR..LOOP command is a variation of the LOOP
command.
➢ Here, the commands are automatically executed until
an evaluated condition returns false.
Syntax:
FOR <counter> IN [REVERSE] <initial_value> . . <final_value>
LOOP
<commands>
END LOOP;

The FOR LOOP Command - Example
Example:
declare
j number(3);
begin
FOR j IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(j);
END LOOP;
END;
/
➢ The FOR command initializes a control variable called J, which
has the initial value of 1.
➢ The commands will be executed until END LOOP is found.
➢ Then, the control returns to the FOR command, which
increments the variable and analyzes the master condition, i.e.,
whether the value of J is less than the final value.
➢ When this happens the cycle is reinitialized.

The FOR LOOP Command - Example
➢ The REVERSE clause makes the counter start with the
highest value and decrease until it reaches the lowest
value.
declare
j number(3);
begin
FOR j IN reverse 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(j);
END LOOP;
END;
/

The WHILE Command
➢ The WHILE command is another control structure.
This structure only executes the commands if the
analyzed condition is true.
declare
x number(3);
begin
x:=1;
while x<=20 loop
DBMS_OUTPUT.PUT_LINE(x);
x:=x+1;
END LOOP;
END;
/

Integrating SQL in a PL/SQL Program
➢ SQL commands can be inserted in the execution
section of a PL/SQL block.
➢ The developer can use (reference) a variable or
constant declared in the declaration section.
➢ Inside a PL/SQL program the SELECT command uses
a new clause called INTO that allows transferring the
contents of the fields in a row to the variables in
memory.
➢ Command: SET SERVEROUTPUT ON, GET
(Retreive the file), RUN (@ Execute the program)

Integrating SQL in a PL/SQL Program
Declare
Var_name number (7,2) ;
Begin
SELECT salary INTO Var_name FROM emp2 where eno
= 101;
dbms_output.put_line('salary=' || Var_name);
End;
/
➢ Note that to attribute contents to a field in a variable,
the SELECT command must return only one row;
➢ otherwise an error will occur, since several values are
being assigned to only one variable.

Lecture Notes Unit5 chapter 15 PL/SQL Programming

More Related Content

What's hot

Similar to Lecture Notes Unit5 chapter 15 PL/SQL Programming

More from Murugan146644

Recently uploaded

Lecture Notes Unit5 chapter 15 PL/SQL Programming