greatest 和 least 多列最大最小值

1. LEAST 返回值列表中最小值，格式： LEAST(*value1, value2, value3, …*)

含义：返回value列表最小的值。

value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

当value值列表中有一个为NULL，则返回NULL值。

2. GREATEST 返回值列表中最大值，格式： GREATEST(*value1, value2, value3, …*)

含义：返回value列表最大的值。

value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

当value值列表中有一个为NULL，则返回NULL值。

EXAMPLE:

select greatest (1, 3, 2 ) from dual ; — 返回3

select greatest ( 'A’, 'B’, 'C’ ) from dual ; — 返回C

select greatest (null, 'B’, 'C’ ) from dual ; — 返回null

wm_concat函数

在日常的数据查询过程中，经常遇到一条信息分多条记录存储，并以同一个ID关联的情况，比如常见的房产证权利人信息，因为共有权人可能有很多，不可能把所有的权利人都放到权利人表的权利人字段，把所有权利人的证件号都放到权利人证件号字段，所以在数据库设计时候，会采用一个权利人一条记录，并以权利ID关联的方式存放。

但是在数据查询时候，有时候又希望将所有权利人信息一起展示，这里可能就会用到Oracle的wm_concat函数示例样例：

Select circuit_num, w.a_city_name
  From tablename_info w
  where w.circuit_num='南小区NE001NP';

NUM             A_CITY_NAME
南小区NE001NP  **市
南小区NE001NP  **市

通用版本语句,如果拼接的字符串过长，可以将to_char函数去掉，默认为BLOG类型。

Select circuit_num, to_char(wm_concat(w.a_city_name))
  From tablename_info w
  where w.circuit_num='南小区NE001NP'
 group by w.circuit_num
having count(a_city_name) > 1;

CIRCUIT_NUM             TO_CHAR(WM_CONCAT(W.A_CITY_NAME))
南小区NE001NP **市,**市

LISTAGG函数

Oracle LISTAGG() 功能同WM_CONCAT()函数

聚合查询用法 GROUP BY 和 PARTITION BY 的使用和比较基本语法：LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)。可以配合 GROUP BY 进行聚合查询，也可以配合 PARTITION BY 进行聚合查询。

配合 GROUP BY 查询语句：

SELECT u.ID,
       u.NAME,
       LISTAGG(r.VALUE, ',') WITHIN GROUP(ORDER BY r.VALUE) AS AGG_VALUES
  FROM TEST_USER u
  LEFT OUTER JOIN TEST_RECORD r
    ON u.ID = r.ID
 WHERE r.TAG IN ('start', 'end')
 GROUP BY u.ID, u.NAME;

配合 PARTITION BY 查询语句：

SELECT DISTINCT u.ID,
                u.NAME,
                LISTAGG(r.VALUE, ',') WITHIN GROUP(ORDER BY r.VALUE) OVER(PARTITION BY U.ID) AS AGG_VALUES
  FROM TEST_USER u
  LEFT OUTER JOIN TEST_RECORD r
    ON u.ID = r.ID
 WHERE r.TAG IN ('start', 'end');

INSERT ALL 多表多行插入

INSERT ALL | FIRST 函数，多表多行插入。

无条件 Insert all

语法:

INSERT [ALL] [condition_insert_clause]
[insert_into_clause values_clause] (subquery)

示例:

INSERT ALL
INTO sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal)
INTO mgr_history(emp_id,manager_id,salary) values (empid,hiredate,sal)
SELECT employee_id empid,hire_date hiredate,salary sal,manager_id mgr
FROM employees
WHERE employee_id>200;

有条件的Insert 语法:

INSERT [ALL | FIRST]
WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
......
[ELSE] [insert_into_clause values_clause] 
Subquery；

示例:

Insert All
when id>5 then into z_test1(id, name) values(id,name)
when id<>2 then into z_test2(id) values(id)
else into z_test3 values(name)
select id,name from z_test;

当使用ALL关键字时,oracle会从上至下判断每一个条件,当条件满足时就执行后面的into语句在上面的例子中,如果ID=6 那么将会在z_test1中插入一条记录,同时也在z_test2中插入一条记录

备注:
  当使用FIRST关键字时,oracle会从上至下判断每一个条件,当遇到第一个满足时就执行后面的into语句,
  同时中断判断的条件判断,在上面的例子中,如果ID=6,仅仅会在z_test1中插入一条数据

MERGE 存在则更新，不存在则新增

语法如下：

