执行完存储过程后,t_message表中的send_flag标志还是1,很奇怪,找不到原因,为什么是这样?
请大家,帮忙看看,谢谢了
输出:
[code=SQ]L
SQL> execute pro_createtable_usermsg(1);
v_kk1:148
v_kk2:0
PL/SQL procedure successfully completed
Executed in 0.234 seconds
[/code]存储过程:
[code=SQ]L
CREATE OR REPLACE PROCEDURE PRO_CREATETABLE_USERMSG(FORECASTTYPE IN NUMBER)
AUTHID CURRENT_USER IS
SQL_IN VARCHAR2(5000);
TAB_NAME VARCHAR2(200);
ERR1 VARCHAR2(100);
TOM_SQLCODE VARCHAR2(10);
TOM_IP VARCHAR2(20);
K CHAR(1);
v_starttime varchar2(10);
v_endtime varchar2(10);
v_flag number default 0;
v_exit number default 0;
v_kk number default 0;
v_kk1 number default 0;
v_kk2 number default 0;
BEGIN
SELECT DECODE(SUBSTR(TO_CHAR(SYSDATE, 'HH24'), 1), '0', '1', '2')
INTO K
FROM DUAL;
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--start--',
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
IF FORECASTTYPE = 1 THEN
v_starttime := '070000';
v_endtime := '113000';
while to_char(sysdate, 'hh24miss') between v_starttime and v_endtime loop
v_exit := 0;
update t_message
set send_flag = 2
where send_flag = 1
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);
commit;
select count(*)
into v_flag
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);
--if v_flag >= 1 and mod(to_char(sysdate, 'mi'), 5) = 0 and
--mod(to_char(sysdate, 'ss'), 59) = 0 then
if v_flag >= 1 then
TAB_NAME := 'USER_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '_1_' ||
TO_CHAR(SYSDATE, 'HH24MISS');
SQL_IN := 'CREATE TABLE ' || TAB_NAME || ' NOLOGGING AS ';
SQL_IN := SQL_IN ||
' SELECT /*+ INDEX(B(USER_MOBILE)) */ USER_MOBILE,FORECAST_AREA,';
SQL_IN := SQL_IN || ' USER_AREA,''' || '07:00''';
SQL_IN := SQL_IN ||
' SEND_TIME,B.SERVICE_ID,MESSAGE,SEV_CODE,FEETYPE AS FEE_TYPE,FEECODE,SEND_FLAG,B.FORECAST_TYPE';
SQL_IN := SQL_IN ||
' FROM T_MESSAGE A, T_USER_MOBILE_DZ B, T_SERVICE C';
SQL_IN := SQL_IN || ' WHERE A.SERVICE_ID = C.ID';
SQL_IN := SQL_IN || ' AND C.SEND_TYPE = 0';
SQL_IN := SQL_IN || ' AND A.SEND_FLAG = 2';
SQL_IN := SQL_IN || ' AND A.AREA_ID = B.FORECAST_AREA';
SQL_IN := SQL_IN || ' AND A.SERVICE_ID = B.SERVICE_ID';
SQL_IN := SQL_IN || ' AND B.USER_STATUS = 1';
SQL_IN := SQL_IN || ' AND DECODE(B.FORECAST_TYPE,9,' || K ||
',B.FORECAST_TYPE)=1';
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--create-start-' || TAB_NAME ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
select count(*) into v_kk1 from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--此处查出来的记录数是148
--commit;
DBMS_OUTPUT.put_line('v_kk1:'||v_kk1);
EXECUTE IMMEDIATE SQL_IN;
select count(*) into v_kk2 from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--此处查出来的记录数是0
DBMS_OUTPUT.put_line('v_kk2:'||v_kk2);
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--create-end-' || TAB_NAME ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
SQL_IN := 'alter table ' || TAB_NAME || ' rename to ' ||
replace(upper(TAB_NAME), 'U', 'Y');
EXECUTE IMMEDIATE SQL_IN;
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--rename--' ||
replace(upper(TAB_NAME), 'U', 'Y') ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
v_exit := 1;
select count(*) into v_kk from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);
update t_message
set send_flag = 3
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);
commit;
end if;
exit when v_exit = 1;
end loop;
END IF;
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--end--',
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ERR1 := SUBSTR(SQLERRM, 1, 100);
TOM_SQLCODE := SQLCODE;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO TOM_IP FROM DUAL;
INSERT INTO T_ROVER_ALERT
(ALERTNAME,
ALERTKEY,
ALERTDATE,
DATETIME,
FLAG,
MSG,
ALARTERR,
ALERTLEVEL)
VALUES
(TOM_IP,
'PRO_CREATETABLE_USERMSG',
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
SYSDATE,
0,
ERR1,
TOM_SQLCODE,
5);
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--EXCEPTION--' || ERR1,
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
END PRO_CREATETABLE_USERMSG;[/code]
请大家,帮忙看看,谢谢了
输出:
[code=SQ]L
SQL> execute pro_createtable_usermsg(1);
v_kk1:148
v_kk2:0
PL/SQL procedure successfully completed
Executed in 0.234 seconds
[/code]存储过程:
[code=SQ]L
CREATE OR REPLACE PROCEDURE PRO_CREATETABLE_USERMSG(FORECASTTYPE IN NUMBER)
AUTHID CURRENT_USER IS
SQL_IN VARCHAR2(5000);
TAB_NAME VARCHAR2(200);
ERR1 VARCHAR2(100);
TOM_SQLCODE VARCHAR2(10);
TOM_IP VARCHAR2(20);
K CHAR(1);
v_starttime varchar2(10);
v_endtime varchar2(10);
v_flag number default 0;
v_exit number default 0;
v_kk number default 0;
v_kk1 number default 0;
v_kk2 number default 0;
BEGIN
SELECT DECODE(SUBSTR(TO_CHAR(SYSDATE, 'HH24'), 1), '0', '1', '2')
INTO K
FROM DUAL;
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--start--',
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
IF FORECASTTYPE = 1 THEN
v_starttime := '070000';
v_endtime := '113000';
while to_char(sysdate, 'hh24miss') between v_starttime and v_endtime loop
v_exit := 0;
update t_message
set send_flag = 2
where send_flag = 1
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);
commit;
select count(*)
into v_flag
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);
--if v_flag >= 1 and mod(to_char(sysdate, 'mi'), 5) = 0 and
--mod(to_char(sysdate, 'ss'), 59) = 0 then
if v_flag >= 1 then
TAB_NAME := 'USER_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '_1_' ||
TO_CHAR(SYSDATE, 'HH24MISS');
SQL_IN := 'CREATE TABLE ' || TAB_NAME || ' NOLOGGING AS ';
SQL_IN := SQL_IN ||
' SELECT /*+ INDEX(B(USER_MOBILE)) */ USER_MOBILE,FORECAST_AREA,';
SQL_IN := SQL_IN || ' USER_AREA,''' || '07:00''';
SQL_IN := SQL_IN ||
' SEND_TIME,B.SERVICE_ID,MESSAGE,SEV_CODE,FEETYPE AS FEE_TYPE,FEECODE,SEND_FLAG,B.FORECAST_TYPE';
SQL_IN := SQL_IN ||
' FROM T_MESSAGE A, T_USER_MOBILE_DZ B, T_SERVICE C';
SQL_IN := SQL_IN || ' WHERE A.SERVICE_ID = C.ID';
SQL_IN := SQL_IN || ' AND C.SEND_TYPE = 0';
SQL_IN := SQL_IN || ' AND A.SEND_FLAG = 2';
SQL_IN := SQL_IN || ' AND A.AREA_ID = B.FORECAST_AREA';
SQL_IN := SQL_IN || ' AND A.SERVICE_ID = B.SERVICE_ID';
SQL_IN := SQL_IN || ' AND B.USER_STATUS = 1';
SQL_IN := SQL_IN || ' AND DECODE(B.FORECAST_TYPE,9,' || K ||
',B.FORECAST_TYPE)=1';
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--create-start-' || TAB_NAME ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
select count(*) into v_kk1 from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--此处查出来的记录数是148
--commit;
DBMS_OUTPUT.put_line('v_kk1:'||v_kk1);
EXECUTE IMMEDIATE SQL_IN;
select count(*) into v_kk2 from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--此处查出来的记录数是0
DBMS_OUTPUT.put_line('v_kk2:'||v_kk2);
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--create-end-' || TAB_NAME ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
SQL_IN := 'alter table ' || TAB_NAME || ' rename to ' ||
replace(upper(TAB_NAME), 'U', 'Y');
EXECUTE IMMEDIATE SQL_IN;
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--rename--' ||
replace(upper(TAB_NAME), 'U', 'Y') ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
v_exit := 1;
select count(*) into v_kk from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);
update t_message
set send_flag = 3
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);
commit;
end if;
exit when v_exit = 1;
end loop;
END IF;
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--end--',
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ERR1 := SUBSTR(SQLERRM, 1, 100);
TOM_SQLCODE := SQLCODE;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO TOM_IP FROM DUAL;
INSERT INTO T_ROVER_ALERT
(ALERTNAME,
ALERTKEY,
ALERTDATE,
DATETIME,
FLAG,
MSG,
ALARTERR,
ALERTLEVEL)
VALUES
(TOM_IP,
'PRO_CREATETABLE_USERMSG',
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
SYSDATE,
0,
ERR1,
TOM_SQLCODE,
5);
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--EXCEPTION--' || ERR1,
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
END PRO_CREATETABLE_USERMSG;[/code]
[code=SQ]L
SQL>
SQL>
SQL>
SQL> declare
2 SQL_IN VARCHAR2(5000);
3 v_kk1 number default 0;
4 v_kk2 number default 0;
5 FORECASTTYPE number default 0;
6 BEGIN
7 FORECASTTYPE :=1;
8 IF FORECASTTYPE = 1 THEN
9 update t_message_test set send_flag=2 where send_flag = 1
10 and forecast_type = FORECASTTYPE
11 and (service_id = 3 or service_id = 5);
12
13 select count(*) into v_kk1 from t_message_test
14 where send_flag = 2
15 and forecast_type = FORECASTTYPE
16 and (service_id = 3 or service_id = 5);
17 DBMS_OUTPUT.put_line('v_kk1:'||v_kk1);
18 --commit;
19 SQL_IN :='create table t_message_test_copy as select * from t_message_test';
20 SQL_IN :=SQL_IN||' where send_flag=2';
21 SQL_IN :=SQL_IN||' and forecast_type ='||FORECASTTYPE;
22 SQL_IN :=SQL_IN||' and (service_id = 3 or service_id = 5)';
23
24 EXECUTE IMMEDIATE SQL_IN;
25
26 select count(*) into v_kk2 from t_message_test
27 where send_flag = 2
28 and forecast_type = FORECASTTYPE
29 and (service_id = 3 or service_id = 5);
30 DBMS_OUTPUT.put_line('v_kk2:'||v_kk2);
31
32 update t_message_test set send_flag=3 where send_flag = 2
33 and forecast_type = FORECASTTYPE
34 and (service_id = 3 or service_id = 5);
35
36 commit;
37 END IF;
38 end;
39 /
SQL>
v_kk1:19398656
v_kk2:19398656
PL/SQL procedure successfully completed
[/code]
AUTHID CURRENT_USER IS
SQL_IN VARCHAR2(5000);
TAB_NAME VARCHAR2(200);
ERR1 VARCHAR2(100);
TOM_SQLCODE VARCHAR2(10);
TOM_IP VARCHAR2(20);
K CHAR(1);
v_starttime varchar2(10);
v_endtime varchar2(10);
v_flag number default 0;
v_exit number default 0;
v_kk number default 0;
v_kk1 number default 0;
v_kk2 number default 0;
BEGIN
SELECT DECODE(SUBSTR(TO_CHAR(SYSDATE, 'HH24'), 1), '0', '1', '2')
INTO K
FROM DUAL; --10点之前 K为1,10点之后(包括10点)K为2
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--start--',
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss')); --记录程序运行的日志
commit;
IF FORECASTTYPE = 1 THEN --FORECASTTYPE 传入的变量,10之前调用这个存储过程传入的值是1,之后传入的值是2
v_starttime := '070000';
v_endtime := '113000';
while to_char(sysdate, 'hh24miss') between v_starttime and v_endtime loop --循环再7:00到11:30之间循环
v_exit := 0; --退出标志 ,用于测试,第一次生成数据后 就退出循环
update t_message
set send_flag = 2
where send_flag = 1
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--将t_message中send_flag字段先置为2
commit;
select count(*)
into v_flag
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5); --取出t_message中send_flag字段为2的记录条数
--if v_flag >= 1 and mod(to_char(sysdate, 'mi'), 5) = 0 and
--mod(to_char(sysdate, 'ss'), 59) = 0 then
if v_flag >= 1 then --有记录时 就执行下面的建表语句 TAB_NAME := 'USER_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '_1_' ||
TO_CHAR(SYSDATE, 'HH24MISS');--表名 SQL_IN := 'CREATE TABLE ' || TAB_NAME || ' NOLOGGING AS ';
SQL_IN := SQL_IN ||
' SELECT /*+ INDEX(B(USER_MOBILE)) */ USER_MOBILE,FORECAST_AREA,';
SQL_IN := SQL_IN || ' USER_AREA,''' || '07:00''';
SQL_IN := SQL_IN ||
' SEND_TIME,B.SERVICE_ID,MESSAGE,SEV_CODE,FEETYPE AS FEE_TYPE,FEECODE,SEND_FLAG,B.FORECAST_TYPE';
SQL_IN := SQL_IN ||
' FROM T_MESSAGE A, T_USER_MOBILE_DZ B, T_SERVICE C';
SQL_IN := SQL_IN || ' WHERE A.SERVICE_ID = C.ID';
SQL_IN := SQL_IN || ' AND C.SEND_TYPE = 0';
SQL_IN := SQL_IN || ' AND A.SEND_FLAG = 2';
SQL_IN := SQL_IN || ' AND A.AREA_ID = B.FORECAST_AREA';
SQL_IN := SQL_IN || ' AND A.SERVICE_ID = B.SERVICE_ID';
SQL_IN := SQL_IN || ' AND B.USER_STATUS = 1';
SQL_IN := SQL_IN || ' AND DECODE(B.FORECAST_TYPE,9,' || 1 ||
',B.FORECAST_TYPE)=1'; --建表语句 insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--create-start-' || TAB_NAME ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));--记录日志
select count(*)
into v_kk1
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--查看send_flag = 2的记录数
--commit;
DBMS_OUTPUT.put_line('v_kk1:' || v_kk1);--输出记录数 EXECUTE IMMEDIATE SQL_IN;--执行动态建表语句
select count(*)
into v_kk2
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--查看send_flag = 2的记录数 DBMS_OUTPUT.put_line('v_kk2:' || v_kk2);--输出记录数 insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--create-end-' || TAB_NAME ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));--记录日志 commit;
SQL_IN := 'alter table ' || TAB_NAME || ' rename to ' ||
replace(upper(TAB_NAME), 'U', 'Y');--修改前面创建的表的名称 EXECUTE IMMEDIATE SQL_IN;--修改前面创建的表的名称 insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--rename--' ||
replace(upper(TAB_NAME), 'U', 'Y') ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));--记录日志 commit; v_exit := 1;--退出循环条件 select count(*)
into v_kk
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--查看send_flag = 2的记录数 update t_message
set send_flag = 3
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--将send_flag = 2的记录的 send_flag更新为3 commit;
end if;
exit when v_exit = 1;--退出循环
end loop;
END IF; insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--end--',
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));--记录日志
commit;
EXCEPTION --异常处理
WHEN OTHERS THEN
ROLLBACK;
ERR1 := SUBSTR(SQLERRM, 1, 100);
TOM_SQLCODE := SQLCODE;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO TOM_IP FROM DUAL;
INSERT INTO T_ROVER_ALERT
(ALERTNAME,
ALERTKEY,
ALERTDATE,
DATETIME,
FLAG,
MSG,
ALARTERR,
ALERTLEVEL)
VALUES
(TOM_IP,
'PRO_CREATETABLE_USERMSG',
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
SYSDATE,
0,
ERR1,
TOM_SQLCODE,
5);
insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG--EXCEPTION--' || ERR1,
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
commit;
END PRO_CREATETABLE_USERMSG;
PRO_CREATETABLE_USERMSG PRO_CREATETABLE_USERMSG--start-- 2011-02-22 09:55:40
PRO_CREATETABLE_USERMSG PRO_CREATETABLE_USERMSG_1--create-start-USER_20110222_1_095605095610 2011-02-22 09:56:10
PRO_CREATETABLE_USERMSG PRO_CREATETABLE_USERMSG_1--create-end-USER_20110222_1_095605100340 2011-02-22 10:03:40
PRO_CREATETABLE_USERMSG PRO_CREATETABLE_USERMSG_1--rename--YSER_20110222_1_095605100342 2011-02-22 10:03:42
PRO_CREATETABLE_USERMSG PRO_CREATETABLE_USERMSG--end-- 2011-02-22 10:03:45
and forecast_type = FORECASTTYPE and (service_id = 3 or service_id = 5)
导致表的send_flag未被更新到,所以就有send_flag=1的记录
你仔细检查哈
取v_flag,v_kk1,v_kk2 的条件都一样,但是取到的数据不一样, v_flag和v_kk1都为148,这中间执行的动态建表语句中,保存到的数据中取到的send_flag是为2,执行到v_kk2处,取到的值是0
update t_message
set send_flag = 2
where send_flag = 1
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--将t_message中send_flag字段先置为2
commit;
select count(*)
into v_flag
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5); --取出t_message中send_flag字段为2的记录条数
---在此处 取得 v_flag 为148 --if v_flag >= 1 and mod(to_char(sysdate, 'mi'), 5) = 0 and
--mod(to_char(sysdate, 'ss'), 59) = 0 then
if v_flag >= 1 then --有记录时 就执行下面的建表语句 TAB_NAME := 'USER_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '_1_' ||
TO_CHAR(SYSDATE, 'HH24MISS');--表名 SQL_IN := 'CREATE TABLE ' || TAB_NAME || ' NOLOGGING AS ';
SQL_IN := SQL_IN ||
' SELECT /*+ INDEX(B(USER_MOBILE)) */ USER_MOBILE,FORECAST_AREA,';
SQL_IN := SQL_IN || ' USER_AREA,''' || '07:00''';
SQL_IN := SQL_IN ||
' SEND_TIME,B.SERVICE_ID,MESSAGE,SEV_CODE,FEETYPE AS FEE_TYPE,FEECODE,SEND_FLAG,B.FORECAST_TYPE';
SQL_IN := SQL_IN ||
' FROM T_MESSAGE A, T_USER_MOBILE_DZ B, T_SERVICE C';
SQL_IN := SQL_IN || ' WHERE A.SERVICE_ID = C.ID';
SQL_IN := SQL_IN || ' AND C.SEND_TYPE = 0';
SQL_IN := SQL_IN || ' AND A.SEND_FLAG = 2';
SQL_IN := SQL_IN || ' AND A.AREA_ID = B.FORECAST_AREA';
SQL_IN := SQL_IN || ' AND A.SERVICE_ID = B.SERVICE_ID';
SQL_IN := SQL_IN || ' AND B.USER_STATUS = 1';
SQL_IN := SQL_IN || ' AND DECODE(B.FORECAST_TYPE,9,' || 1 ||
',B.FORECAST_TYPE)=1'; --建表语句 insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--create-start-' || TAB_NAME ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));--记录日志
select count(*)
into v_kk1
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--查看send_flag = 2的记录数
--commit;
DBMS_OUTPUT.put_line('v_kk1:' || v_kk1);--输出记录数---在此处 取得 v_kk1 为148 EXECUTE IMMEDIATE SQL_IN;--执行动态建表语句
select count(*)
into v_kk2
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--查看send_flag = 2的记录数 DBMS_OUTPUT.put_line('v_kk2:' || v_kk2);--输出记录数---在此处 取得 v_kk1 为0 insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--create-end-' || TAB_NAME ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));--记录日志 commit;
SQL_IN := 'alter table ' || TAB_NAME || ' rename to ' ||
replace(upper(TAB_NAME), 'U', 'Y');--修改前面创建的表的名称 EXECUTE IMMEDIATE SQL_IN;--修改前面创建的表的名称 insert into t_exec_pro_log
(pro_name, txt, create_time)
values
('PRO_CREATETABLE_USERMSG',
'PRO_CREATETABLE_USERMSG_1--rename--' ||
replace(upper(TAB_NAME), 'U', 'Y') ||
to_char(sysdate, 'hh24miss'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));--记录日志 commit; v_exit := 1;--退出循环条件 select count(*)
into v_kk
from t_message
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--查看send_flag = 2的记录数 update t_message
set send_flag = 3
where send_flag = 2
and forecast_type = FORECASTTYPE
and (service_id = 3 or service_id = 5);--将send_flag = 2的记录的 send_flag更新为3 commit;
DBMS_OUTPUT.put_line('v_kk2:' || v_kk2);--输出记录数---在此处 取得 v_kk1 为0