Monday, July 5, 2010

Pl/sql Programmes for beginners

--> set serveroutput on

-->Sum of Given two numbers
begin
dbms_output.put_line('The sum of Two nos : '||(&FirstNumber+&SecondNumber));
end;
/
-->Sum of Given two numbers
declare
m number(3):=&FirstNumber;
n number(3):=&SecondNumber;
Total Number(3);
begin
total:=m+n;
dbms_output.put_line('The sum of Two nos : '||total);
end;
/

declare
vdname dept.dname%type;
begin
select dname into vdname from dept where deptno=(select deptno from emp where empno=&EmployeeNumber);
dbms_output.put_line('Dname is :'||vdname);
end;
/

-->Display the details of employee given by employee number
declare
vname varchar2(15);
vsal number(6,2);
vcom Number(6,2);
vgsal number(6,2);
begin
select ename,sal,comm,sal+nvl(comm,0) into vname,vsal,vcom,vgsal from emp where empno=&EmployeeNumber;
dbms_output.put_line('The details of Employee : '||vname||' '||vsal||' '||vcom||' '||vgsal);
end;
/

-->Display the details of employee given by employee number
declare
vname varchar2(15);
vdno number(4);
vloc varchar2(15);
begin
select e.ename,d.deptno,d.loc into vname,vdno,vloc from emp e,dept d where empno=&EmployeeNumber and e.deptno=d.deptno;
dbms_output.put_line('The details of Employee : '||vname||' '||vdno||' '||vloc);
end;
/
-->Nested Blocks


declare
n number:=100;
begin
declare
m number:=200;
tol number;
begin
tol:=m+n;
dbms_output.put_line('The sum of M,N Numbers : '||tol);
end;
end;


/

declare
n number:=100;
begin
dbms_output.put_line('The Value of N Number is : '||n);
declare
m number:=200;
n number:=300;
tol number;
begin
tol:=m+n;
dbms_output.put_line('The Value of N Number is : '||n);
dbms_output.put_line('The Value of m Number is : '||m);
dbms_output.put_line('The sum of M,N Numbers : '||tol);
end;
dbms_output.put_line('The Value of N Number is : '||n);
end;
/

declare
n number:=100;
begin
dbms_output.put_line('The Value of N Number is : '||n);
declare
m number:=200;
tol number;
begin
n:=300;
tol:=m+n;
dbms_output.put_line('The Value of N Number is : '||n);
dbms_output.put_line('The Value of m Number is : '||m);
dbms_output.put_line('The sum of M,N Numbers : '||tol);
end;
dbms_output.put_line('The Value of N Number is : '||n);
end;
/
--> Lable Blocks

<>
declare
n number:=100;
begin
dbms_output.put_line('The Value of N Number is : '||n);

<>
declare
m number:=200;
tol number;
begin
n:=300;
tol:=m+n;
dbms_output.put_line('The Value of block2 N Number is : '||n);
dbms_output.put_line('The Value of block1 N Number is : '||block1.n);
dbms_output.put_line('The Value of m Number is : '||m);
dbms_output.put_line('The sum of M,N Numbers : '||tol);
end;
dbms_output.put_line('The Value of N Number is : '||n);
end;
/

--> Write a pl sql programwe...........
declare
i std%rowtype;
hm number;
lm number;
tm number;
am number;
begin
select rool,name,greatest(m1,m2,m3,m4,m5),least(m1,m2,m3,m4,m5),m1+m2+m3+m4+m5,round((m1+m2+m3+m4+m5)/5) into i.rool,i.name,hm,lm,tm,am from std
where rool='&EnterTheRoolNumber';
dbms_output.put_line(lpad(' ',34,'*'));
dbms_output.put_line('U r Entering the Rool NO is : '||i.rool);
dbms_output.put_line('The Student name is : '||i.name);
dbms_output.put_line(lpad('Highest marks is : ',22)||hm);
dbms_output.put_line(lpad('Lowest marks is : ',22)||lm);
dbms_output.put_line(lpad('Total marks is : ',22)||tm);
dbms_output.put_line(lpad('Avarage marks is : ',22)||am);
dbms_output.put_line(lpad(' ',34,'*'));
end;
/
--> Hike the sal as 2000 if sal is lessthan 200 for entered empno ?
--> IF-THEN-END IF;
declare
i emp%rowtype;
begin
select empno,sal into i.empno,i.sal from emp where empno='&employee';
if i.sal<2000 then
update emp set sal=sal+2000 where empno=i.empno;
end if;
end;
/
-->IF-THEN-ELSE-END IF;
declare
i emp%rowtype;
begin
select empno,sal into i.empno,i.sal from emp where empno='&employee';
if i.sal<2000 then
update emp set sal=sal+2000 where empno=i.empno;
else
update emp set sal=sal-2000 where empno=i.empno;
end if;
end;
/

