oracle 存储过程 没报错 运行通过 就是添加不上数据

[xiao224269]

第一次写,求大神指点.

CREATE OR REPLACE PROCEDURE P_USER_STATISTICS IS
  time DATE;
  cursor userStatistics is
   select * from (
         select  t1.register_num register_num,
                 t2.recharge_num recharge_num,
                 t3.apply_loan_sum apply_loan_sum,
                 t4.invest_num invest_num,
                 t5.sys_register_sum sys_register_sum,
                 t6.pc_register_sum pc_register_sum,
                 t7.wap_register_sum wap_register_sum,
                 t8.app_register_sum app_register_sum,
                 t9.login_sum login_sum
                 from
        (select count(1) as register_num from users us where us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate)) t1,

        (select count(distinct urd.user_id) as recharge_num from user_recharge_details urd where urd.is_completed = '1'and trunc(urd.time) = trunc(sysdate)) t2,

        (select count(distinct b.user_id) as apply_loan_sum  from bids b where trunc(b.time) = trunc(sysdate)) t3,

        (select count(distinct inv.user_id) as invest_num from invests inv where trunc(inv.time) = trunc(sysdate)) t4,

        (select count(1) as sys_register_sum from users us where us.channel_id = '1' and trunc(us.time) = trunc(sysdate)) t5,

        (select count(1) as pc_register_sum from users us where us.channel_id = '0' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate)) t6,

        (select count(1) as wap_register_sum from users us where us.channel_id = '2' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate)) t7,

        (select count(1) as app_register_sum from users us where us.channel_id = '3' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate)) t8,

        (select count(1) as login_sum from users us where trunc(us.last_login_time) = trunc(sysdate)) t9

   );
  us userStatistics%rowtype;

  BEGIN
  select to_char(sysdate,'yyyy/MM/dd') time
    into time
    from dual t;
  for us in userStatistics loop

 INSERT INTO Statistic_Behavior
      (id,
       time,
       register_num,
       recharge_num,
       apply_loan_sum,
       invest_num,
       sys_register_sum,
       pc_register_sum,
       wap_register_sum,
       app_register_sum,
       login_sum)
    values
      (SEQ_STATISTIC_BEHAVIOR.NEXTVAL,
       time,
       us.register_num,
       us.recharge_num,
       us.apply_loan_sum,
       us.invest_num,
       us.sys_register_sum,
       us.pc_register_sum,
       us.wap_register_sum,
       us.app_register_sum,
       us.login_sum);

  END loop;
  commit;
end;

[xiao224269]

查询的语句是可以查询出数据的.

[xiao224269]

改造的现在有报错.

[xiao224269]

不报错,能运行,还是数据添加不上

[碧水幽幽泉]

存储过程需要调用才有结果呀。 记得执行： exec p_user_statistics;

[xiao224269]

执行报错了....................

[xiao224269]

可以了,因为time是date类型,我又重新转了一下 to_date(to_char(sysdate,'yyyy/MM/dd'),'yyyy/MM/dd')

[xiao224269]

你用手机回复,我结贴了

[碧水幽幽泉]

帮你改造了下，运行下试试看:

create or replace procedure p_user_statistics
 is
    register_num           number(10);
    recharge_num         number(10);
    apply_loan_sum      number(10);
    invest_num               number(10);
    sys_register_sum    number(10);
    pc_register_sum     number(10);
    wap_register_sum  number(10);
    app_register_sum  number(10);
    login_sum                number(10);
begin     

     select count(1)   into  register_num  from users us where us.is_allow_login = '1'  and trunc(us.time) = trunc(sysdate)；
     select count(distinct urd.user_id) into recharge_num from user_recharge_details urd where urd.is_completed = '1'and trunc(urd.time) = trunc(sysdate) t2;
     select count(distinct b.user_id) into apply_loan_sum  from bids b where trunc(b.time) = trunc(sysdate);
     select count(distinct inv.user_id) into invest_num from invests inv where trunc(inv.time) = trunc(sysdate);
     select count(1) into sys_register_sum from users us where us.channel_id = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into pc_register_sum from users us where us.channel_id = '0' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate);
     select count(1) into wap_register_sum from users us where us.channel_id = '2' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate);
     select count(1) into app_register_sum from users us where us.channel_id = '3' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate);
     select count(1) into login_sum from users us where trunc(us.last_login_time) = trunc(sysdate);
 
     insert into statistic_behavior
                        (
                         id,
                         time,
                         register_num,
                         recharge_num,
                         apply_loan_sum,
                         invest_num,
                         sys_register_sum,
                         pc_register_sum,
                         wap_register_sum,
                         app_register_sum,
                         login_sum
                         )
              values
                        (
                         seq_statistic_behavior.nextval,
                         to_char(sysdate,'yyyy/mm/dd'),
                         register_num,
                         recharge_num,
                         apply_loan_sum,
                         invest_num,
                         sys_register_sum,
                         pc_register_sum,
                         wap_register_sum,
                         app_register_sum,
                         login_sum
                         );
                         
  commit;
  
