- 创建一个表结构与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（截取指定字符前面部分）