Oracle 中 table 函数的应用浅析

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

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')));

以上所述是www.887551.com给大家介绍的oracle 中 table 函数的应用浅析，希望对大家有所帮助

Oracle 中 table 函数的应用浅析

相关推荐