oracle 在过程中调用函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13729925/
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
Calling a function within a procedure
提问by Christa L
I apologize ahead of time for the probably basic question. I am student and it is crunch time!
对于可能的基本问题，我提前道歉。我是学生，现在是关键时刻！
I am using Oracle 10g Express.
我正在使用 Oracle 10g Express。
I created a function:
我创建了一个函数：
create or replace FUNCTION test_glaccounts_description
(
account_description_param VARCHAR2
)
RETURN NUMBER
AS
description_dup_var NUMBER;
BEGIN
SELECT 1
INTO description_dup_var
FROM general_ledger_accounts
WHERE account_description = account_description_param;
RETURN description_dup_var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
And would like to use that function in a procedure. I've tried:
并想在程序中使用该功能。我试过了：
PROCEDURE insert_gla_with_test
(
account_number_param NUMBER,
account_description_param VARCHAR2
)
AS
BEGIN
IF test_glaccounts_description = 1 THEN
INSERT INTO general_ledger_accounts
VALUES (account_number_param, account_description_param);
ELSE raise_application_error (-20001, 'Duplicate account description');
END IF;
END;
But it doesn't like the "test_gla_accounts" line.....what am I doing wrong? To my understanding, the function returns a value of 1 or 0, in the procedure, if the function returned a 1, I would like the param fields added to the table. If the function returned a 0, I would like the procedure to raise the error.
但它不喜欢“test_gla_accounts”行......我做错了什么？据我了解，该函数返回的值是 1 或 0，在程序中，如果函数返回 1，我希望将 param 字段添加到表中。如果函数返回 0，我希望该过程引发错误。
回答by Justin Cave
test_glaccounts_descriptiontakes a parameter (account_description_param) and returns a NUMBER. In order to call the function, therefore, you need to pass in a parameter. Assuming that you want to pass in the account_description_paramthat is passed in to the insert_gla_with_testprocedure
test_glaccounts_description接受一个参数 ( account_description_param) 并返回一个NUMBER. 因此，为了调用该函数，您需要传入一个参数。假设你想传入account_description_param那个被传入到insert_gla_with_test程序中的
CREATE OR REPLACE PROCEDURE insert_gla_with_test
(
account_number_param NUMBER,
account_description_param VARCHAR2
)
AS
BEGIN
IF test_glaccounts_description( account_description_param ) = 1 THEN
INSERT INTO general_ledger_accounts
VALUES (account_number_param, account_description_param);
ELSE
raise_application_error (-20001, 'Duplicate account description');
END IF;
END;
