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/sql 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/sql 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/sql. pl/sql 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.
The implicit cursor gives the result set of the SELECT query to a RECORD variable,
which supports two new keywords INNER and OUTER in addition to identifiers.
For example, the RECORD variable of the implicit cursor to get the result set is named INNER:
create table record_filed(id int, zqdm varchar(100));
insert into record_filed values(12,'hello world!');
DECLARE
CURSOR c_a IS
select * from record_filed;
zqdm_p record_filed.id%TYPE;
BEGIN
FOR inner IN c_a LOOP
zqdm_p := inner.id;
end loop;
end;
/
For example, the RECORD variable of the implicit cursor to get the result set is named OUTER:
create table record_filed(id int, zqdm varchar(100));
insert into record_filed values(12,'hello world!');
DECLARE
CURSOR c_a IS
select * from record_filed;
zqdm_p record_filed.id%TYPE;
BEGIN
FOR outer IN c_a LOOP
zqdm_p := outer.id;
end loop;
end;
/
It is worth noting:
The OUTER is a keyword of UNRESERVED_KEYWORD,
which can be used as a table name, column name, function name, type name, etc;
INNER is the keyword of TYPE_FUNC_NAME_KEYWORD,
which can only be used as the function name;
If the type name(Specifically, associative arrays, varrays, and nested tables) is the same as the name of the FOR loop variable,
an error is reported;
Outside of pl/sql,
After you have created an object with the same name as the OUTER,
you are not allowed to create an object of the same type as the OUTER.
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_listRETURN return_typeIS 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. Cursor names ignore case.
The cursor is defined without parameters,
and can also be opened in the form of cursor name plus ().
Both of the following uses open cursor c_tmp, for example:
open C_tmp; open c_tmp; open c_tmp();
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/sql 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/sql 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/sql 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/sql 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,the value of lightdb_dblevel_syntax_compatible_type must be 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/sql, as in traditional database programming, you use cursors to process query result sets. However, in pl/sql, 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/sql statements use implicit cursors that pl/sql defines and manages for you:
SELECT INTO,
Implicit cursor FOR LOOP.
The following pl/sql 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;
/
To be compatible with Oracle databases, LightDB supports the use of the WITH clause in implicit cursor FOR LOOP statements.
For example:
CREATE TABLE employees (employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50));
INSERT INTO employees VALUES (1, 'John');
INSERT INTO employees VALUES (2, 'Jane');
INSERT INTO employees VALUES (3, 'Bob');
BEGIN
FOR emp_rec IN (WITH employeetemp AS
( SELECT employee_id AS eid, first_name AS fname FROM employees
) SELECT eid,fname FROM employeetemp
) loop
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.eid || ', Name: ' || emp_rec.fname);
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/sql 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;
/
A variable of cursor type created using CREATE DOMAIN. For example:
CREATE DOMAIN mycursor AS refcursor;
SELECT dbms_output.serveroutput(true);
DECLARE
employee_cursor mycursor;
v_employee_name VARCHAR2(50);
BEGIN
OPEN employee_cursor for SELECT first_name FROM employees_curs WHERE employee_id = 1500;
LOOP
FETCH employee_cursor INTO v_employee_name;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END LOOP;
CLOSE employee_cursor;
END;
/
Use sys_refcursor as a parameter variable. For example:
CREATE TABLE customer_address
(
ca_address_sk integer not null,
ca_address_id char(16) not null,
ca_street_number char(10) ,
ca_street_name varchar(60) ,
ca_street_type char(15) ,
ca_suite_number char(10) ,
ca_city varchar(60) ,
ca_county varchar(30) ,
ca_state char(2) ,
ca_zip char(10) ,
ca_country varchar(20) ,
ca_gmt_offset decimal(5,2) ,
ca_location_type char(20) ,
PRIMARY KEY (ca_address_sk)
);
DECLARE
i_id integer := 1;
address_id char(16);
street_number char(10);
street_name varchar(60) ;
street_type char(15);
suite_number char(10);
city varchar(60);
county varchar(30);
state char(2);
zip char(10);
country varchar(20);
gmt_offset decimal(5,2);
location_type char(20);
BEGIN
-- insert
address_id := 'address_id';
street_number := '3588' ;
street_name := 'street_name';
street_type := 'street_type';
suite_number := '1';
city := 'Hang Zhou';
county := 'Bin Jiang';
state := 'CN';
zip := '000000';
country := 'China';
gmt_offset := 1.02;
location_type := 'location_type';
INSERT INTO customer_address(ca_address_sk, ca_address_id, ca_street_number, ca_street_name, ca_street_type, ca_suite_number, ca_city, ca_county, ca_state, ca_zip, ca_country, ca_gmt_offset, ca_location_type)
VALUES (i_id, address_id, street_number, street_name, street_type, suite_number, city, county, state, zip, country, gmt_offset, location_type);
END;
/
CREATE OR REPLACE PROCEDURE getCustomerAddress(cur OUT sys_refcursor) IS
BEGIN
OPEN cur FOR SELECT * FROM customer_address;
END;
/
DECLARE
ad sys_refcursor;
res customer_address%rowtype;
BEGIN
getCustomerAddress(ad);
LOOP
FETCH ad INTO res;
EXIT WHEN ad%NOTFOUND;
dbms_output.put_line('ca_address_sk:' || res.ca_address_sk);
dbms_output.put_line('ca_address_id:' || res.ca_address_id);
dbms_output.put_line('ca_street_number:' || res.ca_street_number);
dbms_output.put_line('ca_street_name:' || res.ca_street_name);
dbms_output.put_line('ca_street_type:' || res.ca_street_type);
dbms_output.put_line('ca_suite_number:' || res.ca_suite_number);
dbms_output.put_line('ca_city:' || res.ca_city);
dbms_output.put_line('ca_county:' || res.ca_county);
dbms_output.put_line('ca_state:' || res.ca_state);
dbms_output.put_line('ca_zip:' || res.ca_zip);
dbms_output.put_line('ca_country:' || res.ca_country);
dbms_output.put_line('ca_gmt_offset:' || res.ca_gmt_offset);
dbms_output.put_line('ca_location_type:' || res.ca_location_type);
END LOOP;
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.
In the for loop, the result of the select query is given to a record type,
This operation is also known as an implicit cursor operation.
A field in the record type is used to receive a query statement in the query results
(update, delete, insert will all report errors in this syntax),
This field is used as an object for commands(open for) to dynamically open the cursor.
LightDB does not support the following two points:
The format between FOR and IN can be A or A.B.
For example, the format between FOR and IN can be A:
select dbms_output.serveroutput('t');
create table employees(salary int,
job_id varchar2(100), employee_id int,
last_name varchar(100), first_name varchar(100),
department_id int, hire_date date);
create table V2_text (
object_name VARCHAR2(40),
object_id VARCHAR2(8),
sql_text VARCHAR2(1000)
);
create table orders (id int,org_id int,user_id int
,order_id int,customer_id int, order_date date, price int
,primary key (org_id, id),status varchar(20));
insert into employees values (12308, 'SA_REP', 100, 'huaxiang', 'zhao', 10, TO_DATE('31-DEC-2005', 'DD-MON-YYYY'));
insert into employees values (12301, 'AD_FG', 12301, 'huahai', 'zhao', 10, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12302, 'SA_REP', 12302, 'huaduo', 'zhao', 10, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into orders values (12301, 100, 2346, 456043, 23490, TO_DATE('31-DEC-2005', 'DD-MON-YYYY'), 2000, 'Completed');
-- select
insert into V2_text (object_name, object_id, sql_text)
values ('EmployeeTable', 'OBJ0001', 'SELECT job_id FROM employees WHERE department_id = 10');
DECLARE
type t_dynamic_cursor is ref cursor;
dynamic_cur t_dynamic_cursor;
type t_inter_code_table is table of varchar2(128);
rec_all_code t_inter_code_table;
BEGIN
FOR rec IN (
SELECT object_name,object_id,sql_text FROM V2_text
) LOOP
OPEN dynamic_cur FOR rec.sql_text;
-- Perform cursor operations
fetch dynamic_cur bulk collect into rec_all_code;
for i in 1..rec_all_code.count loop
DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
end loop;
close dynamic_cur;
END LOOP;
END;
/
For example, the format between FOR and IN can be A.B:
declare
type t_dynamic_cursor is ref cursor;
dynamic_cur t_dynamic_cursor;
type t_inter_code_table is table of varchar2(128);
rec_all_code t_inter_code_table;
type emp_rec_type is record
(ename varchar2(25),
sql_text varchar2(10),
sal number(7,2)
);
type emp_record_type is record
(ename varchar2(25),
job varchar2(10),
sal number(7,2),
rec emp_rec_type
);
sys emp_record_type;
BEGIN
FOR sys.rec IN (
SELECT object_name,object_id,sql_text FROM V2_text
) LOOP
NULL;
END LOOP;
END;
/
The format of dynamic statements after FOR does not support multiple complex types to access variables in nests,
and only supports a single complex type.
For example, a single complex type can be used to access a variable:
DECLARE
type t_dynamic_cursor is ref cursor;
dynamic_cur t_dynamic_cursor;
type t_inter_code_table is table of varchar2(128);
rec_all_code t_inter_code_table;
BEGIN
FOR rec IN (
SELECT object_name,object_id,sql_text FROM V2_text
) LOOP
OPEN dynamic_cur FOR rec.sql_text;
-- Perform cursor operations
fetch dynamic_cur bulk collect into rec_all_code;
for i in 1..rec_all_code.count loop
DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
end loop;
close dynamic_cur;
END LOOP;
END;
/
For example, multiple complex types are not supported for nested access to variables:
DECLARE
type t_dynamic_cursor is ref cursor;
dynamic_cur t_dynamic_cursor;
type t_inter_code_table is table of varchar2(128);
rec_all_code t_inter_code_table;
type emp_rec_type is record
(ename varchar2(25),
sql_text varchar2(100),
sal number(7,2)
);
type emp_record_type is record
(ename varchar2(25),
job varchar2(10),
sal number(7,2),
rec emp_rec_type
);
type empl_record_type is record
(ename varchar2(25),
job varchar2(10),
sal number(7,2),
rec_emp emp_record_type
);
sys empl_record_type;
BEGIN
FOR sys.rec_emp.rec IN (
SELECT object_name,object_id,sql_text FROM V2_text
) LOOP
NULL;
END LOOP;
END;
/
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 OPEN FOR statement includes the query itself.
The second OPEN FORi 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/sql cursor variable the value of another pl/sql 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/sql 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.