oracle 将 TABLE 类型作为参数传递给 PLSQL 过程

Passing TABLE type as parameter to PLSQL Procedure

提问by Bender

I have a plsql block like so that basically sets bunch of flags in a collection to run procedures in a package:

我有一个像这样的 plsql 块，它基本上在集合中设置一堆标志来运行包中的过程：

declare

  TYPE tRunFlagRec IS RECORD(tRunFlag BOOLEAN);
  TYPE tRunFlagTab IS TABLE OF tRunFlagRec INDEX BY VARCHAR2(64);
  vValidationsTab tRunFlagTab;

begin

  vValidationsTab('some_validation_1').tRunFlag := true;
  vValidationsTab('some_validation_2').tRunFlag := true;
  vValidationsTab('some_validation_3').tRunFlag := true;
  vValidationsTab('some_validation_4').tRunFlag := true;

  owner.validation_pkg.main(pRunFlags => vValidationsTab);

end;

And a procedure with some if statements that calls other procedures based on the flags it receives:

还有一个带有一些 if 语句的过程，它根据接收到的标志调用其他过程：

  --declared in package
  TYPE tRunFlagRec IS RECORD(tRunFlag BOOLEAN);
  TYPE tRunFlagTab IS TABLE OF tRunFlagRec INDEX BY VARCHAR2(64);
  vValidationsTab tRunFlagTab;

PROCEDURE MAIN(pRunFlags in tRunFlagTab) IS

    vSome_validation_1_FLAG  BOOLEAN := pRunFlags('FLAG_1').tRunFlag;
    vSome_validation_2_FLAG  BOOLEAN := pRunFlags('FLAG_2').tRunFlag;
    vSome_validation_3_FLAG  BOOLEAN := pRunFlags('FLAG_3').tRunFlag;
    vSome_validation_4_FLAG  BOOLEAN := pRunFlags('FLAG_4').tRunFlag;

  BEGIN
    DBMS_OUTPUT.PUT_LINE('Checking for 1');
    IF vSome_validation_1_FLAG THEN
      GET_SOME_VALIDATION_1();
    ELSE
      DBMS_OUTPUT.PUT_LINE('Run Flag set to False. Skipping...');
    END IF;
    DBMS_OUTPUT.PUT_LINE('Checking for 2');
    IF vSome_validation_2_FLAG THEN
      GET_SOME_VALIDATION_2();
    ELSE
      DBMS_OUTPUT.PUT_LINE('Run Flag set to False. Skipping...');
    END IF;
    DBMS_OUTPUT.PUT_LINE('Checking for 3');
    IF vSome_validation_3_FLAG THEN
      GET_SOME_VALIDATION_3();
    ELSE
      DBMS_OUTPUT.PUT_LINE('Run Flag set to False. Skipping...');
    END IF;
    DBMS_OUTPUT.PUT_LINE('Checking for 4');
    IF vSome_validation_4_FLAG THEN
      GET_SOME_VALIDATION_4();
    ELSE
      DBMS_OUTPUT.PUT_LINE('Run Flag set to False. Skipping...');
    END IF;
  END;

I'm getting the error: PLS-00306: wrong number or types of arguments in call to 'MAIN'. Any ideas?

我收到错误：PLS-00306：调用“MAIN”时的参数数量或类型错误。有任何想法吗？

回答by Justin Cave

Don't redeclare packaged types in local code. The tRunFlagRecand tRunFlagTabtypes that you declare in your anonymous PL/SQL blocks are different than the types of similar names that are declared in the package so you can't use them interchangeably. Just use the packaged types in your anonymous PL/SQL block

不要在本地代码中重新声明打包类型。您在匿名 PL/SQL 块中声明的tRunFlagRec和tRunFlagTab类型与在包中声明的相似名称的类型不同，因此您不能互换使用它们。只需在匿名 PL/SQL 块中使用打包类型

declare
  vValidationsTab owner.validation_pkg.tRunFlagTab;
begin
  vValidationsTab('some_validation_1').tRunFlag := true;
  vValidationsTab('some_validation_2').tRunFlag := true;
  vValidationsTab('some_validation_3').tRunFlag := true;
  vValidationsTab('some_validation_4').tRunFlag := true;

  owner.validation_pkg.main(pRunFlags => vValidationsTab);
end;

回答by Sandeep

Step 1 - Create Package so that Declaration can be consistent for both caller and being called

第 1 步 - 创建包，以便调用者和被调用者的声明可以保持一致

CREATE OR REPLACE PACKAGE declare_here
IS
   TYPE trunflagrec IS RECORD (
      trunflag   BOOLEAN
   );

   TYPE trunflagtab IS TABLE OF trunflagrec
      INDEX BY VARCHAR2 (64);
END;

Step 2 - This is where you are putting your logic, I have commented the some lines, but you can un-comment them in your code, they are straight forward

第 2 步 - 这是您放置逻辑的地方，我已经注释了一些行，但是您可以在代码中取消注释它们，它们很简单

CREATE OR REPLACE PROCEDURE main (prunflags IN declare_here.trunflagtab)
IS
   vsome_validation_1_flag   BOOLEAN := prunflags ('some_validation_1').trunflag;
   vsome_validation_2_flag   BOOLEAN := prunflags ('some_validation_2').trunflag;
   vsome_validation_3_flag   BOOLEAN := prunflags ('some_validation_3').trunflag;
   vsome_validation_4_flag   BOOLEAN := prunflags ('some_validation_4').trunflag;
BEGIN
   DBMS_OUTPUT.put_line ('Checking for 1');

   IF vsome_validation_1_flag
   THEN
      --get_some_validation_1 ();
      DBMS_OUTPUT.put_line ('get_some_validation_1');
   ELSE
      DBMS_OUTPUT.put_line ('Run Flag set to False. Skipping...');
   END IF;

   DBMS_OUTPUT.put_line ('Checking for 2');

   IF vsome_validation_2_flag
   THEN
      --get_some_validation_2 ();
      DBMS_OUTPUT.put_line ('get_some_validation_2');
   ELSE
      DBMS_OUTPUT.put_line ('Run Flag set to False. Skipping...');
   END IF;

   DBMS_OUTPUT.put_line ('Checking for 3');

   IF vsome_validation_3_flag
   THEN
      --get_some_validation_3 ();
      DBMS_OUTPUT.put_line ('get_some_validation_3');
   ELSE
      DBMS_OUTPUT.put_line ('Run Flag set to False. Skipping...');
   END IF;

   DBMS_OUTPUT.put_line ('Checking for 4');

   IF vsome_validation_4_flag
   THEN
      --get_some_validation_4 ();
      DBMS_OUTPUT.put_line ('get_some_validation_4');
   ELSE
      DBMS_OUTPUT.put_line ('Run Flag set to False. Skipping...');
   END IF;
END;

Step 3 - Call the Procedure Created in Step 2

步骤 3 - 调用在步骤 2 中创建的过程

DECLARE
   vvalidationstab   declare_here.trunflagtab;
BEGIN
   vvalidationstab ('some_validation_1').trunflag := TRUE;
   vvalidationstab ('some_validation_2').trunflag := TRUE;
   vvalidationstab ('some_validation_3').trunflag := TRUE;
   vvalidationstab ('some_validation_4').trunflag := TRUE;
   main (prunflags => vvalidationstab);
END;