Oracle PL/SQL for DBAs

Cursors

We looked carefully at cursors in Chapter 2, and here they are again! How do cursors and table functions interact? Let’s start by looking at a non-pipelined version of a table function.

    /* File on web: date_parser.sql */
    CREATE OR REPLACE FUNCTION date_parse ( p_curs SYS_REFCURSOR )
                      RETURN order_date_t AS

      v_order_rec orders%ROWTYPE;
      v_ret_val   order_date_t := order_date_t(  );

    BEGIN

      -- for every order in the cursor...
      LOOP
        FETCH p_curs INTO v_order_rec;
        EXIT WHEN p_curs%NOTFOUND;

        -- extend the array by 3 and populate with cmoponents of the
        -- orders creation, assignment and close date
        v_ret_val.EXTEND(3);
        v_ret_val(v_ret_val.LAST - 2) := order_date_o(v_order_rec.order_number,
                                                      'O',
                                        TO_CHAR(v_order_rec.create_date,'YYYY'),
                                        TO_CHAR(v_order_rec.create_date,'Q'),
                                        TO_CHAR(v_order_rec.create_date,'MM'));
        v_ret_val(v_ret_val.LAST - 1) := order_date_o(v_order_rec.order_number,
                                                      'A',
                                        TO_CHAR(v_order_rec.assign_date,'YYYY'),
                                        TO_CHAR(v_order_rec.assign_date,'Q'),
                                        TO_CHAR(v_order_rec.assign_date,'MM'));
        v_ret_val(v_ret_val.LAST) := order_date_o(v_order_rec.order_number,
                                                  'C',
                                        TO_CHAR(v_order_rec.close_date,'YYYY'),
                                        TO_CHAR(v_order_rec.close_date,'Q'),
                                        TO_CHAR(v_order_rec.close_date,'MM'));
      END LOOP;  -- every order in ths cursor

      RETURN(v_ret_val);

    END;

And here are the results when three orders are queried.

 ORDER_NUMBER D YEAR QUARTER MONTH ------------ - ---------- ---------- ---------- 1 O 2005 3 8 1 A 2005 3 8 1 C 2005 3 8 2 O 2005 4 10 2 A 2005 4 10 2 C 2005 4 10 3 O 2005 4 12 3 A 2005 ...