CURSOR (WHILE LOOP)
===============================================================
CURSOR (WHILE
LOOP)
===============================================================
DECLARE
V_EmpRCount NUMBER(2);
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp;
BEGIN
OPEN EmpCursor;
FETCH EmpCursor INTO Employees(MyIndex);
WHILE EmpCursor%FOUND
LOOP
MyIndex := MyIndex + 1;
FETCH
EmpCursor INTO Employees(MyIndex);
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE('====================Employee
informations are shown below ======================');
DBMS_OUTPUT.PUT_LINE(CHR(9)
|| CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName
Job Deptno HireDate
MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
WHILE
Employees.EXISTS(MyIndex)
LOOP
DBMS_OUTPUT.PUT_LINE(
LPAD(Employees(1).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' '
||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| Employees.COUNT || ' recode is
displayd on Screen....');
END;
================================================================= Employee
salary summery using procedure
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
V_TotalSal NUMBER := 0 ;
V_Dept10Sal NUMBER := 0 ;
V_Dept20Sal NUMBER := 0 ;
V_Dept30Sal NUMBER := 0 ;
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp;
BEGIN
OPEN EmpCursor;
FETCH EmpCursor INTO Employees(MyIndex);
WHILE EmpCursor%FOUND
LOOP
MyIndex := MyIndex + 1;
FETCH
EmpCursor INTO Employees(MyIndex);
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE(RPAD('==================Employee
informations are shown below ',80,'='));
DBMS_OUTPUT.PUT_LINE(CHR(9)
|| CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('
EName Job Deptno
Salary ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',40,'='));
WHILE
Employees.EXISTS(MyIndex)
LOOP
DBMS_OUTPUT.PUT_LINE(
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9)
|| ' ' ||
LPAD(Employees(MyIndex).Deptno,3)||
' ' ||
LPAD(Employees(MyIndex).Sal,6));
V_TotalSal :=V_TotalSal +
Employees(MyIndex).Sal;
IF Employees(MyIndex).Deptno =10 THEN
V_Dept10Sal
:=V_Dept10Sal +
Employees(MyIndex).Sal;
ELSIF
Employees(MyIndex).Deptno =20 THEN
V_Dept20Sal
:=V_Dept20Sal +
Employees(MyIndex).Sal;
ELSIF
Employees(MyIndex).Deptno =30 THEN
V_Dept30Sal
:=V_Dept30Sal +
Employees(MyIndex).Sal;
END IF;
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| Employees.COUNT || ' recode is
displayd on Screen....');
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| 'Employee salary Summary ' || CHR(10) );
DBMS_OUTPUT.PUT_LINE('Total
Investment of the company (Monthly) for
Department 10 is ' || TO_CHAR(V_Dept10Sal,'99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total
Investment of the company (Yearly) for
Department 10 is ' ||
TO_CHAR(V_Dept10Sal*12,'9G99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total
Investment of the company (Monthly) for
Department 20 is ' || TO_CHAR(V_Dept20Sal,'99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total
Investment of the company (Yearly) for
Department 20 is ' ||
TO_CHAR(V_Dept20Sal*12,'9G99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total
Investment of the company (Monthly) for
Department 30 is ' || TO_CHAR(V_Dept30Sal,'99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total
Investment of the company (Yearly) for
Department 30 is ' ||
TO_CHAR(V_Dept30Sal*12,'9G99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total
Investment of the company (Monthly) ' ||
TO_CHAR(V_TotalSal,'99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total
Investment of the company (Yearly) ' ||
TO_CHAR(V_TotalSal*12,'9G99G999D99'));
END;
=================================================================
Department wise
employee data
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
V_Deptno NUMBER(2)
:= &GDeptno;
MyCounter NUMBER(2)
:=0;
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp;
BEGIN
OPEN EmpCursor;
FETCH
EmpCursor INTO Employees(MyIndex);
WHILE EmpCursor%FOUND
LOOP
MyIndex := MyIndex + 1;
FETCH
EmpCursor INTO Employees(MyIndex);
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE('====================Employee
informations are shown below ======================');
DBMS_OUTPUT.PUT_LINE(CHR(9)
|| CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName
Job Deptno HireDate
MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
WHILE
Employees.EXISTS(MyIndex)
LOOP
IF
Employees(MyIndex).Deptno=V_Deptno THEN
DBMS_OUTPUT.PUT_LINE(
LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' '
||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
MyCounter
:=MyCounter +1;
END IF;
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| MyCounter || ' recode is displayd on Screen....');
END;
=================================================================
Job wise employee
data
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
V_Job Emp.Job%TYPE
:= &GJob;
MyCounter NUMBER(2)
:=0;
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp;
BEGIN
OPEN EmpCursor;
FETCH
EmpCursor INTO Employees(MyIndex);
WHILE EmpCursor%FOUND
LOOP
MyIndex := MyIndex + 1;
FETCH
EmpCursor INTO Employees(MyIndex);
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE('====================Employee
informations are shown below ======================');
DBMS_OUTPUT.PUT_LINE(CHR(9)
|| CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName
Job Deptno HireDate
MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
WHILE
Employees.EXISTS(MyIndex)
LOOP
IF
Employees(MyIndex).Deptno=V_Deptno THEN
DBMS_OUTPUT.PUT_LINE(
LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' '
||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
MyCounter
:=MyCounter +1;
END IF;
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| MyCounter || ' recode is displayd on Screen....');
END;
=================================================================
Top N Employee
Details using procedure
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
V_Top NUMBER(2) := >opEmp;
PrvTopSal Emp.Sal%TYPE :=0;
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp
ORDER BY Sal DESC;
BEGIN
OPEN EmpCursor;
FETCH EmpCursor INTO Employees(MyIndex);
WHILE EmpCursor%FOUND
LOOP
MyIndex := MyIndex + 1;
FETCH
EmpCursor INTO Employees(MyIndex);
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE(RPAD('================Top
' || V_Top ||' Employee informations are
shown below ',80,'='));
DBMS_OUTPUT.PUT_LINE(CHR(9)
|| CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName
Job Deptno HireDate
MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
WHILE
Employees.EXISTS(MyIndex) AND MyIndex
<= V_Top
LOOP
DBMS_OUTPUT.PUT_LINE(
LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' '
||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
IF
PrvTopSal = Employees(MyIndex).Sal
THEN
V_Top := V_Top+1 ;
PrvTopSal :=
Employees(MyIndex).Sal;
ELSE
PrvTopSal := Employees(MyIndex).Sal;
END IF;
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| (MyIndex - 1 ) || ' recode is displayd on Screen....');
END;
=================================================================
Top N Employee
Details using OLAP
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
V_Top NUMBER(2) := >opEmp;
MyIndex NUMBER(2) :=1 ;
CURSOR EmpCursor IS
SELECT
Empno,EName,Job,Deptno,Sal
,DENSE_RANK()
OVER(ORDER BY Sal
DESC) SalRank
FROM Emp;
TYPE Emp_Table_Type IS TABLE OF EmpCursor%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
BEGIN
OPEN EmpCursor;
FETCH EmpCursor INTO Employees(MyIndex);
WHILE EmpCursor%FOUND
LOOP
MyIndex := MyIndex + 1;
FETCH
EmpCursor INTO Employees(MyIndex);
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE(RPAD('================Top
' || V_Top ||' Employee informations are
shown below ',80,'='));
DBMS_OUTPUT.PUT_LINE(CHR(9)
|| CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName
Job Deptno Salary
');
DBMS_OUTPUT.PUT_LINE(RPAD('=',50,'='));
WHILE
Employees.EXISTS(MyIndex) AND Employees(MyIndex).SalRank <= V_Top
LOOP
DBMS_OUTPUT.PUT_LINE(
LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
LPAD(Employees(MyIndex).Sal,6));
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| (MyIndex-1) || '
recode is displayd on Screen....');
END;
=================================================================
Employe manager info with out
contacting database
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
MyIndex NUMBER(2) :=1 ;
InnerIndex NUMBER(2) :=1;
CURSOR EmpCursor IS
SELECT *
FROM Emp;
TYPE Emp_Table_Type IS TABLE OF EmpCursor%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
V_MgrName Emp.Ename%TYPE;
BEGIN
OPEN EmpCursor;
FETCH EmpCursor INTO Employees(MyIndex);
WHILE EmpCursor%FOUND
LOOP
MyIndex := MyIndex + 1;
FETCH
EmpCursor INTO Employees(MyIndex);
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE(RPAD('================Employee
informations are shown below ',80,'='));
DBMS_OUTPUT.PUT_LINE(CHR(9)
|| CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName
Job Deptno Salary
MgrName');
DBMS_OUTPUT.PUT_LINE(RPAD('=',60,'='));
WHILE
Employees.EXISTS(MyIndex)
LOOP
IF
Employees(MyIndex).Mgr IS NOT NULL THEN
WHILE Employees.EXISTS(InnerIndex)
LOOP
IF
Employees(InnerIndex).Empno = Employees(MyIndex).Mgr THEN
EXIT ;
ELSE
InnerIndex := InnerIndex +1;
END IF;
END LOOP;
V_MgrName :=Employees(InnerIndex).EName;
ELSE
V_MgrName :='NC';
END IF;
InnerIndex :=1;
DBMS_OUTPUT.PUT_LINE(
LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
LPAD(Employees(MyIndex).Sal,6) || ' ' ||V_MgrName );
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| (MyIndex-1) || '
recode is displayd on Screen....');
END;
/
/
No comments:
Post a Comment