例子中的語句使用動態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;

Oracle EXECUTE IMMEDIATE語句裡面的引號處理

相關文章