Implicit cursors

OceanBase Database automatically sets up a cursor and creates a workspace for DML statements such as modification and deletion. These implicitly created cursors are called implicit cursors. The name of an implicit cursor is SQL, which is defined by OceanBase Database.

OceanBase Database automatically performs operations such as defining, opening, fetching, and closing an implicit cursor. Users cannot perform these operations. Users can only use the attributes of an implicit cursor to perform the corresponding operations. The data stored in the workspace of an implicit cursor is unrelated to user-defined explicit cursors. Instead, it contains the data of the most recently executed SQL statement.

The attributes of an implicit cursor are as follows:

• SQL%FOUND is a Boolean attribute. It is TRUE if the last read operation returned a record.

• SQL%NOTFOUND is a Boolean attribute. It is the opposite of SQL%FOUND.

• SQL%ROWCOUNT is a numeric attribute. It returns the number of records fetched from the cursor.

• SQL%ISOPEN is a Boolean attribute. It is always FALSE. An implicit cursor is closed immediately after the execution of an SQL statement.

The following example deletes all employees in a specified department from the emp table. If the department has no employees, it is deleted from the dept table.

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