003-函数

数字操作函数

SELECT Trunc(1234.56, 1) "1234.5"  --截取
       ,Round(1234.56, 1) "1234.6" --四舍五入
       ,Ceil(0.4) "1"              --向上取整
       ,Floor(0.6) "0"             --向下取整
       ,Abs(-5) "5"                --绝对值
  FROM Dual;

日期操作函数

SELECT To_Date('2017-11-11 09:00:00', 'YYYY-MM-DD HH24:MI:SS') 字符转日期
      ,SYSDATE - 1 / (24 * 60 * 60)                            减去1秒
      ,Trunc(SYSDATE)                                          去除时分秒
      ,Trunc(Add_Months(Last_Day(SYSDATE) + 1, -2))            上月头
      ,Trunc(Add_Months(Last_Day(SYSDATE), -1))                上月末
      ,Trunc(Add_Months(Last_Day(SYSDATE) + 1, -1))            本月头
      ,Trunc(Last_Day(SYSDATE))                                本月末
      ,Trunc(Add_Months(Last_Day(SYSDATE) + 1, 0))             下月头
      ,Trunc(Last_Day(Add_Months(Last_Day(SYSDATE) + 1, 0)))   下月末
      ,Add_Months(SYSDATE, 1)                                  日期增加或减少n月
      ,Months_Between(SYSDATE, SYSDATE - 30)                   日期相差月数
      ,To_Char(SYSDATE, ' Q')                                  Q季度
      ,To_Char(SYSDATE, 'WW')                                  当年第几周
      ,To_Char(SYSDATE, 'DDD')                                 当年第几天
      ,To_Char(SYSDATE, 'W')                                   本月第几周
  FROM Dual;

处理时间间隔

NUMTODSINTERVAL
单位：’DAY’、’HOUR’、’MINUTE’、’SECOND’

SELECT (To_Date('2017-11-11', 'YYYY-MM-DD') + Numtodsinterval(3, 'DAY')) "2017-11-14"
  FROM Dual;

NUMTOYMINTERVAL
单位：’YEAR’、’MONTH’

SELECT (To_Date('2017-11-11', 'YYYY-MM-DD') + Numtoyminterval(3, 'YEAR')) "2020-11-11"
  FROM Dual;

字符串操作函数

大小写转换

SELECT Lower('ORACLE')   "sql"   --小写
      ,Upper('oracle')   "SQL"  --大写
      ,Initcap('ORACLE') "Sql"  --首字母大写
  FROM Dual;

获取字符串长度

SELECT Length('ORACLE') "6"  --获取字符长度（字母汉字都占1位）
      ,Lengthb('数据库') "6" --获取字节长度（字母占1位，汉字可能占2位）
  FROM Dual;

字符串截取

SELECT Substr('ORACLE数据库', 7)     "数据库" --从第7位往后截取
      ,Substr('ORACLE数据库', 7, 1)  "数"      --从第7位往后截取1位
      ,Substr('ORACLE数据库', -3)   "数据库"  --从倒数第3位往后截取
      ,Substrb('ORACLE数据库', 7, 2) "数"       --字节截取，如汉字占2位，只截取(7,1)会取到空
  FROM Dual;

字符串替换

SELECT REPLACE('ORACLE数据库', 'ORACLE', 'oracle') "oracle数据库" 
      ,REVERSE('oracle') "'elcaro" --字符反转
  FROM Dual;

查找字符串位置

SELECT Instr('ORACLE数据库', '据', 1, 1)  "8"  --查找第1位开始，第1次匹配的字符串位置
      ,Instrb('ORACLE数据库', '据', 1, 1) "9" --字节查找
  FROM Dual;

字符填充

SELECT Lpad('ORACLE', 10, '*') "****ORACLE" --左填充
      ,Rpad('ORACLE', 10, '*') "ORACLE****" --右填充
  FROM Dual;

NVL

SELECT Nvl(1, 2)     "1"  --如果1为空，返回2
      ,Nvl2(1, 2, 3) "2"  --如果1为空，返回3,否则返回2
      ,Nullif(1, 2)  "1"  --如果1和2相等返回空，不相等返回1
  FROM Dual;

FOR UPDATE

对查询出来的数据加行级锁

