Sql – How to create a SQL function with a parameter

oracleoracle-apexplsqlsql

The following code returns one field, a string, based on the input where it says "28".

SELECT data.id, LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
  FROM (
     SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
      FROM (
         SELECT es.EVENT_ID as id, s.SERVICE_NAME as name FROM DT_SERVICES s
            JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
         WHERE es.EVENT_ID = 28
      )
) data
WHERE rownumber = cnt
START WITH rownumber = 1
CONNECT BY PRIOR rownumber = rownumber-1;

How can I create a SQL function out of this so that I can pass in whatever number (instead of 28) and the function would return whatever the result of that select turns out to be?

I tried creating one but I keep getting compilation errors.

Current SQL to create function

create or replace function "DT_SERVICE_STRING" (id in VARCHAR2)
  return VARCHAR2 is
begin
  DECLARE 
     result VARCHAR(200);
  SELECT data.id, LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
    INTO result
    FROM (
      SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
      FROM (
        SELECT es.EVENT_ID as id, s.SERVICE_NAME as name FROM DT_SERVICES s
        JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
        WHERE es.EVENT_ID = id
      )
  ) data
  WHERE rownumber = cnt
  START WITH rownumber = 1
  CONNECT BY PRIOR rownumber = rownumber-1;

  return result;
end;‚Äč

Error:
Compilation failed,line 7 (15:22:21)
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior

Best Solution

Assuming that you want a PL/SQL function that is callable from a SQL statement (you cannot have a function defined in SQL), it sounds like you want something like

CREATE OR REPLACE FUNCTION get_conc_names( p_event_id IN dt_event_service.event_id%type )
  RETURN VARCHAR2
IS
  l_conc_names VARCHAR2(32676); 
  -- You may want a smaller variable if you know the result will be smaller
BEGIN
  SELECT LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
    INTO l_conc_names
    FROM (
      SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
        FROM (SELECT es.EVENT_ID as id, s.SERVICE_NAME as name 
                FROM DT_SERVICES s
                     JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
               WHERE es.EVENT_ID = p_event_id )
          ) data
   WHERE rownumber = cnt
   START WITH rownumber = 1
 CONNECT BY PRIOR rownumber = rownumber-1;
  RETURN l_conc_names;
END;

Based on the code you just posted, it appears that you just need to get rid of the DECLARE and to move the declaration of the local variable result before the BEGIN and after the IS.