Can a Sql statement be encapsulated into a sub function for better readability?

Question

I try to implement a table function by following Tony Andrews example. But my Sql statement will be several lines long (>20) and will also contain parameters. Is it possible to encapsulate the Sql statement somewhere else and to refer to it from the main function to keep everything more readable?

In traditional programming languages I would assign the raw Sql into a string and replace the parameters as required.

20 lines isn't very long, but you could always hide the query behind a view. (Just don't stack several layers of views on views). — Ronnis, Mar 08 '11 at 21:22
Indeed I tried to use a view first, but it was to slow compared to the parameterized Sql. Because of that I choose to write a function :-) — al-bex, Mar 08 '11 at 21:51
Refer to it from **what** main function. You shall elaborate on how do you want to use the statement (or the data returned by executing it), because in its current form the question is unanswerable. — jachguate, Mar 08 '11 at 23:29

score 3 · Answer 1 · answered Mar 08 '11 at 22:06

Yes, you can:

--declare variables
TYPE curref is REF CURSOR;
c1 curref;

q VARCHAR2(2000);

--save query to string variable
q:='<insert 20 line query here>';

--open cursor 
OPEN c1 FOR q;

--then, work on cursor rows, one by one

Notice that 'q' can be quite complex, with concatenated parameter values (or subqueries that can be stored as separate varchar variables):

q:='
    select distinct
        fn.BASENAME as name
        ,h.PARENT_ID as "admin_place_id"
        ,h.lev as "admin_level"
    FROM (
        SELECT CHILD_ID, PARENT_ID, level lev
        from '||schema||'.NT_ADMIN_GRAPH
        START WITH CHILD_ID='||q_sub2||'
        CONNECT BY CHILD_ID=PRIOR PARENT_ID
    UNION ALL
        SELECT null, '||q_sub2||', 0
        FROM DUAL
    ) h
    '
    ||q_sub||
    '
    ORDER BY h.lev asc    
';

See Oracle's documentation for more examples of how to implement this.

Many thanks. It is not exactly the way I did it, becuase of to many string concatenations. But your answer leads me to cursors. — al-bex, Mar 09 '11 at 10:24

score 1 · Accepted Answer · answered Mar 09 '11 at 10:30

Inspired by drapkin11 I found a solution using a cursor:

CREATE OR REPLACE Function TotalIncome( name_in IN varchar2 )
RETURN varchar2
IS
    total_val number(6);

    cursor c1 is
      select monthly_income
      from employees
      where name = name_in;

BEGIN
    total_val := 0;
    FOR employee_rec in c1
    LOOP
        total_val := total_val + employee_rec.monthly_income;
    END LOOP;
    RETURN total_val;
END;

Can a Sql statement be encapsulated into a sub function for better readability?

2 Answers2