在 Oracle 用户定义函数中编写 select 语句

发布于 2024-10-01 06:06:12 字数 449 浏览 3 评论 0原文

我对 Oracle SQL 很陌生（尽管我已经编写了相当多的 SQL），并且在函数中嵌入简单的 SELECT 语句时遇到了麻烦。感觉就像 SQL ABC，但我似乎无法理解它:(

我认为我正在使用 PL-SQL

这是到目前为止我所拥有的：

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;

正如我所说，我已经尝试了很多通过查看谷歌上的代码示例对上述代码进行了变体，但似乎无法正确理解。

原文

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 :(

I think I'm using 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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

他是夢罘是命 2024-10-08 06:06:12

使用：

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;

问题是 my_result 被用作变量，但从未声明。

我使用 %TYPE 表示法来声明变量，因此它使用与用于填充它的列相同的数据类型。如果列数据类型发生变化，变量会自动更改以适应表的变化，无需担心表更改后的数据类型问题，除非您完全删除该列。

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.

I used the %TYPE notation 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.

回复收藏 0 原文

酒绊 2024-10-08 06:06:12

回答我上面对 OMG Ponies 答案的最后评论：

要从函数返回一个以上的结果，请使用 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;

In answer to my last comment of OMG Ponies answer above:

To get one more than one result back from a function, use the 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;

回复收藏 0 原文

~没有更多了~