CURSOR (SIMPLE LOOP)
============================================================
PL/SQL
TABLE
============================================================
DECLARE
V_Empno Emp.Empno%TYPE := &EmpID;
Employee Emp%ROWTYPE;
BEGIN
IF V_Empno IS NOT NULL THEN
SELECT *
INTO Employee
FROM Emp
WHERE Empno=V_Empno;
IF Employee.Empno IS NOT
NULL THEN
DBMS_OUTPUT.PUT_LINE('Details of the employee
that yoy want to delete ');
DBMS_OUTPUT.PUT_LINE('Name : ' ||
Employee.EName);
DBMS_OUTPUT.PUT_LINE('Desgnation : ' || Employee.Job);
DBMS_OUTPUT.PUT_LINE(' Deptno : ' || Employee.Deptno);
DBMS_OUTPUT.PUT_LINE(' Sal : ' || Employee.Sal);
DBMS_OUTPUT.PUT_LINE(' Comm : ' ||
NVL(TO_CHAR(Employee.Comm),'Can''t Applied'));
DBMS_OUTPUT.PUT_LINE(' MgeNo. : ' || NVL(TO_CHAR(Employee.Mgr),'Can''t
Controled'));
DBMS_OUTPUT.PUT_LINE(' HireDate : ' || Employee.HireDate);
INSERT INTO EmpAudit
VALUES
(Employee.Empno,Employee.EName,Employee.Job, Employee.Mgr,Employee.HireDate,
Employee.Sal,Employee.Comm,
Employee.Deptno,USER,USERENV('TERMINAL'),SYSDATE );
DBMS_OUTPUT.PUT_LINE('Data Deletion Start ');
DELETE FROM Emp
WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('Employee recode is
deleted successfully.'|| CHR(10)||'
Thank you for using our product.. ');
ELSE
DBMS_OUTPUT.PUT_LINE('Recode is not found or
some problm in deletion. Sorry Try Again
..'|| CHR(10)||' Thank you for using our
product.. ');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL
Encouted can''t proceed further. '|| CHR(10) ||' Thank you for using our product.. ');
END IF;
END;
DECLARE
V_Empno Emp.Empno%TYPE
:= &EmpID;
Employee Emp%ROWTYPE;
BEGIN
SELECT *
INTO Employee
FROM Emp
WHERE Empno=V_Empno;
IF V_Empno IS NOT NULL THEN
IF Employee.Empno IS NOT
NULL THEN
DBMS_OUTPUT.PUT_LINE('Details of the employee that yoy
want to delete ');
DBMS_OUTPUT.PUT_LINE('Name : ' || Employee.EName);
DBMS_OUTPUT.PUT_LINE('Desgnation : ' || Employee.Job);
DBMS_OUTPUT.PUT_LINE(' Deptno : ' || Employee.Deptno);
DBMS_OUTPUT.PUT_LINE(' Sal : ' || Employee.Sal);
DBMS_OUTPUT.PUT_LINE(' Comm : ' ||
NVL(TO_CHAR(Employee.Comm),'Can''t Applied'));
DBMS_OUTPUT.PUT_LINE(' MgeNo. : ' || NVL(TO_CHAR(Employee.Mgr),'Can''t
Controled'));
DBMS_OUTPUT.PUT_LINE(' HireDate : ' || Employee.HireDate);
DBMS_OUTPUT.PUT_LINE('Data Deletion Start ');
DELETE FROM Emp
WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('Employee recode is deleted
successfully.'|| CHR(10)||' Thank you
for using our product.. ');
ELSE
DBMS_OUTPUT.PUT_LINE('Recode is not found or some problm
in deletion. Sorry Try Again ..'||
CHR(10)||' Thank you for using our
product.. ');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encouted can''t
proceed further. '|| CHR(10)||' Thank
you for using our product.. ');
END IF;
END;
DECLARE
V_EmpRCount NUMBER(2);
MyIndex NUMBER(2)
:=1 ;
V_RowID NUMBER(2)
:= 65 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
BEGIN
SELECT
COUNT(*)
INTO V_EmpRCount
FROM Emp;
LOOP
EXIT WHEN MyIndex > V_EmpRCount ;
SELECT *
INTO
Employees(MyIndex)
FROM
Emp
WHERE SUBSTR(ROWID,18,1)
=CHR(V_RowID);
V_RowID := V_RowID+1;
MyIndex := MyIndex + 1;
END LOOP;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE('=================Employee
informations are shown below ===================');
DBMS_OUTPUT.PUT_LINE(CHR(9)
|| CHR(9) ||' ------------------------------------');
DBMS_OUTPUT.PUT_LINE('EmpNo EName
Job Deptno HireDate
MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE('=================================================================
=========');
LOOP
EXIT
WHEN NOT Employees.EXISTS(MyIndex);
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));
MyIndex := MyIndex + 1;
END LOOP;
END;
DECLARE
V_Index NUMBER(2) :=1;
TYPE Dept_TableType IS TABLE OF
Dept%ROWTYPE
INDEX BY BINARY_INTEGER;
CURSOR DeptCursor
IS
SELECT * FROM
Dept;
DeptTable
Dept_TableType;
BEGIN
OPEN DeptCursor;
LOOP
FETCH DeptCursor INTO
DeptTable(V_Index);
V_Index :=V_Index + 1;
EXIT WHEN
DeptCursor%NOTFOUND;
END LOOP;
CLOSE DeptCursor;
END;
DECLARE
V_Index NUMBER(2) :=1;
TYPE Dept_TableType IS TABLE OF
Dept%ROWTYPE
INDEX BY BINARY_INTEGER;
CURSOR DeptCursor
IS
SELECT * FROM
Dept;
DeptTable Dept_TableType;
BEGIN
OPEN DeptCursor;
LOOP
FETCH DeptCursor INTO
DeptTable(V_Index);
EXIT WHEN
DeptCursor%NOTFOUND;
V_Index :=V_Index + 1;
END LOOP;
CLOSE DeptCursor;
FOR MyIndex IN 1..V_Index-1
LOOP
DBMS_OUTPUT.PUT_LINE(DeptTable(MyIndex).DName);
END LOOP;
END;
===============================================================
CURSOR
(Simple 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;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH EmpCursor INTO Employees(MyIndex);
MyIndex
:= MyIndex + 1;
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,'='));
LOOP
EXIT
WHEN NOT Employees.EXISTS(MyIndex);
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));
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;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH
EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
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,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex);
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;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH
EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
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,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex);
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 :=
UPPER('&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;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH
EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
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,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex);
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;
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;
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 HireDate
MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex) OR
MyIndex > 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)|| ' ' ||
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;
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);
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;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH
EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
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,'='));
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 );
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)
|| (MyIndex-1) || '
recode is displayd on Screen....');
END;
No comments:
Post a Comment