一、概覽

INITCAP

返回字串並將字串的第一個字母變為大寫

INSTR(C1,C2,I,J)

在一個字串中搜尋指定的字元,返回發現指定的字元的位置;

C1 被搜尋的字串

C2 希望搜尋的字串

I 從哪個位置開始找,預設為1，

J 第幾次出現,預設為1

SCOTT@ORCL> select instr('oracle traning','ra',1,2) instring from dual;

  INSTRING

----------

         9

LENGTH:返回字串的長度；

LOWER: 返回字串,並將所有的字元小寫；

UPPER: 返回字串,並將所有的字元大寫；

RPAD和LPAD(貼上字元)

RPAD 在列的右邊貼上字元

LPAD 在列的左邊貼上字元

SCOTT@ORCL> select lpad(rpad('gao',10,'*'),17,'*') string from dual;

STRING

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

*******gao*******

LTRIM和RTRIM

LTRIM 刪除左邊出現的字串

RTRIM 刪除右邊出現的字串

SCOTT@ORCL> select ltrim(rtrim(' gao qian jing ',' g'),' ') string from dual;

STRING

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

gao qian jin

SUBSTR(string,start,count)

取子字串,從start開始（包括開始）,取count個

SCOTT@ORCL> select substr(13088888888,3,8) string from dual;

STRING

--------

08888888

REPLACE(string,s1,s2)

string 希望被替換的字元或變數

s1 被替換的字串

s2 要替換的字串

SCOTT@ORCL> select replace('he love you','he','I') string from dual;

STRING

----------

I love you

ABS：返回指定值的絕對值

CEIL：返回大於或等於給出數字的最小整數

SCOTT@ORCL>  select ceil(3.1415927) from dual;

CEIL(3.1415927)

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

              4

FLOOR:對給定的數字取整數

SCOTT@ORCL> select floor(2345.67) from dual;

FLOOR(2345.67)

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

          2345

MOD(n1,n2)

返回一個n1除以n2的餘數

SCOTT@ORCL> select mod(10,3),mod(3,3),mod(2,3) from dual;

MOD(10,3)   MOD(3,3)   MOD(2,3)

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

         1          0          2

ROUND和TRUNC

按照指定的精度進行舍入

round(55.5) = 56

round(55.4) = 55

trunc(sysdate,'yyyy') --返回當年第一天

trunc(sysdate,'mm') --返回當月第一天

trunc(sysdate,'day') --返回當前星期的第一天（週日）

TRUNC（89.985，2）=89.98

TRUNC（89.985）=89 (即取整)

TRUNC（89.985，-1）=80

日期函式

SQL> select  to_char(add_months(to_date('202012','yyyymm'),2),'yyyymm') datum from dual;   --增加或減去月份

DATUM

------

202102

SQL> select last_day(sysdate) datum from dual;   --返回本月的最後一天

DATUM

-----------

2020/12/31

SQL> select  months_between(to_date('20000520','yyyymmdd'),to_date('20050520','yyyymmdd')) mon_betw from dual;  --月份差異

  MON_BETW

----------

       -60

SQL> select next_day( sysdate, 'MONDAY') from dual;   --當前日期的下個星期一

NEXT_DAY(SYSDATE,'MONDAY')

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

2020/12/21 6:52:42

GREATEST：返回一組表示式中的最大值,即比較字元的編碼大小

LEAST：返回一組表示式中的最小值

STDDEV(distinct|all)

求標準差,ALL表示對所有的值求標準差,DISTINCT表示只對不同的值求標準差

SQL> select stddev(sal) from scott.emp;

STDDEV(SAL)

-----------

1182.503223

pl/sql中的case語句

select  (case  when  DUMMY='X'  then  0  else  1  end)   as  flag  from  dual;

1.case的第1種用法：

case col when 'a' then 1

when 'b' then 2

else 0 end

這種用法跟decode一樣沒什麼區別

2.case的第2種用法：

case when score <60 then 'd'

when score >=60 and score <70 then 'c'

when score >=70 and score <80 then 'b'

else 'a' end

NVL(expr1, expr2)

expr1為NULL，返回expr2，不為NULL，返回expr1，注意兩者的型別要一致

NVL2 (expr1, expr2, expr3) 

expr1不為NULL，返回expr2,為NULL,返回expr3,expr2和expr3型別不同的話，expr3會轉換為expr2的型別

NULLIF (expr1, expr2) 

相等返回NULL，不等返回expr1

to_char 中fm

有9的地方如果有數字就顯示如果沒有數字就不顯示，有0的地方在沒有數字的時候也會有０來佔位

select to_char(9999.09556,'fm99999.0900'),to_char(9999.09556,'fm00099.0900') from dual；

分析函式

分析函式用於計算基於組的某種聚合值，它和聚合函式的不同之處是對於每個組返回多行，而聚合函式對於每個組只返回一行,常用的分析函式如下所列:

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 ...)

二、分析函式

2.1 rank（等級函式，也指示排名函式）

