Explicit cursors are mainly used to process query statements, especially when the query result contains multiple records.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only 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 only 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.
Retrieve data from the cursor.
Close the cursor.
Declare the cursor
Declare the cursor name and the corresponding SELECT statement.
The syntax is as follows:
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 you specify a data type, you cannot use length constraints. For example, NUMBER(4) and CHAR(10) are invalid.
The syntax is as follows:
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]...)];
When passing parameters to the cursor, you can use the same value-passing methods as those for function parameters, namely positional notation and named notation. PL programs cannot use the OPEN statement to reopen a cursor.
Retrieve data from the cursor
Use the FETCH statement to retrieve data rows from the result set and store them in the specified output variable.
The syntax is as follows:
FETCH cursor_name INTO {variable_list | record_variable };
/* You can use a 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 in a LOOP statement to process each record in the loop until all records in the active set are processed. The cursor attribute %NOTFOUND is used to detect the exit condition.
Close the cursor
The CLOSE statement is used to close the cursor.
The syntax is as follows:
CLOSE cursor_name;
After retrieving and processing the data from the cursor result set, you should close the cursor promptly to release the system resources it occupies and make its workspace invalid. After the cursor is closed, you cannot use the FETCH statement to retrieve data from it. You can use the OPEN statement to reopen a closed cursor.
Use an explicit cursor to loop through each record in a result set
The following steps show how to use an explicit cursor to loop through each record in a result set.
- Declare a cursor in the declaration section. You can use the following two methods.
CURSOR cursor_name IS query; --Method 1
CURSOR cursor_name(para_name data_type) IS query; --Method 2
- Declare a record variable in the declaration section to store the rows returned by the cursor.
record_name cursor_name%ROWTYPE;
- Open the cursor in the execution section. You can use the following two methods.
OPEN cursor_name; --Method 1
OPEN cursor_name(para_name); --Method 2
- Retrieve row data from the cursor in the execution section. Each time, you can use the
LOOPstatement in the following format to retrieve multiple rows of data.
LOOP
FETCHcursor_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.