Rather than executing a whole query at once, it is possible to set
up a cursor that encapsulates the query, and then read
the query result a few rows at a time. One reason for doing this is
to avoid memory overrun when the result contains a large number of
rows. (However, PL/oraSQL users do not normally need
to worry about that, since FOR
loops automatically use a cursor
internally to avoid memory problems.) A more interesting usage is to
return a reference to a cursor that a function has created, allowing the
caller to read the rows. This provides an efficient way to return
large row sets from functions.
A cursor is a pointer to a private SQL area that stores
information about processing a specific SELECT
or DML statement.
A cursor that is constructed and managed by PL/oraSQL is an implicit cursor. A cursor that you construct and manage is an explicit cursor. You can get information about any session cursor from its attributes (which you can reference in procedural statements, but not in SQL statements).
An implicit cursor is a session cursor that is constructed and
managed by PL/oraSQL. PL/oraSQL opens
an implicit cursor every time you run a SELECT
or
DML statement.You cannot control an implicit cursor,
but you can get information from its attributes.
An implicit cursor closes after its associated statement runs; however,
its attribute values remain available until another SELECT
or DML statement runs.
The most recently run SELECT
or DML statement might be in a different scope.
To save an attribute value for later use, assign it to a local variable immediately. Otherwise,
other operations, such as subprogram invocations,
might change the value of the attribute before you can test it.
SQL%ISOPEN
Attribute: Is the Cursor Open?
SQL%ISOPEN
always returns FALSE
,because an
implicit cursor always closes after its associated statement runs.
SQL%FOUND
Attribute: Were Any Rows Affected?
SQL%FOUND
returns:
NULL
if no SELECT
or DML statement has run,
TRUE
if a SELECT
statement returned one or more rows or a DML statement affected one or more rows,
FALSE
otherwise.
uses SQL%FOUND
to determine if a DELETE
statement affected any rows. For example:
create table employees(salary int, job_id varchar(100), employee_id int, last_name varchar(100), first_name varchar(100), department_id int, hire_date date); create table departments(department_id int, department_name varchar(100), location_id int, staff int); create table locations(country_id varchar(100),city varchar(100),location_id int); create table jobs(job_title varchar(100), job_id varchar(100)); insert into employees values (12308, 'SA_REP', 100, 'huaxiang', 'zhao', 2345, TO_DATE('31-DEC-2005', 'DD-MON-YYYY')); insert into employees values (12301, 'AD_FG', 12301, 'huahai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into employees values (12302, 'SA_REP', 12302, 'huaduo', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into employees values (12302, 'S[HT]_CLERK', 12302, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into employees values (12302, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into employees values (12304, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into employees values (12305, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into employees values (12306, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into employees values (12307, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into employees values (12308, 'AD_MAN', 100, 'huaxiang', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY')); insert into departments values (110, 'asd', 1700, 2345); insert into departments values (2345, 'asd', 1800, 2345); insert into departments values (110, 'asd', 1900, 2345); insert into departments values (110, 'AD_FG', 12302, 2345); insert into locations values ('JP', 'Japan', 1700); insert into locations values ('Toronto', 'Canada', 1800); insert into locations values ('WH', 'Whitehorse', 1900); insert into jobs values ('sdadf', 'AD_FG'); DROP TABLE dept_temp; CREATE TABLE dept_temp AS SELECT * FROM departments; DECLARE dept_no NUMBER := 12; BEGIN DELETE FROM dept_temp WHERE department_id = dept_no; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE ('Delete succeeded for department number ' || dept_no); ELSE DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no); END IF; END; /
SQL%NOTFOUND
Attribute: Were Any Rows Affected?
SQL%NOTFOUND
(the logical opposite of SQL%FOUND
) returns:
NULL
if no SELECT
or DML statement has run,
FALSE
if a SELECT
statement returned one or more rows or a DML statement affected one or more rows.
SQL%ROWCOUNT
Attribute: How Many Rows Were Affected?
SQL%ROWCOUNT
returns:
NULL
if no SELECT
or DML statement has run,
Otherwise, the number of rows returned by a SELECT
statement or affected by a DML statement (an INTEGER
).
uses SQL%ROWCOUNT
to determine the number of rows that were deleted. For example:
DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE mgr_no int := 123005; rowcount int := 0; BEGIN DELETE FROM employees_temp WHERE employee_id = mgr_no; rowcount := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE ('Number of employees deleted: ' || rowcount); END; /
An explicit cursor is a session cursor that is constructed and managed.You must declare and define an explicit cursor, giving it a name and associating it with a query (typically, the query returns multiple rows). Then you can process the query result set in either of these ways:
Open
the explicit cursor, fetch
rows from the result set, and close
the explicit cursor,
Use the explicit cursor in a cursor FOR LOOP
statement.
Unlike an implicit cursor, you can reference an explicit cursor or cursor variable by its name. Therefore, an explicit cursor or cursor variable is called a named cursor.
You can either declare an explicit cursor first and then define it later in the same block, subprogram, or package, or declare and define it at the same time.
An explicit cursor declaration
, which only declares a cursor, has this syntax:
CURSOR cursor_name parameter_list
RETURN return_type;
An explicit cursor definition
has this syntax:
CURSOR cursor_nameparameter_list
RETURN return_type
IS select_statement;
To actually use theexplicit cursor
, you can define it directly without declaring it.
declares and defines three explicit cursors. For example:
DECLARE CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1 CURSOR c2 IS -- Declare and define c2 SELECT employee_id, job_id, salary FROM employees WHERE salary > 2000; CURSOR c1 RETURN departments%ROWTYPE IS -- Define c1, SELECT * FROM departments -- repeating return type WHERE department_id = 110; CURSOR c3 RETURN locations%ROWTYPE; -- Declare c3 CURSOR c3 IS -- Define c3, SELECT * FROM locations -- omitting return type WHERE country_id = 'JP'; BEGIN NULL; END; /
After declaring and defining an explicit cursor, you can open it with the OPEN statement.
You close an open explicit cursor with the CLOSE
statement,
thereby allowing its resources to be reused. After closing a cursor,
you cannot fetch records from its result set or reference its attributes.
After opening an explicit cursor, you can fetch the rows of the query result set with
the FETCH
statement. The basic syntax of a FETCH
statement that returns one row is:
FETCH cursor_name INTO into_clause;
The into_clause
is either a list of variables or a single record variable.
For each column that the query returns, the variable list or record must have a corresponding
type-compatible variable or field. The %TYPE
and %ROWTYPE
attributes are useful for declaring variables and records for use in FETCH
statements.
The FETCH
statement retrieves the current row of the result set,
stores the column values of that row into the variables or record,
and advances the cursor to the next row.
Typically, you use the FETCH
statement inside a LOOP
statement,
which you exit when the FETCH
statement runs out of rows.
To detect this exit condition, use the cursor attribute %NOTFOUND
.
PL/oraSQL does not raise an exception when a FETCH
statement returns no rows.
fetches the result sets of two explicit cursors one row at a time,
using FETCH
and %NOTFOUND inside LOOP
statements.
The first FETCH
statement retrieves column values into variables.
The second FETCH
statement retrieves column values into a record.
The variables and record are declared with %TYPE
and %ROWTYPE
,
respectively. For example:
DECLARE CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK') ORDER BY last_name; v_lastname employees.last_name%TYPE; -- variable for last_name v_jobid employees.job_id%TYPE; -- variable for job_id CURSOR c2 IS SELECT * FROM employees WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]') ORDER BY job_id; v_employees employees%ROWTYPE; -- record variable for row of table BEGIN OPEN c1; LOOP -- Fetches 2 columns into variables FETCH c1 INTO v_lastname, v_jobid; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE (RPAD(v_lastname, 25, ' ') || v_jobid); END LOOP; CLOSE c1; DBMS_OUTPUT.PUT_LINE ('-------------------------------------'); OPEN c2; LOOP -- Fetches entire row into the v_employees record FETCH c2 INTO v_employees; EXIT WHEN c2%NOTFOUND; DBMS_OUTPUT.PUT_LINE (RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id); END LOOP; CLOSE c2; END; /
fetches the first five rows of a result set into five records,
using five FETCH
statements,
each of which fetches into a different record variable.
The record variables are declared with %ROWTYPE
. For example:
DECLARE CURSOR c IS SELECT e.job_id, j.job_title FROM employees e, jobs j WHERE e.job_id = j.job_id AND e.employee_id = 100 ORDER BY last_name; -- Record variables for rows of cursor result set: job1 c%ROWTYPE; job2 c%ROWTYPE; job3 c%ROWTYPE; job4 c%ROWTYPE; job5 c%ROWTYPE; BEGIN OPEN c; FETCH c INTO job1; -- fetches first row FETCH c INTO job2; -- fetches second row FETCH c INTO job3; -- fetches third row FETCH c INTO job4; -- fetches fourth row FETCH c INTO job5; -- fetches fifth row CLOSE c; DBMS_OUTPUT.PUT_LINE (job1.job_title || ' (' || job1.job_id || ')'); DBMS_OUTPUT.PUT_LINE (job2.job_title || ' (' || job2.job_id || ')'); DBMS_OUTPUT.PUT_LINE (job3.job_title || ' (' || job3.job_id || ')'); DBMS_OUTPUT.PUT_LINE (job4.job_title || ' (' || job4.job_id || ')'); DBMS_OUTPUT.PUT_LINE (job5.job_title || ' (' || job5.job_id || ')'); END; /
An explicit cursor query can reference any variable in its scope. When you open an explicit cursor, PL/oraSQL evaluates any variables in the query and uses those values when identifying the result set. Changing the values of the variables later does not change the result set.
the explicit cursor query references the variable factor
.
When the cursor opens, factor
has the value 2. Therefore,
sal_multiple
is always 2 times sal
,
despite that factor
is incremented after every fetch. For example:
DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; BEGIN OPEN c1; -- PL/SQL evaluates factor LOOP FETCH c1 INTO sal, sal_multiple; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('factor = ' || factor); DBMS_OUTPUT.PUT_LINE ('sal = ' || sal); DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple); factor := factor + 1; -- Does not affect sal_multiple END LOOP; CLOSE c1; END; /
Variable in Explicit Cursor Query—Result Set Change. For example:
DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; BEGIN DBMS_OUTPUT.PUT_LINE ('factor = ' || factor); OPEN c1; -- PL/SQL evaluates factor LOOP FETCH c1 INTO sal, sal_multiple; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('sal = ' || sal); DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple); END LOOP; CLOSE c1; factor := factor + 1; DBMS_OUTPUT.PUT_LINE ('factor = ' || factor); OPEN c1; -- PL/SQL evaluates factor LOOP FETCH c1 INTO sal, sal_multiple; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('sal = ' || sal); DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple); END LOOP; CLOSE c1; END; /
When an explicit cursor query includes a virtual column (an expression), that column must have an alias if either of the following is true:
You close an open explicit cursor with the CLOSE
statement,
thereby allowing its resources to be reused. After closing a cursor,
you cannot fetch records from its result set or reference its attributes.
If you try, PL/oraSQL raises the predefined exception INVALID_CURSOR
.
You use the cursor to fetch into a record that was declared with %ROWTYPE
,
You want to reference the virtual column in your program.
The virtual column in the explicit cursor needs an alias for both of the preceding reasons. For example:
DECLARE CURSOR c1 IS SELECT employee_id, (salary * .05) raise FROM employees WHERE job_id LIKE '%_MAN' ORDER BY employee_id; emp_rec c1%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Raise for employee #' || emp_rec.employee_id || ' is $' || emp_rec.raise); END LOOP; CLOSE c1; END; /
You can create an explicit cursor that has formal parameters, and then pass different actual parameters to the cursor each time you open it. In the cursor query, you can use a formal cursor parameter anywhere that you can use a constant. Outside the cursor query, you cannot reference formal cursor parameters.
Creates an explicit cursor whose two formal parameters represent a job and its maximum salary. When opened with a specified job and maximum salary, the cursor query selects the employees with that job who are overpaid (for each such employee, the query selects the first and last name and amount overpaid). Next, the example creates a procedure that prints the cursor query result set (for information about procedures, see PL/SQL Subprograms). Finally, the example opens the cursor with one set of actual parameters, prints the result set, closes the cursor, opens the cursor with different actual parameters, prints the result set, and closes the cursor. For example:
DECLARE CURSOR c (job VARCHAR2, max_sal NUMBER) IS SELECT last_name, first_name, (salary - max_sal) overpayment FROM employees WHERE job_id = job AND salary > max_sal ORDER BY salary; last_name_ employees.last_name%TYPE; first_name_ employees.first_name%TYPE; overpayment_ employees.salary%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE ('----------------------'); DBMS_OUTPUT.PUT_LINE ('Overpaid Stock Clerks:'); DBMS_OUTPUT.PUT_LINE ('----------------------'); OPEN c('ST_CLERK', 5000); LOOP FETCH c INTO last_name_, first_name_, overpayment_; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE (last_name_ || ',' || first_name_ || ', (by' || overpayment_ || ')'); END LOOP; CLOSE c; DBMS_OUTPUT.PUT_LINE ('----------------------'); DBMS_OUTPUT.PUT_LINE ('Overpaid Sales Representatives:'); DBMS_OUTPUT.PUT_LINE ('----------------------'); OPEN c('SA_REP', 10000); LOOP FETCH c INTO last_name_, first_name_, overpayment_; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE (last_name_ || ',' || first_name_ || ', (by' || overpayment_ || ')'); END LOOP; CLOSE c; END; /
When you create an explicit cursor with formal parameters, you can specify default values for them. When a formal parameter has a default value, its corresponding actual parameter is optional. If you open the cursor without specifying the actual parameter, then the formal parameter has its default value.
parameter_name [IN] data_type [:= | default expression
]
The application scenarios are stored procedures, functions, anonymous blocks and packages.
Compared with Oracle, there are some differences. For example, for data_type
,
Oracle supports the declaration of char in the cursor parameter string,
but the default value of lightdb's char length is 1, so for strings whose length exceeds 1,
you need to specify the char length, char(100). But for variable-length character types,
such as varchar, varchar2, etc., the usage is the same as oracle.
And does not support oracle types: string, long, raw, long raw, rowid,
timestamp with local time, etc. The constant type defined by constant is not supported in the package.
Expressions
also have some differences compared to Oracle.
Boolean Expressions
The package supports functions that return boolean types, boolean expressions and boolean literals (NULL supported by oracle does not support),
Functions, stored procedures, and anonymous blocks support functions that return boolean types, boolean constants, Boolean variables, boolean expressions, and boolean literals (NULL supported by oracle does not support).
Character Expressions
The package supports functions that return values of type characer and characer literals,
Functions, stored procedures, and anonymous blocks support functions that return values of type characer, characer constants, characer variables, characer expressions, and characer literals.
Date Expressions
The package supports functions that return values of type date and date literals,
Functions, stored procedures, and anonymous blocks support functions that return values of type date, date constants, date variables, date expressions, and date literals.
Numeric Expressions
Not supported in package: collection, named_cursor, numberic_constant, numberic_variable, placeholder, SQL% cursor attribute, ** (operator not supported),
Not supported in functions, stored procedures, anonymous blocks: collection, named_cursor, placeholder, SQL% cursor attribute, ** (operator not supported).
Case Expressions
search_case_expression and simple_case_expression are supported in packages, functions, stored procedures, anonymous blocks.
Creates an explicit cursor whose formal parameter represents a location ID. The default value of the parameter is the location ID of company headquarters. For example:
DECLARE CURSOR c (location NUMBER DEFAULT 1700) IS SELECT d.department_name, e.last_name manager, l.city FROM departments d, employees e, locations l WHERE l.location_id = location AND l.location_id = d.location_id AND d.department_id = e.department_id ORDER BY d.department_id; dept_name departments.department_name%TYPE; mgr_name employees.last_name%TYPE; city_name locations.city%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE ('DEPARTMENTS AT HEADQUARTERS:'); DBMS_OUTPUT.PUT_LINE ('--------------------------------'); OPEN c; LOOP FETCH c INTO dept_name, mgr_name, city_name; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')'); END LOOP; DBMS_OUTPUT.PUT_LINE ('--------------------------------'); CLOSE c; DBMS_OUTPUT.PUT_LINE ('DEPARTMENTS IN CANADA:'); DBMS_OUTPUT.PUT_LINE ('--------------------------------'); OPEN c(1800); -- Toronto LOOP FETCH c INTO dept_name, mgr_name, city_name; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')'); END LOOP; CLOSE c; OPEN c(1900); -- Whitehorse LOOP FETCH c INTO dept_name, mgr_name, city_name; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')'); END LOOP; CLOSE c; END; /
If you add formal parameters to a cursor, and you specify default values for the added parameters, then you need not change existing references to the cursor. For example:
DECLARE CURSOR c (job VARCHAR2, max_sal NUMBER, hired DATE DEFAULT TO_DATE('31-DEC-1999', 'DD-MON-YYYY')) IS SELECT last_name, first_name, (salary - max_sal) overpayment FROM employees WHERE job_id = job AND salary > max_sal AND hire_date > hired ORDER BY salary; last_name_ employees.last_name%TYPE; first_name_ employees.first_name%TYPE; overpayment_ employees.salary%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE ('--------------------------------'); DBMS_OUTPUT.PUT_LINE ('Overpaid Sales Representatives Hired After 2004:'); DBMS_OUTPUT.PUT_LINE ('--------------------------------'); OPEN c('SA_REP', 10000); LOOP FETCH c INTO last_name_, first_name_, overpayment_; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE (last_name_ || ', (by ' || overpayment_ || ')'); END LOOP; CLOSE c; END; /
The syntax for the value of an explicit cursor attribute is cursor_name
immediately followed by attribute (for example, c1%ISOPEN
).
You close an open explicit cursor with the CLOSE
statement,
thereby allowing its resources to be reused. After closing a cursor,
you cannot fetch records from its result set or reference its attributes.
If you try, PL/oraSQL raises the predefined exception INVALID_CURSOR
.
%ISOPEN
Attribute: Is the Cursor Open?
%ISOPEN
returns true
,if its explicit cursor is open.
false
oterwise.
opens the explicit cursor c1
only if it is not open and closes it only if it is open. For example:
DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; the_name employees.last_name%TYPE; the_salary employees.salary%TYPE; BEGIN IF NOT c1%ISOPEN THEN OPEN c1; END IF; FETCH c1 INTO the_name, the_salary; IF c1%ISOPEN THEN CLOSE c1; END IF; END; /
To use ROWNUM, first execute the command set lightdb_syntax_compatible_type TO 'oracle';
.
%FOUND
Attribute: Were Any Rows Affected?
%FOUND
returns:
NULL
after the explicit cursor is opened but before the first fetch,
TRUE
f the most recent fetch from the explicit cursor returned a row,
FALSE
otherwise.
loops through a result set, printing each fetched row and exiting when there are no more rows to fetch. For example:
DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11 ORDER BY last_name; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE ('Name = ' || my_ename || ' salary = ' || my_salary); ELSE -- fetch failed EXIT; END IF; END LOOP; END; /
%NOTFOUND
Attribute: Were Any Rows Affected?
%NOTFOUND
(the logical opposite of %FOUND
) returns:
NULL
after the explicit cursor is opened but before the first fetch,
FALSE
if the most recent fetch from the explicit cursor returned a row,
TRUE
otherwise.
%NOTFOUND
is useful for exiting a loop when FETCH
fails to return a row. For example:
DECLARE CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11 ORDER BY last_name; rowcount int; name_1 employees.last_name%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO name_1; EXIT WHEN c1%NOTFOUND; rowcount := c1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (rowcount ||'. ' || name_1); IF c1%ROWCOUNT = 5 THEN DBMS_OUTPUT.PUT_LINE ('--- Fetched 5th row ---'); END IF; END LOOP; CLOSE c1; END; /
%ROWCOUNT
Attribute: How Many Rows Were Affected?
%ROWCOUNT
returns:
Zero after the explicit cursor is opened but before the first fetch,
Otherwise, the number of rows fetched (an INTEGER
).
numbers and prints the rows that it fetches and prints a message after fetching the fifth row. For example:
DECLARE CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11 ORDER BY last_name; name_1 employees.last_name%TYPE; rowcount int; BEGIN OPEN c1; LOOP FETCH c1 INTO name_1; EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; rowcount := c1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (rowcount || '. ' || name_1); IF c1%ROWCOUNT = 5 THEN DBMS_OUTPUT.PUT_LINE ('--- Fetched 5th row ---'); END IF; END LOOP; CLOSE c1; END; /
In PL/oraSQL, as in traditional database programming, you use cursors to process query result sets. However, in PL/oraSQL, you can use either implicit or explicit cursors.
The former need less code, but the latter are more flexible. For example, explicit cursors can accept parameters.
The following PL/oraSQL statements use implicit cursors that PL/oraSQL defines and manages for you:
SELECT INTO
,
Implicit cursor FOR LOOP
.
The following PL/oraSQL statements use explicit cursors:
Explicit cursor FOR LOOP
,
OPEN
, FETCH
, and CLOSE
.
Using an implicit cursor, the SELECT INTO
statement retrieves values from
one or more database tables (as the SQL SELECT
statement does) and
stores them in variables (which the SQL SELECT
statement does not do).
If you expect the query to return only one row,
then use the SELECT INTO
statement to store values from
that row in either one or more scalar variables, or one record variable.
If the query might return multiple rows,
but you care about only the nth row,
then restrict the result set to that row with the clause WHERE ROWNUM=n
.
If you must assign a large quantity of table data to variables,
LightDB recommends using the SELECT INTO
statement with
the BULK COLLECT
clause.
The cursor FOR LOOP
statement lets you run a SELECT
statement
and then immediately loop through the rows of the result set.
This statement can use either an implicit or explicit cursor (but not a cursor variable).
If you use the SELECT
statement only in the cursor FOR LOOP statement,
then specify the SELECT
statement inside the cursor
FOR LOOP
statement. Implicit Cursor FOR LOOP
Statement.
For example:
BEGIN FOR item IN ( select last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND employee_id > 120 ) LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /
Explicit Cursor FOR LOOP
Statement. For example:
DECLARE CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND employee_id > 120 ORDER BY last_name; BEGIN FOR item IN c1 LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /
Passing Parameters to Explicit Cursor FOR LOOP
Statement. For example:
DECLARE CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS SELECT * FROM employees WHERE job_id = job AND salary > max_wage; BEGIN FOR person IN c1('ST_CLERK', 3000) LOOP -- process data record DBMS_OUTPUT.PUT_LINE ('Name = ' || person.last_name || ', salary = ' || person.salary || ', Job id= ' || person.job_id); END LOOP; END; /
Cursor FOR LOOP
References Virtual Columns. For example:
BEGIN FOR item IN ( SELECT first_name || ' ' || last_name AS full_name, salary * 10 AS dream_salary FROM employees WHERE ROWNUM < 6 ORDER BY dream_salary DESC, last_name ASC ) LOOP DBMS_OUTPUT.PUT_LINE (item.full_name || ' dreams of making ' || item.dream_salary); END LOOP; END; /
If you process a query result set by looping through it and running another query for each row, then you can improve performance by removing the second query from inside the loop and making it a subquery of the first query.
While an ordinary subquery is evaluated for each table,
a correlated subquery
is evaluated for each row.
Subquery in FROM
Clause of Parent Query. For example:
DECLARE CURSOR c1 IS SELECT t1.department_id, t1.department_name, t2.staff FROM departments t1, ( SELECT department_id, COUNT(*) AS staff FROM employees GROUP BY department_id ) t2 WHERE (t1.department_id = t2.department_id) AND t2.staff > 5 ORDER BY t2.staff; BEGIN FOR dept IN c1 LOOP DBMS_OUTPUT.PUT_LINE ('Department = ' || dept.department_name || ', staff = ' || dept.staff); END LOOP; END; /
Correlated Subquery. For example:
DECLARE CURSOR c1 IS SELECT department_id, last_name, salary FROM employees t WHERE salary >= ( SELECT AVG(salary) FROM employees WHERE t.department_id = department_id ) ORDER BY department_id, last_name; BEGIN FOR person IN c1 LOOP DBMS_OUTPUT.PUT_LINE ('Making above-average salary = ' || person.last_name); END LOOP; END; /
A cursor
variable is like an explicit cursor, except that:
It is not limited to one query. You can open a cursor variable for a query, process the result set, and then use the cursor variable for another query.
You can assign a value to it.
You can use it in an expression.
It can be a subprogram parameter. You can use cursor variables to pass query result sets between subprograms.
It can be a host variable. You can use cursor variables to pass query result sets between PL/oraSQL stored subprograms and their clients.
It cannot accept parameters. You cannot pass parameters to a cursor variable, but you can pass whole queries to it. The queries can include variables.
To create a cursor variable, either declare a variable of the predefined type SYS_REFCURSOR
or define a REF CURSOR
type and then declare a variable of that type.
The basic syntax of a REF CURSOR
type definition is:
TYPE type_name IS REF CURSOR RETURN return_type
%TYPE
Cursor Variable Declarations. For example:
DECLARE TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; TYPE genericcurtyp IS REF CURSOR; cursor1 empcurtyp; cursor2 genericcurtyp; my_cursor SYS_REFCURSOR; TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE; dept_cv deptcurtyp; -- strong cursor variable BEGIN NULL; END; /
Cursor Variable with User-Defined Return Type. For example:
DECLARE TYPE EmpRecTyp IS RECORD ( employee_id NUMBER, last_name VARCHAR2(25), salary NUMBER(8,2)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; emp_cv EmpCurTyp; BEGIN NULL; END; /
After declaring a cursor variable, you can open it with the OPEN FOR
statement,
which does the following:
Associates the cursor variable with a query (typically, the query returns multiple rows).
The query can include placeholders for bind variables,
whose values you specify in the USING
clause of the OPEN FOR
statement.
Allocates database resources to process the query.
Processes the query; that is:
Identifies the result set. If the query references variables, their values affect the result set.
If the query has a FOR UPDATE
clause, locks the rows of the result set.
Positions the cursor before the first row of the result set.
After opening a cursor variable, you can fetch the rows of the query result set
with the FETCH
statement. The return type of the cursor variable
must be compatible with the into_clause
of the FETCH
statement.
The first iOPEN FOR
statement includes the query itself.
The second OPEN FOR
i statement references a variable whose value is a query.
For example:
DECLARE cv SYS_REFCURSOR; -- cursor variable v_lastname employees.last_name%TYPE; -- variable for last_name v_jobid employees.job_id%TYPE; -- variable for job_id query_2 VARCHAR2(200) := 'SELECT * FROM employees WHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'') ORDER BY job_id'; v_employees employees%ROWTYPE; -- record variable row of table BEGIN OPEN cv FOR SELECT last_name, job_id FROM employees WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK') ORDER BY last_name; LOOP -- Fetches 2 columns into variables FETCH cv INTO v_lastname, v_jobid; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE (RPAD(v_lastname, 25, ' ') || v_jobid); END LOOP; DBMS_OUTPUT.PUT_LINE ('-------------------------------------'); CLOSE CV; OPEN cv FOR query_2; LOOP -- Fetches entire row into the v_employees record FETCH cv INTO v_employees; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE (RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id); END LOOP; CLOSE cv; END; /
You can assign to a PL/oraSQL cursor variable the value of another PL/oraSQL cursor variable or host cursor variable.
target_cursor_variable := source_cursor_variable;
If source_cursor_variable
is open, then after the assignment,
target_cursor_variable
is also open.
The two cursor variables point to the same SQL work area.
If source_cursor_variable
is not open,
opening target_cursor_variable
after the assignment
does not open source_cursor_variable
.
Fetching from Cursor Variable into Collections. For example:
DECLARE TYPE empcurtyp IS REF CURSOR; emp_cv empcurtyp; emp_cf empcurtyp; item employees%rowtype; BEGIN OPEN emp_cv FOR SELECT * FROM employees WHERE job_id = 'SA_REP' ORDER BY salary DESC; OPEN emp_cf FOR SELECT * FROM employees; emp_cf := emp_cv; LOOP FETCH emp_cf INTO item; DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', salary = ' || item.salary); EXIT WHEN emp_cf%notfound; END LOOP; CLOSE emp_cf; END; /
The query associated with a cursor variable can reference any variable in its scope.
When you open a cursor variable with the OPEN FOR statement, PL/oraSQL evaluates any variables in the query and uses those values when identifying the result set. Changing the values of the variables later does not change the result set.
Fetching from Cursor Variable into Collections. For example:
DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; cv SYS_REFCURSOR; BEGIN OPEN cv FOR SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('factor = ' || factor); DBMS_OUTPUT.PUT_LINE ('sal = ' || sal); DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple); factor := factor + 1; -- Does not affect sal_multiple END LOOP; CLOSE cv; END; /
Fetching from Cursor Variable into Collections. For example:
DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; cv SYS_REFCURSOR; BEGIN DBMS_OUTPUT.PUT_LINE ('factor = ' || factor); OPEN cv FOR SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('sal = ' || sal); DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple); END LOOP; factor := factor + 1; DBMS_OUTPUT.PUT_LINE ('factor = ' || factor); CLOSE cv; OPEN cv FOR SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('sal = ' || sal); DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple); END LOOP; CLOSE cv; END; /
A cursor variable has the same attributes as an explicit cursor (see Explicit Cursor Attributes.). The syntax for the value of a cursor variable attribute is cursor_variable_name immediately followed by attribute (for example, cv%ISOPEN).If a cursor variable is not open, referencing any attribute except %ISOPEN raises the predefined exception INVALID_CURSOR.