Execute dynamic SQL statements

Most dynamic SQL statements can be handled by using the EXECUTE IMMEDIATE statement.

If a dynamic SQL statement is a SELECT statement that returns multiple rows, PL/SQL provides two methods for executing dynamic SQL:

• Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.

• Use the OPEN FOR, FETCH, or CLOSE clauses with a cursor.

EXECUTE IMMEDIATE statement

If the SQL statement is complete, you can directly execute it by using the EXECUTE IMMEDIATE statement. You can pass in or out parameters by using placeholders, as shown in the following examples:

• If the dynamic SQL statement is a SELECT statement that returns at most one row, you can specify output parameters by using the INTO clause and input parameters by using the USING clause.

• If the dynamic SQL statement is a SELECT statement that returns multiple rows, you can specify output parameters by using the BULK COLLECT INTO clause and input parameters by using the USING clause.

• If the dynamic SQL statement is a DML clause that does not contain RETURNING INTO, you can specify all parameters by using the USING clause.

• If the dynamic SQL statement is a DML clause that contains RETURNING INTO, you can specify input parameters by using the USING clause and output parameters by using RETURNING INTO.

The following example uses dynamic SQL to change the name of the employee whose ID is 111 to Roger and update the employee's phone number:

obclient> CREATE TABLE emp(  
   empno          NUMBER(4,0),  
   empname        VARCHAR(10),
   job_id         VARCHAR2(200),
   job            VARCHAR(10),    
   deptno         NUMBER(2,0),
   phone_num      NUMBER(20,0)

);
Query OK, 0 rows affected

obclient> INSERT INTO emp VALUES (111,'Ismael','01','AD_ASST',1,'5151244369');
Query OK, 1 row affected

obclient> SELECT empno,empname,phone_num FROM emp
     where empno=111;
+-------+---------+------------+
| EMPNO | EMPNAME | PHONE_NUM  |
+-------+---------+------------+
|   111 | Ismael  | 5151244369 |
+-------+---------+------------+
1 row in set

obclient> DECLARE
        v_id NUMBER := 111;
        v_name  VARCHAR2(20) := 'Roger';
        v_phone VARCHAR2(50);
      BEGIN
        EXECUTE IMMEDIATE 'UPDATE emp SET empname= :NAME WHERE empno = :ID
          RETURNING phone_num INTO :PHONE'
          USING v_name, v_id, OUT v_phone;
        DBMS_OUTPUT.PUT_LINE(v_phone);
      END;
      /
Query OK, 0 rows affected

obclient> SELECT empno,empname,phone_num FROM emp
     where empno=111;
+-------+---------+------------+
| EMPNO | EMPNAME | PHONE_NUM  |
+-------+---------+------------+
|   111 | Roger   | 5151244369 |
+-------+---------+------------+
1 row in set

In this example, three parameters are passed in by using the USING clause, including two input parameters and one output parameter.

You can also use a cursor variable to execute dynamic SQL. You can use placeholders, and specify variables by using the USING clause when you open the cursor. Here is an example:

obclient> SET SERVEROUTPUT ON;
Query OK, 0 rows affected

obclient> DECLARE
       cv SYS_REFCURSOR;
       query_2 VARCHAR2(200) :=
         'SELECT * FROM emp
          where empno = :x';
       v_employees emp%ROWTYPE;
     BEGIN
       OPEN cv FOR query_2  USING 111;
       LOOP
           FETCH cv INTO v_employees;
           EXIT WHEN cv%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE(v_employees.empno||'-'||v_employees.empname);
       END LOOP;
       CLOSE cv;
     END;
     /
Query OK, 0 rows affected

111-Ismael

OPEN FOR, FETCH, and CLOSE clauses

If a dynamic SQL statement is a SELECT statement that returns multiple rows, you can use local dynamic SQL to handle it as follows:

• Use the OPEN FOR statement to associate a cursor variable with the dynamic SQL statement. In the USING clause of the OPEN FOR statement, specify a binding variable for each placeholder in the dynamic SQL statement.

• Use the FETCH statement to retrieve one row result, multiple result sets, or all result sets at a time.

  Note

  After a transaction is committed, OceanBase Database allows the CURSOR to continue FETCH.

• Use the CLOSE statement to close the cursor variable.

Here is an example that retrieves all manager-level employees from the employee information and retrieves the row result set at a time.

obclient> DECLARE
      TYPE EmpCurType  IS REF CURSOR;
      v_emp_cur        EmpCurType;
      emp_rec          emp%ROWTYPE;
      v_st_str         VARCHAR2(200);
      v_e_job          emp.job%TYPE;
    BEGIN
  -- Dynamic SQL statement with a placeholder:
      v_st_str := 'SELECT * FROM emp WHERE job_id = :j';

  -- Open the cursor and specify the binding variable in the USING clause:
      OPEN v_emp_cur FOR v_st_str USING 'AD_ASST';

  -- Retrieve a row from the result set:
      LOOP
        FETCH v_emp_cur INTO emp_rec;
        EXIT WHEN v_emp_cur%NOTFOUND;
      END LOOP;

  -- Close the cursor:
      CLOSE v_emp_cur;
    END;
  /
Query OK, 0 rows affected

Repeated placeholder names

If a placeholder name is repeated in a dynamic SQL statement, the way the placeholder is associated with a binding variable depends on the type of the dynamic SQL statement.

If the dynamic SQL statement is a PL/SQL anonymous block or a CALL statement, each placeholder name in the USING clause must have a corresponding binding variable. If a placeholder name is repeated, its corresponding binding variable does not need to be repeated. All references to the placeholder name correspond to the same binding variable in the USING clause.

If the dynamic SQL statement is not a PL/SQL anonymous block or a CALL statement, the placeholder is associated with a binding variable in the USING clause by position, not by name.