Oracle PLSQL

要求:-如果表没有记录，则需要插入，如果已经结束日期列仅更新基于条款的年数 (例如，如果条款年为10，则10*12 = 120个月，意味着 ..。10年需要添加到结束日期col。

CREATE TABLE USA_ASC11
   (    POLICY_NO  NUMBER, 
 START_DATE DATE, 
 TERMS_OF_YEAR NUMBER, 
        frequ number,
  END_DATE DATE
   ) 


INSERT INTO USA_ASC11 VALUES(101, '2-5-2019' , 10 , 1, '22-5-2019' );
复制

当我执行下面的程序时，出现错误。请纠正并给我发送完美的程序。

create or replace procedure I_U_Ploicy
(
ip_start_date varchar2, ip_terms number, I_terms_of_year NUMBER
)
Is
v_pol number;
v_strtdate date;
v_terms number;
v_enddate Date;
p_frequ  number;
vnct number;
Begin
select POLICY_NO , START_DATE , TERMS_OF_YEAR  ,add_months(to_date(ip_start_date, 'DD-MM-YYYY'),ip_terms*12)-1 
as End_date 
INTO v_pol, v_strtdate, v_terms  , v_enddate FROM usa_asc11 WHERE FREQU=p_frequ;
if vnct =0 then
update usa_asc11
set end_date = v_enddate where POLICY_NO = v_pol;
else
insert into policy_p1(POLICY_NO , START_DATE , TERMS_OF_YEAR, End_date, FREQU)values
(1, '2-6-2019', 10, '2-6-2019' , 1);
commit;
End if;
Exception
When Others Then
Raise_Application_Error(-20000, 'There is no data to be update');
End;
复制

提前感谢布罗。

是的，我也有问题

SQL> create or replace procedure I_U_Ploicy
  2  (
  3  ip_start_date varchar2, ip_terms number, I_terms_of_year NUMBER
  4  )
  5  Is
  6  v_pol number;
  7  v_strtdate date;
  8  v_terms number;
  9  v_enddate Date;
 10  p_frequ  number;
 11  vnct number;
 12  Begin
 13  select POLICY_NO , START_DATE , TERMS_OF_YEAR  ,add_months(to_date(ip_start_date, 'DD-MM-YYYY'),ip_terms*12)-1
 14  as End_date
 15  INTO v_pol, v_strtdate, v_terms  , v_enddate FROM usa_asc11 WHERE FREQU=p_frequ;
 16  if vnct =0 then
 17  update usa_asc11
 18  set end_date = v_enddate where POLICY_NO = v_pol;
 19  else
 20  insert into policy_p1(POLICY_NO , START_DATE , TERMS_OF_YEAR, End_date, FREQU)values
 21  (1, '2-6-2019', 10, '2-6-2019' , 1);
 22  commit;
 23  End if;
 24  Exception
 25  When Others Then
 26  Raise_Application_Error(-20000, 'There is no data to be update');
 27  End;
 28  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE I_U_PLOICY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/1     PL/SQL: SQL Statement ignored
20/13    PL/SQL: ORA-00942: table or view does not exist
复制

测试用例...

:-(