存储过程中建多个同结构表视图的方法?

[Harbin_SAKURA]

我遇到了如下难题：

通过存储过程建视图，视图包含多个相同结构的表，我开始这么做:

create_view_sql:= 'CREATE VIEW PP as select * from (
select * from TABLE_1 union all
select * from TABLE_2 union all
select * from TABLE_3);';

EXECUTE IMMEDIATE create_view_sql;

其中TABLE_1与TABLE_2,TABLE_3结构完全相同，但是一直提示"无效字符"，上网查了一下，发现在存储过程中select * from TABLE_1 union all select * from TABLE_2这样是不能执行的，那么这种需求应该怎么实现呢？

[xman_78tom]

lz 的动态语句字符串的最后多了个分号（;）吧。

SQL> ed

  1  declare
  2   stmt varchar2(1000):='create view num_v as select * from (select 1 num from dual union all select 2 from dual)';
  3  begin
  4   execute immediate stmt;
  5* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from num_v;

       NUM
----------
         1
         2

[gdd714]

[Quote=引用 1 楼 xman_78tom 的回复:]

lz 的动态语句字符串的最后多了个分号（;）吧。
SQL code

SQL> ed

1 declare
2 stmt varchar2(1000):='create view num_v as select * from (select 1 num from dual union all select 2 from dual)';
3 begin
4 exe……
[/Quote]

高手呀。

[「已注销」]

被楼主误导了

[cnwz]

写代码还是得细致啊

[心中的彩虹]

[Quote=引用楼主 harbin_sakura 的回复:]
我遇到了如下难题：

通过存储过程建视图，视图包含多个相同结构的表，我开始这么做:

create_view_sql:= 'CREATE VIEW PP as select * from (
select * from TABLE_1 union all
select * from TABLE_2 union all
select * from TABLE_3);';

……
[/Quote]
create_view_sql:= 'CREATE VIEW PP as select * from (
select * from TABLE_1 union all
select * from TABLE_2 union all
select * from TABLE_3);'; 多了个分号

[Harbin_SAKURA]

非常感谢各位的回复，我还需要多多学习呀