我得到错误：PLS-00103：在使用过程oracle中遇到符号“INSERT”

pod7payv 于 2023-03-17 发布在 Oracle

关注(0)|答案(1)|浏览(217)

我是甲骨文的新手，当我写这段代码时，我得到了错误：PLS-00103 .请帮帮我我的代码是：

CREATE OR REPLACE PROCEDURE LCR_REP_PROFILE_DETAILE
  (VAR_NAME     IN VARCHAR2,    
   VRA_EFF_DATE IN DATE)              
AS
  VAR_FORMULA VARCHAR2(4000);
  VAR_BALANCE NUMBER ;
BEGIN
  FOR I IN 1 .. 5 LOOP
    SELECT QRY, BALANCE   
    INTO VAR_FORMULA, VAR_BALANCE   
    FROM  TBL_LCR_QRY TLQ                 
    WHERE NAME = VAR_NAME              
      AND ID= i 
      AND EFF_DATE = VAR_EFF_DATE;

    
    EXECUTE IMMEDIATE VAR_BALANCE INTO VAR_FORMULA ;  
    INSERT INTO TBL_LCR_PROFILE_DETAILE(ID,NAME,FORMULA,EFF_DATE,BALANCE )
    SELECT CODE,VAR_NAME,VAR_QRY,VAR_EFF_DATE,VAR_BALANCE    
    FROM DUAL ;
    COMMIT;

  END LOOP ;
END;

我的错误是：

9/5       PL/SQL: SQL Statement ignored
14/22     PL/SQL: ORA-00904: "VAR_EFF_DATE": invalid identifier
17/5      PL/SQL: Statement ignored
17/23     PLS-00382: expression is of wrong type
18/5      PL/SQL: SQL Statement ignored
19/34     PL/SQL: ORA-00904: "VAR_EFF_DATE": invalid identifier

请帮帮我。
我想修正我的错误

oracle

来源：https://stackoverflow.com/questions/75703941/i-get-error-pls-00103-encountered-the-symbol-insert-in-use-procedure-oracle

1条答案

按热度按时间

cclgggtu1#

一堆错误。请看我在代码中写的注解。首先修复它，然后看看它是否工作。我无法运行它来验证，我没有你的表或数据，而且你也没有提供任何数据。

CREATE OR REPLACE PROCEDURE LCR_REP_PROFILE_DETAILE
  (VAR_NAME     IN VARCHAR2(4000),    --> no size here!
   VRA_EFF_DATE IN DATE)              --> VRA? Did you mean VAR?
AS
  VAR_QRY VARCHAR2(4000);
  VAR_BALANCE NUMBER ;
BEGIN
  FOR I IN 1 .. 5 LOOP
    SELECT NAME, QRY, BALANCE   
    INTO VAR_NAME, VAR_QRY, VAR_BALANCE   --> you can't select into VAR_NAME, it is IN parameter
    FROM  TBL_LCR_QRY TLQ                 
    WHERE NAME = 'VAR_NAME'               --> remove single quotes   
      AND ID= i 
      AND VAR_EFF_DATE = EFF_DATE;

    --DBMS_OUTPUT.PUT_LINE(VAR_QRY);

    -- what is this supposed to do? Should've probably been
    -- EXECUTE IMMEDIATE VAR_QRY INTO VAR_BALANCE
    EXECUTE IMMEDIATE BALANCE, QRY INTO VAR_BALANCE, VAR_QRY ;  

    --DBMS_OUTPUT.PUT_LINE(VAR_NAME ||'     '||VAR_BALANCE);

    INSERT INTO TBL_LCR_PROFILE_DETAILE(ID,NAME,FORMULA,EFF_DATE,BALANCE )
    SELECT CODE,VAR_NAME,VAR_QRY,VAR_EFF_DATE,VAR_BALANCE    --> this can't work; CODE was never declared
    FROM DUAL ;
    COMMIT;

  END LOOP ;
END;

赞(0）回复(0）举报 2023-03-17

我来回答

我得到错误：PLS-00103：在使用过程oracle中遇到符号“INSERT”

1条答案

相关问题

热门标签

最新问答