问题一: 在pl/sql过程中不能直接ddl,只能本地sql 例子: str:='create or replace view as select .....'; execute immediate str; 问题二: 在pl/sql不能直接查询,如:select * from hz_2_50hy1;--这样会错 对应用游标实现;
例子: declare str varchar2(4000); xqdzm tb_jp_dzm_dd.dmid%type; cursor mycur is select dmid from tb_jp_dzm_dd where substr(dmid,1,4)='1405' and substr(dmid,5,2)<>'00' ; begin for v_mycur in mycur loop; str:='create or replace view hz_2_50a as select ''总计'' as mc,''00'' as code,count(*) as dws, sum(decode(sign(a201-7),-1,1,0,1,1,0)) as dws1, sum(decode(sign(a201-8)*sign(a201-19),-1,1,0,1,1,0)) as dws2, sum(decode(sign(a201-20)*sign(a201-49),-1,1,0,1,1,0)) as dws3, sum(decode(sign(a201-50)*sign(a201-99),-1,1,0,1,1,0)) as dws4, sum(decode(sign(a201-100)*sign(a201-199),-1,1,0,1,1,0)) as dws5, sum(decode(sign(a201-200)*sign(a201-499),-1,1,0,1,1,0)) as dws6, sum(decode(sign(a201-500)*sign(a201-999),-1,1,0,1,1,0)) as dws7, sum(decode(sign(a201-1000)*sign(a201-2999),-1,1,0,1,1,0)) as dws8, sum(decode(sign(a201-3000)*sign(a201-4999),-1,1,0,1,1,0)) as dws9, sum(decode(sign(5000-a201),-1,1,0,1,1,0)) as dws10 from tb_jp_ja2002_data where dwbs=''10'' and a056='''||v_mycur.xqdzm||''' and a18 in (''110'',''141'',''151'')' ; execute immediate str; end loop; .....
问题1: 在pl/sql可以执行ddl,可以通过如下途径,你的语句太长,这里以create view view1 as select * from tab为例 1、使用动态sql a、execute immediate 'create view view1 as select * from tab'; b、使用dbms_sql 2、使用oracle的dbms_utility包 dbms_utility.exec_ddl_statement('create view view1 as select * from tab'); 问题2: 当然不能了,sqlplus包含了许多的报表功能,这些在pl/sql中就是不能用的。
在pl/sql过程中不能直接ddl,只能本地sql
例子:
str:='create or replace view as select .....';
execute immediate str;
问题二:
在pl/sql不能直接查询,如:select * from hz_2_50hy1;--这样会错
对应用游标实现;
declare
str varchar2(4000);
xqdzm tb_jp_dzm_dd.dmid%type;
cursor mycur is
select dmid from tb_jp_dzm_dd where substr(dmid,1,4)='1405' and substr(dmid,5,2)<>'00' ;
begin
for v_mycur in mycur loop;
str:='create or replace view hz_2_50a as
select ''总计'' as mc,''00'' as code,count(*) as dws,
sum(decode(sign(a201-7),-1,1,0,1,1,0)) as dws1,
sum(decode(sign(a201-8)*sign(a201-19),-1,1,0,1,1,0)) as dws2,
sum(decode(sign(a201-20)*sign(a201-49),-1,1,0,1,1,0)) as dws3,
sum(decode(sign(a201-50)*sign(a201-99),-1,1,0,1,1,0)) as dws4,
sum(decode(sign(a201-100)*sign(a201-199),-1,1,0,1,1,0)) as dws5,
sum(decode(sign(a201-200)*sign(a201-499),-1,1,0,1,1,0)) as dws6,
sum(decode(sign(a201-500)*sign(a201-999),-1,1,0,1,1,0)) as dws7,
sum(decode(sign(a201-1000)*sign(a201-2999),-1,1,0,1,1,0)) as dws8,
sum(decode(sign(a201-3000)*sign(a201-4999),-1,1,0,1,1,0)) as dws9,
sum(decode(sign(5000-a201),-1,1,0,1,1,0)) as dws10
from tb_jp_ja2002_data
where dwbs=''10'' and a056='''||v_mycur.xqdzm||''' and a18 in (''110'',''141'',''151'')' ;
execute immediate str;
end loop;
.....
在pl/sql可以执行ddl,可以通过如下途径,你的语句太长,这里以create view view1 as select * from tab为例
1、使用动态sql
a、execute immediate 'create view view1 as select * from tab';
b、使用dbms_sql
2、使用oracle的dbms_utility包
dbms_utility.exec_ddl_statement('create view view1 as select * from tab');
问题2:
当然不能了,sqlplus包含了许多的报表功能,这些在pl/sql中就是不能用的。