RE: reference cursors
- From: <Stephen.Lee@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 30 Jan 2004 13:05:20 -0600
I'll have to look at this when I am more awake.
But ...
*I* am not trying to do anything. Somebody ELSE is trying to do something,
and it's stinking up the place, and they want me to do SOMETHING to make it
go faster. So I did SOMETHING. For what it's worth, here is the resulting,
big, ugly thing. I trust you will interpret from my naming "conventions"
what I thought about it. I not real inclined to fiddle with the original
query because then I would end up owning it. (Where's my ten-foot pole.)
Enjoy.
create or replace package RateRetrievalMeth as
type doink_type is record (
location_code time_mileage_rate.location_code%type,
car_type time_mileage_rate.car_type%type,
rate_code time_mileage_rate_code.rate_code%type,
plan_code time_mileage_rate_code.plan_code%type,
effective_date time_mileage_rate.effective_date%type,
discontinue_date time_mileage_rate.discontinue_date%type,
tmrrate time_mileage_rate.time_mileage_rate%type,
extra_day_time_mile_rate_amt
time_mileage_rate.extra_day_time_mile_rate_amt%type,
extra_hour_time_mile_rate_amt
time_mileage_rate.extra_hour_time_mile_rate_amt%type,
mile_rate time_mileage_rate.mile_rate%type,
mile_allow_nbr time_mileage_rate.mile_allow_nbr%type,
extra_day_mile_allow_nbr
time_mileage_rate.extra_day_mile_allow_nbr%type,
extra_hour_mile_allow_nbr
time_mileage_rate.extra_hour_mile_allow_nbr%type,
pickup_return_rule time_mileage_rate_code.pickup_return_rule%type,
text_rule time_mileage_rate_code.text_rule%type,
guarantee_rule time_mileage_rate_code.guarantee_rule%type,
deposit_rule time_mileage_rate_code.deposit_rule%type,
one_way_rule time_mileage_rate_code.one_way_rule%type,
point_of_sale_rule time_mileage_rate_code.point_of_sale_rule%type,
inclusive_rule time_mileage_rate_code.inclusive_rule%type,
description_text time_mileage_rate_code.description_text%type,
minimum_adv_required_days
time_mileage_rate_code.minimum_adv_required_days%type,
minimum_adv_required_hrs
time_mileage_rate_code.minimum_adv_required_hrs%type,
minimum_rate_code time_mileage_rate_code.minimum_rate_code%type,
minimum_time_mileage_plan_code
time_mileage_rate_code.minimum_time_mileage_plan_code%type,
category_code_clone time_mileage_rate.category_code_clone%type,
sell_rule time_mileage_rate_code.sell_rule%type,
rental_agreement_segment_code
time_mileage_rate_code.rental_agreement_segment_code%type,
currency_code time_mileage_rate_code.currency_code%type,
commission_pct time_mileage_rate_code.commission_pct%type,
commission_amt time_mileage_rate_code.commission_amt%type,
allow_discount_flag time_mileage_rate_code.allow_discount_flag%type
);
type doink_table is table of doink_type;
--cursor MultiRates is
-- SELECT location_code, car_type,tmri.rate_code tmrirate,tmri.plan_code
tmricode,tmr.effective_date tmreffdate,
-- tmr.discontinue_date tmrdisdate,tmr.time_mileage_rate
tmrrate,extra_day_time_mile_rate_amt,extra_hour_time_mile_rate_amt,
--
mile_rate,mile_allow_nbr,extra_day_mile_allow_nbr,extra_hour_mile_allow_nbr,
pickup_return_rule,
--
text_rule,guarantee_rule,deposit_rule,one_way_rule,point_of_sale_rule,inclus
ive_rule,description_text,
--
minimum_adv_required_days,minimum_adv_required_hrs,minimum_rate_code,minimum
_time_mileage_plan_code,
--
tmr.category_code_clone,sell_rule,rental_agreement_segment_code,currency_cod
e,commission_pct,
-- commission_amt,allow_discount_flag
-- FROM time_mileage_rate tmr, time_mileage_rate_code tmri;
cursor MultiRates is select * from duct_tape;
type doink_cursor is ref cursor return MultiRates%rowtype;
-- type doink_cursor is ref cursor return doink_type;
procedure GetRates (
sLocCode in time_mileage_rate.location_code%type,
nMinAdvReqDays in
time_mileage_rate_code.minimum_adv_required_days%type,
nMinAdvReqHrs in time_mileage_rate_code.minimum_adv_required_hrs%type,
sEffDate in varchar2,
sDisconDate in varchar2,
sTMRateCode in varchar2,
sCatagory in varchar2,
sPlanCode in varchar2,
sCarType in varchar2,
cRateRet out doink_cursor,
nReturn out number);
end;
/
create or replace package body RateRetrievalMeth as
procedure GetRates (
sLocCode in time_mileage_rate.location_code%type,
nMinAdvReqDays in
time_mileage_rate_code.minimum_adv_required_days%type,
nMinAdvReqHrs in time_mileage_rate_code.minimum_adv_required_hrs%type,
sEffDate in varchar2,
sDisconDate in varchar2,
sTMRateCode in varchar2,
sCatagory in varchar2,
sPlanCode in varchar2,
sCarType in varchar2,
cRateRet out doink_cursor,
nReturn out number
) as
the_table doink_table;
begin
execute immediate 'truncate table xxxxxx.duct_tape';
SELECT /*+ NO_EXPAND INDEX (tmr TMR_LCT_FK_IDX) INDEX (tmri
TMRC_TEST_PROC) cardinality (t4, 10) cardinality (t3, 10) cardinality (t2,
10) cardinality (t1, 10) */
location_code,
car_type,
tmri.rate_code tmrirate,
tmri.plan_code tmricode,
tmr.effective_date tmreffdate,
tmr.discontinue_date tmrdisdate,
tmr.time_mileage_rate tmrrate,
extra_day_time_mile_rate_amt,
extra_hour_time_mile_rate_amt,
mile_rate,
mile_allow_nbr,
extra_day_mile_allow_nbr,
extra_hour_mile_allow_nbr,
pickup_return_rule,
text_rule,
guarantee_rule,
deposit_rule,
one_way_rule,
point_of_sale_rule,
inclusive_rule,
description_text,
minimum_adv_required_days,
minimum_adv_required_hrs,
minimum_rate_code,
minimum_time_mileage_plan_code,
tmr.category_code_clone,
sell_rule,
rental_agreement_segment_code,
currency_code,
commission_pct,
commission_amt,
allow_discount_flag
BULK COLLECT INTO the_table
FROM time_mileage_rate tmr, time_mileage_rate_code tmri
WHERE tmr.rate_code = tmri.rate_code
AND tmr.plan_code = tmri.plan_code
AND tmr.location_code = sLocCode
AND tmri.minimum_adv_required_days <= nMinAdvReqDays
AND tmri.minimum_adv_required_hrs <= nMinAdvReqHrs
AND tmr.effective_date <= TO_DATE (sEffDate, 'YYYY-MM-DD')
AND (tmr.discontinue_date >= TO_DATE (sDisconDate, 'YYYY-MM-DD')
OR tmr.discontinue_date IS NULL
)
AND (tmr.rate_code IN
(select /*+ cardinality (t1, 10) */ column_value from table
(in_list(sTMRateCode)) t1 where rownum > 0)
OR tmr.category_code_clone IN
(select /*+ cardinality (t2, 10) */ column_value from table
(in_list(sCatagory)) t2 where rownum > 0)
)
AND tmr.plan_code IN
(select /*+ cardinality (t3, 10) */ column_value from table
(in_list(sPlanCode)) t3 where rownum > 0)
AND tmr.car_type IN
(select /*+ cardinality (t4, 10) */ column_value from table
(in_list(sCarType)) t4 where rownum > 0)
ORDER BY location_code, tmri.rate_code, tmri.plan_code, tmr.car_type;
for i in 1 .. the_table.count loop
insert into duct_tape values(
the_table(i).location_code,
the_table(i).car_type,
the_table(i).rate_code,
the_table(i).plan_code,
the_table(i).effective_date,
the_table(i).discontinue_date,
the_table(i).tmrrate,
the_table(i).extra_day_time_mile_rate_amt,
the_table(i).extra_hour_time_mile_rate_amt,
the_table(i).mile_rate,
the_table(i).mile_allow_nbr,
the_table(i).extra_day_mile_allow_nbr,
the_table(i).extra_hour_mile_allow_nbr,
the_table(i).pickup_return_rule,
the_table(i).text_rule,
the_table(i).guarantee_rule,
the_table(i).deposit_rule,
the_table(i).one_way_rule,
the_table(i).point_of_sale_rule,
the_table(i).inclusive_rule,
the_table(i).description_text,
the_table(i).minimum_adv_required_days,
the_table(i).minimum_adv_required_hrs,
the_table(i).minimum_rate_code,
the_table(i).minimum_time_mileage_plan_code,
the_table(i).category_code_clone,
the_table(i).sell_rule,
the_table(i).rental_agreement_segment_code,
the_table(i).currency_code,
the_table(i).commission_pct,
the_table(i).commission_amt,
the_table(i).allow_discount_flag
);
end loop;
commit;
open cRateRet for select * from duct_tape;
nReturn := 0;
exception
when others then
nReturn := sqlcode;
end;
end;
/
-----Original Message-----
I know Jonathan Lewis had some other suggestions. I am still going to add my
two cents.
Maybe I don't understand what you're trying to do, but is this what you're
looking for?
drop table t1 ;
drop table t2 ;
drop type t_tab_type ;
drop type t_type ;
drop package my_types ;
drop function f ;
create table t1 (n number, v varchar2 (30)) ;
create table t2 (n number, v varchar2 (30)) ;
insert into t1 (n, v) values (1, 'ONE') ;
insert into t2 (n, v) values (2, 'TWO') ;
insert into t2 (n, v) values (3, 'THREE') ;
commit ;
create type t_type
as object
(value number,
name varchar2 (30)
) ;
/
create type t_tab_type
as table of t_type ;
/
create package my_types
as
type c_ref is ref cursor ;
end my_types ;
/
create function f (table_num_in in number)
return my_types.c_ref
is
t_data t_tab_type := t_tab_type () ;
rc my_types.c_ref ;
begin
if table_num_in = 1
then
for t_rec in (select n, v from t1)
loop
t_data.extend ;
t_data (t_data.count) := t_type (t_rec.n, t_rec.v) ;
end loop ;
elsif table_num_in = 2
then
for t_rec in (select n, v from t2)
loop
t_data.extend ;
t_data (t_data.count) := t_type (t_rec.n, t_rec.v) ;
end loop ;
end if ;
open rc for
select
value, name
from
table (cast (t_data as t_tab_type)) ;
return rc ;
end f ;
/
SQL> variable x refcursor
SQL> execute :x := f (1)
Procédure PL/SQL terminée avec succès.
SQL> print x
VALUE NAME
--------- ------------------------------
1 ONE
SQL> execute :x := f(2)
Procédure PL/SQL terminée avec succès.
SQL> print x
VALUE NAME
--------- ------------------------------
2 TWO
3 THREE
SQL>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
