ORACLE存储过程动态创建视图
create or replace procedure "CREAR_PF_VIEW" is
pragma AUTONOMOUS_TRANSACTION;
cursor cur is --定义游标
SELECT SPBLX FROM YDS_SHBPDY;
sqlstr varchar2(32767);
sql_ls varchar2(32767);
uid varchar2(32767);
begin
open cur;
fetch cur
into uid;
sqlstr := 'create or replace view V_PF as select * from (select distinct XMBH as XMBH From YDS_SHPB ) X1 ';
while cur%found
loop
sql_ls := ' left join (select B.XMBH as '|| uid || '_BH,B.JBRYJ as '|| uid || '_YJ,B.JBR1 as '|| uid ||
'_QZ, B.JBRQ1 as '|| uid || '_RQ from YDS_SHBPDY A,YDS_SHPB B where A.SPBLX = B.SPBLX and A.SPBLX ='''|| uid|| '''
) '|| uid|| ' on X1.XMBH = '|| uid|| '.'|| uid || '_BH';
sqlstr := sqlstr || sql_ls;
-- dbms_output.put_line(sql_ls);
---执行拼接字符串
-- execute immediate sqlstr;
fetch cur
into uid;
end loop;
close cur;
--dbms_output.enable(100000000);
--dbms_output.put_line(sqlstr);
execute immediate sqlstr;
commit;
end;
pragma AUTONOMOUS_TRANSACTION;
cursor cur is --定义游标
SELECT SPBLX FROM YDS_SHBPDY;
sqlstr varchar2(32767);
sql_ls varchar2(32767);
uid varchar2(32767);
begin
open cur;
fetch cur
into uid;
sqlstr := 'create or replace view V_PF as select * from (select distinct XMBH as XMBH From YDS_SHPB ) X1 ';
while cur%found
loop
sql_ls := ' left join (select B.XMBH as '|| uid || '_BH,B.JBRYJ as '|| uid || '_YJ,B.JBR1 as '|| uid ||
'_QZ, B.JBRQ1 as '|| uid || '_RQ from YDS_SHBPDY A,YDS_SHPB B where A.SPBLX = B.SPBLX and A.SPBLX ='''|| uid|| '''
) '|| uid|| ' on X1.XMBH = '|| uid|| '.'|| uid || '_BH';
sqlstr := sqlstr || sql_ls;
-- dbms_output.put_line(sql_ls);
---执行拼接字符串
-- execute immediate sqlstr;
fetch cur
into uid;
end loop;
close cur;
--dbms_output.enable(100000000);
--dbms_output.put_line(sqlstr);
execute immediate sqlstr;
commit;
end;
>更多相关文章
首页推荐
佛山市东联科技有限公司一直秉承“一切以用户价值为依归
- 01-11全球最受赞誉公司揭晓:苹果连续九年第一
- 12-09罗伯特·莫里斯:让黑客真正变黑
- 12-09谁闯入了中国网络?揭秘美国绝密黑客小组TA
- 12-09警示:iOS6 惊现“闪退”BUG
- 05-06TCL科技:预计大尺寸面板价格上涨动能有望延
- 05-06新加坡电信Optus任命新首席执行官以重建品牌
- 05-06微软宣布为消费级用户账户提供安全密钥支持
- 05-06当好大数据产业“守门员”(筑梦现代化 共绘
- 04-29通用智能人“通通”亮相中关村论坛
相关文章
24小时热门资讯
24小时回复排行
热门推荐
最新资讯
操作系统
黑客防御