⚡ Dynamic SQL
EXECUTE IMMEDIATE and dynamic cursors when names are not known at compile time.
| Snippet | |
|---|---|
| ☆EXECUTE IMMEDIATE (DDL) — Run DDL at runtime |
BEGIN EXECUTE IMMEDIATE 'CREATE TABLE tmp_t (id NUMBER)'; END;
|
| ☆EXECUTE IMMEDIATE + INTO — Dynamic query returning a value with binds |
EXECUTE IMMEDIATE 'SELECT name FROM employees WHERE id = :1' INTO v_name USING 100;
|
| ☆RETURNING INTO — Dynamic DML returning the new value |
EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.1 WHERE id = :1 RETURNING salary INTO :2' USING 100 RETURNING INTO v_sal;
|
| ☆Dynamic Ref Cursor — Open a cursor for SQL built at runtime |
OPEN rc FOR 'SELECT * FROM ' || v_table;
|