oracle - 在 PL-SQL block 中声明和使用 PROCEDURE

Question

SET serveroutput ON;
DECLARE
PROCEDURE get_csa_type( pin_CVC_object_id     IN  NUMBER
                       ,posa_csa_type         OUT common.types.string
                       ,pona_permanent_csa    OUT common.types.string       
                      )
IS

     lc_csa_type common.types.string := 'SELECT UNIQUE csa.csa_type,csa.permanent_csa '||
    'FROM ems.ibo_sm_cvc_rfs cvc' ||
    ',ems.ibo_nbn_csa csa ' ||   
    'WHERE cvc.object_id = :1 ' ||
    'AND csa.csa_id = cvc.csa_id';
BEGIN    


    EXECUTE IMMEDIATE lc_csa_type BULK COLLECT INTO posa_csa_type, pona_permanent_csa
    USING pin_CVC_object_id;

END;
ls_csa_type common.types.string;
ls_permanent_csa common.types.string;
BEGIN
get_csa_type(pin_CVC_object_id => 8581213
            ,posa_csa_type => ls_csa_type
            ,pona_permanent_csa => ls_permanent_csa);
 dbms_output.put_line(ls_csa_type || ls_permanent_csa); 

END;

我可以在 pl-sql block 中声明然后调用上述过程吗？当我尝试运行它时，出现了几个错误...

Encountered the symbol "LS_CSA_TYPE" when expecting one of the following:

begin function pragma procedure

Encountered the symbol "DBMS_OUTPUT" when expecting one of the following:

:= . ( % ;

Answer 1

您需要在子过程之前的开头移动变量声明。下面的类似代码对我有用:

DECLARE
  ls_csa_type varchar2(4000);
  ls_permanent_csa varchar2(4000);

  PROCEDURE get_csa_type( pin_CVC_object_id     IN  NUMBER
                         ,posa_csa_type         OUT varchar2
                         ,pona_permanent_csa    OUT varchar2 ) IS
      lc_csa_type varchar2(4000) := 'SELECT dummy, dummy from dual where 1 = :1 ';
  BEGIN    
      EXECUTE IMMEDIATE lc_csa_type 
      INTO posa_csa_type, pona_permanent_csa
      USING pin_CVC_object_id;
  END get_csa_type;

BEGIN
  get_csa_type(pin_CVC_object_id => 1
              ,posa_csa_type => ls_csa_type
              ,pona_permanent_csa => ls_permanent_csa);
  dbms_output.put_line(ls_csa_type || ls_permanent_csa); 
END;