PL/SQL

Database programming
language
PL/SQL ENVIRONMENT
PL/SQL - Basic Syntax

DECLARE <declarations section>

BEGIN <executable
command(s)> EXCEPTION
<exception handling>
END;

*It is not a case sensitive

SCALAR DATA TYPES
The 'Hello World' Example

DECLARE
message varchar2(20):= 'Hello,
World!';
BEGIN
dbms_output.put_line(message);
END;
/
 EXAMPLE-1
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;
 PL/SQL If

 PL/SQL supports the programming

language features like conditional
statements and iterative statements.

 Its programming constructs are similar

to how you use in programming
languages like Java and C++.
 Syntax: (IF-THEN-ELSE statement):
Syntax: (IF-THEN statement):

IF condition IF condition
THEN THEN
Statement: {...statements to exe
{It is executed when c cute when condition
ondition is true} is TRUE...}
END IF; ELSE
{...statements to exe
cute when condition
is FALSE...}
END IF;
 EXAMPLE
DECLARE
a number(3) := 500;
BEGIN
-- check the boolean condition using if state
ment
IF( a < 20 ) THEN
-- if condition is true then print the followin
g
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;
 CASE STATEMENT
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 wit
h Grace');
else dbms_output.put_line('Failed');
END CASE;
END;
Syntax for a basic loop: Syntax for a loop EXIT:

LOOP LOOP
Sequence of statemen statements;
ts; EXIT;
END LOOP; (or)
{ EXIT WHEN conditi
on;}
END LOOP;
 EXAMPLE FOR LOOP EXIT:
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>10;
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
/
Syntax of while loop: Syntax of for loop:

WHILE <condition> FOR counter IN

LOOP statements; initial_value .. final_val
END LOOP; ue LOOP statement
s;
END LOOP;
while loop:
DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
FOR LOOP
BEGIN
FOR k IN 1..10 LOOP
-- note that k was not declared
DBMS_OUTPUT.PUT_LINE(k);
END LOOP;
END;
FOR LOOP
DECLARE
VAR1 NUMBER;
BEGIN
VAR1:=10;
FOR VAR2 IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE (VAR1*VA
R2);
END LOOP;
END;
 1.WRITE A PL/SQL PROGRAM FIND THE SUM OF N

NUMBERS .
DECLARE
n number;
i number;
sum number:=0;
BEGIN
n:=:n;
For i in 1..n loop
sum:=sum+i;
End loop;
dbms_output.put_line(sum);
End;
/
2.WRITE A PL/SQL PROGRAM TO FIND
ALL ARITHMETIC OPERATIONS.

DECLARE BEGIN
a number;
a:=:a; b:=:b;
b number;
c:=a+b;
c number;
d:=a*b;
d number;
e:=a/b;
f:=a mod b;
e number;
f number;dbms_output.put_line(‘sum is’||c)
dbms_output.put_line(‘multiplicat
on is’||d);
dbms_output.put_line(‘division is’
e);
dbms_output.put_line(‘mod is’||f);
3.WRITE A PL/SQL PROGRAM TO FIND GIVEN
NUMBER IS EVEN OR ODD.
 DECLARE

a number;
BEGIN
a:=:a;
If(mod(a,2)=0) then
dbms_output.put_line(‘even number’);
Else
dbms_output.put_line(‘odd number’);
End if;
End;
4.TO FIND THE FACTORIAL OF A GIVEN
NUMBER
 DECLARE

n number; fact number:=1;

BEGIN
n:=:n;
While(n>=1)
Loop
fact:=n*fact; n:=n-1;
End loop;
dbms_output.put_line(‘factorial is’||fact);
End;
5.TO FIND THE SUM OF DIGITS OF GIVEN NUMBER.

DECLARE
a number;
b number;
sum number:=0;
BEGIN
a:=:a;
While(a<>0)
loop
b:=floor(mod(a,10));
sum:=sum+b;
a:=a/10;
End loop;
dbms_output.put_line(‘sum of digits’||sum);
End;
selecting and inserting
values:
 SELECT SYNTAX;
 select field name into PL/SQL value from table name
where condition;

 EXAMPLE

 declare
 s number;
 begin
 select sal into s from emp where ename='KING';
 dbms_output.put_line(s);
 end;
Inserting values

Declare
n number;
area number;
begin n:=:n;
area:=4*n;
insert into area values(n,area);
end;
create table area(s number,area number);
select * from area;
example
Declare
s number;
Begin
select sal into s from emp where empno=7900;
if(s<1000)then s:=s+500;
dbms_output.put_line(s);
else s:=s+1000;
dbms_output.put_line(s);
end if;
end;
create table emp1 as select sal*12 as ann_sal from emp
where job='MANAGER';
select * from emp1
PL/SQL Procedure
 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.
 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.
PL/SQL Create Procedure

Syntax for creating procedure:

 CREATE [OR REPLACE] PROCEDURE procedure_n
ame
 [ (parameter [,parameter]) ]
 IS
 [declaration_section]
 BEGIN
 executable_section
 [EXCEPTION
 exception_section]
 END [procedure_name];
Example:
Procedure for sum of n
numbers
create or replace procedure
sumn(n in number)as
i number;
sumn number:=0;
begin
for i in 1..n
loop
sumn:=sumn+i;
end loop;
Contd..
dbms_output.put_line(sumn);
end;

declare
n number;
begin
n:=:n;
sumn(n);
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 to create a function:

CREATE [OR REPLACE] FUNCTI

ON function_name [parameters]
[(parameter_name [IN | OUT | IN
OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
 < function_body >
END [function_name];
Write a function to find
factorial of a number
 create or replace function fact1(n1 in
number)return number is
 i number;
 f number:=1;
 Begin
 for i in 1..n1 loop
 f:=f*i;
 end loop;
 return(f);
 end;
Contd..
Declare
n1 number(20);
m number;
Begin
n1:=:n1;
m:=fact1(n1);
dbms_output.put_line(m);
end;
PL/SQL Cursor
 When an SQL statement is processed,
Oracle creates a memory area known as
context area. A cursor is a pointer to this
context area. It contains all information
needed for processing the statement. In
PL/SQL, the context area is controlled by
Cursor. A cursor contains information on a
select statement and the rows of data
accessed by it.
 A cursor is used to referred to 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.
Creating tables
CREATE TABLE CUSTOMERS(
 ID INT NOT NULL,
 NAME VARCHAR (20) NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR (25),
 SALARY DECIMAL (18, 2),
 PRIMARY KEY (ID)
);
Inserting values
 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
 VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

 VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

 VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );


 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

 VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );


 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

 VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

 VALUES (6, 'Komal', 22, 'MP', 4500.00 );
Implicit cursors
 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
 Explicitcursors 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;
Contd..
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
Explicit cursors
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;
Explicit Cursors
PL/SQL Trigger

Trigger is invoked by Oracle engine

automatically whenever a specified
event occurs. 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.
PL/SQL Trigger
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).
Triggers could be defined on the
table, view, schema, or database with
which the event is associated.
Creating a trigger
 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;
Advantages of Triggers
These are the following 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
Trigger example
 create or replace trigger t1 before insert on cust for
each row
 begin
 if:new.age>30then
 :new.age:=30;
 end if;
 end;

 desc cust;

 insert into cust values(101,'ABC',35,'Hyderabad');