如何把多条语句统计的结果集用一个存储全部展示_多个语句一起执行结果统计-优快云博客

本文链接：https://blog.youkuaiyun.com/dreaminglove/article/details/5895768

本文探讨了如何将两个用于创建视图的存储过程合并为一个。通过分析现有的存储过程代码，提出了合并的方法，旨在简化代码并提高执行效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.
CREATE OR REPLACE PROCEDURE procunit AS
sqlstr VARCHAR2(2000) := 'create or replace view v_tmp as select d.drugdesc';
BEGIN
FOR cur1 IN (SELECT DISTINCT u.class FROM unit u) LOOP
    sqlstr := sqlstr || ',count(decode(u.class,''' || cur1.class || ''',1)) ' || cur1.class;
END LOOP;
sqlstr := sqlstr || ' from casecrime_drug c left join cddrug d on c.drugid=d.drugid
                      left join casesecurity y on y.caseno=c.caseno
                      left join unit u on u.unitid=y.inputunitid
                      left join suspect s on s.caseno=y.caseno
                      group by d.drugdesc';
DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END;

2。
CREATE OR REPLACE PROCEDURE procunits AS
sqlstr VARCHAR2(2000) := 'create or replace view v_tmps as select d.drugdesc';
BEGIN
FOR cur1 IN (SELECT DISTINCT u.class FROM unit u) LOOP
    sqlstr := sqlstr || ',count(decode(u.class,''' || cur1.class || ''',1)) ' || cur1.class;
END LOOP;
sqlstr := sqlstr || ' from casecrime_drug c left join cddrug d on c.drugid=d.drugid
                      left join casesecurity y on y.caseno=c.caseno
                      left join unit u on u.unitid=y.inputunitid
                      left join suspect s on s.caseno=y.caseno
                      group by d.drugdesc';
DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END;

比如把上面的结果拼合在一起？最终的结果要的是如下执行出来的结果
一样：select * from v_tmps union all select * from v_tmp

那么如何把上面两个存储过程写成一个存储过程。改写成一个存储过程？