Solution:
SQL>create table employee(eid int primary key,ename varchar(10),address varchar(10));
SQL>create table investment(inv_no int primary key, inv_name varchar(10),inv_date varchar(10),inv_amount int,eid int);
SQL>insert intoemployee values(1,'reshma','koregoan');
SQL>insert into investment values(1,'house','15thaug','50000',1);
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure which will display details of employees invested amount in “Mutual Fund”
SQL> set serveroutput on;
SQL> create or replace procedure p1(n in varchar) as cursor c1 is select e_name,inv_amount from employee,investment where employee.e_id=investment.e_id and inv_amount=50000;
2 c c1 %rowtype;
3 begin
4 open c1;
5 dbms_output.put_line('e_name'||''||'inv_amount');
6 loop
7 fetch c1 into c;
8 exit when c1 %notfound;
9 if(c.inv_amount=n) then
10 dbms_output.put_line(c.e_name||''||c.inv_amount);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('50000');
3 end;
4 /
e_nameinv_amount
reshma50000
2)Write a cursor which will display date wise investment details.
SQL> set serveroutput on;
SQL> declare
2 cursor c1 is select inv_date,inv_no,inv_name,inv_amount from employee,investment where employee.e_id=investment.e_id;
3 c c1 %rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c;
8 exit when c1 %notfound;
9 dbms_output.put_line(c.inv_date||''||c.inv_no||''||c.inv_name||''||c.inv_amount);
10 end loop;
11 close c1;
12 end;
13 /
15thaug1house50000
20thsept2land60000
25thoct3vehicle70000
0 Comments