log返回一个有趣函数的结果

Question

我有一个函数可以返回定期储蓄的金额，但在我的代码中，系统会返回一个错误。

CREATE OR REPLACE FUNCTION moneysaving 
RETURN number 
IS
moineysaving_p number(10);  
BEGIN
select  customer_id , case  
when periods_id = "TK12" then
case 
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =12
then TRUNC(money *(1+0.063/365*365))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >12 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <24 
then TRUNC((money *(1+0.063/365*365))+(money *(1+0.003/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=24
then TRUNC((money *(1+0.063/365*365))+(money *(1+0.06/365*365)))
end 
when   periods_id = "TK6" then 
case
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =6
then TRUNC(money *(1+0.054/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >6 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <12
then TRUNC((money *(1+0.054/365*365))+(money *(1+0.3/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=12
then TRUNC((money *(1+0.054/365*365))+(money *(1+0.054/365*365)))
end
when   periods_id = "TK3" then 
case
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =3
then TRUNC(money *(1+0.05/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >3|| TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <6
then TRUNC(money *(1+0.05/365*365))+(money *(1+0.003/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=6
then TRUNC(money *(1+0.05/365*365))+(money *(1+0.05/365*365)))
end
when   periods_id = "TK1" then
case
when  TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =1
then TRUNC(money *(1+0.045/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >1 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <3
then TRUNC(money *(1+0.045/365*365))+money *(1+0.003/365*365)))
when  TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=3
then TRUNC(money *(1+0.045/365*365))+money *(1+0.045/365*365)))
end 
else TRUNC(money *(1+0.003/365*365))
end 
from saving ;
RETURN moineysaving_p;
END;

你在银行存了一些注册期限的钱，你来按期限取钱，并计算你在该期限内存款时收到的金额，ora-00695，ora-001765

Answer 1

ORA-01756：带引号的字符串未正确终止。

使用单引号"而不是双引号"&"；。

CREATE OR REPLACE FUNCTION moneysaving 
RETURN number 
IS
moineysaving_p number(10);  
BEGIN
select  customer_id , case  
when periods_id = 'TK12' then
case 
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =12
then TRUNC(money *(1+0.063/365*365))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >12 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <24 
then TRUNC((money *(1+0.063/365*365))+(money *(1+0.003/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=24
then TRUNC((money *(1+0.063/365*365))+(money *(1+0.06/365*365)))
end 
when   periods_id = 'TK6' then 
case
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =6
then TRUNC(money *(1+0.054/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >6 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <12
then TRUNC((money *(1+0.054/365*365))+(money *(1+0.3/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=12
then TRUNC((money *(1+0.054/365*365))+(money *(1+0.054/365*365)))
end
when   periods_id = 'TK3' then 
case
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =3
then TRUNC(money *(1+0.05/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >3|| TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <6
then TRUNC(money *(1+0.05/365*365))+(money *(1+0.003/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=6
then TRUNC(money *(1+0.05/365*365))+(money *(1+0.05/365*365)))
end
when   periods_id = 'TK1' then
case
when  TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =1
then TRUNC(money *(1+0.045/365*365)))
when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >1 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <3
then TRUNC(money *(1+0.045/365*365))+money *(1+0.003/365*365)))
when  TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=3
then TRUNC(money *(1+0.045/365*365))+money *(1+0.045/365*365)))
end 
else TRUNC(money *(1+0.003/365*365))
end 
from saving ;
RETURN moineysaving_p;
END;