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

红太狼 2024-04-18 00:26 62阅读 0赞

例子中的语句使用动态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/