Geek asks and answers

PL / SQL refactoring: many cursors with the same number of rows (theoretically)

I am writing PL / SQL and found that I am getting into a repeating pattern:

cursor c_curs1 is
   select a, b, c
   from (...) big_subquery_1
   where big_subquery_1.a_ind = 'Y'

cursor c_curs2 is
   select a, b, c
   from (...) big_subquery_2
   where big_subquery_2.b_ind = 'R'

cursor c_curs3 is
   select a, b, c
   from (...) big_subquery_3
   where big_subquery_3.c_ind = 'M'

...
type t_curs1_tab is table of c_curs1;
type t_curs2_tab is table of c_curs2;
type t_curs3_tab is table of c_curs3;
...
v_curs1_results t_curs1_tab := t_curs1_tab();
v_curs2_results t_curs2_tab := t_curs2_tab();
v_curs3_results t_curs3_tab := t_curs3_tab();

Then, when processing the results, I have this code:

    open c_curs1;
    fetch c_curs1 bulk collect into v_curs1_results;
    close c_curs1;

    if v_curs1_results.first is not null and v_curs1_results.last is not null then
       for i in v_curs1_results.first .. v_curs1_results.last loop
           /*Do something with field a in the results
             Do something with field b in the results
             Do something with field c in the results*/
           ....
       end loop;
    end if;

The code in the processing loop is the same for all cursors, since all 3 cursors return a,b,c- the only difference the cursor refers to. I wanted to reorganize this into some sort of shared processor with a result set, but I'm stuck here:

procedure sp_process_collection(in_collection t_curs1_tab) is ...

v_curs1_results, v_curs2_results PLS-00306 wrong number of types or arguments.... , ? ( , ), , , . , , PL/SQL. , PL/SQL (, , Java/# ), , , .

( Oracle 10g)

+3
4

, TYPE. . TYPE, , .

cursor c_curs1 is
   select a, b, c
   from (...) big_subquery_1
   where big_subquery_1.a_ind = 'Y'

cursor c_curs2 is
   select a, b, c
   from (...) big_subquery_2
   where big_subquery_2.b_ind = 'R'

cursor c_curs3 is
   select a, b, c
   from (...) big_subquery_3
   where big_subquery_3.c_ind = 'M'

...
type t_curs_tab is table of c_curs1;
...
v_curs_results t_curs_tab := t_curs_tab();
...
open c_curs1;
fetch c_curs1 bulk collect into v_curs_results;
close c_curs1;
...
open c_curs2;
fetch c_curs2 bulk collect into v_curs_results;
close c_curs2;
...
open c_curs3;
fetch c_curs3 bulk collect into v_curs_results;
close c_curs3;
+4

, TYPE , . :

CREATE TYPE TRIPLE_TYPE IS OBJECT
(
  a NUMBER,
  b NUMBER,
  c NUMBER
);

TRIPLE_TYPE:

CREATE TYPE TRIPLES_TYPE IS TABLE OF TRIPLE_TYPE;

, TRIPLE_TYPE :

DECLARE

  cursor c_curs1 is
    select TRIPLE_TYPE(a, b, c)
    from (...) big_subquery_1
    where big_subquery_1.a_ind = 'Y';

  triples TRIPLES_TYPE;

BEGIN
  OPEN c_curs1;
  FETCH c_curs1 BULK COLLECT INTO triples;
  CLOSE c_curs1;
END;

, TRIPLES_TYPE:

procedure sp_process_collection(in_collection TRIPLES_TYPE) is ...
+2

REF CURSOR?

- :

CREATE PROCEDURE sp_process_cursor(in_cursor SYS_REFCURSOR)

/* you don't need to open the cursor, it already open */
FETCH in_cursor BULK COLLECT INTO v_cursor_results;
IF v_cursor_results.first is not null and v_cursor_results.last IS NOT NULL THEN
  FOR i in v_cursor_results.FIRST .. v_cursor_results.LAST LOOP
  ...
  END LOOP;
END IF;

:

OPEN c_curs1;
sp_process_cursor(c_curs1);
CLOSE c_curs1;

OPEN c_curs2;
sp_process_cursor(c_curs2);
...
+2

, 3 , "big_subquery" , . , a, b c. , :

CREATE or replace procedure my_proc IS

  cursor c_curs1 is
   select a, b, c
   from (...) big_subquery_1
   where big_subquery_1.a_ind = 'Y';

  cursor c_curs2 is
   select a, b, c
   from (...) big_subquery_2
   where big_subquery_2.b_ind = 'R';

  cursor c_curs3 is
   select a, b, c
   from (...) big_subquery_3
   where big_subquery_3.c_ind = 'M';

  -- local procedure to handle processing of a,b,c "types"
  procedure process_type(i_type in varchar2) is
  begin
    -- do something great here
  end process_type;

BEGIN
  for rec in c_curs1
  loop
    process_type(rec.a);
    -- do something else
    process_type(rec.b);
    -- do something more
    process_type(rec.c);
    -- do something even more
  end loop;

  for rec in c_curs2
  loop
    -- do processing for this cursor
    -- use process_type like above
  end loop;

  for rec in c_curs3
  loop
    -- do processing for this cursor
    -- use process_type like above
  end loop;

END;

, 3 (varchar2, date number), process_type, 3 (a, b ). - :

procedure process_type(i_type_a in varchar2, i_type_b in date, i_type_c in number) is
  begin
    -- do something great here
  end process_type;

, .

0

More articles:


All Articles