User:Mommymaa/sandbox

1.

create or replace function calculate_fee(cid number) return number is cursor c1 is select * from customer where cust_id=cid; total_time number:=0; total_cost number:=0; v customer%rowtype; cnt number:=0; ue exception; begin open c1; loop fetch c1 into v; exit when c1%notfound; cnt:=cnt+1; end loop; if(cnt!=0) then select sum(duration) into total_time from booking where cust_id=cid; if (total_time>=5) then total_cost:=total_time*250; return total_cost; else return -1; dbms_output.put_line('training duration less than 5 hours'); end if; else return -2; --raise ue; --dbms_output.put_line('invalid customer id');

end if; --exception --when ue then --dbms_output.put_line('invalid customer id'); end;

declare a number; begin a:=calculate_fee(101); dbms_output.put_line('total fee= '||a); if(a=-1) then dbms_output.put_line('training duration less than 5 '); elsif(a=-2) then dbms_output.put_line('hours invalid cust_id'); end if; end; /

- 2. original create or replace package cust_booking is procedure disp_bookings(cid number); end;

create or replace package body cust_booking is procedure disp_bookings(cid number) is cursor c1 is select * from customer where cust_id=cid; cursor c2 is select * from booking where cust_id=cid; v customer%rowtype; vb booking%rowtype; cnt number:=0; cntb number:=0; flag number; e1 exception; e2 exception; begin open c1; loop fetch c1 into v;	exit when c1%notfound; cnt:=cnt+1; end loop;

if(cnt!=0) then open c2; flag:=1; loop fetch c2 into vb; exit when c2%notfound; if(flag=1) then dbms_output.put_line('Booking_id Vehicle_id Training_date Duration'); dbms_output.put_line(vb.booking_id||'            '||vb.vehicle_id||'          '||vb.training_date_time||'       '||vb.duration); flag:=0; else dbms_output.put_line(vb.booking_id||'            '||vb.vehicle_id||'          '||vb.training_date_time||'       '||vb.duration); end if; cntb:=cntb+1; end loop; if(cntb=0) then raise e2; end if; else raise e1; end if; exception when e1 then dbms_output.put_line('invalid cust_id'); when e2 then dbms_output.put_line('no booking'); end; end;

exec cust_booking.disp_bookings(101);

-- 3.

3.A create or replace type booking_type is object( booking_id number(5), training_date date,duration number(5));

3.B create or replace type booking_list_type is table of booking_type;

3.C create table cust_booking1( cust_id number(4) primary key,name varchar2(20), booking_list booking_list_type) nested table booking_list store as temp;

3.D

insert into cust_booking1 values(1,'ALFRED',booking_list_type(booking_type(219,'01-DEC-16',1),booking_type(303,'01-JAN-16',7),booking_type(309,'01-MAR-16',5)));

insert into cust_booking1 values(2,'BRUCE',booking_list_type(booking_type(101,'20-JUN-14',4),booking_type(156,'23-JUL-15',2),booking_type(199,'31-DEC-15',5)));

3.E create or replace procedure show_booking(cid number) is b booking_list_type;

begin select booking_list into b from cust_booking1 where cust_id=cid;

for i in b.first..b.last loop dbms_output.put_line(b(i).booking_id||' '||b(i).training_date||' '||b(i).duration); end loop; exception when no_data_found then dbms_output.put_line('invalid cust_id'); end;

-- declare cursor c1 is select * from emp; r emp%rowtype; begin open c1; loop fetch c1 into r; exit when c1%notfound; dbms_output.put_line(r.empno); end loop; end;

declare a number; begin select empno into a from emp where salary=15500; dbms_output.put_line(a); end;

create or replace function px return number is r emp%rowtype; cursor c1 is select * from emp; summ number:=0; begin open c1; loop fetch c1 into r; exit when c1%notfound; summ:=summ+r.salary; end loop; dbms_output.put_line('sum is'||summ); return summ; end;

declare summ number; begin summ:=px; dbms_output.put_line('sum out of fx is'||summ); end; /

- declare cursor c1 is select * from emp where salary>100000; r emp%rowtype; begin open c1; loop fetch c1 into r; exit when c1%notfound; dbms_output.put_line(r.empname); end loop; end;

