C# Oracle 存储过程参数顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/959242/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# Oracle Stored Procedure Parameter Order
提问by JC.
With this
有了这个
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()
and
和
cmd.Parameters["NUMFOUND"].Value.ToString()
get swapped, although running the procedure through the VS2008 Server Explorer returns correct data.
尽管通过 VS2008 服务器资源管理器运行该过程返回正确的数据，但会被交换。
Why is this?
为什么是这样？
采纳答案by Marc Gravell
I'm not an Oracle buff, so I can't verify - but it soundslike they are being passed by position (rather than passed by name). The moral equivelent to:
我不是 Oracle 爱好者，所以我无法验证 - 但听起来它们是按位置传递的（而不是按名称传递）。道德等同于：
EXEC SomeProc 'Foo', 'Bar'
instead of:
代替：
EXEC SomeProc @arg1='Foo', @arg2='Bar'
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.
这并不少见 - 多年来（在 COM 时代）我的很多代码都必须使用按位置传递的 ADODB 驱动程序。
In this case, the name that you give serves onlyas 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()
If the above runs without error, it is passing by position. And it they arepassed by position... then simply add them in the right order ;-p And never add new parameters except at the end...
如果上面运行没有错误，它是通过位置。并且它们是按位置传递的...然后只需按正确的顺序添加它们;-p 并且永远不要添加新参数，除非在最后...
回答by tuinstoel
Not an answer to the question but you can use 'insert ... returning ... into ' in stead of select bgt_sequence.currval from dual, for example:
不是问题的答案，但您可以使用 'insert ... returns ... into ' 代替 select bgt_sequence.currval from dual，例如：
begin
insert into test (id)
values(test_seq.nextval)
returning id into p_id;
end;
See http://www.adp-gmbh.ch/ora/sql/insert_into_x_returning_y.html
见http://www.adp-gmbh.ch/ora/sql/insert_into_x_returning_y.html
回答by MarcE
You can probably set the BindByName parameter on the OracleCommand object. This works for straight SQL queries with parameters, I've not tried it with stored procedures but it would be logical...
您可能可以在 OracleCommand 对象上设置 BindByName 参数。这适用于带参数的直接 SQL 查询，我没有用存储过程尝试过，但它是合乎逻辑的......
cmd.BindByName = true;
