Re: What's wrong in my code, Any idea

From: andrewst <member14183_at_dbforums.com>
Date: Thu, 17 Jul 2003 11:33:46 +0000
Message-ID: <3121087.1058441626@dbforums.com>

Originally posted by Karsten Farrell
> Hi Areon Ku, thanks for writing this:
> > My code may not be complete... Its just a sample --Actually What
> i,m trying
> > to achieve here is to pick one of the value from category table
> and use that
> > value to calculate distinct values from another table and do
> some
> > calcualions on that and insert these calculated values into
> another summary
> > table and repeat the same thing for next value in the category
> table..
> > The problem i,m facing is i,m not able to use that variable as
> input to
> > cursor and then process other statements based on that
> variable.
> > Hope i,m able to explain the situation here.
> >
> > Thanks
> > !!
>
> If I understand you correctly, why not try something like this (I
> probably haven't even come close to the table and column names you'll
> need):
>
> CREATE OR REPLACE PROCEDURE TestCountSum (
> p_organ_tx varchar2,
> p_temp_name varchar2,
> p_category_tx varchar2
> ) IS
> --
> cursor c_category is
> select some_cat_col
> from category
> where category_tx = p_category_tx;
> --
> cursor c_second_table (
> p_some_param varchar2
> ) is
> select some_num_col, some_text_col
> from sum_demo_surg_block
> where some_other_column = p_some_param;
> --
> v_organ_tx number;
> v_value_tx varchar2(32);
> BEGIN
> --
> -- for each category record...
> for r_category in c_category loop
> --
> -- read corresponding row in second_table
> for r_second_table in c_second_table (
> r_category.some_cat_col
> ) loop
> --
> -- calculate some stuff
> v_organ_tx := r_second_table.some_num_col * 10;
> v_value_tx := r_second_table.some_text_col
> || to_char(v_organ_tx);
> --
> -- insert calc'd values in table.
> insert into sum_disc_tx (
> organ_tx
> , sum_id
> , datestamp
> , value_tx
> )
> values (
> v_organ_tx
> , seq_sum_id.nextval
> , sysdate
> , v_value_tx
> );
> end loop;
> end loop;
> END TestCountSum;
>
> Just don't let Tom Kyte (see
> http://asktom.oracle.com/http://asktom.oracle.com) see
> this because
> he'll have a stroke since I'm not using bind variables ... but
> it's just
> an example to show the general layout.
> --
> [:%s/Karsten Farrell/Oracle DBA/g]

And if possible, avoid doing it all record-by-record in cursor loops, like this:

[php]CREATE OR REPLACE PROCEDURE TestCountSum (

  p_organ_tx varchar2,
  p_temp_name varchar2,
  p_category_tx varchar2

) IS

BEGIN   insert into sum_disc_tx
  ( organ_tx

  , sum_id
  , datestamp
  , value_tx

  )
  select t2.some_num_col * 10

       , seq_sum_id.nextval
       , sysdate
       , t2.some_text_col || to_char(t2.some_num_col * 10)
  from   category t1
       , sum_demo_surg_block t2

  where t2.some_other_column = t1.some_cat_col   and t1.category_tx = p_category_tx;

END;
/[/php]

--
Posted via http://dbforums.com

Received on Thu Jul 17 2003 - 06:33:46 CDT