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 Server Explorer运行该过程会返回正确的数据,但仍会进行交换。
Why is this?
为什么是这样?
3 个解决方案
#1
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:
我不是Oracle buff,所以我无法验证 - 但听起来它们是通过位置传递(而不是通过名称传递)。道德等同于:
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 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()
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...
如果上面运行没有错误,它就是通过位置。并且它们通过位置传递...然后只需按正确的顺序添加它们;-p并且永远不要添加新的参数,除了最后...
#2
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;
#3
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'而不是从dual中选择bgt_sequence.currval,例如:
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
#1
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:
我不是Oracle buff,所以我无法验证 - 但听起来它们是通过位置传递(而不是通过名称传递)。道德等同于:
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 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()
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...
如果上面运行没有错误,它就是通过位置。并且它们通过位置传递...然后只需按正确的顺序添加它们;-p并且永远不要添加新的参数,除了最后...
#2
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;
#3
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'而不是从dual中选择bgt_sequence.currval,例如:
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