Cursors
An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.
General Syntax for creating a cursor is as given below:
CURSOR cursor_name IS select_statement;
There are four steps in using an Explicit Cursor.
- DECLARE the cursor in the declaration section.
- OPEN the cursor in the Execution Section.
- FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
- CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
How to access an Explicit Cursor?
These are the three steps in accessing the cursor.
1) Open the cursor.
2) Fetch the records in the cursor one at a time.
3) Close the cursor.
1) Open the cursor.
2) Fetch the records in the cursor one at a time.
3) Close the cursor.
General Syntax to open a cursor is:
OPEN cursor_name;
General Syntax to fetch records from a cursor is:
FETCH cursor_name INTO record_name;
OR
FETCH cursor_name INTO variable_list;
General Syntax to close a cursor is:
CLOSE cursor_name;
When a cursor is opened, the first row becomes the current row. When the data is fetched it is copied to the record or variables and the logical pointer moves to the next row and it becomes the current row. On every fetch statement, the pointer moves to the next row. If you want to fetch after the last row, the program will throw an error. When there is more than one row in a cursor we can use loops along with explicit cursor attributes to fetch all the records.
Points to remember while fetching a row:
· We can fetch the rows in a cursor to a PL/SQL Record or a list of variables created in the PL/SQL Block.
· If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.
· If you are fetching a cursor to a list of variables, the variables should be listed in the same order in the fetch statement as the columns are present in the cursor.
· If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.
· If you are fetching a cursor to a list of variables, the variables should be listed in the same order in the fetch statement as the columns are present in the cursor.
General Form of using an explicit cursor is:
DECLARE
variables;
records;
create a cursor;
BEGIN
OPEN cursor;
FETCH cursor;
process the records;
CLOSE cursor;
END;
Explicit Cursor, Lets Look at the example below
Example 1:
DECLARE
emp_rec emp_tbl%rowtype;
CURSOR emp_cur IS
SELECT *
FROM
WHERE salary > 10;
BEGIN
OPEN emp_cur;
FETCH emp_cur INTO emp_rec;
dbms_output.put_line (emp_rec.first_name || ' ' || emp_rec.last_name);
CLOSE emp_cur;
END;
What are Explicit Cursor Attributes?
Oracle provides some attributes known as Explicit Cursor Attributes to control the data processing while using cursors. We use these attributes to avoid errors while accessing cursors through OPEN, FETCH and CLOSE Statements.
When does an error occur while accessing an explicit cursor?
a) When we try to open a cursor which is not closed in the previous operation.
b) When we try to fetch a cursor after the last operation.
b) When we try to fetch a cursor after the last operation.
These are the attributes available to check the status of an explicit cursor.
Attribute
|
Explanation
|
%ISOPEN
|
- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
|
%FOUND
|
- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Returns NULL if cursor is open, but fetch has not been executed.
- Returns TRUE if a successful fetch has been executed.
- Returns FALSE if no row was returned.
|
%NOTFOUND
|
- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Return NULL if cursor is open, but fetch has not been executed.
- Returns FALSE if a successful fetch has been executed.
- Returns TRUE if no row was returned.
|
%ROWCOUNT
|
- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Returns the number of rows fetched.
- The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.
|
Using Loops with Explicit Cursors:
Oracle provides three types of cursors namely SIMPLE LOOP, WHILE LOOP and FOR LOOP. These loops can be used to process multiple rows in the cursor. Here I will modify the same example for each loops to explain how to use loops with cursors.
Cursor with a Simple Loop:
DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur%rowtype;
BEGIN
IF NOT sales_cur%ISOPEN THEN OPEN sales_cur;
END IF;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
|| ' ' ||emp_cur.salary);
END LOOP;
END;
Cursor with a While Loop:
DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur%rowtype;
BEGIN
IF NOT sales_cur%ISOPEN THEN OPEN sales_cur;
END IF;
FETCH sales_cur INTO sales_rec;
WHILE sales_cur%FOUND THEN
LOOP
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
|| ' ' ||emp_cur.salary);
FETCH sales_cur INTO sales_rec;
END LOOP;
END;
Cursor with a FOR Loop:
DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur%rowtype;
BEGIN
FOR emp_rec in sales_cur
LOOP
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
|| ' ' ||emp_cur.salary);
END LOOP;
END;
Cursor with parameters
The basic syntax for a cursor with parameters is:
CURSOR cursor_name (parameter_list)
IS
SELECT_statement;
For example, you could define a cursor called c2 as below.
CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number
from courses_tbl
where subject_id = subject_id_in;
The result set of this cursor is all course_numbers whose subject_id matches the subject_id passed to the cursor via the parameter.
Cursor with return clause
The basic syntax for a cursor with a return clause is:
CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;
For example, you could define a cursor called c3 as below.
CURSOR c3
RETURN courses_tbl%ROWTYPE
IS
SELECT *
from courses_tbl
where subject = 'Mathematics';
The result set of this cursor is all columns from the course_tbl where the subject is Mathematics.
SELECT FOR UPDATE Statement
The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.
The syntax for the Select For Update is:
CURSOR cursor_name
IS
select_statement
FOR UPDATE [of column_list] [NOWAIT];
For example, you could use the Select For Update statement as follows:
CURSOR c1
IS
SELECT course_number, instructor
from courses_tbl
FOR UPDATE of instructor;
If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Ofstatement.
WHERE CURRENT OF Statement
If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Ofstatement.
The syntax for the Where Current Of statement is either:
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.
Updating using the WHERE CURRENT OF Statement
Here is an example where we are updating records using the Where Current Of Statement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999; else
UPDATE courses_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1; COMMIT;
end if; close c1; RETURN cnumber; END;
Deleting using the WHERE CURRENT OF Statement
Here is an example where we are deleting records using the Where Current Of Statement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN
open c1; fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999; else
DELETE FROM courses_tbl
WHERE CURRENT OF c1;
COMMIT; end if; close c1;
RETURN cnumber; END;
Cursor within a cursor
create or replace procedure MULTIPLE_CURSORS_PROC is
v_owner varchar2(40);
v_table_name varchar2(40);
v_column_name varchar2(100);
/* First cursor */
cursor get_tables is
select distinct tbl.owner, tbl.table_name
from all_tables tbl
where tbl.owner = 'SYSTEM';
/* Second cursor */
cursor get_columns is
select distinct col.column_name
from all_tab_columns col
where col.owner = v_owner
and col.table_name = v_table_name;
begin
-- Open first cursor
open get_tables;
loop
fetch get_tables into v_owner, v_table_name;
-- Open second cursor
open get_columns;
loop
fetch get_columns into v_column_name;
end loop;
close get_columns;
end loop;
close get_tables;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;
declare
cursor cd is select * from dept;
cursor ce is select * from emp;
begin
for i in cd loop
dbms_output.put_line(i.deptno||' '||i.dname);
for j in ce loop
if i.deptno = j.deptno then
dbms_output.put_line(j.ename||' '||j.job);
end if;
end loop;
end loop;
end ;
/
REF CURSOR
• A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result.
• To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, and then declare cursor variables of that type.
Syntax of the REF Cursor
Define a REF Cursor TYPE:
TYPE ref_type_name IS REF CURSOR
[RETURN {
cursor_name%ROWTYPE
|ref_cursor_name%ROWTYPE
|record_name%TYPE
|record_type_name
|db_table_name%ROWTYPE
}
];
RETURN
specifies the data type of a cursor variable return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row from a cursor or strongly typed cursor variable. You can use the %TYPE attribute to provide the datatype of a previously declared record.
specifies the data type of a cursor variable return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row from a cursor or strongly typed cursor variable. You can use the %TYPE attribute to provide the datatype of a previously declared record.
Ø cursor_name
An explicit cursor previously declared within the current scope.
An explicit cursor previously declared within the current scope.
Ø ref_cursor_name
An ref cursor previously declared within the current scope.
An ref cursor previously declared within the current scope.
Ø record_name
A user-defined record previously declared within the current scope.
A user-defined record previously declared within the current scope.
Ø record_type_name
A user-defined record type that was defined using the data type specifies RECORD.
Ø db_table_name
A database table or view, which must be accessible when the declaration is elaborated.
A database table or view, which must be accessible when the declaration is elaborated.
Ø %ROWTYPEA record type that represents a row in a database table or a row fetched from a cursor or strongly typed cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
Ø %TYPE
Provides the datatype of a previously declared user-defined record.
Provides the datatype of a previously declared user-defined record.
Ø type_name
A user-defined cursor variable type that was defined as a REF CURSOR.
A user-defined cursor variable type that was defined as a REF CURSOR.
Cursor_variable_declaration:
cursor_variable_name ref_type_name;
OPEN a REF cursor...
OPEN cursor_variable_name
FOR select_statement;
/*To be sure it's not open already:*/
IF NOT cursor_variable_name%ISOPEN THEN
OPEN cursor_variable_name FOR select_statement;
END IF;
Types of REF CURSOR
Strongly Typed: A REF CURSOR that specifies a specific return type
DECLARE
TYPE EmpCurTyp IS REF CURSOR
RETURN emp%ROWTYPE; -- strongly typed ref cursor
cursor1 EmpCurTyp;
BEGIN
NULL;
END;
Weakly Typed: A REF CURSOR that does not specify the return type
DECLARE
TYPE EmpCurTyp IS REF CURSOR -- Weakly typed ref cursor
cursor1 EmpCurTyp;
BEGIN
NULL;
END;
Three statements to control a cursor variable
· OPEN-FOR
· FETCH
· CLOSE
1. OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
2. PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement.
Simple Example:
DECLARE
TYPE EmpCurTyp IS REF CURSOR
RETURN emp%ROWTYPE; -- strong cursor
emp1 EmpCurTyp;
PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS
person emp%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('-----');
DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');
LOOP
FETCH emp_cv INTO person;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name = ' || person.ENAME ||' ' || person.JOB);
END LOOP;
END;
BEGIN
OPEN emp1
FOR SELECT *
FROM emp
WHERE ROWNUM < 11;
process_emp_cv(emp1);
CLOSE emp1;
OPEN emp1
FOR SELECT *
FROM emp
WHERE ENAME LIKE 'R%';
process_emp_cv(emp1);
CLOSE emp1;
END;
Difference between REF CURSOR and CURSOR with Example:
DECLARE
TYPE rc IS REF CURSOR;
CURSOR c IS SELECT * FROM dual;
l_cursor rc;
BEGIN
IF (to_char(SYSDATE,'dd') = 30) THEN
OPEN l_cursor FOR SELECT * FROM emp;
ELSIF (to_char(SYSDATE,'dd') = 29) THEN
OPEN l_cursor FOR SELECT * FROM dept;
ELSE
OPEN l_cursor FOR SELECT * FROM dual;
END IF;
OPEN c;
-----
/* some manipulation here */
-----
CLOSE c;
END;
Comparisons
1. Cursor C will always be select * from dual. The ref cursor can be anything.
2. Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).
3. Ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.
Usage Restrictions
The following are restrictions on cursor variable usage.
1. Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null.
2. Null cannot be assigned to a cursor variable.
3. The value of a cursor variable cannot be stored in a database column.
4. Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement.
Cursor with variable in an "IN CLAUSE"
Question: I'm trying to use a variable in an IN CLAUSE.
Assumptions & declarations
1. Ref_cursor is of type REF CURSOR declared in Package
2. I will to pass a comma separated Numbers as a string
3. This should be used in the query in the IN Clause
4. Execute the Query and Return the Output as REF Cursor
Something similar to the following:
Create or Replace Function func_name (inNumbers in Varchar2)
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
Begin
Open out_cursor
For Select * from Table_name
where column_name in (inNumbers);
Return out_cursor;
End;
I seem to be getting an error when I try the code above. How can I use a variable in an IN CLAUSE?
Answer: Unfortunately, there is no easy way to use a variable in an IN CLAUSE if the variable contains a list of items. We can, however, suggest two alternative options:
Option #1
Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:
Create or Replace Function func_name
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
v1 varchar(2);
v2 varchar(2);
v3 varchar(2);
Begin
v1 := '1';
v2 := '2';
v3 := '3';
Open out_cursor
For Select * from Table_name
where column_name in (v1, v2, v3);
Return out_cursor;
End;
Option #2
You could try storing your values in a table. Then use a sub-select to retrieve the values.
For example:
Create or Replace Function func_name
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
Begin
Open out_cursor
For Select * from Table_name
where column_name in (select values from list_table);
Return out_cursor;
End;
In this example, we've stored our list in a table called list_table.
Oracle Bulk Collect
One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. When rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches. The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.
Starting in Oracle10g, an Oracle bulk collect may be performed by the the PL/SQL engine for you. The PL/SQL engine may automatically use Oracle bulk collect to collect 100 rows at a time because of a cursor loop.
This use of Oracle bulk collect allows your code to process rows without having to setup and execute the Oracle bulk collect operation. The result of this use of Oracle bulk collect is that bulk collecting 75 rows may not provide you with much of a benefit, but using Oracle bulk collect to collect large numbers of rows (many hundreds) will provide increased performance.
Bulk collect is easy to use. First, define the collection or collections that will be collected using the Oracle bulk collect. Next, define the cursor to retrieve the data in the Oracle bulk collect. Finally, bulk collect the data into the collections.
In database versions 9iR2 and later, you can bulk collect into records:
declare
type sales_tab is table of sales%rowtype;
t_sal sales_tab;
begin
select * bulk collect into t_sal from sales;
dbms_output.put_line(t_sal.count);
end;
/
type sales_tab is table of sales%rowtype;
t_sal sales_tab;
begin
select * bulk collect into t_sal from sales;
dbms_output.put_line(t_sal.count);
end;
/
100
For Bulk Collect examples:
No comments:
Post a Comment