--> IF-THEN-ELSE IF-ELSE IF-ELSE-END IF;
declare
am number(8):=&Enter_Avrage_Marks;
grade varchar2(9);
begin
if am>=70 and am<=100 then
grade:='Distinct';
else if am>=60 and am<70 then
grade:='First Class';
else if am>=35 and am<50 then
grade:='Second Class';
else if am<35 then
grade:='Fail';
else
dbms_output.put_line('U r Entering The Marks Is;'||' '||am||' '||'Please Enter the Marks As 0 to 100');
end if;
dbms_output.put_line('The Grade Is :'||' '||grade);
end;
/

--> Case Stmts
declare
Months number:=&Enter_Month_number;
begin
case months
when '1' then
dbms_output.put_line('The Month Is : January');
when '2' then
dbms_output.put_line('The Month Is : FEB');
when '3' then
dbms_output.put_line('The Month Is : Mar');
when '4' then
dbms_output.put_line('The Month Is : APR');
when '5' then
dbms_output.put_line('The Month Is : MAY');
when '6' then
dbms_output.put_line('The Month Is : JUN');
when '7' then
dbms_output.put_line('The Month Is : JUL');
when '8' then
dbms_output.put_line('The Month Is : AUG');
when '9' then
dbms_output.put_line('The Month Is : SEP');
when '10' then
dbms_output.put_line('The Month Is : OCT');
when '11' then
dbms_output.put_line('The Month Is : NOV');
when '12' then
dbms_output.put_line('The Month Is : DEC');
else
dbms_output.put_line('Ur entered Wrong Month number');
end case;
end;
/
-->Insert the values with for loop
begin
for i in 1..5
loop
insert into best values(i,'&name');
end loop;
end;
/
-->Display the below o/p as given number
/*
12*1=12
12*2=24
12*3=36
12*4=48
12*5=60
12*6=72
12*7=84
12*8=96
12*9=108
12*10=120
*/

declare
v number;
n number:=&Enter_Number;
begin
for i in 1..10
loop
v:=n*i;
dbms_output.put_line(n||'*'||i||'='||v);
end loop;
end;
/
-->Display the below o/p as given number
/*
1
12
123
1234
12345
1234
123
12
1
*/
declare
v number;
n number:=&EnterNo;
begin
for i in 1..n
loop
for j in 1..i
loop
v:=v||j;
end loop;
dbms_output.put_line(v);
v:=null;
end loop;
for i in reverse 1..n-1
loop
for j in 1..i
loop
v:=v||j;
end loop;
dbms_output.put_line(v);
v:=null;
end loop;
end;
/

-->Display the below o/p as given number
/*
1
12
123
1234
12345
1234
123
12
1
*/
declare
v number;
begin
for i in 1..&n
loop
for j in 1..i
loop
v:=v||j;
end loop;
dbms_output.put_line(v);
v:=null;
end loop;
for i in reverse 1..&n
loop
for j in 1..i
loop
v:=v||j;
end loop;
dbms_output.put_line(v);
v:=null;
end loop;
end;
/
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare
cursor c

vno deptno.dept%type:=&DeptNo;
vname dname.dept%type:=&dname;
vloc loc.dept%type:=&location;
begin
insert into dept values(vno,vname,vloc);
if sql%found then
dbms_output.put_line(sql%rowcount||'Record Added to Table');
dbms_output.put_line('The data is : '||vno||' '||vname||' '||vloc);
else
dbms_output.put_line('no record Added to table');
end if;
end;
/

-->Goto <>
declare
c number:=&No;
begin
dbms_output.put_line(c);
c:=c+1;
if c>10 then
goto lable1
end if;
<>
dbms_output.put_line('Out of the Stmts');
end;
/
--<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>--

-->DECLARE

declare
vsal number:=500;
cursor c is
select sal into vsal from emp where empno=7788;/*-->Here if use INTO is no use of it*/
begin
dbms_output.put_line('the vsal is:'||vsal);
end;
/
-->OPEN

open ;

-->FETCH
declare
cursor c is
select empno,ename from emp where deptno=30;
vno emp.empno%type;
vname emp.ename%type;
begin
open c;
dbms_output.put_line('the data is:');
loop
fetch c into vno,vname;

exit when c%notfound;
dbms_output.put_line(vno||' '||vname);
end loop;
end;
/


declare
cursor c is
select empno,ename from emp where deptno=30;
i emp%rowtype;
begin
open c;
dbms_output.put_line('the data is:');
loop
fetch c into i.empno,i.ename;
exit when c%notfound;
dbms_output.put_line(i.empno||' '||i.ename);
end loop;
end;
/


declare
cursor c is
select empno,ename from emp where deptno=30;
i emp%rowtype;
begin
open c;
dbms_output.put_line('The Employee Detauils Are :');
dbms_output.put_line(rpad('ENAME',8)||rpad('EMPNO',8));
dbms_output.put_line(rpad('-----',8)||rpad('--------',8));
loop
fetch c into i.empno,i.ename;
exit when c%notfound;
dbms_output.put_line(rpad(i.empno,8)||rpad(i.ename,18));
end loop;
end;
/