MERGE INTO table_name alias1
USING (table | view | sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
  UPDATE table_name SET col1 = col_val1, col2 = col2_val
WHEN NOT MATCHED THEN
  INSERT (column_list) VALUES (column_values);

用中文来解释Merge语法，就是：

在alias2中Select出来的数据，每一条都跟alias1进行 ON (join condition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作(Insert)。

因此，严格意义上讲，”在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中alias2的记录数。

备注：

1、insert 和update是可选的 
2、UPDATE 和INSERT 后面可以跟WHERE 子句 
3、在ON条件中可以使用常量来insert 所有的行到目标表中，不需要连接到源表和目标表 
4、UPDATE 子句后面可以跟delete 来去除一些不需要的行。

1、可省略的update 或者insert

 MERGE INTO products p
   USING newproducts np
   ON (p.product_id = np.product_id)
   WHEN MATCHED THEN
     UPDATE SET p.product_name = np.product_name, p.category = np.category;

2、当条件不满足的时候INSERT

MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN NOT MATCHED THEN
  INSERT VALUES (np.product_id, np.product_name, np.category);

3、带条件的insert 和update

MERGE INTO products p   
 USING newproducts np   
    ON (p.product_id = np.product_id)   
    WHEN MATCHED THEN  
    UPDATE  
     SET p.product_name = np.product_name,   
     p.category = np.category   
    WHERE p.category = 'DVD'  
    WHEN NOT MATCHED THEN  
     INSERT  
     VALUES (np.product_id, np.product_name, np.category)   
    WHERE np.category != 'BOOKS'

4、无条件的insert

MERGE INTO products p   
 USING newproducts np   
  ON (1=0)   
  WHEN NOT MATCHED THEN  
   INSERT  
   VALUES (np.product_id, np.product_name, np.category)   
   WHERE np.category = 'BOOKS'

5、delete 子句

 merge into products p
    using newproducts np
    on(p.product_id = np.product_id)
    when matched then
    update
    set p.product_name = np.product_name
    delete where category = 'macle1_cate';

Instr函数

在oracle中没有indexof()函数语法：instr(字符串，StrTarget,左右方向，StrTarget出现第几次)

方法具体用法： select instr('保定市南市区','市',1,1)-1 from dual; 解释：

1. '保定市南市区' =>可以是表达式，也可以是具体数据 
2. '市'=>为分离的标志，这里为两组数据中的“市”这个字 
3. 第一个1为从左边开始，如果为-1，则从右边开始。       
4. 第二个1为“市”出现的第几次。

在GBK编码下，一个中文占两个字节 select instr('保定市南市区','市',1,1) from dual;的值就是5 select instr('保定市南市区','市',1,2) from dual;的值就是9 select instr('保定市南市区','市',-1,1) from dual;的值就是9 select instr('保定市南市区','市',-1,2) from dual;的值就是5

Instr(StrSource,StrTarget)instr函数也有三种情况：① instr(字段,’关键字’)>0相当于字段like '%关键字%’② instr(字段,’关键字’)=1相当于字段like '关键字%’③ instr(字段,’关键字’)=0相当于字段not like '%关键字%’数据量比较小的时候，使用like，数据量较大情况下，使用instr，数据量更大情况下，使用Oracle的instr函数与索引配合提高模糊查询的效率。

substr 字符串截取函数

语法：substr(字符串,截取开始位置,截取长度) //返回截取的字

示例语句：

substr('Hello World',0,1) //返回结果为 'H'  *从字符串第一个字符开始截取长度为1的字符串
substr('Hello World',1,1) //返回结果为 'H'  *0和1都是表示截取的开始位置为第一个字符
substr('Hello World',2,4) //返回结果为 'ello'
substr('Hello World',-3,3)//返回结果为 'rld' *负数(-i)表示截取的开始位置为字符串右端向左数第i个字符

常配合instr使用：

Select substr('1.jrchange.1286632',instr('1.jrchange.1286632','.','1','2')+1) From dual;

replace替换函数

语法：REPLACE(char,search_string[,replacement_string]) 解释：replace中，每个search_string都被replacement_string所代替

select replace('acdd','cd','ef') from dual; --> aefd

如果replacement_string为空或为null，那么所有的search_string都被移除

select replace('acdd','cd','') from dual; --> ad

如果search_string 为null，那么就返回原来的char

 select replace('acdd','ef') from dual; -->acdd
 select replace('acdd','','') from dual; -->acdd(也是两者都为空的情况)

translate 替换函数

\textcolor{red}{与replace类似是替换函数}，但translate是一次替换多个单个的字符。

  语法：TRANSLATE('char','from_string','to_string')
  解释：translate中，每个from_string中的字符被to_string替换

基本用法，字符对应替换。例子：select translate('1234567','123' ,'abc') from dual ;--1替换为a,2替换为b,3替换为c结果：abc4567 。

如果没有对应字符则替换为null;select translate('1234567','123' ,'ab') from dual；--3替换为null;结果：ab4567.

如果对应字符过多，不影响select translate('1234567','123' ,'abccd') from dual；结果：abc4567

如果替换字符整个为空字符，则直接返回nullselect translate('1234567','123' ,'') from dual；结果：null;

如果想筛掉对应字符，应传入一个不相关字符，同时替换字符也加一个相同字符；select translate('1234567','#123' ,'#') from dual;结果：4567；

如果相同字符对应多个字符，按第一个；select translate('12334567','1233' ,'abcd') from dual;结果：abcc4567;

如果想保留某些特定字符筛选掉其他的，比如筛掉汉字保留数字先把数字筛选掉，select translate('你师看了3三楼2的6开8发','#0123456789' ,'#') from dual;再用筛选出的汉字去筛选原来的语句留下数字，select translate('你师看了3三楼2的6开8发','#'||translate('你师看了3三楼2的6开8发','#0123456789' ,'#'),'#') from dual；结果：3268；

rename重命名表

语法：RENAME table_name TO new_name;
实例：RENAME promotions TO campaigns;

在RENAME表语句中：

首先，指定将要重命名的表名称。
其次，指定新的表名。新名称不能与同一模式中的另一个表相同。

请注意，一旦执行了RENAME语句，就不能回滚了。

当重命名表时，Oracle自动将旧表上的索引，约束和授权转移到新表上。另外，它使依赖重命名表(原表)的所有对象失效，如视图，存储过程，函数和同义词。

rebuild重建索引

一：考虑重建索引的场合1：表上频繁发生update,delete操作2：表上发生了alter table ..move操作（move操作导致了rowid变化）

二：判断重建索引的标准索引重建是否有必要，一般看索引是否倾斜的严重，是否浪费了空间；那应该如何才可以判断索引是否倾斜的严重，是否浪费了空间，如下：1 对索引进行结构分析Analyze index indexname validate structure;2, 在执行步骤1的session中查询index_stats表，不要到别的session去查询 select height,DEL_LF_ROWS/LF_ROWS from index_stats;3, 在步骤2查询出来的height>=4或者DEL_LF_ROWS/LF_ROWS>0.2的场合，该索引考虑重建；Example:

SQL> select count(*) from test_index;
  COUNT(*)
  2072327
SQL> analyze index pk_t_test validate structure;
Index analyzed
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
  HEIGHT         DEL_LF_ROWS/LF_ROWS
     3                   0
SQL> delete from test_index where rownum<250000;
249999 rows deleted
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
  HEIGHT                 DEL_LF_ROWS/LF_ROWS
     3                   0
SQL> analyze index pk_t_test validate structure;
Index analyzed
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
   HEIGHT                  DEL_LF_ROWS/LF_ROWS
     3                  0.0777430939338362

三：重建索引的方式 1：drop 原来的索引，然后再创建索引; 2：alter index indexname rebuild (online); 方式一：耗时间，无法在247环境中实现方式二：比较快，可以在247环境中实现建议使用方式二四：alter index rebuid内部过程和注意点1：alter index rebuild 和alter index rebuild online的区别(1) 扫描方式不同Rebuild以index fast full scan（or table full scan）方式读取原索引中的数据来构建一个新的索引，有排序的操作; rebuild online 执行表扫描获取数据，有排序的操作;Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost)

(2) rebuild 会阻塞dml操作，rebuil online 不会阻塞dml操作；

(3) rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。

注意点：1，执行rebuild操作时，需要检查表空间是否足够；2，虽然说rebuild online操作允许dml操作，但是还是建议在业务不繁忙时间段进行；3，Rebuild操作会产生大量redo log ；

五：重建分区表上的分区索引重建分区索引方法： Alter index indexname rebuild partition paritionname tablespace tablespacename; Alter index indexname rebuild subpartition partitioname tablespace tablespacename; Partition name 可以从user_ind_partitions查找 Tablepace 参数允许alter index操作更改索引的存储空间；

六：索引状态描述 select status,T.* from user_indexes T where table_name='表名'在数据字典中查看索引状态，发现有三种：valid:当前索引有效N/A :分区索引有效unusable:索引失效

move降低高水位线

语法：

MOVE [ONLINE] 
[segment_attributes_clause] 
[data_segment_compression]
[index_org_table_clause]
[ { LOB_storage_clause | varray_col_properties }
    [ { LOB_storage_clause | varray_col_properties } ]...
]
[parallel_clause]

a. 我们可以使用move将一个table从当前的tablespace上移动到另一个tablespace上，如：

alter table t move tablespace tablespace_name;

b. 我们还可以用move来改变table已有的block的存储参数,如：

alter table t move storage (initial 30k next 50k);

c.另外，move操作也可以用来解决table中的行迁移的问题。

使用move的一些注意事项:

a. table上的index需要rebuild：

在前面我们讨论过，move操作后，数据的rowid发生了改变，我们知道，index是通过rowid来fetch数据行的，所以，table上的index是必须要rebuild的。SQL> create index i_my_objects on my_objects (object_id);

Index created

SQL> alter table my_objects move;

Table altered

SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';

INDEX_NAME STATUS

I_MY_OBJECTS UNUSABLE

从这里可以看到，当table MY_OBJECTS进行move操作后，该table 上的inedx的状态为UNUSABLE，这时，我们可以使用alter index I_MY_OBJECTS rebuild online的命令，对index I_MY_OBJECTS进行在线rebuild。

b. move时对table的锁定

当我们对tabe MY_OBJECTS进行move操作时，查询v$locked_objects视图可以发现，table MY_OBJECTS上加了exclusive lock锁。

SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;

OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE

32471 9 DLINGER 6

SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';

OBJECT_ID

----------

32471

这就意味着，table在进行move操作时，我们只能对它进行select的操作。反过来说，当我们的一个session对table进行DML操作且没有commit时，在另一个session中是不能对这个table进行move操作的，否则oracle会返回这样的错误信息：ORA-00054: 资源正忙，要求指定 NOWAIT。

c. 关于move时空间使用的问题：

当我们使用alter table move来降低table的HWM时，有一点是需要注意的，这时，当前的tablespace中需要有1倍于table的空闲空间以供使用：

SQL> CREATE TABLESPACE TEST1

2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\TEST1.dbf' SIZE 5M

3 UNIFORM SIZE 128K ;

SQL> create table my_objects tablespace test1 as select * from all_objects;

表已创建。

SQL> select bytes/1024/1024 from user_segments where segment_name='MY_OBJECTS';

BYTES/1024/1024

---------------

3.125

SQL> alter table MY_OBJECTS move;

alter table MY_OBJECTS move

ERROR 位于第 1 行:

ORA-01652: 无法通过16（在表空间TEST1中）扩展 temp 段

SQL> ALTER DATABASE

2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\TEST1.DBF' RESIZE 7M;

数据库已更改。

SQL> alter table MY_OBJECTS move;

表已更改。

shrink降低高水位线（建议）

Shrink space的常用命令如下：

普通表
alter table tablename enable row movement;--打开行移动
alter table tablename shrink space cascade;--压缩表及相关数据段，并下调HWM
alter table tablename shrink sapce compact;--只压缩，不下调HWM
alter table tablename shrink sapce ;--下调HWM
alter table tablename disable row movement;--关闭行移动

分区表
alter table TA_HISTORYALARM_2021_10 enable row movement;
alter table TA_HISTORYALARM_2021_10 MODIFY PARTITION PARTITION_14 shrink space;
alter table TA_HISTORYALARM_2021_10 disable row movement;

子分区
-- 开启行迁移
alter table TA_HISTORYALARM_2021_10 enable row movement;
-- 进行子分区空间的收缩
alter table TA_HISTORYALARM_2021_10 MODIFY SUBPARTITION PTABLE1_SUB1 shrink space;
-- 关闭行迁移
alter table TA_HISTORYALARM_2021_10 enable row movement;

针对delete操作引起的空间不释放现象，或者，更正式一点的说法，如何降低高水位线，方法有很多种，如，shrink space；move tablespace；create table xxx as select * from xxx 重建表等。使用这些方法前，我们的原则是：如果可以truncate，直接truncate，该操作会重置高水位线，BLOCKS会被置为0，NUM_ROWS置为0；否则，优先使用shrink space，该方法不需要重建索引。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

我们使用shrink space降低高水位线，释放空间，其中，使用shrink space命令前，需要先alter table test enable row movement;开启行移动，再次对表进行分析、查询，结果如下：

--生产库LOCAL本地管理，查看表空间管理方式
SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT FROM DBA_TABLESPACES;
--打开行移动
alter table test enable row movement;
--下调HWM
alter table test shrink space;
--关闭行移动
alter table test disable row movement;

ANALYZE TABLE TA_HISTORYALARM_2021_12 ESTIMATE STATISTICS;
--查询表占用的空间：
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 'TA_HISTORYALARM_2021_12';

move和shrink的总结

一、move

move过程中需要额外的表空间，需要的大小大约等于当前表中数据量的大小，move结束后立即释放该额外空间。
move过程中对表加排它锁，会影响其他session的DML操作。
move操作并不会维护索引，因此move完毕后需要对索引rebuild。
move操作会降低HWM，但是并不会释放HWM以上的空块，也就是说，move只会对HWM以下的块进行操作。
move操作的一些相关测试数据：以2000000数据（233M）为例，删除800000条数据，执行move操作。大概用时4秒，共产生了319K的redo，56K的undo。表由233M缩小至145M。
move操作可以完全消除行迁移。
move操作后，为表分配的数据段位置发生了改变，即段头块的位置发生了改变。

二、shrink

shrink过程中并没有用到额外的表空间。
shrink操作其实可以分为两步：

第一步：对数据进行重组，即只会整理碎片，不会降低高水位，也就是说不会释放空间。通过一系列的delete/insert组合来完成，具体的语法是 alter table t1 shrink space compact。该过程会在表上加共享锁，在移动的行中加排它锁。并且会维护索引。第二步：降低HWM，回收空间，与move不同的是，shrink可以回收HWM以上的块。该过程会在表上加排它锁，因此业务繁忙时并不适合执行该降低HWM的操作。 3.shrink操作会维护索引，但是不会对索引进行碎片整理。如果加入cascade选项，那么维护索引的同时会对索引进行碎片整理。 4.shrink操作的一些相关测试数据：以2000000数据（233M）为例，删除800000条数据，分两步执行shrink操作。数据重组大概用时1分钟58秒，共产生了895M的redo，353M的undo。回收HWM阶段仅用1秒，产生了4K的redo，1K的undo。表由233M缩小至226M。 5.shrink操作不能完全消除行迁移。 6.shrink操作后，为表分配的数据段位置并没有发生变化，即段头块的位置没有改变。

to_char 函数FM的作用

0表示：如果参数(double或者float类型)存在数字就显示数字，不存在数字就显示09表示：如果参数(double或者float类型)存在数字就显示数字，不存在数字就显示空格FM表示：将9带来的空格删除

-- 不带FM的格式-- 不带FM的返回数据第一位永远是空格，且不占指定格式的位数-- 0：无非0数字则用0补充，超过指定格式长度则返回#

SELECT to_char(0, '00000.000') FROM dual;       -- 空格+00000.000
SELECT to_char(10, '00000.000') FROM dual;      -- 空格+00010.000 
SELECT to_char(010.10, '00000.000') FROM dual;  -- 空格+00010.100
SELECT to_char(111010.10, '00000.000') FROM dual; -- ##########

-- 9：非零数字前的0用空格代替，不带FM的仅小数点前的起作用

SELECT to_char(0.10, '99999.999') FROM dual;    -- 空格+五个空格+.100
SELECT to_char(0, '99999.999') FROM dual;      -- 空格+五个空格+.000
SELECT to_char(10, '99999.999') FROM dual;    -- 空格+三个空格+10.000
SELECT to_char(010.10, '99999.999') FROM dual;  -- 空格+三个空格+10.100

-- 带FM的格式-- FM：移除因9带来的空格和0-- fm00000.000和00000.000的效果差不多，只是没有空格

SELECT to_char(10, 'fm00000.000') FROM dual;     -- 00010.000
SELECT to_char(010.10, 'fm00000.000') FROM dual; -- 00010.100

-- 9先把前后0转换成空格，FM再移除空格

SELECT to_char(0.10, 'fm99999.999') FROM dual;   -- .1
SELECT to_char(0, 'fm99999.999') FROM dual;     -- 0.
SELECT to_char(10, 'fm99999.999') FROM dual;     -- 10.
SELECT to_char(010.10, 'fm99999.999') FROM dual; -- 10.1

unpivot | pivot行列转换函数

列转行（unpivot）

With t as (
Select 1 id,'桃子' name,100 q1,200 q2,300 q3,400 q4 from dual
Union
Select 2 id,'苹果' name,111 q1,222 q2,333 q3, 444 q4 from dual
Union
Select 3 id,'西瓜' name,123 q1,234 q2,345 q3,456 q4 from dual
)
Select * from t unpivot(nums for jidu in (q1,q2,q3,q4)) order by id;

    ID  NAME    JIDU    NUMS
1   1   桃子  Q1  100
2   1   桃子  Q2  200
3   1   桃子  Q3  300
4   1   桃子  Q4  400
5   2   苹果  Q1  111
6   2   苹果  Q2  222
7   2   苹果  Q4  444
8   2   苹果  Q3  333
9   3   西瓜  Q1  123
10  3   西瓜  Q3  345
11  3   西瓜  Q2  234
12  3   西瓜  Q4  456

行转列（pivot）

With t as (
Select 1 id,'桃子' name,100 q1,200 q2,300 q3,400 q4 from dual
Union
Select 2 id,'苹果' name,111 q1,222 q2,333 q3, 444 q4 from dual
Union
Select 3 id,'西瓜' name,123 q1,234 q2,345 q3,456 q4 from dual
),
bb as(
Select * from t unpivot(nums for jidu in (q1,q2,q3,q4)) order by id
)

Select * from bb pivot(max(nums) for jidu in ('Q1' Q1,'Q2' Q2,'Q3' Q3,'Q4' Q4)) --这里的max是如果有多行，选择最大，比如桃子有两个Q1

    ID  NAME    Q1  Q2  Q3  Q4
1   1   桃子  100 200 300 400
2   2   苹果  111 222 333 444
3   3   西瓜  123 234 345 456

分析函数

常用的分析函数如下所列：

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
ratio_to_report() over(partition by ... order by ...)
nulls first/nulls last

1、rank()/dense_rank over(partition by ... order by ...)

说明： over()在什么条件之上; 　　partition by 按哪个字段划分组；　　order by 按哪个字段排序；注意：　　（1）使用rank()/dense_rank() 时，必须要带order by否则非法　　（2）rank()/dense_rank()分级的区别：　　　　rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。　　　　dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。

2、lead()/lag() over(partition by ... order by ...) 取前面/后面第n行记录

说明：lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。lag(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

3、ratio_to_report() over(partition by ... ）

ratio_to_report主要完成对百分比的计算，语法为ratio_to_report(exp) over()也就是根据over窗口函数的作用区间，求出作用区间中的单个值在整个区间的总值的比重比如要求scott用户下emp表中每个员工的工资占本部门的比重select ename,sal,deptno,ratio_to_report(sal) over(partition by deptno) ratio from emp;需要注意的是：exp表达式不能进行ratio_to_report函数的嵌套，但是可以使用其他的函数；当sal的值乘以一个常数值的时候并不影响最后的计算结果比如select ename,sal,deptno,ratio_to_report(sal100) over(partition by deptno) ratio from emp;的计算结果和第一个sql的计算结果是一样的如果想以％的形式来表示的话，可以通过下面的语句来实现select ename,sal,deptno,to_char(round(ratio100,2))||'%' baifenbi from (select ename,sal,deptno,ratio_to_report(sal) over(partition by deptno) ratio from emp);

常用的方法

大批量数据删除方法

create or replace procedure clear_historyalarm AS
  TYPE alarmid_type IS TABLE OF VARCHAR2(255);
  start_time NUMBER;
  alarmid alarmid_type;
  CURSOR del_alarmid_data IS
    select a.alarmuniqueid
      from TA_HISTORYALARM_2022_03 a
     where not exists (select 1
              from TA_HISALARM_CUST_2022_03 b
             where a.alarmuniqueid = b.alarmuniqueid);
i integer;
BEGIN
  OPEN del_alarmid_data;
  LOOP
    FETCH del_alarmid_data BULK COLLECT
      INTO alarmid LIMIT 5000;
    EXIT WHEN alarmid.count = 0;
    --DBMS_OUTPUT.put_line(alarmid.count || ' rows');
   -- start_time := DBMS_UTILITY.get_time;
    FORAll i IN alarmid.first .. alarmid.last
      DELETE FROM TA_HISTORYALARM_2022_03 WHERE alarmuniqueid = alarmid(i);
    COMMIT;
    --dbms_lock.sleep(3);
   -- DBMS_OUTPUT.put_line('Delete  : ' || (DBMS_UTILITY.get_time - start_time)/100);
  END LOOP;
  CLOSE del_alarmid_data;

--回收分区表空间，循环执行
  execute immediate 'alter table TA_HISTORYALARM_2022_03 enable row movement';
  for i in 1 .. 31 loop
    execute immediate 'alter table TA_HISTORYALARM_2022_03 MODIFY PARTITION PARTITION_'|| i ||' shrink space';
  end loop;
  execute immediate 'alter table TA_HISTORYALARM_2022_03 disable row movement';

END;

将IP地址掩码转换成起始IP 终止IP的函数

CREATE OR REPLACE FUNCTION prefix2segment (ip_address VARCHAR2 ,prefix VARCHAR2) RETURN VARCHAR2 is

/**

名称 prefix2segment

输入 58.207.64.10  18

输出 58.207.64.1  58.207.127.255

测试 select prefix2segment('58.207.64.10',18) from dual;

BITAND(x,y)

BITOR(x,y) = (x + y) - BITAND(x, y);

BITXOR(x,y) = BITOR(x,y) - BITAND(x,y) = (x + y) - BITAND(x, y) * 2;

start_ip

end_ip

**/

v_return  VARCHAR2(4000);

v_comma1  INTEGER;

v_comma2  INTEGER;

v_comma3  INTEGER;

v_length  INTEGER;

v_ipaddr1 number(3);

v_ipaddr2 number(3);

v_ipaddr3 number(3);

v_ipaddr4 number(3);

v_prefix_tmp    VARCHAR2(32);

v_prefix_tmp1    VARCHAR2(32);

v_prefix_tmp2    VARCHAR2(32);

v_prefix_b1 VARCHAR2(18);

v_prefix_b2 VARCHAR2(18);

v_prefix_b3 VARCHAR2(18);

v_prefix_b4 VARCHAR2(18);

v_prefix1 number(3);

v_prefix2 number(3);

v_prefix3 number(3);

v_prefix4 number(3);

v_netmask1 number(3);

v_netmask2 number(3);

v_netmask3 number(3);

v_netmask4 number(3);

v_broadcast1 number(3);

v_broadcast2 number(3);

v_broadcast3 number(3);

v_broadcast4 number(3);

v_startip VARCHAR2(15);

v_endip   VARCHAR2(15);

begin

v_prefix_tmp:='00000000000000000000000000000000' ;

v_length:=LENGTH(ip_address);

v_comma1:=INSTR(ip_address,'.',1,1);

v_comma2:=INSTR(ip_address,'.',1,2);

v_comma3:=INSTR(ip_address,'.',1,3);

v_ipaddr1:=TO_NUMBER(SUBSTR(ip_address,1,v_comma1-1));

v_ipaddr2:=TO_NUMBER(SUBSTR(ip_address,v_comma1+1,v_comma2-v_comma1-1));

v_ipaddr3:=TO_NUMBER(SUBSTR(ip_address,v_comma2+1,v_comma3-v_comma2-1));

v_ipaddr4:=TO_NUMBER(SUBSTR(ip_address,v_comma3+1,v_length-v_comma3));

v_prefix_tmp1:=substr(v_prefix_tmp,1,prefix);

v_prefix_tmp2:=substr(v_prefix_tmp,prefix+1,32-length(v_prefix_tmp1));

v_prefix_tmp :=replace(v_prefix_tmp1,0,1)||v_prefix_tmp2;

v_prefix_b1:=substr(v_prefix_tmp,1,8);

v_prefix_b2:=substr(v_prefix_tmp,9,8);

v_prefix_b3:=substr(v_prefix_tmp,17,8);

v_prefix_b4:=substr(v_prefix_tmp,25,8);

v_prefix1:=F_BIN_TO_DEC(v_prefix_b1);

v_prefix2:=F_BIN_TO_DEC(v_prefix_b2);

v_prefix3:=F_BIN_TO_DEC(v_prefix_b3);

v_prefix4:=F_BIN_TO_DEC(v_prefix_b4);

v_netmask1:=bitand(v_ipaddr1,v_prefix1);

v_netmask2:=bitand(v_ipaddr2,v_prefix2);

v_netmask3:=bitand(v_ipaddr3,v_prefix3);

v_netmask4:=bitand(v_ipaddr4,v_prefix4);

--BITXOR(x,y) = BITOR(x,y) - BITAND(x,y) = (x + y) - BITAND(x, y) * 2;

v_broadcast1:=255-((v_netmask1+v_prefix1)-2*bitand(v_netmask1,v_prefix1));

v_broadcast2:=255-((v_netmask2+v_prefix2)-2*bitand(v_netmask2,v_prefix2));

v_broadcast3:=255-((v_netmask3+v_prefix3)-2*bitand(v_netmask3,v_prefix3));

v_broadcast4:=255-((v_netmask4+v_prefix4)-2*bitand(v_netmask4,v_prefix4));

v_startip:=to_char(v_netmask1)||'.'||to_char(v_netmask2)||'.'||to_char(v_netmask3)||'.'||to_char(v_netmask4+1);

v_endip  :=to_char(v_broadcast1)||'.'||to_char(v_broadcast2)||'.'||to_char(v_broadcast3)||'.'||to_char(v_broadcast4-1);

/**

v_return:=v_ipaddr1||','||v_ipaddr2||','||v_ipaddr3||','||v_ipaddr4||'***'||

+v_prefix1||','||v_prefix2||','||v_prefix3||','||v_prefix4||'***'||

+v_netmask1||','||v_netmask2||','||v_netmask3||','||v_netmask4||'***'||

+v_broadcast1||','||v_broadcast2||','||v_broadcast3||','||v_broadcast4||'***'||v_startip||','||v_endip;

**/

v_return:=v_startip||','||v_endip;

RETURN v_return;

END;

判断ip是否在一个ip段内

CREATE OR REPLACE FUNCTION IpToLong(ip in varchar2) return number is
  p1 number;
  p2 number;
  p3 number;
begin
  p1 := instr(ip, '.', 1, 1);
  p2 := instr(ip, '.', 1, 2);
  p3 := instr(ip, '.', 1, 3);
  return to_number(substr(ip, 1, p1-1))*256*256*256 
       + to_number(substr(ip, p1+1, p2-p1-1))*256*256 
       + to_number(substr(ip, p2+1, p3-p2-1))*256 
       + to_number(substr(ip, p3+1));
end;

--检测sql语句：
select *  from test_ip where IpToLong(ip) between IpToLong(start_ip) and IpToLong(end_ip);

去掉Tab，回车，空格符

 update bd_prod_cate c set c.cate_name = replace(c.cate_name,chr(9),'')//去掉tab符号的
 update bd_prod_cate c set c.cate_name = replace(c.cate_name,chr(10),'')//去掉回车符号的
 update bd_prod_cate c set c.cate_name = trim(c.cate_name)//去掉空格符号的

杀掉会话

--ORA-00054 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效” 问题的解决方法
--找出是哪个会话锁住了哪张表，session_id 为会话 ID；object_name 表名
select l.session_id,o.owner,o.object_name
from v$locked_object l,dba_objects o
where l.object_id=o.object_id;

--找出引发锁的会话，其中的 serial# 是我们需要用到的会话序列号。
select s.username,s.sid,s.serial#,s.logon_time,s.*
from  v$locked_object l,v$session s
where l.session_id=s.sid 
order by s.logon_time;

--杀掉会话，命令格式为：alter system kill session 'sid,serial#'
alter system kill session '916,2383';

快照恢复数据

-当不小心把数据删除掉，并且把事务提交了的话，那我们可以通过oracle提供的快照来进行恢复。出来的结果是某个时间点之前的全部数据 
--这时候我们可以查看某个时间点的数据了，然后导出数据再重新导入就可以了。
select * from test as of timestamp to_timestamp('2013-05-22 14:00:00','yyyy-mm-dd hh24:mi: ss');

正则函数

regexp_like() --返回满足条件的字段regexp_instr() --返回满足条件的字符或字符串的位置regexp_replace() --返回替换后的字符串regexp_substr() --返回满足条件的字符或字符串这四个函数的功能分别对应传统sql的 like操作符和 instr 、replace 、substr函数，在一般的、不怎么复杂的模式匹配中，使用传统的sql函数就能满足，当需要实现的操作比较复杂时，使用正则表达式函数，就可以写出简洁、强大的sql语句。

--查询value中以1开头60结束的记录并且长度是7位
select * from fzq where value like '1____60';
select * from fzq where regexp_like(value,'1....60');

--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。

--使用like就不是很好实现了。
select * from fzq where regexp_like(value,'1[0-9]{4}60');

-- 也可以这样实现，使用字符集。
select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');

-- 查询value中不是纯数字的记录
select * from fzq where not regexp_like(value,'^[[:digit:]]+$');

-- 查询value中不包含任何数字的记录。
select * from fzq where regexp_like(value,'^[^[:digit:]]+$');

--查询以12或者1b开头的记录.不区分大小写。
select * from fzq where regexp_like(value,'^1[2b]','i');

--查询以12或者1b开头的记录.区分大小写。
select * from fzq where regexp_like(value,'^1[2B]');

-- 查询数据中包含空白的记录。
select * from fzq where regexp_like(value,'[[:space:]]');

--查询所有包含小写字母或者数字的记录。
select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');

--查询任何包含标点符号的记录。
select * from fzq where regexp_like(value,'[[:punct:]]');

SELECT l.id, l.name
  FROM xb_link l
 WHERE regexp_replace(TRIM(l.name),
                      '([A-Za-z0-9]|[[:punct:]]|[[:space:]])',
                      '') IS NOT NULL;

正则表达式

匹配过程中可能会涉及到的元字符（Meta Character）对应的sql代码：

^ 使表达式定位至一行的开头
$ 使表达式定位至一行的末尾
*匹配0次或更多次
? 匹配0次或1次
+匹配1次或更多次
{m} 正好匹配m次
{m,} 至少匹配m次
{m,n} 至少匹配m次但不超过n次
[:alpha:] 字母字符，匹配字符A-Z、a-z
[:lower:] 小写字母字符,匹配字符a-z
[:upper:] 大写字母字符,匹配字符A-Z
[:digit:] 数字,匹配数字0-9
[:alphanum:] 字母数字字符,匹配字符A-Z、a-z、0-9
[:space:] 空白字符（禁止打印），如回车、换行符、竖直制表符和换页符[:punct:] 标点字符
[:cntrl:] 控制字符（禁止打印）
[:print:] 可打印字符|分隔替换选项，通常与分组符()一期使用

() 将子表达式分组为一个替换单元，量词单元或后向引用单元
[char] 字符列表
. 匹配除null之外的任意单个字符
/ 要匹配的字符是一个特殊字符、常量或者后者引用
x|y 匹配 x 或 y
[abc] 匹配abc中的任何单个字符
[a-z] 匹配 a 到 z 范围内的任意单个字符

--校验数字的表达式
数字：^[0-9]*$
n位的数字：^\d{n}$
至少n位的数字：^\d{n,}$
m-n位的数字：^\d{m,n}$
零和非零开头的数字：^(0|[1-9][0-9]*)$
非零开头的最多带两位小数的数字：^([1-9][0-9]*)+(\.[0-9]{1,2})?$
带1-2位小数的正数或负数：^(\-)?\d+(\.\d{1,2})$
正数、负数、和小数：^(\-|\+)?\d+(\.\d+)?$
有两位小数的正实数：^[0-9]+(\.[0-9]{2})?$
有1~3位小数的正实数：^[0-9]+(\.[0-9]{1,3})?$
非零的正整数：^[1-9]\d*$ 或 ^([1-9][0-9]*){1,3}$ 或 ^\+?[1-9][0-9]*$
非零的负整数：^\-[1-9][]0-9"*$ 或 ^-[1-9]\d*$
非负整数：^\d+$ 或 ^[1-9]\d*|0$
非正整数：^-[1-9]\d*|0$ 或 ^((-\d+)|(0+))$
非负浮点数：^\d+(\.\d+)?$ 或 ^[1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0$
非正浮点数：^((-\d+(\.\d+)?)|(0+(\.0+)?))$ 或 ^(-([1-9]\d*\.\d*|0\.\d*[1-9]\d*))|0?\.0+|0$
正浮点数：^[1-9]\d*\.\d*|0\.\d*[1-9]\d*$ 或 ^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$
负浮点数：^-([1-9]\d*\.\d*|0\.\d*[1-9]\d*)$ 或 ^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$
浮点数：^(-?\d+)(\.\d+)?$ 或 ^-?([1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0)$


--校验字符的表达式
汉字：^[\u4e00-\u9fa5]{0,}$
英文和数字：^[A-Za-z0-9]+$ 或 ^[A-Za-z0-9]{4,40}$
长度为3-20的所有字符：^.{3,20}$
由26个英文字母组成的字符串：^[A-Za-z]+$
由26个大写英文字母组成的字符串：^[A-Z]+$
由26个小写英文字母组成的字符串：^[a-z]+$
由数字和26个英文字母组成的字符串：^[A-Za-z0-9]+$
由数字、26个英文字母或者下划线组成的字符串：^\w+$ 或 ^\w{3,20}$
中文、英文、数字包括下划线：^[\u4E00-\u9FA5A-Za-z0-9_]+$
中文、英文、数字但不包括下划线等符号：^[\u4E00-\u9FA5A-Za-z0-9]+$ 或 ^[\u4E00-\u9FA5A-Za-z0-9]{2,20}$
可以输入含有^%&',;=?$\"等字符：[^%&',;=?$\x22]+
禁止输入含有~的字符：[^~]+



--特殊需求表达式
Email地址：^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$
域名：[a-zA-Z0-9][-a-zA-Z0-9]{0,62}(\.[a-zA-Z0-9][-a-zA-Z0-9]{0,62})+\.?
InternetURL：[a-zA-z]+://[^\s]* 或 ^http://([\w-]+\.)+[\w-]+(/[\w-./?%&=]*)?$
手机号码：^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\d{8}$
电话号码("XXX-XXXXXXX"、"XXXX-XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX")：^(\(\d{3,4}-)|\d{3.4}-)?\d{7,8}$
国内电话号码(0511-4405222、021-87888822)：\d{3}-\d{8}|\d{4}-\d{7}
电话号码正则表达式（支持手机号码，3-4位区号，7-8位直播号码，1－4位分机号）: ((\d{11})|^((\d{7,8})|(\d{4}|\d{3})-(\d{7,8})|(\d{4}|\d{3})-(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1})|(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1}))$)
身份证号(15位、18位数字)，最后一位是校验位，可能为数字或字符X：(^\d{15}$)|(^\d{18}$)|(^\d{17}(\d|X|x)$)
账号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^[a-zA-Z][a-zA-Z0-9_]{4,15}$
密码(以字母开头，长度在6~18之间，只能包含字母、数字和下划线)：^[a-zA-Z]\w{5,17}$
强密码(必须包含大小写字母和数字的组合，不能使用特殊字符，长度在 8-10 之间)：^(?=.*\d)(?=.*[a-z])(?=.*[A-Z])[a-zA-Z0-9]{8,10}$
强密码(必须包含大小写字母和数字的组合，可以使用特殊字符，长度在8-10之间)：^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$
日期格式：^\d{4}-\d{1,2}-\d{1,2}
一年的12个月(01～09和1～12)：^(0?[1-9]|1[0-2])$
一个月的31天(01～09和1～31)：^((0?[1-9])|((1|2)[0-9])|30|31)$
钱的输入格式：
有四种钱的表示形式我们可以接受:"10000.00" 和 "10,000.00", 和没有 "分" 的 "10000" 和 "10,000"：^[1-9][0-9]*$
这表示任意一个不以0开头的数字,但是,这也意味着一个字符"0"不通过,所以我们采用下面的形式：^(0|[1-9][0-9]*)$
一个0或者一个不以0开头的数字.我们还可以允许开头有一个负号：^(0|-?[1-9][0-9]*)$
这表示一个0或者一个可能为负的开头不为0的数字.让用户以0开头好了.把负号的也去掉,因为钱总不能是负的吧。下面我们要加的是说明可能的小数部分：^[0-9]+(.[0-9]+)?$
必须说明的是,小数点后面至少应该有1位数,所以"10."是不通过的,但是 "10" 和 "10.2" 是通过的：^[0-9]+(.[0-9]{2})?$
这样我们规定小数点后面必须有两位,如果你认为太苛刻了,可以这样：^[0-9]+(.[0-9]{1,2})?$
这样就允许用户只写一位小数.下面我们该考虑数字中的逗号了,我们可以这样：^[0-9]{1,3}(,[0-9]{3})*(.[0-9]{1,2})?$
1到3个数字,后面跟着任意个 逗号+3个数字,逗号成为可选,而不是必须：^([0-9]+|[0-9]{1,3}(,[0-9]{3})*)(.[0-9]{1,2})?$
备注：这就是最终结果了,别忘了"+"可以用"*"替代如果你觉得空字符串也可以接受的话(奇怪,为什么?)最后,别忘了在用函数时去掉去掉那个反斜杠,一般的错误都在这里
xml文件：^([a-zA-Z]+-?)+[a-zA-Z0-9]+\\.[x|X][m|M][l|L]$
中文字符的正则表达式：[\u4e00-\u9fa5]
双字节字符：[^\x00-\xff] (包括汉字在内，可以用来计算字符串的长度(一个双字节字符长度计2，ASCII字符计1))
空白行的正则表达式：\n\s*\r (可以用来删除空白行)
HTML标记的正则表达式：<(\S*?)[^>]*>.*?|<.*? /> ( 首尾空白字符的正则表达式：^\s*|\s*$或(^\s*)|(\s*$) (可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式)
腾讯QQ号：[1-9][0-9]{4,} (腾讯QQ号从10000开始)
中国邮政编码：[1-9]\d{5}(?!\d) (中国邮政编码为6位数字)
IPv4地址：((2(5[0-5]|[0-4]\d))|[0-1]?\d{1,2})(\.((2(5[0-5]|[0-4]\d))|[0-1]?\d{1,2})){3}

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。