C＃Oracle存储过程参数顺序 [英] C# Oracle Stored Procedure Parameter Order

问题描述

通过这种

PROCEDURE "ADD_BOOKMARK_GROUP" (
  "NAME" IN VARCHAR2, 
  "BOOKMARK_GROUP_ID" IN NUMBER, 
  "STAFF_ID" IN VARCHAR2,
  "MAX_NO" IN INT,
  "NUMFOUND" OUT INT, 
  "NEW_ID" OUT NUMBER) IS

BEGIN

NEW_ID := -1;

SELECT COUNT(*) INTO NUMFOUND FROM BOOKMARK_GROUP_TABLE WHERE STAFF_ID = STAFF_ID;

IF NUMFOUND < MAX_NO THEN
    INSERT INTO BOOKMARK_GROUP_TABLE (NAME, BOOKMARK_GROUP_ID, STAFF_ID) VALUES(NAME, BOOKMARK_GROUP_ID, STAFF_ID);
    SELECT BGT_SEQUENCE.currval INTO NEW_ID FROM dual;
END IF;
END;

我觉得很有意思，如果我不按顺序添加参数，他们所定义的，例如：

I find it interesting that if I don't add parameters in the order they were defined in, e.g.

OracleCommand cmd = new OracleCommand("ADD_BOOKMARK_GROUP", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("NAME", name));
...
cmd.Parameters.Add(new OracleParameter("NEW_ID", OracleDbType.Decimal)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("NUMFOUND", OracleDbType.Int32)).Direction = ParameterDirection.Output;

而不是

instead of

OracleCommand cmd = new OracleCommand("ADD_BOOKMARK_GROUP", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("NAME", name));
...
cmd.Parameters.Add(new OracleParameter("NUMFOUND", OracleDbType.Int32)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("NEW_ID", OracleDbType.Decimal)).Direction = ParameterDirection.Output;

按

The values returned by

cmd.Parameters["NEW_ID"].Value.ToString()

以及

cmd.Parameters["NUMFOUND"].Value.ToString()

被换，虽然贯穿VS2008的服务器资源管理器，程序返回正确的数据。

get swapped, although running the procedure through the VS2008 Server Explorer returns correct data.

为什么这是

推荐答案

我不是一个Oracle的buff，所以我无法验证 - 但它的声音的像他们正在被传递位置（而不是按名称传递）。道德equivelent为：

I'm not an Oracle buff, so I can't verify - but it sounds like they are being passed by position (rather than passed by name). The moral equivelent to:

EXEC SomeProc 'Foo', 'Bar'

而不是：

EXEC SomeProc @arg1='Foo', @arg2='Bar'

这是不是非常少见 - 多年来（在COM日）有很多我的代码曾与一个传递位置ADODB司机工作。

This isn't hugely uncommon - for years (in the COM days) a lot of my code had to work with a pass-by-position ADODB driver.

在这种情况下，你给发球的只有名称作为本地key来查找从收藏价值。您可以通过发明的名称很容易地验证：

In this case, the name that you give serves only as a local key to lookup the value from the collection collection. You can verify easily by inventing a name:

cmd.Parameters.Add(new    OracleParameter("BANANA", ...
cmd.Parameters.Add(new    OracleParameter("GUITAR", ...
...
cmd.Parameters["BANANA"].Value.ToString()
cmd.Parameters["GUITAR"].Value.ToString()

如果没有错误以上运行时，它是按位置传递。而且它自己的是按位置过去了......然后简单地添加他们在正确的顺序;-p而且从来除了在末尾添加新的参数...

If the above runs without error, it is passing by position. And it they are passed by position... then simply add them in the right order ;-p And never add new parameters except at the end...

这篇关于C＃Oracle存储过程参数顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！