動的SQLは、EXECUTE IMMEDIATE ステートメントを使用して処理します。
機能の適用範囲
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionはMySQLモードのみを提供します。
動的SQLステートメントが複数行を返す SELECT ステートメントの場合、PLは以下の2つの方法で動的SQLを実行することを提供します：
EXECUTE IMMEDIATEステートメントとBULK COLLECT INTO句を併用するカーソルの
OPEN FOR、FETCH、またはCLOSE句を使用する
EXECUTE IMMEDIATEステートメント
SQLステートメントが完全な場合は、EXECUTE IMMEDIATE を使用して直接実行します。パラメータは以下の方法で渡したり受け取ったりし、プレースホルダーを使用する必要があります：
ダイナミックSQLが
SELECTステートメントで、最大1行のレコードを返す場合は、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 句を使用して3つのパラメータを渡しています。これには、2つの入力パラメータと1つの出力パラメータが含まれます。
また、カーソル変数を使用してダイナミック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ステートメントを使用して、1行ずつ、複数の結果セットを一度に、またはすべての結果セットを一度に取得します。説明
OceanBaseデータベースは、トランザクションコミット後に、
CURSORが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';
-- 結果セットから1行ずつ取得します：
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句内の1つのバインド変数に対応します。
動的SQLステートメントがPL匿名ブロックまたはCALLステートメントでない場合、プレースホルダーはUSING句内のバインド変数と名前ではなく位置で関連付けられます。