oracle inside(7)

執行動態SQL有兩種方式：一是 DBMS_SQL 程式包；二是本機動態 SQL。
7.1 DBMS_SQL 程式包
功能強大，函數眾多，很少使用。
7.2 本機動態SQL
7.2.1 執行 DDL 語句
BEGIN
PL/SQL 進階編程
第 29 頁 共 29 頁
EXECUTE IMMEDIATE 'CREATE TABLE yang_test (eid VARCHAR2(30) PRIMARY KEY)';
EXECUTE IMMEDIATE 'ALTER TABLE yang_test ADD (ePhoto CLOB)';
EXECUTE IMMEDIATE 'ALTER TABLE yang_test MODIFY (eid NUMBER)';
EXECUTE IMMEDIATE 'DROP TABLE yang_test';
END;
/
7.2.2 使用綁定變數
DECLARE
l_sql VARCHAR2(200);
l_photo yang_test.ePhoto%TYPE;
l_id yang_test.eid%TYPE;
BEGIN
l_sql := 'INSERT INTO yang_test VALUES(:1, :2)';
EXECUTE IMMEDIATE l_sql USING 10, 'welcome to our couty';
COMMIT;
l_sql := 'SELECT ePhoto, eid FROM yang_test WHERE eid = :1';
EXECUTE IMMEDIATE l_sql INTO l_photo, l_id USING 10;
DBMS_OUTPUT.PUT_LINE(l_id);
DBMS_OUTPUT.PUT_LINE(l_photo);
END;
/
注意：運行以上代碼，查看結果！！！
7.2.3 執行 PL/SQL 塊
DECLARE
l_id NUMBER;
l_sql VARCHAR2(200) := 'BEGIN SELECT eid INTO :1 FROM yang_test; END;';
BEGIN
EXECUTE IMMEDIATE l_sql INTO l_id;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_id));
END;