oracle資料類型及操作

標籤：資料表   時分秒   員工   輸入   alt   3.3   理解   列表   格式轉換   

1. Oracle字串操作

1.1 字串類型

? CHAR和VARCHAR2類型

l CHAR存放定長字元，如果資料存不滿指定長度則用空格補齊，CHAR類型浪費空間換取查詢時間的縮短。

l VARCHAR2存放可變長字元，實際資料有多少長度則佔多少，VARCHAR2節省空間的，查詢時間較CHAR類型要長。

? CHAR和VARCHAR2的儲存編碼

字串在資料庫中儲存的預設單位是位元組，也可顯式指定為字元。

l CHAR(10)，等價於CHAR(10 BYTE)

l 如果指定單位為字元：CHAR(10 CHAR)，20個位元組

l VARCHAR2(10)，等價於VARCHAR2(10 BYTE)

l 指定單位為字元：VARCHAR2(10 CHAR)，20個位元組

每個英文字元佔用一個位元組，每個中文字元按編碼不同，佔用2-4個位元組。

l ZHS16GBK：2個位元組

l UTF-8：2-4個位元組

? CHAR和VARCHAR2的最大長度

l CHAR類型最大長度為2000位元組，即：CHAR(2000)，最多儲存2000個英文字元，1000個漢字(GBK)

l VARCHAR2最大長度為4000位元組，即：VARCHAR2(4000)，最多儲存4000個英文字元，2000個漢字(GBK)

l CHAR如果不指定長度，預設為1個位元組，VARCHAR2必須指定長度

? LONG和CLOB類型

l LONG類型可以認為是VARCHAR2的加長版，用來儲存變長字串，最多達2GB的字串資料，但是LONG類型有諸多限制，不建議使用：

n 每個表只能有一個LONG類型列

n 不能作為主鍵

n 不能建立索引

n 不能出現在查詢條件中等

l CLOB用來儲存定長或變長字串，最多達4GB的字串資料，Oracle建議開發中使用CLOB替代LONG類型。

CREATE TABLE emp( id NUMBER(4), name CHAR(20), detail CLOB );

1.2 字串函數

? CONCAT和”||”

l CONCAT是字串串連函數，文法如下：

CONCAT(char1,char2)

用於返回兩個字串串連後的結果，concat只能有兩參數，即只能串連兩個字串，三個字串串連，需要使用兩個CONCAT。

示範：以“姓名:工資”形式列出

SELECT CONCAT(CONCAT(ename,‘:‘),sal) FROM emp;

l ||串連符號，可以串連多個字串

示範：以“姓名：績效：薪資”的形式列出

SELECT ename||‘:‘||comm||‘:‘||sal FROM emp;

示範：以別名為empinfo的方式列出以上資訊

SELECT ename||‘:‘||comm||‘:‘||sal AS empinfo FROM emp; --AS也可以省略 SELECT ename||‘:‘||comm||‘:‘||sal empinfo FROM emp;

l 在串連時，如果任何一個參數是NULL，相當於串連了一個空格。

? LENGTH

LENGTH(char)用於返回參數字串的長度。如果字元類型是VARCHAR2，返回字元的實際長度，如果是CHAR，長度還包括補齊的空格。

示範：查詢員工姓名的長度

SELECT ename,LENGTH(ename) FROM emp;

? UPPER、LOWER和INITCAP

l UPPER(char)用於將字元轉換為大寫形式

l LOWER(char)用於將字串轉換為小寫形式

l INITCAP(char)用於將字串中每個單詞的首字母大寫，其他字元小寫，單詞之間用空格和非字母字元分隔

l 如果這三個函數的輸入參數是NULL值，仍然返回NULL值

示範：將hello world轉換為大寫、小寫、首字母大寫

SELECT UPPER(‘hello world‘),LOWER(‘HELLO WORLD‘),INITCAP(‘hello wORLD‘) FROM dual;

? TRIM、LTRIM、RTRIM

l TRIM(c2 FROM c1)表示從c1的前後截去c2

l LTRIM(c1[,c2])表示從c1的左邊(Left)截去c2

l RTRIM(c1[,c2])表示從c1的右邊(Right)截去c2

後面兩個函數中，如果沒有參數c2就去除空格，其中最常用的是TRIM，經常用來去掉字串前後的空格。

示範：以上三種用法示範

SELECT TRIM(‘e‘ from ‘elite‘) AS t1,LTRIM(‘elite‘,‘e‘) AS t2,RTRIM(‘elite‘,‘e‘) AS t3 FROM dual;

示範：示範沒有c2參數，去除空格

SELECT TRIM(‘e‘ from ‘elite‘) AS t1,LTRIM(‘ elite ‘) AS t2,RTRIM(‘ elite ‘) AS t3 FROM dual;

