执行动态语句后，update操作无效，求助，急急急！请各位帮忙看看...谢谢啦

-无-为- 2011-02-22 10:48:13
执行完存储过程后，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]
...全文
231 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
-无-为- 2011-02-22
  • 打赏
  • 举报
 回复
这个是执行存储过程 产生的日志

[Quote=引用 8 楼 rookie_ceo 的回复:]

SQL code

PRO_CREATETABLE_USERMSG PRO_CREATETABLE_USERMSG--start-- 2011-02-22 09:55:40
PRO_CREATETABLE_USERMSG PRO_CREATETABLE_USERMSG_1--create-s……
[/Quote]
-无-为- 2011-02-22
  • 打赏
  • 举报
 回复 


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
-无-为- 2011-02-22
  • 打赏
  • 举报
 回复
不好意思 这个是一个小的功能处理，注释如下：


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;  --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;
lxyzxq2008 2011-02-22
  • 打赏
  • 举报
 回复
没有数据，也不知道你的where条件能不能查到数据
lxyzxq2008 2011-02-22
  • 打赏
  • 举报
 回复
代码太长，太乱啦，你就把更新的那一快贴出来就行了，这么一大堆，怎么看啊
-无-为- 2011-02-22
  • 打赏
  • 举报
 回复
我用PLSQL Developer单步调试的


-无-为- 2011-02-22
  • 打赏
  • 举报
 回复
(⊙v⊙)嗯 我一步步的调试了 查到v_kk1是148 单步执行完动态语句后 v_kk2是0
gelyon 2011-02-22
  • 打赏
  • 举报
 回复
你一步一步调试下过程嘛，你这代码看起来比较枯燥，逻辑不清楚
-无-为- 2011-02-22
  • 打赏
  • 举报
 回复
我在sqlplus下模拟这个过程，v_kk1和v_kk2输出一致，，，不知道这个原因怎么解释。
[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]
-无-为- 2011-02-22
  • 打赏
  • 举报
 回复
xiexie谢谢各位 这个过程没有错，原因是因为 有一个检测的进程没有写好 修改了我更改的send_flag

备注啊 从昨天找到今天 才搞明白
-无-为- 2011-02-22
  • 打赏
  • 举报
 回复
这里写错了，不是v_kk1 为0 ，而是v_kk2 为0 




        DBMS_OUTPUT.put_line('v_kk2:' || v_kk2);--输出记录数



---在此处 取得 v_kk1 为0
-无-为- 2011-02-22
  • 打赏
  • 举报
 回复
我单步调试
取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;




[Quote=引用 10 楼 gelyon 的回复:]

你是不是t_message表里面send_flag=1的有些记录不满足条件
and forecast_type = FORECASTTYPE and (service_id = 3 or service_id = 5)
导致表的send_flag未被更新到，所以就有send_flag=1的记录
你仔细检查哈
[/Quote]
gelyon 2011-02-22
  • 打赏
  • 举报
 回复
你是不是t_message表里面send_flag=1的有些记录不满足条件
and forecast_type = FORECASTTYPE and (service_id = 3 or service_id = 5)
导致表的send_flag未被更新到，所以就有send_flag=1的记录
你仔细检查哈
数据库执行SQL语句跟踪
不管你是刚学习软件开发的初学者，还是在IT职场打拼多年的职场老鸟，都免不了要同各种数据库打交道，你可能会碰到以下几个问题：1、感觉自己写的sql语句没有错，但是执行结果总是不对；2、某个功能执行的语句知道有...
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
青少儿编程NOIP信息学奥赛通关必备-C++语法专题-程序的分支结构-if...else...语句
本课程为零基础C++语法专题课，是青少儿... 4、学完本课程后，孩子可以自主编程，解决竞赛中，生活实际问题， 5、拓展孩子计算思维，应对人工智能时代的挑战。 6、本课程介绍的是程序的分支结构-if...else...语句的知识
03.分支语句和循环语句
1.例举：Object、String、Math、Random... 2.正则表达式和包装类及日起和时间相关
if语句介绍 视频
1.基础介绍及安装操作 2.快速掌握基础的SQL语句 3.高级特性提示(存储过程，视图，索引) 4.程序操作数据的桥梁JDBC
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章