Scribd
Upload
11 views7 pages

PL/SQL Nested If-Else Concepts

This document discusses various PL/SQL concepts including functions, procedures, cursors, and triggers. It provides examples of how to write functions to check conditions and return values, procedures to pass multiple values, and uses of implicit and explicit cursors. Triggers are demonstrated to automatically insert deleted records into a log table using an instead of delete trigger.

Uploaded by

Jyovita
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
11 views7 pages

PL/SQL Nested If-Else Concepts

This document discusses various PL/SQL concepts including functions, procedures, cursors, and triggers. It provides examples of how to write functions to check conditions and return values, procedures to pass multiple values, and uses of implicit and explicit cursors. Triggers are demonstrated to automatically insert deleted records into a log table using an instead of delete trigger.

Uploaded by

Jyovita
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Unit 10

PL/SQL Concepts
Nested if-else

Program to find the largest of three given numbers:


If-Elsif Ladder

Switch Case
Function

 Can return only one variable.


 Can be called through a select statement or a PL/SQL block.
 Must return a variable.

Function to check whether the given three sides form a triangle or not:

Calling a function using select statement:

Calling a function using a PL/SQL block:

declare
a number := &a;
b number := &b;
c number := &c;
answer varchar(5);
begin
answer := is_triangle(a, b, c);
if answer = 'True' then
dbms_output.put_line('a = ' || a || 'b = ' || b || 'and c = ' || c|| ' form a triangle');
else
dbms_output.put_line('a = ' || a || 'b = ' || b || 'and c = ' || c|| ' do not form a triangle');
end if;
end;
/
Procedure

 Can return multiple variables.


 Cannot be called in the select statement.

PL/SQL procedure to print number of projects assigned to an employee, find the department of
an employee and print the first name and last name of an employee given his/her employee id:

The following database is used:


Procedure:

create or replace procedure emp_details

(empid in [Link]%type, proj_count out number, total_time out number) is

begin

select count(projectid), sum(nvl(assignedtime, 0))

into proj_count, total_time from

employees left outer join workson using(employeeid)

where employeeid = empid

group by employeeid;

end;

Calling the procedure:

Cursor:

Uses the following:

1. %found: Is true if query executes on one or more rows and false otherwise.
2. %notfound: Is false if query execute on one or more rows and true otherwise.
3. %rowcount: Counts the number of rows affected by the query.
4. %isopen: Is true if the cursor is open and false otherwise.

Implicit Cursor
Consider the following employee table:

Cursor:

Explicit Cursor:
Trigger

Employee relation and emp_del relations:

Performing the delete query:

Employee relation and emp_del relations after delete query is executed:

You might also like