declare
cursor c is
select empno,job,sal from emp where deptno=30;
i c%rowtype;
begin
open c;
dbms_output.put_line('the data is:');
dbms_output.put_line(rpad('EMPNO',6)||rpad('JOB',10)||rpad('SAL',9));
dbms_output.put_line(rpad('-----',6)||rpad('--------',10)||rpad('--------',8));

loop
fetch c into i;
exit when c%notfound;
if i.job='CLERK'THEN
i.sal:=i.sal+1000;
elsif i.job='MANAGER' THEN
i.sal:=i.sal+2000;
elsif i.job='SALESMAN' THEN
i.sal:=i.sal+3000;
end if;
update emp set sal=i.sal where empno=i.empno;
dbms_output.put_line(rpad(i.empno,6)||' '||rpad(i.job,10)||' '||i.sal);
end loop;
close c;
end;
/
------------------------>>>>>>>>>>>>>
create table emp_rep(empno number(5),ename varchar2(15),job varchar2(15),deptno number(3));
declare
cursor c is
select empno,ename,job,deptno from emp where deptno=20;
i c%rowtype;
begin
open c;
loop
fetch c into i;
exit when c%notfound;
insert into emp_rep values(i.empno,i.ename,i.job,i.deptno);
dbms_output.put_line(i.empno||' '||i.ename||' '||i.job||' '||i.deptno);
end loop;
close c;
end;
/




/***********************************************************************************************************************************/

dbms_output.put_line('The Employee Details are : ');
dbms_output.put_line(rpad('EMPNO',6)||rpad('ENAME',8)||rpad('JOB',10)||rpad('SAL',6)||rpad('COMM',6)||rpad('DNO',3));
dbms_output.put_line(rpad('-----',6)||rpad('------',8)||rpad('---------',10)||rpad('-----',6)||rpad('-----',6)||rpad('---',3));
dbms_output.put_line(rpad(i.empno,6)||rpad(i.ename,8)||rpad(i.job,10)||rpad(i.sal,6)||rpad(i.comm,6)||rpad(i.deptno,3));

(i.empno,i.ename,i.job,i.sal,i.comm,i.deptno);


/***********************************************************************************************************************************/


--->
declare
cursor c is
select empno,ename,sal basic,sal*0.45 hra,sal*0.25 da,sal*0.15 pf from emp where sal>2000;
i c%rowtype;
gross number;
begin
open c;
dbms_output.put_line('The Employee Details are : ');
dbms_output.put_line(rpad('EMPNO',6)||rpad('ENAME',8)||rpad('Basic',10)||rpad('hra',6)||rpad('da',6)||rpad('pf',3));
dbms_output.put_line(rpad('-----',6)||rpad('------',8)||rpad('---------',10)||rpad('-----',6)||rpad('-----',6)||rpad('---',3));
loop
fetch c into i;
exit when c%notfound;
gross:=i.basic+i.hra+i.da-i.pf;
dbms_output.put_line(rpad(i.empno,6)||rpad(i.ename,8)||rpad(i.basic,10)||rpad(i.hra,6)||rpad(i.da,6)||rpad(i.pf,3));
end loop;
dbms_output.put_line('Gross Salary is : '||' '||gross);
dbms_output.put_line('The no of emps eligible for :'||c%rowcount);
close c;
end;
/



declare
cursor c is
select empno,ename,sal basic,sal*0.45 hra,sal*0.25 da,sal*0.15 pf from emp where sal>2000;
i c%rowtype;
gross number;
begin
open c;
dbms_output.put_line('The Employee Details are : ');
dbms_output.put_line(rpad('EMPNO',6)||rpad('ENAME',8)||rpad('Basic',10)||rpad('hra',9)||rpad('da',9)||rpad('pf',9)||'gross');
dbms_output.put_line(rpad('-----',6)||rpad('------',8)||rpad('---------',10)||rpad('-----',9)||rpad('-----',9)||rpad('---',9)||'-----');
loop
fetch c into i;
if c%found then
gross:=i.basic+i.hra+i.da-i.pf;
dbms_output.put_line(rpad(i.empno,6)||rpad(i.ename,8)||rpad(i.basic,10)||rpad(i.hra,9)||rpad(i.da,9)||rpad(i.pf,9)||gross);
else
exit;
end if;
end loop;
dbms_output.put_line('The no of emps eligible for PF is:'||c%rowcount);

close c;
end;
/
--->


declare
cursor c is
vno deptno.dept%type:=&DeptNo;
vname dname.dept%type:=&dname;
vloc loc.dept%type:=&location;
begin
open c;
loop
fetch c into vno,vname,vloc;
if c%found then
insert into dept values(vno,vname,vloc);
dbms_output.put_line(c%rowcount||'Record Added to Table');
dbms_output.put_line('The data is : '||vno||' '||vname||' '||vloc);
else
dbms_output.put_line('no record Added to table');
end if;
end loop;
close c;
end;
/



