Writing a select statement inside an Oracle user-defined function

Question

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.

Solution

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.

Comments

[the_new_mr]

Thank you for your answer. When I try this code, I get the following error messages: Error(4,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor external language The symbol "begin" was substituted for "DECLARE" to continue.

[the_new_mr]

Error(15,3): PLS-00103: Encountered the symbol "RETURN" when expecting one of the following: * & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset

[the_new_mr]

Error(17,21): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor

[OMG Ponies]

@the_new_mr: Try now - I forgot the semi-colon to terminate the SELECT statement. Had already removed the DECLARE keyword ~6 minutes ago -- you should really refresh the page before you copy/paste in case someone updates their answer.

[OMG Ponies]

@Quassnoi: Thank you -- I rarely included the function name in the END notation. Too busy making sure I was first :/

[the_new_mr]

@OMG Ponies Thanks. I tried the new code and it worked perfectly. However, it would seem I have a new problem that I didn't know I would have and that maybe the way I'm trying to do it is wrong. When I try and call this function, I get "exact fetch returns more than requested number of rows". The result of the select statement returns two rows (as it should do) so how can I run this? Maybe trying to put it into a variable isn't the right method?

[OMG Ponies]

@the_new_mr: Do you need all results, or just one? If all, you need to return a SYS_REFCURSOR. Otherwise, you need to change the query appropriately to return a single value - IE: MAX