Dynamic ORACLE stored procedures to create a view

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
create or replace procedure "CREAR_PF_VIEW" is
pragma AUTONOMOUS_TRANSACTION;
Cursor cur is - the cursor
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);
- executive string
-- 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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Andy at December 11, 2013 - 10:21 PM