? LPAD、RPAD

PAD即補丁，LPAD和RPAD兩個函數都叫做補位函數。

l LPAD(char1,n,char2)左補位函數

l RPAD(char1,n,char2)右補位函數

在字串參數char1的左邊或右邊用char2補足到n位，其中參數char2可重複多次。

示範：在sal列中用$補位位6位

SELECT ename,LPAD(sal,6,‘$‘) AS lp,RPAD(sal,6,‘$‘) AS rp FROM emp;

? SUBSTR

SUBSTR表示在一個字串中截取子字串，文法如下：

SUBSTR(char,[m[,n]])

用於返回char中從m位開始截取n個字元的子串，字串的首位計數從1開始。

l 如果m=0，則從首字母開始，如果m取負數，則從尾部開始

l 如果沒有設定n，或者n的長度超過了char的長度，則取到字串末尾為止

示範1：

SELECT SUBSTR(‘hello world‘,4,5) FROM dual;

示範2：

SELECT SUBSTR(‘hello world‘,0,8) FROM dual;

示範3：

SELECT SUBSTR(‘hello world‘,-4,2) FROM dual;

示範4：

SELECT SUBSTR(‘hello world‘,2) FROM dual;

示範5：

SELECT SUBSTR(‘hello world‘,0,100) FROM dual;

? INSTR

用來返回一個字串中子字串的位置，文法是：

INSTR(char1,char2[,n[,m]])

l 返回子串char2在源字串char1中的位置

l 從n的位置開始搜尋，沒有指定n，從第1個字元開始搜尋

l m用於指定子串的第m次出現次數，如果不指定則取值1

l 如果在char1中沒有找到子串char2，返回0

示範1：

SELECT INSTR(‘hello world‘,‘o‘) FROM dual;

示範2：

SELECT INSTR(‘hello world‘,‘o‘,6) FROM dual;

示範3：

SELECT INSTR(‘hello world‘,‘o‘,9) FROM dual;

示範4：

SELECT INSTR(‘hello world‘,‘o‘,-1) FROM dual;

示範5：

SELECT INSTR(‘hello world‘,‘o‘,6,1) FROM dual;

2. Oracle數值操作

2.1 數實值型別

? 整數

NUMBER(p)表示整數，p表示數位總位元，取值為1-38。

示範：學生id設定為4位整數

CREATE TABLE stedunt(id NUMBER(4));

? 浮點數

NUMBER(p,s)表示浮點數，含義如下：

l p：NUMBER可以儲存的最大數字長度(不包括左右兩邊的0)

l s：表示小數位最大的數字長度(包括左側的0)

l 如果指定了s但是沒有指定p，則p預設為38

如：id NUMBER(*,s)

示範：設定員工薪資為6位元，小數位為2位，最大取值即9999.99

CREATE TABLE empx(sal NUMBER(6,2));

NUMBER的變種資料類型：內部實現是NUMBER，可以將其理解為NUMBER的別名，目的是多種資料庫及程式設計語言相容。

l NUMBER(p,s)：完全映射至NUMBER(p,s)

l DECIMAL(p,s)或DEC(p,s)：完全映射至NUMBER(p,s)

l INTEGER或INT：完全映射至NUMBER(38)類型

l SMALLINT：完全映射至NUMBER(38)類型

l FLOAT(b)：映射至NUMBER類型

l DOUBLE PRECISION：映射至NUMBER類型

l REAL：映射至NUMBER類型

2.2 數值函數

? ROUND

ROUND用來四捨五入，文法如下：

ROUND(n,[,m])

用於將參數n按照m的數字要求四捨五入。

l 參數中的n可以是任一數字，指要被處理的數字

l m必須是整數

l m取整數則四捨五入到小數點後m位

l m取0則四捨五入到整數位

l m取負數則四捨五入到小數點前m位

l m預設值為0

示範：

SELECT ROUND(45.678,2),ROUND(45.678,0),ROUND(45.678,-1) FROM dual;

? TRUNC

TRUNC表示截取，文法如下：

TRUNC(n[,m])

n，m的含義同ROUND。

示範：

SELECT TRUNC(45.678,2),TRUNC(45.678,0),TRUNC(45.678,-1),TRUNC(45.678) FROM dual;

? MOD

取餘，文法如下：

MOD(m,n)

用於返回m除以n後的餘數，如果n為0則直接返回m。

示範：薪水值按1000取餘

SELECT ename,MOD(sal,1000) FROM emp;

? CEIL和FLOOR

l CEIL(n)：表示取大於或等於n的最小整數值

FLOOR(n)：表示取小於或等於n的最大整數值

示範：

SELECT CEIL(45.678),FLOOR(45.678),CEIL(45), FLOOR(45)FROM dual;

