Re: PL/SQL Question

From: Michael Bialik <michael_bialik_at_my-deja.com>
Date: Mon, 18 Sep 2000 20:21:20 GMT
Message-ID: <8q5tf6$adq$1@nnrp1.deja.com>

Hi.

 It is possible by using dynamic SQL ( either DBMS_SQL for Oracle 7.x  and 8.0 or Native Dynamic SQL for 8i ).  I couldn't check the code, but I thing that following idea will work:

 CREATE PROCEDURE ...
  CURSOR tab_a_crs IS SELECT * FROM tab_a;   TYPE my_tab_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;   my_tab my_tab_type;
  sql_stmt VARCHAR2(400) := NULL;
 BEGIN

  my_tab(1) := 'cl_fee';
  my_tab(2) := 'gi_fee';
  my_tab(3) := 'fl_fee';

  ...
  my_tab(9) := 'zz_fee';

  FOR tab_a_rec IN tab_a_crs LOOP
   sql_stmt := 'UPDATE tab_B SET ';
   sql_stmt := sql_stmt || my_tab(to_number(tab_a_rec.code_1)) || ' = '

               || to_char(tab_a_rec.amt_1) || ', ' ||
               my_tab(to_number(tab_a_rec.code_2)) || ' = '
               || to_char(tab_a_rec.amt_2) || ', ' ||
               my_tab(to_number(tab_a_rec.code_3)) || ' = '
               || to_char(tab_a_rec.amt_3) || ', ' ||
               my_tab(to_number(tab_a_rec.code_4)) || ' = '
               || to_char(tab_a_rec.amt_4) || ', ' ||
               my_tab(to_number(tab_a_rec.code_5)) || ' = '
               || to_char(tab_a_rec.amt_5) || ', ' ||
               my_tab(to_number(tab_a_rec.code_6)) || ' = '
               || to_char(tab_a_rec.amt_6) || ', ' ||
               my_tab(to_number(tab_a_rec.code_7)) || ' = '
               || to_char(tab_a_rec.amt_7) || ' ' ;

    /* Concatenate WHERE condition */
    sql_stmt := sql_stmt || 'WHERE ...';

    /* Execute created dynamic SQL stmt (with DBMS_SQL or Native(8i))*/

  END LOOP;
 END;  I am not sure that performance will be good enough in that case.  You will have to deal with bind variables as well.

 HTH. Michael.

In article <_tsx5.53$eX6.16745_at_nnrp2.sbc.net>,   mGOplexAWAY_at_swbell.net wrote:
> I am attempting to create a PL/SQL procedure and I am trying to see
 if there
> is a way of accomplishing what I am trying to do without having to
 write a
> very long IF THEN ELSE statement.
>
> TABLE A has the following fields ...
>
> code_1 VARCHAR2(1)
> code_2 VARCHAR2(1)
> ...
> code_7 VARCHAR2(1)
> amt_1 NUMBER(7,2)
> amt_2 NUMBER(7,2)
> ...
> amt_7 NUMBER(7,2)
>
> TABLE B has the fields like ...
> cl_fee NUMBER(7,2)
> gi_fee NUMBER(7,2)
> fl_fee NUMBER(7,2)
> ...
> zz_fee NUMBER(7,2)
>
> In the code_x fields there can be a value between '1' and '9'. For
 each
> record, it can be completely different. Depending on the value in each
> code_x field, I need to update the fee fields in Table B with the
> corresponding amount from the amount_x fields in Table A.
>
> For example,
>
> Table A
> -----------
> code_1 = 4 amt_1 = 120.00
> code_2 = 1 amt_2 = 175.00
> code_3 = 2 amt_3 = 100.00
>
> If '4' corresponds to fl_fee, and '1' corresponds to cl_fee and '2'
> corresponds to zz_fee then I need to update TABLE B such that
>
> cl_fee = 175.00
> fl_fee = 120.00
> zz_fee = 100.00
>
> With 7 buckets that can have 9 possible values, I am thinking that I
 would
> need 63 different IF conditions to cover all possibilities. Does
 anyone
> know if a more efficient way of accomplishing this than to write all
 those
> IFs?
>
> TIA
>
> David
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Sep 18 2000 - 15:21:20 CDT