1.ORACLE 索引及性能相关系统视图使用2.ORACLE闪回功能基本用法3.【检查ORACLE阻塞】如果阻塞超过N秒则发短信报警并KILL进程4.如何批量脚本停用(启用)SQL Server 和 ORACLE 数据库用户作业5.ORACLE隔离级别与脏读/不可重复读/幻读的关系以及SELECT FOR UPDATE用法6.ORACLE 中 START WITH CONNECT BY PRIOR 用法(用于父子关系的数据表查询)7.ORACLE / SQL Server-查询最终阻塞者进程脚本8.关于Oracle客户端运行SQLPlus.exe9.SQL SERVER / ORACLE 拼接多行记录字段为一个字符串的方法10.ORACLE自定义函数返回记录集(表类型)的写法11.【转】Oracle 建立索引及SQL优化12.【ORACLE】调整序列的当前种子值13.ORACLE 检索某列包含特定字符串的数据表工具存储过程14.ORACLE常用修改字段脚本15.在oracle中怎么通过字段名查询其所在的表16.ORACLE 查询所有用户调度作业17.【ORACLE】查看死锁进程并结束死锁的脚本18.Oracle获取最近执行的SQL语句19.Oracle的函数返回表类型(转)20.ORACLE变量定义及使用(另,T-SQL EXISTS的PLSQL替代写法)21.ORACLE常用脚本示例22.使用 PL/SQL Developer Version 14 调试 Oracle 存储过程23.Oracle前端工具(PL/SQL Developer)—在不安装Oracle客户端的情况下使用24.SQL SERVER/ORACLE 将ID串列转换为多行的写法25.ORACLE 游标和NULLIF函数在触发器中的使用26.关于“LINQ to Entities does not recognize the Method '...' ”问题原因浅析27.Oracle作业执行与SQL Develop运行语句(脚本)的区别28.ORACLE 创建表前判断是否已存在
29.ORACLE 返回类型为SYS_REFCURSOR的存储过程用法
30.ORACLE杂文之自定义函数与随机函数返回类型为SYS_REFCURSOR的存储过程示例(备忘)含调用代码
1、准备全局临时表:
DROP TABLE "REPORT"."TEMPPOPSETTLEMENTDETAIL";
/
CREATE GLOBAL TEMPORARY TABLE "REPORT"."TEMPPOPSETTLEMENTDETAIL"
( "TEMPPOPSETTLEMENTDETAILID" NUMBER(10,0),
"SETTLEMENTID" NUMBER(10,0),
"STATUSFLAG" NUMBER(5,0),
"ENCOUNTERID" NUMBER(10,0),
"DISPLAYNAME" VARCHAR2(50) DEFAULT '',
"ADMITDATEON" DATE DEFAULT to_date('1900-01-01','yyyy-mm-dd'),
"SEQNOTEXT" VARCHAR(50) DEFAULT '',
"RECEIPTSEQNOTEXT" VARCHAR(50) DEFAULT '',
"TOTALAMOUNT" NUMBER(18,4) DEFAULT 0,
"ACCOUNTAMOUNT" NUMBER(18,4) DEFAULT 0,
"STATUSDESC" VARCHAR(50) DEFAULT '',
"ROWVERSION" DATE DEFAULT SYSDATE
) ON COMMIT DELETE ROWS ;
/
2、存储过程内容:
CREATE OR REPLACE PROCEDURE REPORT.PGETOPSETTLEMENTDETAILREPORT
(
V_IN_DAILYSTATFLAG IN NUMBER DEFAULT 0
, V_IN_STARTDATE IN DATE
, V_IN_ENDDATE IN DATE
, V_IN_SETTLEMENTEMPLOYEEIDS IN VARCHAR2
, V_IN_DEPARTMENTIDS IN VARCHAR2
, CV_out_RDReport OUT SYS_REFCURSOR
)
AS
/*
2024-09-19 guoshaoyue 参照REPORT.PGETOPSETTLEMENTTOTALREPORT==>REPORT.PGETOPSETTLEMENTDETAILREPORT
2018-05-21 xumin 创建存储过程 收费员汇总表(REPORT.PGETOPSETTLEMENTTOTALREPORT)
测试脚本(仅返回一行,另测试时需注释COMMIT;语句):
declare
c SYS_REFCURSOR;
v_row REPORT.TEMPPOPSETTLEMENTDETAIL%rowtype;
begin
REPORT.PGETOPSETTLEMENTDETAILREPORT(0,to_date('2019-02-01','yyyy-MM-dd'),to_date('2020-09-30','yyyy-MM-dd'),'1462,1739,1799','837069,837111,837114,837090',c);
FETCH c INTO v_row;
DBMS_OUTPUT.PUT_LINE('SETTLEMENTID: ' || v_row.SETTLEMENTID || ', STATUSFLAG: ' || v_row.STATUSFLAG || ', DISPLAYNAME: ' || v_row.DISPLAYNAME || ', TOTALAMOUNT: ' || v_row.TOTALAMOUNT || ', STATUSDESC: ' || v_row.STATUSDESC);
CLOSE c;
end;
*/
V_ListID number(10,0);
V_EndDATE DATE;
BEGIN
REPORT.GETPROCLISTID('report.PGETOPSETTLEMENTDETAILREPORT',V_ListID);
V_EndDATE:=V_IN_ENDDATE+1;
DELETE FROM REPORT.TEMPPOPSETTLEMENTDETAIL WHERE ROWVERSION < SYSDATE - 1.0/24/60*5;
IF V_IN_DAILYSTATFLAG = 0 THEN
BEGIN
INSERT INTO report.TEMPPOPSETTLEMENTDETAIL(TEMPPOPSETTLEMENTDETAILID, SETTLEMENTID, STATUSFLAG, ENCOUNTERID, DISPLAYNAME, ADMITDATEON, SEQNOTEXT, RECEIPTSEQNOTEXT, TOTALAMOUNT, ACCOUNTAMOUNT, STATUSDESC)
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.SETTLEMENTEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND a.SETTLEMENTORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND a.DAILYSTATID < 0
AND a.SUPPLYSTATID<0
AND a.STATUSFLAG < 3
AND ctv.CODEDTERMID=11667
UNION ALL
-- 退费,作废,重打
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.DROPEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND a.SETTLEMENTORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND (a.DROPSTATID < 0 OR a.DROPSTATID IS NULL )
AND a.SUPPLYSTATID<0
AND a.STATUSFLAG > 0
AND a.STATUSFLAG < 3
AND ctv.CODEDTERMID=11667;
END;
ELSIF V_IN_DAILYSTATFLAG = 1 THEN
BEGIN
INSERT INTO report.TEMPPOPSETTLEMENTDETAIL(TEMPPOPSETTLEMENTDETAILID, SETTLEMENTID, STATUSFLAG, ENCOUNTERID, DISPLAYNAME, ADMITDATEON, SEQNOTEXT, RECEIPTSEQNOTEXT, TOTALAMOUNT, ACCOUNTAMOUNT, STATUSDESC) --统计除补打外的所有发票
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, DATA.DAILYSTATLIST c, DATA.Dailystat d, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.SETTLEMENTID = c.DAILYSTATRESOURCEID
AND c.DAILYSTATID = d.DAILYSTATID
AND c.SETTLEMENTSTATUSCODEID =0
AND c.SUPPLYCODEID< 1
AND d.DAILYSTATKINDCODEID = 2
AND d.STATDATETIME>=V_IN_STARTDATE
AND d.STATDATETIME<V_EndDATE
AND d.STATORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND d.STATEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND ctv.CODEDTERMID=11667
UNION ALL
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, DATA.DAILYSTATLIST c, DATA.Dailystat d, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.SETTLEMENTID = c.DAILYSTATRESOURCEID
AND c.DAILYSTATID = d.DAILYSTATID
AND c.SETTLEMENTSTATUSCODEID IN (1,2)
AND c.SUPPLYCODEID< 1
AND d.DAILYSTATKINDCODEID = 2
AND d.STATDATETIME>=V_IN_STARTDATE
AND d.STATDATETIME<V_EndDATE
AND d.STATORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND d.STATEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND ctv.CODEDTERMID=11667;
END;
ELSIF V_IN_DAILYSTATFLAG = 2 THEN
BEGIN
INSERT INTO report.TEMPPOPSETTLEMENTDETAIL(TEMPPOPSETTLEMENTDETAILID, SETTLEMENTID, STATUSFLAG, ENCOUNTERID, DISPLAYNAME, ADMITDATEON, SEQNOTEXT, RECEIPTSEQNOTEXT, TOTALAMOUNT, ACCOUNTAMOUNT, STATUSDESC)
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.SETTLEMENTEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND a.SETTLEMENTORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND a.SETTLEMENTTIME>=V_IN_STARTDATE
AND a.SETTLEMENTTIME <V_EndDATE
AND a.SUPPLYSTATID<0
AND a.STATUSFLAG < 3
AND ctv.CODEDTERMID=11667
UNION ALL
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.DROPEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND a.DROPORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND a.DROPTIME >=V_IN_STARTDATE
AND a.DROPTIME <V_EndDATE
AND a.statusflag > 0
AND a.SUPPLYSTATID<0
AND a.STATUSFLAG < 3
AND ctv.CODEDTERMID=11667;
END;
END IF ;
OPEN CV_out_RDReport FOR
SELECT * FROM REPORT.TEMPPOPSETTLEMENTDETAIL WHERE TEMPPOPSETTLEMENTDETAILID=V_ListID;
COMMIT;
END PGETOPSETTLEMENTDETAILREPORT;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库