Oracle 常用sql(报表常用sql)
数据库建模式、用户、表空间、表、数据语句在最末尾
一、数据类型转化
函数**:translate(字段 USING NCHAR_CS)**
样例**:**
SELECT translate(COLUMN1 USING NCHAR_CS) FROM DEMO WHERE 1=1
函数**:cast(字段 as nvarchar2(10)) **
样例**:**
SELECT cast(COLUMN1 as nvarchar2(10)) FROM DEMO WHERE 1=1
二、字符串操作
1、字符串拼接
1.1、|| 拼接
语法**:字段or字符串||字符串**
样例**:**
select 'a'||'b' from dual;
1.2、CONCAT()拼接
语法**:concat('a','b')**
样例**:**
select concat('a','b') from dual;
PS**:多个字符串需要嵌套concat**
1.3、字符串分组拼接,指定拼接符
语法**:listagg(连接字段,',').... group by 分组字段**
PS**:listagg分组时常会根据某个字段排序,保证拼接有效性**
样例**:**
SELECT
COLUMN1,
LISTAGG(COLUMN3 ,'-') WITHIN GROUP(ORDER BY ID)
FROM
DEMO
where 1=1
GROUP BY COLUMN1 ORDER BY COLUMN1 ASC
图例**:**
2、字符串截取
语法**:SUBSTR()**
样例**:**
SELECT SUBSTR('0123456789', 1) FROM dual --截取所有字符串 '0123456789'
SELECT SUBSTR('0123456789', 2) FROM dual --从第2个字符开始 '123456789'
SELECT SUBSTR('0123456789', -2) FROM dual --从倒数第2个字符 '89'
SELECT SUBSTR('0123456789', 2, 3) FROM dual --第2个字符开始,截取3个字符 '123'
SELECT SUBSTR('0123456789', -4, 3) FROM dual --倒数第4个字符开始,截取3个字符 '678'
3、字符串查找
语法**:INSTR()**
样例**:**
--表示从字符开始查找第1次出现的位置
SELECT INSTR('aabbccddeeffgghhiiggffeeddcc', 'ff') FROM dual --返回11
--表示从开始查找子字符串第1次出现的位置
SELECT INSTR('aabbccddeeffgghhiiggffeeddcc', 'ff', 5) FROM dual --返回11
--表示从第5个字符开始查找子字符串第1次出现的位置
SELECT INSTR('aabbccddeeffgghhiiggffeeddcc', 'ff', 5) FROM dual --返回11
--表示从第5个字符开始查找子字符串第1次出现的位置
SELECT INSTR(''aabbccddeeffgghhiiggffeeddcc'', 'ff', 5, 1) FROM dual --返回11
--表示从第3个字符开始查找子字符串第2次出现的位置
SELECT INSTR('aabbccddeeffgghhiiggffeeddcc', 'ff', 3, 2) FROM dual --返回21
4、字符串次数统计
函数**:REGEXP_COUNT(列,'统计字串')**
样例**:**
SELECT REGEXP_COUNT(COLUMN2,'a') FROM DEMO WHERE 1=1
5、正则替换
语法**:replace()**
样例**:**
select replace(regexp_replace('123null?·123123“\123','[(null)?*·*“*]+',''),'\','\\') from dual;
6、Oracle blob字段插入
DECLARE
V_LANG BLOB := to_blob('15486A1F2B22D....超长16进制字符串');
BEGIN
INSERT INTO ACT_GE_BYTEARRAY
(ID_, REV_, NAME_, DEPLOYMENT_ID_, BYTES_, GENERATED_)
VALUES('1', 1, 'xxxx', '10001', V_LANG, 1);
COMMIT;
END;
三、日期操作
1、获取年、月、季、时、分、秒
语法**:to_char(字段, 'yyyy-MM-dd HH🇲🇲ss')**
样例**:**
SELECT to_char(SYSDATE, 'yyyy') 年,
to_char(SYSDATE, 'q') 季,
to_char(SYSDATE, 'MM') 月,
to_char(SYSDATE, 'WW') 周每年,
to_char(SYSDATE, 'dy') 周,
to_char(SYSDATE, 'dd') 日,
to_char(SYSDATE, 'DD') 日每年,
to_char(SYSDATE, 'HH') 时,
to_char(SYSDATE, 'mm') 分,
to_char(SYSDATE, 'ss') 秒,
to_char(SYSDATE, 'SSSS') 豪秒
FROM dual
图例**:**
2、字符串转日期
语法**:to_date()**
样例**:**
to_date('2022-07-17','yyyy-mm-dd')
四、循环条件
语法:for i in 列 loop...end loop;** **
样例:
SELECT * FROM DEMO;
BEGIN
for row_test in (SELECT * FROM DEMO) loop
sys.dbms_output.put_line(DEMO.ID);
end loop;
END;
五、分支条件
1、CASE WHEN...THEN...语句
语法**:CASE WHEN 条件 THEN 结果**
或者**:CASE 字段 WHEN 条件 THEN 结果**
样例**:**
SELECT
ID,
(case
WHEN COLUMN1='t1' THEN '测试11'
WHEN COLUMN1='t2' THEN '测试22'
WHEN COLUMN1='t3' THEN '测试33'
ELSE '其他' END) detail
FROM
DEMO d
where 1=1
/**or**/
(CASE COLUMN1
WHEN 't1' THEN '测试11'
WHEN 't2' THEN '测试22'
WHEN 't3' THEN '测试33'
ELSE '其他' END) detail
2、decode ()语句
语法**:decode (条件, 值1, 返回值1, 值2, 返回值2,…值n,返回值n,缺省值)**
样例**:**
SELECT
ID,
decode(COLUMN2,'a1','1111', COLUMN2,'a2','2222') mm
FROM
DEMO
where 1=1
六、统计函数
1、分组求和赋值每列
场景**:根据某个字段分组后将求和后值依次给每一列**
语法**:SUM(求和列) OVER(PARTITION BY 分组列) mm**
样例**:**
SELECT
ID,
SUM(COLUMN3) OVER(PARTITION BY COLUMN1) mm
FROM DEMO
图例**:**
2、分组连续求和赋值每列
场景**:根据某个字段分组根据排序依次赋值给每一列**
语法**:SUM(求和列) OVER(PARTITION BY 分组列 ORDER BY 排序 ASC) mm**
样例**:**
SELECT
ID,
SUM(COLUMN3) OVER(PARTITION BY COLUMN1 ORDER BY ID ASC) mm
FROM DEMO
图例**:**
2、补充函数
函数 | 说明 |
---|---|
SUM(...) | 求和函数 |
COUNT(...) | 统计函数 |
avg() | 求平均 |
max() | 求最大值 |
min() | 求最小值 |
variance() | 方差 |
七、排序函数
1、分组跳跃排序
场景**:根据某个字段进行分组之后进行跳跃排序**
语法**:rank() OVER(PARTITION BY 分组条件,... ORDER BY 排序条件) mm**
样例**:**
SELECT
ID,
rank() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2) mm
FROM DEMO
图例**:**
2、将数据按照某字段进行分组均分
场景**:根据某个字段进行分组之后进行跳跃排序**
语法**:NTILE(分区数量) OVER(ORDER BY 分区依据) mm**
样例**:**
SELECT
ID,
NTILE(3) OVER(ORDER BY COLUMN3) mm
FROM DEMO
图例**:**
3、补充函数
函数 | 说明 |
---|---|
row_number() OVER(...) | 依次排序 |
rank() OVER(...) | 跳跃排序(相同,会占位) |
dense_rank() OVER(...) | 连续排序 |
NTILE(...) OVER(...) | 均分分组排序 |
PS** :**
函数**:CUBE()**
说明**:等于对全表进行group by**
用法**: GROUP BY CUBE (...)**
八、行列操作
1、分组拼接
语句**:listagg() WITHIN GROUP () **
语法**:listagg(合并字段,拼接符号) WITHIN GROUP (order by 依靠什么字段合并)**
样例**:**
select xxx,
listagg(B,',') within group (order by A) Bstr
from table
where 1=1
group by 分组条件
图例**:**
2、一行拆分为多行
语句**:REGEXP_SUBSTR()**
语法**:distinct REGEXP_SUBSTR(拆分字段,正则,开始,结束).......connect by .....**
样例**:**
SELECT distinct REGEXP_SUBSTR('A,B,C,D','[^,]+',1, level) test,t.*
FROM DEMO t
connect by level <= regexp_count('A,B,C,D', ',') + 1
图例**:**
2、行转列
语句**:PIVOT**
语法**:PIVOT(聚合函数 for 需要转化的列 in (值)**
样例**:**
SELECT * FROM DEMO
PIVOT
(
SUM(COLUMN4) FOR
COLUMN1 IN ('t1','t2','t3')
)
图例**:**
3、列转行
语句**:UNPIVOT ()**
语法**:select ... from ... UNPIVOT(列名,...)**
样例**:**
SELECT * FROM DEMO
UNPIVOT (
tmp FOR TEST IN (COLUMN1,COLUMN2)
)
图例**:**
九、资源
**PS:**上图来源于博客Oracle常用函数总结_Seven_0507的博客
开源协议:** CC 4.0 BY-SA **版权协议
连接:https://blog.csdn.net/KEEP_GIONG/article/details/80089703
零、建表sql
进行学习之前请准备好oracle,创建好对用户、模式、应表进行学习
建立用户、模式、表空间语句
-- 移除表空间
DROP TABLESPACE DEMO INCLUDING CONTENTS AND DATAFILES
-- 移除用户
drop user DEMO CASCADE
-- 创建表空间
CREATE TABLESPACE DEMO
DATAFILE 'D:/DEMO.dbf'
SIZE 1G AUTOEXTEND ON next 1G
-- 实际应用
create user DEMO identified by 123456
default tablespace DEMO
quota unlimited on DEMO
-- 权限设设置
grant create session to DEMO;
grant create table to DEMO;
grant create tablespace to DEMO;
-- 需要登陆之后才能使用
-- 查看当前用户的权限
select * from role_sys_privs;
建立表、数据
-- DEMO.DEMO definition
CREATE TABLE "DEMO"
( "ID" NUMBER(38,0),
"COLUMN1" VARCHAR2(100),
"COLUMN2" VARCHAR2(100),
"COLUMN3" NUMBER(38,0),
"COLUMN4" VARCHAR2(100)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO" ;
CREATE INDEX "DEMO_ID_IDX" ON "DEMO" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO" ;
CREATE INDEX "DEMO_COLUMN1_IDX" ON "DEMO" ("COLUMN1")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO" ;
CREATE INDEX "DEMO_COLUMN3_IDX" ON "DEMO" ("COLUMN3")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO" ;
数据**:**
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(1, 't1', 'a1', 10, '1');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(2, 't1', 'a1', 20, '2');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(3, 't1', 'a2', 30, '3');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(4, 't1', 'a2', 40, '4');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(5, 't1', 'a3', 50, '5');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(6, 't2', 'a3', 60, '1');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(7, 't2', 'a1', 70, '2');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(8, 't2', 'a1', 80, '3');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(9, 't2', 'a2', 90, '4');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(10, 't2', 'a2', 100, '5');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(11, 't3', 'a3', 110, '1');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(12, 't3', 'a3', 120, '2');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(13, 't3', 'a4', 130, '3');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(15, 't3', 'a5', 140, '5');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(16, 't3', 'a5', 150, '6');
标题:Oracle 常用sql(报表常用sql)
作者:sirwsl
地址:https://www.wslhome.top/articles/2022/07/17/1658041102060.html