Oracle常用單行函數（原創）

来源:https://www.cnblogs.com/loveleaf/archive/2018/08/06/9417765.html
-Advertisement-
Play Games

前言： 想把單行函數進行一個比較全面的總結，並分享給有需要的人，有不明之處還請多多指教。 SQL函數：Oracle的內置函數，包括了單行函數和多行函數，本文重點講解單行函數。單行函數又可以分為許多類，本人將常用的分為5大類： 字元函數（7個），數值函數（7個），日期函數（7個），轉換函數（3個），通 ...


 

 前言：

想把單行函數進行一個比較全面的總結，並分享給有需要的人，有不明之處還請多多指教。

SQL函數：Oracle的內置函數，包括了單行函數和多行函數，本文重點講解單行函數。單行函數又可以分為許多類，本人將常用的分為5大類：

字元函數（7個），數值函數（7個），日期函數（7個），轉換函數（3個），通用函數（9個），下麵開始分析。

一、字元函數：

1.concat函數:用來拼接2個字元串，Expression:concat(str1,str2);

Example:

select concat('milktea','leaf') from dual;

 結果如圖：

 註意：concat函數不能處理2個以上的字元串拼接，否則會報參數個數無效的錯誤。

select concat('milktea','leaf','third') from dual;

 

2、substr函數：用於截取字元串，從指定位置index截取指定長度length的字元串。Expression:substr(str,index,length);

Example:

select substr('milktealeaf',5,3) from dual;

結果如圖：

註意：此處字元串的下標從1開始，index的值是幾就是第幾個。

另外還有一個substrb函數，和substr函數作用相同，但2者稍有差別：substr按字元取，而substrb按位元組取。結論顯而易見：

在取英文字母時，2者相同，但在取漢字時，一個漢字2個位元組，如果是奇數個位元組則自動捨棄最後一位，示例如下：

select substr('milktealeaf',2,5) from dual;

select substrb('milktealeaf',2,5) from dual;

select substr('李先森陳小姐你們好呀',2,5) from dual;

select substrb('李先森陳小姐你們好呀',2,5) from dual;

 

 3、Instr函數：用於獲取一個字元串中子串str_son在字元串str_father中從index下標查找第times次出現的位置,返回一個下標

Expression:(str_father,str_son,index,times)

 Example:

select instr('milkteatealeaftea','tea',3,2) from dual;

註：如圖所示。第二個tea在下標為8處。

另外，也有一個instrb函數，同substr和substrb函數一樣，instr用於字元，instrb用於位元組，示例如下：

select instr('李先森陳女士李先森李先森陳女士','李先森',3,2) from dual;

select instrb('李先森陳女士李先森李先森陳女士','李先森',3,2) from dual;

 

4、length函數：返回一個字元串的長度 Expression：length(str);

Example:

select length('milktea') from dual

5、lpad函數：用於對字元串左側進行填充的函數，l即為left，Expression:lpad(str1,length,str2)其中str1為被填充的字元串，length為填充以後的長度，str2為要填充的字元。

Example:

select lpad('leaf',9,'%*') from dual;

註：從左側開始填充，只要長度達到立即停止，不管要填充的字元串有沒有填充完整。有了左填充，必然有右填充rpad,r即為right,從字元串右側進行填充。示例如下：

select rpad('leaf',9,'%*') from dual;

6、replace函數：顧名思義，替換函數，將字元串中的子串用其他字元進行替換。Expression:replace(str_father,str_son,str_replace);

Example:

select replace('milkteathird','third','leaf') from dual;

7、ltrim函數：去掉字元串左側的空白。 Expression：ltrim(str)

Example:

select ltrim('  milktea  leaf  ') from dual;

可以看到左側空白已經去除，那麼同樣的有rtrim函數，去掉右側空白。去空白，那麼當然左右2側都去也可以，trim函數。示例如下：

select rtrim('  milktea  leaf  ') from dual;

select trim('  milktea  leaf  ') from dual;

 

二、數值函數：

1、round函數：用於對一個數取四捨五入後的結果。Expression(number,m)    number為數字，m為要四捨五入的位數,必須為整數。

Example：

select round(13.14,1) from dual;

select round(13.14,-1) from dual;

可以看出，當位數為正數時，保存小數點後面的位數，負數時直接取整數部分的四捨五入。

2、trunc函數：直接進行截取數字，不進行四捨五入。Expression: trunc(number,m)number為截取的數字，m為截取的位數。

Example：

select trunc(15.15,1) from dual;

select trunc(15.15,-1) from dual;

並沒有進行四捨五入，直接截取，毫不留情。

3、mod函數：對2個數進行取模運算，如果除數為0 ，則返回被除數。Expression:mod(number1,number2)

Example:

select mod(5,2) from dual;

select mod(3,0) from dual;

