Re: create type with index
Date: Mon, 16 Mar 2009 08:06:37 -0700 (PDT)
Message-ID: <58597e67-7cd1-4d8d-bcbd-0c0c5c62ca83_at_v39g2000yqm.googlegroups.com>
On Mar 16, 3:42 pm, ddf <orat..._at_msn.com> wrote:
> On Mar 16, 2:36 am, NOVA <nova1..._at_gmail.com> wrote:
>
>
>
>
>
> > On Mar 15, 10:04 pm, ddf <orat..._at_msn.com> wrote:
>
> > > Comments embedded.
>
> > > On Mar 15, 2:00 am, NOVA <nova1..._at_gmail.com> wrote:
>
> > > > Dear all,
>
> > > > I face a problem with this package
>
> > > > what is the target?
>
> > > Who can say? You're wasting effort trying to use this type when it's
> > > completely unnecessary.
>
> > > > I have table "xch_t" (exchange rate table) to convert the currency to
> > > > 'SAR'.
>
> > > > for example:
> > > > 1 USD = 3.75 SAR
> > > > 1 EUR = 4.84859 SAR
>
> > > > so the table xch shown below:
> > > > CUCD1 CVRA
> > > > USD 3.75
> > > > JPY 0.0381255
> > > > EUR 4.84859
>
> > > > I have a lot of procedures use the exchange, so I want to make it an
> > > > array by create type "xch_type" but the index will by CUCD1.
>
> > > Why???
>
> > > > let us start togather. execute all lines below.
> > > > note: this is an example to be easier to understand.
>
> > > > CREATE TABLE XCH_T -- line 1
> > > > (
> > > > CUCD1 CHAR(4 BYTE) NOT NULL,
> > > > CVRA NUMBER(15,7) NOT NULL
> > > > );
> > > > insert into XCH_T values ('USD ', 3.75);
> > > > insert into XCH_T values ('JPY ', 0.0381255);
> > > > insert into XCH_T values ('EUR ',4.84859);
>
> > > > CREATE TABLE aa
> > > > (
> > > > CUCD1 CHAR(4 BYTE) NOT NULL,
> > > > CVRA NUMBER(15,7) NOT NULL,
> > > > conv_result NUMBER(15,7) NOT NULL
> > > > );
>
> > > > create or replace package package_name as
>
> > > The type and the variable are unnecessary. I would stop trying to use
> > > them.
>
> > > > type xch_type is table of
> > > > number(15,7)
> > > > index by varchar2(4);
> > > > xch xch_type;
> > > > procedure proc_t;
> > > > end package_name;
> > > > /
>
> > > The package specification compiles successfully; your body does not.
>
> > > > create or replace package body package_name as -- line 25
> > > > procedure proc_t as
> > > > begin
>
> > > xch is NOT a function, it's a type, and it isn't part of the XCH_T
> > > table, so why are you trying to select it?
>
> > > > insert into aa
> > > > select cucd1, CVRA, xch(cucd1) from xch_t; -- line 29
> > > > end proc_t;
>
> > > This is not how to load such a table.
>
> > > > begin
> > > > select cvra into xch(cucd1) from xch_t; -- line 33
> > > > end package_name;
> > > > /
>
> > > > it show my error in line29 and 33.
>
> > > > Can you help me.
>
> > > Your body, if you REALLY want to use this type, needs to be written
> > > this way:
>
> > > create or replace package body package_name as -- line 25
>
> > > procedure proc_t as
>
> > > cursor get_xch_data is
> > > select cucd1, cvra from xch_t;
>
> > > begin
> > > for xrec in get_xch_data loop
> > > -- Populate the table
> > > xch(xrec.cucd1):= xrec.cvra;
> > > -- Use the table to populate another
> > > table (busy work, in my opinion)
> > > insert into aa
> > > values (xrec.cucd1, xrec.cvra, xch(xrec.cucd1));
> > > end loop;
>
> > > end proc_t;
>
> > > end package_name;
> > > /
>
> > > The above code, in its entirety, can be replaced with:
>
> > > create or replace package package_name as
>
> > > procedure proc_t;
>
> > > end package_name;
> > > /
>
> > > create or replace package body package_name as -- line 25
>
> > > procedure proc_t as
>
> > > cursor get_xch_data is
> > > select cucd1, cvra from xch_t;
>
> > > begin
> > > for xrec in get_xch_data loop
>
> > > insert into aa
> > > values (xrec.cucd1, xrec.cvra, xrec.cvra);
>
> > > end loop;
>
> > > end proc_t;
>
> > > end package_name;
> > > /
>
> > > Your array, as I see it, is useless here and it's only causing you
> > > problems.
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -
>
> > I understand what do you mean.
>
> > I try to use array as a function.
>
> > look at last change after your advice
>
> > create or replace package package_name as
> > type xch_type is table of
> > number(15,7)
> > index by varchar2(4);
> > xch xch_type;
> > procedure proc_t;
> > end package_name;
> > /
>
> > create or replace package body package_name as
> > cursor get_xch_data is
> > select cucd1, cvra from xch_t;
> > procedure proc_t as
> > begin
> > --insert into aa select cucd1, CVRA, xch(cucd1) from xch_t; --
> > line 6
> > insert into aa select cucd1, CVRA, xch('USD') from xch_t; --
> > line 7
> > end proc_t;
> > begin
> > for xrec in get_xch_data loop
> > xch(xrec.cucd1):= xrec.cvra;
> > end loop;
> > end package_name;
> > /
>
> > I still want to use an array. if you look at line 6 there is error
> > here "xch(cucd1)"
> > and in line 7 is successfull but the different is line 6 is dynamic
> > and line 7 is static.
>
> > How can I make line t successfully.
>
> > thx alot.- Hide quoted text -
>
> > - Show quoted text -
>
> You cannot get 'line 6' to work as you have it written. Please
> understand this. You would need to write a function that uses that
> array and passes it a value from a select statement to get something
> like line 6 to work. I'll leave it to you to write the function.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
you right,
I add function as below
function xch_f(cucd1 char, amont number) return number as begin
return xch(cucd1);
end xch_f;
and change lin 6 to
insert into aa select cucd1, CVRA, xch_f(cucd1) from xch_t;
It work successfully.
by this way I increase the performance to load into a table.
let me explain:
I have big query inserted to table and there is function read from
xch_t (this table is very large because it is history data, that means
there is column for date and I always take sysdate) by pass currency
code to return number.
but there are a lot of exhange rate so it need to use function many of
times. on other hand one query inside the function executed many
times.
so I find solution by use with statment that is query like function query except no filter on currency code. and inside big query I make select statment from "WITH" by add filter for current code.
what is the different:
- By function: it take 28:10 min becuase xch_t executed many
times, same query
- By With statment: 4:30 min becuase xch_t executed one time,
and filterd inside the big query
- By array (after your help): 7:20 min becuase xch_t executed one
time, and pass the currency code to function then to array.
What do you think?
I still try to find more performance at least like (by with statment).
Using with statment has one disadvantage that it make the big query more bigger and difficult to read when I want to make change.
I waiting for your advice.
thx Received on Mon Mar 16 2009 - 10:06:37 CDT