create or replace procedure proc_rpt_DataGetRate
( stationids in nvarchar2,
startDate in varchar2,
endDate in varchar2
)
isv_begindate date;
v_enddate date;…… v_begindate := to_char(to_date(startDate,'yyyy-mm-dd HH24:mi:ss'),'yyyy-mm-dd HH24:mi:ss');
v_enddate := to_char(to_date(endDate,'yyyy-mm-dd HH24:mi:ss'),'yyyy-mm-dd HH24:mi:ss');不知道为什么,startDate 和endDate 我虽然输入的是‘2010-1-1 9:00:00’和‘2010-1-2 9:00:00’,但是ORACLE总是会自动转成‘01-1月 -10’和‘02-1月 -10’。然后后面的转换就会报错。刚开始是把startDate 和endDate 都设为DATE型,可是ORACLE也是会自动转成‘01-1月 -10’和‘02-1月 -10’,无发进行转换。不知有什么办法没?
解决方案 »
- SQL*PLUS 导出表的记录字段的如何滤掉空格??
- 求救!!!
- 怎样将一个ORACLE数据库服务器的表数据自动定时备份到另一个ORACLE数据库服务器的表中?
- 如何在SELECT 中使用nologging可以不写日志提高速度
- Oralce中集合数据结构的比较
- 一个修改数据类型的问题?
- oracle 10g比 9i有什么优势吗
- 请问一下,Oracle中将字符串转化为Int型的SQL语句是to_number(temp),请问在MYSQL里面的SQL语句该怎么写?急啊!!!
- select * from test where char1 like '%中%'; 报错,请进!Ora_Do failed (ORA-01403:
- 求各位老师们给个查询语句实现下面的要求,谢谢!
- 如何对Package加密??
- 各位朋友帮帮忙~毕业设计想做个数据库相关的 可暂时没太好的思路
v_enddate := to_char(to_date(endDate,'yyyy-mm-dd HH24:mi:ss'),'yyyy-mm-dd HH24:mi:ss');v_begindate,v_enddate声明是date型,为什么还要用to_char转换呢?
Java里面吗,还是pl/sql里面,如果是前者,检查下参数数据类型设置正确没?是String类型还是java.sql.Date类型
如果是后者,检查输入参数类型是否是varchar2,最好把调用的代码也贴出来,这样才能找出问题。
create or replace procedure proc_rpt_DataGetRate
( stationids in nvarchar2,
startDate in varchar2,
endDate in varchar2
)
is
CURSOR getdatarate_cur IS
SELECT STATIONID, STATIONSN, STATIONNAME, CODE, GETRATE, MISSINGBYCORRESPONDINGRATE, BREAKDOWNRATE
FROM TEMP_DATAGETRATE;
v_stationid varchar2(10);--拆分后监测站ID
v_begindate varchar2(10);
v_enddate varchar2(10);
v_bd varchar2(10);
v_ed varchar2(10);
v_s VARCHAR2(1000);
v_tableName varchar2(20); --表名
v_colName varchar2(20);--列名
v_recycle number(10);
v_getRate int;--获取率
v_getRateTotal number(10);--获取率
v_missBycorresponding number(10);--通信丢失率
v_missBycorrespondingTotal number(10);--通信丢失率
v_breakdownRate number(10);--设备故障率
v_breakdownRateTotal number(10);--设备故障率
v_startyear VARCHAR2(4); --开始年
v_endyear VARCHAR2(4); --结束年
v_stationSN varchar2(10); --监测站编码
v_stationName varchar2(100); --监测站名称
v_stationids varchar2(100);
v_strat pls_integer := 1;
v_location number(10);
v_STATIONIDval INTEGER;
V_STATIONSNVAL VARCHAR2(10);
V_STATIONNAMEVAL VARCHAR2(10);
V_CODEVAL VARCHAR2(10);
V_GETRATEVAL NUMBER(10,3);
V_MISSINGBYCORRESPONDINGRATE NUMBER(10,3);
V_BREAKDOWNRATEVAL NUMBER(10,3);begin
dbms_output.put_line('startDate is '||startDate);
dbms_output.put_line('endDate is '||endDate);
v_stationids := ','||stationids||',';
v_location := instr(v_stationids,',',v_strat);
v_stationids := ','||stationids||',';
v_begindate := to_date(startDate,'yyyy-mm-dd HH24:mi:ss');
v_enddate := to_date(endDate,'yyyy-mm-dd HH24:mi:ss');
dbms_output.put_line('v_begindate is '||v_begindate);
dbms_output.put_line('v_enddate is '||v_enddate);
v_startyear := to_char(startDate,'yyyy');
v_endyear := to_char(endDate,'yyyy');
v_bd := v_begindate;
v_ed := v_enddate;
WHILE v_location > 0 LOOP
dbms_output.put_line(v_bd);
dbms_output.put_line(v_ed);
dbms_output.put_line(v_begindate);
dbms_output.put_line(v_enddate);
v_strat := v_location;
v_location := instr(v_stationids,',',v_strat+1);
if v_location>0 then
v_stationid := SUBSTR(v_stationids,v_strat+1,v_location-v_strat-1);
v_s := 'select HisTableName, ColName, Recycle from t_item_mapping where stationid = '||v_stationid||' and code = ''1CV''';
execute immediate v_s into v_tableName,v_colName,v_recycle;
dbms_output.put_line(v_tableName);
dbms_output.put_line(v_colName);
dbms_output.put_line(v_recycle);
v_s := 'select Code,FullDesc from t_station_def where id = '||v_stationid;
execute immediate v_s into v_stationSN,v_stationName;
dbms_output.put_line(v_stationSN);
dbms_output.put_line(v_stationName);
v_s := 'select count(*) from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||v_bd||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||v_ed||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is not null and '||v_colName||'!= -99999';
dbms_output.put_line(v_s);
execute immediate v_s into v_getRate;
v_getRateTotal := v_getRate;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is not null and '||v_colName||'!= -99999';
execute immediate v_s into v_getRate;
v_getRateTotal := v_getRateTotal+v_getRate;
end if;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is null';
execute immediate v_s into v_missBycorresponding;
v_missBycorrespondingTotal := v_missBycorresponding;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is null';
execute immediate v_s into v_missBycorresponding;
v_missBycorrespondingTotal := v_missBycorrespondingTotal+v_missBycorresponding;
end if;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||'= -99999';
execute immediate v_s into v_breakdownRate;
v_breakdownRateTotal := v_breakdownRate;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||'= -99999';
execute immediate v_s into v_breakdownRate;
v_breakdownRateTotal := v_breakdownRateTotal+v_breakdownRate;
end if;
insert into TEMP_DATAGETRATE values
(
v_stationid,
v_stationSN,
v_stationName,
'1CV',
v_getRateTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle),
v_missBycorrespondingTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle),
v_breakdownRateTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle)
);
v_s := 'select HisTableName, ColName, Recycle from t_item_mapping where stationid = '||v_stationid||' and code = ''46CV''';
execute immediate v_s into v_tableName,v_colName,v_recycle;
v_s := 'select Code,FullDesc from t_station_def where id = '||v_stationid;
execute immediate v_s into v_stationSN,v_stationName;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is not null and '||v_colName||'!= -99999';
execute immediate v_s into v_getRate;
v_getRateTotal := v_getRate;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is not null and '||v_colName||'!= -99999';
execute immediate v_s into v_getRate;
v_getRateTotal := v_getRateTotal+v_getRate;
end if;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is null';
execute immediate v_s into v_missBycorresponding;
v_missBycorrespondingTotal := v_missBycorresponding;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is null';
execute immediate v_s into v_missBycorresponding;
v_missBycorrespondingTotal := v_missBycorrespondingTotal+v_missBycorresponding;
end if;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||'= -99999';
execute immediate v_s into v_breakdownRate;
v_breakdownRateTotal := v_breakdownRate;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||'= -99999';
execute immediate v_s into v_breakdownRate;
v_breakdownRateTotal := v_breakdownRateTotal+v_breakdownRate;
end if;
insert into TEMP_DATAGETRATE values
(v_stationid,
v_stationSN,
v_stationName,
'46CV',
v_getRateTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle),
v_missBycorrespondingTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle),
v_breakdownRateTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle)
);
end if;
end loop;
OPEN getdatarate_cur;
LOOP
FETCH getdatarate_cur
INTO v_STATIONIDval, V_STATIONSNVAL, V_STATIONNAMEVAL, V_CODEVAL, V_GETRATEVAL, V_MISSINGBYCORRESPONDINGRATE, V_BREAKDOWNRATEVAL;
dbms_output.put_line(v_STATIONIDval);
EXIT WHEN getdatarate_cur%NOTFOUND;
END LOOP;
end proc_rpt_DataGetRate;
有点长,我主要是想去查询日期用。不知道哪里写的有问题
( stationids in nvarchar2,
startDate in varchar2,
endDate in varchar2
)
is
CURSOR getdatarate_cur IS
SELECT STATIONID, STATIONSN, STATIONNAME, CODE, GETRATE, MISSINGBYCORRESPONDINGRATE, BREAKDOWNRATE
FROM TEMP_DATAGETRATE;
v_stationid varchar2(10);--拆分后监测站ID
--v_begindate varchar2(10);
--v_enddate varchar2(10);
--v_bd varchar2(10);
--v_ed varchar2(10);
v_bd date;
v_ed date;
v_s VARCHAR2(1000);
v_tableName varchar2(20); --表名
v_colName varchar2(20);--列名
v_recycle number(10);
v_getRate int;--获取率
v_getRateTotal number(10);--获取率
v_missBycorresponding number(10);--通信丢失率
v_missBycorrespondingTotal number(10);--通信丢失率
v_breakdownRate number(10);--设备故障率
v_breakdownRateTotal number(10);--设备故障率
v_startyear VARCHAR2(4); --开始年
v_endyear VARCHAR2(4); --结束年
v_stationSN varchar2(10); --监测站编码
v_stationName varchar2(100); --监测站名称
v_stationids varchar2(100);
v_strat pls_integer := 1;
v_location number(10);
v_STATIONIDval INTEGER;
V_STATIONSNVAL VARCHAR2(10);
V_STATIONNAMEVAL VARCHAR2(10);
V_CODEVAL VARCHAR2(10);
V_GETRATEVAL NUMBER(10,3);
V_MISSINGBYCORRESPONDINGRATE NUMBER(10,3);
V_BREAKDOWNRATEVAL NUMBER(10,3);begin
dbms_output.put_line('startDate is '||startDate);
dbms_output.put_line('endDate is '||endDate);
v_stationids := ','||stationids||',';
v_location := instr(v_stationids,',',v_strat);
v_stationids := ','||stationids||',';
--v_begindate := to_date(startDate,'yyyy-mm-dd HH24:mi:ss');
--v_enddate := to_date(endDate,'yyyy-mm-dd HH24:mi:ss');
dbms_output.put_line('v_begindate is '||startDate);
dbms_output.put_line('v_enddate is '||endDate);
v_startyear := to_char(startDate,'yyyy');
v_endyear := to_char(endDate,'yyyy');
v_bd := to_date(startDate,'yyyy-mm-dd HH24:mi:ss');
v_ed := to_date(endDate,'yyyy-mm-dd HH24:mi:ss');
WHILE v_location > 0 LOOP
dbms_output.put_line(startDate);
dbms_output.put_line(endDate);
--dbms_output.put_line(v_begindate);
--dbms_output.put_line(v_enddate);
v_strat := v_location;
v_location := instr(v_stationids,',',v_strat+1);
if v_location>0 then
v_stationid := SUBSTR(v_stationids,v_strat+1,v_location-v_strat-1);
v_s := 'select HisTableName, ColName, Recycle from t_item_mapping where stationid = '||v_stationid||' and code = ''1CV''';
execute immediate v_s into v_tableName,v_colName,v_recycle;
dbms_output.put_line(v_tableName);
dbms_output.put_line(v_colName);
dbms_output.put_line(v_recycle);
v_s := 'select Code,FullDesc from t_station_def where id = '||v_stationid;
execute immediate v_s into v_stationSN,v_stationName;
dbms_output.put_line(v_stationSN);
dbms_output.put_line(v_stationName);
v_s := 'select count(*) from '||v_tableName||'_'||v_startyear||' where gettime between '||v_bd||'and '||v_ed||' and '||v_colName||' is not null and '||v_colName||'!= -99999';
dbms_output.put_line(v_s);
execute immediate v_s into v_getRate;
v_getRateTotal := v_getRate;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is not null and '||v_colName||'!= -99999';
execute immediate v_s into v_getRate;
v_getRateTotal := v_getRateTotal+v_getRate;
end if;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is null';
execute immediate v_s into v_missBycorresponding;
v_missBycorrespondingTotal := v_missBycorresponding;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is null';
execute immediate v_s into v_missBycorresponding;
v_missBycorrespondingTotal := v_missBycorrespondingTotal+v_missBycorresponding;
end if;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||'= -99999';
execute immediate v_s into v_breakdownRate;
v_breakdownRateTotal := v_breakdownRate;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||'= -99999';
execute immediate v_s into v_breakdownRate;
v_breakdownRateTotal := v_breakdownRateTotal+v_breakdownRate;
end if;
insert into TEMP_DATAGETRATE values
(
v_stationid,
v_stationSN,
v_stationName,
'1CV',
v_getRateTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle),
v_missBycorrespondingTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle),
v_breakdownRateTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle)
);
v_s := 'select HisTableName, ColName, Recycle from t_item_mapping where stationid = '||v_stationid||' and code = ''46CV''';
execute immediate v_s into v_tableName,v_colName,v_recycle;
v_s := 'select Code,FullDesc from t_station_def where id = '||v_stationid;
execute immediate v_s into v_stationSN,v_stationName;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is not null and '||v_colName||'!= -99999';
execute immediate v_s into v_getRate;
v_getRateTotal := v_getRate;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is not null and '||v_colName||'!= -99999';
execute immediate v_s into v_getRate;
v_getRateTotal := v_getRateTotal+v_getRate;
end if;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is null';
execute immediate v_s into v_missBycorresponding;
v_missBycorrespondingTotal := v_missBycorresponding;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||' is null';
execute immediate v_s into v_missBycorresponding;
v_missBycorrespondingTotal := v_missBycorrespondingTotal+v_missBycorresponding;
end if;
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_startyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||'= -99999';
execute immediate v_s into v_breakdownRate;
v_breakdownRateTotal := v_breakdownRate;
if v_startyear != v_endyear then
v_s := 'select count('||v_colName||') from '||v_tableName||'_'||v_endyear||' where gettime between to_date('''||startDate||''',''yyyy-mm-dd HH24:mi:ss'') and to_date('''||endDate||''',''yyyy-mm-dd HH24:mi:ss'') and '||v_colName||'= -99999';
execute immediate v_s into v_breakdownRate;
v_breakdownRateTotal := v_breakdownRateTotal+v_breakdownRate;
end if;
insert into TEMP_DATAGETRATE values
(v_stationid,
v_stationSN,
v_stationName,
'46CV',
v_getRateTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle),
v_missBycorrespondingTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle),
v_breakdownRateTotal/((to_date(startDate,'yyyy-mm-dd hh24:mi:ss')-to_date(endDate,'yyyy-mm-dd hh24:mi:ss'))*24*60*60/v_recycle)
);
end if;
end loop;
OPEN getdatarate_cur;
LOOP
FETCH getdatarate_cur
INTO v_STATIONIDval, V_STATIONSNVAL, V_STATIONNAMEVAL, V_CODEVAL, V_GETRATEVAL, V_MISSINGBYCORRESPONDINGRATE, V_BREAKDOWNRATEVAL;
dbms_output.put_line(v_STATIONIDval);
EXIT WHEN getdatarate_cur%NOTFOUND;
END LOOP;
end proc_rpt_DataGetRate;
你 select sysdate from dual;看下是什么格式
然后修改下 nls_date_format 格式
再试下 不一定有用 呵呵
对,有点智能的过头了。ORACLE的存储过程写起来真的挺麻烦,相对MSSQL的。
dbms_output.put_line(v_ed);
dbms_output.put_line(v_begindate);
dbms_output.put_line(v_enddate);
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'