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

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 some开发者_开发百科where 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.

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.

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;