4、ceil函數：用於返回大於等於當前數的最小整數 Expression:ceil(number)

Example:

select ceil(13.14) from dual;

5、floor函數：和ceil函數相反，用於返回小於等於當前數的最大整數 Expression:floor(number)

Example:

select floor(13.14) from dual;

6、abs函數：返回當前數的絕對值：Expression:abs(number)

Example:

select abs(-1314) from dual;

7、cos函數：返回當前數的餘弦值  Expression:cos(number)

Example:

 

select cos(0) from dual;

三、日期函數：

1、sysdate函數：返回系統當前日期 Expression :sysdate

Example:

select sysdate from dual;

2、round函數：對日期取四捨五入的結果，如果精度是日的話以每周周四為標準，精度是月以每月16日為標準，精度是年以每年7月1日為標準。   分別四捨五入到最近的周日，每月1號，以及每年1月1日，  Expression:round(date,'format')

Example:

select round(sysdate,'day') from dual;

select round(sysdate,'month') from dual;

select round(sysdate,'year') from dual;

可以看到現在的時間過了周三，取最近的周日8月5日，按月份還沒過16日，取每月1日，按年份已經過了7月1日，取下一年的1月1日。

3、trunc函數：截取日期，精度為日，取當前星期的第一天（按第一天是周日算），精度為月，按每月第一日算，精度為年，按每年1月1日算。Expression：trunc(date,'format')

Example:

select trunc(sysdate,'day') from dual;

select trunc(sysdate,'month') from dual;

select trunc(sysdate,'year') from dual;

今天周六，取上周日7月29，按月份取本月1日，按年份取今年1月1日。

4、add_months函數：在日期的基礎上加減整月後的日期，Expression:add_months(date,number)其中date為日期，number為整數。

Example:

select add_months(sysdate,3) from dual;

select add_months(sysdate,-3) from dual;

5、months_between函數：返回2個日期之間的月份差數，參數前者大於後者返回正數，前者小於後者返回負數。Expression:months_between(date1,date2);

Example:

select months_between(to_date('2018-8-4','YYYY-MM-DD'),to_date('1949-10-1','YYYY-MM-DD')) from dual;

6、next_day函數：用於返回特定日期後的特定某一天（比如工作日）Expression:next_day(date,str)其中str對應特定某一天，必須與日期語言匹配，如果語言是America，那麼周一為Monday,語言是簡體中文，對應星期一。

Example:

select next_day(sysdate,'星期一') from dual;

7、last_day函數：用於取特定日期的當月最後一天。Expression:last_day(date)

Example:

select last_day(sysdate) from dual;

四、轉換函數：

1、to_char函數：分為2類

（1）日期轉字元：Expression: to_char(date,format,nlsparams)其中nlsparams指定日期的顯示語言，格式為：'NLS_DATE_LANGUAGE=LANGUAGE',也可以不要。

Example:

select to_char(sysdate,'DD-MON-RR','NLS_DATE_LANGUAGE=AMERICAN') from dual;

select to_char(sysdate,'YYYY"年"MM"月"DD"日"') from dual;

註意：如果格式中帶有字元時，須將字元用雙引號引起來。

（2）數值轉字元：Expression:    to_char(number,format)

此處格式常用有：1) 9:顯示數字，忽略前導0；2）0：顯示數字，位數不足用0補齊；3）.:小數點; 4),:逗號 5）$:顯示美元符號; 6)L:本地貨幣符號

Example:

select to_char(1314520,'L99,999,99.99') from dual;

2、to_date函數：用於將字元串轉化為日期類型 Expression:  to_date(str,format,nlsparams)其中nlsparams可不要

Example:

select to_date('1999-02-17','YYYY-MM-DD') from dual;

3、to_number函數：用於將含有數字的字元串轉化為數值類型：Expression:   to_number(str,format)

Example:

select to_number('$1314520','$999999999.99') from dual;

註意：此處format的作用限制了數的範圍，當format裡面的格式如果比字元串中的數字小的話，會報錯,示例如下:

select to_number('$1314520','$99999.99') from dual;

 

五、通用函數：

下麵實例多用到Oracle中scott用戶下的emp表，數據如下：

 

1、nvl函數：解決值為null的問題，當第一個參數為空時，返回第二個參數的值 Expression: nvl(expr1,expr2)

Example:

select nvl(comm,000) from emp;

註意表達式2中值的類型須和原來欄位的類型相同。

2、nvl2函數：如果參數列表中，第一個表達式的值不為空，則返回第二個表達式的值，如果為空，則返回第三個表達式的值。Expression: nvl2(expr1,expr2,expr3)

Example:

select nvl2(comm,comm,000) from emp;

3、nullif函數：用於判斷2個表達式的值是否相等，相等返回null，不等返回第一個表達式的值。Expression: nullif(expr1,expr2);

Example:

