欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle 中 table 函数的应用浅析

程序员文章站 2022-11-23 14:43:02
表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段处理,并省去中间结果的存储和...

表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段处理,并省去中间结果的存储和缓冲表。

1. 用游标传递数据

利用游标 ref cursor 可将数据集(多行记录)传递到pl/sql函数:

select *
 from table (myfunction (cursor (select *
         from mytab)));  

2. 利用两个实体化视图(或表)作为样板数据

create materialized view sum_sales_country_mv
build immediate
refresh complete
enable query rewrite
as
select substr (s.calendar_month_desc, 1, 4) year, c.country_id country,
   sum (sum_amount_sold) sum_amount_sold
 from sum_sales_month_mv s, customers c
 where s.cust_id = c.cust_id
  and c.country_id in ('us', 'uk', 'fr', 'es', 'jp', 'au')
group by substr (s.calendar_month_desc, 1, 4), c.country_id
create materialized view sum_es_gend_mv
build deferred
refresh fast
enable query rewrite
as
select substr (s.calendar_month_desc, 1, 4) year,
   s.calendar_month_desc cal_month, c.cust_gender,
   sum (sum_amount_sold) sum_amount_sold
 from sum_sales_month_mv s, customer c
 where s.cust_id = c.cust_id
  and c.country_id = 'es'
  and sunstr (s.calendar_month_desc, 1, 4) = '2000'
group by substr (s.calendar_month_desc, 1, 4),
   s.calendar_month_desc,
   c.cust_gender;

3. 定义对象类型和基于对象类型的表类型

定义对象类型并且为进一步引用做好准备。

(1)定义对象类型:type sales_country_t

create materialized view sum_es_gend_mv
build deferred
refresh fast
enable query rewrite
as
select substr (s.calendar_month_desc, 1, 4) year,
   s.calendar_month_desc cal_month, c.cust_gender,
   sum (sum_amount_sold) sum_amount_sold
 from sum_sales_month_mv s, customer c
 where s.cust_id = c.cust_id
  and c.country_id = 'es'
  and sunstr (s.calendar_month_desc, 1, 4) = '2000'
group by substr (s.calendar_month_desc, 1, 4),
   s.calendar_month_desc,
   c.cust_gender;

(2)定义表类型:type sum_sales_country_t_tab

create type sum_sales_country_t_tab as table of sales_country_t;

(3)定义对象类型:type sales_gender_t

create type sales_gender_t as object (
 year    varchar2 (4),
 country_id  char (2),
 cust_gender  char (1),
 sum_amount_sold number
);

(4)定义表类型:type sum_sales_gender_t_tab

create type sum_sales_gender_t_tab as table of sales_gender_t;

(5)定义对象类型:type sales_roll_t

create type sales_roll_t as object (
 channel_desc  varchar2 (20),
 country_id  char (2),
 sum_amount_sold number
);

(6)定义表类型:type sum_sales_roll_t_tab

create type sum_sales_roll_t_tab as table of sales_roll_t;

(7)检查一下建立的类型

select object_name, object_type, status
 from user_objects
 where object_type = 'type';

4. 定义包:create package and define ref cursor

create or replace package cursor_pkg
i type sales_country_t_rec is record (
  year    varchar (4),
  country   char (2),
  sum_amount_sold number
 );
 type sales_gender_t_rec is record (
  year    varchar2 (4),
  country_id  char (2),
  cust_gender  char (1),
  sum_amount_sold number
 );
 type sales_roll_t_rec is record (
  channel_desc  varchar2 (20),
  country_id  char (2),
  sum_amount_sold number
 );
 type sales_country_t_rectab is table of sales_country_t_rec;
 type sales_roll_t_rectab is table of sales_roll_t_rec;
 type strong_refcur_t is ref cursor
  return sales_country_t_rec;
 type row_refcur_t is ref cursor
  return sum_sales_country_mv%rowtype;
 type roll_refcur_t is ref cursor
  return sales_roll_t_rec;
 type refcur_t is ref cursor;
end corsor_pkg;

5. 定义表函数

(1)定义表函数:function table_ref_cur_week

create or replace function table_ref_cur_week (cur cursor.refcur_t)
 return sum_sales_country_t_tab
is
 year    varchar (4);
 country   char (2);
 sum_amount_sold number;
 objset   sum_sales_country_t_tab := sum_sales_country_t_tab ();
 i     number     := 0;
begin
 loop
-- fetch from cursor variable
  fetch cur
  into year, country, sum_amount_sold;
  exit when cur%notfound;
      -- exit when last row is fetched
-- append to collection
  i := i + 1;
  objset.extend;
  objset (i) := sales_country_t (year, country, sum_amount_sold);
 end loop;
 close cur;
 return objset;
end;
/

(2)定义表函数:function table_ref_cur_strong

create or replace function table_ref_cur_strong (cur cursor_pkg.strong_refcur_t)
 return sum_sales_country_t_tab pipelined
is
 year    varchar (4);
 country   char (2);
 sum_amount_sold number;
 i     number  := 0;
begin
 loop
  fetch cur
  into year, country, sum_amount_sold;
  exit when cur%notfound;     -- exit when last row fetched
  pipe row (sales_country_t (year, country, sum_amount_sold));
 end loop;
 close cur;
 return;
end;
/

(3)定义表函数:function table_ref_cur_row

create or replace function table_ref_cur_row (cur cursor_pkg.row_refcur_t)
 return sum_sales_country_t_tab pipelined
is
 in_rec cur%rowtype;
 out_rec sales_country_t := sales_country_t (null, null, null);
begin
 loop
  fetch cur
  into in_rec;
  exit when cur%notfound;    -- exit when last row is fetched
  out_rec.year := in_rec.year;
  out_rec.country := in_rec.country;
  out_rec.sum_amount_sold := in_rec.sum_amount_sold;
  pipe row (out_rec);
 end loop;
 close cur;
 return;
end;
/

(4)定义表函数:function gender_table_ref_cur_week

create or replace function gender_table_ref_cur_week (cur cursor_pkg.refcur_t)
 return sum_sales_gender_t_tab
is
 year    varchar2 (4);
 country_id  char (2);
 cust_gender  char (1);
 sum_amount_sold number;
 objset   sum_sales_gender_t_tab := sum_sales_gender_t_tab ();
 i     number     := 0;
begin
 loop
  fetch cur
  into year, country_id, cust_gender, sum_amount_sold;
  exit when cur%notfound;    -- exit when last row is fetched
  i := i + 1;
  objset.extend;
  objset (i) :=
   sum_sales_gender_t (year, country_id, cust_gender, sum_amount_sold);
 end loop;
 close cur;
 return objset;
end;
/

6. 调用表函数

下列 sql 查询语句调用已被定义的表函数。

select *
 from table (table_ref_cur_week (cursor (select *
           from sum_sales_country_mv)));
select *
 from table (table_ref_cur_strong (cursor (select *
            from sum_sales_country_mv)));
select *
 from table (table_ref_cur_row (cursor (select *
           from sum_sales_country_mv)));
select *
 from table (table_ref_cur_week (cursor (select *
           from sum_sales_country_mv
           where country = 'au')));

以上所述是小编给大家介绍的oracle 中 table 函数的应用浅析,希望对大家有所帮助