declare
cursor pc is /* primary cursor */
select 'x' from emp where ename=upper('&Entername');
dv char(1);/* Dummy variable */
begin
open pc;
fetch pc into dv;
if pc%found then
dbms_output.put_line('The Ename Is correct');
else
dbms_output.put_line('The Ename Is not correct Please enter correct Ename');
end if;
close c;
end;

declare
cursor c is /* primary cursor */
select 'x' from emp where ename=upper('&Entername');
dvn emp%rowtype;
dv char(1);/* Dummy variable */
begin
open pc;
fetch pc into dv,dvn.deptno;
if pc%found then
dbms_output.put_line('The Ename Is correct');
if dvn.deptno:='&num' then
dbms_output.put_line('The Ename Is correct');
else
dbms_output.put_line('The Ename Is not correct Please enter correct Ename');
end if;
else
dbms_output.put_line('The Ename Is not correct Please enter correct Ename');
end if;
close c;
end;
/

-->declare
cursor c(dno number) is
select * from emp where deptno=dno;
i c%rowtype;
bonus number;
begin
open c(&dno);
dbms_output.put_line('The Employee Details are : ');
dbms_output.put_line(rpad('ENAME',8)||rpad('SAL',6)||rpad('JOB',10)||rpad('BONUS',6)||rpad('DNO',3));
dbms_output.put_line(rpad('-----',8)||rpad('----',6)||rpad('---------',10)||rpad('-----',6)||rpad('---',3));
loop
fetch c into i;
exit when c%notfound;
if i.job='CLERK' then
bonus:=i.sal+nvl(i.comm,2)*2;
elsif i.job='SALESMAN' then
bonus:=i.sal+nvl(i.comm,2)*3;
else
bonus:=i.sal+nvl(i.comm,2)*4;
end if;
dbms_output.put_line(rpad(i.ename,6)||' '||rpad(i.sal,6)||' '||rpad(i.job,10)||' '||BONUS||' '||rpad(i.deptno,4));
end loop;
close c;
end;

--> 26-03-10

DECLARE
CURSOR C IS
SELECT empno,ename,sal FROM emp WHERE deptno=30;
BEGIN
FOR i IN c
LOOP
IF i.sal<3000 THEN
UPDATE emp SET sal=sal+1000 WHERE empno=i.empno;
END if;
DBMS_OUTPUT.PUT_LINE('The emp det are : '||i.ename||' '||i.sal);
END LOOP;
END;
/
-->
DECLARE
CURSOR C IS
SELECT job,min(sal) mnsal,max(sal) mxsal,sum(sal) smsal,avg(sal) agsal FROM emp GROUP BY job;
BEGIN
FOR i IN c
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(i.job,10)||' '||RPAD(i.mnsal,6)||' '||RPAD(i.mxsal,6)||' '||RPAD(i.smsal,6)||' '||ROUND(i.agsal));
END LOOP;
END;
/


-->
declare
cursor dc is
select deptno from dept;
cursor ec(pdno number) is
select ename,sal,deptno from emp where deptno=pdno;
vdno dept.deptno%type;
i ec%rowtype;
begin
open dc;
loop
fetch dc into vdno;
exit when dc%notfound;
open ec(vdno);
loop
fetch ec into i;
exit when ec%notfound;
dbms_output.put_line(rpad(i.ename,8)||' '||rpad(i.sal,6)||' '||i.deptno);
end loop;
dbms_output.put_line('*********************');
close ec;
end loop;
close dc;
end;
/

declare
cursor dc is
select dname,deptno from dept;
i dc%rowtype;
cursor ec is
select ename,sal,deptno from emp;
j ec%rowtype;
begin
open dc;
loop
fetch dc into i;
exit when dc%notfound;
dbms_output.put_line(rpad(i.dname,8)||' '||i.deptno);
close dc;
dbms_output.put_line('*********************');
open ec;
loop
fetch ec into j;
exit when ec%notfound;
dbms_output.put_line(rpad(j.ename,8)||' '||rpad(j.sal,6)||' '||j.deptno);
end loop;
close ec;
end;
/


--->
declare
cursor c is
select empno,ename,sal basic,sal*0.45 hra,sal*0.25 da,sal*0.15 pf from emp where sal>2000;
i c%rowtype;
gross number;
begin
open c;
dbms_output.put_line('The Employee Details are : ');
dbms_output.put_line(rpad('EMPNO',6)||rpad('ENAME',8)||rpad('Basic',10)||rpad('hra',6)||rpad('da',6)||rpad('pf',3));
dbms_output.put_line(rpad('-----',6)||rpad('------',8)||rpad('---------',10)||rpad('-----',6)||rpad('-----',6)||rpad('---',3));
loop
fetch c into i;
exit when c%notfound;
gross:=i.basic+i.hra+i.da-i.pf;
dbms_output.put_line(rpad(i.empno,6)||rpad(i.ename,8)||rpad(i.basic,10)||rpad(i.hra,6)||rpad(i.da,6)||rpad(i.pf,3));
end loop;
dbms_output.put_line('Gross Salary is : '||' '||gross);
dbms_output.put_line('The no of emps eligible for :'||c%rowcount);
close c;
end;
/