select nullif(job,'MANAGER') from emp;

4、coalesce函數：用於返回表達式中第一個not null的結果    Expression:coalesce(expr1,expr2,expr2,expr...)

Example:

select coalesce(comm,null,0) from emp;

5、case表達式：根據取值不同返回不同的結果。 Expression:

case expr
when value1 return result1
when value2 return result2
when value3 return result3
when ......
else result
end 

Example:

select ename,
case job
when 'CLERK' then '辦事員'
when 'SALESMAN' then '銷售'
when 'MANAGER' then '經理'
when 'ANALYST' then '分析員'
else '總裁'
end
from emp

6、decode函數：根據取值不同返回不同的結果。和case表達式的作用相同，Expression: decode(expr,'value1','result1','value2','result2',...,result)

當expr的值為value1是返回結果result1,值為value2時返回結果result2，如果都沒有就返回預設值result;

Example:

select ename,decode(job,'CLERK','辦事員','SALESMAN','銷售','MANAGER','經理','ANALYST','分析員','總裁')from emp;

7、sign函數：判斷當前數字的符號，大於0返回1，等於0返回0，小於0返回-1   Expression:  sign(number)

Example:

select sign(13),sign(0),sign(-14) from dual;

8、ascii函數：用於返回當前字元對應的ascii碼表中的位置   Expression:   ascii(str)

Example:

select ascii('a') from dual;

9、chr函數：和ascii函數剛好相反，返回ascii碼表中的字元   Expression:   chr(number)

Example:

select chr(97) from dual;

 

 

到此為止，oracle的單行函數已全部總結完畢，有需要的童鞋可以進來看看，後續還會總結其他，等待up主更新~

 2018-08-04  17:20:42

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 基於多用戶的Oracle數據泵導入導出數據
    其中：syspwd：sys的登陸密碼；MyOrcl：所創建的資料庫服務名。 其中：dump_name ：所創建的數據泵名字；'E:\DumpFiles' ：創建數據泵存放.dump文件的絕對路徑。 導出數據： 其中：schemas：需要導出的用戶名；dumpfile：預設在數據泵目錄下生成對應的.d ...
  • mysql 開發進階篇系列 15 鎖問題 (總結)
    1. innodb 行鎖是基於索引實現的，如果不通過索引訪問數據，innodb會使用表鎖。 http://www.cnblogs.com/MrHSR/p/9376086.html 2. Innodb 間隙鎖(Next-key)機制，以及innodb使用間隙鎖的原因 http://www.cnblog ...
  • mongodb的安裝與配置
    1.mongodb安裝包下載（mongodb-win32-x86_64-v3.4-latest.zip），解壓到D:\mongodb文件夾下（解壓前創建目錄即可），創建data、logs文件夾【創建mongodb.log文件】 鏈接：https://pan.baidu.com/s/1K7g36CA_ ...
  • mssql sqlserver 關鍵字 GROUPING用法簡介及說明
    轉自: http://www.maomao365.com/?p=6208 摘要: GROUPING 用於區分列是否由 ROLLUP、CUBE 或 GROUPING SETS 聚合而產生的行 如果是原生態的行聚合，則返回0 ，新增的行數據就返回1 grouping 語法簡介 ： GROUPING (< ...
  • sql server 備份與恢復系列二 事務日誌概述
    1.1 日誌文件與數據文件一致性 在上一章備份與恢復里瞭解到事務日誌的重要性，這篇重點來瞭解事務日誌。 事務日誌記錄了資料庫所有的改變，能恢復該資料庫到改變之前的任意狀態。在sql server實例每次啟動時都會去檢查數據文件與日誌文件的一致性。 包括日誌記錄的任何已提交的數據必須體現在數據文件上， ...
  • Sqlserver資料庫中，跨許可權執行語句
    問題來源：最近有同事需要執行批量刪除語句。根據他提供的業務需求，推薦他使用“TRUNCATE TABLE”語句。但使用該語句需要 ALTER許可權，這與執行用戶的角色不符。 解決辦法：使用EXECUTE AS語句修改執行許可權。代碼如下： 通過列印出來的loginame可以看出，執行用戶信息已經被修改。 ...
  • 使用動態SQL創建資料庫
    /*其實我也搞不懂為什麼要用SQL來創建，明明SQL Server有圖形化創建資料庫多省事啊!*/USE master;​DECLARE @sqlstr nvarchar(max)/*定義一個變數*/DECLARE @database_name nvarchar(20) = 'MyDB';/*這裡輸 ...
  • MySQL實現SQL Server排名函數
    最近在MySQL中遇到分組排序查詢時，突然發現MySQL中沒有row_number() over(partition by colname)這樣的分組排序。並且由於MySQL中沒有類似於SQL Server中的row_number()、rank()、dense_rank()等排名函數，所有找到以下實 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類