declare cursor c1 is select * from emp where salary>100000; r emp%rowtype; flag number:=0; begin open c1; loop fetch c1 into r; exit when c1%notfound; flag:=flag+1; dbms_output.put_line(r.empname); end loop; if(flag=0) then dbms_output.put_line('not found'); end if; end; / -

SQL> declare 2 sal number; 3 begin 4 select max(salary) into sal from emp; 5 dbms_output.put_line('salary'||sal); 6 end; 7 / --- declare sal emp.salary%type; begin select avg(salary) into sal from emp; dbms_output.put_line(sal); end; / -

declare flag number:=0; cursor c1 is select * from emp where salary>1000000; begin for i in c1 loop flag:=flag+1; dbms_output.put_line(i.salary); end loop; if(flag=0) then dbms_output.put_line('not found'); end if; end;

---

declare cursor c1(a number) is select * from emp where salary>a; v1 emp%rowtype; begin open c1(&h); loop fetch c1 into v1; exit when c1%notfound; dbms_output.put_line(v1.empname); end loop; end; /

---

declare a number; begin loop select distinct salary into a from emp; dbms_output.put_line(a); end; /

declare cursor c1 is select distinct salary from emp; a number; begin open c1;

loop fetch c1 into a; exit when c1%notfound; dbms_output.put_line('a='||a); end loop; exception when no_data_found then dbms_output.put_line('jjv');

end;

-

declare cursor c1 is select * from emp where salary>200000; flag number:=0; e exception; r emp%rowtype; begin open c1; loop fetch c1 into r; exit when c1%notfound; dbms_output.put_line(r.empname); flag:=flag+1; end loop; if(flag=0) then raise e; end if; exception when e then dbms_output.put_line('not found'); end; /

declare cursor c1 is select * from emp where rownum<=0; r emp%rowtype; a number; flag number:=0; e exception; begin select sum(salary) into a from emp where rownum<=3; open c1; loop fetch c1 into r; exit when c1%notfound; flag:=flag+1; dbms_output.put_line(r.empname);

end loop;

if(flag=0) then raise e; else dbms_output.put_line(a); end if; exception when e then dbms_output.put_line('no data found'); end; /

---

declare cursor c1 is select * from emp where rownum<=0; flag number:=0; e exception;

m number:=1; r emp%rowtype; begin open c1; loop fetch c1 into r; exit when c1%notfound; flag:=flag+1; m:=m*r.deptno;

end loop;

if(flag=0) then raise e; else dbms_output.put_line('m='||m); end if; exception when e then dbms_output.put_line('error hai'); end;

- create or replace procedure p1(a varchar2) is cursor c1 is select * from emp where empname=a; r emp%rowtype; e exception; flag number:=0; begin open c1; loop fetch c1 into r; exit when c1%notfound;

dbms_output.put_line(r.empname||' '||r.empno); flag:=flag+1; end loop; if(flag=0) then raise e; end if; exception when e then dbms_output.put_line('error'); end; /

--

create or replace procedure p2(name varchar2) is cursor c1 is select * from emp where empname=name; r emp%rowtype; flag number:=0; e exception; begin open c1; loop fetch c1 into r; exit when c1%notfound; flag:=flag+1; dbms_output.put_line('name   empno     sal'); dbms_output.put_line(r.empname ||' '|| r.empno|| ' '|| r.salary); end loop; if(flag=0) then raise e; end if; exception when e then dbms_output.put_line('error'); end; /

--- create or replace procedure p2(sal number) is

cursor c1 is select * from emp where salary>sal; r emp%rowtype; flag number:=0; e exception;

begin open c1;

loop fetch c1 into r;	exit when c1%notfound; flag:=flag+1; if(flag=1) then

dbms_output.put_line('name   empno     sal'); dbms_output.put_line(r.empname ||' '|| r.empno|| ' '|| r.salary); else dbms_output.put_line(r.empname ||' '|| r.empno|| ' '|| r.salary); end if; end loop;

if(flag=0) then raise e; end if;

exception when e then dbms_output.put_line('error'); end; /

create or replace procedure p3 is cursor c1 is select * from emp where empname=lower('&name'); r emp%rowtype; flag number:=0; e exception; begin open c1; loop fetch c1 into r; exit when c1%notfound; flag:=flag+1; if(flag=1) then dbms_output.put_line('empno name salary'); dbms_output.put_line(r.empno||' '||r.empname||' '||r.salary); else dbms_output.put_line(r.empno||' '||r.empname||' '||r.salary); end if; end loop; if(flag=0) then raise e; end if; exception when e then dbms_output.put_line('error'); end; /

