oracle - 过程 : invalid identifier 中 UPDATE oracle 中的变量

Question

我不明白为什么服务会提示Fehler(36,11):PL/SQL:ORA-00904:“FOUND_VP”:无效标识符

变量在第一次开始时声明...是否不能在查询中直接使用变量？

尝试存储以下过程时:

create or replace PROCEDURE fpwl_update_vp(
     my_zn IN NUMBER, my_verwaltung IN VARCHAR2 , my_variante IN NUMBER, my_vp IN NUMBER 
   ) IS



BEGIN

DECLARE
search_VP IFT_INFO_LAUF.VP%TYPE;
found_VP IFT_INFO_LAUF.VP%TYPE;
INFOversion number := 25;


BEGIN -- search SYFA_VP

          SELECT SYFA_VP
          INTO found_VP
          FROM FPWL_VP_MAPPING
          WHERE INFO_VP=search_VP ;


          exception
          when no_data_found then
           dbms_output.put_line ('Kein SYFA VP : Importiere aus SYFA');
          --found_VP:=:=cus_info25.pa_info_data.fn_insert_syfa_vp(my_vp,25);

          WHEN OTHERS THEN
          ROLLBACK;
          RETURN;
END; -- SYFA VP


-- Update VP
UPDATE IFT_INFO_LAUF
 SET vp = found_VP
WHERE id_kopf IN 
  (SELECT id_kopf 
    FROM ift_info_kopf 
    WHERE fahrtnummer= my_zn  
    AND verwaltung= my_verwaltung
    AND variante = my_variante
  )
;


  --COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;

END ;

Answer 1

您的问题是found_VP超出了范围。

将“DECLARE” block 的内容移至“IS”之后:

create or replace PROCEDURE fpwl_update_vp(
 my_zn IN NUMBER, my_verwaltung IN VARCHAR2 , my_variante IN NUMBER, my_vp IN NUMBER 
) IS
  search_VP IFT_INFO_LAUF.VP%TYPE;
  found_VP IFT_INFO_LAUF.VP%TYPE;
  INFOversion number := 25;
BEGIN
  BEGIN -- search SYFA_VP

  etc