数据库
金钱处理
保留精度
select CAST((to_number('20.0000') + to_number('20.0000')) AS NUMBER (10, 4)) from dual;
-- 40.0000
分组
分组后展示非分组数据
select COLLECT_BWAY_ID,wm_concat(COLLECT_BWAY_NAME) from sk_way_info t group by COLLECT_BWAY_ID;
分组统计
select COLLECT_BWAY_ID,count(1) from sk_way_info t group by COLLECT_BWAY_ID order by count(1) desc;
select COLLECT_BWAY_ID,count(1) from sk_way_info t group by COLLECT_BWAY_ID having count(1) > 2 order by count(1) desc;
虚表
虚表+union all、union模拟表
with tmp as (select '021501-1137' as ORDER_ID, 'GJ' as PRODUCT_TYPE, 'GJ' as PRODUCT_ID, 3552.0000 as SELL_RMB_SUM from dual
union
select '021501-1137' as ORDER_ID, 'GJ' as PRODUCT_TYPE, 'GJ' as PRODUCT_ID, 3860.0000 as SELL_RMB_SUM from dual
union
select '021501-1169' as ORDER_ID, 'GJ' as PRODUCT_TYPE, 'GJ' as PRODUCT_ID, 8660.0000 as SELL_RMB_SUM from dual
union
select '021501-1169' as ORDER_ID, 'GJ' as PRODUCT_TYPE, 'GJ' as PRODUCT_ID, 8540.0000 as SELL_RMB_SUM from dual
) select tmp.*, o.* from tmp left join ORDER_FLOW_INFO o on tmp.ORDER_ID = o.ORDER_ID
;
使用虚表+left join，排查出不在左表中的数据
with tmp as (select '021501-1137' as ORDER_ID, 'GJ' as PRODUCT_TYPE, 'GJ' as PRODUCT_ID, 3552.0000 as SELL_RMB_SUM from dual
union
select '021501-1137' as ORDER_ID, 'GJ' as PRODUCT_TYPE, 'GJ' as PRODUCT_ID, 3860.0000 as SELL_RMB_SUM from dual
union
select '021501-1169' as ORDER_ID, 'GJ' as PRODUCT_TYPE, 'GJ' as PRODUCT_ID, 8660.0000 as SELL_RMB_SUM from dual
union
select '021501-1169' as ORDER_ID, 'GJ' as PRODUCT_TYPE, 'GJ' as PRODUCT_ID, 8540.0000 as SELL_RMB_SUM from dual
) select tmp.*, o.* from tmp left join ORDER_FLOW_INFO o on tmp.ORDER_ID = o.ORDER_ID
where o.order_id is null
union 和 union all
union会自动去重，union all不会去重。相比来说如果不需要去重使用
union all效率更高
分页
select *
from (
select a.*, ROWNUM rn
from (
sql语句
) a
where ROWNUM < 6000
)
where rn >= 5000
列转行
select wm_concat('''' || route_id || '''')
from ROUTE_CATFLUSH_TEMP
where flag = 2;
行转列
单字段值以某个字符分割转列
SELECT REGEXP_SUBSTR('072106-19137,072109-2362', '[^,]+', 1, rownum)
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH('072106-19137,072109-2362') - LENGTH(REPLACE('072106-19137,072109-2362', ',', '')) + 1
072106-19137
072109-2362
over() 函数
row_number()
分组排序
row_number() over(partition by r.route_id order by r.UPD_DATE desc) rn
count()
分组计数
count(t.TEAM_ID) over (partition by r.route_id order by r.UPD_DATE desc) teamCount
case when
select
case 字段
when xx then xx
when yy then yy
else zz end as 字段别名
not in 和 not exist
select count(distinct r.ROUTE_ID)
from ROUTE_BASEINFO r
where r.PRODUCT_TYPE in ('0', '1')
and not exists(select 1 from ROUTE_CATEGORY c where r.ROUTE_ID = c.ROUTE_ID);
select count(distinct r.ROUTE_ID)
from ROUTE_BASEINFO r
where r.PRODUCT_TYPE in ('0', '1')
and r.ROUTE_ID not in (select c.ROUTE_ID from ROUTE_CATEGORY c);
in 和 exsit
1张订单多张票，查询出票状态含有状态2得订单信息
比如：
订单 212109-1244, 3张票，状态分别位[2,9,1] 则此订单符合要求
订单 212109-1245, 3张票，状态分别位[1,9,1] 则此订单不符合要求
select *
from TICKET_PRODUCT_ORDER_DETAIL d
where d.add_time > sysdate - 10
and exists(select 1 from TICKET_PRODUCT_ORDER_DETAIL c where d.HQYC_STATUS = c.HQYC_STATUS and c.HQYC_STATUS = '2')
会话、锁
-- 获取数据库中的会话列表
select s.username
, s.sid || ',' || s.SERIAL# as 会话标识
, s.sid
, s.serial#
, s.logon_time
, o.OBJECT_NAME
from v$locked_object l,
v$session s,
dba_objects o
where l.object_id = o.object_id
and l.session_id = s.sid
order by s.logon_time;
-- 查看正在运行的SQL
select osuser, username, sql_text
from v$session a,
v$sqltext b
where a.sql_address = b.address
and a.SID in (sid,SERIAL#)
order by a.SID;
-- 其中的 serial# 是我们需要用到的会话序列号，杀掉会话
alter system kill session 'sid,SERIAL#' immediate ;
-- 获取会话在linux中的进程号，可以使用kill -9 pid 杀掉会话进程
select spid as 进程号, osuser, s.program
from v$session s,
v$process p
where s.paddr = p.addr
and s.sid = 1897;
merge操作
merge into student a
using (select 12 as class_id
from dual) b
on a.class_id = b.class_id
when not matched then
insert (class_id, name)
values (?, ?)
when matched then
update
set name = ?
where class_id = ?
;
常用函数
decode
select sum(decode(字段, '1',1,'6',1,'7',1)) from route_baseinfo
建表
-- 序列创建
CREATE SEQUENCE SEQ_ROUTE_CATFLUSH_TEMP
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE;
-- 建表
create table ROUTE_CATFLUSH_TEMP
(
ID NUMBER NOT NULL constraint pk_route_catflush_temp primary key,
ROUTE_ID CHAR(11) NOT NULL,
FLAG NUMBER default 0 NOT NULL,
TIME TIMESTAMP(6)
);
-- 触发器
create trigger ROUTE_CATFLUSH_TEMP_TRI1
before
insert
on ROUTE_CATFLUSH_TEMP
for each row
when (new.id is null)
begin
select SEQ_ROUTE_CATFLUSH_TEMP.nextval into :new.id from dual;
end;
--添加，修改字段
alter table ROUTE_CATFLUSH_TEMP
add REMARK varchar2(255);
alter table ROUTE_CATFLUSH_TEMP
modify REMARK varchar2(1000);
-- 创建索引
create index INDEX_RCFLUSH_TMP01
on ROUTE_CATFLUSH_TEMP (ROUTE_ID, FLAG);
细节
空字符串和NULL
oracle数据库对空字符串和null代表同样的意义
name not null;
update set name = ‘’ where id = 1;
这个语句报错：不能插入NULL值到非空字段中
count()中的字段
count(1),count(),count(字段）。
count(字段）如何该字段存在空值（NULL或者空字符串？）则不会统计该条数据行
count(1),和count() 都会统计
PLSQL
分页处理
DECLARE
v_total number:=0;
v_start number:=1;
v_end number:= 0;
v_pagesize number := 500;
v_totalpage number :=0;
v_sindex number :=0;
v_eindex number :=0;
BEGIN
-- 将查询值赋值给变量（select xx into 变量）
select count(1) into v_end from ROUTE_CATFLUSH_TEMP_TEST;
v_total := v_end + v_start +1;
v_totalpage := (v_total + v_pagesize -1) / v_pagesize;
for v_currentpage in 0 .. v_totalpage-1 loop
v_sindex := v_start + (v_pagesize * v_currentpage);
if (v_sindex + v_pagesize > v_end ) then
v_eindex := v_end;
else
v_eindex := v_sindex + v_pagesize -1;
end if;
update ROUTE_CATFLUSH_TEMP_TEST set flag = 0 where id >= v_sindex and id<= v_eindex;
delete from ROUTE_CATEGORY where ROUTE_ID in (select route_id from ROUTE_CATFLUSH_TEMP_TEST where id >= v_sindex and id<= v_eindex);
delete from ROUTE_BACKUP_TEMP_TEST where ROUTE_ID in (select route_id from ROUTE_CATFLUSH_TEMP_TEST where id >= v_sindex and id<= v_eindex);
commit;
end loop;
END;
游标处理
游标逐条处理，效率不是很高，适合小批量数据
DECLARE
V_COUNTER NUMBER :=0;
-- 定义游标
CURSOR v_route_catflush IS
select
T.ROUTE_ID , T.ID
from ROUTE_CATFLUSH_TEMP_TEST T where T.id >0 and T.ID < 1002 order by id asc;
BEGIN
FOR row IN v_route_catflush LOOP
update ROUTE_CATFLUSH_TEMP_TEST set flag = 0 where id=row.id;
delete from ROUTE_BACKUP_TEMP_TEST where ROUTE_ID = row.ROUTE_ID;
delete from ROUTE_CATEGORY where ROUTE_ID = row.ROUTE_ID;
V_COUNTER := V_COUNTER + 1;
IF(V_COUNTER >= 250) THEN
commit;
V_COUNTER :=0;
end if;
end loop;
commit;
END;
闪回查询
select '现有', COMMISSION, COMMISSION_RATE, COMMISSION_RULE_FLG
from ORDER_COMMISSION_INFO t
where ORDER_ID = '212109-4872'
union all
select 'FLASH_BACK',
COMMISSION,
COMMISSION_RATE,
COMMISSION_RULE_FLG
from ORDER_COMMISSION_INFO AS OF TIMESTAMP to_timestamp('20210928 14:00:00', 'yyyymmdd hh24:mi:ss')
where ORDER_ID = '212109-4872';
慢SQL
select *
from (
select f.*,
rank() over (order by f."平均执行时间'MS'" desc) elapsed_rank
from (
select *
from (
SELECT S.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME / 1000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
1
ELSE
EXECUTIONS
END), 2) "平均执行时间'MS'",
S.EXECUTIONS "执行次数",
S.OPTIMIZER_COST "COST",
S.SORTS,
S.MODULE, --连接模式（JDBC THIN CLIENT：程序）
-- S.LOCKED_TOTAL,
S.PHYSICAL_READ_BYTES "物理读",
-- S.PHYSICAL_READ_REQUESTS "物理读请求",
S.PHYSICAL_WRITE_REQUESTS "物理写",
-- S.PHYSICAL_WRITE_BYTES "物理写请求",
S.ROWS_PROCESSED "返回行数",
S.DISK_READS "磁盘读",
S.DIRECT_WRITES "直接路径写",
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME,
s.LAST_LOAD_TIME,
s.PLAN_HASH_VALUE
FROM GV$SQLAREA S
WHERE 1 = 1
AND S.PARSING_SCHEMA_NAME = USER
-- AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD') --当天
AND S.LAST_LOAD_TIME > TO_DATE('2021-11-01 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
-- AND S.LAST_LOAD_TIME < TO_DATE('2021-11-01 16:45:00' , 'YYYY-MM-DD HH24:MI:SS') -- 可能会被冲掉，要适时的获取。
-- AND S.COMMAND_TYPE IN (2, 3, 5, 6, 189)
-- AND s.PLAN_HASH_VALUE != '4061475391' -- 排除部分SQL
) o
where o."平均执行时间'MS'" > 1000
-- and o."平均执行时间'MS'" < 20000
) f
)
where elapsed_rank <= 100;
一条SQL展示注释、数据、字段
根据这个语句生成SQL，执行：
select (select 'select ' || WM_CONCAT('''' || u.comments || '''') || ' from dual'
from USER_COL_COMMENTS u
where table_name = 'FK_FKDFP'
) || ' union all ' ||
(select 'select ' || WM_CONCAT('''' || u.COLUMN_NAME || '''') || ' from dual'
from USER_COL_COMMENTS u
where table_name = 'FK_FKDFP_FB')
|| ' union all ' ||
(select 'select ' || WM_CONCAT('to_char(' || u.COLUMN_NAME || ') ')
from USER_COL_COMMENTS u
where table_name = 'FK_FKDFP_FB')
|| ' from FK_FKDFP_FB where PK_FKDFP_B=' || '''' || 'xxx' || ''''
from dual;
查看外链并使用
select *from all_db_links;
select * from xxx@外链名称
995
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
