在 Oracle 用户定义函数中编写 select 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4147764/
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
Writing a select statement inside an Oracle user-defined function
提问by the_new_mr
I'm quite new to Oracle SQL (though I've written a fair bit of SQL) and am having trouble embedding a simple SELECT statement in a function. It feels like SQL ABC but I can't seem to get it :(
我对 Oracle SQL 很陌生（尽管我已经编写了相当多的 SQL）并且在将简单的 SELECT 语句嵌入到函数中时遇到了麻烦。感觉就像 SQL ABC，但我似乎无法理解:(
I thinkI'm using PL-SQL
我想我正在使用 PL-SQL
Here's what I have so far:
这是我到目前为止所拥有的：
create or replace FUNCTION GET_GROUP_BY_ID RETURN VARCHAR2
AS my_result
BEGIN
SELECT fav_group.name
INTO my_result
FROM fav_group
WHERE fav_group.id = 12345
RETURN my_result;
END GET_GROUP_BY_ID;
As I said, I've tried a LOT of variations on the above code by looking at code examples on google but can't seem to get it right.
正如我所说，通过查看 google 上的代码示例，我已经尝试了对上述代码的很多变体，但似乎无法做到正确。
回答by OMG Ponies
Use:
用：
CREATE OR REPLACE FUNCTION GET_GROUP_BY_ID
RETURN VARCHAR2 AS
my_result FAV_GROUP.NAME%TYPE;
BEGIN
SELECT fav_group.name
INTO my_result
FROM fav_group
WHERE fav_group.id = 12345;
RETURN my_result;
END GET_GROUP_BY_ID;
The problem was my_result was being used as a variable, but never declared.
问题是 my_result 被用作变量，但从未声明。
I used the %TYPEnotation to declare the variable so it used the same data type as the column being used to populate it. If the column data type ever changes, the variable automatically changes to suit -- no concerns with data type issues after changes to the table, unless you remove the column entirely.
我使用%TYPE符号来声明变量，因此它使用与用于填充它的列相同的数据类型。如果列数据类型发生变化，变量会自动更改以适应 - 更改表后无需担心数据类型问题，除非您完全删除该列。
回答by the_new_mr
In answer to my last comment of OMG Ponies answer above:
回答我对上面的 OMG Ponies 的最后一条评论：
To get one more than one result back from a function, use the REF CURSOR
要从函数返回一个以上的结果，请使用 REF CURSOR
create or replace
PACKAGE BODY REPORTING AS
FUNCTION GET_GROUP_BY_GID RETURN REF_CURSOR AS
RESULT_SET REF_CURSOR;
BEGIN
OPEN RESULT_SET FOR
SELECT favorite_group.name
FROM favorite_group
WHERE favorite_group.creator_gid = 450160;
RETURN RESULT_SET;
EXCEPTION WHEN OTHERS THEN
RAISE;
END GET_GROUP_BY_GID;
