Barter Sessions

PL/SQL
Introduction to PL/SQL
• What is PL/SQL?

– Developed by Oracle corporation in the 1980s.

– It’s a procedure language extension for SQL and

Oracle relational database.
PL/SQL Block Basic syntax
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
PL/SQL Block description
• Declarations
– This section starts with the keyword DECLARE.
– It is an optional section and defines all variables, cursors, etc.

• Executable Commands
– It’s enclosed between keywords BEGIN and END.
– It’s a mandatory section.
– It consists of executable PL/SQL statements of the program.

• Exception Handling
– This section starts with the keyword EXCEPTION.
– This section is also an optional section.
– It contains exceptions that handle errors in the program.
Simple PL/SQL program
Print natural numbers from 1 to 5.

declare
i number;
begin
i:=1;
loop
dbms_output.put_line(i);
i:=i+1;
exit when i > 5;
end loop;
end;
Procedures
• It’s a named block of statement.
• It may or may not return a value.
SYNTAX:

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, …])]
{IS | AS}
BEGIN
<procedure body>
END procedure_name;
Simple program using Procedure
create or replace procedure topperStudent
as
name students.s_name%type;
begin
select name from student where marks=(select max(marks) from student)
dbms_output.put_line(name);
end;

• To execute, there are two ways to do it:

– 1) exec topperStudent;
– 2) begin topperStudent end;
Cursor
• A cursor is a temporary area created in the main memory
when a SQL statement is executed. A cursor contains
information on a select statement and the rows of data
accessed by it.
• This temporary work area is used to store the data retrieved
from the database, and manipulate this data.
• A cursor can hold more than one row, but can process only
one row at a time. The set of rows the cursor holds is called
the active set.
• There are two types of cursors in PL/SQL:
– Implicit
– Explicit
Implicit Cursor
• Implicit cursors get created when you execute
DMA queries like Select, insert, delete, update.
• Oracle gives some useful attributes on this
implicit cursors to help us check the status of
DML operations
Attribute Usage

%FOUND If DML statement affects at least one row returns

TRUE else returns FALSE

%NOTFOUND If DML statement affects at least one row returns

FALSE else returns TRUE

%ROWCOUNT Return the number of rows affected by the DML

operations INSERT, DELETE, UPDATE, SELECT
Example of Implicit Cursor
create or replace procedure updateFees(newFee int)
as
var_rows number;
begin
update student set fees=newFee;
if SQL%FOUND then
var_rows :=SQL%ROWCOUNT;
dbms_output.put_line('The fees of '|| var_rows || ' students was updated');
else
dbms_output.put_line('Some issue in updating');
end if;
end;
Explicit Cursor
• They must be created when you are executing a
SELECT statement that returns more than one
row in a PL/SQL procedure or a function.
• Even though the cursor stores multiple records,
only one record can be processed at a time,
which is called as current row. When you fetch a
row the current row position moves to next row.
• Both implicit and explicit cursors have the same
functionality, but they differ in the way they are
accessed.
 Explicit Cursor : Example 1
• Show the average fees paid by students of each
department.

declare cursor c1
is
select deptName as Department,avg(fees) as Average_Fees from student
natural join department group by deptName;
rec1 c1%rowtype;
begin
for rec1 in c1 loop
dbms_output.put_line(rec1.Department ||' '||rec1.Average_Fees);
end loop;
end;
Explicit Cursor : Example 2
Show the department wise student details.

declare cursor c1
is
select distinct deptName from department;
cursor c2(dept varchar)
is
select name,marks from student natural join department where deptName=dept ;
rec1 c1%rowtype;
rec2 c2%rowtype;
begin
for rec1 in c1 loop
dbms_output.put_line(rec1.deptName);
for rec2 in c2(rec1.deptname) loop
dbms_output.put_line(rec2.name||' '||rec2.marks);
end loop;
dbms_output.put_line('');
end loop;
end;
Procedure with cursor
• Statement: Write a procedure which will display details of all students from a
given department:

create or replace procedure listStudents(dept varchar)

is
cursor c1 is
select rollNo,name,marks from student natural join department where deptName=dept;
rec1 c1%rowtype;
begin
for rec1 in c1 loop
dbms_output.put_line(rec1.rollNo||' '||rec1.name||' '||rec1.marks);
end loop;
end;

To Execute:
begin
listStudents('production');
end;
PL/SQL Functions
• A PL/SQL function is same as a procedure except
that it always returns a value.
• General 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];
Functions: Example 1
• Write a function which will return the total fees
collected for a given department.
create or replace function totalFees(dept varchar)
return int
is
total int;
begin
select sum(fees) into total from student natural join department
where deptName=dept;
return total;
end;