CREATE TABLE emp1
(
Num NUMBER(5),
NAME VARCHAR2(15),
basic NUMBER(6,2),
hra NUMBER(6,2),
-- da NUMBER(6,2)
-- pf NUMBER(6,2),
-- gross NUMBER(6,2),
deptno number(3)
);

DECLARE
CURSOR c1 IS
SELECT unique deptno FROM emp;
CURSOR c2(dno NUMBER) IS
SELECT empno num,ename name,sal basic,sal*0.45 hra,deptno from emp where deptno=dno;
BEGIN
-- DELETE FROM emp1;
-- COMMIT;
dbms_output.put_line('The Employee Details are : ');
dbms_output.put_line(rpad('NUM',6)||rpad('NAME',8)||rpad('Basic',10)||rpad('hra',6)||rpad('da',6)||rpad('pf',6)||rpad('deptno',3));
dbms_output.put_line(rpad('-----',6)||rpad('------',8)||rpad('---------',10)||rpad('-----',6)||rpad('-----',6)||rpad('---',6)||rpad('------',3));
FOR i IN c1
LOOP
FOR j IN c2(i.deptno)
LOOP
INSERT INTO emp1 VALUES(j.num,j,name,j.basic,j.hra,j.deptno);
END LOOP;
INSERT INTO emp1(num) VALUES(NULL);
END LOOP;
END;
/



-->27-03-10
declare
cursor c is
select ename,sal,deptno from emp FOR UPDATE;
v c%rowtype;
begin
open c;
loop
fetch c into v;
exit when c%notfound;
if v.deptno=10 then
update emp set sal=sal+2000 where current of c;
end if;
end loop;
close c;
end;

-->
declare
cursor c is
select ename,deptno from emp FOR UPDATE;
v c%rowtype;
begin
open c;
loop
fetch c into v;
exit when c%notfound;
dbms_output.put_line(rpad(v.ename,10)||v.deptno);
if v.deptno=20 then
delete from emp where current of c;
end if;
end loop;
dbms_output.put_line('records are'||c%rowcount);
close c;
dbms_output.put_line('------------------------');
dbms_output.put_line('After deleting 20th dept');
dbms_output.put_line('-------------------------');
open c;
loop
fetch c into v;
exit when c%notfound;
dbms_output.put_line(rpad(v.ename,10)||v.deptno);
end loop;
dbms_output.put_line('records are'||c%rowcount);
close c;
end;
/



DECLARE
CURSOR c IS
SELECT * FROM EMPSTAGE WHERE status='NEW';
cnt NUMBER(5);
cnt1 NUMBER(5);
ve VARCHAR2(15);
vrr VARCHAR2(25);
I c%ROWTYPE;
BEGIN
OPEN c;
LOOP
FETCH C INTO I;
EXIT WHEN C%NOTFOUND;
SELECT COUNT(*) INTO cnt FROM empstage WHERE empno=i.empno;
SELECT COUNT(*) INTO cnt1 FROM empstage WHERE deptno=i.deptno;
IF cnt>1 THEN
ve:='ERROR';
vrr:='DUPLICATE DATA';
ELSIF i.ename IS NULL THEN
ve:='ERROR';
vrr:='THERE IS NO NAME';
ELSIF i.job not in('ANALYST','CLERK','MANAGER','PRESIDENT','SALESMAN') THEN
ve:='ERROR';
vrr:='JOB IS NOT VALID';
ELSIF i.mgr<>i.empno THEN
ve:='ERROR';
vrr:='THIS EMPLOYEE NO REFERENCE';
ELSIF i.sal NOT IN(1000,6000) THEN
ve:='ERROR';
vrr:='SAL NOT IN RANGE';
ELSIF cnt1=0 THEN
ve:='ERROR';
vrr:='THIS EMPLOYEE NO REFERENCE';
ELSE
ve:='UPDATED';
vrr:='RECORD IS INSERTED';
END IF;
IF ve='ERROR' THEN
UPDATE empstage SET status=ve,errmsg=vrr WHERE empno=i.empno;
ELSE
INSERT INTO EMP VALUES(i.empno,i.ename,i.job,i.mgr,i.sal,i.hiredate,i.sal,i.comm,i.deptno);
end if;
end loop;
close c;
end;
/



--> EXCEPTIONS <--

DECLARE
vno NUMBER(4);
vname varchar2(15);
vdno number(5);
begin
select empno,ename,deptno into vno,vname,vdno from emp where deptno=&dno;
dbms_output.put_line(vname||' Working in : '||vdno);
exception
when no_data_found then
dbms_output.put_line('pls enter correct dptno');
when others then
dbms_output.put_line('some unknoen exceptin');
end;







