[ACCEPTED]-Writing a select statement inside an Oracle user-defined function-plsql

Accepted answer
Score: 12

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 8 used as a variable, but never declared.

I 7 used the %TYPE notation to declare the variable 6 so it used the same data type as the column 5 being used to populate it. If the column 4 data type ever changes, the variable automatically 3 changes to suit -- no concerns with data 2 type issues after changes to the table, unless 1 you remove the column entirely.

Score: 0

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

To get one more than one result 1 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;

More Related questions