PLSQL常用函數(shù)
1)處理字符的函數(shù)
|| 或 CONCAT---并置運算符。
格式∶CONCAT(STRING1, STRING2)
例：’ABC’|| ’DE’=’ABCDE’
CONCAT(‘ABC’,’DE’) =’ABCDE’
ASCII---返回字符的ASCII碼。
例：ASCII(‘A’) = 65
CHR---返回指定ASCII碼的字符。
例：CHR(65) = ‘A’
INSTR---搜索子串位置
格式∶INSTR(STRING , SET[ , 開始位置[ , 出現(xiàn)次數(shù)]])
例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
INITCAP---將字符串每個單詞首字母均變?yōu)榇髮?BR> 例: INITCAP(‘this is a test’)=’ This Is A Test’’
LENGTH----計算串長
格式∶ LENGTH(string)
RPAD,LPAD---右填充、左填充。默認為填充空格。
格式: RPAD(字符串 , 字符個數(shù) , 填充字符)
例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
LTRIM,RTRIM-----左右截斷。默認為刪除空格。
格式∶ LTRIM(STRING[,’SET’])
例∶ LTRIM(‘***tes*t***’ , ’*’)=’ tes*t***’
LOWER----將字符串轉換為小寫
格式∶LOWER(string)
UPPER---將字符串轉換為大寫
格式∶UPPER(string)
SUBSTR----提取子串。START為正數(shù)時從左開始、為負數(shù)時從右開始
格式∶ SUBSTR(STRING , START [ , COUNT])
例∶ SUBSTR(‘WORDSTAR’ , 2 , 3)=’ ORD’
REPLACE---搜索指定字符串并替換
格式∶REPLACE(string , substring , replace_string)
例∶ REPLACE(‘this is a test’ , ‘this’ , ‘that an’)=’that an is a test’
TRIM---刪除字符串前綴或尾隨字符
格式∶TRIM( [LEADING | TRAILING ｜BOTH] [ trimchar FROM ] string)
LEADING---刪除前綴字符
TRAILING---刪除后綴字符
BOTH---前后綴字符均刪除(默認方式)
Trimchar---指定刪除的字符
注：INSTR,LENGTH,SUBSTR加B時針對字節(jié)．
2)處理數(shù)字的函數(shù)
LEAST---返回參數(shù)列表中的最小值。返回參數(shù)類型以第一參數(shù)為準
格式∶LEAST(value,value,value,value,value,….)value為數(shù)字或字符串
例∶ LEAST(1,2,5,-10,9)= -10
SIGN---返回參數(shù)的符號位,負數(shù)--- -1,0----0,正數(shù)---1
格式∶SIGN(value)
CELL---返回大于等于特定值的最小整數(shù)
格式∶CELL(value)
例∶ CELL(-10,9)= -10
3）處理日期
SYSDATE---系統(tǒng)時間。精確至秒
ADD_MONTH—加減月份。numvalue為負數(shù)時減去相應月份
格式: ADD_MONTH (data1,numvalue)
MONTHS_BETWEEN---返回兩日期之間的月數(shù),當data1
格式: MONTHS_BETWEEN(data1,data2)
LAST_DAY---返回指定日期的最后一天。
格式∶LAST_DAY(date)
TRUNC---將日期按照format格式截短，缺省為DD(不是四舍五入)
格式：TRUNC（date, format）
例∶ TRUNC (to_date(‘2002-11-07’,’yyyy-mm-dd’),’MM’)= 2002-11-01
4）分組函數(shù)
返回基于多個行的單一結果．
常用函數(shù)：
AVG---求平均值
COUNT---返回查詢的行數(shù)
MAX---返回查詢列的最大值
MIN---返回查詢列的最小值
SUM---返回查詢列的總和
MAX, MIN常與GROUP BY配套使用
5）轉換函數(shù)
TO_DATE---轉換字符串為日期型
格式∶ TO_DATE(STRING[,’FORMAT’])
TO_CHAR---轉換日期型或數(shù)值型為字符串。最重要的函數(shù)之一．其FORMAT格式多種多樣
格式∶TO_CHAR(DATE [,’FORMAT’])
FORMAT---具體格式參考ORACLE8i DBA 寶典P835數(shù)字格式元素
P836 日期格式元素
TO_NUMBER---轉換字符串為數(shù)字
格式∶TO_NUMBER(string [ , format])
FORMAT---具體格式參考ORACLE8i DBA 寶典P835數(shù)字格式元素
6)其他
DECODE---IF語句的另一形式。將輸入數(shù)值與參數(shù)列表比較，返回對應值。應用于將表的行轉換成列以及IF語句無法應用的場合。當與SIGN聯(lián)合使用時功能擴展,可以判斷大于小于的情況.
格式: DECODE(input_value , value1 , result1 , value2 , result2 , ….defult_result)
例∶ DECODE(VALUE,1,100,2,300,500)
當VALUE=1時返回100
當VALUE=2時返回300
否則返回500
DECODE(SIGN(VALUES-100), -1,-10,1,10,0)
當VALUE<100時返回-10
當VALUE>100時返回10
當VALUE=100時返回0
SELECT SUM(DECODE(EST_INT_KEY,77771,1,0)) A,
SUM(DECODE(EST_INT_KEY,77772,1,0)) B,
SUM(DECODE(EST_INT_KEY,77773,1,0)) C
FROM PMS_BLK
NVL---空值置換
格式: NVL(value,替換值)
例: NVL(value,’nullvalue’)當value為NULL值時返回nullvalue否則返回value的值
VSIZE---以字節(jié)為單位返回數(shù)據類型尺寸
格式∶VSIZE(數(shù)據類型)
例∶ VSIZE(SYSDATE)=8
USER---得到當前用戶名
ROWNUM--- oracle系統(tǒng)順序分配為從查詢返回的行的編號，返回的第一行分配的是1，第二行是2，依此類推，這個偽字段可以用于限制查詢返回的總行數(shù)。不能以任何基表的名稱作為前綴?？梢杂迷谙拗品祷赜涗洍l數(shù)的地方不受ORDER BY的影響.
例∶ select rownum,month,sell from sale where rownum=1
或select rownum,month,sell from sale where rownum<2(返回第一條記錄)
返回rownum在4—10之間的記錄
select rownum,month,sell from sale where rownum<10
minus
select rownum,month,sell from sale where rownum<5;
以下均錯誤:
select rownum,month,sell from sale where rownum=2(1以上)
select rownum,month,sell from sale where rownum>2
ROWID---數(shù)據行在物理磁盤上的物理地址，Oracle通過ROWID來定位數(shù)據的具體位置，這是存取表中數(shù)據的最快的方法.
RETURN---用于函數(shù)和過程中.遇見RETURN程序立刻中斷,返回,不再執(zhí)行剩余部分.
SQLCODE---返回ORACLE錯誤號。
SQLERRM---返回ORACLE錯誤信息。
----------------------------------------------------------------------------------------------------------
1、Dateadd
在向指定日期加上一段時間的基礎上，返回新的 datetime 值。
語法 ：DATEADD(datepart,number,date)
參數(shù)
datepart
是規(guī)定應向日期的哪一部分返回新值的參數(shù)。下表列出了識別的日期部分和縮寫。
日期部分 縮寫
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
number
是用來增加datepart的值。如果指定一個不是整數(shù)的值，則將廢棄此值的小數(shù)部分。
例如，如果為datepart指定day，為number指定1.75，則date將增加1。
date
是返回datetime或smalldatetime值或日期格式字符串的表達式。有關指定日期的更多信息，請參見datetime和smalldatetime。
如果您只指定年份的最后兩位數(shù)字，則小于或等于"兩位數(shù)年份截止期"配置選項的值的最后兩位數(shù)字的數(shù)字所在世紀與截止年所
在世紀相同。大于該選項的值的最后兩位數(shù)字的數(shù)字所在世紀為截止年所在世紀的前一個世紀。
例如，如果two digit year cutoff 為 2049（默認），則 49 被解釋為 2049，2050 被解釋為 1950。
為避免模糊，請使用四位數(shù)的年份。
返回類型
返回 datetime，但如果 date 參數(shù)是 smalldatetime，返回 smalldatetime。
2、DATEDIFF
返回跨兩個指定日期的日期和時間邊界數(shù)。
一、 語法
DATEDIFF ( datepart , startdate , enddate )
二、參數(shù)
datepart
是規(guī)定了應在日期的哪一部分計算差額的參數(shù)。下表列出了 Microsoft? SQL Server? 識別的日期部分和縮寫。
日期部分 縮寫
year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
startdate
是計算的開始日期。startdate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表達式。
因為 smalldatetime 只精確到分鐘，所以當用 smalldatetime 值時，秒和毫秒總是 0。
如果您只指定年份的最后兩位數(shù)字，則小于或等于"兩位數(shù)年份截止期"配置選項的值的最后兩位數(shù)字的數(shù)字所在世紀與截止年所在世紀相同。大于該選項的值的最后兩位數(shù)字的數(shù)字所在世紀為截止年所在世紀的前一個世紀。例如，如果 two digit year cutoff 為 2049（默認），則 49 被解釋為 2049，2050 被解釋為 1950。為避免模糊，請使用四位數(shù)的年份。
enddate
是計算的終止日期。enddate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表達式。
三、返回類型
integer
四、用法
此函數(shù)計算兩個指定日期之間日期部分的數(shù)目。結果為日期部分中等于（date2 - date1）的有符號的整數(shù)值。
當結果不是日期部分的偶數(shù)倍時，DATEDIFF 將被截斷而不是被舍入。
當使用 day 作為日期部分時，DATEDIFF 返回兩個指定的時間之間（包括第二個日期但不包括第一個日期）的午夜數(shù)。
當使用 month 作為日期部分時，DATEDIFF 返回兩個日期之間（包括第二個日期但不包括第一個日期）出現(xiàn)的月的第一天的數(shù)目。
當使用 week 作為日期部分時，DATEDIFF 返回兩個日期（包括第二個日期但不包括第一個日期）之間星期日的數(shù)目。
對于更小的時間單位存在溢出值：
milliseconds 24 天
seconds 68 年
minutes 4083 年
others 沒有溢出限制
如果超出這些限制，此函數(shù)將返回溢出錯誤。
五、標準和兼容性
SQL/92 Transact-SQL 擴展。
SQL/99 Transact-SQL 擴展。
Sybase 與 Adaptive Server Enterprise 兼容。
六、示例
下面的語句返回 1：
select datediff( hour, ''4:00am'', ''5:50am'' )
下面的語句返回 102：
select datediff( month, ''1987/05/02'', ''1995/11/15'' )
下面的語句返回 0：
select datediff( day, ''00:00'', ''23:59'' )
下面的語句返回 4：
select datediff( day, ''1999/07/19 00:00'',''1999/07/23 23:59'' )
下面的語句返回 0：
select datediff( month, ''1999/07/19'', ''1999/07/23'' )
下面的語句返回 1：
select datediff( month, ''1999/07/19'', ''1999/08/23'' )
3、decode函數(shù)
decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值)
該函數(shù)的含義如下：
IF 條件=值1 THEN
RETURN(翻譯值1)
ELSIF 條件=值2 THEN
RETURN(翻譯值2)
......
ELSIF 條件=值n THEN
RETURN(翻譯值n)
ELSE
RETURN(缺省值)
END IF
·使用方法：
1、比較大小
select decode(sign(變量1-變量2),-1,變量1,變量2) from dual; --取較小值
sign()函數(shù)根據某個值是0、正數(shù)還是負數(shù)，分別返回0、1、-1
例如：
變量1=10，變量2=20
則sign(變量1-變量2)返回-1，decode解碼結果為“變量1”，達到了取較小值的目的。
2、表、視圖結構轉化
現(xiàn)有一個商品銷售表sale，表結構為：
month char(6) --月份
sell number(10,2) --月銷售金額
現(xiàn)有數(shù)據為：
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要轉化為以下結構的數(shù)據：
year char(4) --年份
month1 number(10,2) --1月銷售金額
month2 number(10,2) --2月銷售金額
month3 number(10,2) --3月銷售金額
month4 number(10,2) --4月銷售金額
month5 number(10,2) --5月銷售金額
month6 number(10,2) --6月銷售金額
month7 number(10,2) --7月銷售金額
month8 number(10,2) --8月銷售金額
month9 number(10,2) --9月銷售金額
month10 number(10,2) --10月銷售金額
month11 number(10,2) --11月銷售金額
month12 number(10,2) --12月銷售金額
結構轉化的SQL語句為：
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,
month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
group by substrb(month,1,4);
4、CAST和CONVERT
將某種數(shù)據類型的表達式顯式轉換為另一種數(shù)據類型。CAST 和 CONVERT 提供相似的功能。
語法
使用 CAST：
CAST ( expression AS data_type )
使用 CONVERT：
CONVERT (data_type[(length)], expression [, style])
參數(shù)
expression
是任何有效的 Microsoft? SQL Server? 表達式。有關更多信息，請參見表達式。
data_type
目標系統(tǒng)所提供的數(shù)據類型，包括 bigint 和 sql_variant。不能使用用戶定義的數(shù)據類型。有關可用的數(shù)據類型的更多信息，請參見數(shù)據類型。
length
nchar、nvarchar、char、varchar、binary 或 varbinary 數(shù)據類型的可選參數(shù)。
style
日期格式樣式，借以將 datetime 或 smalldatetime 數(shù)據轉換為字符數(shù)據（nchar、nvarchar、char、varchar、nchar 或 nvarchar 數(shù)據類型）；或者字符串格式樣式，借以將 float、real、money 或 smallmoney 數(shù)據轉換為字符數(shù)據（nchar、nvarchar、char、varchar、nchar 或 nvarchar 數(shù)據類型）。
SQL Server 支持使用科威特算法的阿拉伯樣式中的數(shù)據格式。
在表中，左側的兩列表示將 datetime 或 smalldatetime 轉換為字符數(shù)據的 style 值。給 style 值加 100，可獲得包括世紀數(shù)位的四位年份 (yyyy)。
不帶世紀數(shù)位 (yy) 帶世紀數(shù)位 (yyyy)
標準
輸入/輸出**
- 0 或 100 (*) 默認值 mon dd yyyy hh:miAM（或 PM）
1 101 美國 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英國/法國 dd/mm/yy
4 104 德國 dd.mm.yy
5 105 意大利 dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 或 109 (*) 默認值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10 110 美國 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd
- 13 或 113 (*) 歐洲默認值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 或 120 (*) ODBC 規(guī)范 yyyy-mm-dd hh:mm:ss[.fff]
- 21 或 121 (*) ODBC 規(guī)范（帶毫秒） yyyy-mm-dd hh:mm:ss[.fff]
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格）
- 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM
- 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM
* 默認值（style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121）始終返回世紀數(shù)位 (yyyy)。
** 當轉換為 datetime 時輸入；當轉換為字符數(shù)據時輸出。
*** 專門用于 XML。對于從 datetime 或 smalldatetime 到 character 數(shù)據的轉換，輸出格式如表中所示。對于從 float、money 或 smallmoney 到 character 數(shù)據的轉換，輸出等同于 style 2。對于從 real 到 character 數(shù)據的轉換，輸出等同于 style 1。
重要 默認情況下，SQL Server 根據截止年份 2049 解釋兩位數(shù)字的年份。即，兩位數(shù)字的年份 49 被解釋為 2049，而兩位數(shù)字的年份 50 被解釋為 1950。許多客戶端應用程序（例如那些基于 OLE 自動化對象的客戶端應用程序）都使用 2030 作為截止年份。SQL Server 提供一個配置選項（"兩位數(shù)字的截止年份"），借以更改 SQL Server 所使用的截止年份并對日期進行一致性處理。然而最安全的辦法是指定四位數(shù)字年份。
當從 smalldatetime 轉換為字符數(shù)據時，包含秒或毫秒的樣式將在這些位置上顯示零。當從 datetime 或 smalldatetime 值進行轉換時，可以通過使用適當?shù)?char 或 varchar 數(shù)據類型長度來截斷不需要的日期部分。
下表顯示了從 float 或 real 轉換為字符數(shù)據時的 style 值。
值 輸出
0（默認值） 最大為 6 位數(shù)。根據需要使用科學記數(shù)法。
1 始終為 8 位值。始終使用科學記數(shù)法。
2 始終為 16 位值。始終使用科學記數(shù)法。
在下表中，左列表示從 money 或 smallmoney 轉換為字符數(shù)據時的 style 值。
值 輸出
0（默認值） 小數(shù)點左側每三位數(shù)字之間不以逗號分隔，小數(shù)點右側取兩位數(shù)，例如 4235.98。
1 小數(shù)點左側每三位數(shù)字之間以逗號分隔，小數(shù)點右側取兩位數(shù)，例如 3,510.92。
2 小數(shù)點左側每三位數(shù)字之間不以逗號分隔，小數(shù)點右側取四位數(shù)，例如 4235.9819。
返回類型
返回與 data type 0 相同的值。
5、CHARINDEX函數(shù)
返回字符或者字符串在另一個字符串中的起始位置。CHARINDEX函數(shù)調用方法如下：
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Expression1是要到expression2中尋找的字符中，start_location是CHARINDEX函數(shù)開始在expression2中找expression1的位置。
CHARINDEX函數(shù)返回一個整數(shù)，返回的整數(shù)是要找的字符串在被找的字符串中的位置。假如CHARINDEX沒有找到要找的字符串，那么函數(shù)整數(shù)“0”。讓我們看看下面的函數(shù)命令執(zhí)行的結果：
CHARINDEX('SQL', 'Microsoft SQL Server')
這個函數(shù)命令將返回在“Microsoft SQL Server”中“SQL”的起始位置，在這個例子中，CHARINDEX函數(shù)將返回“S”在“Microsoft SQL Server”中的位置11。
接下來，我們看這個CHARINDEX命令：
CHARINDEX('7.0', 'Microsoft SQL Server 2000')
在這個例子中，CHARINDEX返回零，因為字符串“7.0” 不能在“Microsoft SQL Server”中被找到。接下來通過兩個例子來看看如何使用CHARINDEX函數(shù)來解決實際的T-SQL問題。
6、DATEPART
返回代表指定日期的指定日期部分的整數(shù)。
語法
DATEPART ( datepart ,date )
參數(shù)
datepart
是指定應返回的日期部分的參數(shù)。下表列出了 Microsoft? SQL Server? 識別的日期部分和縮寫。
日期部分 縮寫
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
Hour hh
minute mi, n
second ss, s
millisecond ms
week (wk, ww) 日期部分反映對 SET DATEFIRST 作的更改。任何一年的 1 月 1 日定義了 week 日期部分的開始數(shù)字，例如：DATEPART(wk, 'Jan 1, xxxx') = 1，此處 xxxx 代表任一年。
weekday (dw) 日期部分返回對應于星期中的某天的數(shù)，例如：Sunday = 1、Saturday = 7。weekday 日期部分產生的數(shù)取決于 SET DATEFIRST 設定的值，此命令設定星期中的第一天。
date
是返回 datetime 或 smalldatetime 值或日期格式字符串的表達式。對 1753 年 1 月 1 日之后的日期用datetime 數(shù)據類型。更早的日期存儲為字符數(shù)據。當輸入 datetime 值時，始終將其放入引號中。因為 smalldatetime 只精確到分鐘，所以當用 smalldatetime 值時，秒和毫秒總是 0。
如果只指定年份的最后兩位數(shù)字，則小于或等于"兩位數(shù)年份截止期"配置選項的值的最后兩位數(shù)字的數(shù)字所在世紀與截止年所在世紀相同。大于該選項的值的最后兩位數(shù)字的數(shù)字所在世紀為截止年所在世紀的前一個世紀。例如，如果 two digit year cutoff 為 2049 （默認），則 49 被解釋為 2049，2050 被解釋為 1950。為避免模糊，請使用四位數(shù)的年份。
有關時間值指定的更多信息，請參見時間格式。有關日期指定的更多信息，請參見 datetime 和 smalldatetime。
返回類型
int
注釋
DAY、MONTH、和 YEAR 函數(shù)分別是 DATEPART(dd, date)、DATEPART(mm, date)、和 DATEPART(yy, date) 的同義詞。
示例
GETDATE 函數(shù)返回當前日期；然而，比較時并不總是需要完整的日期信息（通常只是對日期的一部分進行比較）。此示例顯示 GETDATE 及 DATEPART 的輸出。
SELECT GETDATE() AS 'Current Date'
GO
下面是結果集：
Current Date
---------------------------
Feb 18 1998 11:46PM
SELECT DATEPART(month, GETDATE()) AS 'Month Number'
GO
下面是結果集：
Month Number
------------
2
此示例假設日期是 5 月 29 日。
SELECT DATEPART(month, GETDATE())
GO
下面是結果集：
-----------
5
(1 row(s) affected)
在此示例中，以數(shù)字表示日期。注意：SQL Server 將 0 解釋為 01/01/1900。
SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)
下面是結果集：
----- ------ ------
1 1 1900
7、SUBSTRING
功能：返回字符、二進制、文本或圖像表達式的一部分
語法：SUBSTRING ( expression, start, length )
SUBSTR(str,pos): 由<str>中，選出所有從第<pos>位置開始的字元。請注意，這個語法不適用于SQL Server上。
SUBSTR(str,pos,len): 由<str>中的第<pos>位置開始，選出接下去的<len>個字元。
SQL 中的 substring 函數(shù)是用來抓出一個欄位資料中的其中一部分。這個函數(shù)的名稱在不同的資料庫中不完全一樣：
MySQL: SUBSTR(), SUBSTRING()
Oracle: SUBSTR()
SQL Server: SUBSTRING()
參數(shù)：
expression 字符串、二進制字符串、文本、圖像、列或包含列的表達式。請勿使用包含聚合函數(shù)的表達式。
start 整數(shù)或可以隱式轉換為 int 的表達式，指定子字符串的開始位置。
length 整數(shù)或可以隱式轉換為 int 的表達式，指定子字符串的長度。
返回值：
如果 expression 是一種支持的字符數(shù)據類型，則返回字符數(shù)據。如果 expression 是一種支持的二進制數(shù)據類型，則返回二進制數(shù)據。如果 start = 1，則子字符串從表達式的第一個字符開始。
返回字符串的類型與給定表達式的類型相同（下表所示內容除外）。
給定表達式 返回類型
image varbinary
ntext nvarchar
8、stuff
stuff的功能：刪除指定長度的字符串并在指定的起始點插入另一組字符
STUFF ( character_expression , start , length , character_expression )
character_expression ：操作的字符，
start:刪除和插入的起始點，
length:刪除的長度，
character_expression ：要插入的字符
注：sql的stuff()函數(shù)和編程中數(shù)組的下標起始位置不一樣，數(shù)組是以0開始的，而 stuff()函數(shù)是以 1 開始的。
如果 stuff() 函數(shù)以 0 開始的話，它是從expression1_Str 字符串的最后一個字符開始截取。例子如下所示：
例1：stuff('00000000',0,3,'123') --> 對應的結果是： (沒有值輸出)
例2：stuff('00000000',1,3,'123') --> 對應的結果是：12300000
9、ceiling函數(shù) floor函數(shù)
ceiling函數(shù)返回大于或等于所給數(shù)字表達式的最小整數(shù)。
floor函數(shù)返回小于或等于所給數(shù)字表達式的最大整數(shù)。
比如
celling(12.1) 結果為 13
floor(12.1)結果為 12
在介紹這個之前你必須明白什么是oracle中的空值null
1.NVL函數(shù)
NVL函數(shù)的格式如下：NVL(expr1,expr2)
含義是：如果oracle第一個參數(shù)為空那么顯示第二個參數(shù)的值，如果第一個參數(shù)的值不為空，則顯示第一個參數(shù)本來的值。
例如：
SQL> select ename,NVL(comm, -1) from emp;
ENAME NVL(COMM,-1)
——————– ————
SMITH -1
ALLEN 300
WARD 500
JONES -1
MARTIN 1400
BLAKE -1
FORD -1
MILLER -1
其中顯示-1的本來的值全部都是空值的
2 NVL2函數(shù)
NVL2函數(shù)的格式如下：NVL2(expr1,expr2, expr3)
含義是：如果該函數(shù)的第一個參數(shù)為空那么顯示第二個參數(shù)的值，如果第一個參數(shù)的值不為空，則顯示第三個參數(shù)的值。
SQL> select ename,NVL2(comm,-1,1) from emp;
ENAME NVL2(COMM,-1,1)
——————– —————
SMITH 1
ALLEN -1
WARD -1
JONES 1
MARTIN -1
BLAKE 1
CLARK 1
SCOTT 1
上面的例子中。凡是結果是1的原來都不為空，而結果是-1的原來的值就是空。
3. NULLIF函數(shù)
NULLIF(exp1,expr2)函數(shù)的作用是如果exp1和exp2相等則返回空(NULL)，否則返回第一個值。
下面是一個例子。使用的是oracle中HR schema，如果HR處于鎖定，請啟用
這里的作用是顯示出那些換過工作的人員原工作，現(xiàn)工作。
SQL> SELECT e.last_name, e.job_id,j.job_id,NULLIF(e.job_id, j.job_id) “Old Job ID”
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;
LAST_NAME JOB_ID JOB_ID Old Job ID
————————————————– ——————– ——————– ——————–
De Haan AD_VP IT_PROG AD_VP
Hartstein MK_MAN MK_REP MK_MAN
Kaufling ST_MAN ST_CLERK ST_MAN
Kochhar AD_VP AC_MGR AD_VP
Kochhar AD_VP AC_ACCOUNT AD_VP
Raphaely PU_MAN ST_CLERK PU_MAN
Taylor SA_REP SA_MAN SA_REP
Taylor SA_REP SA_REP
Whalen AD_ASST AC_ACCOUNT AD_ASST
Whalen AD_ASST AD_ASST
可以看到凡是employee。job_id和job_histroy.job_id相等的，都會在結果中輸出NULL即為空，否則顯示的是employee。job_id
4.Coalesce函數(shù)
Coalese函數(shù)的作用是的NVL的函數(shù)有點相似，其優(yōu)勢是有更多的選項。
格式如下：
Coalesce(expr1, expr2, expr3….. exprn)
Coalesce是這樣來處理這些參數(shù)的。如果第一個參數(shù)為空，則看第二個參數(shù)是否是空，否則則顯示第一個參數(shù)，如果第二個參數(shù)是空再看第三個參數(shù)是否為空，否則顯示第二個參數(shù)，依次類推。
這個函數(shù)實際上是NVL的循環(huán)使用，在此就不舉例子了
SQL語句基礎
--數(shù)據操作
SELECT --從數(shù)據庫表中 檢索數(shù)據行和列
INSERT --向數(shù)據庫表 添加新數(shù)據行
DELETE --從數(shù)據庫表中 刪除數(shù)據行
UPDATE --更新 數(shù)據庫表中的數(shù)據
--數(shù)據定義
CREATE TABLE --創(chuàng)建一個數(shù)據庫表
DROP TABLE --從數(shù)據庫中刪除表
ALTER TABLE --修改數(shù)據庫表結構
CREATE VIEW --創(chuàng)建一個視圖
DROP VIEW --從數(shù)據庫中刪除視圖
CREATE INDEX --為數(shù)據庫表創(chuàng)建一個索引
DROP INDEX --從數(shù)據庫中刪除索引
CREATE PROCEDURE --創(chuàng)建一個存儲過程
DROP PROCEDURE --從數(shù)據庫中刪除存儲過程
CREATE TRIGGER --創(chuàng)建一個觸發(fā)器
DROP TRIGGER --從數(shù)據庫中刪除觸發(fā)器
CREATE SCHEMA --向數(shù)據庫添加一個新模式
DROP SCHEMA --從數(shù)據庫中刪除一個模式
CREATE DOMAIN --創(chuàng)建一個數(shù)據值域
ALTER DOMAIN --改變域定義
DROP DOMAIN --從數(shù)據庫中刪除一個域
--數(shù)據控制
GRANT --授予用戶訪問權限
DENY --拒絕用戶訪問
REVOKE --解除用戶訪問權限
--事務控制
COMMIT --結束當前事務
ROLLBACK --中止當前事務
SET TRANSACTION --定義當前事務數(shù)據訪問特征
--程序化SQL
DECLARE --為查詢設定游標
EXPLAN --為查詢描述數(shù)據訪問計劃
OPEN --檢索查詢結果打開一個游標
FETCH --檢索一行查詢結果
CLOSE --關閉游標
PREPARE --為動態(tài)執(zhí)行準備SQL 語句
EXECUTE --動態(tài)地執(zhí)行SQL 語句
DESCRIBE --描述準備好的查詢
---局部變量
declare @id char(10)
--set @id = '10010001'
select @id = '10010001'
---全局變量
---必須以@@開頭
--IF ELSE
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' --打印字符串'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'
--CASE
use pangu
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end
--WHILE CONTINUE BREAK
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x --打印變量x 的值
while @y < 3
begin
select @c = http://www.cnblogs.com/debuging/archive/2012/11/05/mailto:100*@x + @y
print @c --打印變量c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
--WAITFOR
--例 等待1 小時2 分零3 秒后才執(zhí)行SELECT 語句
waitfor delay ’01:02:03’
select * from employee
--例 等到晚上11 點零8 分后才執(zhí)行SELECT 語句
waitfor time ’23:08:00’
select * from employee
***SELECT***
select *(列名) from table_name(表名) where column_name operator value
ex:(宿主)
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
stockname like '[a-zA-Z]%' --------- ([]指定值的范圍)
stockname like '[^F-M]%' --------- (^排除指定范圍)
--------- 只能在使用like關鍵字的where子句中使用通配符)
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stocksex = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
order by 1,2 --------- by列號
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查詢
--------- 除非能確保內層select只返回一個行的值，
--------- 否則應在外層where子句中用一個in限定符
select distinct column_name form table_name --------- distinct指定檢索獨有的列值，不重復
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
--------- group by 將表按行分組,指定列中有相同的值
having count(*) = 2 --------- having選定指定的組
select *
from table1, table2
where table1.id *= table2.id -------- 左外部連接，table1中有的而table2中沒有得以null表示
table1.id =* table2.id -------- 右外部連接
select stockname from table1
union [all] ----- union合并查詢結果集，all-保留重復行
select stockname from table2
***insert***
insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
value (select Stockname , Stocknumber from Stock_table2)---value為select語句
***update***
update table_name set Stockname = "xxx" [where Stockid = 3]
Stockname = default
Stockname = null
Stocknumber = Stockname + 4
***delete***
delete from table_name where Stockid = 3
truncate table_name ----------- 刪除表中所有行，仍保持表的完整性
drop table table_name --------------- 完全刪除表
***alter table*** --- 修改數(shù)據庫表結構
alter table database.owner.table_name add column_name char(2) null .....
sp_help table_name ---- 顯示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ----- 實現(xiàn)刪除列的方法（創(chuàng)建新表）
alter table table_name drop constraint Stockname_default ---- 刪除Stockname的default約束
***function(/*常用函數(shù)*/)***
----統(tǒng)計函數(shù)----
AVG --求平均值
COUNT --統(tǒng)計數(shù)目
MAX --求最大值
MIN --求最小值
SUM --求和
--AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id
--MAX
--求工資最高的員工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)
--STDEV()
--STDEV()函數(shù)返回表達式中所有數(shù)據的標準差
--STDEVP()
--STDEVP()函數(shù)返回總體標準差
--VAR()
--VAR()函數(shù)返回表達式中所有值的統(tǒng)計變異數(shù)
--VARP()
--VARP()函數(shù)返回總體變異數(shù)
----算術函數(shù)----
/***三角函數(shù)***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的余弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的余切
/***反三角函數(shù)***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
--把弧度轉換為角度返回與表達式相同的數(shù)據類型可為
--INTEGER/MONEY/REAL/FLOAT 類型
RADIANS(numeric_expression) --把角度轉換為弧度返回與表達式相同的數(shù)據類型可為
--INTEGER/MONEY/REAL/FLOAT 類型
EXP(float_expression) --返回表達式的指數(shù)值
LOG(float_expression) --返回表達式的自然對數(shù)值
LOG10(float_expression)--返回表達式的以10 為底的對數(shù)值
SQRT(float_expression) --返回表達式的平方根
/***取近似值函數(shù)***/
CEILING(numeric_expression) --返回>=表達式的最小整數(shù)返回的數(shù)據類型與表達式相同可為
--INTEGER/MONEY/REAL/FLOAT 類型
FLOOR(numeric_expression) --返回<=表達式的最小整數(shù)返回的數(shù)據類型與表達式相同可為
--INTEGER/MONEY/REAL/FLOAT 類型
ROUND(numeric_expression) --返回以integer_expression 為精度的四舍五入值返回的數(shù)據
--類型與表達式相同可為INTEGER/MONEY/REAL/FLOAT 類型
ABS(numeric_expression) --返回表達式的絕對值返回的數(shù)據類型與表達式相同可為
--INTEGER/MONEY/REAL/FLOAT 類型
SIGN(numeric_expression) --測試參數(shù)的正負號返回0 零值1 正數(shù)或-1 負數(shù)返回的數(shù)據類型
--與表達式相同可為INTEGER/MONEY/REAL/FLOAT 類型
PI() --返回值為π 即3.1415926535897936
RAND([integer_expression]) --用任選的[integer_expression]做種子值得出0-1 間的隨機浮點數(shù)
----字符串函數(shù)----
ASCII() --函數(shù)返回字符表達式最左端字符的ASCII 碼值
CHAR() --函數(shù)用于將ASCII 碼轉換為字符
--如果沒有輸入0~255之間的ASCII碼值,CHAR函數(shù)會返回一個NULL值
LOWER() --函數(shù)把字符串全部轉換為小寫
UPPER() --函數(shù)把字符串全部轉換為大寫
STR() --函數(shù)把數(shù)值型數(shù)據轉換為字符型數(shù)據
LTRIM() --函數(shù)把字符串頭部的空格去掉
RTRIM() --函數(shù)把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING() --函數(shù)返回部分字符串
RIGHT 函數(shù) [字符串]
--------------------------------------------------------------------------------
功能
返回字符串中最右邊的字符。
語法
RIGHT ( string-expression, integer-expression )
參數(shù)
string-expression 從左邊截斷的字符串。
integer-expression 要返回的從字符串結尾處開始計數(shù)的字符數(shù)。
用法
如果字符串包含多字節(jié)字符，并且使用了適當?shù)臍w類，則返回的字節(jié)數(shù)可能大于指定的字符數(shù)。
CHARINDEX(),PATINDEX() --函數(shù)返回字符串中某個指定的子串出現(xiàn)的開始位置
SOUNDEX() --函數(shù)返回一個四位字符碼
--SOUNDEX函數(shù)可用來查找聲音相似的字符串但SOUNDEX函數(shù)對數(shù)字和漢字均只返回0 值
DIFFERENCE() --函數(shù)返回由SOUNDEX 函數(shù)返回的兩個字符表達式的值的差異
--0 兩個SOUNDEX 函數(shù)返回值的第一個字符不同
--1 兩個SOUNDEX 函數(shù)返回值的第一個字符相同
--2 兩個SOUNDEX 函數(shù)返回值的第一二個字符相同
--3 兩個SOUNDEX 函數(shù)返回值的第一二三個字符相同
--4 兩個SOUNDEX 函數(shù)返回值完全相同
REPLICATE() --函數(shù)返回一個重復character_expression 指定次數(shù)的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
運行結果如下
----------------------
abcabcabc NULL*/
REVERSE() --函數(shù)將指定的字符串的字符排列順序顛倒
REPLACE() --函數(shù)返回被替換了指定子串的字符串
/*select replace('abc123g', '123', 'def')
運行結果如下
----------------------
abcdefg*/
SPACE() --函數(shù)返回一個有指定長度的空白字符串
STUFF() --函數(shù)用另一子串替換字符串指定位置長度的子串
----數(shù)據類型轉換函數(shù)----
CAST() 函數(shù)語法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT() 函數(shù)語法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])
select cast(100+99 as char) convert(varchar(12), getdate())
運行結果如下
-----------------------
199 Jan 15 2000
----日期函數(shù)----
DAY() --函數(shù)返回date_expression 中的日期值
MONTH() --函數(shù)返回date_expression 中的月份值
YEAR() --函數(shù)返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>) --函數(shù)返回指定日期date 加上指定的額外日期間隔number 產生的新日期
DATEDIFF(<datepart> ,<number> ,<date>) --函數(shù)返回兩個指定日期在datepart 方面的不同之處
DATENAME(<datepart> , <date>) --函數(shù)以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>) --函數(shù)以整數(shù)值的形式返回日期的指定部分
GETDATE() --函數(shù)以DATETIME 的缺省格式返回系統(tǒng)當前的日期和時間
----系統(tǒng)函數(shù)----
APP_NAME() --函數(shù)返回當前執(zhí)行的應用程序的名稱
COALESCE() --函數(shù)返回眾多表達式中第一個非NULL 表達式的值
COL_LENGTH(<'table_name'>, <'column_name'>) --函數(shù)返回表中指定字段的長度值
COL_NAME(<table_id>, <column_id>) --函數(shù)返回表中指定字段的名稱即列名
DATALENGTH() --函數(shù)返回數(shù)據表達式的數(shù)據的實際長度
DB_ID(['database_name']) --函數(shù)返回數(shù)據庫的編號
DB_NAME(database_id) --函數(shù)返回數(shù)據庫的名稱
HOST_ID() --函數(shù)返回服務器端計算機的名稱
HOST_NAME() --函數(shù)返回服務器端計算機的名稱
IDENTITY(<data_type>[, seed increment]) [AS column_name]) --IDENTITY() 函數(shù)只在SELECT INTO 語句中使用用于插入一個identity column列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() --函數(shù)判斷所給定的表達式是否為合理日期
ISNULL(<check_expression>, <replacement_value>) --函數(shù)將表達式中的NULL值用指定值替換
ISNUMERIC() --函數(shù)判斷所給定的表達式是否為合理的數(shù)值
NEWID() --函數(shù)返回一個UNIQUEIDENTIFIER 類型的數(shù)值
NULLIF(<expression1>, <expression2>) --NULLIF 函數(shù)在expression1 與expression2 相等時返回NULL 值若不相等時則返回expression1的值
