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杂文之自定义函数与随机函数1、显示使用游标(并定义返回记录类型)
DECLARE
TYPE detail_record IS RECORD
( ENCOUNTERID PRPA.ENCOUNTER.ENCOUNTERID%type,
SICKBEDID PRPA.ENCOUNTER.SICKBEDID%type,
DISPLAYNAME PRPA.ENCOUNTER.DISPLAYNAME%type,
BIRTHTIME PRPA.ENCOUNTER.BIRTHTIME%type,
SEQNOTEXT PRPA.ENCOUNTER.SEQNOTEXT%type);
TYPE detail_cursor IS REF CURSOR RETURN detail_record;
v_prodcur detail_cursor ;
v_prodrec detail_record;
BEGIN
OPEN v_prodcur FOR SELECT ENCOUNTERID, SICKBEDID, DISPLAYNAME, BIRTHTIME, SEQNOTEXT FROM PRPA.ENCOUNTER
WHERE ISDELETED=0 AND ENCOUNTERKINDCODEID=1 AND RESPONSIBLEDEPARTMENTID=4;
LOOP
FETCH v_prodcur INTO v_prodrec ;
EXIT WHEN v_prodcur%notfound;
dbms_output.put_line ('ENCOUNTERID:' || CAST(v_prodrec.ENCOUNTERID AS VARCHAR));
dbms_output.put_line ('SEQNOTEXT:' || v_prodrec.SEQNOTEXT);
END LOOP;
CLOSE v_prodcur;
END;
CREATE OR REPLACE TRIGGER REPORT.DOCTORSHIFTRECORDDETAIL_TRG
AFTER INSERT OR DELETE OR UPDATE OF ISNEWINPATIENT, ISOUTPATIENT, ISTRANSFEREDIN, ISTRANSFEREDOUT, ISDEATH, ISCRITICAL, ISSURGERY, ISLEVEL4SURGERY, ISBEDSIDE, ISSPECIAL, ISOTHER, ISDELETED
ON REPORT.DOCTORSHIFTRECORDDETAIL
FOR EACH ROW
begin
merge into REPORT.DOCTORSHIFTRECORD a
using (SELECT DOCTORSHIFTRECORDID, COUNT(1) CURRENTTOTALCOUNT FROM REPORT.DOCTORSHIFTRECORDDETAIL WHERE ISDELETED=0 AND DOCTORSHIFTRECORDID = :NEW.DOCTORSHIFTRECORDID GROUP BY DOCTORSHIFTRECORDID) b
on (a.DOCTORSHIFTRECORDID=b.DOCTORSHIFTRECORDID AND a.ISDELETED=0)
when matched then update SET a.CURRENTTOTALCOUNT=b.CURRENTTOTALCOUNT;
merge into REPORT.DOCTORSHIFTRECORD a
using (select DOCTORSHIFTRECORDID, SUM(ISNEWINPATIENT) NEWINPATIENTCOUNT, SUM(ISOUTPATIENT) OUTPATIENTCOUNT, SUM(ISTRANSFEREDIN) TRANSFEREDINCOUNT, SUM(ISTRANSFEREDOUT) TRANSFEREDOUTCOUNT
, SUM(ISDEATH) DEATHCOUNT, SUM(ISCRITICAL) CRITICALCOUNT, SUM(ISSURGERY) SURGERYCOUNT, SUM(ISLEVEL4SURGERY) LEVEL4SURGERYCOUNT, SUM(ISBEDSIDE) BEDSIDECOUNT
, SUM(ISSPECIAL) SPECIALCOUNT, SUM(ISOTHER) OTHERCOUNT FROM REPORT.DOCTORSHIFTRECORDDETAIL WHERE ISDELETED=0 AND DOCTORSHIFTRECORDID=:NEW.DOCTORSHIFTRECORDID GROUP BY DOCTORSHIFTRECORDID) b
on (a.DOCTORSHIFTRECORDID=b.DOCTORSHIFTRECORDID AND a.ISDELETED=0)
when matched then update SET a.NEWINPATIENTCOUNT=b.NEWINPATIENTCOUNT, a.OUTPATIENTCOUNT=b.OUTPATIENTCOUNT, a.TRANSFEREDINCOUNT=b.TRANSFEREDINCOUNT, a.TRANSFEREDOUTCOUNT=b.TRANSFEREDOUTCOUNT
, a.DEATHCOUNT=b.DEATHCOUNT, a.CRITICALCOUNT=b.CRITICALCOUNT, a.SURGERYCOUNT=b.SURGERYCOUNT, a.LEVEL4SURGERYCOUNT=b.LEVEL4SURGERYCOUNT, a.BEDSIDECOUNT=b.BEDSIDECOUNT
, a.SPECIALCOUNT=b.SPECIALCOUNT, a.OTHERCOUNT=b.OTHERCOUNT;
end;
2、比较两个时间(按分钟、小时、天数):
select sysdate,to_date('2023-6-5 11:35:39','yyyy-mm-dd hh24:mi:ss'), ROUND(TO_NUMBER(sysdate - to_date('2023-6-5 11:35:39','yyyy-mm-dd hh24:mi:ss')) * 24 * 60) from dual;
select sysdate,to_date('2023-6-5 11:35:39','yyyy-mm-dd hh24:mi:ss'), ROUND(TO_NUMBER(sysdate - to_date('2023-6-5 11:35:39','yyyy-mm-dd hh24:mi:ss')) * 24) from dual;
select sysdate,to_date('2022-12-9 11:35:39','yyyy-mm-dd hh24:mi:ss'), ROUND(TO_NUMBER(sysdate - to_date('2022-12-9 11:35:39','yyyy-mm-dd hh24:mi:ss'))) from dual;
3、触发器(更新某一个字段时触发):
CREATE TABLE "PORX"."COMBINEDMEDICATIONREQUESTSTATUSMOIDFIED"
("COMBINEDMEDICATIONREQUESTID" NUMBER(10,0) NOT NULL ,
"OLDSTATUSCODEID" NUMBER(5,0) DEFAULT (-1) NOT NULL ,
"NEWSTATUSCODEID" NUMBER(5,0) DEFAULT (-1) NOT NULL ,
"ROWVERSION" DATE DEFAULT (sysdate))
TABLESPACE "JXTBSPACE" ;
CREATE OR REPLACE TRIGGER "PORX"."COMBINEDMEDICATIONREQUEST_TRG"
BEFORE UPDATE OF STATUSCODEID OR DELETE ON "PORX"."COMBINEDMEDICATIONREQUEST" FOR EACH ROW
BEGIN
CASE
WHEN UPDATING ('STATUSCODEID') THEN
IF :new.STATUSCODEID != :old.STATUSCODEID THEN
INSERT INTO "PORX"."COMBINEDMEDICATIONREQUESTSTATUSMOIDFIED"(COMBINEDMEDICATIONREQUESTID, OLDSTATUSCODEID, NEWSTATUSCODEID)
VALUES(:old.COMBINEDMEDICATIONREQUESTID, :old.STATUSCODEID, :new.STATUSCODEID);
END IF;
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20003, '不能物理删除记录');
END CASE;
END;
4、NULLIF 函数在触发器的典型应用:
CREATE OR REPLACE TRIGGER REPORT.trg_updDOCTORSHIFTRECORDDETAIL AFTER UPDATE ON REPORT.DOCTORSHIFTRECORDDETAIL FOR each row
DECLARE
v_ip varchar2(30);
v_user varchar2(30);
BEGIN
SELECT sys_context('userenv', 'ip_address'), sys_context('userenv', 'SESSION_USER') INTO v_ip, v_user FROM dual;
IF :new.ISGENERATED = 0 THEN
INSERT INTO "REPORT"."DOCTORSHIFTRECORDDETAILTRACE" (DOCTORSHIFTRECORDDETAILID, DOCTORSHIFTRECORDID, ORDINAL, ENCOUNTERID, SICKBEDID, SICKBEDNO, DISPLAYNAME, BIRTHTIME, GENDERCODEID, GENDERDESC, SEQNOTEXT
, ISNEWINPATIENT, ISOUTPATIENT, ISTRANSFEREDIN, ISTRANSFEREDOUT, ISDEATH, ISCRITICAL, ISSURGERY, ISLEVEL4SURGERY, ISBEDSIDE, ISSPECIAL, ISOTHER, ISGENERATED
, GENERATEDON, ILLNESSSTATEBRIEF, CREATEEMPLOYEEID, MODIFIEDEMPLOYEEID, ROWVERSION, ISDELETED, DIAGNOSIS, SURGERIES, NOTE, SICKBEDORDINAL
, N_DOCTORSHIFTRECORDID, N_ORDINAL, N_ENCOUNTERID, N_SICKBEDID, N_SICKBEDNO, N_DISPLAYNAME, N_BIRTHTIME, N_GENDERCODEID, N_GENDERDESC, N_SEQNOTEXT
, N_ISNEWINPATIENT, N_ISOUTPATIENT, N_ISTRANSFEREDIN, N_ISTRANSFEREDOUT, N_ISDEATH, N_ISCRITICAL, N_ISSURGERY, N_ISLEVEL4SURGERY, N_ISBEDSIDE, N_ISSPECIAL, N_ISOTHER, N_ISGENERATED
, N_GENERATEDON, N_ILLNESSSTATEBRIEF, N_CREATEEMPLOYEEID, N_MODIFIEDEMPLOYEEID, N_ROWVERSION, N_ISDELETED, N_DIAGNOSIS, N_SURGERIES, N_NOTE, N_SICKBEDORDINAL
, USERNAME, IP)
VALUES(:old.DOCTORSHIFTRECORDDETAILID, :old.DOCTORSHIFTRECORDID, :old.ORDINAL, :old.ENCOUNTERID, :old.SICKBEDID, :old.SICKBEDNO, :old.DISPLAYNAME, :old.BIRTHTIME, :old.GENDERCODEID, :old.GENDERDESC, :old.SEQNOTEXT
, :old.ISNEWINPATIENT, :old.ISOUTPATIENT, :old.ISTRANSFEREDIN, :old.ISTRANSFEREDOUT, :old.ISDEATH, :old.ISCRITICAL, :old.ISSURGERY, :old.ISLEVEL4SURGERY, :old.ISBEDSIDE, :old.ISSPECIAL, :old.ISOTHER, :old.ISGENERATED
, :old.GENERATEDON, :old.ILLNESSSTATEBRIEF, :old.CREATEEMPLOYEEID, :old.MODIFIEDEMPLOYEEID, :old.ROWVERSION, :old.ISDELETED, :old.DIAGNOSIS, :old.SURGERIES, :old.NOTE, :old.SICKBEDORDINAL
, NULLIF(:new.DOCTORSHIFTRECORDID, :old.DOCTORSHIFTRECORDID), NULLIF(:new.ORDINAL, :old.ORDINAL), NULLIF(:new.ENCOUNTERID, :old.ENCOUNTERID), NULLIF(:new.SICKBEDID, :old.SICKBEDID), NULLIF(:new.SICKBEDNO, :old.SICKBEDNO)
, NULLIF(:new.DISPLAYNAME, :old.DISPLAYNAME), NULLIF(:new.BIRTHTIME, :old.BIRTHTIME), NULLIF(:new.GENDERCODEID, :old.GENDERCODEID), NULLIF(:new.GENDERDESC, :old.GENDERDESC), NULLIF(:new.SEQNOTEXT, :old.SEQNOTEXT)
, NULLIF(:new.ISNEWINPATIENT, :old.ISNEWINPATIENT), NULLIF(:new.ISOUTPATIENT, :old.ISOUTPATIENT), NULLIF(:new.ISTRANSFEREDIN, :old.ISTRANSFEREDIN), NULLIF(:new.ISTRANSFEREDOUT, :old.ISTRANSFEREDOUT), NULLIF(:new.ISDEATH, :old.ISDEATH)
, NULLIF(:new.ISCRITICAL, :old.ISCRITICAL), NULLIF(:new.ISSURGERY, :old.ISSURGERY), NULLIF(:new.ISLEVEL4SURGERY, :old.ISLEVEL4SURGERY), NULLIF(:new.ISBEDSIDE, :old.ISBEDSIDE), NULLIF(:new.ISSPECIAL, :old.ISSPECIAL)
, NULLIF(:new.ISOTHER, :old.ISOTHER), NULLIF(:new.ISGENERATED, :old.ISGENERATED), NULLIF(:new.GENERATEDON, :old.GENERATEDON), NULLIF(:new.ILLNESSSTATEBRIEF, :old.ILLNESSSTATEBRIEF), NULLIF(:new.CREATEEMPLOYEEID, :old.CREATEEMPLOYEEID)
, NULLIF(:new.MODIFIEDEMPLOYEEID, :old.MODIFIEDEMPLOYEEID), NULLIF(:new.ROWVERSION, :old.ROWVERSION), NULLIF(:new.ISDELETED, :old.ISDELETED), NULLIF(:new.DIAGNOSIS, :old.DIAGNOSIS), NULLIF(:new.SURGERIES, :old.SURGERIES), NULLIF(:new.NOTE, :old.NOTE), NULLIF(:new.SICKBEDORDINAL, :old.SICKBEDORDINAL)
, v_user, v_ip);
END if;
END trg_updDOCTORSHIFTRECORDDETAIL;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库