CREATE OR REPLACE PROCEDURE getCalMYGP(date1 varchar,date2 varchar,number1 int)
is
mylgzgsl int:=0;
tempCount int:=0;
TYPE TCUR IS REF CURSOR;
CUR TCUR;
tNSRDZDAH NUMBER(20);
tFPZL_DM VARCHAR2(5 BYTE);
tFP_GPFS_DM CHAR(1 BYTE);
tMYGPZGSL NUMBER(10);
tLGSL NUMBER(10);
tZDFPJE NUMBER(16,2);
tPZRQ DATE;
tYXBZ CHAR(1 BYTE);
tFP_YJJG_DM CHAR(2 BYTE);
tZDGPSL NUMBER(10);
tZGGPSL NUMBER(10);
tZDFPJE_DM NUMBER(22, 2);
tBL NUMBER(10);
tYXQ_Q DATE;
tYXQ_Z DATE;
dateA date := TO_DATE(date1, 'YYYY-MM-DD');
dateB date := TO_DATE(date2, 'YYYY-MM-DD');
BEGIN
OPEN CUR FOR select pz.NSRDZDAH,pz.FPZL_DM,pz.FP_GPFS_DM,pz.MYGPZGSL,pz.LGSL,pz.ZDFPJE,pz.PZRQ,pz.YXBZ,yj.FP_YJJG_DM,my.ZDGPSL,my.ZGGPSL,my.ZDFPJE_DM,my.BL,my.YXQ_Q,my.YXQ_Z
from FP_PZ pz inner join MYGPZGSL_CS my on my.FPZL_DM=pz.FPZL_DM inner join DJ_NSRXX nsrxx on pz.NSRDZDAH=nsrxx.NSRDZDAH inner join FP_YJ yj on yj.NSRDZDAH=pz.NSRDZDAH where pz.PZRQ>=dateA and pz.PZRQ<=dateB;
LOOP
FETCH CUR INTO tNSRDZDAH,tFPZL_DM,tFP_GPFS_DM,tMYGPZGSL,tLGSL,tZDFPJE,tPZRQ,tYXBZ,tFP_YJJG_DM,tZDGPSL,tZGGPSL,tZDFPJE_DM,tBL,tYXQ_Q,tYXQ_Z;
EXIT WHEN CUR%NOTFOUND;
select sum(fp.fs) into tempCount from FP_YJ fp where fp.FPZL_DM=tFPZL_DM AND fp.RQ>=to_date(tYXQ_Q,'YYYY-MM-DD') and fp.RQ<=to_date(tYXQ_Z,'YYYY-MM-DD');
if (((tZDGPSL<tMYGPZGSL) and (tMYGPZGSL<=tZGGPSL)) and (tZDFPJE>=tZDFPJE_DM) and (tMYGPZGSL*(1 + tBL)<tempCount/number1)) then
mylgzgsl:=tempCount/number1;
else
mylgzgsl:=tMYGPZGSL;
end if;
tempCount:=0;
select count(*) into tempCount from SHJK_MYGPZGSL sh where sh.NSRDZDAH=tNSRDZDAH and sh.FPZL_DM=tFPZL_DM and sh.YXQ_Q= to_date(tYXQ_Q,'YYYY-MM-DD') AND sh.YXQ_Z= to_date(tYXQ_Z,'YYYY-MM-DD');
if (tempCount= 0) then
insert into SHJK_MYGPZGSL(NSRDZDAH,FPZL_DM,FP_YJJG_DM,MYGPZGSL_Y,MYGPZGSL_X,YXQ_Q,YXQ_Z,PZXX_DM) values(tNSRDZDAH,tFPZL_DM,tFP_YJJG_DM,tMYGPZGSL,mylgzgsl,to_date(tYXQ_Q,'YYYY-MM-DD'),to_date(tYXQ_Z,'YYYY-MM-DD'),'00');
else
update SHJK_MYGPZGSL set MYGPZGSL_Y=tMYGPZGSL,FP_YJJG_DM=tFP_YJJG_DM,MYGPZGSL_X=mylgzgsl,PZXX_DM='11' where NSRDZDAH=tNSRDZDAH and PZXX_DM='00' and FPZL_DM=tFPZL_DM and YXQ_Q=to_date(tYXQ_Q,'YYYY-MM-DD') AND YXQ_Z=to_date(tYXQ_Z,'YYYY-MM-DD');
end if ;
END LOOP;
CLOSE CUR;
end getCalMYGP;以上存储过程, Database 日期格式为 DD-MM-YY 但存储过程插入的时候 用DD-MM-YY格式就插入不了 数据,只能插入YYYY-MM-DD,所以很困惑,请各位帮忙,谢谢!
is
mylgzgsl int:=0;
tempCount int:=0;
TYPE TCUR IS REF CURSOR;
CUR TCUR;
tNSRDZDAH NUMBER(20);
tFPZL_DM VARCHAR2(5 BYTE);
tFP_GPFS_DM CHAR(1 BYTE);
tMYGPZGSL NUMBER(10);
tLGSL NUMBER(10);
tZDFPJE NUMBER(16,2);
tPZRQ DATE;
tYXBZ CHAR(1 BYTE);
tFP_YJJG_DM CHAR(2 BYTE);
tZDGPSL NUMBER(10);
tZGGPSL NUMBER(10);
tZDFPJE_DM NUMBER(22, 2);
tBL NUMBER(10);
tYXQ_Q DATE;
tYXQ_Z DATE;
dateA date := TO_DATE(date1, 'YYYY-MM-DD');
dateB date := TO_DATE(date2, 'YYYY-MM-DD');
BEGIN
OPEN CUR FOR select pz.NSRDZDAH,pz.FPZL_DM,pz.FP_GPFS_DM,pz.MYGPZGSL,pz.LGSL,pz.ZDFPJE,pz.PZRQ,pz.YXBZ,yj.FP_YJJG_DM,my.ZDGPSL,my.ZGGPSL,my.ZDFPJE_DM,my.BL,my.YXQ_Q,my.YXQ_Z
from FP_PZ pz inner join MYGPZGSL_CS my on my.FPZL_DM=pz.FPZL_DM inner join DJ_NSRXX nsrxx on pz.NSRDZDAH=nsrxx.NSRDZDAH inner join FP_YJ yj on yj.NSRDZDAH=pz.NSRDZDAH where pz.PZRQ>=dateA and pz.PZRQ<=dateB;
LOOP
FETCH CUR INTO tNSRDZDAH,tFPZL_DM,tFP_GPFS_DM,tMYGPZGSL,tLGSL,tZDFPJE,tPZRQ,tYXBZ,tFP_YJJG_DM,tZDGPSL,tZGGPSL,tZDFPJE_DM,tBL,tYXQ_Q,tYXQ_Z;
EXIT WHEN CUR%NOTFOUND;
select sum(fp.fs) into tempCount from FP_YJ fp where fp.FPZL_DM=tFPZL_DM AND fp.RQ>=to_date(tYXQ_Q,'YYYY-MM-DD') and fp.RQ<=to_date(tYXQ_Z,'YYYY-MM-DD');
if (((tZDGPSL<tMYGPZGSL) and (tMYGPZGSL<=tZGGPSL)) and (tZDFPJE>=tZDFPJE_DM) and (tMYGPZGSL*(1 + tBL)<tempCount/number1)) then
mylgzgsl:=tempCount/number1;
else
mylgzgsl:=tMYGPZGSL;
end if;
tempCount:=0;
select count(*) into tempCount from SHJK_MYGPZGSL sh where sh.NSRDZDAH=tNSRDZDAH and sh.FPZL_DM=tFPZL_DM and sh.YXQ_Q= to_date(tYXQ_Q,'YYYY-MM-DD') AND sh.YXQ_Z= to_date(tYXQ_Z,'YYYY-MM-DD');
if (tempCount= 0) then
insert into SHJK_MYGPZGSL(NSRDZDAH,FPZL_DM,FP_YJJG_DM,MYGPZGSL_Y,MYGPZGSL_X,YXQ_Q,YXQ_Z,PZXX_DM) values(tNSRDZDAH,tFPZL_DM,tFP_YJJG_DM,tMYGPZGSL,mylgzgsl,to_date(tYXQ_Q,'YYYY-MM-DD'),to_date(tYXQ_Z,'YYYY-MM-DD'),'00');
else
update SHJK_MYGPZGSL set MYGPZGSL_Y=tMYGPZGSL,FP_YJJG_DM=tFP_YJJG_DM,MYGPZGSL_X=mylgzgsl,PZXX_DM='11' where NSRDZDAH=tNSRDZDAH and PZXX_DM='00' and FPZL_DM=tFPZL_DM and YXQ_Q=to_date(tYXQ_Q,'YYYY-MM-DD') AND YXQ_Z=to_date(tYXQ_Z,'YYYY-MM-DD');
end if ;
END LOOP;
CLOSE CUR;
end getCalMYGP;以上存储过程, Database 日期格式为 DD-MM-YY 但存储过程插入的时候 用DD-MM-YY格式就插入不了 数据,只能插入YYYY-MM-DD,所以很困惑,请各位帮忙,谢谢!
解决方案 »
- 关于oracle的备份
- oracle 批量更新数据 月份中日的值必须介于 1 和当月最后一日之间
- SQLLDR 手动打命令可以,通过程序不行,救命,谢谢!
- 一条我最想知道的SQL语句 ^o^
- oracle+mail+ORA-29279错误提示
- 建表正常了,为什么插入数据的时候出错?
- ORACLE有一分跟MSSQL详细帮助或者函数库什么的吗,有的话在哪
- 客户端程序运行过程中出现ORA-03113错误??有相关日志记录。高手看看!
- 服务器掉电后oracle9i for win2000数据库不能启动???
- 如何查看数据库中某数据是否被锁?谢谢
- oracle这样的表样给他排序呀?
- 根据主键判定是insert还是update的问题
可能date会根据默认格式转变为字符。你可以做做测试。在进入sqlplus前,先命令行执行
set nls_date_format=DD-MM-YY效果就不一样了。
就没有问题 , 可是在存储过程中就只能使用YYYY-MM-DD还是不明白
dateB date := TO_DATE(date2, 'YYYY-MM-DD'); 这两句如果不用to_date?
就直接用 date1,然后Debug看它是什么数据类型?
不知道是否理解了你的意思,你要的是下面的存储过程吗?
CREATE OR REPLACE PROCEDURE getCalMYGP(date1 varchar,date2 varchar,number1 int)
is
mylgzgsl int:=0;
tempCount int:=0;
TYPE TCUR IS REF CURSOR;
CUR TCUR;
tNSRDZDAH NUMBER(20);
tFPZL_DM VARCHAR2(5 BYTE);
tFP_GPFS_DM CHAR(1 BYTE);
tMYGPZGSL NUMBER(10);
tLGSL NUMBER(10);
tZDFPJE NUMBER(16,2);
tPZRQ DATE;
tYXBZ CHAR(1 BYTE);
tFP_YJJG_DM CHAR(2 BYTE);
tZDGPSL NUMBER(10);
tZGGPSL NUMBER(10);
tZDFPJE_DM NUMBER(22, 2);
tBL NUMBER(10);
tYXQ_Q DATE;
tYXQ_Z DATE;
dateA date := TO_DATE(date1, 'DD-MM-YY');
dateB date := TO_DATE(date2, 'DD-MM-YY');
BEGIN
OPEN CUR FOR select pz.NSRDZDAH,pz.FPZL_DM,pz.FP_GPFS_DM,pz.MYGPZGSL,pz.LGSL,pz.ZDFPJE,pz.PZRQ,pz.YXBZ,yj.FP_YJJG_DM,my.ZDGPSL,my.ZGGPSL,my.ZDFPJE_DM,my.BL,my.YXQ_Q,my.YXQ_Z
from FP_PZ pz inner join MYGPZGSL_CS my on my.FPZL_DM=pz.FPZL_DM inner join DJ_NSRXX nsrxx on pz.NSRDZDAH=nsrxx.NSRDZDAH inner join FP_YJ yj on yj.NSRDZDAH=pz.NSRDZDAH where pz.PZRQ>=dateA and pz.PZRQ <=dateB;
LOOP
FETCH CUR INTO tNSRDZDAH,tFPZL_DM,tFP_GPFS_DM,tMYGPZGSL,tLGSL,tZDFPJE,tPZRQ,tYXBZ,tFP_YJJG_DM,tZDGPSL,tZGGPSL,tZDFPJE_DM,tBL,tYXQ_Q,tYXQ_Z;
EXIT WHEN CUR%NOTFOUND;
select sum(fp.fs) into tempCount from FP_YJ fp where fp.FPZL_DM=tFPZL_DM AND fp.RQ>=tYXQ_Q and fp.RQ <=tYXQ_Z;
if (((tZDGPSL <tMYGPZGSL) and (tMYGPZGSL <=tZGGPSL)) and (tZDFPJE>=tZDFPJE_DM) and (tMYGPZGSL*(1 + tBL) <tempCount/number1)) then
mylgzgsl:=tempCount/number1;
else
mylgzgsl:=tMYGPZGSL;
end if;
tempCount:=0;
select count(*) into tempCount from SHJK_MYGPZGSL sh where sh.NSRDZDAH=tNSRDZDAH and sh.FPZL_DM=tFPZL_DM and sh.YXQ_Q= tYXQ_Q AND sh.YXQ_Z= tYXQ_Z;
if (tempCount= 0) then
insert into SHJK_MYGPZGSL(NSRDZDAH,FPZL_DM,FP_YJJG_DM,MYGPZGSL_Y,MYGPZGSL_X,YXQ_Q,YXQ_Z,PZXX_DM) values(tNSRDZDAH,tFPZL_DM,tFP_YJJG_DM,tMYGPZGSL,mylgzgsl,tYXQ_Q,tYXQ_Z,'00');
else
update SHJK_MYGPZGSL set MYGPZGSL_Y=tMYGPZGSL,FP_YJJG_DM=tFP_YJJG_DM,MYGPZGSL_X=mylgzgsl,PZXX_DM='11' where NSRDZDAH=tNSRDZDAH and PZXX_DM='00' and FPZL_DM=tFPZL_DM and YXQ_Q=tYXQ_Q AND YXQ_Z=tYXQ_Z;
end if ;
END LOOP;
CLOSE CUR;
end getCalMYGP;
2.MYGPZGSL_CS.YXQ_Q,MYGPZGSL_CS.YXQ_Z是否为Date类型
3.其他表中的YXQ_Q,YXQ_Z是否都是Date类型
dateA date := TO_DATE(date1, 'DD-MM-YY');
dateB date := TO_DATE(date2, 'DD-MM-YY');
所以调用的时候传入的参数就应该是'DD-MM-YY'格式的字符串了