Cursors
-
Cursors can be defined as private memory where
the data stores temporarily.
- Cursors are used to process the multiple
records.
- Cursors holds the multiple rows which is
written in SQL statement.
Data base
contains two static cursors
1. Implicit
Cursor.
2. Explicit
Cursor.
Implicit Cursor:
- In Implicit Cursor the DML operations will be performed.
- The implicit cursor cannot be named and they
cannot be controlled and referred to another place.
- We can refer the implicit cursor through the attributes only.
Explicit Cursor:
- It is a static cursor.
- It returns multiple records in SQL statements
called Explicit cursor and also this is a record-by-record
process.
- Explicit
Cursor is also known as
‘Active set area’.
Explicit cursor
contains 4 types:
1.Declare
2.Open
3.Close
4.Fetch
Declare:
Before using an explicit cursor, you need to declare it in
the declaration section which is going to use in PL/SQL block.
Syntax: Cursor Cursor name is (Select Statement).
Open: Before
fetching the records, the cursor should be opened.
Syntax: Open Cursor name;
When we open a cursor, Oracle parses the query, binds
variables, and executes the associated SQL stmt.
Fetch: Fetching the
current records or current statement into the variables.
Syntax: FETCH cursor_name INTO
variable_list;
To retrieve all rows in a result, we need to fetch each row
till the last one.
Close:
After fetching all rows, we need to close the cursor with
the CLOSE statement
Syntax: CLOSE cursor_name;
Closing a cursor instructs Oracle to release
allocated memory at an appropriate time.
If you declare a cursor in an anonymous block, procedure,
or function,
the cursor will automatically be closed when the execution of these objects
ends.
However, you must explicitly close package-based
cursors. Note that if you close a cursor that has not opened yet, Oracle will
raise an INVALID_CURSOR exception.
Example:
DECLARE
CURSOR C1 IS
SELECT ENAME
,SAL
FROM EMP;
V_EMPNAME VARCHAR2(10);
V_SAL VARCHAR2(10);
BEGIN
OPEN C1;
FETCH C1
INTO V_EMPNAME
,V_SAL;
DBMS_OUTPUT.PUT_LINE(V_EMPNAME ||' '||V_SAL);
FETCH C1
INTO V_EMPNAME
,V_SAL;
DBMS_OUTPUT.PUT_LINE(V_EMPNAME ||' '||'High salary');
CLOSE C1;
END;
/
Explicit
Cursor Attributes:
Every explicit cursor having four attributes.
1.
% not
found.
2.
% found.
3.
% is
open.
4.
%row count.
1.%found:
- This attribute returns Boolean value either true or
false.
- This attribute returns true when fetch statement
returns at least one record.
- This attribute returns false when fetch statement
does not return any records.
Syntax: cursor_name % found
Example:
DECLARE
CURSOR C1 IS
SELECT *
FROM EMP
WHERE ENAME = :ENAME;
I EMP%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO I;
IF C1%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee exists: ' || I.ENAME || ' ' || I.SAL);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee does not exist');
END IF;
CLOSE C1;
END;
2.%
not found:
- This is opposite to found attributes.
-
-
Syntax: cursor_name % notfound.
Example:
CURSOR C1 IS
SELECT ENAME
, SAL
FROM EMP;
v_ename VARCHAR2(10);
v_sal VARCHAR2(10);
BEGIN
LOOP
FETCH C1
INTO v_ename
, v_sal;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename || ' ' || v_sal);
END LOOP;
CLOSE C1;
END;
3.% is Open :
- This attribute returns
Boolean value either true or false.
- This attribute returns true
when cursor already open it returns false when cursor is not open.
Syntax: Cursor_name % isopen.
Example:
CURSOR C1 IS
SELECT *
FROM EMP;
I EMP%ROWTYPE;
BEGIN
THEN
OPEN C1;
END IF;
LOOP
FETCH C1
INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME || ' ' || I.SAL);
END LOOP;
CLOSE C1;
4.%row count :
- This attribute returns number datatype. It counts number
of records numbers fetched from the cursor.
Syntax: Cursor_name % rowcount.
Example:
CURSOR C1 IS
SELECT ENAME
, SAL
FROM EMP
ORDER BY SAL DESC;
v_ename VARCHAR2(10);
v_sal VARCHAR2(10);
BEGIN
LOOP
FETCH C1 INTO v_ename, v_sal;
EXIT WHEN C1%ROWCOUNT >= 5;
DBMS_OUTPUT.PUT_LINE(v_ename || ' ' || v_sal);
END LOOP;
CLOSE C1;
END;
No comments:
Post a Comment