我遇到了如下难题:通过存储过程建视图,视图包含多个相同结构的表,我开始这么做: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这样是不能执行的,那么这种需求应该怎么实现呢?
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这样是不能执行的,那么这种需求应该怎么实现呢?
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
select * from TABLE_1 union all
select * from TABLE_2 union all
select * from TABLE_3);'; 多了个分号