在 ORACLE 的 select 语句中从 PL/SQL 调用函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25384984/
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 from PL/SQL in a select statement in ORACLE
提问by Patrick Bacon
Here is my PL/SQL function portion. I am trying to use the function in a select statement. for example we can write a query like select count(column_name) from table_name. Here count is a function. I want to use my own functions like this. I've tried differently (using the function outside the PL/SQL function, inside the PL/SQL function). But it throws an error PLS-00231:function 'GET_ANNUAL_COMP' may not be used in SQLwhen used inside PL/SQL function and throws ORA-00904 invalid identifierwhen when used outside the PL/SQL function.
这是我的 PL/SQL 函数部分。我正在尝试在 select 语句中使用该函数。例如，我们可以编写一个查询，如select count(column_name) from table_name. 这里 count 是一个函数。我想像这样使用我自己的功能。我尝试了不同的方法（使用 PL/SQL 函数外部的函数，PL/SQL 函数内部的函数）。但是PLS-00231:function 'GET_ANNUAL_COMP' may not be used in SQL在 PL/SQL 函数内部使用时会抛出错误，而在 PL/SQL 函数ORA-00904 invalid identifier外部使用时会抛出错误。
I'm using oracle 11g.
我正在使用 oracle 11g。
declare
em_sal number(20);
em_comm employees.commission_pct%type;
annual_salary number(10,4);
function get_annual_comp(sal in number, comm in number)
return number is
begin
return (sal*12 + comm*12);
end;
begin
select salary into em_sal from employees where employee_id=149;
select commission_pct into em_comm from employees where employee_id=149;
annual_salary := get_annual_comp(em_sal,em_comm);
dbms_output.put_line('total salary '|| annual_salary);
select get_annual_comp(salary,commission_pct) from employees where department_id=90;
end;
/
回答by Patrick Bacon
Compile the function in an appropriate schema (sames schema that will be running anonymous block) as follows:
在适当的模式（将运行匿名块的模式相同）中编译函数，如下所示：
CREATE OR REPLACE FUNCTION GET_ANNUAL_COMP(
sal IN NUMBER,
comm IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (sal*12 + comm*12);
END;
With same schema as the function, run the anonymous block:
使用与函数相同的模式，运行匿名块：
DECLARE
em_sal NUMBER(20);
em_comm employees.commission_pct%type;
annual_salary NUMBER(10,4);
BEGIN
SELECT salary INTO em_sal FROM employees WHERE employee_id=149;
SELECT commission_pct INTO em_comm FROM employees WHERE employee_id=149;
annual_salary := get_annual_comp(em_sal,em_comm);
dbms_output.put_line('total salary '|| annual_salary);
SELECT SUM(get_annual_comp(salary,commission_pct)) into annual_salary
FROM employees
WHERE department_id=90;
dbms_output.put_line('department annual salary '|| annual_salary);
END;
/
回答by balaaagi
Create the function globally. Also ensure that the function is created in same schema also the logged in user has necessary privileges in the schema.
全局创建函数。还要确保该函数是在同一架构中创建的，并且登录用户在该架构中具有必要的权限。