end p_user_statistics;

[碧水幽幽泉]

调整了下格式:

create or replace procedure p_user_statistics
 is
    register_num        number(10);
    recharge_num        number(10);
    apply_loan_sum      number(10);
    invest_num          number(10);
    sys_register_sum    number(10);
    pc_register_sum     number(10);
    wap_register_sum    number(10);
    app_register_sum    number(10);
    login_sum           number(10);
begin     

     select count(1) into register_num from users us where us.is_allow_login = '1'  and trunc(us.time) = trunc(sysdate)；
     select count(distinct urd.user_id) into recharge_num from user_recharge_details urd where urd.is_completed = '1' and trunc(urd.time) = trunc(sysdate);
     select count(distinct b.user_id) into apply_loan_sum  from bids b where trunc(b.time) = trunc(sysdate);
     select count(distinct inv.user_id) into invest_num from invests inv where trunc(inv.time) = trunc(sysdate);
     select count(1) into sys_register_sum from users us where us.channel_id = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into pc_register_sum from users us where us.channel_id = '0' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into wap_register_sum from users us where us.channel_id = '2' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into app_register_sum from users us where us.channel_id = '3' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into login_sum from users us where trunc(us.last_login_time) = trunc(sysdate);
 
     insert into statistic_behavior
                (
                 id,
                 time,
                 register_num,
                 recharge_num,
                 apply_loan_sum,
                 invest_num,
                 sys_register_sum,
                 pc_register_sum,
                 wap_register_sum,
                 app_register_sum,
                 login_sum
                 )
            values
                (
                 seq_statistic_behavior.nextval,
                 to_char(sysdate,'yyyy/mm/dd'),
                 register_num,
                 recharge_num,
                 apply_loan_sum,
                 invest_num,
                 sys_register_sum,
                 pc_register_sum,
                 wap_register_sum,
                 app_register_sum,
                 login_sum
                 );
                         
  commit;
  
end p_user_statistics;

[碧水幽幽泉]

trunc(urd.time) = trunc(sysdate)；改成trunc(urd.time) = trunc(sysdate);

create or replace procedure p_user_statistics
 is
    register_num        number(10);
    recharge_num        number(10);
    apply_loan_sum      number(10);
    invest_num          number(10);
    sys_register_sum    number(10);
    pc_register_sum     number(10);
    wap_register_sum    number(10);
    app_register_sum    number(10);
    login_sum           number(10);
begin     

     select count(1) into register_num from users us where us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
     select count(distinct urd.user_id) into recharge_num from user_recharge_details urd where urd.is_completed = '1' and trunc(urd.time) = trunc(sysdate);
     select count(distinct b.user_id) into apply_loan_sum from bids b where trunc(b.time) = trunc(sysdate);
     select count(distinct inv.user_id) into invest_num from invests inv where trunc(inv.time) = trunc(sysdate);
     select count(1) into sys_register_sum from users us where us.channel_id = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into pc_register_sum from users us where us.channel_id = '0' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into wap_register_sum from users us where us.channel_id = '2' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into app_register_sum from users us where us.channel_id = '3' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
     select count(1) into login_sum from users us where trunc(us.last_login_time) = trunc(sysdate);
 
     insert into statistic_behavior
                (
                 id,
                 time,
                 register_num,
                 recharge_num,
                 apply_loan_sum,
                 invest_num,
                 sys_register_sum,
                 pc_register_sum,
                 wap_register_sum,
                 app_register_sum,
                 login_sum
                 )
            values
                (
                 seq_statistic_behavior.nextval,
                 to_char(sysdate,'yyyy/mm/dd'),
                 register_num,
                 recharge_num,
                 apply_loan_sum,
                 invest_num,
                 sys_register_sum,
                 pc_register_sum,
                 wap_register_sum,
                 app_register_sum,
                 login_sum
                 );
                         
  commit;

end p_user_statistics;

[碧水幽幽泉]

引用 12 楼 xiao224269的回复:

可以了,因为time是date类型,我又重新转了一下 to_date(to_char(sysdate,'yyyy/MM/dd'),'yyyy/MM/dd')

不用这么麻烦，直接trunc(sysdate)就好，而且性能更高！

[碧水幽幽泉]

to_char(sysdate,'yyyy/mm/dd') 改成trunc(sysdate) 看看

[碧水幽幽泉]

现在是否OK了，还有问题么？

[碧水幽幽泉]

好的，哈哈！