PARULINSTITUTEOF ENGINEERING &TECHNOLOGY

FACULTY OF ENGINEERING & TECHNOLOGY

PARUL UNIVERSITY

Database Management System

UNIT-9
PL/SQL Concepts
Computer Science & Engineering
Nilesh Khodifad (Asst.Professor. PIET-CSE)
➢ View
➢ Trigger
➢ PL/SQL
➢ Cursor
➢ Stored Procedure
➢ Function
Views
➢ The view is a virtual table. It does not physically exist. Rather, it is
created by a query joining one or more tables.

➢ A view contains rows and columns, just like a real table

➢ The fields in a view are fields from one or more real tables in the
database
Creating an SQL VIEW
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
Views
View Creation – Example

1. CREATE VIEW transaction_view AS SELECT acc_no, tr_date,amt from

transaction;

2. Create view account_view AS SELECT

a.acc_no,a.balance,l.loan_amt,l.interest_rate from account a, loan l where
a.acc_no=l.acc_no and a.city=‘Ahmedabad’;
The above views would create a virtual table based on the
result set of the select statement. To show created views:

1. SELECT * FROM transaction_view;

2. SELECT * FROM account_view;
Views
Updating View

You can modify the definition of a VIEW without dropping it by using the
following syntax:

CREATE OR REPLACE VIEW view_name

AS SELECT columns
FROM table
WHERE predicates;
1. Create or Replace view account_view
AS SELECT a.acc_no,a.balance,l.loan_amt,l.interest_rate,
l.remaining_loan from account a, loan l where a.acc_no=l.acc_no
and a.city=‘Vadodara’;
Views

Deleting View : to delete the Views that can already created

once
The syntax for dropping a VIEW :

DROP VIEW view_name;

View Drop - Example

DROP VIEW transaction_view;

 Views
Question: Can you update the data in an view?
Answer : A view is created by joining one or more tables. When you update
record(s) in a view, it updates the records in the underlying tables that make
up the View. So, yes, you can update the data in View providing you have the
proper privileges to the underlying tables.

Question: Does the SQL View exist if the table is dropped from the database?
Answer: Yes, View continues to exist even after one of the tables (that the SQL
View is based on) is dropped from the database. However, if you try to query
the View after the table has been dropped, you will receive a message
indicating that the View has errors.
Views
 Trigger
➢ A trigger is a block structure which is fired when a DML statements like
Insert, Delete, Update is executed on a database table. A trigger is
triggered automatically when an associated DML statement is executed.

Syntax of Triggers

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER}
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition) ;
Trigger
Creating Trigger

1. CREATE TRIGGER deleted_details

BEFORE
DELETE on loan
FOR EACH ROW

2. CREATE TRIGGER inserted_details

after
insert on transaction
FOR EACH ROW
Trigger
Creating Trigger

3. CREATE TRIGGER updated_details

BEFORE
UPDATE on account
FOR EACH ROW when name=‘Patel Hiren’;

Deleting Trigger

Drop trigger inserted_details on transaction;

Trigger
➢ The syntax for a dropping a Trigger is:

DROP TRIGGER trigger_name ON tbl_name;

➢ The syntax for a disabling a Trigger is:

ALTER TRIGGER trigger_name DISABLE;

➢ The syntax for a enabling a Trigger is:

ALTER TRIGGER trigger_name ENABLE;

PL/SQL
Procedural Language – SQL: An extension to SQL with
design features of programming languages (procedural
and object oriented)
PL/SQL
PL/SQL BLOCK STRUCTURE

DECLARE (optional)
- variable declarations
BEGIN (required)
- SQL statements
- PL/SQL statements or sub-blocks
EXCEPTION (optional)
- actions to perform when errors occur
END; (required)
PL/SQL
➢ There are 2 types of Datatypes:
1. Scalar (Char, Varchar2, Date and Number)
2. Composite (%rowtype)

➢ All Variables required to be used in the program must be declared before

their use. Variable names cannot be table, column and keyword names.

➢ Assignment Operator is denoted as “:=” and each statement of PL/SQL ends