create or replace function make_payment(em number, name varchar2 , salary number) return number is cursor c1 is select * from emp where empno=em; r emp%rowtype; a number; m number:=0; n number:=1; flag number:=0; begin open c1; loop fetch c1 into r; exit when c1%notfound; flag:=flag+1; end loop; if(flag=1) then

return m; else select max(deptno) into a from emp; a:=a+1; insert into emp values(em,name,salary,a);

return n; end if; end;

declare x number; begin x:=make_payment(12405,'atiya',12000); if(x=0) then dbms_output.put_line('record exists'); else dbms_output.put_line('record inserted'); end if; end; /

create or replace function f1(en number) return number is cursor c1 is select * from emp where empno=en; r emp%rowtype; flag number:=0; begin open c1; loop fetch c1 into r; exit when c1%notfound; flag:=flag+1; if(r.comm=0) then return 0; end if; if(r.comm>0) then return 1; else return -1; end if; end loop; if(flag=0) then return -2; end if; end; /

declare a number; begin a:=f1(749900); if(a=0) then dbms_output.put_line('comm is zero'); elsif(a=1) then dbms_output.put_line('comm is greater than zero'); elsif(a=-1) then dbms_output.put_line('comm is null'); else dbms_output.put_line('no record exists'); end if; end; / -- create or replace package pck1 is function f2(empn number) return varchar; end;

create or replace package body pck1 is function f2(empn number) return varchar is cursor c1 is select dname from dept where deptno=(select deptno from emp where empno=empn); name varchar2(20); flag number:=0; begin open c1; loop fetch c1 into name; exit when c1%notfound; flag:=flag+1; return name; end loop; if(flag=0) then return 'doesnt exist'; end if; end; end;

declare a varchar2(20); begin a:=pck1.f2(7499); dbms_output.put_line('dept name='||a); end; / a=pck1.f2(7499);

create or replace package pc4 is function f4(empn number) return number; end;

create or replace package body pc4 is function f4(empn number) return number is cursor c1 is select grade from salgrade where (select sal from emp where empno=empn)>losal and (select sal from emp 	where empno=empn)losal and (select sal from emp where empno=4545) <hisal ; - que 2- packg-fun(empno) return deptno pro(deptno) print location

---

create or replace package pc5 is function f5(empn number) return number; procedure p5(deptn number) ; end;

create or replace package body pc5 is function f5(empn number) return number is cursor c1 is select deptno from emp where empno=empn; a number; flag number:=0; begin open c1; loop fetch c1 into a;			exit when c1%notfound; flag:=flag+1; return a;		end loop; if(flag=0) then return 0; end if; end;

procedure p5(deptn number) is cursor c1 is select loc from dept where deptno=deptn; a varchar2(20); flag number:=0; begin open c1; loop fetch c1 into a;			exit when c1%notfound; flag:=flag+1; dbms_output.put_line('location is '|| ' '||a); end loop; if(flag=0) then dbms_output.put_line('no such deptno'); end if; end; end;

declare b number; c varchar2(20); begin b:=pc5.f5(7499); if(b=0) then dbms_output.put_line('not found'); else pc5.p5(b); end if;

end;

create or replace package pc6 is function f6 return number; procedure p6(av number); end;

create or replace package body pc6 is function f6 	return number is cursor c1 is select avg(sal) from emp; a number; flag number:=0; begin open c1; loop fetch c1 into a;			exit when c1%notfound; flag:=flag+1; return a;		end loop; end; procedure p6(av number) is cursor c1 is select * from emp where sal>av; r emp %rowtype; flag number:=0; begin open c1; loop fetch c1 into r;			exit when c1%notfound; flag:=flag+1; dbms_output.put_line(r.empno||' '||r.ename||' '||r.sal); end loop; if(flag=0) then dbms_output.put_line('not found'); end if; end;

end;

declare b number(7,2); begin b:=pc6.f6; dbms_output.put_line(b); pc6.p6(b); end;

create or replace package pc7 is procedure p7(a varchar2); end;

