示例1：

CREATE OR REPLACE PROCEDURE PRC_DM_AUDIT_DZBL IS

V_START_TIME DATE;
V_END_TIME DATE;
V_STATUS VARCHAR2(10);
V_FAILED_REASON VARCHAR2(100);

BEGIN

V_START_TIME := SYSDATE;

EXECUTE IMMEDIATE 'TRUNCATE TABLE DM_AUDIT_DZBL';
INSERT INTO DM_AUDIT_DZBL
(TJSJ, TJZ, ETL_DATE, YWJGDM,TJX,TJXMS)
SELECT TJRQ,TJZ,SYSDATE,YWJGDM,TJX,TJXMS
FROM(
--入院人次
SELECT '1_RYRC' TJX,'入院人次' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZJLSJ) TJRQ,
COUNT(DISTINCT BZZYH||JLLYJGBH||JLLYXTBH) TJZ
FROM BZ_ZYRYJL@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZJLSJ)

UNION ALL

--出院人次

SELECT '2_CYRC' TJX,'出院人次' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZJLSJ) TJRQ,
COUNT(DISTINCT BZZYH||JLLYJGBH||JLLYXTBH) TJZ
FROM BZ_CYJL@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZJLSJ)

UNION ALL

--住院总费用

SELECT '3_ZYZFY' TJX,'住院总费用' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZFYZJ) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)

UNION ALL

--药品收入

SELECT '4_YPSR' TJX,'药品收入' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZXYF)+ SUM(BZZCY1)+SUM(BZZCY2) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--以下字段为支持 住院费用情况饼图
--床费
SELECT '5_CF' TJX,'床费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZCF) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--护理费
SELECT '5_HLF' TJX,'护理费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZHLF) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--西药费
SELECT '5_XYF' TJX,'西药费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZXYF) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--中成药费
SELECT '5_ZCY1' TJX,'中成药费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZZCY1) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--中草药费
SELECT '5_ZCY2' TJX,'中草药费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZZCY2) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--放射费
SELECT '5_FSF' TJX,'放射费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZFSF) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--化验费
SELECT '5_HYF' TJX,'化验费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZHYF) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--输氧费
SELECT '5_SYF' TJX,'输氧费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZSYF) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--输血费
SELECT '5_SXF' TJX,'输血费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZSXF) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ)
UNION ALL
--诊疗费
SELECT '5_ZLF' TJX,'诊疗费' TJXMS,
NVL(YWJGDM,'UNKNOW') YWJGDM,
TRUNC(BZRQ) TJRQ,
SUM(BZZLF) TJZ
FROM BZ_ZYBASY@DB_EHR
WHERE NVL(ZFBZ,0) <> 1
GROUP BY YWJGDM,TRUNC(BZRQ);

V_STATUS := 'SUCCESS';

V_END_TIME := SYSDATE;

PRC_LOG_PROCEDURE_RUN('DM_AUDIT_DZBL',

V_STATUS,
V_FAILED_REASON,
V_START_TIME,
V_END_TIME,
NULL,
NULL);

COMMIT;

EXCEPTION

WHEN OTHERS THEN
V_STATUS := 'FAIL';
V_FAILED_REASON := SQLERRM;
ROLLBACK;
PRC_LOG_PROCEDURE_RUN('DM_AUDIT_DZBL',
V_STATUS,
V_FAILED_REASON,
V_START_TIME,
V_END_TIME,
NULL,
NULL);

END;

示例2：（使用游标）

CREATE OR REPLACE PROCEDURE PRC_DM_SJSCQK IS

--定义存储记录的变量
ywjgdm_record VARCHAR2(200);
--定义游标
cursor ywjgdm_cursor is
select jgbh FROM XT_YLFWJG@Db_Dcz;
begin
delete from DM_SJSCQK;
open ywjgdm_cursor;
fetch ywjgdm_cursor into ywjgdm_record;
while ywjgdm_cursor%found loop
insert into DM_SJSCQK (ZSL,RQ,YWJGMC,YWJGDM,SJJGBH)
        select sum(z.dpscg) zsl,rq,jg.jgmc as ywjgmc,ywjgdm_record as ywjgdm,jg.sjjgbh from(
            select * from hdc_sjjsclqkhz@Db_Dcz t
                    where ywjgdm in 
                                (select jgbh
                                    FROM XT_YLFWJG@Db_Dcz 
                                        START WITH jgbh = ywjgdm_record
                                CONNECT BY nocycle PRIOR jgbh = sjjgbh)
                                ) z
               left join XT_YLFWJG@Db_Dcz jg on jg.jgbh = ywjgdm_record
               group by rq,jg.sjjgbh,jg.jgmc;
fetch ywjgdm_cursor into ywjgdm_record;
end loop;
close ywjgdm_cursor;
COMMIT;
end;

示例3：表中加入UUID和索引

create or replace procedure PRC_ADD_UUID is

v_sql varchar2(2000);
idx_sql varchar2(2000);
begin
for rec in (select t.TABLE_NAME
from user_tables t
where t.TABLE_NAME not like 'XT\_%' escape
'\'
and t.TABLE_NAME not like '%\_ZL' Escape
'\'
AND t.table_name not like '%\_RY' escape
'\'
and t.TABLE_NAME like '%\_%' escape '\'
order by t.TABLE_NAME) loop
begin
v_sql := 'alter table ' || rec.table_name ||
' add UUID varchar(32) default sys_guid() ';
execute immediate v_sql;
exception
when others then
dbms_output.put_line('[' || rec.table_name || ']' || sqlerrm);
end;
begin
idx_sql := 'create index IDX_'||rec.table_name||'_UUID on '||
rec.table_name||'(UUID) ';
execute immediate idx_sql;
exception
when others then
dbms_output.put_line('[' || rec.table_name || ']' || sqlerrm);
end;
end loop;
end PRC_ADD_UUID;