• FOR UPDATE：如果数据正在被锁定，会等到数据被释放（commit）后才执行锁定
• FOR UPDATE NOWAIT：发现数据被锁定，立刻返回报错

SELECT * FROM Emp FOR UPDATE NOWAIT;

也可用于在PL\SQL DEV中快速DML操作

字符串分割

默认用英文逗号分隔，也可以在后面指定分隔符号

SELECT t.Column_Value FROM TABLE(Split('AAA,BBB,CCC', ',')) t;

GREATEST

比较数值大小，当存在NULL时，返回NULL

SELECT Greatest('A', 'B', 'C') "C" FROM Dual;

URL编码解码

ESCAPE编码

BEGIN
  Dbms_Output.Put_Line(Utl_Url.Escape('http://www.google.com/参数=+/', TRUE, 'UTF8'));
END;

UNESCAPE解码

BEGIN
Dbms_Output.Put_Line(Utl_Url.Unescape('http%3A%2F%2Fwww.google.com%2F%E5%8F%82%E6%95%B0%3D%2B%2F'
                                       ,'UTF8'));
END;

分组排序/计算

• RANK：跳跃排序
• DENSE_RANK：连续排序
• ROW_NUMBER：没有重复值排序
• MAX/MIN：分组求最大最小值
• LAG： 获取当前记录往前面1行Sal的值，没有则默认值为100
• LEAD：获取当前记录往后面1行Sal的值，没有则默认值为100
• RATIO_TO_REPORT：计算Sal占总数的比例

SELECT t.Ename
      ,t.Job
      ,t.Sal
      ,Rank() Over(PARTITION BY t.Job ORDER BY t.Sal) Rank_Num
      ,Dense_Rank() Over(PARTITION BY t.Job ORDER BY t.Sal) Dense_Num
      ,Row_Number() Over(PARTITION BY t.Job ORDER BY t.Sal) Row_Num
      ,MAX(t.Sal) Over(PARTITION BY t.Job) Max_Sal
      ,MIN(t.Sal) Over(PARTITION BY t.Job) Min_Sal
      ,Lag(t.Sal, 1, 100) Over(PARTITION BY t.Job ORDER BY t.Sal) Lag_Sal
      ,Lead(t.Sal, 1, 100) Over(PARTITION BY t.Job ORDER BY t.Sal) Lead_Sal
      ,SUM(t.Sal) Over(PARTITION BY t.Job) Sum_Sal
      ,AVG(t.Sal) Over(PARTITION BY t.Job) Avg_Sal
      ,COUNT(t.Sal) Over(PARTITION BY t.Job) Count_Num
      ,Ratio_To_Report(t.Sal) Over(PARTITION BY 1) Ratio_Amount
  FROM Emp t;

MERGE

用于两个表之间数据的更新同步，基于源表对目标表做Insert，Update，Delete操作

MERGE INTO Targettable t
USING Sourcetable s
ON (t.Numid = s.Numid) --强制需要括号
WHEN MATCHED THEN      --匹配
  UPDATE SET t.Describtion = s.Describtion
  DELETE WHERE s.Numid <> t.Numid
WHEN NOT MATCHED THEN   --不匹配
  INSERT VALUES (s.Numid, s.Describtion);

TIPS：
MERGE为以上固定写法，DELETE只会对MATCHED所有数据进行删除筛选，UPDATE更新后的数据如果符合删除条件，也会被删除

INSERT ALL/FIRST

一个来源插入多个目标表
FIRST：考虑先后关系，如果有数据满足多个WHEN条件，则只执行第一个WHEN
ALL：不考虑先后关系，只要满足条件就插入

INSERT ALL 
WHEN User_Id = 1 THEN 
INTO Tablename(User_Id, User_Name) VALUES(Tablename_s.Nextval, '001') 
ELSE 
INTO Tablename(User_Id, User_Name) VALUES(Tablename_s.Nextval, '002')
SELECT User_Id FROM Tablename;

表结构行转列