create or replace package body pc7 is --create or replace function fx17(job1 varchar2) return number is cursor c1 is select deptno from emp where sal=(select max(sal) from emp where job=job1); a number;

flag number:=0; begin open c1; loop fetch c1 into a;			exit when c1%notfound; flag:=flag+1; return a;		end loop; if(flag=0) then return 0; end if; end;

procedure p7(a varchar2) is cursor c1 is select * from dept where deptno=fx17(a); r dept%rowtype; flag number:=0; m number:=fx17(a); begin open c1; loop fetch c1 into r;				exit when c1%notfound; flag:=flag+1; dbms_output.put_line(r.deptno||' '||r.dname); end loop; if(flag=0) then dbms_output.put_line('NOT FOUND'); end if; end;

end;

declare a varchar2(20):=&a; begin pc7.p7(a); end; /

--- create or replace procedure p8 is	cursor c1(n number) is select * from emp where empno=n; r emp%rowtype; begin open c1(&a); loop fetch c1 into r; exit when c1%notfound; dbms_output.put_line(r.ename||' '||r.sal); end loop; end; /

procedure p7(a varchar2) is cursor c1(a1 number) is select * from dept where deptno=a1; r dept%rowtype; flag number:=0; m number:=fx17(a); begin dbms_output.put_line('m='||m); if(m is not null) then open c1(m); loop fetch c1 into r;				exit when c1%notfound; flag:=flag+1; dbms_output.put_line(r.deptno||' '||r.dname); end loop; if(flag=0) then dbms_output.put_line('NOT FOUND'); end if; else dbms_output.put_line('null hai'); end if; end;

-- 1.

create or replace package test1 is function testinsert(empn number,enam varchar2,job1 varchar2,mgr number,sal1 number,comm1 number) return number; end;

create or replace package body test1 is function testinsert(empn number,enam varchar2,job1 varchar2,mgr number,sal1 number,comm1 number) return number is cursor c1 is select * from emp where empno=empn; r emp%rowtype; --a number; a2 number; flag number:=0; begin open c1; loop fetch c1 into r;			exit when c1%notfound; flag:=flag+1; end loop; if(flag=0) then select max(deptno) into a2 from emp ; a2:=a2+10; insert into emp values(empn,enam,job1,mgr,sysdate,sal1,comm1,a2); return 1; else return 0; end if; end;

end;

declare b number; empn1 number; enam1 varchar2(20); job11 varchar2(20); mgr1 number; sal11 number; comm11 number; begin dbms_output.put_line('enter empno'); empn1:=&empno; dbms_output.put_line('enter empname'); enam1:=&empname; dbms_output.put_line('enter job'); job11:=&job; dbms_output.put_line('enter mgr'); mgr1:=&mgr; dbms_output.put_line('enter salary'); sal11:=&salary; dbms_output.put_line('enter commision'); comm11:=&comm; b:=test1.testinsert(empn1,enam1,job11,mgr1,sal11,comm11); if(b=1) then dbms_output.put_line('inserted'); else dbms_output.put_line('empno exists'); END IF; end;

2.

create or replace procedure display11(deptn number) is cursor c1 is select * from emp where deptno=deptn order by sal desc ; r emp%rowtype; flag number:=0; begin open c1; loop fetch c1 into r;		exit when c1%notfound; flag:=flag+1; dbms_output.put_line(r.empno||' '||r.sal); end loop; if(flag=0) then dbms_output.put_line('not found'); end if; end;

exec display11(20); -

1.

create or replace procedure pt1(empn number) is cursor c1 is select * from dept where deptno=(select deptno from emp where empno=empn); r dept%rowtype; flag number:=0; e exception; begin open c1; loop fetch c1 into r;		exit when c1%notfound; flag:=flag+1; dbms_output.put_line(r.dname||' '||r.loc); end loop; if(flag=0) then raise e;	end if; exception when e then dbms_output.put_line('no such empno'); end; /

--

2.

create or replace function ft1(empn varchar2) return number is cursor c1 is select * from emp where ename=empn; r emp%rowtype; flag number:=0; c number; begin open c1; loop fetch c1 into r;		exit when c1%notfound; flag:=flag+1; if(r.comm is null) then return -1; else c:=r.sal*r.comm; return c;		end if; end loop; if(flag=0) then return -2; end if;

end; /

