oracle常用SQL

  • PLSQL查询中文是问号(？？？？)
    image

  • 一列分隔成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 

--把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;

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的行，成功
  • 锁表后怎么解锁
--查询
select a.OS_USER_NAME, c.owner, c.object_name, b.sid, b.serial#, logon_time
  from v$locked_object a, v$session b, dba_objects c
 where a.session_id = b.sid
   and a.object_id = c.object_id
 order by b.logon_time;    
--kill
 alter system kill session 'sid,serial#';
  • oracle数据库服务器的最大允许连接数一般多少？
最大允许连接数并不是一个固定的值，而是可以根据数据库的硬件资源、性能要求以及业务需求进行配置的。这个值通常受到多个参数的影响，包括processes参数和sessions参数等。
 `SELECT name, value FROM v$parameter WHERE name IN ('processes', 'sessions');` 。processes，数据库实例支持的最大并发用户进程数。
sessions，数据库实例支持的最大会话数。
  • 匿名块，使用了BEGIN...END结构和FOR...LOOP循环。它的目的是遍历表1中的所有id，并对每个id执行某些操作
BEGIN
    FOR tbl IN (SELECT id FROM 表1) LOOP
        -- 在这里添加对每个id的操作
        -- 例如：DBMS_OUTPUT.PUT_LINE('ID: ' || tbl.id);
    END LOOP;
END;
  • 事务
BEGIN
  INSERT INTO employees (id, name) VALUES (1, 'Alice');
  UPDATE departments SET count = count + 1 WHERE id = 100;
  COMMIT; -- 提交事务
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- 如果发生异常，回滚事务
END;
  • 表里插入重复，怎么只留一条
--rowid =guid，peop_id=用户唯一id
 delete from 表1 where rowid in (
		  SELECT MIN(rowid) FROM 表1 a where peop_id='123'
		  group by name 
		  having(count(1)>1) 
       ) and peop_id='123';

posted on 2019-09-18 09:52  anjun_xf  阅读(211)  评论(0)  编辑  收藏  举报

相关博文：
· 查看oracle死锁
· ag-grid常用代码整理
· Oracle常用sql
· Orcale数据库
· oracle查询语句大全(oracle 基本命令大全一)
阅读排行：
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通

导航

TOP
点击右上角即可分享
微信分享提示