我的存储过程如下:
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,
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>='||logTime||' and create_sj<'||logTime+1||'
group by userid, getday(create_sj)';
这里创建视图的时候报错,字符到数值的转换错误各位达人,我该怎么改?
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,
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>='||logTime||' and create_sj<'||logTime+1||'
group by userid, getday(create_sj)';
这里创建视图的时候报错,字符到数值的转换错误各位达人,我该怎么改?
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>='||logTime||' and create_sj <'||logTime+1||'
group by userid, getday(create_sj)
你的这个语句在oracle里面能执行么?
select getday(create_sj), t.userid, sum(cast(cast(cast(logintime as DATE)-cast(create_sj as DATE) as int)*24*60 as int)) num
from tb_log_grxt_loginlog t where type=2
and create_sj>='06-1月 -09' and create_sj<'07-1月 -09'
group by userid, getday(create_sj)这句话是可以执行的
execute immediate 'CREATE OR REPLACE VIEW view_online_time AS
select getday(create_sj) log_time, 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>='''||logTime||''' and create_sj <'''||logTime+1||'''--是不是这里的引号的问题
group by userid, getday(create_sj)';
报字符到数值的转换错误
我怀疑是这里sum(cast((cast(logintime as DATE)-cast(create_sj as DATE))*24*60 as int))但是不知道该怎么改
create table TB_LOG_GRXT_LOGINLOG
(
ID NUMBER(12) not null,
USERID NUMBER(6) not null,
TYPE CHAR(1) not null,
USERSTATUS CHAR(1) not null,
CREATE_SJ TIMESTAMP(6) default systimestamp not null,
LOGINTIME TIMESTAMP(6),
SESSIONID VARCHAR2(50)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table TB_LOG_GRXT_LOGINLOG
is '个人系统账号登陆注册日志表';
-- Add comments to the columns
comment on column TB_LOG_GRXT_LOGINLOG.ID
is '流水id';
comment on column TB_LOG_GRXT_LOGINLOG.USERID
is '账号id';
comment on column TB_LOG_GRXT_LOGINLOG.TYPE
is '类型:1注册2账号登入3账号登出';
comment on column TB_LOG_GRXT_LOGINLOG.USERSTATUS
is '用户状态';
comment on column TB_LOG_GRXT_LOGINLOG.CREATE_SJ
is '创建时间';
comment on column TB_LOG_GRXT_LOGINLOG.LOGINTIME
is '登陆时间';
comment on column TB_LOG_GRXT_LOGINLOG.SESSIONID
is 'sessionid';
-- Create/Recreate primary, unique and foreign key constraints
alter table TB_LOG_GRXT_LOGINLOG
add constraint LOG_TB_GRXT_LOGINLOG_ID primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
得到的是2009-1-8
sum(cast((cast(logintime as DATE)-cast(create_sj as DATE))*24*60 as int)) num
去掉看有没有错误
select getday(create_sj), t.userid, sum(cast(cast(cast(logintime as DATE)-cast(create_sj as DATE) as int)*24*60 as int)) num
from tb_log_grxt_loginlog t where type=2
and create_sj>='06-1月 -09' and create_sj<'07-1月 -09'
group by userid, getday(create_sj)这句话在plsql里连oracle是执行的出来的
抓狂
( thedate IN DATE
) RETURN DATE AS
BEGIN
RETURN to_date(to_char(thedate,'yyyymmdd'),'yyyymmdd');
END GETDAY;
把你的
create_sj>='||logTime||' and create_sj <'||logTime+1||'
用to_date函数转一下试试吧
是这里
+1加错地方了
给分了 怎么给啊
这里的问题,应该是类型不一致所引起的,logTime是字符型的吗???create_sj又是什么类型,如果create_sj>='06-1月 -09' and create_sj <'07-1月 -09'
这个可以的话,就说明你的create_sj是字符型,将logTime转成字符型应该就可以了。