编译此函数时收到以下错误:

Compilation errors for PROCEDURE INV.USP_MSC_MODIFICA_ESTADO

Error: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:( - + case mod new not null <an identifier> 
<a double-quoted delimited-identifier> <a bind variable> 
 continue avg count current exists max min prior sql stddev 
sum variance execute forall merge time timestamp interval 
date <a string literal with character set specification> 
<a number> <a single-quoted SQL string> pipe 
<an alternatively-quoted string literal with character set specification> 
<an alternat    Line: 14    Text: IF SELECT TRUNC((SYSDATE) -TO_DATE(@FCH_GRABACION, 'DD/MM/YYYY HH24:MI:SS')) From DUAL=1 THEN 

CREATE OR REPLACE PROCEDURE "USP_MSC_MODIFICA_ESTADO" AS 
  BEGIN 
    DECLARE 
      CURSOR reservar IS 
      SELECT 
        id_reserva, 
        fch_grabacion  
       FROM tb_msc_reserva 
      WHERE to_date(to_char(fch_grabacion, 'dd/mm/yyyy')) = to_date(to_char(SYSDATE, 'dd/mm/yyyy')) - 1; 
      id_reserva    VARCHAR2(50); 
      fch_grabacion DATE; 
    BEGIN 
      OPEN reservar; 
      FETCH reservar INTO id_reserva, fch_grabacion; 
 
      IF SELECT TRUNC((SYSDATE) - TO_DATE(@fch_grabacion, 'DD/MM/YYYY HH24:MI:SS')) FROM dual=1 THEN 
 
      UPDATE inv.tb_msc_reserva t 
      SET t.flg_estado = 'C' 
      WHERE id_reserva = @id_reserva; 
      COMMIT; 
    END if; 
    WHILE (@@fetch_status = 0) 
 
    CLOSE RESERVAR; 
 
  END; 

请您参考如下方法:

坦率地说代码中有太多错误。不能全部写下来。
几个错误:

  • 不需要在第4行声明。
  • 太多Begin语句。
  • While(@@ FETCH_STATUS = 0)不用于循环终止。使用退出时间。
  • 错过了循环的开始和结束循环[假设您在使用游标而不是Select Into时正在使用Loop]
    CREATE OR REPLACE PROCEDURE "USP_MSC_MODIFICA_ESTADO" 
    AS 
    CURSOR reservar 
    IS 
    SELECT id_reserva, fch_grabacion 
    FROM tb_msc_reserva 
    WHERE TO_DATE (TO_CHAR (fch_grabacion, 'dd/mm/yyyy')) = 
    TO_DATE (TO_CHAR (SYSDATE, 'dd/mm/yyyy')) 
    - 1; 
     
    id_reserva      VARCHAR2 (50); 
    fch_grabacion   DATE; 
    BEGIN 
    OPEN reservar; 
     
    FETCH reservar 
    INTO id_reserva, fch_grabacion; 
     
    LOOP 
    IF (SELECT TRUNC (  (SYSDATE) - TO_DATE (@fch_grabacion, 'DD/MM/YYYY HH24:MI:SS')) 
    FROM DUAL) = 1 
    THEN 
    UPDATE inv.tb_msc_reserva t 
    SET t.flg_estado = 'C' 
    WHERE id_reserva = @id_reserva; 
     
    COMMIT; 
    END IF; 
     
    EXIT WHEN (@@fetch_status = 0); 
    END LOOP; 
     
    CLOSE reservar; 
    END; 
    

  • 评论关闭
    IT序号网

    微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!