存储过程如下:
create or replace procedure proc_report_online_time(
logTime_ in date DEFAULT getday(sysdate-1)
)AS BEGINdeclare logTime date;
m1_ number(10,0);
m2_ number(10,0);
m3_ number(10,0);
m4_ number(10,0);
m5_ number(10,0);
m6_ number(10,0);
m7_ number(10,0);
begin
if logTime_ is null then
logTime := getday(sysdate-1);
else
logTime := getday(logTime_);
end if; DELETE Tb_Report_Online_Time WHERE log_time = logtime;
execute immediate 'CREATE OR REPLACE VIEW view_online_time AS
select getday(create_sj) log_time, userid userid,
sum(cast((cast(logintime as DATE)-cast(create_sj as DATE))*24*60 as int)) num
from tb_log_grxt_loginlog where type=2
and create_sj>=to_date('''||logTime||''', ''yyyy-mm-dd'')
and create_sj<to_date('''||logTime||''', ''yyyy-mm-dd'')+1
group by userid, getday(create_sj)';
select count(userid) into m1_ from view_online_time
where num<=10;
执行到这里的时候报文字与格式字符串不匹配错误
何解?
m1_ number(10,0)啊
create or replace procedure proc_report_online_time(
logTime_ in date DEFAULT getday(sysdate-1)
)AS BEGINdeclare logTime date;
m1_ number(10,0);
m2_ number(10,0);
m3_ number(10,0);
m4_ number(10,0);
m5_ number(10,0);
m6_ number(10,0);
m7_ number(10,0);
begin
if logTime_ is null then
logTime := getday(sysdate-1);
else
logTime := getday(logTime_);
end if; DELETE Tb_Report_Online_Time WHERE log_time = logtime;
execute immediate 'CREATE OR REPLACE VIEW view_online_time AS
select getday(create_sj) log_time, userid userid,
sum(cast((cast(logintime as DATE)-cast(create_sj as DATE))*24*60 as int)) num
from tb_log_grxt_loginlog where type=2
and create_sj>=to_date('''||logTime||''', ''yyyy-mm-dd'')
and create_sj<to_date('''||logTime||''', ''yyyy-mm-dd'')+1
group by userid, getday(create_sj)';
select count(userid) into m1_ from view_online_time
where num<=10;
执行到这里的时候报文字与格式字符串不匹配错误
何解?
m1_ number(10,0)啊
dbms_output.put_line(l_sql);看看输出的这个文本就知道哪里多了或者少了啊