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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 01:47:01  来源:igfitidea点击:

Why we can't execute stored procedure in select statement in oracle? is there any strong reason?

oracleoracle10g

提问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 prbe 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 selectlist, it may not make sense. For example, the selectlist inside an existsis always ignored.

即使在select列表中使用了一个过程,它也可能没有意义。例如,selectan 中的列表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;