declare
i emp%rowtype;
begin
select ename,sal into i.ename,i.sal from emp where empno=&eno;
if i.sal<2000 then
raise_application_error(-20448,'no updation');
else
update emp set sal=sal*3 where empno=i.empno;
end if;
exception
when no_data_found then
dbms_output.put_line('pls enter correct dptno');
when others then
dbms_output.put_line('some unknoen exceptin');
end;






declare
i emp%rowtype;
begin
select ename,deptno into i.ename,i.deptno from emp where empno=&eno and ename='&ename';
dbms_output.put_line('The emp : '||i.ename||'Working on : '||i.deptno);
select ename,deptno into i.ename,i.deptno from emp where deptno=&dno;
dbms_output.put_line('The emp : '||i.ename||'Working on : '||i.deptno);
exception
when no_data_found then
dbms_output.put_line('pls enter correct dptno');
when TOO_MANY_ROWS then
dbms_output.put_line('there r more than one rows');
when others then
dbms_output.put_line('Raise exception');
end;





DECLARE
TYPE name IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
n name;
BEGIN
n(-1):='SHIVA';
n(0):='RAMA';
n(1):='KRISHNA';
dbms_output.put_line('THE NAME IS : '||n(-1)||' '||n(0)||' '||n(1));
END;



DECLARE
TYPE name IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE pays IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
n name;
p payS;
i number:=1;
tsal NUMBER:=0;
BEGIN
FOR k IN (SELECT ename ,sal FROM emP)
LOOP
n(i):=k.ename;
p(i):=k.sal;
i:=i+1;
END LOOP;
FOR m IN 1..n.COUNT
LOOP
tsal:=tsal+p(m);
DBMS_OUTPUT.PUT_LINE(RPAD(n(m),9)||' '||RPAD(p(m),6)||' '||tsal);
END LOOP;
END;



DECLARE
TYPE pf_info IS RECORD ( pfno NUMBER(4),amount NUMBER(4,2));
TYPE emp_rec IS RECORD ( eid NUMBER(4),name VARCHAR2(20),basic NUMBER(4),pl.pf_info);
TYPE etab IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
ctr NUMBER;
e etab;
BEGIN
FOR i IN (SELECT empno,ename,sal basic,sal*0.45 pamt FROM emp WHERE sal>2000)
LOOP
e(ctr).eid:=i.empno;
e(ctr).name:=i.ename;
e(ctr).basic:=i.basic;
e(ctr).pf.pno:=i.empno+5;
e(ctr).pf.amount:=i.pamt;

ctr:=ctr+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE DETAILS ARE : ');
FOR k IN 1..e.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(e(k).eid,6)||' '||RPAD(e(k).name,9)||' '||RPAD(e(k).basic,6)
||' '||RPAD(e(k).pf.pfno,6)||' '||RPAD(e(k).pf.amount,6));
END LOOP;
END;


--> BULK COLLECT

DECLARE
TYPE name IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE pays IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
n name;
p pays;
BEGIN
SELECT ename ,sal BULK COLLECT INTO n,p FROM emp;
FOR i IN 1..n.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(n(i),9)||' '||RPAD(p(i),6));
END LOOP;
END;




>CREATE TBALE trac AS SELECT ename,sal FROM emP;
DECLARE
TYPE name IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE pays IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
n name;
p pays;
BEGIN
DBMS_OUTPUT.PUT_LINE(' INSERTING THE RECORDS ARE : ');
SELECT ename ,sal BULK COLLECT INTO n,p FROM emp;
FOR i IN 1..n.COUNT
LOOP
INSERT INTO trac VALUES(n(i),p(i));
DBMS_OUTPUT.PUT_LINE(RPAD(n(i),9)||' '||RPAD(p(i),6));
END LOOP;
END;




BEGIN
DBMS_OUTPUT.PUT_LINE(' INSERTING THE RECORDS ARE : ');
FOR i IN (SELECT ename,sal FROM emp)
LOOP
INSERT INTO trac VALUES(i.ename,i.sal);
DBMS_OUTPUT.PUT_LINE(RPAD(i.ename,9)||' '||RPAD(i.sal,6));
END LOOP;
END;




DECLARE
TYPE name IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE pays IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
n name;
p pays;
CURSOR c1 IS SELECT ename,sal FROM emp;
BEGIN
OPEN c1;
DBMS_OUTPUT.PUT_LINE(' THE EMPLOYEE DETAILS ARE : ');
FETCH c1 BULK COLLECT INTO n,p;
FOR i IN 1..n.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(n(i),9)||' '||RPAD(p(i),6));
END LOOP;
CLOSE c1;
END;




