Explicit cursors are primarily used for processing query statements, especially when the query result contains multiple records.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition only provides MySQL mode.
Syntax for calling an explicit cursor
After declaring an explicit cursor, you can define it in the same subprogram or package, or declare and define it at the same time.
The syntax for declaring an explicit cursor is as follows:
CURSOR cursor_name [ parameter_list ] RETURN return_type;
The syntax for defining an explicit cursor is as follows:
CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
IS select_statement;
Steps for processing an explicit cursor
Processing an explicit cursor involves the following four steps:
Declare the cursor.
Open the cursor.
Fetch data from the cursor.
Close the cursor.
Declare the cursor
Declare the cursor name and the corresponding SELECT statement.
Syntax:
CURSOR cursor_name[(parameter[, parameter]...)] IS select_statement;
Cursor parameters can only be input parameters. The syntax is as follows:
parameter_name [IN] datatype [{:= | DEFAULT} expression]
Notice
When specifying data types, do not use length constraints. For example, NUMBER(4) and CHAR(10) are invalid.
Open the cursor
The OPEN statement is used to open the cursor, execute the corresponding SELECT statement, store the query results in the workspace, and position the pointer at the beginning of the workspace to indicate the result set of the cursor. If the cursor query statement includes the FOR UPDATE option, the OPEN statement will also lock the data rows corresponding to the cursor result set in the database table.
Syntax:
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]...)];
When passing parameters to the cursor, you can use the same value passing methods as function parameters, namely positional notation and named notation. PL programs cannot use the OPEN statement to reopen a cursor.
Fetch data from the cursor
Use the FETCH statement to retrieve data rows from the result set and store them in the specified output variable.
Syntax:
FETCH cursor_name INTO {variable_list | record_variable };
/* You can use the cursor to fetch data in batches. */
FETCH { cursor | cursor_variable | :host_cursor_variable }
{ into_clause | BULK COLLECT INTO { collection | :host_array }
[, { collection | :host_array } ]... [ LIMIT numeric_expression ] } ;
Typically, the FETCH statement is used within a LOOP statement to process each record in the active set until all records in the active set have been processed. The cursor attribute %NOTFOUND is used to detect the exit condition.
Close the cursor
The CLOSE statement is used to close the cursor.
Syntax:
CLOSE cursor_name;
After fetching and processing the data from the cursor result set, you should promptly close the cursor to release the system resources it occupies and make its workspace invalid. Once the cursor is closed, you cannot use the FETCH statement to retrieve data from it. A closed cursor can be reopened using the OPEN statement.
Use an explicit cursor to loop through each record in a result set
The following steps demonstrate how to use an explicit cursor to loop through each record in a result set.
- Declare a cursor in the declaration section using one of the following methods.
CURSOR cursor_name IS query; -- Method 1
CURSOR cursor_name(para_name data_type) IS query; -- Method 2
Declare a record in the declaration section to store the rows returned by the cursor.
record_name cursor_name%ROWTYPE;Open the cursor in the execution section using one of the following methods.
OPEN cursor_name; -- Method 1 OPEN cursor_name(para_name); -- Method 2Fetch row data from the cursor in the execution section. Each row is fetched one at a time, and you can use the following format of the
LOOPstatement to fetch multiple rows at once.LOOP FETCH cursor_name INTO record_name; EXIT WHEN cursor_name%NOTFOUND; statement; [ statement; ]... END LOOP;Close the cursor.
CLOSE cursor_name;
Example: Use an explicit cursor to loop through each record in a result set.
obclient> CREATE TABLE ware (
w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);
Query OK, 0 rows affected
obclient> INSERT INTO ware VALUES(1, 1200, .1868, 'W_NAME_1', 'jTNkXKWXOdh',
'lf9QXTXXGoF04IZBkCP7', 'srRq15uvxe5', 'GQ', 506811111);
Query OK, 1 row affected
obclient> INSERT INTO ware VALUES(2, 1200, .0862, 'W_NAME_2', 'xEdT1jkENtbLwoI1Zb0',
'NT0j4RCQ4OqrS', 'vlwzndw2FPrO', 'XR', 063311111);
Query OK, 1 row affected
obclient> CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
END;/
Query OK, 0 rows affected
obclient> CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt
AS
PROCEDURE sp_record_print_by_record(p_record IN ware%ROWTYPE)
AS
BEGIN
dbms_output.put_line('Print a record :');
dbms_output.put_line('W_ID : ' || p_record.w_id
|| ', W_YTD : ' || p_record.w_ytd
|| ', W_TAX : ' || p_record.w_tax
|| ', W_NAME : ' || p_record.w_name
|| ', W_STREET_1 : ' || p_record.w_street_1
|| ', W_STREET_2 : ' || p_record.w_street_2
|| ', W_CITY : ' || p_record.w_city
|| ', W_STATE : ' || p_record.w_state
|| ', W_ZIP : ' || p_record.w_zip )
;
dbms_output.put_line('');
END;
PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
IS
CURSOR c1(cp_w_id ware.w_id%TYPE ) IS SELECT * FROM ware WHERE w_id = cp_w_id;
r_ware ware%ROWTYPE;
BEGIN
OPEN c1(p_w_id);
dbms_output.put_line('Open a cursor with a parameter [ ' || p_w_id || ' ].');
LOOP
FETCH c1 INTO r_ware ;
dbms_output.put_line('Fetch the cursor one time.');
EXIT WHEN c1%NOTFOUND ;
sp_record_print_by_record(r_ware);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Raise an unkown exception !');
END ;
END;
/
Query OK, 0 rows affected
obclient>SET SERVEROUTPUT ON;
Query OK, 0 rows affected
obclient>CALL pkg_ware_mgmt.sp_record_print(2);
Query OK, 0 rows affected
Open a cursor with a parameter [ 2 ].
Fetch the cursor one time.
Print a record :
W_ID : 2, W_YTD : 1200, W_TAX : .0862, W_NAME : W_NAME_2, W_STREET_1 : xEdT1jkENtbLwoI1Zb0, W_STREET_2 : NT0j4RCQ4OqrS, W_CITY : vlwzndw2FPrO, W_STATE : XR, W_ZIP : 63311111
Fetch the cursor one time.