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 v_cnt number;
BEGIN
SELECT count(1) INTO v_cnt FROM dba_tables WHERE owner='ACT' AND TABLE_NAME='PHYSICALORDERPRESCRIPTIONMAP' ;
IF v_cnt = 0 THEN
EXECUTE immediate
'CREATE TABLE "ACT"."PHYSICALORDERPRESCRIPTIONMAP"
( "PHYSICALREGISTERID" NUMBER(10,0) NOT NULL ENABLE,
"PHYSICALREGISTERITEMID" NUMBER(10,0) NOT NULL ENABLE,
"ENCOUNTERID" NUMBER(10,0) NOT NULL ENABLE,
"ORDERPRESCRIPTIONID" NUMBER(10,0) NOT NULL ENABLE,
"AMOUNT" NUMBER(18,4),
"ATTACHAMOUNT" NUMBER(18,4),
"ISDELETED" NUMBER(1,0) DEFAULT 0,
"ROWVERSION" TIMESTAMP (6) DEFAULT systimestamp,
"PHYSICALPARTYEXAMLABELID" NUMBER(10,0),
"PHYSICALITEMCLASSIFICATIONFLAG" NUMBER(3,0) DEFAULT 0,
"COMPOSITEITEMID" NUMBER(10,0),
"PHYSICALREGISTERBALANCEID" NUMBER(10,0),
"PEREGISTERDATE" DATE,
"PESEQNO" NUMBER(10,0),
"STATUSCODEID" NUMBER(10,0) DEFAULT (0),
CONSTRAINT "PHYSICALORSMAP_PK" PRIMARY KEY ("PHYSICALREGISTERID", "PHYSICALREGISTERITEMID", "ENCOUNTERID", "ORDERPRESCRIPTIONID"))';
END if;
end;
/
2、判断是否全局临时表已存在,不存在则创建:
DECLARE v_cnt number;
BEGIN
SELECT count(1) INTO v_cnt FROM dba_tables WHERE owner='ACT' AND TABLE_NAME='TEMPPHYSICALOPACCOUNTLIST' ;
IF v_cnt = 0 THEN
EXECUTE immediate
'CREATE GLOBAL TEMPORARY TABLE "ACT"."TEMPPHYSICALOPACCOUNTLIST"
( "ACCOUNTLISTID" NUMBER(10,0),
"ORDERPRESCRIPTIONID" NUMBER(10,0),
"PARENTORDERPRESCRIPTIONID" NUMBER(10,0),
"ENCOUNTERID" NUMBER(10,0),
"ACCOUNTINGORGANIZATIONID" NUMBER(10,0),
"COSTORGANIZATIONID" NUMBER(10,0),
"ACCOUNTINGEMPLOYEEID" NUMBER(10,0),
"ACCOUNTINGDATETIME" DATE,
"OCCURDATE" DATE,
"APPENDDATETIME" DATE,
"EXECORGANIZATIONID" NUMBER(10,0),
"ACCOUNTINGDOCTORID" NUMBER(10,0),
"ORDERSOURCEFLAG" NUMBER(3,0),
"MAINCONSUMABLECODEID" NUMBER(10,0),
"MAINCONSUMABLEID" NUMBER(10,0),
"CONSUMABLECODEID" NUMBER(10,0),
"CONSUMABLEID" NUMBER(10,0),
"PRICE" NUMBER(18,4),
"QUANTITY" NUMBER(18,4),
"AMOUNT" NUMBER(18,4),
"FEEKINDID" NUMBER(10,0) DEFAULT -1,
"PAYPROPORTION" NUMBER(18,4) DEFAULT 10000,
"HASPAID" NUMBER(1,0) DEFAULT 0,
"ISFEEKINDFROMCALC" NUMBER(1,0) DEFAULT 0,
"STANDARDPRICE" NUMBER(18,4),
"ISDUPLICATEREMOVAL" NUMBER(1,0) DEFAULT 0,
"PHYSICALCLASSIFICATIONFLAG" NUMBER(10,0) DEFAULT 1,
"PHYSICALITEMCLASSIFICATIONFLAG" NUMBER(10,0) DEFAULT 0 ) ON COMMIT DELETE ROWS';
END if;
end;
/
DECLARE
v_cnt number(10,0);
BEGIN
SELECT count(1) INTO v_cnt FROM dba_tab_columns c WHERE c.owner='ACT' AND TABLE_NAME='TEMPPHYSICALOPACCOUNTLIST' AND c.column_name = 'ISDUPLICATEREMOVAL1';
IF (v_cnt = 0) THEN
EXECUTE immediate 'ALTER TABLE "ACT"."TEMPPHYSICALOPACCOUNTLIST" ADD "ISDUPLICATEREMOVAL1" NUMBER(1,0) DEFAULT 0';
END if;
END;
/
DECLARE
v_cnt number(10,0);
BEGIN
SELECT count(1) INTO v_cnt FROM dba_tab_columns c WHERE c.owner='ACT' AND TABLE_NAME='TEMPPHYSICALOPACCOUNTLIST' AND c.column_name = 'PHYSICALCLASSIFICATIONFLAG';
IF (v_cnt = 0) THEN
EXECUTE immediate 'ALTER TABLE "ACT"."TEMPPHYSICALOPACCOUNTLIST" ADD "PHYSICALCLASSIFICATIONFLAG" NUMBER(10,0) DEFAULT 1';
END if;
END;
/
DECLARE
v_cnt number(10,0);
BEGIN
SELECT count(1) INTO v_cnt FROM dba_tab_columns c WHERE c.owner='ACT' AND TABLE_NAME='TEMPPHYSICALOPACCOUNTLIST' AND c.column_name = 'PHYSICALITEMCLASSIFICATIONFLAG';
IF (v_cnt = 0) THEN
EXECUTE immediate 'ALTER TABLE "ACT"."TEMPPHYSICALOPACCOUNTLIST" ADD "PHYSICALITEMCLASSIFICATIONFLAG" NUMBER(10,0) DEFAULT 0';
END if;
END;
/
合集:
ORACLE
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库