随笔 - 589  文章 - 0 评论 - 116 trackbacks - 5

-- run 1_datatype_data.sql before running this script
ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation;

drop procedure dynamic_sql_proc;
CREATE PROCEDURE dynamic_sql_proc LANGUAGE SQLSCRIPT AS
    v_sql1   VARCHAR(1024);
    v_sql2   VARCHAR(1024);
    v_msg    VARCHAR(200);
BEGIN
    init_proc();

    v_sql1 := 'INSERT INTO "MESSAGE_BOX" VALUES (''First message from Dynamic SQL'', SYSTIMESTAMP)';
    EXEC :v_sql1;

    v_sql2 := 'INSERT INTO message_box VALUES (''Second message from Dynamic SQL'', SYSTIMESTAMP)';
    EXEC :v_sql2;

    v_sql1 := 'Third message from Dynamic SQL';
    EXEC 'INSERT INTO message_box VALUES (''' || :v_sql1 || ''', SYSTIMESTAMP)';
END;

call dynamic_sql_proc();
select message from message_box;

posted on 2013-04-25 23:21  沧海-重庆  阅读(2471)  评论(0编辑  收藏