oracle - 是否可以在 PL SQL 过程中创建本地 View ？

Question

我正在寻找一种解决方案，告诉 Oracle“这个查询应该像 View 一样重用”(如果我问，那是我不想/不能如果可能，创建一个完整的全局 View )。

使用以下最小模型(我省略了其他列):

• 有所有者拥有书籍。
• 书有作者和章节。
• 章节有书籍(例如:书籍的另一种格式中包含的章节)和作者。
• 一个作者至少有一个_authors_related_data1_和几个_authors_related_data2_。 _authors_related_data3_, ..., _authors_related_dataN_ 也是如此。

因此下面的创建语句(我没有针对 Oracle 验证它们，更多的是为了理解问题而不是测试)。

create table owners(  owner_id  number not null, constraint pk_owners primary key (owner_id));
create table books(   book_id   number not null, constraint pk_books primary key (book_id));
create table authors( author_id number not null, constraint pk_authors primary key (author_id));
create table chapters( chapter_id number not null, constraint pk_chapters primary key (chapter_id));

create table owned_books(
  owner_id number not null,
  book_id  number not null, 
  constraint pk_owned_books primary key (owner_id, book_id),
  constraint fk_owned_books_1 foreign key (owner_id) references owners (owner_id),
  constraint fk_owned_books_2 foreign key (book_id) references books (book_id) 
);
create table book_authors(
  book_id number not null,
  author_id  number not null, 
  constraint pk_book_authors primary key (book_id, author_id),
  constraint fk_book_authors_1 foreign key (author_id) references authors (author_id),
  constraint fk_book_authors_2 foreign key (book_id) references books (book_id) 
);
create table chapter_authors(
  chapter_id number not null,
  author_id  number not null, 
  constraint pk_chapter_authors primary key (chapter_id, author_id),
  constraint fk_chapter_authors_1 foreign key (author_id) references authors (author_id),
  constraint fk_chapter_authors_2 foreign key (chapter_id) references chapters (chapter_id) 
);
create table book_chapters(
  chapter_id number not null,
  book_id  number not null, 
  constraint pk_book_chapters primary key (chapter_id, book_id),
  constraint fk_book_chapters_1  foreign key (chapter_id) references chapters (chapter_id)
  constraint fk_book_chapters_2 foreign key (book_id) references books (book_id)  
);

create table authors_related_data1( 
  author_id number not null,
  constraint pk_authors_related_data1 primary key (author_id),
  constraint fk_authors_related_data1_1 foreign key (author_id) references authors (author_id)
);
create table authors_related_data2(
  data_id number not null,
  author_id number not null,
  constraint pk_authors_related_data2 primary key (data_id),
  constraint authors_related_data2 foreign key (author_id) references authors (author_id)
);

我想做的查询(和重复的部分):

with v_books as (
  select books.book_id
  from owned_books 
  inner join books on books.book_id = owned_books.book_id
  where owned_books.owner_id = P_OWNER_ID
), v_authors as (
    select authors.author_id
    from v_books
    inner join book_authors on book_authors.book_id = v_books.book_id
    inner join authors on authors.author_id = book_authors.author_id
  union
    select authors.author_id
    from v_books
    inner join book_chapters on book_chapters.book_id = v_books.book_id
    inner join chapter_authors on chapter_authors.chapter_id = book_chapters.chapter_id
    inner join authors on authors.author_id = book_chapters.author_id  
)
  select authors_related_data1.*
  from   v_authors
  inner join authors_related_data1 on authors_related_data1.author_id = v_authors.author_id
;

with v_books as (
  select books.book_id
  from owned_books 
  inner join books on books.book_id = owned_books.book_id
  where owned_books.owner_id = P_OWNER_ID
), v_authors as (
    select authors.author_id
    from v_books
    inner join book_authors on book_authors.book_id = v_books.book_id
    inner join authors on authors.author_id = book_authors.author_id
  union
    select authors.author_id
    from v_books
    inner join book_chapters on book_chapters.book_id = v_books.book_id
    inner join chapter_authors on chapter_authors.chapter_id = book_chapters.chapter_id
    inner join authors on authors.author_id = book_chapters.author_id  
)
  select authors_related_data2.*
  from   v_authors
  inner join authors_related_data2 on authors_related_data2.author_id = v_authors.author_id
;

两个查询的第一部分(带有...)相同。

这样的观点会很棒:

create view v_owned_authors as (
  with v_books as (
    select books.book_id
    from owned_books 
    inner join books on books.book_id = owned_books.book_id
  )
    (
        select authors.author_id
        from v_books
        inner join book_authors on book_authors.book_id = v_books.book_id
        inner join authors on authors.author_id = book_authors.author_id
      union
        select authors.author_id
        from v_books
        inner join book_chapters on book_chapters.book_id = v_books.book_id
        inner join chapter_authors on chapter_authors.chapter_id = book_chapters.chapter_id
        inner join authors on authors.author_id = book_chapters.author_id  
    )
;

之前的查询会很简单:

  select authors_related_data2.*
  from   v_owned_authors
  inner join authors_related_data2 on authors_related_data2.author_id = v_authors.author_id
  where v_owned_authors.owner_id = P_OWNER_ID

但是:

• 拥有的书籍集可能太大， View 不会有 P_OWNER_ID 参数和减少拥有的书籍子句。因此，出于性能原因，我想避免使用该 View ，因为我认为 Oracle 无法优化此类用例。
• 出于各种(合法的)原因，我不能。

Answer 1

您可以使用表函数或管道函数

这是一个表函数的例子，来自: http://oracle-base.com/articles/misc/pipelined-table-functions.php

CREATE TYPE t_tf_row AS OBJECT (
  id           NUMBER,
  description  VARCHAR2(50)
);
/

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

-- Build the table function itself.
CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS
  l_tab  t_tf_tab := t_tf_tab();
BEGIN
  FOR i IN 1 .. p_rows LOOP
    l_tab.extend;
    l_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i);
  END LOOP;

  RETURN l_tab;
END;
/

-- Test it.
SELECT *
FROM   TABLE(get_tab_tf(10))
ORDER BY id DESC;

这是一个流水线函数的例子:

CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
   r t_tf_row%rowtype;
BEGIN
  for z in (select id, desc from sometalbe) loop
    r.id := z.id;
    r.description := z.desc;
    PIPE ROW(r);   
  END LOOP;
 RETURN;
END;