问题描述
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;
推荐答案
SQL语句中不允许使用过程，因为混合使用声明式和命令式编程风格会造成混淆.
Procedures are not allowed in SQL statements because mixing declarative and imperative programming styles is confusing.
SQL语句是条件列表-由Oracle决定如何产生与这些条件匹配的结果集. PL/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.
在下面的示例中，pr应该执行多少次?它在id = 1之前还是之后执行?如果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.
select *
from table1
where id = 1
and pr;
即使在select列表中使用了某个过程，也可能没有任何意义.例如，始终会忽略exists中的select列表.
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 * from dual where exists (select pr from dual);
但是实际上，SQL语句有时需要与外界交互，并且需要一些过程逻辑.允许使用函数是因为它们通常只是计算某些内容并返回一个值.函数通常不依赖于程序状态，并且有很多副作用.您的函数可以使用会话变量，更新表(如果将其设置为PRAGMA AUTONOMOUS TRANSACTION)，设置上下文等.Oracle无法阻止您执行这些操作，但是禁止SQL语句中的过程将至少不鼓励使用此类代码.
But in reality SQL statements sometimes need to interact with the outside world, and some procedural logic is needed. Functions are allowed because they usually just calculate something and return a value. Functions normally do not depend on the program state and have a lot of side-effects. Your functions could use 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.
这篇关于为什么我们不能在oracle的select语句中执行存储过程?有什么强烈的理由吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！