Use of Cursors Attributes with Explicit cursor
--====================================================
--Cursor
Attributes :-
--====================================================
SQL%ISOPEN
Attribute: this
attribute checkes wheather cursor Is the Cursor Open or not and return boolean value
TRUE OR FALSE respectively.
SQL%FOUND Attribute: Were Any Rows Affected and return boolean value
TRUE OR FALSE respectively.
SQL%NOTFOUND Attribute: Were
No Rows Affected and return boolean value
TRUE OR FALSE respectively.
SQL%ROWCOUNT Attribute: How
Many Rows Were Affected or not and return
number of rows are affected by this DML statements.
SQL%BULK_ROWCOUNT
:
Getting Number of Rows Affected by FORALL Statement
SQL%BULK_EXCEPTIONS
:
Handling FORALL Exceptions After FORALL Statement Completes
--====================================================
-- Use of Cursors
Attributes with Explicit cursor
--====================================================
--Explicit cursor
/*
An explicit
cursor is a session cursor that you construct and manage.
You must declare
and define an explicit cursor, giving it a name and associating it with a query
(typically, the query returns multiple rows).
Syntax:- The
syntax for the value of an explicit cursor attribute is cursor_name immediately
followed by attribute (for example, c1%ISOPEN).
*/
|
EMPNO
|
ENAME
|
HIREDATE
|
DOB
|
GENDER
|
SAL
|
DEPTNO
|
CREATION_DATE
|
JOB
|
COMM
|
|
1000
|
Sachin
|
11-Sep-85
|
01-Mar-20
|
M
|
25000
|
20
|
21-04-2020 18:46
|
MANAGER
|
|
|
1002
|
Kumar
|
01-Aug-82
|
11-Mar-20
|
M
|
125000
|
20
|
21-04-2020 18:46
|
|
|
|
1008
|
Sonu
|
11-Sep-85
|
01-Mar-20
|
M
|
2500
|
10
|
21-04-2020 18:46
|
CLERK
|
|
|
1003
|
Chavi
|
25-Mar-81
|
15-Feb-20
|
F
|
5000
|
20
|
21-04-2020 18:46
|
CLERK
|
|
|
1006
|
Ravi
|
11-Sep-89
|
01-Mar-19
|
M
|
2500
|
10
|
21-04-2020 18:46
|
CLERK
|
|
|
1005
|
Vinod
|
18-Sep-86
|
01-Mar-20
|
M
|
17000
|
30
|
21-04-2020 18:46
|
DEVELOPER
|
|
|
1004
|
Neel
|
28-Jan-88
|
19-Jul-19
|
M
|
27000
|
30
|
21-04-2020 18:46
|
MANAGER
|
|
|
1007
|
Manoj
|
11-Sep-85
|
09-Mar-18
|
M
|
18000
|
10
|
21-04-2020 18:46
|
DEVELOPER
|
|
|
1001
|
Amit
|
11-Sep-80
|
01-Jan-20
|
M
|
10000
|
40
|
21-04-2020 18:46
|
DEVELOPER
|
|
--====================================================
--Example %ISOPEN Explicit Cursor Attribute | it return
:- Boolean Value
DECLARE
V_name XX_EMP_TL.ENAME%TYPE;
V_salary XX_EMP_TL.sal%TYPE;
CURSOR c1 IS
SELECT ENAME, SAL FROM XX_EMP_TL;
BEGIN
IF NOT
c1%ISOPEN THEN
OPEN c1;
END IF;
LOOP
FETCH c1 INTO
V_name,
V_salary;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line ('Employee
:- '||V_name||' and
salary :- '||
V_salary);
END LOOP;
IF c1%ISOPEN THEN
CLOSE c1;
END IF;
END;
OUTPUT:-
Employee :-
Sachin and
salary :- 25000
Employee :- Rena
and
salary :- 125000
Employee :- Sonu
and
salary :- 2500
Employee :-
Chavi and
salary :- 5000
Employee :- Ravi
and
salary :- 2500
Employee :-
Vinod and
salary :- 17000
Employee :- Rani
and
salary :- 27000
Employee :-
Manoj and
salary :- 18000
Employee :- Amit
and
salary :- 10000
--====================================================
--Example %FOUND Explicit Cursor Attribute | it return
:- Boolean Value
DECLARE
CURSOR c1 IS
SELECT ENAME, SAL FROM XX_EMP_TL;
V_name XX_EMP_TL.ENAME%TYPE;
V_salary XX_EMP_TL.sal%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO
V_name,
V_salary;
IF c1%FOUND THEN -- fetch succeeded
dbms_output.put_line ('Employee
:- '||V_name||' and
salary :- '||
V_salary);
ELSE -- fetch failed
EXIT;
END IF;
END LOOP;
close c1;
END;
OUTPUT:-
Employee :-
Sachin and
salary :- 25000
Employee :- Rena
and
salary :- 125000
Employee :- Sonu
and
salary :- 2500
Employee :-
Chavi and
salary :- 5000
Employee :- Ravi
and
salary :- 2500
Employee :-
Vinod and
salary :- 17000
Employee :- Rani
and
salary :- 27000
Employee :-
Manoj and
salary :- 18000
Employee :- Amit
and
salary :- 10000
--====================================================
--Example %NOTFOUND Explicit Cursor Attribute | it
return :- Boolean Value
DECLARE
CURSOR c1 IS
SELECT ENAME, SAL FROM XX_EMP_TL;
V_name XX_EMP_TL.ENAME%TYPE;
V_salary XX_EMP_TL.sal%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO
V_name,
V_salary;
IF c1%NOT FOUND THEN -- fetch succeeded
EXIT;
ELSE -- fetch failed
dbms_output.put_line ('Employee
:- '||V_name||' and
salary :- '||
V_salary);
END IF;
END LOOP;
close c1;
END;
OUTPUT:-
Employee :-
Sachin and
salary :- 25000
Employee :- Rena
and
salary :- 125000
Employee :- Sonu
and
salary :- 2500
Employee :-
Chavi and
salary :- 5000
Employee :- Ravi
and
salary :- 2500
Employee :-
Vinod and
salary :- 17000
Employee :- Rani
and
salary :- 27000
Employee :-
Manoj and
salary :- 18000
Employee :- Amit
and
salary :- 10000
--====================================================
--Example %ROWCOUNT Explicit Cursor Attribute | it
return :- Number Value
DECLARE
CURSOR c1 IS
SELECT ENAME, SAL FROM XX_EMP_TL;
V_name XX_EMP_TL.ENAME%TYPE;
V_salary XX_EMP_TL.sal%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO
V_name,
V_salary;
IF c1%NOTFOUND THEN -- fetch succeeded
dbms_output.put_line
('total
no or record in this query is :- '||c1%ROWCOUNT);
EXIT;
ELSE -- fetch failed
dbms_output.put_line (c1%ROWCOUNT ||' :-
Employee Name:- '||V_name||' and salary :- '||
V_salary);
END IF;
END LOOP;
close c1;
END;
OUTPUT:-
1 :-
Employee Name:-
Sachin and
salary :- 25000
2 :-
Employee Name:- Rena
and
salary :- 125000
3 :-
Employee Name:- Sonu
and
salary :- 2500
4 :-
Employee Name:-
Chavi and
salary :- 5000
5 :-
Employee Name:- Ravi
and
salary :- 2500
6 :-
Employee Name:-
Vinod and
salary :- 17000
7 :-
Employee Name:- Rani
and
salary :- 27000
8 :-
Employee Name:-
Manoj and
salary :- 18000
9 :-
Employee Name:- Amit
and
salary :- 10000
total no or record in this
query is :- 9
Comments
Post a Comment