Oracle EXECUTE IMMEDIATE语句里面的引号处理

例子中的语句使用动态SQL创建了一个存储过程
将单引号 ' 改写为两个单引号 ''

BEGIN
EXECUTE IMMEDIATE 'create or replace PROCEDURE SMDP_CLEAN_SUBSCRIPTIONS AS
                        CURSOR subscriptions_cur
                        IS
                            SELECT * FROM SMDP_SUBSCRIPTIONS where N_STATUS = 5;
                    
                        TYPE subscriptions_aat IS TABLE OF subscriptions_cur%ROWTYPE
                            INDEX BY PLS_INTEGER;
                    
                        l_subscriptions subscriptions_aat;
                        limit_in Number(10,0);
                        
                        
                        STATUS_LOADED integer :=3;
                        STATUS_ERROR integer :=4;
                        STATUS_GARBAGE integer :=5;
                    
                        CURSOR CUR_SUB is
                            -- ALL subscriptions with status LOADED (=> check if their profile are still in DEMO mode)
                            select s.N_SUBSCRIPTION_ID, s.N_STATUS, p.C_MNO_TRIGRAM, p.C_PROFILE_ID
                            from SMDP_SUBSCRIPTIONS s, SMDP_SUBSCRIPTION_PROFILES p where (s.N_STATUS=STATUS_LOADED or s.N_STATUS=STATUS_ERROR) and p.N_SUBSCRIPTIONPROFILE_ID=s.N_SUBSCRIPTIONPROFILE_ID;
                    
                        TYPE res_table IS TABLE OF CUR_SUB%ROWTYPE;        
                        res res_table;
                        oaps_param varchar2(40);
                        oaps_value varchar2(40);
                        
                        BEGIN           
                            OPEN CUR_SUB;
                            FETCH CUR_SUB BULK COLLECT INTO res;

                            
                            FOR indx IN 1 .. res.COUNT LOOP
                                oaps_param:= '' profile.''||res(indx).C_MNO_TRIGRAM||''.''||res(indx).C_PROFILE_ID||''.mode'';
                                dbms_output.put_line(''id=''||res(indx).N_SUBSCRIPTION_ID||'' status=''||res(indx).N_STATUS||'' oaps=''||oaps_param);
                                -- Get OAPS parameter associated with its profile
                                begin
                                    oaps_value := ''PROD'';
                                    select UPPER(C_PARAM_VALUE) into oaps_value from OAPS_PARAMETERS where C_PRODUCT_TYPE=''SMDP'' and C_PARAM_NAME=oaps_param;
                                    dbms_output.put_line(''FlagMode=''||oaps_value);
                                    -- if no param found, (PROD, DEMO or PROD_RETRY??), do nothing...
                                    exception when NO_DATA_FOUND then null;
                                end;
                                -- if subscription is as LOADED and not in demo mode then set the status to READY_TO_GARBAGE
                                IF oaps_value <> ''DEMO'' THEN
                                    update SMDP_SUBSCRIPTIONS set N_STATUS=STATUS_GARBAGE where SMDP_SUBSCRIPTIONS.N_SUBSCRIPTION_ID=res(indx).N_SUBSCRIPTION_ID;
                                    dbms_output.put_line(''Set to GARBAGE'');
                                END IF;
                            end loop;
                            CLOSE CUR_SUB;
                    
                        limit_in := 1000;
                        OPEN subscriptions_cur;
                        LOOP
                            FETCH subscriptions_cur
                                BULK COLLECT INTO l_subscriptions LIMIT limit_in;
                    
                            FORALL indx IN 1 .. l_subscriptions.COUNT
                               delete from SMDP_SUBSCRIPTIONS where N_SUBSCRIPTION_ID = l_subscriptions(indx).N_SUBSCRIPTION_ID;
                               commit;
                            
                            FORALL indxDiversified IN 1 .. l_subscriptions.COUNT
                               delete from SMDP_DIVERSIFIED_SCRIPTS where N_SUBSCRIPTION_ID = l_subscriptions(indxDiversified).N_SUBSCRIPTION_ID;
                               commit;  
                               
                            EXIT WHEN l_subscriptions.COUNT < limit_in;
                    
                       END LOOP;
                    
                       CLOSE subscriptions_cur;
                    END SMDP_CLEAN_SUBSCRIPTIONS;';
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2132167/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2132167/