PL/SQL Introduction
• PL/SQL is a combination of SQL along with the procedural
features of programming languages.
• Basic Syntax of PL/SQL which is a block-structured language;
this means that the PL/SQL programs are divided and written
in logical blocks of code. Each block consists of three sub-parts
• Every PL/SQL statement ends with a semicolon (;).
• Following is the basic structure of a PL/SQL block −
DECLARE <declarations section>
BEGIN <executable command(s)>
EXCEPTION <exception handling> END;
Pl/SQL Block structure
Explanation
Sections Description
•This section starts with the keyword DECLARE.
Declarations •It is an optional section and defines all variables, cursors, and other
elements to be used in the program.
•This section is enclosed between the keywords BEGIN and END and
Executable it is a mandatory section.
Commands •It consists of the executable PL/SQL statements of the program.
•It should have at least one executable line of code.
• This section starts with the keyword EXCEPTION.
Exception • This optional section contains exception(s) that handle errors in
Handling the program.
The 'Hello World'
Example
DECLARE
msg varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END; /
Types of PL/SQL
block
PL/SQL blocks
are of mainly
two types.
Anonymous
Named Blocks
blocks
Anonymous blocks:
Unnamed
Below are few more characteristics
of
locks.
•These blocks don't have any reference name
specified for them.
•These blocks start with the keyword 'DECLARE'
or 'BEGIN'.
•These blocks can have all three sections of the
block, in which execution section is mandatory,
the other two sections are optional.
Named
blocks:
Below are few more characteristics of Named blocks.
•These blocks can be called from other blocks.
•The block structure is same as an anonymous block,
except it will never start with the keyword 'DECLARE'.
Instead, it will start with the keyword 'CREATE' which
instruct the compiler to create it as a database object.
•These blocks can be nested within other blocks. It can
also contain nested blocks.
blocks are basically of two types:
•Procedure
•Function
Unnamed block
Examples
Not possible in
MySQL but possible
with oracle SQL
Variable
Syntax for declaring variable:
Following is the syntax for declaring variable:
variable_name [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT initial_value]
Naming rules for PL/SQL
variables
The variable in PL/SQL must follow some naming rules like
other programming languages.
The variable_name should not exceed 30 characters.
The name of the variable must begin with ASCII letter. The
PL/SQL is not case sensitive so it could be either lowercase or
uppercase. For example: v_data and V_DATA refer to the same
variables.
You should make your variable easy to read and understand,
after the first character, it may be any number, underscore (_)
or dollar sign ($).
NOT NULL is an optional specification on the variable.
Example
[Link]
2. a integer := 30;
3. b integer := 40;
4. c integer;
5. f real;
[Link]
7. c := a + b;
8. dbms_output.put_line('Value of c: ' || c);
9. f := 100.00
10. dbms_output.put_line('Value of f: ' || f);
[Link];
Variable Scope in PL/SQL
PL/SQL allows nesting of blocks. A program block can contain
another inner block. If you declare a variable within an inner
block, it is not accessible to an outer block. There are two
types of variable scope:
Local Variable: Local variables are the inner block variables
which are not accessible to outer blocks.
Global Variable: Global variables are declared in outermost
block.
Constant
Syntax to declare a constant:
constant_name CONSTANT datatype := VALUE;
Example of Constant
DECLARE
-- constant declaration
pi constant number := 3.141592654;
radius number(5,2);
BEGIN
-- processing
radius := 9.5;
area := pi * radius * radius;
-- output
dbms_output.put_line('Area: ' || area);
END;
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;
Unnamed block Examples-
•
•
SQL
SQL> declare // For loop
2 a number:=1;
• 3 begin
• 4 for a in 1..10 loop
• 5 dbms_output.put_line(a);
• 6 end loop;
• 7 end;
•
• SQL> declare //Simple loop
• 2 a number:=1;
• 3 begin
• 4 loop
• 5 dbms_output.put_line(a);
• 6 a:=a+1;
• 7 exit when a>10;
• 8 end loop;
• 9 end;
•
PL/SQL Literals
PL/SQL If
Syntax: (IF-THEN statement):
IF condition
THEN
Statement: {It is executed when condition is true}
END IF;
IF condition
THEN
{...statements to execute when condition is TRUE...}
ELSE
{...statements to execute when condition is FALSE...}
END IF;
IF condition1
THEN
{...statements to execute when condition1 is TRUE...}
ELSIF condition2
THEN
{...statements to execute when condition2 is TRUE...}
END IF;
IF condition1
THEN
{...statements to execute when condition1 is TRUE...}
ELSIF condition2
THEN
{...statements to execute when condition2 is TRUE...}
ELSE
{...statements to execute when both condition1 and condition2 are FALSE...}
END IF;
Unnamed block Examples-
•
• 2
SQL
SQL> declare
a number:=1;
//While loop
• 3 begin
• 4 while a<11 loop
• 5 dbms_output.put_line(a);
• 6 a:=a+1;
• 7 end loop;
• 8 end;
•
• SQL> declare // if-else
• 2 a number(4);
• 3 begin
• 4 for a in 5..15 loop
• 5 if mod(a,5)=0 then
• 6 dbms_output.put_line(a);
• 7 else
• 8 dbms_output.put_line('value'||a);
• 9 end if;
• 10 end loop;
• 11 end;
Unnamed block
MySQL Examples-
We can use stored
procedure instead of
unnamed block in
MySQL
DECLARE VARIABLE
DECLARE
a integer := 30;
b integer := 40;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 100.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
GLOBAL VARIABLE
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;
/
IF ELSE
IF condition
THEN
Statement: {It is executed when condition is true}
END IF;
IF condition
THEN
{...statements to execute when condition is TRUE...}
ELSE
{...statements to execute when condition is FALSE...}
END IF;
IF condition1
THEN
{...statements to execute when condition1 is TRUE...}
ELSIF condition2
THEN
{...statements to execute when condition2 is TRUE...}
IF ELSE IN PL/SQL
DECLARE
a number(3) := 500;
BEGIN
IF( a < 20 ) THEN
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
PL/SQL CASE STATEMENT
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
Example
DECLARE
grade char(1) := 'A';
BEGIN
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('Average');
when 'F' then dbms_output.put_line('Passed with Grace');
else dbms_output.put_line('Failed');
END CASE;
END;
PL/SQL Loop
Syntax for a basic loop:
LOOP
Sequence of statements;
END LOOP;
Exit Loop
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;
Example
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>10;
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
WHILE LOOP
WHILE <condition>
LOOP statements;
END LOOP;
Example
DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
FOR LOOP
FOR counter IN initial_value .. final_value LOOP
LOOP statements;
END LOOP;
Example
BEGIN
FOR k IN 1..10 LOOP
-- note that k was not declared
DBMS_OUTPUT.PUT_LINE(k);
END LOOP;
END;
Reverse in For Loop
DECLARE
VAR1 NUMBER;
BEGIN
VAR1:=10;
FOR VAR2 IN REVERSE 1..10
LOOP
DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
END LOOP;
END;
PL/SQL Procedure
•The PL/SQL stored procedure or simply a
procedure is a PL/SQL block which performs
one or more specific tasks.
•The procedure contains a header and a body.
•Header: The header contains the name of the
procedure and the parameters or variables
passed to the procedure.
•Body: The body contains a declaration
section, execution section and exception
section similar to a general PL/SQL block.
Stored Procedure
CREATE PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Stored Procedure-
Parameters
In MySQL, a
parameter has
one of three
modes:
IN OUT INOUT
Stored Procedure-
Parameters
IN – is the default mode. When you define an IN parameter in a
stored procedure, the calling program has to pass an argument
to the stored procedure.
OUT – the value of an OUT parameter can be changed inside the
stored procedure and its new value is passed back to the calling
program
INOUT – an INOUT parameter is the combination of IN
and OUT parameters. It means that the calling program
may pass the argument, and the stored procedure can modify
the INOUT parameter and pass the new value back to the
calling program.
Suppose I have created a table as
create table user(id number(10) primary key,n
ame varchar2(100));
I have to insert records
create or replace procedure "INSERTUSER"
(id IN NUMBER, name IN VARCHAR2)
is
begin
insert into user values(id, name);
end;
Calling a Procedure
BEGIN
insertuser(101,’ABC’);
dbms_output.put_line('record inserted ');
END;
Without parameter Example
CREATE PROCEDURE Alluser() BEGIN
SELECT * FROM user;
END
begin
Allstud();
End;
The IN parameter example
CREATE PROCEDURE Alluser(IN Uname VARCHAR(25))
BEGIN
SELECT * FROM user where Name=Uname;
END
Begin
Alluser(‘ABC’);
End;
DECLARE
a number;
b number;
c number;
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;
Consider table Stud(Roll, Att,Status)
Write a PL/SQL block for following requirement.
Roll no. of student will be entered by user. Attendance of
roll no. entered by user will be checked in
Stud table. If attendance is less than 75% then display the
message “Term not granted” and set the
status in stud table as “D”. Otherwise display message
“Term granted” and set the status in stud table as
“ND”
Declare
mroll number(10);
matt number(10);
Begin
mroll:= &mroll;
select att into matt from stud11 where roll = mroll;
if matt<75 then
dbms_output.put_line(mroll||'is detained');
update stud11 set status='D'where roll=mroll;
else
dbms_output.put_line(mroll||'is Not detained');
update stud11 set status='ND'where roll=mroll;
end if;
End;
Function
A function is a named PL/SQL Block which is similar to a procedure. The
major difference between a procedure and a function is, a function must always
return a value, but a procedure may or may not return a value.
Syntax to create a function is
CREATE [OR REPLACE] FUNCTION function_name
[parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
CREATE OR REPLACE FUNCTION
employee_details RETURN
VARCHAR(20);
IS
emp_name VARCHAR(20);
BEGIN
SELECT first_name INTO emp_name
FROM emp WHERE empID = '100';
RETURN emp_name;
END;
Function returns the total number of CUSTOMERS in the
customers table
CREATE FUNCTION totalCust
RETURN number
IS
total number(2) := 0;
BEGIN
SELECT count(*) into total FROM
customers;
RETURN total;
END;
Call Function in PL/SQL
DECLARE c number(2); BEGIN
c := totalCust();
dbms_output.put_line('Total no. of Customers:
' || c);
END;
Select totalCust() from dual;
Cursor
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.
Two Types of Cursors
[Link] Cursor
[Link] Cursor
Implicit cursors
The implicit cursors are automatically generated
while an SQL statement is executed. These are
created by default to process the statements
when DML statements like INSERT, UPDATE,
DELETE etc. are executed.
Explicit cursors
The Explicit cursors are defined by the
programmers to gain more control over the
context area. These cursors 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.
Implicit Cursor
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 5000;
IF sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
END IF;
END;
Explicit Cursor
Syntax of explicit cursor
Following is the syntax to create an explicit
cursor:
CURSOR cursor_name IS select_statement;
Step for Defining Explicit Cursor
Execute the following program to retrieve the customer name and address.
DECLARE
c_name [Link]%type;
c_addr [Link]%type;
CURSOR c_customers is SELECT name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
PL/SQL Exception Handling
An error occurs during the program execution is called
Exception in PL/SQL.
PL/SQL facilitates programmers to catch such conditions
using exception block in the program and an
appropriate action is taken against the error
condition.
There are two type of exceptions:
System-defined Exceptions
User-defined Exceptions
Syntax for exception handling:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
........ WHEN others THEN
exception3-handling-statements
END;
DECLARE
c_id [Link]%type := 8;
c_name [Link]%type;
c_addr [Link]%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 ('Addre: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
Syntax for raising an exception
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
PL/SQL Trigger
• Trigger is stored into database and invoked repeatedly,
when specific condition match.
• Triggers are stored programs, which are automatically
executed or fired when some event occurs.
• Triggers are written to be executed in response to any of
the following events.
• A database manipulation (DML) statement.
• A database definition (DDL) statement.
• A database operation.
Advantages of Triggers
• Trigger generates some derived column values
automatically
• Enforces referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions