3 Replies Latest reply: Jul 15, 2012 11:51 PM by indra budiantho RSS

    how to write Function returing table or set of rows.

    947771
      Dear all

      1) have found 3 way to get table from function
      please tel me which one is better and in which situation?

      2) And also tel me is it possible to make store proc insted of function , if yes then will it be used like table in from clause like function?


      ------------------------------------------------------------------------- ex first.
      create table my_table
      (prodid number, a varchar2(1), b varchar2(1),
      c varchar2(1), d varchar2(1), e varchar2(1));

      create type my_tab_type is object
      (prodid number, a varchar2(1), b varchar2(1),
      c varchar2(1), d varchar2(1), e varchar2(1))
      .
      /

      create type my_tab_type_coll is table of my_tab_type;
      /

      create or replace function get_some_data (p_val in number)
      return my_tab_type_coll pipelined is
      begin
      FOR i in (select * from my_table where prodid=p_val) loop
      pipe row(my_tab_type(i.prodid,i.a,i.b,i.c,i.d,i.e));
      end loop;
      return;
      end;
      /

      SELECT * FROM table(get_Some_Data(3));

      ----------------------------------------------------------------------------ex second
      step1)
      one can create globle tem table "Tlb_3". then can make a package like fllowing
      create or replace
      PACKAGE pk1
      AS
      TYPE T_type IS TABLE OF Tlb_3%ROWTYPE;
      END;

      step 2) making a function like ex fisrt and using globle temp table and package to return value.

      ------------------------------------------------------------------------------ex three

      CREATE OR REPLACE TYPE results_typ AS OBJECT
      (id     INT,
           name     VARCHAR2(15),
           parentid INT,
           departmentid INT,
           lvl     INT);
      /
      CREATE OR REPLACE TYPE results_tab AS TABLE OF results_typ;

      CREATE OR REPLACE FUNCTION getChildItems
      (p_startingID INT)
      RETURN results_tab
      AS
      v_results results_tab := results_tab();
      BEGIN
      FOR rc IN
           (SELECT id, name, parentid, departmentid, LEVEL AS lvl
           FROM     people
           START WITH id = p_startingID
           CONNECT BY PRIOR id = parentid)
      LOOP
           v_results.EXTEND;
           v_results (v_results.COUNT) :=
           resultS_typ (rc.id, rc.name, rc.parentid, rc.departmentid, rc.lvl);
      END LOOP;
      RETURN v_results;
      END getChildItems;
      ---------------------------------------------------------------------------------------------
      Please also tel me if there is any other solution?

      yours sincerely.

      Edited by: 944768 on Jul 15, 2012 9:24 PM

      Edited by: 944768 on Jan 2, 2013 4:14 AM

      Edited by: 944768 on Jan 2, 2013 4:15 AM
        • 1. Re: how to write Function returing table or set of rows.
          HuaMin Chen
          Use a SP to return a refcursor like
          CREATE PACKAGE "HR"."HR_DATA" IS -- Declare types, variables, constants, exceptions, cursors,
          -- and subprograms that can be referenced from outside the package.

          PROCEDURE "GETCURSORS" (
          "DEP_ID" IN NUMBER,
          "EMPLOYEES_C" OUT SYS_REFCURSOR,
          "DEPENDENTS_C" OUT SYS_REFCURSOR);

          END "HR_DATA";

          CREATE PACKAGE BODY "HR"."HR_DATA" IS

          -- Implement subprograms, initialize variables declared in package
          -- specification.

          -- Make private declarations of types and items, that are not accessible
          -- outside the package
          PROCEDURE "GETCURSORS" (
          "DEP_ID" IN NUMBER,
          "EMPLOYEES_C" OUT SYS_REFCURSOR,
          "DEPENDENTS_C" OUT SYS_REFCURSOR) IS

          -- Declare constants and variables in this section.

          BEGIN -- executable part starts here

          NULL;

          -- EXCEPTION -- exception-handling part starts here

          END "GETCURSORS";

          END "HR_DATA";

          Edited by: HuaMin Chen on Jul 16, 2012 12:37 PM
          • 2. Re: how to write Function returing table or set of rows.
            Billy~Verreynne
            All these methods are wrong for the basic purpose of returning data. The most efficient, performant and scalable way, is returning the SQL cursor handle directly to the caller.

            E.g.
            create or replace procedure FunkyProc( c out sys_refcursor ) is
            begin
              open c for select ... from ...;
            end;
            Any other method requires the SQL cursor to be consumed first by PL/SQL, data fetched from the SQL engine into the PL/SQL engine, and then the PL/SQL engine passing that data on to the client. Sending data on a detour through the PL/SQL engine, on the way to the client, is not a good idea. The more moving parts there are, the slower the process, the more complex the process, and the less robust the process.

            The methods you listed have their specific areas of application. Pipeline tables for example are used for complex data transformation - or parallelising PL/SQL code via SQL engine's Parallel Query feature. Bulk fetching reduce context switching when row-by-row processing is required (individual rows fetched into the PL/SQL engine). Etc.

            However, using a cursor to fetch data via SQL is unavoidable. All SQL are parsed and executed as cursors. Thus it makes a lot more sense to simply pass that cursor handle to the client to consume, than to add a PL/SQL layer in-between the SQL cursor and the client.
            • 3. Re: how to write Function returing table or set of rows.
              indra budiantho
              where do you want to use the result?

              if you want to use it in another loop, suppose:

              your_assoc_arrays = f(); -- get your results
              for (select...) loop
              your_assoc_arrays()... --
              end loop;

              then it is better to use associative arrays like above, instead of another select below for performance reason:

              for (select..) loop
              for(select...) loop
              end loop;
              end loop;


              Edited by: Indra Budiantho on Jul 15, 2012 9:44 PM

              Edited by: Indra Budiantho on Jul 15, 2012 9:47 PM