SQL oracle 过程返回整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/262338/
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
oracle procedure returns integer
提问by shaunf
In oracle, I want to create a delete sproc that returns an integer based on the outcome of the deletion.
在 oracle 中，我想创建一个删除 sproc，它根据删除的结果返回一个整数。
this is what i have so far.
这是我到目前为止。
create or replace
PROCEDURE Testing
(
iKey IN VARCHAR2
)
AS
BEGIN
delete from MyTable WHERE
TheKey = iKey;
END Testing;
i've tried putting a RETURNS INTEGER in but the sproc won't compile.
我试过将 RETURNS INTEGER 放入但 sproc 无法编译。
采纳答案by stjohnroe
Use a function and the implicit SQL cursor to determine the number of rows deleted
使用函数和隐式 SQL 游标确定删除的行数
create or replace
FUNCTION Testing
(
iKey IN VARCHAR2
) RETURN INTEGER
AS
BEGIN
delete from MyTable WHERE
TheKey = iKey;
RETURN SQL%ROWCOUNT;
END Testing;
That should work
那应该工作
回答by Justin Cave
A procedure does not return a value. A function returns a value, but you shouldn't be doing DML in a function (otherwise you cannot do things like reference the function in a SQL statement, you confuse permission grants since normally DBAs want to be able to grant read-only users access to all the functions so that users are doing computations consistently, etc.).
过程不返回值。一个函数返回一个值，但你不应该在一个函数中执行 DML（否则你不能像在 SQL 语句中引用该函数那样做，你会混淆权限授予，因为通常 DBA 希望能够授予只读用户访问权限到所有功能，以便用户始终如一地进行计算等）。
You can add an OUT parameter to the procedure to return the status. If "success" means that one or more rows were updated, you can use SQL%ROWCOUNT to get a count of the number of rows modified by the prior SQL statement and use that to populate the return parameter, i.e.
您可以向过程添加 OUT 参数以返回状态。如果“成功”意味着更新了一行或多行，则可以使用 SQL%ROWCOUNT 获取先前 SQL 语句修改的行数的计数，并使用该计数填充返回参数，即
CREATE OR REPLACE PROCEDURE test_proc (
p_iKey IN VARCHAR2,
p_retVal OUT INTEGER
)
AS
BEGIN
DELETE FROM myTable
WHERE theKey = p_iKey;
IF( SQL%ROWCOUNT >= 1 )
THEN
p_retVal := 1;
ELSE
p_retVal := 0;
END IF;
END test_proc;
Of course, from a general code clarity standpoint, I'm dubious about OUT parameters that appear to be trying to return a status code. You are generally much better served by assuming success and throwing exceptions in the event of an error.
当然，从一般代码清晰度的角度来看，我对似乎试图返回状态代码的 OUT 参数表示怀疑。假设成功并在发生错误时抛出异常通常会更好地为您服务。
回答by user34850
You can use a stored procedure to return results.
您可以使用存储过程返回结果。
CREATE OR REPLACE PROCEDURE testing (iKey IN VARCHAR2, oRes OUT NUMBER)
AS
BEGIN
DELETE FROM MyTable
WHERE TheKey = iKey;
oRes := SQL%ROWCOUNT;
END;
To call the procedure use something like:
要调用该过程，请使用以下内容：
DECLARE
pRes NUMBER;
BEGIN
testing ('myspecialkey', pRes);
DBMS_OUTPUT.put_line (pRes);
END;
回答by Jacob Schoen
You are probably looking for a function instead.
您可能正在寻找一个函数。
FUNCTION TESTING (iKEY IN VARCHAR2) RETURN NUMBER
IS
v_count NUMBER;
yourNumber NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM MyTable
WHERE TheKey = iKey;
IF v_count > 0
THEN
DELETE FROM MyTable
WHERE TheKey = iKey;
SELECT COUNT(*) INTO v_count
FROM MyTable
WHERE TheKey = iKey;
IF (v_count = 0)
THEN
yourNumber := 1; --means successful deletion
END IF;
ELSE
yourNumber := 0; --means no items to delete
END IF;
return yourNumber;
EXCEPTION
WHEN OTHERS THEN
RETURN -1; --means error was encountered
END TESTING;
Note: Where I work we generally put functions inside a sql package.
注意：在我工作的地方，我们通常将函数放在 sql 包中。
回答by PG08 de Spurs
The SQL%ROWCOUNTwill only return a value immediately after a DML its value will reset if another DML is executed.
在SQL%ROWCOUNT一个DML如果执行另一条DML将其值重置后会立刻只返回一个值。
To get around the problem as I was executing deletes in a loop I issued the following command after the dml:
为了解决我在循环中执行删除时的问题，我在 dml 之后发出了以下命令：
row_count := row_count + SQL%ROWCOUNT;
Please make sure you declare and initialize row_count := 0;
请确保您声明并初始化 row_count := 0;
