Oracle 系統函式的使用集合

字符函式

Ltrim

函式形式：ltrim(string)
函式說明：去掉指定字串string左側空格
舉例：select ltrim(’ aaa bbb ccc ') from dual;
運行結果：
aaa bbb ccc（左側空格已經被洗掉）

進階用法：

函式形式：ltrim(string1,string2)
函式說明：從字串string1左側開始去除與string2字符集合中單個字符匹配的字符，直到在string1中遇上某個字符，該字符不在string2字符集合中，
舉例：

SQL> select ltrim(‘abadababa’,‘ab’) from dual;

dababa

Rtrim

函式形式：rtrim(string)
函式說明：去掉指定字串string右側空格
舉例：select rtrim(’ aaa bbb ccc ') from dual;
運行結果：
aaa bbb ccc（右側空格已經被洗掉）

進階用法：

函式形式：rtrim(string1,string2)
函式說明：從字串string1右側開始去除與string2字符集合中單個字符匹配的字符，直到在string1中遇上某個字符，該字符不在string2字符集合中，
這段話讀起來有點拗口，但是對于真正理解這兩個函式的用法至關重要，oracle中的這兩個函式中的第二個引數，可以是多個字符組成的“字符集合”，為什么說是“字符集合”而不是“字串”（雖然嚴格意義上講并沒有字符集合這個概念），因為在以string2為基礎對string1做字符去除的時候，string2并不是作為一個字串那樣的整體來與string1匹配的，而是單個單個字符的匹配，比如string1='abccba'，string2='abc'，如果string2作為一個整體來匹配，顯然不管是用ltrim還是rtrim函式，對string1操作之后回傳的結果應該是'cba'，但實際上不是，回傳的是一個空串！

舉例：

SQL> select rtrim(‘abadababa’,‘ab’) from dual;

abad

trim

函式形式：trim(string)
函式說明：去掉指定字串string兩側空格
舉例：select trim(’ aaa bbb ccc ') from dual;
運行結果：
aaa bbb ccc（兩側空格已經被洗掉）

進階用法：

函式形式：trim( leading | trailing | both string1 FROM string2)
函式說明：從string2中去除左側 | 右側 | 左側兩側（默認是both，即左右側都去掉）的string1字符，注意，string1只能是單個字符，
舉例：

SQL> select trim(leading ‘abc’ from ’ a aaaa bb cc aa caa cca a ') from dual;
select trim(leading ‘abc’ from ’ a aaaa bb cc aa caa cca a ') from dual
*
ERROR at line 1:
ORA-30001: trim set should have only one character

SQL> select trim(leading ’ ’ from ’ a aaaa bb cc aa caa cca a ') from dual;

a aaaa bb cc aa caa cca a

SQL> select trim(trailing ’ ’ from ’ a aaaa bb cc aa caa cca a ') from dual;

TRIM(TRAILING’'FROM’AAAAABBCCAA

a aaaa bb cc aa caa cca a

SQL> select trim(bith ’ ’ from ’ a aaaa bb cc aa caa cca a ') from dual;
select trim(bith ’ ’ from ’ a aaaa bb cc aa caa cca a ') from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> select trim(both ’ ’ from ’ a aaaa bb cc aa caa cca a ') from dual;

TRIM(BOTH’'FROM’AAAAABBCCA

a aaaa bb cc aa caa cca a

oracle中實作截取字串：substr

函式形式：substr(string, start_position, [length])
函式說明：string是元字串，start_position為開始位置，length是可選項，表示子字串的位數，
舉例：
substr(‘ABCDEFG’, 0); – 回傳結果是：ABCDEFG，從0位開始截取后面所有

substr(‘ABCDEFG’, 2); – 回傳結果是：BCDEFG，從2位開始截取后面所有

substr(‘ABCDEFG’, 0, 3); – 回傳結果是：ABC，從0位開始往后截取3個字符長度

substr(‘ABCDEFG’, 0, 100); – 回傳結果是：ABCDEFG，雖然100超出了元字串長度，但是系統會按元字串最大數量回傳，不會影響回傳結果

substr(‘ABCDEFG’, -3); – 回傳結果是：EFG，如果是負數，則從尾部往前數，截取-3位置往后的所有字串

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-7EXjOyp2-1617298166775)(H:\Orcale作業提交\第五次作業\images\Snipaste_2021-04-01_21-32-32.png)]

查找字串位置：instr

函式形式：instr(string, subString, [start_position, [nth_appearance]])
函式說明：其中，string是元字串；subString是要查找的子字串；start_position是要查找的開始位置，為可選項（默認為1），注意在這里字串索引從1開始，如果此引數為正，則從左到右檢索，如果此引數為負，則從右到左檢索；nth_appearance是元字串中第幾次出現的子字串，此引數可選，預設默認為1，如果是負數則系統報錯，
舉例：
instr(‘ABCDABCDAEF’, ‘AB’); – 回傳結果是：1，因為instr字串索引從1開始，所以是1不是0

instr(‘ABCDABCDAEF’, ‘DA’, 1, 2); – 回傳結果是：8，回傳第二次出現’DA’的位置

instr(‘A BCDABCDAEF’, ‘DA’, 1, 2)； – 回傳結果是：9，由于我在元字串中加了一個空格，空格仍然算一個字符

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-KdO0QtOC-1617298166779)(H:\Orcale作業提交\第五次作業\images\Snipaste_2021-04-01_21-38-36.png)]

替換字串：replace

函式形式：replace(str1, str2, str3)
函式說明：在str1中查找str2，凡是出現str2的地方，都替換成str3，
舉例：
replace(‘ABCDEFG’, ‘CDE’, ‘cde’); – 回傳結果是：ABcdeFG

replace(‘ABCDEFG’, ‘CDE’, ‘cde’); – 回傳結果是：ABcdeFG

replace(‘ABCDEFG’, ‘CDE’, ‘’); – 回傳結果是：ABFG，CDE被替換成空字符

replace(‘ABCDEFG’, ‘CDE’); – 回傳結果是：ABFG，當不存在第三個引數時，CDE直接被刪掉
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-LPAm3IZZ-1617298166782)(H:\Orcale作業提交\第五次作業\images\Snipaste_2021-04-01_21-42-54.png)]

日期函式

sysdate

函式形式：select sysdate from dual;
函式說明：sysdate函式可以得到目前系統的時間
用法舉例：
select sysdate from dual; 回傳系統時間
select sysdate,add_months(sysdate,12) from dual; --加1年
select sysdate,add_months(sysdate,1) from dual; --加1月
select to_char(sysdate,'yyyy/mm/dd/day') from dual; to_char函式的另一種用法

select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期

select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小時
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;–加1分鐘
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-FHpOygGu-1617298166785)(H:\Orcale作業提交\第五次作業\images\Snipaste_2021-04-01_21-48-34.png)]

Oracle中的next_day(date，char)

函式樣式：next_day(date，char)
函式說明：它是指指定日期date之后的下一個char天后的日期
當char是星期時：它是指下一周星期幾（char）所指示的日期
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-mXaoXhvN-1617298166787)(H:\Orcale作業提交\第五次作業\images\Snipaste_2021-04-01_21-57-20.png)]

