Re: Create a user defined function

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 12 Mar 2006 10:37:22 -0800
Message-ID: <1142188624.955304@yasure.drizzle.com>

Alexander Mueller wrote:
> Alexander Mueller schrieb:
>

>> Laurenz Albe schrieb:
>>
>>> Alexander Mueller <millerax_at_hotmail.com> wrote:
>>>> I need to create a user defined function that i can embed into
>>>> SQL-statements, that returns a string. the objective is to get a 'flat
>>>> list' of all employees that are associated to a certain order.
>>>> I never wrote a SP or user defined function so i can only show you the
>>>> pieces of code that i need to put together:
>>> [...]
>>>
>>>> My Questions:
>>> [...]
>>>
>>> My question: why don't you do it in PL/SQL?

>
> Happy, i could solve it on my own:
>
> Mfg,
> Alex
>
>
> CREATE OR REPLACE FUNCTION "UF_EMPLIST"
> (
> id in int,
> delim in VARCHAR2 default ';'
> )
>
> return VARCHAR2
> is
>
> /* declare select for cursor */
> cursor empCursor is
> SELECT
> TYPISTNAME EMP FROM <some tables>
> WHERE
> <some conditions>
> GROUP BY <columnlist>;
>
> str_return VARCHAR2(32767);
> str_val VARCHAR2(255);
>
> begin
>
> /* open empCursor */
> open empCursor;
>
> loop
>
> /* copy row to string-var */
> fetch empCursor into str_val;
>
> exit when empCursor%NOTFOUND;
>
> /* very unlikely but watch max string len */
> if ( (LENGTH (str_return) + LENGTH (str_val)) > 32767) then
>
> Close empCursor;
> return (str_return);
>
> end if;
>
> /* add delim if there already is an entry and new value is not
> empty */
> str_val := RTRIM(LTRIM(str_val));
> if (LENGTH (str_val) > 0) then
>
> if (LENGTH (str_return) > 0) then
> str_return := str_return || delim;
> end if;
>
> str_return := str_return || str_val;
>
> end if;
>
>
> end loop;
>
> Close empCursor;
> return (str_return);
>
> end UF_EMPLIST_ORDER;

Solved it perhaps but with horribly inefficient code. There is almost no place in Oracle (9i or above) where a cursor loop is a good idea.

Take a look at array processing instead. You will find examples in Morgan's Library at www.psoug.org. Click on Bulk Binding.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Sun Mar 12 2006 - 12:37:22 CST