3. Oracle日期操作

3.1 DATE

DATE和TIMESTAMP是Oracle中最常用的日期類型。DATE用來儲存日期和時間，表示範圍是從公元前4712年1月1日至公元9999年12月31日。

DATE類型在資料庫中的實際儲存固定為7個位元組，格式分別如下：

l 第1位元組：世紀+100

l 第2位元組：年

l 第3位元組：月

l 第4位元組：天

l 第5位元組：小時+1

l 第6位元組：分+1

l 第7位元組：秒+1

? TIMESTAMP

TIMESTAMP表示時間戳記，與DATE的區別是不僅可以儲存日期和時間，還能儲存小數秒，可指定0-9位，預設6位，最高精度可以到ns(納秒)層級。

資料庫內部用7或者11個位元組儲存，精度為0時，用7位元組儲存，與DATE功能相同，精度大於0則用11位元組儲存。格式如下：

l 第1-7位元組：與DATE相同

l 第8-11位元組：納秒，採用4個位元組儲存，內部運算類型為整型

示範：

CREATE TABLE test( c1 DATE, c2 TIMESTAMP(9) );

3.2 日期關鍵字

? SYSDATE

SYSDATE本質是一個Oracle的內建函式，用來返回當前的系統時間，精確到秒，預設顯示格式是DD-MON-RR，只有年月日並不顯示時間。

示範：

SELECT SYSDATE FROM dual;

如果資料庫是英文環境，將顯示“27-JUN-17”的形式，如果是中文環境，將顯示“27-6月-17”的形式。

如果想顯示時分秒，需要將格式轉換一下，使用TO_CHAR轉換。

示範：

SELECT TO_CHAR(SYSDATE,‘yyyy-mm-dd day hh24:mi:ss‘) FROM dual;

? SYSTIMESTAMP

SYSTIMESTAMP也是Oracle的內部日期函數，返回當前系統日期和時間，精確到毫秒。

示範：

SELECT SYSTIMESTAMP,TO_CHAR(SYSTIMESTAMP,‘SSSS.FF‘) FROM dual;

3.3 日期轉換函式

日期資料有時需要和字串資料相互轉換，需要用到日期轉換函式，包括TO_CHAR和TO_DATE。

? TO_DATE

TO_DATE的功能是將字串按照定製格式轉換為日期類型，文法如下：

TO_DATE(char[,fmt[,nlsparams]])

其中：char是要轉換的字串，fmt是轉換格式，nlsparams是指定日期語言。常用的格式如下：

格式	含義
YY	2位元字的年份
YYYY	4位元字的年份
MM	2位元字的月份
MON	簡拼的月份
MONTH	全拼的月份
DD	2位元字的天
DY	周幾的縮寫
DAY	周幾的全拼
HH24	24小時制的小時
HH12	12小時制的小時
MI	顯示分鐘
SS	顯示秒

示範：查詢1982年以後入職的員工

SELECT ename,hiredate FROM emp WHERE hiredate>TO_DATE(‘1982-01-01‘,‘YYYY-MM-DD‘);

? TO_CHAR

TO_CHAR的作用是將其它類型(日期，數值)的資料轉換為字元類型，主要應用在日期類型上，文法如下：

TO_CHAR(date[,fmt[,nlsparams]])

其中fmt是格式，將日期類型資料date按照fmt格式輸出字串，nlsparams用於指定日期語言。

示範：將員工的hiredate轉換為年月日的形式顯示

SELECT ename,hiredate,TO_CHAR(hiredate,‘YYYY"年"MM"月"DD"日"‘) FROM emp;

3.4 日期常用函數

? LAST_DAY

文法：

LAST_DAY(date)

返回日期date所在月的最後一天，一般是按照自然月計算某些商務邏輯，或者安排月末周期性活動時用到。

示範：

SELECT LAST_DAY(SYSDATE) FROM dual; --查詢當月的最後一天 SELECT LAST_DAY(‘20-2月-2000‘) FROM dual; --查詢2000年2月最後一天

? ADD_MONTHS

文法：

ADD_MONTHS(date,i)

返回日期date加上i個月後的日期值。

l 參數i可以是任何數字，大部分時候取正值整數

l 如果i是小數，將會截取整數後在參與運算

l 如果i是負數，則擷取減去i月後的日期值

示範：計算入職員工20周年紀念日

SELECT ename,ADD_MONTHS(hiredate,12*20) FROM emp;

? MONTHS_BETWEEN

文法：

MONTHS_BETWEEN(date1,date2)

用於計算date1和date2兩個日期之間間隔了多少個月，實際運算是date1-date2，如果date2時間比date1晚，則會得到負值。

除非兩個日期間隔是整數月，否則會得到帶小數位的結果。

示範1：date1比date2大整數月

