PL/SQL執行動態SQL

以下程式包對如何執行一段UPDATE語句進行了演示：

CREATE OR REPLACE PACKAGE BODY CUX_TEST IS exp_warning EXCEPTION; exp_error EXCEPTION; -- ==================== -- Output Log. 日誌輸出 -- ==================== PROCEDURE log(p_info VARCHAR2) IS BEGIN --fnd_file.put_line(fnd_file.log, p_info); dbms_output.put_line(p_info); EXCEPTION WHEN OTHERS THEN --fnd_file.put_line(fnd_file.log, 'Exception in CUX_TEST.log: ' || SQLERRM); dbms_output.put_line('Exception in CUX_TEST.log: ' || SQLERRM); RAISE exp_warning; END log; -- ================================ -- Process Request. 程式內部主應用 -- ================================= PROCEDURE process_request(p_table_name IN VARCHAR2, p_key_name IN VARCHAR2, p_interface_id IN NUMBER, p_column_name IN VARCHAR2, p_value IN VARCHAR2) IS l_exe_sql VARCHAR2(4000); BEGIN l_exe_sql := 'UPDATE ' || p_table_name || ' SET ' || p_column_name || ' = ' || p_value|| ' WHERE ' || p_key_name || ' = ' || p_interface_id; log('執行SQL: '||l_exe_sql); EXECUTE IMMEDIATE l_exe_sql; --動態執行DDL語句 EXCEPTION WHEN OTHERS THEN log('Exception in CUX_TEST.process_request: ' || SQLERRM); RAISE exp_warning; END process_request; -- ============== -- Main Procedure -- ============== PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2, p_table_name IN VARCHAR2, p_key_name IN VARCHAR2, p_interface_id IN NUMBER, p_column_name IN VARCHAR2, p_value IN VARCHAR2) IS l_warning_flag NUMBER; l_error_flag NUMBER; BEGIN process_request(p_table_name, p_key_name, p_interface_id, p_column_name, p_value); IF l_warning_flag = 1 THEN retcode := 1; ELSIF l_error_flag = 1 THEN retcode := 2; ELSE retcode := 0; END IF; EXCEPTION WHEN exp_warning THEN --ROLLBACK; retcode := 1; WHEN exp_error THEN --ROLLBACK; retcode := 2; WHEN OTHERS THEN ROLLBACK; retcode := 2; log('Exception in CUX_TEST.main: ' || SQLERRM); END; END CUX_TEST ;