oracle常用SQL
-
PLSQL查询中文是问号(？？？？)
-
一列分隔成6列,如'1.2.3.4.5.6'分隔成6列
SELECT TO_NUMBER(REGEXP_SUBSTR('1.2.3.4.5.6','[^.]+',1,1)) T1
,NVL(TO_NUMBER(REGEXP_SUBSTR('1.2.3.4.5.6','[^.]+',1,2)),0) T2
,NVL(TO_NUMBER(REGEXP_SUBSTR('1.2.3.4.5.6','[^.]+',1,3)),0) T3
,NVL(TO_NUMBER(REGEXP_SUBSTR('1.2.3.4.5.6','[^.]+',1,4)),0) T4
,NVL(TO_NUMBER(REGEXP_SUBSTR('1.2.3.4.5.6','[^.]+',1,5)),0) T5
,NVL(TO_NUMBER(REGEXP_SUBSTR('1.2.3.4.5.6','[^.]+',1,6)),0) T6
from dual;
--返回6列：1 2 3 4 5 6
-
Oracle 自动位数补齐：右侧补零
select RPAD('1234',8,'0') from dual; -
改数据类型 更多：改列名、删列、加列
--把height从number(5)改为number(8,2)
alter table 表1 modify height number(8,2);
- 创建一个表结构与STAT_OUT_RESULT的临时表
CREATE GLOBAL TEMPORARY TABLE TEMP_STAT_OUT_RESULT ON COMMIT DELETE ROWS AS
select * from STAT_OUT_RESULT where 1=2
- 闪回，表误操作，可通过闪回找回，如下SQL表示2016-10-16 16:24:00是误操作的那个时间点，是个大概的时间，不用精确，在这个时间之前就是之前正确的数据，之后就是误操作后的数据
select * from doc_other as of timestamp to_timestamp('2016-10-16 16:24:00','yyyy-mm-dd hh24:mi:ss')
decode函数类似case when：select decode(classno,'1','一班','2','二班','其他班级') as 班级 from student- 一条SQL获得库中所有表及其字段
--用户所有表
SELECT a.table_name,t.comments FROM dba_tables a
left join user_tab_comments t on a.table_name = t.table_name
where a.owner='User名称' and a.table_name =upper('表名称')
--或
SELECT * FROM tab where tabtype='TABLE' and tname not like 'BIN$%'
-- 自定义表字段
SELECT a.column_name,a.data_type,a.data_length,a.DATA_PRECISION,a.DATA_SCALE,a.nullable,b.comments
FROM user_tab_columns a left join user_col_comments b on a.table_name=b.table_name and a.column_name=b.column_name
where a.table_name = upper('表名称')
ORDER BY a.column_id
- 闪回是Oracle备份恢复机制的一部分，闪回技术旨在快速恢复逻辑错误，对于物理损坏或是介质丢失的错误，闪回技术就回天乏术了。(前脚误删除，后脚赶快恢复用这种技术)
a,b变成'a','b'
SELECT ''''|| replace('a,b',',',''',''') || '''' FROM dual;
- where条件中加入特点判断条件（可用于update语句）
SELECT * FROM DOC_EXTEND where v_id='12' and (SELECT count(1) FROM doc_opeator op where v_id='12' and op.deleted_mark=0 and Incision_Type=2)>0
SELECT * FROM DOC_EXTEND where v_id='12' and exists(SELECT v_id FROM doc_opeator op where v_id='12' and op.deleted_mark=0 and Incision_Type=2)
- 同一个科室有多条床位信息，怎么实现每个科室取第一条后返回
SELECT t.* FROM (SELECT deptNo, bed,
row_number() over(partition BY deptNo ORDER BY startDate DESC) rn
FROM mrs_bedr
where unit_id = '{unitId}'
) t
where rn = 1
with关键字的使用（为一个SQL代码段，设置一个变量，然后可以select 这个变量）
with tbl as (
SELECT 'a',1 as a2,2 as a3 dual union all
SELECT 'b',21,12 dual union all
)
select '' as a,sum(a2),sum(a3) from tbl
union all
SELECT * FROM tbl
- 行转列，且返回1条（ORDINALNO是费用类型）
select
(SELECT amount FROM m_fee where v_id=a.v_id and ORDINALNO=1 ) as 总费用
,(SELECT amount FROM m_fee where v_id=a.v_id and ORDINALNO=2 ) as 自付金额
,(SELECT amount FROM m_fee where v_id=a.v_id and ORDINALNO=3 ) as 一般服务费
from m_fee a where v_id='{0}' and rownum = 1
- 库中加锁情况
select A.sid, b.serial#,
decode(A.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
c.object_name,
---b.username,
---b.osuser,
decode(a.lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
B.MACHINE,D.SPID ,b.PROGRAM
from v$lock a,v$session b,all_objects c,V$PROCESS D
where a.sid=b.sid and a.type in ('TM','TX')
and c.object_id=a.id1
AND B.PADDR=D.ADDR;
- 跨库查询语句
select a.acct_number, c.Name from Preaccount_Inf a left join PATIENT@数据库名 c on a.acct_number = c.patient_id
- 一次性插入多条
INSERT INTO DOC_FEE (ORDINALNO, AMOUNT)
select '1', 1 from dual union all
select '12',2 from dual union all
select '13',3 from dual
- 添加字段备注（再次执行就是修改）
- pl/slq 工具登录进去，选择表右键“编辑”，直接修改注释.
- 添加表注释：
Comment on table tb1Name is '个人信息';- 添加字段注释：
comment on column tb1Name.id is '行id';
- 为现有表添加字段和注释
alter table doc_extend add abc_Code VARCHAR2(20)
comment on column doc_extend.abc_Code is '编码';
- 分页
int startNum = 1 + (pageSize * (currNum - 1));
int endNum = pageSize * currNum;
SELECT * FROM (SELECT ROWNUM AS rn, a.*
FROM log_error a
where a.dt >= to_date('2019-01-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
and a.dt <= to_date('2020-04-16 23:59:59', 'yyyy-MM-dd HH24:mi:ss')
) t
WHERE t.rn between startNum and endNum;
- 分页存储过程
create or replace procedure paging
(tableName in varchar2 ,--表名
pageSizes in number,--每页显示记录数
pageNow in number,--当前页
rowNums out number,--总记录数
pageNum out number,--总页数
paging_cursor out pagingPackage.paging_cursor) is
--定义部分
--定义sql语句，字符串
v_sql varchar2(1000);
--定义两个整数，用于表示每页的开始和结束记录数
v_begin number:=(pageNow-1)*pageSizes+1;
v_end number:=pageNow*pageSizes;
begin
--执行部分
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
--把游标和sql语句关联
open paging_cursor for v_sql;
--计算rowNums和pageNum
--组织一个sql语句
v_sql:='select count(*) from '||tableName;
--执行该sql语句,并赋给rowNums
execute immediate v_sql into rowNums;
--计算pageNum
if mod(rowNums,pageSizes)=0 then
pageNum := rowNums/pageSizes;
else
pageNum := rowNums/pageSizes+1;
end if;
end;
- 统计医疗检查阴性占比、阳性占比
select 年度,月份,申请医疗单位,医院分部,申请科室,
round(SUM(DECODE(阴阳性,'阳性',计数,0))/sum(计数)* 100, 2) as "阳性占比%",
round(SUM(DECODE(阴阳性,'阴性',计数,0))/sum(计数)* 100, 2) as "阴性占比%"
from (
SELECT 年度 , 月份 , 申请医疗单位 , 医院分部 ,申请科室,阴阳性,count(1) 计数 FROM (
SELECT distinct
to_char(st.studyTime, 'yyyy') as "年度",
to_char(st.studyTime, 'mm') as "月份",
nvl(st.deviceType,' ') as "设备类型",
nvl(st.deviceId,' ') as "检查设备id",
st.pat_kind as "患者类型id",
nvl(st.req_unit,' ') as "申请医疗单位",
CASE rt.positive
WHEN 0 THEN '未知'
WHEN 1 THEN '阴性'
WHEN 2 THEN '阳性'
WHEN 3 THEN '其它'
Else '--'
END as 阴阳性
FROM study st
where 1=1
and st.studyTime >= to_date('2019-01-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
and st.studyTime <= to_date('2020-04-16 23:59:59', 'yyyy-MM-dd HH24:mi:ss')
) tbl
group by 年度 , 月份 , 申请医疗单位 , 医院分部 ,申请科室 ,阴阳性
ORDER BY 年度 , 月份 , 申请医疗单位 , 医院分部 ,申请科室 ,阴阳性
) group by 年度 , 月份 , 申请医疗单位 , 医院分部 ,申请科室
- 怎么把“,1,312”分割并以table返回
SELECT REGEXP_SUBSTR(',1,12', '[^,]+', 1,rownum) FROM dual CONNECT BY rownum <= LENGTH(',1,12') - LENGTH(REPLACE (',1,12', ',', ''))+1;
- 需求：inpatient表NATIVE_PLACE（籍贯，存“省-市-县”三级code,如：420000,420100,420106），想一条SQL得到“湖北省武汉市武昌区”(弊端：返回的name顺序不对)
SELECT wm_concat(data_value) FROM dict_value where deleted_mark=0 and de_code='GB.中华人民共和国县级及县级以上行政区划' and CHARINDEX(data_key,(SELECT NATIVE_PLACE FROM inpatient bb where bb.inp_no='120585')) >0 ORDER BY data_sort desc
- 含 B08 编码的数据 （列转行，行转列，多行转一行）
select * from(
select
(
--含 B08 编码的数据, 其中wm_concat 列转行
SELECT wm_concat(diag_code||'|') FROM DIAGNOSIS di where di.deleted_mark=0 and di.v_id=a.v_id
) as d_code
from INPATIENT a where a.***
) t where CHARINDEX('B08|',d_code)>0
-
在PL/SQL developer中表名
右键 -> 描述查看表各字段信息：中英文、必填项、默认值信息。
-
递归获得机构表中信息
--递归，生成机构全路径。
SELECT t.org_id, t.org_name, sys_connect_by_path( t.org_id, ',') as org_path
FROM sys_org t where t.deleted_mark=0
START WITH t.org_id = '0-803'--根id
CONNECT BY PRIOR t.org_id = parent_id
--递归
select org_id,org_name,t.parent_id,t.parent_path
from sys_org t where t.deleted_mark=0
start with t.org_id = '0-803'--根id
connect by prior t.org_id = t.parent_id
order by t.org_id desc
- 需求：两个库A和B，怎么从B库同名表中数据同步到A库（快速创建表）
SELECT * FROM device在表名上右键选“重命名”，然后create table device as SELECT * FROM device@acs_145就能把B库中同名表数据同步过来- 表快速备份
create table People_temp as select a.* from People a where b.log_time is not null;
- 执行下面语句，可以对device查询结果执行插入、修改
SELECT * FROM device
for update
- oracle 怎么知道表字段必填
--查询某张表中的字段名，字段类型，是否为空，字段长度等信息
SELECT COLUMN_ID, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = '表名称（注意大小写）'
ORDER BY COLUMN_ID;
--查询某张表中的字段名，字段类型，是否为空，字段长度等信息
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '表名称（注意大小写）'
- sql代码段
declare
i number(2) := 10; --为变量赋值
s varchar2(10) := 'huawei';
begin
dbms_output.put_line(i); --输出：10
dbms_output.put_line(s); --输出：huawei
end;
- 查看数据库实例名称
select * from v$instance或查看数据库ip地址菜单：help-> support info -> TNS名 - Oracle PL/SQL 对同一table执行多条insert、update、delete遇到的问题—— 多条update等放到一个事务中执行。注意：下面SQL如果发生异常，虽然会回滚，但是不会有任何反馈！！！
begin
update tbl a set a.out_time={0} ,a.out_dept='{1}',a.days ={2} where a.visit_id='{3}';
update tbl2 b set b.out_time={0} where b.mother_visitid='{3}' and b.mother_type=1;
commit;
dbms_output.put_line(1);
exception
when others then
rollback;
end;
- 查询某个表的约束条件
SELECT * FROM all_constraints WHERE table_name = '表名称'
select * from all_tab_comments--查询所有用户的表,视图等
select * from user_tab_comments--【查询本用户的表,视图等】
select * from all_col_comments--查询所有用户的表的列名和注释.
select * from user_col_comments--查询本用户的表的列名和注释
select * from all_tab_columns--查询所有用户的表的列名等信息(详细但是没有备注).
select * from user_tab_columns--查询本用户的表的列名等信息(详细但是没有备注).
select table_name from all_tables--查询所有用户的表
- 获得年月日季
--extract获得年月日（返回值number型）
select extract(year from sysdate) from dual--年
select extract(month from sysdate) from dual;--月
select extract(day from sysdate) from dual--日
--to_char获得年月日季
SELECT to_char(sysdate,'q') FROM dual--季
SELECT to_char(sysdate,'yyyy') FROM dual--年
SELECT to_char(sysdate,'mm') FROM dual--月
SELECT to_char(sysdate,'dd') FROM dual--日
SELECT to_char(sysdate,'d') FROM dual--星期中的第几天
SELECT to_char(sysdate,'DAY') FROM dual--星期几
SELECT to_char(sysdate,'ddd') FROM dual--一年中的第几天
--一年各月对应的季度
select distinct to_char(日期, 'q') 季度,
to_char(日期, 'yyyymm') 月份
from (select to_date('2019-01', 'yyyy-mm') + (rownum - 1) 日期
from user_objects
where rownum < 367
and to_date('2019-01-01', 'yyyy-mm-dd') + (rownum - 1) <
to_date('2020-01-01', 'yyyy-mm-dd')
);
- 其他
select sysdate-to_date('2000-8-1','fm yyyy-mm-dd hh:mi:ss') from dual --已活了几天
select months_between(sysdate,to_date('2000-8-1','fm yyyy-mm-dd hh:mi:ss')) from dual;--已活了几月
select months_between(sysdate,to_date('2000-8-1','fm yyyy-mm-dd hh:mi:ss'))/12 from dual;--已活了几年
select (sysdate-to_date('2000-8-1','fm yyyy-mm-dd hh:mi:ss'))/7 from dual;--已活了几周
select * from v$instance--数据库实例信息- oracle 怎么查询每天8点到17点30的数据？
select * from tbl where to_char(st.study_time,'hh24:mi:ss') between '08:00:00' and '17:30:59'
-
oracle 字段是
date类型，保存内容仅到天如2021-4-28等同2021-4-28 00:00:00，查询时等同于后面日后面跟上了“00:00:00” -
返回到日，如“2013-01-06”：
select trunc(sysdate) from dual -
SELECT substr('abc.12',0,instr('abc.12','.')-1) FROM dual;返回：abc（截取指定字符前面部分） -
存储过程不能写明文ddl语句，可以写到动态语句里.
-
函数就是一种特殊存储过程，必须有返回值
-
静态/动态数据字典
--静态数据字典
SELECT * FROM dba_tables
SELECT * FROM dba_segments
SELECT * FROM dba_indexes
SELECT * FROM all_tables
SELECT * FROM all_indexes
SELECT * FROM user_tables
SELECT * FROM user_segments
SELECT * FROM user_indexes
--动态数据字典
--v$ 本地动态视图
SELECT * FROM v$instance
SELECT * FROM v$log
SELECT * FROM v$lock
--gv$ 全局（RAC架构下所有实例）动态视图
SELECT * FROM gv$instance
SELECT * FROM gv$log
SELECT * FROM gv$lock
- PLSQL
--1
declare
v_DT date;
begin
v_DT := to_date('2022-03-28 23:59:59','yyyy-mm-dd hh24:mi:ss');
delete from tient_point a where a.record_time >= trunc(v_DT);
insert into tient_point
(UNIT_ID,VISIT_ID,DEPT_ID,RECORD_TIME)
select UNIT_ID,VISIT_ID,v_DT from patient a
where a.deleted_mark = 0 and a.enter_time<v_dt
end;
--2
declare
abc varchar2(40):='张新悦';
info VARCHAR2(40);
begin
SELECT a into info FROM ( SELECT abc as a FROM dual) where a='张新悦';
DBMS_OUTPUT.PUT_LINE(info);
end;
- Oracle将查询结果存入临时表的写法
CREATE GLOBAL TEMPORARY TABLE tmptable
ON COMMIT PRESERVE ROWS
AS
SELECT * FROM tablename
drop table tablename
- 获得条数
declare
v_Count number(10);
begin
select count(*) into v_Count from user_all_tables t where t.table_name = upper('ydyl_ordmsgrecord');
if v_Count = 0 then return;end if;
dbms_output.put_line(v_Count);
end;
- 执行拼接SQL？
declare
v_Sql varchar2(4000);
begin
v_Sql :='SELECT 1 as aa FROM dual';
execute immediate v_Sql;
commit;
-- dbms_output.put_line(v_Sql);
end;
- 某段时间
select ADD_MONTHS(sysdate,-1) from dual;--一个月前
select TRUNC(SYSDATE - 1) from dual; --一天前
select TRUNC(to_date('2021-11-01','yyyy-mm-dd') - 1) from dual;--指定天前一天
- 快速对某个表全备份
--备份
CREATE TABLE T_URM_MINF_20190321_BACK AS SELECT * FROM T_URM_MINF;
--恢复
INSERT INTO T_URM_MINF SELECT * FROM T_URM_MINF_20190321_BACK;
- 速度优化总结
1、一次查全年数据耗时3min，优化到3s。做法：连接表不要跨库，连接表后面不要跟"||"，如`left join user_tbl t ot on a.code||a.code1 =t.code||t.code2`
- 判断是否存在表或字段并创建
--创建表
declare tableExist number;
begin
select count(1) into tableExist from user_tables where table_name=upper('TTB') ;
if tableExist = 0 then
EXECUTE IMMEDIATE (
'create table ttb(
idd number(1)
)
'
);
end if;
end;
--创建一个字段
declare colExist number;
begin
select count(1) into colExist from all_tab_columns where table_name in ('TTB') and column_name in ('DD2');
if colExist = 0 then
EXECUTE IMMEDIATE (' alter table ttb add dd2 varchar2(20) ');
end if;
end;
- 执行有返回值的存储过程
declare aa number;--返回值
begin
PRC_GET_NO ('001','119', aa);
dbms_output.put_line(aa);
end;
------
CREATE OR REPLACE PROCEDURE PRC_GETNO
(
v_uId in varchar2 default '',
v_dId in varchar2 default '',
v_caseNo out number
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
begin
begin
v_caseNo := 0;
if (v_uId is not null and v_dId is not null) then
SELECT nvl(now_no,0)+1 into v_caseNo FROM csno where U_ID=v_uId and d_no=v_dId;
else
SELECT nvl(now_no,0)+1 into v_caseNo FROM csno where U_ID=v_uId;
end if;
end;
if( v_uId is not null and v_dId is not null and v_caseNo >0) then
update csno set now_no =v_caseNo where U_ID=v_uId and d_no=v_dId;
elsif ( v_uId is not null and v_caseNo >0) then
update csno set now_no =v_caseNo where U_ID=v_uId ;
end if;
begin
commit;
exception when others then rollback;
end;
end;
事务
- Oracle数据库之事务
- 数据异常情况：脏读、不可重复读、幻读
- 锁类型：排它锁（X锁）和共享锁（S锁，Share）。x--不能读改；s--能读不能改。
- 常见SQL锁定模式：
select * from xx 行级共享锁（RS）
insert into xx 行级排他锁（RX）
update xx 行级排他锁（RX）
delete from xx 行级排他锁（RX）
select * from xx for update 行级共享锁（RS）
- 行级排他锁：允许其他的事务修改相同表里其他行，或通过lock命令对相同表添加RX锁定，但是不允许在添加排他锁（X锁）。
for update（行级锁，排他锁） 仅锁住where条件返回的数据，不让其修改和删除（阻塞，改删where条件外的内容不会发生阻塞），但是可以被查询到。
SELECT * FROM s_user where user_name='tome' for update--先锁住用户名是“tome”的行
SELECT * FROM s_user where user_name='tome' --然后查询tome，有返回
update s_user set create_time =sysdate where user_name='tome' --改tome的行，发生阻塞
update s_user set create_time =sysdate where user_name='lily'--改lily的行，成功
