Assignment 4: Handling errors and Exceptions
The RAISE statements raise errors and exceptions during a PL/pgSQL function’s execution. A Raise statement is also given the level of error it should raise and the string error message it should send to postgreSQL. The string can also be embedded with variables and expressions, that one needs to list along with the error message. The percent (%) sign is used as the place holder for the variables that are inserted into the string.
The syntax of the RAISE statement is as follows :
RAISE level ‘’message string ‘’ [, identifier [….]];
The three possible values for the RAISE statement’s level are as follows:
1) DEBUG :- Debug level statements send the specified text as a debug message to the PostgreSQL log.
2) NOTICE :- Notice level statements send the specified text as a Notice;
3) EXCEPTION :-Exception level statements send the specified text as an ERROR. The exception level also causes the current transaction to be aborted.
Assignment 4 : Handling errors and Exceptions
SET A
Using Bank Database
a) Write a stored function to print the total number of customers of a particular branch. ( Accept branch name as input parameter.) In case the branch name is invalid, raise an exception for the same.
create or replace function f1(nm varchar(30))returns text as '
declare rec record;
cnt int;
begin
for rec in select * from branch
loop
if(rec.bname<>nm)then
raise notice''Invalid'';
else
select count(cno) into cnt from bcl where bid in(select bid from branch where rec.bname=nm);
end if;
end loop;
raise notice''customer count is : %'',cnt;
return'' '';
end '
language 'plpgsql';
CREATE FUNCTION
b) Write a stored function to increase the loan approved amount for all loans by 20%. In case the initial loan approved amount was less than Rs 10000, then print a notice to the user, before updating the amount .
create or replace function f2()returns text as'
declare rec record;
begin
for rec in select * from la
loop
if(rec.appmoney<''$10000'')then
raise notice ''amount is less than 10000 '';
else
update la set appmoney=appmoney+(appmoney*0.2)where lano=rec.lano;
end if;
end loop;
return '''';
end'
language'plpgsql';
CREATE FUNCTION
SET B
Using Project-Employee database
create table project(pno int primary key,pname varchar(15),ptype varchar(15),duration int);
CREATE TABLE
create table emp(eno int primary key,ename varchar(15),j_date date);
CREATE TABLE
create table prog_emp(pno int references project(pno),eno int references emp(eno),s_date date);
CREATE TABLE
insert into project values(1,'system','computer',100);
insert into project values(2,'robotics','computer',100);
insert into project values(3,'cloud network','computer',50);
insert into project values(4,'microprocessor','electronics',150);
insert into project values(5,'eye senser','electronics',250);
insert into project values(6,'remote car','meachinical',75);
insert into emp values(1,'anil','04-04-2010');
insert into emp values(2,'ajay','05-23-2010');
insert into emp values(3,'nadim','08-18-2011');
insert into emp values(4,'samadhan','12-28-2012');
insert into emp values(5,'mahesh','12-28-2012');
insert into prog_emp values(1,1,'04-04-2010');
insert into prog_emp values(1,2,'05-23-2010');
insert into prog_emp values(2,2,'05-20-2010');
insert into prog_emp values(3,1,'04-25-2011');
insert into prog_emp values(3,1,'03-25-2011');
insert into prog_emp values(3,4,'02-12-2012');
insert into prog_emp values(4,5,'05-12-2013');
a) Write a stored function to accept project name as input and print the names of employees working on the project. Also print the total number of employees working on that project. Raise an exception for an invalid project name.
create function SetA_a(varchar ) returns integer as $$
declare
rec record;
cur cursor for select * from project,prog_emp,emp where project.pno=prog_emp.pno and emp.eno=prog_emp.eno;
cnt integer:=0;
name alias for $1;
begin
open cur;
loop
fetch cur into rec;
exit when not found;
if rec.pname=name then
raise notice '%',rec.ename;
cnt:=cnt+1;
end if;
end loop;
if cnt=0 then
raise exception 'Invalid Project name';
end if;
close cur;
return cnt;
end;
$$ language plpgsql;
CREATE FUNCTION
b) Write a stored function to decrease the Hours_worked by 2 hours, for all projects in which employees from department no 2 is working. Raise an exception , in case the hours_worked becomes = 0 , after updation.
project4=# create or replace function f3() returns text as'
declare rec record;
n int;
begin
select hours into n from pe where pno in(select pno from project where pname=''Electronics'');
update pe set hours=hours-2 where pno in(select pno from project where pname=''Electronics'');
if(n-2=0)then
raise notice''hours are 0'';
end if;
raise notice''number : %'',n;
return'' '';
end'
language 'plpgsql';
CREATE FUNCTION
SET C
Using Bus transport Database
a) Write a stored function to print the names of drivers working on both shifts on ‘20/04/2014’.
create or replace function f4(d date) returns text as'
declare
rec record;
nm driver.dname%type;
begin
for rec in select * from bd
loop
if(rec.duty_date<>d)then
raise notice''Date Is Invalid'';
else
select dname into nm from driver where dno in(select dno from bd where shift=''m'' and shift=''e'' and duty_date=d);
end if;
end loop;
raise notice''dname=%'',nm;
return'' '';
end'
language 'plpgsql';
CREATE FUNCTION
b) Write a stored function to accept the bus_no and date and print its allotted drivers. Raise an exception in case of invalid bus number.
Project-Employee database
Consider the following database:
Project (pno int, pname char (30), ptype char (20), duration int)
Employee (empno int, ename char (20), joining_date date)
The relationship between Project and Employee is many to many with descriptive attribute start_date.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function to accept project name as input and print the names of employees working on the project. Also print the total number of employees working on that project. Raise an exception for an invalid project name.
b. Write a stored function to accept empno as an input parameter from the user and count the number of projects of a given employee. Raise an exception if the employee number is invalid.
No comments:
Post a Comment