RDBMS 15 Slips
SLIP-15
Investment (inv_no, inv_name, inv_date, inv_amt)
Relationship between Person and Investment is one-to-many.
Constraints: primary key, foreign key,
p_name and inv_name should not be null,
inv_amt should be greater than 10000.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure which will display details of person, made investment on given
date.
2) Write a trigger that restricts insertion or updation of investment having inv_date
greater than current date. (Raise user defined exception and give appropriate
message)
3) Write a function which will return name of person having maximum total amount of
investment.
4) Write a cursor which will display person wise details of investment. (Use
parameterized cursor)
create table per01
(
pno number(10) primary key,
pname varchar2(20)NOT NULL,
addr varchar2(20)
);
create table inv02
(
ino number(10) primary key,
iname varchar2(20)NOT NULL,
idt date,
iamt number(10)check(iamt>10000),
pno number(10) references per01(pno)
);
insert into per01 values(101,'sanjay','pune');
insert into per01 values(102,'mira','panshet');
insert into per01 values(103,'akshay','mumbai');
insert into per01 values(104,'sanket','nasik');
insert into per01 values(105,'sagar','pune');
insert into inv02 values(10,'car','2 jan 2013',500000,101);
insert into inv02 values(20,'home','14 jun 2013',400000,102);
insert into inv02 values(30,'gold','3 feb 2013',300000,103);
insert into inv02 values(40,'education','2 jan 2013',200000,104);
insert into inv02 values(50,'car','15 jan 2019',180000,105);
Q.1) Write a procedure which will display details of person, made investment on given
date.
PROCEDURES
create or replace procedure p15
(vdt in date)
as
cursor c2 is select per01.* from per01,inv02
where per01.pno=inv02.pno and
inv02.idt='2 jan 2013';
x c2%rowtype;
begin
for x in c2
loop
dbms_output.put_line(x.pno||' '||x.pname||' '||x.addr);
end loop;
end p15;
/
Procedure created.
SQL> execute p15('2 jan 2013');
101 sanjay pune
104 sanket nasik
PL/SQL procedure successfully completed.
Q.2) Write a trigger that restricts insertion or updation of investment having inv_date
greater than current date. (Raise user defined exception and give appropriate
message)
TRIGGER
create or replace trigger t015
before insert or update on inv02
for each row
declare
a varchar2(10);
b varchar2(10);
begin
a:=to_char(:new.idt,'dd-mm-yyyy');
b:=to_char(sysdate,'dd-mm-yyyy');
if(a>b) then
raise_application_error('-20010','inv_date is greater than current date');
end if;
end t015;
/
Trigger created.
SQL> insert into inv02 values(50,'car','15 jan 2013',180000,105)
insert into inv02 values(50,'car','15 jan 2013',180000,105)
*
ERROR at line 1:
ORA-20010: inv_date is greater than current date
ORA-06512: at "SYSTEM.T015", line 8
ORA-04088: error during execution of trigger 'SYSTEM.T015'
Q.3) Write a function which will return name of person having maximum total amount of
investment.
FUNCTION
create or replace function f15
(vno in number)
return varchar2
as
vname per01.pname%type;
begin
select pname into vname from per01,inv02
where per01.pno=inv02.pno and
inv02.iamt=(select max(iamt) from inv02);
if sql%found then
return vname;
else
return 0;
end if;
end f15;
/
Function created.
SQL> select f15(500000) from dual;
F15(500000)
--------------------------------------------------------------------------------
sanjay
||Calling||
declare
a number(10);
b varchar2(10);
begin
a:=&a;
b:=f15(a);
if(b>0)then
dbms_output.put_line('the name of person is'||b);
end if;
end;
/
Q.4) Write a cursor which will display person wise details of investment. (Use
parameterized cursor)
declare
cursor c1(vno per01.pno%type)is select inv02.* from per01,inv02
where per01.pno=inv02.pno and
per01.pno=101;
cno per01.pno%type;
x c1%rowtype;
begin
cno:=&cno;
for x in c1(cno)
loop
dbms_output.put_line(x.ino||' '||x.iname||' '||x.idt||' '||x.iamt||' '||x.pno);
end loop;
end;
/
Enter value for cno: 101
old 8: cno:=&cno;
new 8: cno:=101;
10 car 02-JAN-13 500000 101
PL/SQL procedure successfully completed.
*********************************************************************************
END
Tags:
RDBMS