Cong Li

Posted on Sep 5

Introduction to GBase 8s Static Cursor

#database

GBase 8s is a database system compatible with Oracle's PL/SQL programming language, supporting various data types, variable declaration and assignment, sequence, selection, loop control, collection and record objects, static and dynamic SQL, and error handling. This article focuses on using static cursors in database programming. GBase 8s provides two types of static cursors: implicit cursors and explicit cursors. This article will provide a detailed explanation of static cursors in GBase 8s database, including their use cases, characteristics, and how to effectively use them to improve the efficiency of database operations.

Static cursors are read-only cursors that always display the result set as it was when the cursor was opened, and the query used by a static cursor is determined at compile time.

Implicit Cursors

Implicit cursors do not require explicit definition by the user. Whenever a DML statement (INSERT, UPDATE, DELETE) or a SELECT...INTO statement is executed in PL/SQL, an implicit cursor is automatically declared and managed.

1. Features of Implicit Cursors

Managed by PL/SQL, no need for DECLARE, OPEN, FETCH, or CLOSE operations.
Must use SELECT cur_name INTO [variable or other data types]. This performs the OPEN, FETCH, and CLOSE operations.
Implicit cursors can return only one row. If no matching record is found, a NO_DATA_FOUND exception is thrown. If multiple records are returned, a TOO_MANY_ROWS exception occurs.
Cursor attributes can only be checked using the SQL% family.
For any usage of SQL%ISOPEN, the result is always FALSE because implicit cursors are managed by PL/SQL.
Before an implicit cursor is opened, using SQL%FOUND, SQL%NOTFOUND, or SQL%ROWCOUNT will result in NULL.

2. Supported Attributes for Implicit Cursors

`SQL%ISOPEN`

Meaning: Indicates whether the cursor is open.
Always returns FALSE because implicit cursors are always closed after execution.

`SQL%FOUND`

Meaning: Indicates whether any rows were affected.
Returns NULL: No SELECT or DML statement has been run.
Returns TRUE: One or more rows were affected.
Returns FALSE: No rows were affected.

`SQL%NOTFOUND`

Meaning: Indicates whether no rows were affected.
Returns NULL: No SELECT or DML statement has been run.
Returns TRUE: No rows were affected.
Returns FALSE: One or more rows were affected.

`SQL%ROWCOUNT`

Meaning: Returns the number of rows affected.
Returns NULL: No SELECT or DML statement has been run.
Returns the number of affected rows otherwise.

Explicit Cursors

1. Features of Explicit Cursors

Explicit cursors are defined and managed by the user.
They must be declared and associated with a query statement.
They cannot be assigned values, used in expressions, or passed as parameters to subprograms or host variables.
Cursor variables should be used when flexibility is required.

2. Steps to Use Explicit Cursors

1) Define the Cursor: Declare the cursor and its associated query statement.
2) Open the Cursor: Execute the associated query, load the result set into the cursor workspace, and position the cursor before the first row of the result set.
3) Fetch Data: Move the cursor to the appropriate position within the result set as needed.
4) Close the Cursor: After using the cursor, close it to free up resources.

3. Using Cursors

Cursor Declaration and Definition

DECLARE
   -- Declare cursor c1
   CURSOR c1 RETURN departments%ROWTYPE;  
   -- Declare and define cursor c2
   CURSOR c2 IS SELECT employee_id, job_id, salary FROM employees WHERE salary > 2000;
   -- Define cursor c1
   CURSOR c1 RETURN departments%ROWTYPE IS SELECT * FROM departments WHERE department_id = 110;
   -- Declare cursor c3
   CURSOR c3;      
   -- Define cursor c3
   CURSOR c3 IS SELECT * FROM locations WHERE country_id = 'JP';
BEGIN
   NULL;
END;
/

Fetching Data into Regular Variables

-- Create table
CREATE TABLE t(id INT, name VARCHAR(10));
INSERT INTO t VALUES(1, 'jack');
INSERT INTO t VALUES(2, 'jerry');

DECLARE 
   v1 INT;
   v2 VARCHAR(10);
   CURSOR c1 IS SELECT id, name FROM t;
BEGIN
   OPEN c1;
   LOOP
       FETCH c1 INTO v1, v2;
       EXIT WHEN c1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('id = ' || v1);
       DBMS_OUTPUT.PUT_LINE('name = ' || v2);
   END LOOP;

   CLOSE c1;
END;
/

Fetching Data into Record Variables

-- Create table
CREATE TABLE t(id INT, name VARCHAR(10));
INSERT INTO t VALUES(1, 'jack');
INSERT INTO t VALUES(2, 'jerry');

DECLARE 
   v t%ROWTYPE;
   CURSOR c1 IS SELECT id, name FROM t;
BEGIN
   OPEN c1;
   LOOP
       FETCH c1 INTO v;
       EXIT WHEN c1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('id = ' || v.id);
       DBMS_OUTPUT.PUT_LINE('name = ' || v.name);
   END LOOP;

   CLOSE c1;
END;
/

Using Cursors with Parameters (Open Before Call)

-- Create table
CREATE TABLE t(id INT, name VARCHAR(10));
INSERT INTO t VALUES(1, 'jack');
INSERT INTO t VALUES(2, 'jerry');

DECLARE
CURSOR c (c_id INT) IS SELECT name FROM t WHERE id = c_id;
-- Define procedure
PROCEDURE p1 IS
p_name VARCHAR(10);
BEGIN
   LOOP   
       FETCH c INTO p_name;
       EXIT WHEN c%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('result is: ' || p_name);
   END LOOP;   
END;

BEGIN
   OPEN c(2);   -- Open the cursor
   p1;          -- Call the procedure
   CLOSE c;     -- Close the cursor
END;
/

With this detailed introduction, you now have a good understanding of static cursors in GBase 8s and how to apply them. Properly using cursors can not only improve the efficiency of database queries but also make your code clearer and easier to maintain. I hope this article helps you make better use of GBase 8s' cursor functionality. Thank you for reading!

DEV Community