GBase数据库 | Understanding Cursor Variables in GBase 8s: Enhancing Database Operation Efficiency

Among GBase database series (GBase数据库), GBase 8s is for OLTP scenarios and is compatible with Oracle's PL/SQL programming language. To use PL/SQL syntax, it’s essential to set the SQLMODE environment variable to ORACLE, as the default SQLMODE in GBase 8s is set to GBASE, which does not support PL/SQL. This article introduces cursor variables in the Oracle-compatible mode of GBase 8s, covering their usage, characteristics, and how they can be utilized to enhance the efficiency of database operations.

Cursor variables and cursors are two different concepts. Like cursors, cursor variables point to the current row in a multi-row result set. However, while cursors are static, cursor variables are dynamic. Unlike traditional cursors, cursor variables are not bound to specific queries, allowing them to be opened for any compatible query, providing greater flexibility.

Characteristics of Cursor Variables

A cursor variable shares similarities with an explicit cursor but has distinct characteristics:

• Not limited to a single query: Cursor variables can be associated with different queries.
• Can be assigned: Cursor variables can be assigned values.
• Expression-compatible: Cursor variables can be used in expressions.
• Can be passed as parameters: Cursor variables, specifically SYS_REFCURSOR, can be passed between subprograms to transfer result sets.
• Cannot receive parameters: Queries associated with cursor variables can include variables instead of parameters.

Creating a Cursor Variable

To create a cursor variable:

1. Define a cursor type, then declare a variable of that type.
2. Declare a SYS_REFCURSOR variable. A cursor variable is also referred to as a REF CURSOR.

REF CURSOR Syntax

TYPE type_name IS REF CURSOR [ RETURN return_type ]

If a return_type is specified, the defined REF CURSOR and the declared variable are strong types; otherwise, they are weak types. SYS_REFCURSOR is a weak type. Strongly typed cursor variables can be associated with queries that return the specified type, while weakly typed cursor variables can be associated with any query.

The OPEN FOR Statement

When other OPEN FOR statements are used with cursor variables, the cursor variable can be reopened without closing it. After reopening, the previous query associated with the cursor variable will be lost. There is no need to use the CLOSE statement, as cursor variables close automatically when they are no longer needed. Once closed, data cannot be retrieved or referenced, though they can be reopened.

Fetching Data from Cursor Variables

Once a cursor variable is open, you can use the FETCH statement to retrieve rows from the query result set.

Syntax

FETCH cursor_variable INTO into_clause
FETCH cursor_variable BULK COLLECT INTO bulk_collect_into_clause [ LIMIT numeric_expression ]

• into_clause: A list of variables or a single record variable compatible with each column of the returned query.
• bulk_collect_into_clause: Specifies one or more collections for storing multiple rows fetched by the FETCH statement. The LIMIT clause restricts the number of rows retrieved in a single operation.

Using Cursor Variables

Base Table

CREATE TABLE t (id INT, name VARCHAR(10));
INSERT INTO t VALUES(1,'Jerry');
INSERT INTO t VALUES(2,'Jack');

Cursor Variable Declaration

DECLARE
   TYPE tcurtyp IS REF CURSOR RETURN t%ROWTYPE;  -- Strong cursor type
   TYPE genericcurtyp IS REF CURSOR;             -- Weak cursor type
   cursor1   tcurtyp;       -- Strong cursor variable
   cursor2   genericcurtyp; -- Weak cursor variable
   my_cursor SYS_REFCURSOR; -- Weak cursor variable
BEGIN
   NULL;
END;
/

Combining with Dynamic SQL

declare  
   cv SYS_REFCURSOR;
   v_name varchar(10);
   query_1 varchar(200) := 'select name from t';
begin
   open cv for query_1;
   fetch cv into v_name;
    dbms_output.put_line('name = '||v_name);
end;
/

Fetching Cursor into Record Types

declare
   TYPE ty_1 IS REF CURSOR;
   v1 t.id%type;
   v2 t.name%type;
   cursor1 ty_1;
begin
open cursor1 for select * from t;
loop
   fetch cursor1 into v1,v2;
exit when cursor1%notfound;
dbms_output.put_line('id = '||v1||', name = '||v2);
end loop;
close cursor1;    
end;
/   

Using Cursor Variables as Procedure Parameters

Note: Only SYS_REFCURSOR supports being used as input/output parameters. REF CURSOR does not.

CREATE OR REPLACE PACKAGE t_data AS
   PROCEDURE open_t_cv (t_cv IN OUT SYS_REFCURSOR);
END t_data;
/

CREATE OR REPLACE PACKAGE BODY t_data AS
   PROCEDURE open_t_cv (t_cv IN OUT SYS_REFCURSOR) IS
   BEGIN
       OPEN t_cv FOR SELECT * FROM t;
   END open_t_cv;
END t_data;
/

Cursor variables are a valuable feature in GBase 8s, enhancing flexibility and efficiency in database operations and offering developers more programming options. We hope this guide provides a comprehensive understanding of cursor variables and how to effectively use them to improve your database programming skills. If you have further questions about cursor variables or GBase database (GBase数据库), feel free to explore more and reach out for additional support and resources.