执行动态 SQL - OceanBase 数据库 V4.2.5

执行动态 SQL

更新时间：2026-07-15 20:56:58

编辑

动态 SQL 使用 EXECUTE IMMEDIATE 语句处理大多数动态 SQL 语句。

功能适用性

该内容仅适用于 OceanBase 数据库企业版。OceanBase 数据库社区版仅提供 MySQL 模式。

如果动态 SQL 语句是返回多行的 SELECT 语句，PL 提供如下两种方法执行动态 SQL：

将 EXECUTE IMMEDIATE 语句与 BULK COLLECT INTO 子句一起使用
使用游标 OPEN FOR、FETCH 或者 CLOSE 子句

EXECUTE IMMEDIATE 语句

如果 SQL 语句是完整的，那么直接用 EXECUTE IMMEDIATE 执行即可。按照如下方式传入或者传出参数，并且需要使用占位符：

如果动态 SQL 为 SELECT 语句，而且最多返回一行记录，那么可以通过 INTO 子句指定输出参数，通过 USING 子句指定输入参数。
如果动态 SQL 为 SELECT 语句，可能返回多条记录，那么通过 BULK COLLECT INTO 指定输出参数，通过 USING 子句指定输入参数。
如果动态 SQL 为没有 RETURNING INTO 的 DML 子句，所有参数都通过 USING 子句传入。
如果动态 SQL 是带有 RETURNING INTO 的 DML 子句，那么通过 USING 子句指定输入参数，RETURNING INTO 指定输出参数。

如下示例为利用动态 SQL，把 ID 号为 111 的员工，名字改成 Roger，并同步员工的电话：

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

这里利用 USING 子句传入了三个参数，包括两个输入的参数和一个输出参数。

另外可以使用游标变量打开动态 SQL，同样可以使用占位符，打开时使用 USING 子句指定变量。示例如下：

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\CLOSE 子句

如果动态 SQL 语句表示返回多行的 SELECT 语句，则可以使用本地动态 SQL 对其进行如下处理：

使用 OPEN FOR 语句将游标变量与动态 SQL 语句关联。在 OPEN FOR 语句的 USING 子句中，为动态 SQL 语句中的每个占位符指定一个绑定变量。
使用 FETCH 语句一次检索一个行结果，一次检索多个结果集，或一次检索全部结果集。
使用 CLOSE 语句关闭游标变量。

示例：在所有员工信息中检索经理级别的员工，并一次性检索了行结果集。

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
  -- 带占位符的动态 SQL 语句：
      v_st_str := 'SELECT * FROM emp WHERE job_id = :j';

  -- 打开游标并在 USING 子句中指定绑定变量：
      OPEN v_emp_cur FOR v_st_str USING 'AD_ASST';

  -- 一次从结果集中获取行：
      LOOP
        FETCH v_emp_cur INTO emp_rec;
        EXIT WHEN v_emp_cur%NOTFOUND;
      END LOOP;

  -- 关闭游标：
      CLOSE v_emp_cur;
    END;
  /
Query OK, 0 rows affected

重复占位符名称

如果在动态 SQL 语句中重复占位符名称，则占位符与绑定变量关联的方式取决于动态 SQL 语句的类型。

如果动态 SQL 语句表示 PL 匿名块或 CALL 语句。每个占位符名称在 USING 子句中必须有一个对应的绑定变量。如果重复占位符名称，则无需重复其对应的绑定变量。对该占位符名称的所有引用对应于 USING 子句中的一个绑定变量。

如果动态 SQL 语句不是 PL 匿名块或 CALL 语句，则占位符与 USING 子句中的绑定变量按位置而不是按名称关联。