===============================================================
CURSOR (FOR
LOOP)
===============================================================
DECLARE
MyIndexs 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;
Employee EmpCursor
%ROWTYPE;
BEGIN
FOR Employee IN EmpCursor
LOOP
Employees(MyIndexs) := Employee;
MyIndexs :=MyIndexs+1;
END LOOP;
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,'='));
FOR MyIndex IN 1..MyIndexs-1
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));
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 ;
MyIndexs 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;
Employee EmpCursor%ROWTYPE;
BEGIN
FOR Employee IN EmpCursor
LOOP
Employees(MyIndexs) := Employee;
MyIndexs := MyIndexs + 1;
END LOOP;
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,'='));
FOR MyIndex IN 1 ..
MyIndexs-1
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;
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;
MyIndexs 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;
Employee EmpCursor%ROWTYPE;
BEGIN
FOR Employee IN EmpCursor
LOOP
Employees(MyIndexs) := Employee;
MyIndexs := MyIndexs + 1;
END LOOP;
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,'='));
FOR MyIndex IN
1..MyIndexs-1
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;
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%ROWTYPE
:= &GDeptno;
MyCounter NUMBER(2)
:=0;
MyIndexs 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;
Employee EmpCursor%ROWTYPE;
BEGIN
FOR Employee IN EmpCursor
LOOP
Employees(MyIndexs) := Employee;
MyIndexs := MyIndexs + 1;
END LOOP;
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,'='));
FOR MyIndex IN
1..MyIndexs-1
LOOP
IF Employees(MyIndex).Job=V_Job
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;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| MyCounter || ' recode is displayd on Screen....');
END;
/
=================================================================
Top N Employee
Details using procedure
=================================================================
DECLARE
V_Top NUMBER(2) := >opEmp;
PrvTopSal Emp.Sal%TYPE
:=0;
MyIndexC NUMBER(2)
:=1 ;
CURSOR EmpCursor IS
SELECT * FROM Emp
ORDER BY Sal DESC;
TYPE Emp_Table_Type IS TABLE OF EmpCursor%ROWTYPE
INDEX BY BINARY_INTEGER;
EmpTable Emp_Table_Type;
Employee EmpCursor%ROWTYPE;
BEGIN
FOR Employee IN EmpCursor
LOOP
EmpTable(MyIndexC) := Employee;
MyIndexC := MyIndexC + 1;
END LOOP;
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,'='));
FOR MyIndex IN 1..V_Top+1
LOOP
DBMS_OUTPUT.PUT_LINE(
LPAD(EmpTable(MyIndex).Empno,6) || CHR(9) ||
RPAD(EmpTable(MyIndex).EName,8)||' '||
RPAD(EmpTable(MyIndex).Job,9) || ' ' ||
LPAD(EmpTable(MyIndex).Deptno,3)|| ' ' ||
RPAD(EmpTable(MyIndex).HireDate,10) || ' '
||
LPAD(NVL(TO_CHAR(EmpTable(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(EmpTable(MyIndex).Sal,6)|| ' ' ||
LPAD(NVL(TO_CHAR(EmpTable(MyIndex).Comm),'NA'),6));
IF
PrvTopSal = EmpTable(MyIndex).Sal
THEN
V_Top
:= V_Top+1 ;
PrvTopSal :=
EmpTable(MyIndex).Sal;
ELSE
PrvTopSal
:= EmpTable(MyIndex).Sal;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| V_Top || ' 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;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH
EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
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,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex) OR
Employees(MyIndex).SalRank > V_Top;
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 wuth out
contacting database
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
MyIndexC 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;
Employee EmpCursor%ROWTYPE;
V_MgrName Emp.Ename%TYPE;
BEGIN
FOR Employee IN EmpCursor
LOOP
Employees(MyIndexC) := Employee;
MyIndexC := MyIndexC + 1;
END LOOP;
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,'='));
FOR MyIndex IN 1..MyIndexC
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex) ;
IF
Employees(MyIndex).Mgr IS NOT NULL THEN
LOOP EXIT WHEN NOT
Employees.EXISTS(InnerIndex)
OR Employees(InnerIndex).Empno =
Employees(MyIndex).Mgr ;
InnerIndex := InnerIndex +1;
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 );
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| (MyIndexC-1) || '
recode is displayd on Screen....');
END;
No comments:
Post a Comment