组合数组绑定和关联数组

发布于 2024-12-23 01:12:11 字数 570 浏览 2 评论 0原文

我想使用 ODP.NET (4.112.2.0) 从我的 c# .net 4 代码调用 oracle10g 的存储过程“MyStoredProcedure”。

PACKAGE MyPackage AS
TYPE ids_array IS TABLE OF NUMBER(8) INDEX BY BINARY_INTEGER;
PROCEDURE MyStoredProcedure
(
   param1 IN VARCHAR2(10),
   param2 IN ids_array
);

END MyPackage;

我知道如何使用数组绑定功能，以便我可以在单个数据库往返中将多行插入到表中。
我还知道如何调用以关联数组作为参数的存储过程。我不知道如何将两者结合起来？我查看了http://docs.oracle.com/html/E10927_01/featOraCommand。 htm#i1007888 例如和示例代码（但其单独的示例）。谢谢

原文

I want to call oracle10g's stored procedure "MyStoredProcedure" from my c# .net 4 code using ODP.NET (4.112.2.0).

PACKAGE MyPackage AS
TYPE ids_array IS TABLE OF NUMBER(8) INDEX BY BINARY_INTEGER;
PROCEDURE MyStoredProcedure
(
   param1 IN VARCHAR2(10),
   param2 IN ids_array
);

END MyPackage;

I know how to use the array bind feature, so that i can insert multiple rows into a table in a single database round-trip.
I also know how to call a stored procedure which has associative array as a parameter. What i don't know is how to combine both?
I looked at http://docs.oracle.com/html/E10927_01/featOraCommand.htm#i1007888 for example and sample code (but its separate examples).
Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

多情癖 2024-12-30 01:12:11

我最终不得不将数组转换为逗号分隔的字符串，并将其作为参数传递。所以在我的过程中，我将 csv 转换回数组。

回复收藏 0 原文

终陌 2024-12-30 01:12:11

虽然转换为 CSV 有效，但效率不是很高，而且字符数限制为 4000 个。

为了实现你想要的，最好使用 forall 循环

，例如

PACKAGE MyPackage AS
TYPE ids_array IS TABLE OF NUMBER(8) INDEX BY BINARY_INTEGER;
PROCEDURE MyStoredProcedure
(
   param1 IN VARCHAR2(10),
   param2 IN ids_array
);
/
PACKAGE MyPackage BODY

ENDPROCEDURE MyStoredProcedure
(
   param1 IN VARCHAR2(10),
   param2 IN ids_array
) AS
BEGIN

    forall i in param2.FIRST .. param2.LAST
        INSERT INTO TABLE(col1,col2) VALUES (param1, params2(i));

END;

END MyPackage;

在 ODP.NET 中，您有两个参数，第一个是 varchar，第二个是关联数组。

Although converting to CSV worked it is not very efficient and you are limited to 4000 characters.

To achieve what you want it would be best to use a use a forall loop

e.g.

PACKAGE MyPackage AS
TYPE ids_array IS TABLE OF NUMBER(8) INDEX BY BINARY_INTEGER;
PROCEDURE MyStoredProcedure
(
   param1 IN VARCHAR2(10),
   param2 IN ids_array
);
/
PACKAGE MyPackage BODY

ENDPROCEDURE MyStoredProcedure
(
   param1 IN VARCHAR2(10),
   param2 IN ids_array
) AS
BEGIN

    forall i in param2.FIRST .. param2.LAST
        INSERT INTO TABLE(col1,col2) VALUES (param1, params2(i));

END;

END MyPackage;

From the ODP.NET you have two parameters the first being a varchar, the second being an associative array.

回复收藏 0 原文

~没有更多了~