with “;”.

➢ Dbms_Output.Put_Line() is used for printing Output.

➢ &Variable_Name :Variable Name is use to take input from the user.

PL/SQL
➢ Conditional Statements

1. IF …THEN…ELSE 2. CASE…

IF <condition> THEN CASE

<Code> WHEN VARIABLE = VALUE THEN
ELSEIF <condition> THEN <Code>
<Code> WHEN VARIABLE = VALUE THEN
ELSE <Code>
<Code> ELSE
ENDIF; <Code>
END CASE;
PL/SQL
➢ Types of Loops 3. For Loop
1. Simple Loop FOR <Variable> IN <Min> .. <Max>
Loop Loop
<Code> <Code>
Exit when <Cond> End Loop;
End Loop;
4. For Loop
2. While Loop FOR <Variable> IN REVERSE
While <Condition> <Min> .. <Max>
Loop Loop
<Code> <Code>
End Loop; End Loop;
PL/SQL Examples
Write a PL/SQL block to add 2 numbers
declare
x number(5);
y number(5);
z number(7);
begin
x:=10;
y:=20;
z:=x+y;
DBMS_OUTPUT.PUT_LINE('Sum is '||z);
end;
PL/SQL Examples
Write a PL/SQL block to find maximum of 2 numbers
declare
x number(5);
y number(5);
begin
x:=10;
y:=20;
if x > y then
DBMS_OUTPUT.PUT_LINE(‘x is greater than y');
else
DBMS_OUTPUT.PUT_LINE(‘ y is greater than x');
end if;
end;
PL/SQL Examples
declare
Write a PL/SQL area_c number(6, 2) ;

block to find area radius number(1) := 3 ;

of rectangle, square area_r number(6) ;

and circle
area_s number(6) ;
length number(2) := 5 ;

width number(2) := 4 ;
pi constant number(3, 2) := 3.14;

begin

area_c := pi * radius * radius;

DBMS_OUTPUT.PUT_LINE('Circle area = ' || area_c);

area_r := length * width;

DBMS_OUTPUT.PUT_LINE('Rectangle area = ' || area_r);

area_s := length * length;

DBMS_OUTPUT.PUT_LINE('Square area = ' || area_s);

end;
PL/SQL Examples
Write a PL/SQL block to print the sum of Numbers from
1 to 100
declare
a number;
sum1 number :=0;
begin
a:=1;
loop
sum1:=sum1+a;
a:=a+1;
exit when (a>100);
end loop;
DBMS_OUTPUT.PUT_LINE('sum of 1 to 100 is '||sum1);
end;
PL/SQL Examples
Write a PL/SQL program for inserting even numbers in EVEN table and odd number in ODD table from
number 1 to 50.
begin

for i in 1..50
loop
if mod(i,2) = 0 then

insert into EVEN values (i);

end if;

end loop;

for i in 1..50
loop

if mod(i,2)!= 0 then

insert into ODD values (i);

end if;

end loop;

end;
PL/SQL Examples
Write a PL/SQL block to print the given number is prime or not
declare
num number;
if(c>2)
i number:=1;
then
c number:=0;
DBMS_OUTPUT.PUT_LINE(num||' not a prime');
begin
else
num:=#
DBMS_OUTPUT.PUT_LINE(num||' is prime');
for i in 1..num
end if;
loop
end;
if((mod(num,i))=0)
then
c:=c+1;
end if;
end loop;
PL/SQL
%RowType
Variable Name TableName%RowType

acc account%RowType;

- This “acc” variable will have complete structure of the table account. Any
column of account table can be referred using “acc” variable

DECLARE
acc account%RowType;
BEGIN
select * into acc from account where acc_no=‘A001’;
DBMS_OUTPUT.PUT_LINE (acc.name); = “Patel Hiren”
DBMS_OUTPUT.PUT_LINE (acc.city); = “Mehsana”
DBMS_OUTPUT.PUT_LINE (acc.balance); = 50000
DBMS_OUTPUT.PUT_LINE (acc.loan_taken); = “YES”
END;
 Cursor
➢ A cursor is a private set of records.

➢ A cursor is a temporary work area created in the system 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 2 types of Cursors:

1. Implicit Cursor: Created automatically for every query in SQL.
2. Explicit Cursor: Created manually by the programmer.
Cursor
Cursor Attributes

cursorname%ROWCOUNT Rows returned so far

cursorname%FOUND One or more rows retrieved

cursorname%NOTFOUND No rows found

cursorname%ISOPEN Is the cursor open
Cursor
Explicit Cursor

• Declare the cursor

• Open the cursor
• Fetch a row
• Test for end of cursor
• Close the cursor
Cursor
Explicit Cursor Example
DECLARE
CURSOR account_cursor IS SELECT * from account;
acc account%rowtype
BEGIN
DBMS_OUTPUT.PUT_LINE ('******************');
OPEN account_cursor;
FETCH account_cursor into acc;
WHILE account_cursor % Found
LOOP
DBMS_OUTPUT.PUT_LINE (acc.name);
DBMS_OUTPUT.PUT_LINE (acc.city);
DBMS_OUTPUT.PUT_LINE (acc.balance);
DBMS_OUTPUT.PUT_LINE (acc.loan_taken)
DBMS_OUTPUT.PUT_LINE ('******************');
FETCH account_cursor into acc;
END LOOP;
CLOSE account_cursor;
Stored Procedure
➢ A program unit is a self-contained group of program statements that can
be used within a large program
➢ Stored PL/SQL program units are program units that other PL/SQL
program can reference and that other database users can use and execute.
➢ Can receive multiple input parameters and return multiple output values
(or no output values) and perform DML Commands
CREATE [OR REPLACE] PROCEDURE procedure_name [
(parameter mod datatype [,parameter]) ]
IS
[local variable declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Stored Procedure
➢ The header section defines:
1. The procedure name (name should be unique )
2. The parameters that the procedure receives or delivers
(IN, OUT, IN OUT)
3. IS keyword: follows the parameters list
4. The local procedure variables
➢ Create or replace: instructs the DBMS to create the procedure if it does it
exists, otherwise; it replaces the existing one
➢ OR REPLACE clause is optional. But if omitted and a procedure exists with the
same name, an error occurs
➢ Parameter mod: describes how the procedure change the value. It can be:
1. IN: passed parameter is a Read only value. Cannot be changed by the procedure
2. OUT: write-only value. Always comes on the left side of an assignment
statement
3. IN OUT: its value can be changed
Stored Procedure
The Following Stored
EXAMPLE:
Procedure updates
Create or replace procedure account_update the balance of the
( account for a given
curr_acc_no IN varchar2, account number and
curr_name IN varchar2, name of the account.
curr_balance IN number
)
IS Command to
BEGIN Execute the Store
update account Procedure
set balance = curr_balance
where
acc_no = curr_acc_no and
name = curr_name;
COMMIT; Execute account_update(‘A001’,’Patel’,125000);
END;
Function
➢ A function is similar to a procedure, except that it returns a single value.
➢ Functions can accept one, many, or no parameters, but a function must
have a return clause in the executable section of the function.
➢ The datatype of the return value must be declared in the header of the
function. ➢ The syntax for creating a function is as follows:

CREATE [OR REPLACE] FUNCTION function_name

(parameter list)
RETURN datatype
IS
BEGIN
<body>
RETURN (return_value);
END;
Function
EXAMPLE:
Create or replace function account_det
(account_num varchar2) The Following
RETURN varchar2
AS
Function will
acc_name varchar2(30); fetch the name of
BEGIN the account for a
select name into acc_name from
account where acc_no=account_num; given account
RETURN acc_name; number.
END;
2 Ways to Execute the Function:
DECLARE
acc_name VARCHAR2(30); Select
BEGIN
acc_name := account_det(&account_num);
account_det(‘A001’)
DBMS_OUTPUT.PUT_LINE(acc_name); from account;
END;
THANK YOU
www.paruluniversi
ty.ac.in