DECLARE
TYPE name IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE pays IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
n name;
p pays;
BEGIN
UPDATE EMP SET SAL=SAL+3000 WHERE JOB='SALESMAN';
DBMS_OUTPUT.PUT_LINE(' THE EMPLOYEE DETAILS ARE : ');
RETURN ENAME,SAL BULK COLLECT INTO n,p;
FOR i IN 1..n.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(n(i),9)||' '||RPAD(p(i),6));
END LOOP;
CLOSE c1;
END;



DECLARE
TYPE name IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE pays IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
n name;
p pays;
BEGIN
UPDATE EMP SET SAL=SAL+3000 WHERE JOB='SALESMAN';
-- DBMS_OUTPUT.PUT_LINE(' THE EMPLOYEE DETAILS ARE : ');
RETURN ENAME , SAL BULK COLLECT INTO n,p;
FOR i IN 1..n.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(n(i),9)||' '||RPAD(p(i),6));
END LOOP;
END;a












--/./././...././/././/./.././././././././././. PROCEDURES ./././.../././././/.//././././././/././././././../.././././../././././././.

CREATE OR REPLACE PROCEDURE intr(P NUMBER,N NUMBER,R NUMBER)
IS
si NUMBER;
ci NUMBER;
BEGIN
si:=p*n*r/100;
ci:=ROUND(p+POWER((1+r/100),n));
DBMS_OUTPUT.PUT_LINE('THE SIMPLE INTREST IS : '||si);
DBMS_OUTPUT.PUT_LINE('THE COMPONENT INTREST IS : '||ci);
END;

--EXCUTING IN SQL BLOCK
EXEC intr(1000,12,2);

--EXCUTING IN PL-SQL BLOCK
BEGIN
intr(1000,12,2);
END;


--> RETRIVE THE PROCEDURE FROM DATABASE
set pagesize 100
select text user_procedure from user_source where name='PB';

--> TO DISPLAY THE ERRORS
SHOW ERROR;
SHOW ERR;



CREATE OR REPLACE PROCEDURE pb
IS
CURSOR c1 IS SELECT empno,ename,job,sal+NVL(comm,0) net FROM emp;
i c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO i;
EXIT WHEN c1%NOTFOUND;
IF i.job='CLERK' THEN
i.net:=i.net*2;
ELSIF i.job='SALESMAN' THEN
i.net:=i.net*3;
ELSE
i.net:=i.net*0.5;
END IF;
UPDATE emp SET sal=sal+i.net WHERE empno=i.empno;
DBMS_OUTPUT.PUT_LINE(RPAD(i.ename,10)||RPAD(i.job,10)||RPAD(i.net,6));
END LOOP;
CLOSE c1;
END;
/

EXEC pb;

Rollback;

--> IN -- OUT -- INOUT MODE

-->IN & OUT MODE
CREATE OR REPLACE PROCEDURE p1(m NUMBER,n OUT NUMBER)/* m IN NUMBER here DEFAUALT IS IN MODE */
IS
BEGIN
n:=m*m;
END;

VAR r NUMBER;
EXEC p1(10,:R);
PRINT :r;

--> MORE THEN ONE OUT MODES
CREATE OR REPLACE PROCEDURE p2(m NUMBER,n OUT NUMBER,o OUT NUMBER)/* DEFAUALT IS IN MODE */
IS
BEGIN
n:=m*m;
o:=m*m*m;
END;

VAR s NUMBER;
VAR c NUMBER;
EXEC p2(10,:s,:c);
PRINT :r;
PRINT :c;

-->INOUT MODE
CREATE OR REPLACE PROCEDURE p1(m INOUT NUMBER)
IS
BEGIN
m:=m*m+m;
END;

VAR r NUMBER;
EXEC :R:=10;
EXEC p1(:R);
PRINT :r;


-->DEPTNO AS I/P DISPLAY THE NO OF EMPLOYEES
CREATE OR REPLACE PROCEDURE d1(dno NUMBER,pno OUT NUMBER)
IS
BEGIN
SELECT COUNT(empno) INTO pno FROM emp WHERE deptno=dno;
END;

VAR bno NUMBER;
EXEC d1(10,:bno);
PRINT :bno;

-->CALL THE PROCEDURE IN DIFFERENT PL SQL BLOCKS
DECLARE
vdno NUMBER:=&DEPTNO;
vno NUMBER;
BEGIN
d1(vdno,vno);
DBMS_OUTPUT.PUT_LINE(' THE '||vdno||' th DEPARTMENT HAVING '||vno||' EMPLOYEES ');
END;









-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>...>>>>>>>>>>>>>>>>>






CREATE TABLE kcb_acc_tab
(
accno NUMBER(15) CONSTRAINT acc_pk PRIMARY KEY,
name VARCHAR2(20) NOT NULL,
acctype VARCHAR2(20) CONSTRAINT acctype_chk check(acctype in('S','C','R')),
/* S-->SAVINGS , C-->CURRENT , R-->RECORING */
odate TIMESTAMP,
bal NUMBER(9,2) CONSTRAINT bal_chk check (bal>=5000)
)

