Cursors:
Oracle creates a memory area, called as context area for processing an SQL statement, which contains all information needed for processing the statement.
A cursor is a pointer to this context area. PL/SQL controls the context area through cursor. A cursor holds the rows( one or many) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
We can name a cursor so that we can use it in a program to fetch and process the rows returned by the SQL statements, one at a time,
There are two types of cursors:
- Implicit cursor
- Explicit cursor
Implicit cursor:
- Implicit cursors are automatically created by Oracle whenever an SQL statement(DML statements like INSERT, UPDATE AND DELETE) is executed.
- For INSERT operations, the cutsor holds the data that needs to be inserted. For UPDATE AND DELETE operations, the cursors identifies the tows that would be affected.
In PL/SQL, we can refer to the most recent implicit cursor as the SQL CURSOR, which always has the following attributes:
%FOUND : Returns true of Select into, insert, update and delete statement affected one or more rows, else it returns FALSE.
%ISOPEN : always returns false, cause Oracle closes the sql cursor automatically after executing its associated sql statement.
%NOTFOUND : The logical opposite of %FOUND.
%ROWCOUNT: Returns the number of rows affected by an INSERT, UPDATE OR DELETE or returned by a SELECT INTO statement.
Example:
IF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || 'customers selected ');
END IF;
Explicit cursor:
A cursor which we can create ourselves, not a default oracle one, is called explicit cursor.
**A cursor is the name for a structure in memory, called a private SQL area, which the server allocates at runtime for each statement.
Steps to create an Explicit cursor:
- Declare
- Open
- Fetch one or more times
- Close
Example1: retrieving 1 row using explicit cursor:
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
(
c_id IN NUMBER
)
AS
c_name customer.first_name%type;
c_country customer.country%type;
--declaration of cursor
cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;
BEGIN
open c; -- open cursor
-- fetching of data
fetch c INTO c_name, c_country;
dbms_output.put_line('Name: '|| c_name);
dbms_output.put_line('Country: '|| c_country);
Close c; --closing cursor.
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN too_many_rows THEN
dbms_output.put_line('You got more than 1 row!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
Example2: retrieving more than 1 row using explicit cursor:
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
(
c_id IN NUMBER
)
AS
c_name customer.first_name%type;
c_country customer.country%type;
--declaration of cursor
cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;
BEGIN
open c; -- open cursor
LOOP
-- fetching of data
fetch c INTO c_name, c_country;
EXIT WHEN C%NOTFOUND;
dbms_output.put_line('Name: '|| c_name);
dbms_output.put_line('Country: '|| c_country);
END LOOP;
Close c; --closing cursor.
END;
Using Records in cursor:
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
(
c_id IN NUMBER
)
AS
--declaration of cursor
cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;
c_rec customer%rowtype;
BEGIN
open c; -- open cursor
LOOP
-- fetching of data
fetch c INTO c_rec;
EXIT WHEN C%NOTFOUND;
dbms_output.put_line('Name: '|| c_rec.first_name);
dbms_output.put_line('Country: '|| c_rec.country);
END LOOP;
Close c; --closing cursor.
END;
Cursor For Loop:
With cursor FOR loop, the body of the loop is executed for each row returned by the sql query.
Syntax:
FOR customer_rec IN
(Select * from customer where customer_id = 10)
LOOP
Dbms_output.put_line(customer_rec.last_name);
END LOOP;
Example:
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
(
c_id IN NUMBER
)
AS
BEGIN
FOR c_rec IN (SELECT first_name, country FROM customer WHERE customer_id = c_id)
LOOP
dbms_output.put_line('Name: '|| c_rec.first_name);
dbms_output.put_line('Country: '|| c_rec.country);
END LOOP;
END;
Cursor variable and reference cursor:
A cursor variable is a variable that points to a cursor or a result set. We can pass a cursor variable as a arugment to a procedure or function.
Pass a cursor variable back to the host environment that called the program unit - the result set can be consumed for display or other processing.
Syntax:
C_variable SYS_REFCURSOR;
Example:
CREATE OR REPLACE FUNCTION get_names
(
cust_id IN NUMBER
)
RETURN SYS_REFCURSOR
IS
l_return SYS_REFCURSOR;
BEGIN
OPEN l_return FOR
SELECT first_name,last_name FROM customer WHERE customer_id = cust_id;
RETURN l_return;
END get_names;
CREATE OR REPLACE PROCEDURE DISPLAY_NAMES
IS
C_REC SYS_REFCURSOR;
FNAME VARCHAR2(50);
LNAME VARCHAR2(50);
BEGIN
C_REC := GET_NAMES(10);
LOOP
FETCH C_REC INTO FNAME,LNAME;
EXIT WHEN C_REC%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(FNAME);
DBMS_OUTPUT.PUT_LINE(LNAME);
END LOOP;
CLOSE C_REC;
END;
EXECUTE DISPLAY_NAMES();
Exceptions for cursors:
CURSOR_ALREADY_OPEN: we get this exception when we try to open a cursor thats already open.
INVALID_CURSOR: If we try to reference a cursor that does not exist. This may have happened cause we have executed a FETCH cursor or CLOSE cursor before opening the cursor.
No comments:
Post a Comment