oracle常用SQL

• 创建一个表结构与STAT_OUT_RESULT的临时表

CREATE GLOBAL TEMPORARY TABLE TEMP_STAT_OUT_RESULT ON COMMIT DELETE ROWS AS 
select * from STAT_OUT_RESULT where 1=2

• 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（截取指定字符前面部分）