Search code examples
oracleplsqlcursor

oracle create function with cursor which sums another function

I have one situation with function and cursors here. I got function Fun_1 which returns me ref_cursor and this type of data :

<SYSTEM_NAME=SPORT1,CASH_IN=1, CASH_OUT=0, NET_WIN=1, VAUCHER=0>, <SYSTEM_NAME=SPORT2,CASH_IN=279,CASH_OUT=57.86,NET_WIN=278.71, VAUCHER=0.29>,<SYSTEM_NAME=SPORT3,CASH_IN=9.22,CASH_OUT=0.5, NET_WIN=9.22, VAUCHER=0>;

Now I want to create function which will return SUM(CASH_IN), SUM(CASH_OUT), SUM(NET_WIN) and SUM(VAUCHER) from Fun1 function.

What I have to do to achieve this ? Thanks

Solution

  • It is not clear if <SYSTEM_NAME=SPORT1 , CASH_IN=1 etc are individual columns or part of the whole string returned from your function. if they are part of whole string, you may use REGEXP_SUBSTR like in the query below. Assumption here is that all values after = are going to be integers or decimals.

    If they are individual column values, then you could simply use SUBSTR(s,INSTR(s,'=') + 1) on each column to get the value after '='.

    SQL Fiddle

    WITH c
     AS (SELECT REGEXP_SUBSTR(s, 'CASH_IN=([0-9.]+)',  1, 1, NULL, 1)  a,
                REGEXP_SUBSTR(s, 'CASH_OUT=([0-9.]+)', 1, 1, NULL, 1) b,
                REGEXP_SUBSTR(s, 'NET_WIN=([0-9.]+)',  1, 1, NULL, 1)  c,
                REGEXP_SUBSTR(s, 'VAUCHER=([0-9.]+)',  1, 1, NULL, 1)  d
         FROM   t)
SELECT SUM(a) AS sum_CASH_IN,
       SUM(b) AS sum_CASH_OUT,
       SUM(c) AS sum_NET_WIN,
       SUM(d) AS sum_VAUCHER
FROM   c;

    Results:

    | SUM_CASH_IN | SUM_CASH_OUT | SUM_NET_WIN | SUM_VAUCHER |
|-------------|--------------|-------------|-------------|
|      289.22 |        58.36 |      288.93 |        0.29 |