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