--init
CREATE TABLE Temp_Emp(Group_Id NUMBER, Line_No NUMBER, Temp VARCHAR2(240));
--insert
INSERT ALL
INTO Temp_Emp(Group_Id, Line_No, Temp) VALUES(Empno, 1, Ename)
INTO Temp_Emp(Group_Id, Line_No, Temp) VALUES(Empno, 2, Job)
INTO Temp_Emp(Group_Id, Line_No, Temp) VALUES(Empno, 3, Mgr)
INTO Temp_Emp(Group_Id, Line_No, Temp) VALUES(Empno, 4, Hiredate)
INTO Temp_Emp(Group_Id, Line_No, Temp) VALUES(Empno, 5, Sal)
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM Emp;
--query
SELECT *
  FROM Temp_Emp t
 ORDER BY t.Group_Id
         ,t.Line_No;

执行结果：

行转列

按照IN里面的参数进行分列，如果Job能跟IN中参数列对应上，就将Ename放入字段中，注意Ename必须放在聚合函数中

SELECT *
  FROM (SELECT Empno
              ,Ename
              ,Job
              ,Job Jobtemp
          FROM Emp t)
Pivot(MAX(Ename)
   FOR Job IN('ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN'));

列转行

除Ename列以外的列全部转成行，这些列必须是同一类型，Columnkey和Columnvalue为自定义名字，代表列名和列值

SELECT Ename
      ,Columnkey
      ,Columnvalue
  FROM (SELECT t.Ename
              ,t.Job
              ,To_Char(t.Mgr) Mgr
              ,To_Char(t.Hiredate, 'YYYY-MM-DD HH24:MI:SS') Hiredate
              ,To_Char(t.Sal) Sal
              ,To_Char(t.Comm) Comm
              ,To_Char(t.Deptno) Deptno
          FROM Emp t) p Unpivot(Columnvalue FOR Columnkey IN(Job
                                                         ,Mgr
                                                         ,Hiredate
                                                         ,Sal
                                                         ,Comm
                                                         ,Deptno))
 ORDER BY Ename;

字符拼接

字符串用逗号分割拼接，长度不能超过4000

SELECT Wm_Concat(DISTINCT e.Ename) FROM Emp e;

12c以后换成Listagg

SELECT e.Deptno
      ,Listagg(e.Ename, ',') Within GROUP(ORDER BY e.Deptno)
  FROM Emp e
 GROUP BY e.Deptno;

TIPS：以下两种方法可以实现长度不受限制
方法1：

SELECT TRIM(Xmlagg(Xmlelement(Content, e.Ename || ',') ORDER BY e.Empno).Extract('//text()')) AS Concat
  FROM Emp e;

方法2：

SELECT Dbms_Lob.Substr(Rtrim(Xmlagg(Xmlparse(Content e.Ename || ',' Wellformed) ORDER BY e.Empno)
                             .Getclobval()
                            ,',')
                      ,4000 --自定义大小
                      ,1)
  FROM Emp e;

树形遍历

向上遍历根节点

SELECT t.Empno
      ,t.Ename
      ,t.Mgr
      ,LEVEL
  FROM Emp t
 WHERE 1 = 1
 START WITH t.Ename = 'JONES'
CONNECT BY Nocycle PRIOR t.Mgr = t.Empno  --当前记录的Mgr等于下一条记录的Empno  --Nocycle不循环
 ORDER BY LEVEL ASC;

向下遍历子节点

SELECT t.Empno
      ,t.Ename
      ,t.Mgr
      ,Lpad(t.Ename, LEVEL * 5) Tree
      ,LEVEL
  FROM Emp t
 WHERE 1 = 1
 START WITH t.Ename = 'JONES'
CONNECT BY Nocycle PRIOR t.Empno = t.Mgr --本记录的Empno等于下一条记录的Mgr
 ORDER BY LEVEL ASC;

随机数

SELECT LEVEL Seq_Data
      ,Trunc(Dbms_Random.Value(1000, 500000), 2) Num_Data
      ,Decode(Round(Dbms_Random.Value(1, 2)), 1, 'Y', 2, 'N') Flag_Data
      ,To_Date(Round(Dbms_Random.Value(2000, 2017)) || '-' || Round(Dbms_Random.Value(1, 12)) || '-' ||
               Round(Dbms_Random.Value(1, 28))
              ,'YYYY-MM-DD') Date_Data
      ,Dbms_Random.String('A', Dbms_Random.Value(1, 20)) Str_Data
  FROM Dual
CONNECT BY LEVEL < 100;

休眠

DBMS_LOCK.SLEEP(30);--休眠30秒