oracle常用資料庫內建函數

標籤：io   使用   ar   for   檔案   資料   art   sp   on   

　函數　　傳回值　　　　　　　　　　　　範例　　　　　　　　　　　顯示

CEIL(n) 大於或等於數值n的最小整數　　SELECT CEIL(10.6) FROM TABLE_NAME; 11

FLOOR(n) 小於等於數值n的最大整數　 SELECT FLOOR (10.6) FROM TABLE_NAME; 10

MOD(m,n) m除以n的餘數,若n=0,則返回m SELECT MOD (7,5) FROM TABLE_NAME l; 2

POWER(m,n) m的n次方　　　　　　　　 SELECT POWER (3,2) FROM TABLE_NAME; 9

ROUND(n,m) 將n四捨五入,保留小數點後m位　　SELECT ROUND (1234.5678,2) FROM TABLE_NAME; 1234.57

SIGN(n) 若n=0,則返回0,否則,n>0,則返回1,n<0,則返回-1 SELECT SIGN (12) FROM TABLE_NAME; 1

SQRT(n) n的平方根　　　　　　　　　SELECT SQRT (25) FROM TABLE_NAME; 5

2、常用字元函數

INITICAP(char) 把每個字串的第一個字元換成大寫　　SELECT INITICAP (‘mr.ecop‘) FROM TABLE_NAME; Mr.Ecop

LOWER(char) 整個字串換成小寫　　　　　　　　　SELECT LOWER (‘MR.ecop‘) FROM TABLE_NAME; mr.ecop

REPLACE(char,str1,str2) 字串中所有str1換成str2 SELECT REPLACE (‘Scott‘,‘s‘,‘Boy‘) FROM TABLE_NAME; Boycott

SUBSTR(char,m,n) 取出從m字元開始的n個字元的子串　　SELECT SUBSTR (‘ABCDEF‘,2,2) FROM TABLE_NAME;  CD

LENGTH(char) 求字串的長度　　　　SELECT LENGTH (‘ACD‘) FROM TABLE_NAME; 3

|| 共置運算子　　　 SELECT ‘ABCD‘||‘EFGH‘ FROM TABLE_NAME;  ABCDEFGH

3、日期型函數

 

SYSDATE 當前日期和時間 SELECT SYSDATE FROM TABLE_NAME;

LAST_DAY 　本月最後一天 SELECT LAST_DAY(SYSDATE) FROM TABLE_NAME;

ADD_MONTHS(d,n)　當前日期d後推n個月 SELECT ADD_MONTHS (SYSDATE,2) f FROM TABLE_NAME;

MONTHS_BETWEEN(d,n) 日期d和n相差月數 SELECT MONTHS_BETWEEN (SYSDATE,TO_DATE(‘20020812‘,‘YYYYMMDD‘)) FROM TABLE_NAME;

NEXT_DAY(d,day) d後第一周指定day的日期 SELECT NEXT_DAY (SYSDATE,‘Monday‘) FROM TABLE_NAME;

day 格式　　有　　‘Monday‘ 星期一　　‘Tuesday‘ 星期二

‘wednesday‘ 　星期三   ‘Thursday‘ 星期四    ‘Friday‘ 星期五

‘Saturday‘ 星期六   ‘Sunday‘ 星期日

4、特殊格式的日期型函數

Y或YY或YYY 年的最後一位，兩位，三位 SELECTTO_CHAR(SYSDATE,‘YYY‘) FROM TABLE_NAME;

Q 季度,1-3月為第一季度　　　　SELECT TO_CHAR(SYSDATE,‘Q‘) FROM TABLE_NAME;

MM 　月份數　　　　　　　　　　　SELECT TO_CHAR(SYSDATE,‘MM‘) FROM TABLE_NAME;

RM 月份的羅馬錶示 SELECT TO_CHAR(SYSDATE,‘RM‘) FROM TABLE_NAME; IV

month 用9個字元表示的月份名 SELECT TO_CHAR(SYSDATE,‘month‘) FROM TABLE_NAME;

ww 當年第幾周　　　　　　　　　SELECT TO_CHAR(SYSDATE,‘ww‘) FROM TABLE_NAME l;

w 本月第幾周　　　　　　　　　SELECT TO_CHAR(SYSDATE,‘w‘) FROM TABLE_NAME;

DDD 當年第幾天,一月一日為001 ,二月一日032 SELECT TO_CHAR(SYSDATE,‘DDD‘) FROM TABLE_NAME;

DD 當月第幾天 SELECT TO_CHAR(SYSDATE,‘DD‘) FROM TABLE_NAME;

D 周內第幾天 SELECT TO_CHAR(SYSDATE,‘D‘) FROM TABLE_NAME; 如　sunday

DY 周內第幾天縮寫　　　　　　　SELECT TO_CHAR(SYSDATE,‘DY‘) FROM TABLE_NAME; 如　sun

hh12 12小時制小時數　　　　　　　SELECT TO_CHAR(SYSDATE,‘hh12‘) FROM TABLE_NAME;

hh24 24小時制小時數　　　　　　　SELECT TO_CHAR(SYSDATE,‘hh24‘) FROM TABLE_NAME;

Mi 分鐘數　　　　　　　　　　　 SELECT TO_CHAR(SYSDATE,‘Mi‘) FROM TABLE_NAME;

ss 秒數　　　　　　　　　　　　 SELECT TO_CHAR(SYSDATE,‘ss‘) FROM TABLE_NAME;

