oracle - 日志返回一个有趣的函数的结果

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

标签： oracle

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;