Question

我有一个信标设计的Oracle数据库:许多表格都有相同的图象。它们从一个庞大的桌子中重新 pieces,并随着时间的推移,将设立新的桌子。当然,我没有获准进入庞大的桌子,只有小桌。我如何有效地质疑这些表格?

很多人预先表示感谢。

Answer 1

AFAIK,如果是你所说的的话,就没有办法对多个不同的表格使用一份单一的发言稿。你们是否可以认为,所有表格都有联盟?

Answer 2

你们可以发挥编织职能,积极制定从所有相关表格中选取的LQ声明。

--Create tables.  They can be created at any time, but they have
--to follow some sort of naming convention
create table temp1(test1 number, test2 varchar2(100));
create table temp2(test1 number, test2 varchar2(100));
create table temp3(test1 number, test2 varchar2(100));


--Create a package that will return one result at a time from all the tables.
create or replace package query_tables is
  type output_rectype is record
  (
    test1 number
    ,test2 varchar2(100)
  );
  type output_rectype_table is table of output_rectype;
  function query return output_rectype_table pipelined;
end query_tables;
/


create or replace package body query_tables is
  function query return output_rectype_table pipelined is
    sql_statement varchar2(32767);
    output output_rectype;
    my_cursor sys_refcursor;
  begin
    --Build a select statment that combines all relevant tables
    for tables in (select table_name from user_tables where table_name like  TEMP% ) loop
      sql_statement := sql_statement ||  select * from  ||tables.table_name||  union all  ;
    end loop;
    --Remove the last  union all 
    sql_statement := substr(sql_statement, 1, length(sql_statement) - 11);

    --Get one result at a time and return it
    open my_cursor for sql_statement;
    loop
      fetch my_cursor into output;
      exit when my_cursor%notfound;
      pipe row(output);
    end loop;
  end;
end query_tables;
/


--Insert some test data
insert into temp1 values(1,  asdf );
insert into temp2 values(2,  fgds );
insert into temp3 values(3,  rewq );


--Display the results from all tables with one query
select * from table(query_tables.query);

友情链接