在Oracle的过程中选择Statement [英] Select Statement inside a procedure in Oracle

问题描述

很抱歉提出这个问题，但这是因为以前给出的答案都不足以让我理解.我想编写一个存储过程来返回表中的所有列.作为临时查询，我只写

sorry for asking this question but it's because none of the answers given before are enough for me to understand. I want to write a stored procedure that returns all the columns in a table.As an ad hod query I just write

SELECT * FROM EMPLOYEES

但是在这里，我收到一个错误提示我提供INTO子句，我不明白为什么和如何.有人可以解释在上述情况下以及当我只想返回一个时我该怎么做列值(多行).

but here, I get an error which prompts me to provide INTO clause which I don't understand why and how.Could someone explain how would I do that both in the above case and when I want to return just one column values(multiple rows).

推荐答案

具有SQL Server背景的人们习惯于编写返回完整查询结果的存储过程，因此尝试编写如下PL/SQL过程:

People with a SQL Server background are used to writing stored procedures that return whole query results and so try to write PL/SQL procedures something like this:

procedure get_emps is
begin
    -- this will NOT work!
    select * from emp;
end;

不幸的是，事情并非如此简单. PL/SQL中最接近的等效项可能是返回引用游标的函数:

Unfortunately it's not that simple. Probably the nearest equivalent in PL/SQL is a function returning a ref cursor:

function get_emps return sys_refcursor is
    rc sys_refcursor;
begin
    open rc for
        select * from emp;
    return rc;
end;

您可以从这样的调用程序中调用它:

You could call this from a calling program like this:

declare
    cur sys_refcursor;
    emp_rec emp%rowtype;
begin
    cur := get_emps;
    loop
        fetch cur into emp_rec;
        exit when cur%notfound;
    end loop;
    close cur;
end;

或在SQL Plus中，您可以执行以下操作:

or in SQL Plus you could do:

var rc refcursor
:rc := get_emps;
print rc

这篇关于在Oracle的过程中选择Statement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！