ROW_NUMBER()

定義：ROW_NUMBER()函式作用就是將select查詢到的資料進行排序，每一條資料加一個序號，一般多用於分頁查詢，

比如查詢前10個查詢10-100個學生。

例項：

對學生成績排序

這裡number就是每個學生的序號根據studentScore(分數)進行desc倒序

獲取第二個同學的成績資訊

這裡用到的思想就是分頁查詢的思想在原sql外再套一層select

where t.number>=1 and t.number<=10 是不是就是獲取前十個學生的成績資訊納。

RANK()

定義： RANK ()函式，顧名思義排名函式，可以對某一個欄位進行排名，這裡為什麼和ROW_NUMBER()不一樣那，ROW_NUMBER()是排序，

當存在相同成績的學生時，ROW_NUMBER()會依次進行排序，他們序號不相同，而Rank()不一樣出現相同的，他們的排名是一樣的，看下面看例子:

注：當出現兩個學生成績相同是裡面出現變化。

RANK ()是 1 2 2 ，而ROW_NUMBER()則還是1 2 3 ，這就是RANK()和ROW_NUMBER()的區別了

DENSE_RANK()

定義：DENSE_RANK()函式也是排名函式，和RANK()功能相似，也是對欄位進行排名，那它和RANK()到底有什麼不同那？看例子：

DENSE_RANK()密集的排名他和RANK()區別在於，排名的連續性， DENSE_RANK()排名是連續的， RANK()是跳躍的排名，所以一般情況下用的排名函式就 DENSE_RANK()

NTILE()

定義： NTILE ()函式是將有序分割槽中的行分發到指定數目的組中，就是按序號分組的意思，各個組有編號，編號從1開始，就像我們說的分割槽一樣，分為幾個區，一個區會有多少個。

這裡查詢了3次,第一次分為1個區,所以查詢結果number全是1，

第二次分為2個區，查詢結果為 1 1 2，意思就是第一個區為 1 1 （兩個編號的資料），第二個區只有2這個資料。

總結：

select row_number() over ( order by a.deptno desc ) rak,a.* from emp a --排序，用於分頁查詢

select rank () over ( order by a.deptno desc ) rak,a.* from emp a -- 跳躍的排名

select dense_rank() over ( order by a.deptno desc ) rak,a.* from emp a --排名連續

select NTILE ( 4 ) over ( order by a.deptno desc ) rak,a.* from emp a --分幾個組

2.2 windowing函式

Order By 子句

select ename,sal, avg (sal) over () from emp; --所有值的平均值放在每行

select ename,sal, avg (sal) over ( order by ename) from emp; --累積平均

注：在沒有Order by子句時，在全部組上計算平均值，每一行給一個同樣的值。

在用帶有Order by的AVG（）時，每一行的平均值是那一行與前面所有行的平均值（此處用作開窗函式）

select ename,

deptno,

sum (sal) over ( order by ename, deptno) sum_ena me_deptno, -- 但它僅僅在分割槽內對行進行排序，邏輯計算

sum (sal) over ( order by deptno, ename) sum_deptno_ename

from emp

order by ename, deptno --只是排序，不影響邏輯計算

ROW 視窗

注： Windowing 子句給出了一個定義變化或固定的資料視窗方法，分析函式將對這些資料進行操作，

在一組內基於任意變化或固定的視窗中，可以用該子句來讓分析函式計算它的值，

ROW視窗是物理單元，是包括在視窗中的行的物理數。使用前面的列子作為ROW分割槽

select deptno,ename,sal ,

sum ( sal ) over(

partition by deptno

order by ename

rows 2 preceding

) sliding_total

from emp

order by deptno,ename

這將在一組內建立一個變化的視窗，並計算那一組中當前行的 SAL 列加上前兩行 SAL 列的總和，請注意，要使用視窗，必須使用 ORDER BY 子句

Range 視窗

select ename,

hiredate,

sal,

avg (sal) over ( order by hiredate asc range 100 preceding ) avg_sal_100_days_before,

avg (sal) over ( order by hiredate desc range 100 preceding ) avg_sal_100_days_after

from emp

order by hiredate asc

如果有“range 5 preceding”，將產生一個滑動的視窗，它在組中擁有所有當前行以前5行的集合

range是邏輯視窗，是指定當前行對應值的範圍取值，列數不固定，只要行值在範圍內，對應列都包含在內

rows是物理視窗，即根據order by 子句排序後，取的前N行及後N行的資料計算（與當前行的值無關，只與排序後的行號相關）

指定了分析函式工作的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化

over（order by salary rows between 50 preceding and 150 following） --每行對應的資料視窗是之前50行，之後150行

FIRST_VALUE：返回組中的第一個值

LAST_VALUE：返回組中的最後一個值

LAG：上一行

LEAD：下一行

KEEP函式

功能描述：從DENSE_RANK返回的集合中取出排在最後面的一個值的行（可能多行，因為值可能相等），因此完整的語法需要在開始處加上一個集合函式以從中取出記錄

