A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query. Cursor variables have several advantages over explicit cursors including:
Cursor variables can point to a variety of queries provided the queries have a suitable return type. In contrast, explicit cursors are tied to individual queries.
Cursor variables allow cursors to be opened independently of being processed.
Cursor variables can be passed as parameters between application layers, as well as between server side components.
Cursor variables can be used to reduce client-server network traffic by allowing several cursors to be opened on the server in a single round trip.
The following sections illustrate ways in which cursor variables can be used in PL/SQL code. A thorough understanding of these features will allow programmers to build more efficient PL/SQL APIs and may prove useful as a replacement for passing large collections as parameters between PL/SQL.
The example code in this chapter relies on the cursor_variable_test table which is created with the cursor_variable_test.sql script listed below.
cursor_variable_test.sql
-- Create and populate a test table.
CREATE TABLE cursor_variable_test (
id NUMBER(10),
description VARCHAR2(50)
);
INSERT INTO cursor_variable_test (id, description) VALUES (1, 'One');
INSERT INTO cursor_variable_test (id, description) VALUES (2, 'Two');
INSERT INTO cursor_variable_test (id, description) VALUES (3, 'Three');
COMMIT;
The examples in this chapter regularly make use of the SYS_REFCURSOR type which became available in Oracle9i. To make this script backwards compatible, create the package specification listed in the cursor_api.sql script and replace any reference to “SYS_REFCURSOR” with “cursor_api.refcursor”.
cursor_api.sql
-- *****************************************************************
-- Copyright 2005 by Rampant TechPress Inc.
-- Free for non-commercial use! To license, e-mail info@rampant.cc
-- *****************************************************************
CREATE OR REPLACE PACKAGE cursor_api AS
TYPE refcursor IS REF CURSOR;
END cursor_ api;
/
How cursor variables are defined is the topic of the next section.
Defining Cursor Variables
Cursor variables are defined using a REF CURSOR type. The type is defined in one of two ways:
Strongly Typed - The REF CURSOR type is restricted to an individual return type using the RETURN clause. Although this reduces the chances of runtime errors since column mismatches are detected at compilation time, it also limits the overall flexibility of the type.
Weakly Typed - The RETURN clause is omitted allowing the type to reference any return type. This gives greater flexibility, but it increases the likelihood of runtime errors because column mismatches are not picked up at compile time.
The cursor_variable_definitions.sql script shows how strongly and weakly typed cursor variables are defined.
cursor_variable_definitions.sql
-- *****************************************************************
-- Copyright 2005 by Rampant TechPress Inc.
-- Free for non-commercial use! To license, e-mail info@rampant.cc
-- *****************************************************************
SET SERVEROUTPUT ON SIZE 1000000
-- Strongly typed REF CURSOR.
DECLARE
TYPE t_ref_cursor IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;
c_cursor t_ref_cursor;
l_row cursor_variable_test%ROWTYPE;
BEGIN
DBMS_OUTPUT.put_line('Strongly typed REF CURSOR');
OPEN c_cursor FOR
SELECT *
FROM cursor_variable_test;
LOOP
FETCH c_cursor
INTO l_row;
EXIT WHEN c_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
END LOOP;
CLOSE c_cursor;
END;
/
-- Weakly typed REF CURSOR.
DECLARE
TYPE t_ref_cursor IS REF CURSOR;
c_cursor t_ref_cursor;
l_row cursor_variable_test%ROWTYPE;
BEGIN
DBMS_OUTPUT.put_line('Weakly typed REF CURSOR');
OPEN c_cursor FOR
SELECT *
FROM cursor_variable_test;
LOOP
FETCH c_cursor
INTO l_row;
EXIT WHEN c_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
END LOOP;
CLOSE c_cursor;
END;
/
-- Weakly typed REF CURSOR using SYS_RECURSOR.
DECLARE
c_cursor SYS_REFCURSOR;
l_row cursor_variable_test%ROWTYPE;
BEGIN
DBMS_OUTPUT.put_line('Weakly typed REF CURSOR using SYS_RECURSOR');
OPEN c_cursor FOR
SELECT *
FROM cursor_variable_test;
LOOP
FETCH c_cursor
INTO l_row;
EXIT WHEN c_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
END LOOP;
CLOSE c_cursor;
END;
/
The cursor_variable_definitions.sql script defines three anonymous PL/SQL blocks. Each block defines cursor variables that are opened and closed for a simple query against the test table.
The first block provides an example of a strongly typed cursor variable, while the following two examples are examples of weakly typed cursor variables. The third block uses a cursor variable that is defined using the predefined SYS_REFCURSOR type, negating the need to define a weakly typed REF CURSOR type.
The output from this script shows these variations all function as expected.
SQL> @cursor_variable_definitions.sql
Strongly typed REF CURSOR
1 : One
2 : Two
3 : Three
PL/SQL procedure successfully completed.
Weakly typed REF CURSOR
1 : One
2 : Two
3 : Three
PL/SQL procedure successfully completed.
Weakly typed REF CURSOR using SYS_RECURSOR
1 : One
2 : Two
3 : Three
PL/SQL procedure successfully completed.
The return value of a strongly typed REF CURSOR must be a record which can be defined using the %ROWTYPE and %TYPE attributes or as a record structure. The return_types.sql script gives an example of each method along with an invalid scalar definition.
return_types.sql
-- *****************************************************************
-- Copyright 2005 by Rampant TechPress Inc.
-- Free for non-commercial use! To license, e-mail info@rampant.cc
-- *****************************************************************
-- Strongly typed REF CURSOR using %ROWTYPE.
DECLARE
TYPE t_ref_cursor IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;
c_cursor t_ref_cursor;
l_row cursor_variable_test%ROWTYPE;
BEGIN
DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using %ROWTYPE');
OPEN c_cursor FOR
SELECT *
FROM cursor_variable_test;
LOOP
FETCH c_cursor
INTO l_row;
EXIT WHEN c_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
END LOOP;
CLOSE c_cursor;
END;
/
-- Strongly typed REF CURSOR using %TYPE.
DECLARE
l_row cursor_variable_test%ROWTYPE;
TYPE t_ref_cursor IS REF CURSOR RETURN l_row%TYPE;
c_cursor t_ref_cursor;
BEGIN
DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using %TYPE');
OPEN c_cursor FOR
SELECT *
FROM cursor_variable_test;
LOOP
FETCH c_cursor
INTO l_row;
EXIT WHEN c_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
END LOOP;
CLOSE c_cursor;
END;
/
-- Strongly typed REF CURSOR using RECORD.
DECLARE
TYPE t_return_types_rec IS RECORD (
id NUMBER(10),
description VARCHAR2(50)
);
TYPE t_ref_cursor IS REF CURSOR RETURN t_return_types_rec;
c_cursor t_ref_cursor;
l_row t_return_types_rec;
BEGIN
DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using RECORD');
OPEN c_cursor FOR
SELECT *
FROM cursor_variable_test;
LOOP
FETCH c_cursor
INTO l_row;
EXIT WHEN c_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
END LOOP;
CLOSE c_cursor;
END;
/
-- Strongly typed REF CURSOR using SCALAR type. Expect an error!
DECLARE
TYPE t_ref_cursor IS REF CURSOR RETURN NUMBER;
c_cursor t_ref_cursor;
l_row NUMBER;
BEGIN
DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using SCALAR type. Expect an error!');
OPEN c_cursor FOR
SELECT COUNT(*)
FROM cursor_variable_test;
LOOP
FETCH c_cursor
INTO l_row;
EXIT WHEN c_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(l_row);
END LOOP;
CLOSE c_cursor;
END;
/
The output from this script is listed below. Notice that the three variations on the record return type work correctly, while the scalar return type fails as expected.
SQL> @return_types.sql
Strongly typed REF CURSOR using %ROWTYPE
1 : One
2 : Two
3 : Three
PL/SQL procedure successfully completed.
Strongly typed REF CURSOR using %TYPE
1 : One
2 : Two
3 : Three
PL/SQL procedure successfully completed.
Strongly typed REF CURSOR using RECORD
1 : One
2 : Two
3 : Three
PL/SQL procedure successfully completed.
TYPE t_ref_cursor IS REF CURSOR RETURN NUMBER;
*
ERROR at line 2:
ORA-06550: line 2, column 24:
PLS-00362: invalid cursor return type; 'NUMBER' must be a record type
ORA-06550: line 2, column 3:
PL/SQL: Item ignored
The next section shows how cursor variables can be passed as parameters between database procedures and functions.
No comments:
Post a Comment