SELECT TO_CHAR(SYSDATE,‘YYYY-MM-DD HH:24:mi:ss‘) FROM TABLE_NAME;

TO_NUMBER() 將合法的數字字串 SELECT TO_NUMBER (‘88877‘) FROM TABLE_NAME; 88877

TO_CHAR() 將數字轉換為字串　 SELECT TO_CHAR(88877) FROM TABLE_NAME; ‘88877‘

　

SET SERVEROUPT ON;

DBMS_OUTPUT.PUT_LINE(‘helloworld‘)

　

SET HEADINGOFF 由於正在建立資料檔案，不需要表頭

SET PAGESIZE0 不需要分頁

SET LINESIZE80 設定行的最大尺寸

SET ECHOOFF 告訴sqlplus 在執行語句時，不要回顯語句

SET FEEDBACKOFF 禁止sqlplus 顯示有多少滿足查詢的行被檢索到

COL SALESFORMAT 999,999,999

APPEND 添加文本到當前行尾

CHANGE/OLD/NEW/在當前行用新的文本代替舊的文本

　CHANGE/TEXT 從當前行刪除ｗｅｎｂ

　DEL 刪除當前行

　INPUT TEXT 　在當前行之後添加一行

　LIST 顯示緩衝區中的所有行

　LIST n 顯示緩衝區中的第n行

LIST mn 顯示m到n

5、字元函數

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

字元函數主要用於修改字元列。這些函數接受字元輸入，返回字元或數字值。Oracle 提供的一些字元函數如下。

1. CONCAT(char1, char2)

返回串連“char2”的“char1”。

樣本  SELECT CONCAT(CONCAT( COLUMN_NAME, ‘ is a ‘), job) FROM TABLE_NAME;

 

2.INITCAP(string)

將“string”的字元轉成大寫。

SELECT INITCAP( COLUMN_NAME) from TABLE_NAME;

3.LOWER (string)

將“string”轉成小寫。

SELECT LOWER( COLUMN_NAME) from TABLE_NAME;

4.LPAD(char1,n [,char2])

返回“char1”，左起由“char2”中的字元補充到“n”個字元長。如果“char1”比“n”長，則函數返回“char1”的前“n”個字元。

SELECT LPAD( COLUMN_NAME,15,‘*‘) FROM TABLE_NAME;

5.LTRIM(string,trim_set)

從左邊刪除字元，此處“string”是資料庫的列，或者是字面字串，而“trim_set”是我們要去掉的字元的集合。

SELECT LTRIM(‘abcdab‘,‘a‘) FROM TABLE_NAME;

6.REPLACE(string, if, then)

用 0 或其他字元代替字串中的字元。“if”是字元或字串，對於每個出現在“string”中的“if”，都用“then”的內容代替。

SELECT REPLACE(‘JACK and JUE‘,‘J‘,‘BL‘) FROMTABLE_NAME;

7.RPAD(char1, n [,char2])

返回“char1”，右側用“char2”中的字元補充到“n”個字元長。如果“char1”比“n”長，則函數返回“char1”的前“n”個字元。

SELECT RPAD( COLUMN_NAME,15,‘*‘) FROM TABLE_NAME;

8.RTRIM(string,trim_set)

從右側刪除字元，此處“string”是資料庫的列，或者是字面字串，而“trim_set”是我們要去掉的字元的集合。

SELECT RTRIM(‘abcdef‘, ‘f‘) FROM TABLE_NAME;

9.SOUNDEX(char)

返回包含“char”的表意字元的字串。它允許比較英語中拼字不同而發音類似的字。

SELECT  COLUMN_NAME FROM TABLE_NAME WHERESOUNDEX( COLUMN_NAME) = SoUNDEX(‘SMYTHE‘);

 

10.SUBSTR(string, start [,count])

返回“string”中截取的一部分。該命令截取“string”的一個子集，從“start”位置開始，持續“count”個字元。如果我們不指定“count”，則從“start”開始截取到“string”的尾部。

SELECT SUBSTR(‘ABCDEFGIJKLM‘,3,4) FROMTABLE_NAME;

 

11.TRANSLATE(string, if, then)

“if”中字元的位置，並檢查“then”的相同位置，然後用該位置的字元替換“string”中的字元。

SELECT TRANSLATE( COLUMN_NAME,‘AEIOU‘, ‘XXXXX‘) FROM TABLE_NAME;

12.UPPER(string)

返回大寫的“string”。

SELECT UPPER(‘aptech computer education‘) FROMTABLE_NAME;

13.ASCII(string)

該命令是“American Standard Code forInformation Interchange”的縮寫。它是使用數字表示可列印字元的基本規則。該函數返回“string”中第一個（最左邊）字元的ASCII 值。

SELECT ASCII(‘APTECH‘) FROM TABLE_NAME;

14.INSTR (string, set[, start[, occurrence] ] )

該命令“string”中從“start”位置開始尋找字元集合的位置，再尋找“set”出現的第一次、第二次等等的“occurrence”（次數）。“start”的值也可以是負數，代表從字串結尾開始向反方向搜尋。該函數也用於數字和日期資料類型。

SELECT INSTR(‘aptech is aptech‘,‘ap‘,1,2) FROMTABLE_NAME;

 

15.LENGTH(string)

返回“string”的長度值。

SELECT COLUMN_NAME, LENGTH( COLUMN_NAME) FROM TABLE_NAME;

 

oracle常用資料庫內建函數