declare tot number; begin tot:=ft1('TURNER'); if(tot=-1) then dbms_output.put_line('commission is null'); elsif(tot=-2) then dbms_output.put_line('invalid empname'); else dbms_output.put_line('total is '|| ' '||tot); end if; end; /

test case 1 declare tot number; begin tot:=ft1('TURNER'); if(tot=-1) then dbms_output.put_line('commission is null'); elsif(tot=-2) then dbms_output.put_line('invalid empname'); else dbms_output.put_line('total is '|| ' '||tot); end if; end; /	test case 2 declare tot number; begin tot:=ft1('ALLEN'); if(tot=-1) then dbms_output.put_line('commission is null'); elsif(tot=-2) then dbms_output.put_line('invalid empname'); else dbms_output.put_line('total is '|| ' '||tot); end if; end; /

test case 3 declare tot number; begin tot:=ft1('SMITH'); if(tot=-1) then dbms_output.put_line('commission is null'); elsif(tot=-2) then dbms_output.put_line('invalid empname'); else dbms_output.put_line('total is '|| ' '||tot); end if; end; /

---

3.

create or replace package p12 is function f12(empn number) return number; procedure p129(mg number); end; /

create or replace package body p12 is function f12(empn number) return number is cursor c1 is select mgr from emp where empno=empn; m number; flag number:=0; begin open c1; loop fetch c1 into m;			exit when c1%notfound; flag:=flag+1; if(m is null) then return -1; else return m;			end if; end loop; if(flag=0) then return 0; end if; end;

procedure p129(mg number) is cursor c1 is select grade from salgrade where (select sal from emp where empno=mg)>losal and (select sal 		from emp where empno=mg)<hisal; grade number; flag number:=0; E EXCEPTION; begin open c1; loop fetch c1 into  grade ; exit when c1%notfound; flag:=flag+1; dbms_output.put_line('grade is'||' '||grade); end loop; if(flag=0)then raise e;		end if; exception when e then dbms_output.put_line('no such manager exists'); end;

end; /

declare a number; begin a:=p12.f12(7839); if(a=-1) then dbms_output.put_line('mgr is null'); elsif(a=0) then dbms_output.put_line('not found'); else p12.p129(a); end if; end; /

- 4.

create or replace package p13 is procedure p131(alpha varchar2); end; /

create or replace package body p13 is function f130(job1 varchar2) return number is cursor c1 is select avg(sal) from emp where job=job1; a number; flag number:=0; begin open c1; loop fetch c1 into a;			exit when c1%notfound; flag:=flag+1; return a;		end loop; if(flag=0) then return -1; end if; end;

procedure p131(alpha varchar2) is cursor c1(ab number) is select grade from salgrade where ab>losal and ab<hisal; a number; flag number:=0; e exception; av number; begin av:=f130(alpha); --dbms_output.put_line('av is '||av); if(av is null) then dbms_output.put_line('invalid job'); else open c1(av); loop fetch c1 into a;					exit when c1%notfound; flag:=flag+1; dbms_output.put_line('grade is '|| ' '||a); end loop; if(flag=0) then dbms_output.put_line('not found'); end if; end if; end; end;

create or replace procedure dis(b number) is cursor c1 is select * from cus where b_no=b; r cus%rowtype; c t_list; j number:=0; begin

open c1; loop j:=0; fetch c1 into r; exit when c1%notfound; --dbms_output.put_line(r.b_no); --dbms_output.put_line(r.b_name); c:=r.t;	for i in c.first..c.last loop if(j=0) then dbms_output.put_line(r.b_no||' '||r.b_name||' '||c(i).sno||' '||c(i).sname); j:=j+1; else dbms_output.put_line('                       '||c(i).sno||' '||c(i).sname); j:=j+1; end if; end loop; end loop; end;

--- create or replace procedure pu(sn number) is cursor c1 is select * from cus34 where bno=sn; r cus34%rowtype; t1 varr:=varr; flag number:=0; begin open c1; loop fetch c1 into r; exit when c1%notfound; t1:=r.v;	for i in t1.first..t1.last loop if(flag=0) then dbms_output.put_line(r.bno||' '||r.bname||' '||t1(i).sno||' '||t1(i).name); flag:=flag+1; else dbms_output.put_line('                     '||t1(i).sno||' '||t1(i).name);

end if; end loop; end loop; end;