SELECT MONTHS_BETWEEN(‘09-9月-2008‘,‘09-10月-2007‘) FROM dual;

示範2：date1比date2小整數月

SELECT MONTHS_BETWEEN(‘09-9月-2006‘,‘09-10月-2007‘) FROM dual;

示範3：date1比date2小非整數月

SELECT MONTHS_BETWEEN(‘21-9月-2006‘,‘09-10月-2007‘) FROM dual;

? NEXT_DAY

文法：

NEXT_DAY(date,char)

返回date日期資料的下一個周幾，周幾是由參數char來決定的。在中文環境下，直接使用“星期三”這種格式，英文環境下，需要使用“WEDNESDAY”這種格式。為避免麻煩，可以直接使用1-7來表示周日-周六。

示範：17年6月27號的下一個周三是哪天

SELECT NEXT_DAY(‘27-6月-17‘,4) FROM dual;

? LEAST、GREATEST

文法：

LEAST(expr1[,expr2[,expr3]]…) --返回最小值 GREATEST(expr1[,expr2[,expr2]]…) --返回最大值

兩個函數都可以有多個數值，但參數類型必須一致，返回結果是參數列表中最大或最小的值，在比較之前，在參數列表中第二個以後的參數會被隱含的轉換為第一個參數的資料類型，所以如果可以轉換，則繼續比較，如果不能轉換將會報錯。

示範：

SELECT LEAST(SYSDATE,‘10-10月-09‘),GREATEST(SYSDATE,‘10-10月-09‘) FROM dual;

? EXTRACT

文法：

EXTRACT(date FROM datetime)

從參數datetime中提取參數date指定的資料，比如提取年、月、日。

示範：取出年

SELECT ename,EXTRACT(YEAR FROM hiredate) FROM emp;

4. 空值操作

NULL是資料庫裡的重要概念，即空值。當表中的某些欄位值，資料未知或暫時不存在，取值NULL。

Java中簡單的資料類型是不能取值NULL的，在資料庫中，任何資料類型均可取值NULL。

? 插入NULL值

在資料表中，如果要插入NULL值，可以採用顯示指定NULL值方式，或者不插入某個欄位值，即隱式表示NULL值。

示範：

CREATE TABLE student( id NUMBER(4), name CHAR(20), gender CHAR(1) ); INSERT INTO student VALUES(1000,‘李莫愁‘,‘F‘); INSERT INTO student VALUES(1001,‘林平之‘,NULL); --顯示插入NULL值 INSERT INTO student (id,name) VALUES(1002,‘東方不敗‘); --隱式插入NULL值

? 更新成NULL值

把資料的某個欄位更新為NULL值，和更新為其他資料方法相同，需要和WHERE配合使用，不然全表都會被更新為NULL值。

示範：

UPDATE student SET name=NULL; --全表都被更新

注意這種更新只有在此列沒有非空約束的情況下才可操作。如果該列有非空約束，則無法更新為NULL值。

? NULL條件查詢

在條件查詢中，因為NULL不等於任何值，所以不能用“=“，必須用”IS NULL“或”IS NOT NULL”來判斷。

示範：

SELECT name,gender FROM student WHERE gender IS NOT NULL; SELECT name,gender FROM student WHERE gender IS NULL;

? 非空約束

非空(NOT NULL)約束是約束條件的一種，用於確保資料表中某個欄位值不為空白。

因為在預設情況下，任何資料的列都允許有空值，但系統商務邏輯可能會要求某些列不能取空值，這時需要在建表時指定該列不允許為空白。

一旦某個欄位被設定了非空條件約束，這個欄位必須存在有效值。即：當執行插入資料操作時，必須提供這個列的資料，當執行更新操作時，不能給這個列的值設定為NULL。

示範：

ALTER TABLE student MODIFY(NAME NOT NULL);

4.1 控制函數

? NVL

文法：

NVL(expr1,expr2)

將NULL轉變為非NULL。如果expr1為NULL，則取值expr2，expr2是非空值。

其中expr1和expr2可以是任何資料類型，但兩個參數的資料類型必須是一致的。

任何數與NULL做計算，最後都是NULL。如果計算員工月收入，如果comm為NULL 的話則收入為空白，不符合邏輯，所以先將是NULL值的comm轉換為0，再做相加。

示範：計算員工月收入，薪資+績效

SELECT ename,sal,comm,sal+NVL(comm,0) 月收入 FROM emp;

? NVL2

文法：

NVL2(expr1,expr2,expr3)

和NVL函數功能類似，都是將NULL轉變為非空值。NVL2用來判斷expr1是否為NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3。

示範：查詢員工月收入

SELECT ename,sal,comm,NVL2(comm,sal+comm,sal) AS 月收入 FROM emp;

oracle資料類型及操作