oracle oracle的select语句中为什么不能执行存储过程?有什么强有力的理由吗?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/17562024/
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
Why we can't execute stored procedure in select statement in oracle? is there any strong reason?
提问by Hasan
create or replace procedure pr
is
v_date date;
begin
select sysdate into v_date from dual;
DBMS_output.put_line(v_date);
end pr;
回答by Jon Heller
Procedures are not allowed in SQL statements because mixing declarative and imperative programming styles is confusing.
SQL 语句中不允许使用过程,因为混合声明式和命令式编程风格会造成混淆。
A SQL statement is a list of conditions - it's up to Oracle to decide how to produce the result set that matches those conditions. A PL/SQL stored procedure is a set of instructions that change things in a very predictable way.
SQL 语句是一个条件列表 - 由 Oracle 决定如何生成与这些条件匹配的结果集。PL/SQL 存储过程是一组以非常可预测的方式改变事物的指令。
In the below example, how many times should pr
be executed? Is it executed before or after id = 1
? If SQL statements had a pre-defined order then the optimizer would not be able to push predicates, merge subqueries, etc., and performance would be unacceptable.
在下面的例子中,应该pr
执行多少次?它是在之前还是之后执行id = 1
?如果 SQL 语句具有预定义的顺序,那么优化器将无法推送谓词、合并子查询等,性能将无法接受。
select *
from table1
where id = 1
and pr;
Even if a procedure were used in the select
list, it may not make sense. For example, the select
list inside an exists
is always ignored.
即使在select
列表中使用了一个过程,它也可能没有意义。例如,select
an 中的列表exists
总是被忽略。
select * from dual where exists (select pr from dual);
But in reality SQL statements sometimes need to interact with the outside world, and some procedural logic is needed. Functions are allowed because they usuallyjust calculate something and return a value. Functions normally do not depend on the program state and have a lot of side-effects. Your functions coulduse session variables, update tables (if it's set to PRAGMA AUTONOMOUS TRANSACTION
), set a context, etc. Oracle can't stop you from doing those things, but disallowing procedures in SQL statements will at least discourage such code.
但现实中SQL语句有时需要与外界交互,需要一些过程逻辑。函数是允许的,因为它们通常只是计算一些东西并返回一个值。函数通常不依赖于程序状态并且有很多副作用。您的函数可以使用会话变量、更新表(如果设置为PRAGMA AUTONOMOUS TRANSACTION
)、设置上下文等。Oracle 无法阻止您执行这些操作,但在 SQL 语句中禁止过程至少会阻止此类代码。
回答by J?cob
Procedure cannot be executed using select statement, you can use function if you would want to execute using select statement.
过程不能使用select语句执行,如果你想使用select语句执行,可以使用function。
If you would want to execute procedure using select statement then one approach is wrap your procedure with a function and call function using select statement.
如果您想使用 select 语句执行过程,那么一种方法是使用函数包装您的过程并使用 select 语句调用函数。
CREATE OR REPLACE PROCEDURE pr (o_param OUT DATE)
IS
v_date DATE;
BEGIN
SELECT SYSDATE
INTO v_date
FROM DUAL;
o_param := v_date;
END pr;
Now wrap the procedure with a function
现在用一个函数包装这个过程
CREATE OR REPLACE FUNCTION my_funct
RETURN DATE
AS
o_param DATE;
BEGIN
pr (o_param);
RETURN o_param;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (
DBMS_UTILITY.format_error_backtrace || ' ' || SQLERRM
);
-- raise exception
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
DBMS_UTILITY.format_error_backtrace || ' ' || SQLERRM
);
-- raise exception
END my_funct;
/
And call the function using select statement
并使用 select 语句调用函数
SELECT my_funct FROM DUAL
回答by CHIFUNDO
CREATE OR REPLACE PROCEDURE count_salas IS V_count NUMBER(3);
BEGIN
SELECT COUNT(SALES.SALEQTY) as sales INTO V_count
FROM SALES INNER JOIN EMPLOYEE ON EMPLOYEE.EMPID = SALES.EMPID WHERE EMPLOYEE.EMPID = '101';
DBMS_OUTPUT.PUT_LINE(V_count); END V_count;