Rdbms Practical Silps

RDBMS 15 Slips

SLIP-15

Person (p_no, p_name, p_addr)
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)

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
BCA Pratical Solution

My name is Vivek And I from Mumbai and Complete my Graduation Bca.my Age is 23 Years.

Post a Comment

Previous Post Next Post