CREATE SEQUENCE S1 START WITH 1001;

INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'EF CORD','S',SYSDATE,9000);
INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'SMITH','R',SYSDATE,19000);
INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'EF CORD','C',SYSDATE,8400);
INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'SCOTT','S',SYSDATE,9000);
INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'ALLEN','S',SYSDATE,9999);
INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'RAM','C',SYSDATE,5000);
INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'BORDER','R',SYSDATE,7653);
INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'SACHIN','S',SYSDATE,5735);
INSERT INTO kcb_acc_tab VALUES(s1.NEXTVAL,'DHONI','C',SYSDATE,12059);



CREATE TABLE kcb_tranc_tab
(
tid NUMBER(15) CONSTRAINT tid_pk PRIMARY KEY,
accno NUMBER(15)/* CONSTRAINT acc_fk FOREIGN KEY(accno) REFERENCES kcb_acc_tab(accno)*/,
ttype VARCHAR2(20) CONSTRAINT ttype_chk check(ttype in('D','W')),
/* D-->DEPOSITE , W-->WITHDRAW */
amt NUMBER(9,2) CONSTRAINT amt_chk check (amt>=100),
/* check (amt IN(
100,200,300,400,500,600,700,800,900,1000,
1100,1200,1300,1400,1500,1600,1700,1800,1900,2000,
2100,2200,2300,2400,2500,2600,2700,2800,2900,3000,
3100,3200,3300,3400,3500,3600,3700,3800,3900,4000,
4100,4200,4300,4400,4500,4600,4700,4800,4900,5000,
5100,5200,5300,5400,5500,5600,5700,5800,5900,6000,
6100,6200,6300,6400,6500,6600,6700,6800,6900,7000,
7100,7200,7300,7400,7500,7600,7700,7800,7900,8000,
8100,8200,8300,8400,8500,8600,8700,8800,8900,9000,
9100,9200,9300,9400,9500,9600,9700,9800,9900,10000)),
*/
tdate TIMESTAMP
);

ALTER TABLE kcb_tranc_tab ADD CONSTRAINT acc_fk FOREIGN KEY(accno) REFERENCES kcb_acc_tab(accno);



CREATE SEQUENCE S2 START WITH 10;


CREATE OR REPLACE PROCEDURE upd_bal
(
paccno kcb_acc_tab.accno%TYPE,
pttype kcb_tranc_tab.ttype%TYPE,
pamt kcb_tranc_tab.amt%TYPE
)
IS
cbal kcb_acc_tab.bal%TYPE;
pactype kcb_acc_tab.acctype%TYPE;

BEGIN
SELECT bal INTO cbal FROM kcb_acc_tab WHERE accno=paccno;
IF pttype='D' THEN
cbal:=cbal+pamt;

ELSIF pttype='W' THEN
IF cbal<5000 AND pactype='S' THEN
RAISE_APPLICATION_ERROR(-20001,'A not too original message.');
-- RAISE_APPLICATION_ERROR(-20111,'NO WITHDRAWING');
ELSIF cbal<10000 AND pactype='C' THEN
RAISE_APPLICATION_ERROR(-20222,'NO WITHDRAWING');
ELSE
cbal:=cbal-pamt;
END IF;

END IF;
UPDATE kcb_acc_tab SET bal=cbal WHERE accno=paccno;
INSERT INTO kcb_tranc_tab VALUES(s2.NEXTVAL,paccno,pttype,pamt,SYSDATE);
END;



ACCNO NAME ACCTYPE ODATE BAL
---------- -------------------- -------------------- ---------------------------------------------
1001 EF CORD S 04-APR-10 01.30.44.000000 PM 9000
1002 SMITH R 04-APR-10 01.30.44.000000 PM 19000
1003 EF CORD C 04-APR-10 01.30.44.000000 PM 8400
1004 SCOTT S 04-APR-10 01.30.44.000000 PM 9000
1005 ALLEN S 04-APR-10 01.30.44.000000 PM 9999
1006 RAM C 04-APR-10 01.30.44.000000 PM 5000
1007 BORDER R 04-APR-10 01.30.44.000000 PM 7653
1008 SACHIN S 04-APR-10 01.30.44.000000 PM 5735
1009 DHONI C 04-APR-10 01.30.44.000000 PM 12059



EXEC upd_bal(1000,'D',45000);
EXEC upd_bal(1002,'D',50800);
EXEC upd_bal(1006,'W',5000);
EXEC upd_bal(1007,'W',8677);
EXEC upd_bal(1001,'D',8750);
EXEC upd_bal(1004,'W',5000);
EXEC upd_bal(1005,'D',9870);
EXEC upd_bal(1002,'W',5070);



SELECT * FROM kcb_acc_tab;
SELECT * FROM kcb_tranc_tab;




-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>







No comments:

Post a Comment