當char是數字時：他是指從這一周末開始的往后char天后的日期，周末算第一天

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-Rz2LnAKX-1617298166788)(H:\Orcale作業提交\第五次作業\images\Snipaste_2021-04-01_21-58-33.png)]

轉換函式

to_char

• 函式形式：to_char (number, '格式')
  函式說明：使用to_char函式處理數字
  to_char(salary, $99,999.99);
• 函式形式：to_char (date, '格式')
  函式說明：使用to_char函式處理日期
  to_char(date, $99,999.99);
• 數字格式格式：
  9 代表一個數字
  0 強制顯示0
  $ 放置一個$符
  L 放置一個浮動本地貨幣符
  . 顯示小數點
  , 顯示千位指示符

to_date

函式形式：to_date(char,'格式')
函式說明：to_data將字符轉換為格式的日期日期格式
格式控制描述:
YYYY、YYY、YY 分別代表4位、3位、2位的數字年
YEAR 年的拼寫
MM 數字月
MONTH 月的全拼
MON 月的縮寫
DD 數字日
DAY 星期的全拼
DY 星期的縮寫
AM 表示上午或者下午
HH24、HH12 12小時制或24小時制
MI 分鐘
SS 秒鐘
SP 數字的拼寫
TH 數字的序數詞

to_number

函式形式：to_number(char,‘格式’)；
使用to_number函式將字符轉換為數字

更多的格式控制

統計函式

函式形式：select avg|count|max|min|sum(column) from tablename;
函式說明：column是列名稱，tablename是表格名稱

• avg : 求某一列的平均值
• count：求表tablename的行數
• max：求column列中最大的數值
• min：求column列中最小的數值
• sum：求column列數值的和

舉例：

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-3fnCHn69-1617298166789)(H:\Orcale作業提交\第五次作業\images\Snipaste_2021-04-02_00-59-01.png)]

其他函式

decode

函式形式：decode(條件,值1,翻譯值1,值2,翻譯值2,...,預設值)

函式說明： 該函式與程式中的 If…else if…else 意義一樣,
select decode(列名，值1，運算式1，值2，運算式2,......,值n,運算式n) from table
列名就是table中某一列的名稱，在這里將充當條件，即：如果某一行的該列的欄位值等于值1則回傳運算式1的值，等于值2則回傳運算式2的值，

nal

函式格式：nal( string1, replace_with)

函式功能：如果string1為NULL，則NVL函式回傳replace_with的值，否則回傳string1的值，如果兩個引數都為NULL ，則回傳NULL，

user

• 用戶創建
  必須是sysdba的身份才可以創建 create user 用戶名 identified by 密碼;
• 查看當前用戶
  show user
• 改用戶密碼
  alter user 用戶名 identified by 密碼;
• 用新用戶連接資料庫
  必須給新用戶授權能夠連接資料庫會話的權限
grant create session to 用戶名;
• 洗掉用戶
  先必須用sysdba身份登錄資料庫
drop user 用戶名;
  create user 用戶名 identified by 密碼;`
• 查看當前用戶
  show user
• 改用戶密碼
  alter user 用戶名 identified by 密碼;
• 用新用戶連接資料庫
  必須給新用戶授權能夠連接資料庫會話的權限
grant create session to 用戶名;
• 洗掉用戶
  先必須用sysdba身份登錄資料庫
drop user 用戶名;