To execute: select totalFees('production') from dual;

Functions: Example 2
• Get the topper student from a given department
create or replace function getTopper(dept varchar)
return varchar
is
topper varchar(50);
begin
select name into topper from student natural join department
where deptName=dept and marks=(select max(marks) from
student natural join department where deptName=dept) ;
return topper;
end;

• To execute: select getTopper(‘Civil’) from dual;

Triggers
• Triggers are stored routines, which are
automatically executed when some events
occur.
• Triggers are written to be executed in response
to any of the following events:
• DML - DELETE, INSERT, or UPDATE.
• DDL - CREATE, ALTER, or DROP .
• A database operation (SERVERERROR, LOGON, LOGOFF,
STARTUP, or SHUTDOWN).
Trigger: General Syntax
CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name [ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION WHEN ...
-- exception handling
END;
 Trigger : Before Insert
• Fired before the INSERT operation is executed.
• Check for email availability before inserting information of new
student.

CREATE OR REPLACE TRIGGER checkEmail

BEFORE INSERT ON student FOR EACH ROW
declare
rowcount int;
begin
SELECT COUNT(*) into rowcount FROM student WHERE email = :NEW.email;
IF rowcount<>0 THEN
raise_application_error(-20001,'Email Already Registered');
END IF;
END;
Trigger: After Insert
CREATE OR REPLACE TRIGGER cancelAdmit
AFTER INSERT ON student
REFERENCING NEW AS n
FOR EACH ROW
declare
rowcount int;
begin
if :n.fees <10000 then
dbms_output.put_line('Admission cancelled due to less
donation');
end if;
END;
Trigger Before Update
• Give Notification to Admin of Email change.
CREATE OR REPLACE TRIGGER checkUpdatedEmail
BEFORE UPDATE ON student
REFERENCING NEW AS n
FOR EACH ROW
declare
rowcount int;
begin
dbms_output.put_line('The email has been changed to: '
|| :n.email);
END;
Trigger After Delete
• Remove all students of the department, once the
department is deleted.

CREATE OR REPLACE TRIGGER cleanStudents

AFTER DELETE ON department
FOR EACH ROW
declare
dept int;
begin
dept := :OLD.deptNo;
delete from student where deptNo=dept;
END;
Package
• A package is a schema object that groups logically
related PL/SQL types, items, and subprograms.
• A package will have two mandatory parts:
– Package specification
• It just DECLARES the types, variables, constants, exceptions,
cursors, and subprograms that can be referenced from
outside the package
– Package body or definition
• The package body has the codes for various methods declared
in the package specification and other private declarations,
which are hidden from code outside the package.
Syntax for packages
• Package Specification

CREATE PACKAGE cust_sal

AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;

Execute this: exec cust_sal;

• Package body

CREATE OR REPLACE PACKAGE BODY cust_sal

AS
PROCEDURE find_sal(c_id customers.id%TYPE)
IS
c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal FROM customers WHERE id = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END cust_sal;

To Execute: exec cust_sal.find_sal(4);

Exceptions
• An error condition during a program execution is
called an exception in PL/SQL.
• PL/SQL supports programmers to catch such
conditions using EXCEPTION block in the
program
• There are two types of exceptions:
– System-defined exceptions
– User-defined exceptions
General Syntax
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
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;
System Defined Exception
DECLARE
dept department.deptNo%type:=10;
name department.deptName%type;
BEGIN
SELECT deptName into name FROM department where deptNo=dept;
DBMS_OUTPUT.PUT_LINE ('Name: '|| name);
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('No such department!');
WHEN others THEN
dbms_output.put_line('Error!');
END;

Here in this example, we have used system defined exception:

no_data_found
User defined exception
DECLARE
dept department.deptNo%type :=-23;
name department.deptName%type;
ex_invalid_deptNo EXCEPTION;
BEGIN
IF dept <= 0 THEN
RAISE ex_invalid_deptNo;
ELSE
SELECT deptName into name
FROM department
WHERE deptNo = dept;
DBMS_OUTPUT.PUT_LINE (‘Department: '|| name);
END IF;
EXCEPTION
WHEN ex_invalid_deptNo THEN
dbms_output.put_line('Department number must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such department!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
Muchas Gracias!

• Thank you!
• For any queries, ask me on my profile

• http://bbarters.com/user/ksjoshi88
or
• http://bbarters.com/user/prath257
• http://bbarters.com/user/ritesh