各位高手以下为静态sql创建的视图:CREATE OR REPLACE FORCE VIEW EASYLOAN.V_DAIK_TAIZ AS select aa.* from (
select fkshzl.XYDHT,bb.*,fkshzl.CUST_ID from
(
--放款记录
select 3 xu,FANGKID,JYRQ fkrq,ffje fkje,null hkrq,null hkje ,null fkye,null lix from fklc
where FANGKID in
(select id from fkshzl where cust_id=fkshzl.CUST_ID)
--还款明细
union all
select 2 xu,FANGKID,null fkrq,null fkje,jyrq hkrq,shje hkje ,f_fkye(FANGKID,'daik') fkye,f_lixone(hklc.ID) lix
from hklc
where FANGKID in
(select id from fkshzl where cust_id=fkshzl.CUST_ID)
union all
--最新余额
select 1 xu,FANGKID,null fkrq,null fkje,null hkrq,null hkje ,f_fkye(FANGKID,'daik') fkye,f_lixtwo(FANGKID) lix
from fklc
where FANGKID in
(select id from fkshzl where cust_id=fkshzl.CUST_ID)
) bb,fkshzl where bb.fangkid=fkshzl.ID
) aa
现拜求一个动态sql语句,来创建此视图
select fkshzl.XYDHT,bb.*,fkshzl.CUST_ID from
(
--放款记录
select 3 xu,FANGKID,JYRQ fkrq,ffje fkje,null hkrq,null hkje ,null fkye,null lix from fklc
where FANGKID in
(select id from fkshzl where cust_id=fkshzl.CUST_ID)
--还款明细
union all
select 2 xu,FANGKID,null fkrq,null fkje,jyrq hkrq,shje hkje ,f_fkye(FANGKID,'daik') fkye,f_lixone(hklc.ID) lix
from hklc
where FANGKID in
(select id from fkshzl where cust_id=fkshzl.CUST_ID)
union all
--最新余额
select 1 xu,FANGKID,null fkrq,null fkje,null hkrq,null hkje ,f_fkye(FANGKID,'daik') fkye,f_lixtwo(FANGKID) lix
from fklc
where FANGKID in
(select id from fkshzl where cust_id=fkshzl.CUST_ID)
) bb,fkshzl where bb.fangkid=fkshzl.ID
) aa
现拜求一个动态sql语句,来创建此视图
楼主能不能说下需求,给个例子
create or replace view aa as select * from (
select 1 a,2 b,3 c from dual
union all
select 4 d,5 e, 6 f from dual
) bb把这个视图用动态sql创建出来。
请各位大侠高手指点
execute immediate 'create or replace view vi_test(col1,col2,col3) as select ''1 a'',''2 b'',''3 c'' from dual union all select ''4 d'',''5 e'',''6 f'' from dual';
end;
/
execute immediate 'create or replace view aa as select * from (
select 1 a,2 b,3 c from dual
union all
select 4 d,5 e, 6 f from dual
) bb ';
end;
我试着写了一下,您给看看,CREATE OR REPLACE procedure EASYLOAN.PRODUCTE_VIEW(in_cust_id in varchar2,search_date in date)as
str varchar2(3000);
str1 varchar2(500);
str2 varchar2(500);
str3 varchar2(500);
str4 varchar2(500);
str5 varchar2(500);
begin
str:='create or replace view V_DAIK_TAIZa(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10) as select * from(';
str1:='select fkshzl.XYDHT,bb.*,fkshzl.CUST_ID from (';
str2:=' select 3 xu,FANGKID,JYRQ fkrq,ffje fkje,null hkrq,null hkje ,null fkye,null lix
from fklc
where FANGKID in
(select id from fkshzl where cust_id='||in_cust_id||') and jyrq<='||search_date||' union all ';
str3:='select 2 xu,FANGKID,null fkrq,null fkje,jyrq hkrq,shje hkje ,f_fkye(FANGKID,''daik'') fkye,f_lixone(hklc.ID) lix
from hklc
where FANGKID in
(select id from fkshzl where cust_id='||in_cust_id||') and jyrq<='||search_date||' union all ';
str4:='select 1 xu,FANGKID,null fkrq,null fkje,null hkrq,null hkje ,f_fkye(FANGKID,''daik'') fkye,f_lixtwo(FANGKID) lix
from fklc
where FANGKID in
(select id from fkshzl where cust_id='||in_cust_id||')';
str5:=') bb,fkshzl where bb.fangkid=fkshzl.ID) aa';
str:=str||str1||str2||str3||str4||str5;
execute immediate str;
end;
编译通过了,但调用的时候报:标识过长的错误。这是怎么回事?
可以这么改试试
CREATE OR REPLACE procedure EASYLOAN.PRODUCTE_VIEW(in_cust_id in varchar2,search_date in date)as
str varchar2(3000);
str1 varchar2(500);
str2 varchar2(500);
str3 varchar2(500);
str4 varchar2(500);
str5 varchar2(500);
begin
str:='create or replace view V_DAIK_TAIZa(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10) as select * from(';
str1:='select fkshzl.XYDHT,bb.*,fkshzl.CUST_ID from (';
str2:=' select 3 xu,FANGKID,JYRQ fkrq,ffje fkje,null hkrq,null hkje ,null fkye,null lix
from fklc
where FANGKID in
(select id from fkshzl where cust_id=:1) and jyrq <=:2 union all ';
str3:='select 2 xu,FANGKID,null fkrq,null fkje,jyrq hkrq,shje hkje ,f_fkye(FANGKID,''daik'') fkye,f_lixone(hklc.ID) lix
from hklc
where FANGKID in
(select id from fkshzl where cust_id=:1) and jyrq <=:2 union all ';
str4:='select 1 xu,FANGKID,null fkrq,null fkje,null hkrq,null hkje ,f_fkye(FANGKID,''daik'') fkye,f_lixtwo(FANGKID) lix
from fklc
where FANGKID in
(select id from fkshzl where cust_id=:1)';
str5:=') bb,fkshzl where bb.fangkid=fkshzl.ID) aa';
str:=str||str1||str2||str3||str4||str5 using in_cust_id,search_date;
execute immediate str;
end;
能成功的话就不用分成那么多字符串了
谢谢了,您辛苦了。
可是
编译通过,但是调用后报:ora-01027:在数据定义操作中不允许对变量赋值 错误,郁闷
改成这样试试
CREATE OR REPLACE procedure EASYLOAN.PRODUCTE_VIEW(in_cust_id in varchar2,search_date in date)as
str varchar2(3000);
str1 varchar2(500);
str2 varchar2(500);
str3 varchar2(500);
str4 varchar2(500);
str5 varchar2(500);
begin
str:='create or replace view V_DAIK_TAIZa(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10) as select * from(';
str1:='select fkshzl.XYDHT,bb.*,fkshzl.CUST_ID from (';
str2:=' select 3 xu,FANGKID,JYRQ fkrq,ffje fkje,null hkrq,null hkje ,null fkye,null lix
from fklc
where FANGKID in
(select id from fkshzl where cust_id='''||in_cust_id||''') and jyrq <=to_date('''||to_char(search_date,'yyyymmdd')||''',''yyyymmdd'') union all ';
str3:='select 2 xu,FANGKID,null fkrq,null fkje,jyrq hkrq,shje hkje ,f_fkye(FANGKID,''daik'') fkye,f_lixone(hklc.ID) lix
from hklc
where FANGKID in
(select id from fkshzl where cust_id='''||in_cust_id||''') and jyrq <=to_date('''||to_char(search_date,'yyyymmdd')||''',''yyyymmdd'') union all ';
str4:='select 1 xu,FANGKID,null fkrq,null fkje,null hkrq,null hkje ,f_fkye(FANGKID,''daik'') fkye,f_lixtwo(FANGKID) lix
from fklc
where FANGKID in
(select id from fkshzl where cust_id='''||in_cust_id||''')';
str5:=') bb,fkshzl where bb.fangkid=fkshzl.ID) aa';
str:=str||str1||str2||str3||str4||str5 ;
execute immediate str;
end;