Implicit cursors

OceanBase Database automatically sets cursors for DML operations such as ALTER and DELETE, and creates workspaces for the cursors. These cursors implicitly created by the system are referred to as implicit cursors and named after SQL cursors, which is defined by OceanBase Database.

Operations on an implicit cursor, such as definition, opening, value assignment, and closing, are all automatically completed by OceanBase Database without the need for manual intervention. You can complete corresponding operations based on related attributes of an implicit cursor. The data stored in the workspace of an implicit cursor is irrelevant to a user-defined explicit cursor, but is the data contained by the last SQL statement that is executed.

An implicit cursor has the following attributes:

• SQL%FOUND: a BOOLEAN attribute, which returns TRUE if the last read operation returns a record.

• SQL%NOTFOUND: a BOOLEAN attribute, which is opposite to SQL%FOUND.

• SQL%ROWCOUNT: a numeric attribute, which returns the number of records read from the cursor.

• SQL%ISOPEN: a BOOLEAN attribute, which is always FALSE. The implicit cursor is immediately closed after the SQL statement is executed.

The following example deletes all employees of a department from the emp table, and deletes this department from the dept table if this department has no employees:

obclient> CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(15),  
    location           VARCHAR(20),   
   CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);
Query OK, 0 rows affected

obclient> CREATE TABLE emp(  
   empno         NUMBER(4,0),  
   empname    VARCHAR(10),  
    job              VARCHAR(10),  
   mgr              NUMBER(4,0),  
   hiredate       DATE,  
   sal                NUMBER(7,2),  
   comm          NUMBER(7,2),        
   deptno         NUMBER(2,0),   
   CONSTRAINT PK_emp PRIMARY KEY (empno)
);
Query OK, 0 rows affected

obclient> INSERT INTO dept VALUES (10,'ACCOUNTING','Los Angeles');
Query OK, 1 row affected

obclient> INSERT INTO emp VALUES (1839,'KING','PRESIDENT',null,  '1981-11-17',5000,null,10);
Query OK, 1 row affected

obclient> DECLARE
         v_dept_id emp.deptno%TYPE := 10;
     BEGIN
         DELETE FROM emp WHERE deptno=v_dept_id;
         IF SQL%NOTFOUND THEN
             DELETE FROM dept WHERE deptno=v_dept_id;
         END IF;
END;
/
Query OK, 0 rows affected