SELECT last_name, department_id, salary,

MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)

OVER (PARTITION BY department_id) "Worst",

MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)

OVER (PARTITION BY department_id) "Best"

FROM employees

WHERE department_id in (20,80)

ORDER BY department_id, salary;

LAST_NAME DEPARTMENT_ID SALARY Worst Best

三、常用日期函式

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') strDateTime from dual; --獲取年-月-日 時:分:秒

--顯示結果為：2018-09-20 12:35:21

select to_char(sysdate,'yyyy') strYear from dual; --獲取年

--顯示結果為：2018

select to_char(to_date('2018-09-20','yyyy-mm-dd'),'day') strDay from dual;  --查詢某天是星期幾

--顯示結果為：hursday

select floor(sysdate - to_date('20201210','yyyymmdd'))  strTime from dual;  --兩個日期間的天數

select months_between(date'2018-04-23',date'2017-04-23') days from dual;  --月份差

select TO_CHAR(SYSDATE,'DDD'),sysdate from dual  --查詢某天是一年的第幾天

select add_months(sysdate,1) from dual;  --下個月的今天

select trunc(sysdate, 'mm') from dual  --返回當月第一天

select trunc(sysdate,'yy') from dual  --返回當年第一天

select trunc(sysdate,'d') from dual  --返回當前星期的第一天（週日）

select trunc(sysdate,'IW') from dual;  --返回當前星期的第一天（週一）

SELECT last_day(SYSDATE) FROM dual;  --返回指定日期對應月份的最後一天

sysdate - 10 as "10天前",

sysdate - 10 / 24 as "10小時前",

sysdate - 10 / (24 * 60) as "10分鐘前",

sysdate - 10 / (24 * 3600) as "10秒鐘前"

select to_char(TO_DATE('20190308', 'YYYYMMDD'),  'yyyyiw') as week, --oracle求當年的第幾周

       to_char(TO_DATE('20190308', 'YYYYMMDD'),  'yyyyww') as week2, --oracle求當年的第幾周

       to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyy')  as year, --oracle求第幾年

       to_char(TO_DATE('20190308', 'YYYYMMDD'),  'yyyymm') as month, --oracle求當年的第幾月

       to_char(TO_DATE('20190308', 'YYYYMMDD'),  'yyyyddd') as day, --oracle求當年的第幾天

       to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyyq')  as quarter -- oracle求當年的第幾季度

from dual

非ISO: 永遠以新年的第一天為第一週的星期一，第一週一定有七天，而且最後一週不一定有七天。

ISO: 輪到星期幾就是星期幾，新年的第一天是星期幾則為第一週的週期幾，第一週不一定有七天，而且一定會要最後一週滿七天，如果這年最後一週未滿七天，則這一週會持續到下一年的前幾天。每個日曆星期從星期一開始，星期日為第7天。

--關聯子查詢更新

update userinformgroup a set a.usergroupname = ( select b.usergroupname from wdd_bf b where b.userid = a.userid )

where a.informgroupname = 'portError-monitoring' ;

--統計一段期間內的天數

SELECT TO_CHAR(TO_DATE( '20200101' , 'yyyymmdd' ) + ROWNUM - 1 , 'yyyymmdd' ) AS DATAS

FROM DUAL

CONNECT BY ROWNUM <= TRUNC (TO_DATE( '20200201' , 'yyyymmdd' ) -TO_DATE( '20200101' , 'yyyymmdd' )) + 1

-- 獲取連續的資料(注意：level只用使用<,<=,=符號)

select level from dual connect by level <= 5

- 獲取連續的指定時間 ( 注意：獲取連續的時間需要包含當天需要再 + 1 天 )

select sysdate - level + 1 days from dual connect by level <= 5

--列轉行案例

with t as (

select deptno ,

sum ( decode ( job , 'PRESIDENT' , sal , 0 )) as PRESIDENT_SAL ,

sum ( decode ( job , 'MANAGER' , sal , 0 )) as MANAGER_SAL ,

sum ( decode ( job , 'ANALYST' , sal , 0 )) as ANALYST_SAL ,

sum ( decode ( job , 'CLERK' , sal , 0 )) as CLERK_SAL ,

sum ( decode ( job , 'SALESMAN' , sal , 0 )) as SALESMAN_SAL

from emp group by deptno

)

select deptno ,

decode ( lvl , 1 , 'PRESIDENT' , 2 , 'MANAGER' , 3 , 'ANALYST' ,

4 , 'CLERK' , 5 , 'SALESMAN' ) as JOB ,

decode ( lvl , 1 , PRESIDENT_SAL , 2 , MANAGER_SAL , 3 , ANALYST_SAL ,

4 , CLERK_SAL , 5 , SALESMAN_SAL ) as TOTAL_SAL

from t , ( select level lvl from dual connect by level <= 5 )

order by 1 , 2 ;

oracle常用函式

相關文章