sirwsl的博客

开始使用

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

图例**：**

image-20220716171858229

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

图例**：**

image-20220717000157465

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

图例**：**

image-20220716155658074

2、分组连续求和赋值每列

场景**：根据某个字段分组根据排序依次赋值给每一列**

语法**：SUM(求和列) OVER(PARTITION BY 分组列 ORDER BY 排序 ASC) mm**

样例**：**

SELECT 
ID,
SUM(COLUMN3) OVER(PARTITION BY COLUMN1 ORDER BY ID ASC) mm
FROM DEMO

图例**：**

image-20220716155545811

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

图例**：**

image-20220716160529941

2、将数据按照某字段进行分组均分

场景**：根据某个字段进行分组之后进行跳跃排序**

语法**：NTILE(分区数量) OVER(ORDER BY 分区依据) mm**

样例**：**

SELECT 
ID,
NTILE(3) OVER(ORDER BY COLUMN3) mm
FROM DEMO

图例**：**

image-20220716162748464

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 分组条件

图例**：**

image-20220716232214147

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

图例**：**

image-20220716233106972

2、行转列

语句**：PIVOT**

语法**：PIVOT(聚合函数 for 需要转化的列 in (值)**

样例**：**

SELECT * FROM DEMO
PIVOT 
(
   SUM(COLUMN4) FOR 
   COLUMN1 IN ('t1','t2','t3')
)

图例**：**

image-20220716231908249

3、列转行

语句**：UNPIVOT ()**

语法**:select ... from ... UNPIVOT(列名,...)**

样例**：**

SELECT * FROM DEMO 
UNPIVOT (
	tmp FOR TEST IN (COLUMN1,COLUMN2)
)

图例**：**

image-20220716230741153

九、资源

img

img

这里写图片描述

这里写图片描述

这里写图片描述

**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

0 浏览
           
           
标签