CODEWITHSUNDEEP
sqloracle-databasefunctionplsql
the_new_mr
the_new_mr

Reputation: 3733

Writing a select statement inside an Oracle user-defined function

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.

Upvotes: 2

Views: 23738

Answers (2)

the_new_mr
the_new_mr

Reputation: 3733

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;

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

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.

Upvotes: 12

Related Questions