sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' || v_column || ' = :2';
EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE( v_column || '=' || column_value );
END IF;
プロシージャの動的実行
下のサンプルは、create_dept の1番目の引数が IN OUT で定義されているので、new_deptid に値が戻ってきます
plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
いろいろな SQL
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num'
USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
EXECUTE IMMEDIATE 'DROP TABLE bonus';
カーソルのFETCH
sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';
OPEN emp_cv FOR sql_stmt USING v_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
END LOOP;
CLOSE emp_cv;
RETURNING BULK COLLECT INTO句を使用した動的SQL
sql_stmt := 'UPDATE employees SET salary = salary + :1
RETURNING last_name INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING bonus_amt RETURNING BULK COLLECT INTO enames;