EXECUTE IMMEDIATEEXECUTE IMMEDIATE dynamic_sql_string
[USING bind_arguments]
[RETURNING INTO out_arguments];
關鍵點:
USING 綁定輸入參數(對應佔位符 :1, :2...)。RETURNING INTO 捕獲 DML 的 RETURNING 子句結果。DBMS_SQL 緩存游標。EMP 表 CRUD 操作CREATE OR REPLACE PROCEDURE create_table AS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE emp (
id NUMBER, name VARCHAR2(10), salary NUMBER)';
INSERT INTO emp VALUES (100, 'Jacky', 5600);
INSERT INTO emp VALUES (101, 'Rose', 3000);
INSERT INTO emp VALUES (102, 'John', 4500);
END;
CREATE OR REPLACE PROCEDURE find_info(p_id NUMBER) AS
v_name VARCHAR2(10); v_salary NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT name, salary FROM emp WHERE id = :1'
USING p_id
RETURNING INTO v_name, v_salary;
DBMS_OUTPUT.PUT_LINE(v_name || '的收入為:' || v_salary);
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('找不到相應資料');
END;
CREATE OR REPLACE PROCEDURE find_emp(p_salary NUMBER) AS
r_emp emp%ROWTYPE;
TYPE c_type IS REF CURSOR;
c1 c_type;
BEGIN
OPEN c1 FOR 'SELECT * FROM emp WHERE salary > :1' USING p_salary;
LOOP
FETCH c1 INTO r_emp; EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || r_emp.id || ' 姓名: ' || r_emp.name);
END LOOP;
CLOSE c1;
END;
USING 綁定變數,拒絕字串拼接。DBMS_SQL:支援描述、定義、批次綁定,適合未知欄位數量的動態查詢。DBMS_SQL 可避免重複解析。DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 定位動態 SQL 錯誤行號。動態 SQL 是雙面刃:賦予極大靈活性,卻以解析開銷為代價。掌握 EXECUTE IMMEDIATE 與 DBMS_SQL 兩套機制,依場景選擇,並嚴守綁定變數原則,方能開發出安全高效的 PL/SQL 程式。