Oracle数据库实用SQL技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/Gavin_zhang520/article/details/117350890

Oracle实用语句

1. 获取增减指定天数的日期

直接使用sysdate加减指定天数

--获取7日前的日期
SELECT TO_CHAR(SYSDATE - 7, 'yyyymmdd') FROM DUAL;
--获取7日后的日期
SELECT TO_CHAR(SYSDATE + 7, 'yyyymmdd') FROM DUAL;
--获取一个月前的日期
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'yyyymmdd') FROM DUAL;
--获取一个月后的日期
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,+1), 'yyyymmdd') FROM DUAL;

2. 数据格式化

日期格式化

TO_DATE(20210527, 'yyyy-mm-dd hh24:mi:ss')

数字格式化

--将日期转换成指定格式字符串
TO_CHAR(SYSDATE, 'yyyy-mm-dd')
--对数字进行格式化
TO_CHAR(1234567.89, 'FM99999990.00')
--9表示，如果存在数字则显示数字，不存在则显示空格
--0表示，如果存在数字则显示数字，不存在则显示0，即占位符
--FM表示，如果是因为9带来的空格则删除

字符串格式化

--去除字符串左边的空格
LTRIM()
--去除字符串右边的空格
RTRIM()
--去除字符串两侧的空格
TRIM()

--获取字符串的长度
LENGTH()

--转换字符串为大写
LOWER()
--转换字符串为小写
UPPER()
--转换字符串为首字母大写
INITCAP()

--连接两个字符串
CONCAT()
--查找字符在字符串中的位置，存在则输出对应位置，不存则输出0
INSTR('ABC','A')
--截取子字符，获取I-J的子字符串，如果J不存在，则从I到末尾；如果I不存在，则从开头到J。
SUBSTR('ABCDE',I,J)

--替换函数，用D替换ABC中存在的所有D
REPLACE('ABC','B','D')

--左填充函数，用参数3往左补足参数1到参数2的长度
LPAL('ABC',4,'0')
--右填充函数，用参数3往右补足参数1到参数2的长度
RPAD('ABC',4,'0')

--将字符转换为ASCII码值
ASCII()
--将ASCII码转换成对应字符
CHR()

3. 排序空值问题

--空值在后
SELECT * FROM TABLENAME ORDER BY AGE DESC NULLS LAST
--空值在前
SELECT * FROM TABLENAME ORDER BY AGE DESC NULLS FIRST

4. 百分比

--占所有科目的百分比
RATIO_TO_REPORT(SCORE) OVER()
--占各科目的百分比
RATIO_TO_REPORT(SCORE) OVER(PARTITION BY DEPARTMENT)
--第一个括号中代表分子，第二个括号中代表分母（分母缺省为整列的和）

5. 除数为0

DECODE(B,0,0,A/B)

6. 随机数

--按照指定的精度截取一个数
SELECT TRUNC(DBMS_RANDOM.VALUE(1000,9999)) FROM DUAL;
--按照指定的精度进行四舍五入
SELECT ROUND(DBMS_RANDOM.VALUE(1000,9999)) FROM DUAL;
--ceil返回大于或等于，给出数字的最小整数
SELECT CEIL(DBMS_RANDOM.VALUE(1000,9999)) FROM DUAL;
--floor取整数位
SELECT FLOOR(DBMS_RANDOM.VALUE(1000,9999)) FROM DUAL;
--Oracle的dbms_random.value(min，max)函数是完全包括上下限，但是数据分布并不均匀，其中边界值加起来才达到中间值相同的级别

7. Start With

SELECT ... FROM 表名 START WITH 条件1 CONNECT BY PRIOR 条件2 WHERE 条件3
--条件1是根结点的限定语句，可以放宽限定条件，以取得多个根结点（多棵树）。
--条件2是连接条件，其中用PRIOR表示的是上一条记录，比如CONNECT BY PRIOR FID = ID，表示上一条记录的FID=本条记录的ID。
--条件3是过滤条件，用于对返回的所有记录进行过滤。

8. 表被锁死

--查看被锁的表：
SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL#
  FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S
 WHERE L.OBJECT_ID = O.OBJECT_ID
   AND L.SESSION_ID = S.SID;
--解锁：
ALTER SYSTEM KILL SESSION 'sid, serial';

9. 数据库闪回

SELECT * FROM USER.TABLENAME AS OF TIMESTAMP TO_TIMESTAMP('2021-06-05 21:50:00','yyyy-mm-dd hh24:mi:ss');

10.去除表中重复数据

DELETE FROM USER.TABLENAME T
 WHERE T.ROWID IN (SELECT RID FROM (SELECT T.ROWID RID, ROW_NUMBER() OVER(PARTITION BY COL ORDER BY T.COL) RN FROM USER.TABLENAME T) T1 WHERE T1.RN > 1);

11.表分析

analyze table tablename compute statistics for table for all indexes for all columns

12. merge into

merge into 目标表 a
 
using 源表 b
 
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
 
when matched then update set a.更新字段=b.字段
 
when  not matched then insert into a(字段1,字段2……)values(值1,值2……)

13. FOR循环

 DECLARE
   X number; --声明变量
 BEGIN
   x := 1; --给初值
   FOR X IN REVERSE 1 .. 10 LOOP
     --reverse由大到小
     DBMS_OUTPUT.PUT_LINE('内:x=' || x);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); --x=1
 END;

14. WHILE循环

DECLARE
  x number;
BEGIN
  x := 0;
  WHILE x < 9 LOOP
    x := x + 1;
    DBMS_OUTPUT.PUT_LINE('内:x=' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('外:x=' || x);
END;

15. LOOP循环

 DECLARE
   x number;
 BEGIN
   x := 0;
   LOOP
     x := x + 1;
   
     EXIT WHEN x > 9;
     DBMS_OUTPUT.PUT_LINE('内:x=' || x);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('外:x=' || x);
 END;