ORACLE 存储过程动态创建视图

最新推荐文章于 2022-05-22 11:01:43 发布

阿尔法狼

最新推荐文章于 2022-05-22 11:01:43 发布

阅读量5.7k

点赞数

分类专栏： Orcale

本文链接：https://blog.csdn.net/namespacefeng/article/details/17246989

版权

这个Oracle存储过程名为'CREAR_PF_VIEW'，用于动态创建视图V_PF。它首先定义一个游标获取YDS_SHBPDY表中的SPBLX字段，然后通过循环拼接SQL语句，将YDS_SHPB表中的数据左连接到视图中，形成一个带有多个用户特定列（uid_BH, uid_YJ, uid_QZ, uid_RQ）的视图。最后，使用EXECUTE IMMEDIATE执行最终的SQL字符串并提交事务。" 123404371,10854995,计算序列前N项和的C语言程序,"['c语言', '数值计算']

摘要由CSDN通过智能技术生成

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 ||