動的SQLは、EXECUTE IMMEDIATE ステートメントを使用して、ほとんどの動的SQLステートメントを処理します。
適用対象
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionはMySQLモードのみを提供します。
動的SQLステートメントが複数行を返す SELECT ステートメントの場合、PLは動的SQLを実行するために以下の2つの方法を提供します：
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回の処理で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句内の各プレースホルダー名に対して、必ず1つのバインド変数が対応していなければなりません。プレースホルダー名が重複している場合でも、対応するバインド変数を重複させる必要はありません。そのプレースホルダー名へのすべての参照は、USING句内の1つのバインド変数に対応します。
動的SQL文がPL匿名ブロックまたはCALL文でない場合、プレースホルダーはUSING句内のバインド変数と、名前ではなく位置に基づいて関連付けられます。