c# - 通过 C# 将整数数组传递给 oracle 过程

Question

我想通过 C# 将整数数组传递给存储过程。该过程通过 sql developer 工作，但在 c# 中不起作用。这是我的代码，但我被调用“V1”时参数的数量或类型错误错误。请帮我C#代码:

DBEngine oracleEngine = new OracleEngine(connectionString);

DbCommand cmd = oracleEngine.MakeTextCmd("v1");
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter param1 = new OracleParameter();

List<int> values = new List<int>() { 1, 2, 3, 4, 5 };

OracleParameter p_strings = new OracleParameter();
p_strings.ParameterName = "VehicleGroupID_Array";
p_strings.OracleDbType = OracleDbType.Int32;
p_strings.Direction = ParameterDirection.Input;
p_strings.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_strings.Value = new int[5]{1,2,3,4,5};
cmd.Parameters.Add(p_strings);

//DbDataReader reader = oracleEngine.ExecuteReader(cmd);
cmd.ExecuteNonQuery();

我的程序:

create or replace PROCEDURE v1
(
  VehicleGroupID_Array IN INNUMARRAY --  List
)
IS
  p_recordset SYS_REFCURSOR;
BEGIN
  OPEN p_recordset FOR
  SELECT DISTINCT
         "vUserVehicles"."UserID",
         "vUserVehicles"."VehicleID",
         "vUserVehicles"."VehicleName",
         "vUserVehicles"."VehicleSerialNo",
         "vUserVehicles"."Description",
         "vUserVehicles"."VehicleNo",
         "vUserVehicles"."VehicleShahrbaniNo",
         "vUserVehicles"."GSMWirelessDialNo",
         "vUserVehicles"."Status",
         "vUserVehicles"."ThurayaDialNo",
         "vUserVehicles"."Company",
         "vUserVehicles"."MachineModelId",
         "vUserVehicles"."VehicleTypeID",
         "vUserVehicles"."Consumption",
         "vUserVehicles"."RegistrationCode",
         "vUserVehicles"."VehicleKindId"
  FROM   "vUserVehicles"
         INNER JOIN "VehicleGroupDetail"
         ON "vUserVehicles"."VehicleID" = "VehicleGroupDetail"."VehicleID"
  WHERE  "VehicleGroupDetail"."VehicleGroupID" IN (
           select column_value from table(VehicleGroupID_Array))
         )
  ORDER BY "vUserVehicles"."Description" ASC;

  DBMS_SQL.RETURN_RESULT(p_recordset);
END;

和我的类型:

create or replace TYPE INNUMARRAY AS TABLE OF INTEGER;

Answer 1

您的类型:

create or replace TYPE INNUMARRAY AS TABLE OF INTEGER;

是在 SQL 范围内定义的集合。

您传递的参数:

p_strings.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_strings.Value = new int[5]{1,2,3,4,5};

是一个关联数组，只能在 PL/SQL 作用域中定义(即在包中或在 PL/SQL block 中)，不能在 SQL 作用域中使用。

它们是两种不同且不兼容的数据类型。

相反，您可以在包中创建关联数组类型，然后手动将关联数组中的每个值提取到可在 SQL 作用域中使用的集合中:

CREATE PACKAGE vehicles_pkg IS
  TYPE INNUMASSOCARRAY IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

  PROCEDURE v1
  (
    VehicleGroupID_Array IN INNUMASSOCARRAY
  );
END;
/

CREATE PACKAGE BODY vehicles_pkg IS
  PROCEDURE v1
  (
    VehicleGroupID_Array IN INNUMASSOCARRAY
  )
  IS
    p_recordset SYS_REFCURSOR;
    p_array     INNUMARRAY := INNUMARRAY();
    i           BINARY_INTEGER;
  BEGIN
    i := VehicleGroupID_Array.FIRST;
    WHILE i IS NOT NULL LOOP
      p_array.EXTEND;
      p_array( p_array.COUNT ) := VehicleGroupID_Array(i);
      i := VehicleGroupID_Array.NEXT(i);
    END LOOP;

    -- Rest of your procedure using p_array instead of the associative array.
  END;
END;
/

can I define the associative array type outside of package? I want them to be standalone.

不，但是你可以定义一个只包含类型的包:

CREATE PACKAGE vehicles_pkg IS
  TYPE INNUMASSOCARRAY IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
END;
/

CREATE PROCEDURE v1
(
  VehicleGroupID_Array IN vehicles_pkg.INNUMASSOCARRAY
)
IS
  p_recordset SYS_REFCURSOR;
  p_array     INNUMARRAY := INNUMARRAY();
  i           BINARY_INTEGER;
BEGIN
  i := VehicleGroupID_Array.FIRST;
  WHILE i IS NOT NULL LOOP
    p_array.EXTEND;
    p_array( p_array.COUNT ) := VehicleGroupID_Array(i);
    i := VehicleGroupID_Array.NEXT(i);
  END LOOP;

  -- Rest of your procedure using p_array instead of the associative array.
END;
/

或者，更好的是，在包中创建一些通用命名的类型和一个函数，以将关联数组转换为集合，然后在您的过程中重用它们:

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TYPE IntList AS TABLE OF INTEGER
/

CREATE PACKAGE tools IS
  TYPE IntMap IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

  FUNCTION IntMapToList(
    i_map IntMap
  ) RETURN IntList;
END;
/

CREATE PACKAGE BODY tools IS
  FUNCTION IntMapToList(
    i_map IntMap
  ) RETURN IntList
  IS
    o_list IntList := IntList();
    i      BINARY_INTEGER;
  BEGIN
    IF i_map IS NOT NULL THEN
      i := o_list.FIRST;
      WHILE i IS NOT NULL LOOP
        o_list.EXTEND;
        o_list( o_list.COUNT ) := i_map( i );
        i := i_map.NEXT( i );
      END LOOP;
    END IF;
    RETURN o_list;
  END;
END;
/

CREATE PROCEDURE v1
(
  VehicleGroupID_Array IN tools.IntMap
)
IS
  p_recordset SYS_REFCURSOR;
  p_array     IntList := tools.IntMapToList( VehicleGroupID_Array );
  i           BINARY_INTEGER;
BEGIN
  -- Rest of your procedure using p_array instead of the associative array.
  NULL;
END;
/