精华内容
下载资源
问答
  • plsql常用函数

    2017-05-26 15:58:55
    常用plsql函数
  • PLSQL常用函数

    千次阅读 2017-11-22 13:15:08
    PLSQL常用函数  1)处理字符的函数  || 或 CONCAT---并置运算符。  格式∶CONCAT(STRING1, STRING2)  例:’ABC’|| ’DE’=’ABCDE’  CONCAT(‘ABC’,’DE’) =’ABCDE’    ASCII---返回字符的...

       PLSQL常用函数

        1)处理字符的函数

      || 或 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[ , 开始位置[ , 出现次数]])
      例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
      
       INITCAP---将字符串每个单词首字母均变为大写
      例: INITCAP(‘this is a test’)=’ This Is A Test’’
      
      LENGTH----计算串长
      格式∶ LENGTH(string)
      
      RPAD,LPAD---右填充、左填充。默认为填充空格。
      格式: RPAD(字符串 , 字符个数 , 填充字符)
      例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
      
      LTRIM,RTRIM-----左右截断。默认为删除空格。 
      格式∶ LTRIM(STRING[,’SET’])
      例∶ LTRIM(‘***tes*t***’ , ’*’)=’ tes*t***’
       
      LOWER----将字符串转换为小写
      格式∶LOWER(string)
      
      UPPER---将字符串转换为大写
      格式∶UPPER(string)
      
      SUBSTR----提取子串。START为正数时从左开始、为负数时从右开始
      格式∶ 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时针对字节.

      2)处理数字的函数

      LEAST---返回参数列表中的最小值。返回参数类型以第一参数为准
      格式∶LEAST(value,value,value,value,value,….)value为数字或字符串
      例∶ LEAST(1,2,5,-10,9)= -10
      
      SIGN---返回参数的符号位,负数--- -1,0----0,正数---1
      格式∶SIGN(value)
       
      CELL---返回大于等于特定值的最小整数
      格式∶CELL(value)
      例∶ CELL(-10,9)= -10

      3)处理日期

      SYSDATE---系统时间。精确至秒
      
      ADD_MONTH—加减月份。numvalue为负数时减去相应月份
      格式: ADD_MONTH (data1,numvalue)
      
      MONTHS_BETWEEN---返回两日期之间的月数,当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)分组函数

      返回基于多个行的单一结果.
      常用函数:
      AVG---求平均值
      COUNT---返回查询的行数
      MAX---返回查询列的最大值
      MIN---返回查询列的最小值
      SUM---返回查询列的总和
      MAX, MIN常与GROUP BY配套使用

      5)转换函数

      TO_DATE---转换字符串为日期型
      格式∶ TO_DATE(STRING[,’FORMAT’])
      
      TO_CHAR---转换日期型或数值型为字符串。最重要的函数之一.其FORMAT格式多种多样
      格式∶TO_CHAR(DATE [,’FORMAT’])
      FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
      P836 日期格式元素
      
      TO_NUMBER---转换字符串为数字
      格式∶TO_NUMBER(string [ , format])
      FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
       
      6)其他

      DECODE---IF语句的另一形式。将输入数值与参数列表比较,返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。当与SIGN联合使用时功能扩展,可以判断大于小于的情况.
      格式: 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---以字节为单位返回数据类型尺寸
      格式∶VSIZE(数据类型)
      例∶ VSIZE(SYSDATE)=8
      
      USER---得到当前用户名
      
      ROWNUM--- oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数。不能以任何基表的名称作为前缀。可以用在限制返回记录条数的地方不受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---数据行在物理磁盘上的物理地址,Oracle通过ROWID来定位数据的具体位置,这是存取表中数据的最快的方法.
      RETURN---用于函数和过程中.遇见RETURN程序立刻中断,返回,不再执行剩余部分.
      SQLCODE---返回ORACLE错误号。
      SQLERRM---返回ORACLE错误信息。

    ----------------------------------------------------------------------------------------------------------
    1、Dateadd
      在向指定日期加上一段时间的基础上,返回新的   datetime   值。   
      语法 :DATEADD(datepart,number,date)     
      参数
      datepart   
      是规定应向日期的哪一部分返回新值的参数。下表列出了识别的日期部分和缩写。   
      日期部分   缩写     
      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的值。如果指定一个不是整数的值,则将废弃此值的小数部分。
      例如,如果为datepart指定day,为number指定1.75,则date将增加1。   
        
      date    
      是返回datetime或smalldatetime值或日期格式字符串的表达式。有关指定日期的更多信息,请参见datetime和smalldatetime。   
      如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所
      在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。
      例如,如果two  digit year cutoff 为 2049(默认),则 49 被解释为 2049,2050 被解释为 1950。
      为避免模糊,请使用四位数的年份。   
        
      返回类型   
      返回   datetime,但如果   date   参数是   smalldatetime,返回   smalldatetime。 
      
    2、DATEDIFF 
       返回跨两个指定日期的日期和时间边界数。 
       一、 语法 
       DATEDIFF ( datepart , startdate , enddate ) 
       二、参数 
       datepart 
       是规定了应在日期的哪一部分计算差额的参数。下表列出了 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。 
       如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果 two digit year cutoff 为 2049(默认),则 49 被解释为 2049,2050 被解释为 1950。为避免模糊,请使用四位数的年份。 
        
       enddate 
       是计算的终止日期。enddate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。 
        
       三、返回类型 
       integer 
       四、用法
        此函数计算两个指定日期之间日期部分的数目。结果为日期部分中等于(date2 - date1)的有符号的整数值。
        当结果不是日期部分的偶数倍时,DATEDIFF 将被截断而不是被舍入。
        当使用 day 作为日期部分时,DATEDIFF 返回两个指定的时间之间(包括第二个日期但不包括第一个日期)的午夜数。
        当使用 month 作为日期部分时,DATEDIFF 返回两个日期之间(包括第二个日期但不包括第一个日期)出现的月的第一天的数目。
        当使用 week 作为日期部分时,DATEDIFF 返回两个日期(包括第二个日期但不包括第一个日期)之间星期日的数目。
        对于更小的时间单位存在溢出值:

    milliseconds    24 天 
    seconds    68 年 
    minutes    4083 年 
    others    没有溢出限制 
    如果超出这些限制,此函数将返回溢出错误。
        五、标准和兼容性
    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函数
      decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
      
      该函数的含义如下:
      
      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()函数根据某个值是0、正数还是负数,分别返回0、1、-1
      
      例如:
      变量1=10,变量2=20
      则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。
      
      2、表、视图结构转化
      
      现有一个商品销售表sale,表结构为:
      
      month    char(6)      --月份
      sell    number(10,2)   --月销售金额
      
      现有数据为:
      
      200001  1000
      200002  1100
      200003  1200
      200004  1300
      200005  1400
      200006  1500
      200007  1600
      200101  1100
      200202  1200
      200301  1300
      
      想要转化为以下结构的数据:
      
      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
     将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
     语法
     使用 CAST:
     CAST ( expression AS data_type )
     使用 CONVERT:
     CONVERT (data_type[(length)], expression [, style])
     参数
     expression
     是任何有效的 Microsoft? SQL Server? 表达式。有关更多信息,请参见表达式。
     data_type
     目标系统所提供的数据类型,包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息,请参见数据类型。
     length
     nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。
     
     style
     日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。
     
     SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。
     
     在表中,左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。
     
     不带世纪数位 (yy) 带世纪数位 (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 规范 yyyy-mm-dd hh:mm:ss[.fff] 
     - 21 或 121 (*) ODBC 规范(带毫秒) 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)始终返回世纪数位 (yyyy)。
     ** 当转换为 datetime 时输入;当转换为字符数据时输出。
     *** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换,输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换,输出等同于 style 2。对于从 real 到 character 数据的转换,输出等同于 style 1。
     
     
     
     重要 默认情况下,SQL Server 根据截止年份 2049 解释两位数字的年份。即,两位数字的年份 49 被解释为 2049,而两位数字的年份 50 被解释为 1950。许多客户端应用程序(例如那些基于 OLE 自动化对象的客户端应用程序)都使用 2030 作为截止年份。SQL Server 提供一个配置选项("两位数字的截止年份"),借以更改 SQL Server 所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。
     
     
     当从 smalldatetime 转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时,可以通过使用适当的 char 或 varchar 数据类型长度来截断不需要的日期部分。
     
     下表显示了从 float 或 real 转换为字符数据时的 style 值。
     
     值 输出 
     0(默认值) 最大为 6 位数。根据需要使用科学记数法。 
     1 始终为 8 位值。始终使用科学记数法。 
     2 始终为 16 位值。始终使用科学记数法。
     
     
     在下表中,左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。
     
     值 输出 
     0(默认值) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。 
     1 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。 
     2 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。 
     
     
     返回类型
     返回与 data type 0 相同的值。
     
     
     
    5、CHARINDEX函数
        返回字符或者字符串在另一个字符串中的起始位置。CHARINDEX函数调用方法如下: 
           CHARINDEX ( expression1 , expression2 [ , start_location ] )

           Expression1是要到expression2中寻找的字符中,start_location是CHARINDEX函数开始在expression2中找expression1的位置。

           CHARINDEX函数返回一个整数,返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串,那么函数整数“0”。让我们看看下面的函数命令执行的结果:

          CHARINDEX('SQL', 'Microsoft SQL Server')

          这个函数命令将返回在“Microsoft SQL Server”中“SQL”的起始位置,在这个例子中,CHARINDEX函数将返回“S”在“Microsoft SQL Server”中的位置11。
         接下来,我们看这个CHARINDEX命令:

          CHARINDEX('7.0', 'Microsoft SQL Server 2000')

          在这个例子中,CHARINDEX返回零,因为字符串“7.0” 不能在“Microsoft SQL Server”中被找到。接下来通过两个例子来看看如何使用CHARINDEX函数来解决实际的T-SQL问题。

    6、DATEPART
     返回代表指定日期的指定日期部分的整数。
     
     语法
     DATEPART ( datepart ,date ) 
     
     参数
     datepart
     
     是指定应返回的日期部分的参数。下表列出了 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 日期部分的开始数字,例如:DATEPART(wk, 'Jan 1, xxxx') = 1,此处 xxxx 代表任一年。
     
     weekday (dw) 日期部分返回对应于星期中的某天的数,例如:Sunday = 1、Saturday = 7。weekday 日期部分产生的数取决于 SET DATEFIRST 设定的值,此命令设定星期中的第一天。
     
     date
     
     是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。对 1753 年 1 月 1 日之后的日期用datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时,始终将其放入引号中。因为 smalldatetime 只精确到分钟,所以当用 smalldatetime 值时,秒和毫秒总是 0。
     
     如果只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果 two digit year cutoff 为 2049 (默认),则 49 被解释为 2049,2050 被解释为 1950。为避免模糊,请使用四位数的年份。
     
     有关时间值指定的更多信息,请参见时间格式。有关日期指定的更多信息,请参见 datetime 和 smalldatetime。
     
     返回类型
     int
     
     注释
     DAY、MONTH、和 YEAR 函数分别是 DATEPART(dd, date)、DATEPART(mm, date)、和 DATEPART(yy, date) 的同义词。
     
     示例
     GETDATE 函数返回当前日期;然而,比较时并不总是需要完整的日期信息(通常只是对日期的一部分进行比较)。此示例显示 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)
     
     在此示例中,以数字表示日期。注意: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 函数是用来抓出一个栏位资料中的其中一部分。这个函数的名称在不同的资料库中不完全一样: 
     MySQL: SUBSTR(), SUBSTRING() 
     Oracle: SUBSTR() 
     SQL Server: SUBSTRING() 
     参数:
     expression 字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。
     start 整数或可以隐式转换为 int 的表达式,指定子字符串的开始位置。
     length 整数或可以隐式转换为 int 的表达式,指定子字符串的长度。 
     返回值: 
     如果 expression 是一种支持的字符数据类型,则返回字符数据。如果 expression 是一种支持的二进制数据类型,则返回二进制数据。如果 start = 1,则子字符串从表达式的第一个字符开始。 
     返回字符串的类型与给定表达式的类型相同(下表所示内容除外)。
     给定表达式  返回类型  
     image  varbinary 
     ntext   nvarchar 
     
    8、stuff
     stuff的功能:删除指定长度的字符串并在指定的起始点插入另一组字符
     STUFF ( character_expression , start , length , character_expression ) 
     character_expression :操作的字符,
     start:删除和插入的起始点,
     length:删除的长度,
     character_expression :要插入的字符
     注:sql的stuff()函数和编程中数组的下标起始位置不一样,数组是以0开始的,而 stuff()函数是以 1 开始的。
     如果 stuff() 函数以 0 开始的话,它是从expression1_Str 字符串的最后一个字符开始截取。例子如下所示:
        例1:stuff('00000000',0,3,'123') --> 对应的结果是: (没有值输出)
        例2:stuff('00000000',1,3,'123') --> 对应的结果是:12300000


    9、ceiling函数   floor函数    
     ceiling函数返回大于或等于所给数字表达式的最小整数。
     floor函数返回小于或等于所给数字表达式的最大整数。
     比如
     celling(12.1) 结果为 13
     floor(12.1)结果为 12
     
    在介绍这个之前你必须明白什么是oracle中的空值null

    1.NVL函数

    NVL函数的格式如下:NVL(expr1,expr2)

    含义是:如果oracle第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值。

    例如:

    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函数

    NVL2函数的格式如下:NVL2(expr1,expr2, expr3)

    含义是:如果该函数的第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第三个参数的值。

    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函数

    NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL),否则返回第一个值。

    下面是一个例子。使用的是oracle中HR schema,如果HR处于锁定,请启用

    这里的作用是显示出那些换过工作的人员原工作,现工作。

    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函数

    Coalese函数的作用是的NVL的函数有点相似,其优势是有更多的选项。

    格式如下:

    Coalesce(expr1, expr2, expr3….. exprn)

    Coalesce是这样来处理这些参数的。如果第一个参数为空,则看第二个参数是否是空,否则则显示第一个参数,如果第二个参数是空再看第三个参数是否为空,否则显示第二个参数,依次类推。

    这个函数实际上是NVL的循环使用,在此就不举例子了


    SQL语句基础

    --数据操作
    SELECT      --从数据库表中 检索数据行和列
    INSERT      --向数据库表 添加新数据行
    DELETE      --从数据库表中 删除数据行
    UPDATE      --更新 数据库表中的数据
    --数据定义
    CREATE TABLE    --创建一个数据库表
    DROP TABLE     --从数据库中删除表
    ALTER TABLE     --修改数据库表结构
    CREATE VIEW     --创建一个视图
    DROP VIEW     --从数据库中删除视图
    CREATE INDEX    --为数据库表创建一个索引
    DROP INDEX     --从数据库中删除索引
    CREATE PROCEDURE   --创建一个存储过程
    DROP PROCEDURE    --从数据库中删除存储过程
    CREATE TRIGGER    --创建一个触发器
    DROP TRIGGER    --从数据库中删除触发器
    CREATE SCHEMA    --向数据库添加一个新模式
    DROP SCHEMA     --从数据库中删除一个模式
    CREATE DOMAIN    --创建一个数据值域
    ALTER DOMAIN    --改变域定义
    DROP DOMAIN     --从数据库中删除一个域
    --数据控制
    GRANT      --授予用户访问权限
    DENY      --拒绝用户访问
    REVOKE      --解除用户访问权限
    --事务控制
    COMMIT      --结束当前事务
    ROLLBACK     --中止当前事务
    SET TRANSACTION    --定义当前事务数据访问特征
    --程序化SQL
    DECLARE      --为查询设定游标
    EXPLAN      --为查询描述数据访问计划
    OPEN      --检索查询结果打开一个游标
    FETCH      --检索一行查询结果
    CLOSE      --关闭游标
    PREPARE      --为动态执行准备SQL 语句
    EXECUTE      --动态地执行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 = 100*@x + @y
        print @c --打印变量c 的值
        select @y = @y + 1
       end
      select @x = @x + 1
      select @y = 1
     end

    --WAITFOR
    --例 等待1 小时2 分零3 秒后才执行SELECT 语句
    waitfor delay ’01:02:03’
    select * from employee
    --例 等到晚上11 点零8 分后才执行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*** --- 修改数据库表结构

      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 ......... ----- 实现删除列的方法(创建新表)
      alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束
        
    ***function(/*常用函数*/)***

    ----统计函数----
    AVG    --求平均值
    COUNT   --统计数目
    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()函数返回表达式中所有数据的标准差

    --STDEVP()
    --STDEVP()函数返回总体标准差

    --VAR()
    --VAR()函数返回表达式中所有值的统计变异数

    --VARP()
    --VARP()函数返回总体变异数

    ----算术函数----

    /***三角函数***/
    SIN(float_expression) --返回以弧度表示的角的正弦
    COS(float_expression) --返回以弧度表示的角的余弦
    TAN(float_expression) --返回以弧度表示的角的正切
    COT(float_expression) --返回以弧度表示的角的余切
    /***反三角函数***/
    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)
                           --把弧度转换为角度返回与表达式相同的数据类型可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    EXP(float_expression)  --返回表达式的指数值
    LOG(float_expression)  --返回表达式的自然对数值
    LOG10(float_expression)--返回表达式的以10 为底的对数值
    SQRT(float_expression) --返回表达式的平方根
    /***取近似值函数***/
    CEILING(numeric_expression)  --返回>=表达式的最小整数返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    FLOOR(numeric_expression)    --返回<=表达式的最小整数返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    ROUND(numeric_expression)    --返回以integer_expression 为精度的四舍五入值返回的数据
            --类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
    ABS(numeric_expression)      --返回表达式的绝对值返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    SIGN(numeric_expression)     --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型
            --与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
    PI()       --返回值为π 即3.1415926535897936
    RAND([integer_expression])   --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数

     

    ----字符串函数----
    ASCII()         --函数返回字符表达式最左端字符的ASCII 码值
    CHAR()   --函数用于将ASCII 码转换为字符
             --如果没有输入0~255之间的ASCII码值,CHAR函数会返回一个NULL值
    LOWER()   --函数把字符串全部转换为小写
    UPPER()   --函数把字符串全部转换为大写
    STR()   --函数把数值型数据转换为字符型数据
    LTRIM()   --函数把字符串头部的空格去掉
    RTRIM()   --函数把字符串尾部的空格去掉
    LEFT(),RIGHT(),SUBSTRING()  --函数返回部分字符串
    RIGHT 函数 [字符串]
    --------------------------------------------------------------------------------
      功能 
      返回字符串中最右边的字符。
      语法 
      RIGHT ( string-expression, integer-expression )
      参数 
      string-expression    从左边截断的字符串。
      integer-expression    要返回的从字符串结尾处开始计数的字符数。
      用法 
      如果字符串包含多字节字符,并且使用了适当的归类,则返回的字节数可能大于指定的字符数。

    CHARINDEX(),PATINDEX()  --函数返回字符串中某个指定的子串出现的开始位置
    SOUNDEX()  --函数返回一个四位字符码 
        --SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值     
    DIFFERENCE()    --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
        --0 两个SOUNDEX 函数返回值的第一个字符不同
        --1 两个SOUNDEX 函数返回值的第一个字符相同
        --2 两个SOUNDEX 函数返回值的第一二个字符相同
        --3 两个SOUNDEX 函数返回值的第一二三个字符相同
        --4 两个SOUNDEX 函数返回值完全相同
                                         

    REPLICATE()     --函数返回一个重复character_expression 指定次数的字符串
    /*select replicate('abc', 3) replicate( 'abc', -2)
    运行结果如下
    ----------------------
    abcabcabc NULL*/

    REVERSE()       --函数将指定的字符串的字符排列顺序颠倒
    REPLACE()       --函数返回被替换了指定子串的字符串
    /*select replace('abc123g', '123', 'def')
    运行结果如下
    ----------------------
    abcdefg*/


    SPACE()   --函数返回一个有指定长度的空白字符串
    STUFF()   --函数用另一子串替换字符串指定位置长度的子串


    ----数据类型转换函数----
    CAST() 函数语法如下
    CAST() (<expression> AS <data_ type>[ length ])
    CONVERT() 函数语法如下
    CONVERT() (<data_ type>[ length ], <expression> [, style])

    select cast(100+99 as char) convert(varchar(12), getdate())
    运行结果如下
    -----------------------
    199   Jan 15 2000

    ----日期函数----
    DAY()   --函数返回date_expression 中的日期值
    MONTH()   --函数返回date_expression 中的月份值
    YEAR()   --函数返回date_expression 中的年份值
    DATEADD(<datepart> ,<number> ,<date>)   --函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
    DATEDIFF(<datepart> ,<number> ,<date>)   --函数返回两个指定日期在datepart 方面的不同之处
    DATENAME(<datepart> , <date>)  --函数以字符串的形式返回日期的指定部分
    DATEPART(<datepart> , <date>)  --函数以整数值的形式返回日期的指定部分
    GETDATE()  --函数以DATETIME 的缺省格式返回系统当前的日期和时间

    ----系统函数----
    APP_NAME()  --函数返回当前执行的应用程序的名称
    COALESCE()  --函数返回众多表达式中第一个非NULL 表达式的值
    COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
    COL_NAME(<table_id>, <column_id>)   --函数返回表中指定字段的名称即列名
    DATALENGTH() --函数返回数据表达式的数据的实际长度
    DB_ID(['database_name']) --函数返回数据库的编号
    DB_NAME(database_id)  --函数返回数据库的名称
    HOST_ID()     --函数返回服务器端计算机的名称
    HOST_NAME()     --函数返回服务器端计算机的名称
    IDENTITY(<data_type>[, seed increment]) [AS column_name])   --IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
    /*select identity(int, 1, 1) as column_name
     into newtable
     from oldtable*/
    ISDATE()  --函数判断所给定的表达式是否为合理日期
    ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL值用指定值替换
    ISNUMERIC()  --函数判断所给定的表达式是否为合理的数值
    NEWID()   --函数返回一个UNIQUEIDENTIFIER 类型的数值
    NULLIF(<expression1>, <expression2>)   --NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1的值

    展开全文
  • plsql 常用函数

    千次阅读 2015-03-03 10:00:31
    转载自:...  PLSQL常用函数  1)处理字符的函数  || 或 CONCAT---并置运算符。  格式∶CONCAT(STRING1, STRING2)  例:’ABC’|| ’DE’=’ABCDE’  CONCAT(‘ABC

    转载自:http://www.cnblogs.com/debuging/archive/2012/11/05/2754837.html

       PLSQL常用函数

        1)处理字符的函数

      || 或 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[ , 开始位置[ , 出现次数]])
      例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
      
       INITCAP---将字符串每个单词首字母均变为大写
      例: INITCAP(‘this is a test’)=’ This Is A Test’’
      
      LENGTH----计算串长
      格式∶ LENGTH(string)
      
      RPAD,LPAD---右填充、左填充。默认为填充空格。
      格式: RPAD(字符串 , 字符个数 , 填充字符)
      例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
      
      LTRIM,RTRIM-----左右截断。默认为删除空格。 
      格式∶ LTRIM(STRING[,’SET’])
      例∶ LTRIM(‘***tes*t***’ , ’*’)=’ tes*t***’
       
      LOWER----将字符串转换为小写
      格式∶LOWER(string)
      
      UPPER---将字符串转换为大写
      格式∶UPPER(string)
      
      SUBSTR----提取子串。START为正数时从左开始、为负数时从右开始
      格式∶ 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时针对字节.

      2)处理数字的函数

      LEAST---返回参数列表中的最小值。返回参数类型以第一参数为准
      格式∶LEAST(value,value,value,value,value,….)value为数字或字符串
      例∶ LEAST(1,2,5,-10,9)= -10
      
      SIGN---返回参数的符号位,负数--- -1,0----0,正数---1
      格式∶SIGN(value)
       
      CELL---返回大于等于特定值的最小整数
      格式∶CELL(value)
      例∶ CELL(-10,9)= -10

      3)处理日期

      SYSDATE---系统时间。精确至秒
      
      ADD_MONTH—加减月份。numvalue为负数时减去相应月份
      格式: ADD_MONTH (data1,numvalue)
      
      MONTHS_BETWEEN---返回两日期之间的月数,当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)分组函数

      返回基于多个行的单一结果.
      常用函数:
      AVG---求平均值
      COUNT---返回查询的行数
      MAX---返回查询列的最大值
      MIN---返回查询列的最小值
      SUM---返回查询列的总和
      MAX, MIN常与GROUP BY配套使用

      5)转换函数

      TO_DATE---转换字符串为日期型
      格式∶ TO_DATE(STRING[,’FORMAT’])
      
      TO_CHAR---转换日期型或数值型为字符串。最重要的函数之一.其FORMAT格式多种多样
      格式∶TO_CHAR(DATE [,’FORMAT’])
      FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
      P836 日期格式元素
      
      TO_NUMBER---转换字符串为数字
      格式∶TO_NUMBER(string [ , format])
      FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
       
      6)其他

      DECODE---IF语句的另一形式。将输入数值与参数列表比较,返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。当与SIGN联合使用时功能扩展,可以判断大于小于的情况.
      格式: 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---以字节为单位返回数据类型尺寸
      格式∶VSIZE(数据类型)
      例∶ VSIZE(SYSDATE)=8
      
      USER---得到当前用户名
      
      ROWNUM--- oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数。不能以任何基表的名称作为前缀。可以用在限制返回记录条数的地方不受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---数据行在物理磁盘上的物理地址,Oracle通过ROWID来定位数据的具体位置,这是存取表中数据的最快的方法.
      RETURN---用于函数和过程中.遇见RETURN程序立刻中断,返回,不再执行剩余部分.
      SQLCODE---返回ORACLE错误号。
      SQLERRM---返回ORACLE错误信息。

    ----------------------------------------------------------------------------------------------------------
    1、Dateadd
      在向指定日期加上一段时间的基础上,返回新的   datetime   值。   
      语法 :DATEADD(datepart,number,date)     
      参数
      datepart   
      是规定应向日期的哪一部分返回新值的参数。下表列出了识别的日期部分和缩写。   
      日期部分   缩写     
      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的值。如果指定一个不是整数的值,则将废弃此值的小数部分。
      例如,如果为datepart指定day,为number指定1.75,则date将增加1。   
        
      date    
      是返回datetime或smalldatetime值或日期格式字符串的表达式。有关指定日期的更多信息,请参见datetime和smalldatetime。   
      如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所
      在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。
      例如,如果two  digit year cutoff 为 2049(默认),则 49 被解释为 2049,2050 被解释为 1950。
      为避免模糊,请使用四位数的年份。   
        
      返回类型   
      返回   datetime,但如果   date   参数是   smalldatetime,返回   smalldatetime。 
      
    2、DATEDIFF 
       返回跨两个指定日期的日期和时间边界数。 
       一、 语法 
       DATEDIFF ( datepart , startdate , enddate ) 
       二、参数 
       datepart 
       是规定了应在日期的哪一部分计算差额的参数。下表列出了 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。 
       如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果 two digit year cutoff 为 2049(默认),则 49 被解释为 2049,2050 被解释为 1950。为避免模糊,请使用四位数的年份。 
        
       enddate 
       是计算的终止日期。enddate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。 
        
       三、返回类型 
       integer 
       四、用法
        此函数计算两个指定日期之间日期部分的数目。结果为日期部分中等于(date2 - date1)的有符号的整数值。
        当结果不是日期部分的偶数倍时,DATEDIFF 将被截断而不是被舍入。
        当使用 day 作为日期部分时,DATEDIFF 返回两个指定的时间之间(包括第二个日期但不包括第一个日期)的午夜数。
        当使用 month 作为日期部分时,DATEDIFF 返回两个日期之间(包括第二个日期但不包括第一个日期)出现的月的第一天的数目。
        当使用 week 作为日期部分时,DATEDIFF 返回两个日期(包括第二个日期但不包括第一个日期)之间星期日的数目。
        对于更小的时间单位存在溢出值:

    milliseconds    24 天 
    seconds    68 年 
    minutes    4083 年 
    others    没有溢出限制 
    如果超出这些限制,此函数将返回溢出错误。
        五、标准和兼容性
    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函数
      decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
      
      该函数的含义如下:
      
      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()函数根据某个值是0、正数还是负数,分别返回0、1、-1
      
      例如:
      变量1=10,变量2=20
      则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。
      
      2、表、视图结构转化
      
      现有一个商品销售表sale,表结构为:
      
      month    char(6)      --月份
      sell    number(10,2)   --月销售金额
      
      现有数据为:
      
      200001  1000
      200002  1100
      200003  1200
      200004  1300
      200005  1400
      200006  1500
      200007  1600
      200101  1100
      200202  1200
      200301  1300
      
      想要转化为以下结构的数据:
      
      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
     将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
     语法
     使用 CAST:
     CAST ( expression AS data_type )
     使用 CONVERT:
     CONVERT (data_type[(length)], expression [, style])
     参数
     expression
     是任何有效的 Microsoft? SQL Server? 表达式。有关更多信息,请参见表达式。
     data_type
     目标系统所提供的数据类型,包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息,请参见数据类型。
     length
     nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。
     
     style
     日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。
     
     SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。
     
     在表中,左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。
     
     不带世纪数位 (yy) 带世纪数位 (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 规范 yyyy-mm-dd hh:mm:ss[.fff] 
     - 21 或 121 (*) ODBC 规范(带毫秒) 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)始终返回世纪数位 (yyyy)。
     ** 当转换为 datetime 时输入;当转换为字符数据时输出。
     *** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换,输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换,输出等同于 style 2。对于从 real 到 character 数据的转换,输出等同于 style 1。
     
     
     
     重要 默认情况下,SQL Server 根据截止年份 2049 解释两位数字的年份。即,两位数字的年份 49 被解释为 2049,而两位数字的年份 50 被解释为 1950。许多客户端应用程序(例如那些基于 OLE 自动化对象的客户端应用程序)都使用 2030 作为截止年份。SQL Server 提供一个配置选项("两位数字的截止年份"),借以更改 SQL Server 所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。
     
     
     当从 smalldatetime 转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时,可以通过使用适当的 char 或 varchar 数据类型长度来截断不需要的日期部分。
     
     下表显示了从 float 或 real 转换为字符数据时的 style 值。
     
     值 输出 
     0(默认值) 最大为 6 位数。根据需要使用科学记数法。 
     1 始终为 8 位值。始终使用科学记数法。 
     2 始终为 16 位值。始终使用科学记数法。
     
     
     在下表中,左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。
     
     值 输出 
     0(默认值) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。 
     1 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。 
     2 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。 
     
     
     返回类型
     返回与 data type 0 相同的值。
     
     
     
    5、CHARINDEX函数
        返回字符或者字符串在另一个字符串中的起始位置。CHARINDEX函数调用方法如下: 
           CHARINDEX ( expression1 , expression2 [ , start_location ] )

           Expression1是要到expression2中寻找的字符中,start_location是CHARINDEX函数开始在expression2中找expression1的位置。

           CHARINDEX函数返回一个整数,返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串,那么函数整数“0”。让我们看看下面的函数命令执行的结果:

          CHARINDEX('SQL', 'Microsoft SQL Server')

          这个函数命令将返回在“Microsoft SQL Server”中“SQL”的起始位置,在这个例子中,CHARINDEX函数将返回“S”在“Microsoft SQL Server”中的位置11。
         接下来,我们看这个CHARINDEX命令:

          CHARINDEX('7.0', 'Microsoft SQL Server 2000')

          在这个例子中,CHARINDEX返回零,因为字符串“7.0” 不能在“Microsoft SQL Server”中被找到。接下来通过两个例子来看看如何使用CHARINDEX函数来解决实际的T-SQL问题。

    6、DATEPART
     返回代表指定日期的指定日期部分的整数。
     
     语法
     DATEPART ( datepart ,date ) 
     
     参数
     datepart
     
     是指定应返回的日期部分的参数。下表列出了 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 日期部分的开始数字,例如:DATEPART(wk, 'Jan 1, xxxx') = 1,此处 xxxx 代表任一年。
     
     weekday (dw) 日期部分返回对应于星期中的某天的数,例如:Sunday = 1、Saturday = 7。weekday 日期部分产生的数取决于 SET DATEFIRST 设定的值,此命令设定星期中的第一天。
     
     date
     
     是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。对 1753 年 1 月 1 日之后的日期用datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时,始终将其放入引号中。因为 smalldatetime 只精确到分钟,所以当用 smalldatetime 值时,秒和毫秒总是 0。
     
     如果只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果 two digit year cutoff 为 2049 (默认),则 49 被解释为 2049,2050 被解释为 1950。为避免模糊,请使用四位数的年份。
     
     有关时间值指定的更多信息,请参见时间格式。有关日期指定的更多信息,请参见 datetime 和 smalldatetime。
     
     返回类型
     int
     
     注释
     DAY、MONTH、和 YEAR 函数分别是 DATEPART(dd, date)、DATEPART(mm, date)、和 DATEPART(yy, date) 的同义词。
     
     示例
     GETDATE 函数返回当前日期;然而,比较时并不总是需要完整的日期信息(通常只是对日期的一部分进行比较)。此示例显示 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)
     
     在此示例中,以数字表示日期。注意: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 函数是用来抓出一个栏位资料中的其中一部分。这个函数的名称在不同的资料库中不完全一样: 
     MySQL: SUBSTR(), SUBSTRING() 
     Oracle: SUBSTR() 
     SQL Server: SUBSTRING() 
     参数:
     expression 字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。
     start 整数或可以隐式转换为 int 的表达式,指定子字符串的开始位置。
     length 整数或可以隐式转换为 int 的表达式,指定子字符串的长度。 
     返回值: 
     如果 expression 是一种支持的字符数据类型,则返回字符数据。如果 expression 是一种支持的二进制数据类型,则返回二进制数据。如果 start = 1,则子字符串从表达式的第一个字符开始。 
     返回字符串的类型与给定表达式的类型相同(下表所示内容除外)。
     给定表达式  返回类型  
     image  varbinary 
     ntext   nvarchar 
     
    8、stuff
     stuff的功能:删除指定长度的字符串并在指定的起始点插入另一组字符
     STUFF ( character_expression , start , length , character_expression ) 
     character_expression :操作的字符,
     start:删除和插入的起始点,
     length:删除的长度,
     character_expression :要插入的字符
     注:sql的stuff()函数和编程中数组的下标起始位置不一样,数组是以0开始的,而 stuff()函数是以 1 开始的。
     如果 stuff() 函数以 0 开始的话,它是从expression1_Str 字符串的最后一个字符开始截取。例子如下所示:
        例1:stuff('00000000',0,3,'123') --> 对应的结果是: (没有值输出)
        例2:stuff('00000000',1,3,'123') --> 对应的结果是:12300000


    9、ceiling函数   floor函数    
     ceiling函数返回大于或等于所给数字表达式的最小整数。
     floor函数返回小于或等于所给数字表达式的最大整数。
     比如
     celling(12.1) 结果为 13
     floor(12.1)结果为 12
     
    在介绍这个之前你必须明白什么是oracle中的空值null

    1.NVL函数

    NVL函数的格式如下:NVL(expr1,expr2)

    含义是:如果oracle第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值。

    例如:

    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函数

    NVL2函数的格式如下:NVL2(expr1,expr2, expr3)

    含义是:如果该函数的第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第三个参数的值。

    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函数

    NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL),否则返回第一个值。

    下面是一个例子。使用的是oracle中HR schema,如果HR处于锁定,请启用

    这里的作用是显示出那些换过工作的人员原工作,现工作。

    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函数

    Coalese函数的作用是的NVL的函数有点相似,其优势是有更多的选项。

    格式如下:

    Coalesce(expr1, expr2, expr3….. exprn)

    Coalesce是这样来处理这些参数的。如果第一个参数为空,则看第二个参数是否是空,否则则显示第一个参数,如果第二个参数是空再看第三个参数是否为空,否则显示第二个参数,依次类推。

    这个函数实际上是NVL的循环使用,在此就不举例子了


    SQL语句基础

    --数据操作
    SELECT      --从数据库表中 检索数据行和列
    INSERT      --向数据库表 添加新数据行
    DELETE      --从数据库表中 删除数据行
    UPDATE      --更新 数据库表中的数据
    --数据定义
    CREATE TABLE    --创建一个数据库表
    DROP TABLE     --从数据库中删除表
    ALTER TABLE     --修改数据库表结构
    CREATE VIEW     --创建一个视图
    DROP VIEW     --从数据库中删除视图
    CREATE INDEX    --为数据库表创建一个索引
    DROP INDEX     --从数据库中删除索引
    CREATE PROCEDURE   --创建一个存储过程
    DROP PROCEDURE    --从数据库中删除存储过程
    CREATE TRIGGER    --创建一个触发器
    DROP TRIGGER    --从数据库中删除触发器
    CREATE SCHEMA    --向数据库添加一个新模式
    DROP SCHEMA     --从数据库中删除一个模式
    CREATE DOMAIN    --创建一个数据值域
    ALTER DOMAIN    --改变域定义
    DROP DOMAIN     --从数据库中删除一个域
    --数据控制
    GRANT      --授予用户访问权限
    DENY      --拒绝用户访问
    REVOKE      --解除用户访问权限
    --事务控制
    COMMIT      --结束当前事务
    ROLLBACK     --中止当前事务
    SET TRANSACTION    --定义当前事务数据访问特征
    --程序化SQL
    DECLARE      --为查询设定游标
    EXPLAN      --为查询描述数据访问计划
    OPEN      --检索查询结果打开一个游标
    FETCH      --检索一行查询结果
    CLOSE      --关闭游标
    PREPARE      --为动态执行准备SQL 语句
    EXECUTE      --动态地执行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 = 100*@x + @y
        print @c --打印变量c 的值
        select @y = @y + 1
       end
      select @x = @x + 1
      select @y = 1
     end

    --WAITFOR
    --例 等待1 小时2 分零3 秒后才执行SELECT 语句
    waitfor delay ’01:02:03’
    select * from employee
    --例 等到晚上11 点零8 分后才执行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*** --- 修改数据库表结构

      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 ......... ----- 实现删除列的方法(创建新表)
      alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束
        
    ***function(/*常用函数*/)***

    ----统计函数----
    AVG    --求平均值
    COUNT   --统计数目
    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()函数返回表达式中所有数据的标准差

    --STDEVP()
    --STDEVP()函数返回总体标准差

    --VAR()
    --VAR()函数返回表达式中所有值的统计变异数

    --VARP()
    --VARP()函数返回总体变异数

    ----算术函数----

    /***三角函数***/
    SIN(float_expression) --返回以弧度表示的角的正弦
    COS(float_expression) --返回以弧度表示的角的余弦
    TAN(float_expression) --返回以弧度表示的角的正切
    COT(float_expression) --返回以弧度表示的角的余切
    /***反三角函数***/
    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)
                           --把弧度转换为角度返回与表达式相同的数据类型可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    EXP(float_expression)  --返回表达式的指数值
    LOG(float_expression)  --返回表达式的自然对数值
    LOG10(float_expression)--返回表达式的以10 为底的对数值
    SQRT(float_expression) --返回表达式的平方根
    /***取近似值函数***/
    CEILING(numeric_expression)  --返回>=表达式的最小整数返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    FLOOR(numeric_expression)    --返回<=表达式的最小整数返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    ROUND(numeric_expression)    --返回以integer_expression 为精度的四舍五入值返回的数据
            --类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
    ABS(numeric_expression)      --返回表达式的绝对值返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    SIGN(numeric_expression)     --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型
            --与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
    PI()       --返回值为π 即3.1415926535897936
    RAND([integer_expression])   --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数

     

    ----字符串函数----
    ASCII()         --函数返回字符表达式最左端字符的ASCII 码值
    CHAR()   --函数用于将ASCII 码转换为字符
             --如果没有输入0~255之间的ASCII码值,CHAR函数会返回一个NULL值
    LOWER()   --函数把字符串全部转换为小写
    UPPER()   --函数把字符串全部转换为大写
    STR()   --函数把数值型数据转换为字符型数据
    LTRIM()   --函数把字符串头部的空格去掉
    RTRIM()   --函数把字符串尾部的空格去掉
    LEFT(),RIGHT(),SUBSTRING()  --函数返回部分字符串
    RIGHT 函数 [字符串]
    --------------------------------------------------------------------------------
      功能 
      返回字符串中最右边的字符。
      语法 
      RIGHT ( string-expression, integer-expression )
      参数 
      string-expression    从左边截断的字符串。
      integer-expression    要返回的从字符串结尾处开始计数的字符数。
      用法 
      如果字符串包含多字节字符,并且使用了适当的归类,则返回的字节数可能大于指定的字符数。

    CHARINDEX(),PATINDEX()  --函数返回字符串中某个指定的子串出现的开始位置
    SOUNDEX()  --函数返回一个四位字符码 
        --SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值     
    DIFFERENCE()    --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
        --0 两个SOUNDEX 函数返回值的第一个字符不同
        --1 两个SOUNDEX 函数返回值的第一个字符相同
        --2 两个SOUNDEX 函数返回值的第一二个字符相同
        --3 两个SOUNDEX 函数返回值的第一二三个字符相同
        --4 两个SOUNDEX 函数返回值完全相同
                                         

    REPLICATE()     --函数返回一个重复character_expression 指定次数的字符串
    /*select replicate('abc', 3) replicate( 'abc', -2)
    运行结果如下
    ----------------------
    abcabcabc NULL*/

    REVERSE()       --函数将指定的字符串的字符排列顺序颠倒
    REPLACE()       --函数返回被替换了指定子串的字符串
    /*select replace('abc123g', '123', 'def')
    运行结果如下
    ----------------------
    abcdefg*/


    SPACE()   --函数返回一个有指定长度的空白字符串
    STUFF()   --函数用另一子串替换字符串指定位置长度的子串


    ----数据类型转换函数----
    CAST() 函数语法如下
    CAST() (<expression> AS <data_ type>[ length ])
    CONVERT() 函数语法如下
    CONVERT() (<data_ type>[ length ], <expression> [, style])

    select cast(100+99 as char) convert(varchar(12), getdate())
    运行结果如下
    -----------------------
    199   Jan 15 2000

    ----日期函数----
    DAY()   --函数返回date_expression 中的日期值
    MONTH()   --函数返回date_expression 中的月份值
    YEAR()   --函数返回date_expression 中的年份值
    DATEADD(<datepart> ,<number> ,<date>)   --函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
    DATEDIFF(<datepart> ,<number> ,<date>)   --函数返回两个指定日期在datepart 方面的不同之处
    DATENAME(<datepart> , <date>)  --函数以字符串的形式返回日期的指定部分
    DATEPART(<datepart> , <date>)  --函数以整数值的形式返回日期的指定部分
    GETDATE()  --函数以DATETIME 的缺省格式返回系统当前的日期和时间

    ----系统函数----
    APP_NAME()  --函数返回当前执行的应用程序的名称
    COALESCE()  --函数返回众多表达式中第一个非NULL 表达式的值
    COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
    COL_NAME(<table_id>, <column_id>)   --函数返回表中指定字段的名称即列名
    DATALENGTH() --函数返回数据表达式的数据的实际长度
    DB_ID(['database_name']) --函数返回数据库的编号
    DB_NAME(database_id)  --函数返回数据库的名称
    HOST_ID()     --函数返回服务器端计算机的名称
    HOST_NAME()     --函数返回服务器端计算机的名称
    IDENTITY(<data_type>[, seed increment]) [AS column_name])   --IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
    /*select identity(int, 1, 1) as column_name
     into newtable
     from oldtable*/
    ISDATE()  --函数判断所给定的表达式是否为合理日期
    ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL值用指定值替换
    ISNUMERIC()  --函数判断所给定的表达式是否为合理的数值
    NEWID()   --函数返回一个UNIQUEIDENTIFIER 类型的数值
    NULLIF(<expression1>, <expression2>)   --NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1的值

    展开全文
  • plsql 常用函数-转

    2020-10-30 16:09:11
    PLSQL常用函数 1)处理字符的函数  || 或 CONCAT---并置运算符。  格式∶CONCAT(STRING1, STRING2)  例:’ABC’|| ’DE’=’ABCDE’  CONCAT(‘ABC’,’DE’) =’ABCDE’    ASCII---返回字符的ASCII码。 ...
     PLSQL常用函数
    
        1)处理字符的函数
    
      || 或 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[ , 开始位置[ , 出现次数]])
      例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
      
       INITCAP---将字符串每个单词首字母均变为大写
      例: INITCAP(‘this is a test’)=’ This Is A Test’’
      
      LENGTH----计算串长
      格式∶ LENGTH(string)
      
      RPAD,LPAD---右填充、左填充。默认为填充空格。
      格式: RPAD(字符串 , 字符个数 , 填充字符)
      例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
      
      LTRIM,RTRIM-----左右截断。默认为删除空格。 
      格式∶ LTRIM(STRING[,SET])
      例∶ LTRIM(***tes*t***,*)=’ tes*t***’
       
      LOWER----将字符串转换为小写
      格式∶LOWER(string)
      
      UPPER---将字符串转换为大写
      格式∶UPPER(string)
      
      SUBSTR----提取子串。START为正数时从左开始、为负数时从右开始
      格式∶ 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时针对字节.
    
      2)处理数字的函数
    
      LEAST---返回参数列表中的最小值。返回参数类型以第一参数为准
      格式∶LEAST(value,value,value,value,value,.)value为数字或字符串
      例∶ LEAST(1,2,5,-10,9)= -10
      
      SIGN---返回参数的符号位,负数--- -1,0----0,正数---1
      格式∶SIGN(value)
       
      CELL---返回大于等于特定值的最小整数
      格式∶CELL(value)
      例∶ CELL(-10,9)= -10
    
      3)处理日期
    
      SYSDATE---系统时间。精确至秒
      
      ADD_MONTH—加减月份。numvalue为负数时减去相应月份
      格式: ADD_MONTH (data1,numvalue)
      
      MONTHS_BETWEEN---返回两日期之间的月数,当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)分组函数
    
      返回基于多个行的单一结果.
      常用函数:
      AVG---求平均值
      COUNT---返回查询的行数
      MAX---返回查询列的最大值
      MIN---返回查询列的最小值
      SUM---返回查询列的总和
      MAX, MIN常与GROUP BY配套使用
    
      5)转换函数
    
      TO_DATE---转换字符串为日期型
      格式∶ TO_DATE(STRING[,’FORMAT’])
      
      TO_CHAR---转换日期型或数值型为字符串。最重要的函数之一.其FORMAT格式多种多样
      格式∶TO_CHAR(DATE [,’FORMAT’])
      FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
      P836 日期格式元素
      
      TO_NUMBER---转换字符串为数字
      格式∶TO_NUMBER(string [ , format])
      FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
       
      6)其他
    
      DECODE---IF语句的另一形式。将输入数值与参数列表比较,返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。当与SIGN联合使用时功能扩展,可以判断大于小于的情况.
      格式: 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’)valueNULL值时返回nullvalue否则返回value的值
      
      VSIZE---以字节为单位返回数据类型尺寸
      格式∶VSIZE(数据类型)
      例∶ VSIZE(SYSDATE)=8
      
      USER---得到当前用户名
      
      ROWNUM--- oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数。不能以任何基表的名称作为前缀。可以用在限制返回记录条数的地方不受ORDER BY的影响.
      例∶ select rownum,month,sell from sale where rownum=1
      或select rownum,month,sell from sale where rownum<2(返回第一条记录)
      返回rownum在410之间的记录
      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---数据行在物理磁盘上的物理地址,Oracle通过ROWID来定位数据的具体位置,这是存取表中数据的最快的方法.
      RETURN---用于函数和过程中.遇见RETURN程序立刻中断,返回,不再执行剩余部分.
      SQLCODE---返回ORACLE错误号。
      SQLERRM---返回ORACLE错误信息。
    
    ----------------------------------------------------------------------------------------------------------
    1、Dateadd
      在向指定日期加上一段时间的基础上,返回新的   datetime   值。   
      语法 :DATEADD(datepart,number,date)     
      参数
      datepart   
      是规定应向日期的哪一部分返回新值的参数。下表列出了识别的日期部分和缩写。   
      日期部分   缩写     
      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的值。如果指定一个不是整数的值,则将废弃此值的小数部分。
      例如,如果为datepart指定day,为number指定1.75,则date将增加1date    
      是返回datetime或smalldatetime值或日期格式字符串的表达式。有关指定日期的更多信息,请参见datetime和smalldatetime。   
      如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所
      在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。
      例如,如果two  digit year cutoff 为 2049(默认),则 49 被解释为 20492050 被解释为 1950。
      为避免模糊,请使用四位数的年份。   
        
      返回类型   
      返回   datetime,但如果   date   参数是   smalldatetime,返回   smalldatetime。 
      
    2、DATEDIFF 
       返回跨两个指定日期的日期和时间边界数。 
       一、 语法 
       DATEDIFF ( datepart , startdate , enddate ) 
       二、参数 
       datepart 
       是规定了应在日期的哪一部分计算差额的参数。下表列出了 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。 
       如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果 two digit year cutoff 为 2049(默认),则 49 被解释为 20492050 被解释为 1950。为避免模糊,请使用四位数的年份。 
        
       enddate 
       是计算的终止日期。enddate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。 
        
       三、返回类型 
       integer 
       四、用法
        此函数计算两个指定日期之间日期部分的数目。结果为日期部分中等于(date2 - date1)的有符号的整数值。
        当结果不是日期部分的偶数倍时,DATEDIFF 将被截断而不是被舍入。
        当使用 day 作为日期部分时,DATEDIFF 返回两个指定的时间之间(包括第二个日期但不包括第一个日期)的午夜数。
        当使用 month 作为日期部分时,DATEDIFF 返回两个日期之间(包括第二个日期但不包括第一个日期)出现的月的第一天的数目。
        当使用 week 作为日期部分时,DATEDIFF 返回两个日期(包括第二个日期但不包括第一个日期)之间星期日的数目。
        对于更小的时间单位存在溢出值:
    
    milliseconds    24 天 
    seconds    68 年 
    minutes    4083 年 
    others    没有溢出限制 
    如果超出这些限制,此函数将返回溢出错误。
        五、标准和兼容性
    SQL/92    Transact-SQL 扩展。
    
    SQL/99    Transact-SQL 扩展。
    
    Sybase    与 Adaptive Server Enterprise 兼容。
    
       六、示例
        下面的语句返回 1select datediff( hour, ''4:00am'', ''5:50am'' )
        下面的语句返回 102select datediff( month, ''1987/05/02'', ''1995/11/15'' )
        下面的语句返回 0select datediff( day, ''00:00'', ''23:59'' )
        下面的语句返回 4select datediff( day, ''1999/07/19 00:00'',''1999/07/23 23:59'' )
        下面的语句返回 0select datediff( month, ''1999/07/19'', ''1999/07/23'' )
        下面的语句返回 1select datediff( month, ''1999/07/19'', ''1999/08/23'' )
    
     
    
    3、decode函数
      decode(条件,1,翻译值1,2,翻译值2,...值n,翻译值n,缺省值)
      
      该函数的含义如下:
      
      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()函数根据某个值是0、正数还是负数,分别返回01-1
      
      例如:
      变量1=10,变量2=20
      则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。
      
      2、表、视图结构转化
      
      现有一个商品销售表sale,表结构为:
      
      month    char(6)      --月份
      sell    number(10,2)   --月销售金额
      
      现有数据为:
      
      200001  1000
      200002  1100
      200003  1200
      200004  1300
      200005  1400
      200006  1500
      200007  1600
      200101  1100
      200202  1200
      200301  1300
      
      想要转化为以下结构的数据:
      
      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
     将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
     语法
     使用 CAST:
     CAST ( expression AS data_type )
     使用 CONVERTCONVERT (data_type[(length)], expression [, style])
     参数
     expression
     是任何有效的 Microsoft? SQL Server? 表达式。有关更多信息,请参见表达式。
     data_type
     目标系统所提供的数据类型,包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息,请参见数据类型。
     length
     nchar、nvarchar、charvarcharbinaryvarbinary 数据类型的可选参数。
     
     style
     日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、charvarcharnchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 floatreal、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、charvarcharnchar 或 nvarchar 数据类型)。
     
     SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。
     
     在表中,左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。
     
     不带世纪数位 (yy) 带世纪数位 (yyyy) 
     标准 
     输入/输出** 
     - 0100 (*) 默认值 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 
     - 9109 (*) 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM) 
     10 110 美国 mm-dd-yy 
     11 111 日本 yy/mm/dd 
     12 112 ISO yymmdd 
     - 13113 (*) 欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h) 
     14 114 - hh:mi:ss:mmm(24h) 
     - 20120 (*) ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff] 
     - 21121 (*) ODBC 规范(带毫秒) 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 01009109131132012021121)始终返回世纪数位 (yyyy)** 当转换为 datetime 时输入;当转换为字符数据时输出。
     *** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换,输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换,输出等同于 style 2。对于从 realcharacter 数据的转换,输出等同于 style 1。
     
     
     
     重要 默认情况下,SQL Server 根据截止年份 2049 解释两位数字的年份。即,两位数字的年份 49 被解释为 2049,而两位数字的年份 50 被解释为 1950。许多客户端应用程序(例如那些基于 OLE 自动化对象的客户端应用程序)都使用 2030 作为截止年份。SQL Server 提供一个配置选项("两位数字的截止年份"),借以更改 SQL Server 所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。
     
     
     当从 smalldatetime 转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时,可以通过使用适当的 charvarchar 数据类型长度来截断不需要的日期部分。
     
     下表显示了从 floatreal 转换为字符数据时的 style 值。
     
     值 输出 
     0(默认值) 最大为 6 位数。根据需要使用科学记数法。 
     1 始终为 8 位值。始终使用科学记数法。 
     2 始终为 16 位值。始终使用科学记数法。
     
     
     在下表中,左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。
     
     值 输出 
     0(默认值) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.981 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.922 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。 
     
     
     返回类型
     返回与 data type 0 相同的值。
     
     
     
    5、CHARINDEX函数
        返回字符或者字符串在另一个字符串中的起始位置。CHARINDEX函数调用方法如下: 
           CHARINDEX ( expression1 , expression2 [ , start_location ] )
    
           Expression1是要到expression2中寻找的字符中,start_location是CHARINDEX函数开始在expression2中找expression1的位置。
    
           CHARINDEX函数返回一个整数,返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串,那么函数整数“0”。让我们看看下面的函数命令执行的结果:
    
          CHARINDEX('SQL', 'Microsoft SQL Server')
    
          这个函数命令将返回在“Microsoft SQL Server”中“SQL”的起始位置,在这个例子中,CHARINDEX函数将返回“S”在“Microsoft SQL Server”中的位置11。
         接下来,我们看这个CHARINDEX命令:
    
          CHARINDEX('7.0', 'Microsoft SQL Server 2000')
    
          在这个例子中,CHARINDEX返回零,因为字符串“7.0” 不能在“Microsoft SQL Server”中被找到。接下来通过两个例子来看看如何使用CHARINDEX函数来解决实际的T-SQL问题。
    
    6、DATEPART
     返回代表指定日期的指定日期部分的整数。
     
     语法
     DATEPART ( datepart ,date ) 
     
     参数
     datepart
     
     是指定应返回的日期部分的参数。下表列出了 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 作的更改。任何一年的 11 日定义了 week 日期部分的开始数字,例如:DATEPART(wk, 'Jan 1, xxxx') = 1,此处 xxxx 代表任一年。
     
     weekday (dw) 日期部分返回对应于星期中的某天的数,例如:Sunday = 1、Saturday = 7。weekday 日期部分产生的数取决于 SET DATEFIRST 设定的值,此命令设定星期中的第一天。
     
     date
     
     是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。对 175311 日之后的日期用datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时,始终将其放入引号中。因为 smalldatetime 只精确到分钟,所以当用 smalldatetime 值时,秒和毫秒总是 0。
     
     如果只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果 two digit year cutoff 为 2049 (默认),则 49 被解释为 20492050 被解释为 1950。为避免模糊,请使用四位数的年份。
     
     有关时间值指定的更多信息,请参见时间格式。有关日期指定的更多信息,请参见 datetime 和 smalldatetime。
     
     返回类型
     int
     
     注释
     DAYMONTH、和 YEAR 函数分别是 DATEPART(dd, date)、DATEPART(mm, date)、和 DATEPART(yy, date) 的同义词。
     
     示例
     GETDATE 函数返回当前日期;然而,比较时并不总是需要完整的日期信息(通常只是对日期的一部分进行比较)。此示例显示 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
     
     此示例假设日期是 529 日。
     
     SELECT DATEPART(month, GETDATE())
     GO
     
     下面是结果集:
     
     -----------
     5
     (1 row(s) affected)
     
     在此示例中,以数字表示日期。注意:SQL Server 将 0 解释为 01/01/1900SELECT 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 函数是用来抓出一个栏位资料中的其中一部分。这个函数的名称在不同的资料库中不完全一样: 
     MySQL: SUBSTR(), SUBSTRING() 
     Oracle: SUBSTR() 
     SQL Server: SUBSTRING() 
     参数:
     expression 字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。
     start 整数或可以隐式转换为 int 的表达式,指定子字符串的开始位置。
     length 整数或可以隐式转换为 int 的表达式,指定子字符串的长度。 
     返回值: 
     如果 expression 是一种支持的字符数据类型,则返回字符数据。如果 expression 是一种支持的二进制数据类型,则返回二进制数据。如果 start = 1,则子字符串从表达式的第一个字符开始。 
     返回字符串的类型与给定表达式的类型相同(下表所示内容除外)。
     给定表达式  返回类型  
     image  varbinary 
     ntext   nvarchar 
     
    8、stuff
     stuff的功能:删除指定长度的字符串并在指定的起始点插入另一组字符
     STUFF ( character_expression , start , length , character_expression ) 
     character_expression :操作的字符,
     start:删除和插入的起始点,
     length:删除的长度,
     character_expression :要插入的字符
     注:sql的stuff()函数和编程中数组的下标起始位置不一样,数组是以0开始的,而 stuff()函数是以 1 开始的。
     如果 stuff() 函数以 0 开始的话,它是从expression1_Str 字符串的最后一个字符开始截取。例子如下所示:
        例1:stuff('00000000',0,3,'123') --> 对应的结果是: (没有值输出)2:stuff('00000000',1,3,'123') --> 对应的结果是:12300000
    
    
    9、ceiling函数   floor函数    
     ceiling函数返回大于或等于所给数字表达式的最小整数。
     floor函数返回小于或等于所给数字表达式的最大整数。
     比如
     celling(12.1) 结果为 13
     floor(12.1)结果为 12
     
    在介绍这个之前你必须明白什么是oracle中的空值null
    
    1.NVL函数
    
    NVL函数的格式如下:NVL(expr1,expr2)
    
    含义是:如果oracle第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值。
    
    例如:
    
    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函数
    
    NVL2函数的格式如下:NVL2(expr1,expr2, expr3)
    
    含义是:如果该函数的第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第三个参数的值。
    
    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函数
    
    NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL),否则返回第一个值。
    
    下面是一个例子。使用的是oracle中HR schema,如果HR处于锁定,请启用
    
    这里的作用是显示出那些换过工作的人员原工作,现工作。
    
    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函数
    
    Coalese函数的作用是的NVL的函数有点相似,其优势是有更多的选项。
    
    格式如下:
    
    Coalesce(expr1, expr2, expr3….. exprn)
    
    Coalesce是这样来处理这些参数的。如果第一个参数为空,则看第二个参数是否是空,否则则显示第一个参数,如果第二个参数是空再看第三个参数是否为空,否则显示第二个参数,依次类推。
    
    这个函数实际上是NVL的循环使用,在此就不举例子了
    
    
    SQL语句基础
    
    --数据操作
    SELECT      --从数据库表中 检索数据行和列
    INSERT      --向数据库表 添加新数据行
    DELETE      --从数据库表中 删除数据行
    UPDATE      --更新 数据库表中的数据
    --数据定义
    CREATE TABLE    --创建一个数据库表
    DROP TABLE     --从数据库中删除表
    ALTER TABLE     --修改数据库表结构
    CREATE VIEW     --创建一个视图
    DROP VIEW     --从数据库中删除视图
    CREATE INDEX    --为数据库表创建一个索引
    DROP INDEX     --从数据库中删除索引
    CREATE PROCEDURE   --创建一个存储过程
    DROP PROCEDURE    --从数据库中删除存储过程
    CREATE TRIGGER    --创建一个触发器
    DROP TRIGGER    --从数据库中删除触发器
    CREATE SCHEMA    --向数据库添加一个新模式
    DROP SCHEMA     --从数据库中删除一个模式
    CREATE DOMAIN    --创建一个数据值域
    ALTER DOMAIN    --改变域定义
    DROP DOMAIN     --从数据库中删除一个域
    --数据控制
    GRANT      --授予用户访问权限
    DENY      --拒绝用户访问
    REVOKE      --解除用户访问权限
    --事务控制
    COMMIT      --结束当前事务
    ROLLBACK     --中止当前事务
    SET TRANSACTION    --定义当前事务数据访问特征
    --程序化SQL
    DECLARE      --为查询设定游标
    EXPLAN      --为查询描述数据访问计划
    OPEN      --检索查询结果打开一个游标
    FETCH      --检索一行查询结果
    CLOSE      --关闭游标
    PREPARE      --为动态执行准备SQL 语句
    EXECUTE      --动态地执行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 =1then e_wage*1.08
      when job_level =2then e_wage*1.07
      when job_level =3then 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 = 100*@x + @y
        print @c --打印变量c 的值
        select @y = @y + 1
       end
      select @x = @x + 1
      select @y = 1
     end
    
    --WAITFOR
    --例 等待1 小时2 分零3 秒后才执行SELECT 语句
    waitfor delay ’01:02:03select * from employee
    --例 等到晚上11 点零8 分后才执行SELECT 语句
    waitfor time23:08:00select * 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*** --- 修改数据库表结构
    
      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 ......... ----- 实现删除列的方法(创建新表)
      alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束
        
    ***function(/*常用函数*/)***
    
    ----统计函数----
    AVG    --求平均值
    COUNT   --统计数目
    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()函数返回表达式中所有数据的标准差
    
    --STDEVP()
    --STDEVP()函数返回总体标准差
    
    --VAR()
    --VAR()函数返回表达式中所有值的统计变异数
    
    --VARP()
    --VARP()函数返回总体变异数
    
    ----算术函数----
    
    /***三角函数***/
    SIN(float_expression) --返回以弧度表示的角的正弦
    COS(float_expression) --返回以弧度表示的角的余弦
    TAN(float_expression) --返回以弧度表示的角的正切
    COT(float_expression) --返回以弧度表示的角的余切
    /***反三角函数***/
    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)
                           --把弧度转换为角度返回与表达式相同的数据类型可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    EXP(float_expression)  --返回表达式的指数值
    LOG(float_expression)  --返回表达式的自然对数值
    LOG10(float_expression)--返回表达式的以10 为底的对数值
    SQRT(float_expression) --返回表达式的平方根
    /***取近似值函数***/
    CEILING(numeric_expression)  --返回>=表达式的最小整数返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    FLOOR(numeric_expression)    --返回<=表达式的最小整数返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    ROUND(numeric_expression)    --返回以integer_expression 为精度的四舍五入值返回的数据
            --类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
    ABS(numeric_expression)      --返回表达式的绝对值返回的数据类型与表达式相同可为
            --INTEGER/MONEY/REAL/FLOAT 类型
    SIGN(numeric_expression)     --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型
            --与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
    PI()       --返回值为π 即3.1415926535897936
    RAND([integer_expression])   --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数
    
     
    
    ----字符串函数----
    ASCII()         --函数返回字符表达式最左端字符的ASCII 码值
    CHAR()   --函数用于将ASCII 码转换为字符
             --如果没有输入0~255之间的ASCII码值,CHAR函数会返回一个NULL值
    LOWER()   --函数把字符串全部转换为小写
    UPPER()   --函数把字符串全部转换为大写
    STR()   --函数把数值型数据转换为字符型数据
    LTRIM()   --函数把字符串头部的空格去掉
    RTRIM()   --函数把字符串尾部的空格去掉
    LEFT(),RIGHT(),SUBSTRING()  --函数返回部分字符串
    RIGHT 函数 [字符串]
    --------------------------------------------------------------------------------
      功能 
      返回字符串中最右边的字符。
      语法 
      RIGHT ( string-expression, integer-expression )
      参数 
      string-expression    从左边截断的字符串。
      integer-expression    要返回的从字符串结尾处开始计数的字符数。
      用法 
      如果字符串包含多字节字符,并且使用了适当的归类,则返回的字节数可能大于指定的字符数。
    
    CHARINDEX(),PATINDEX()  --函数返回字符串中某个指定的子串出现的开始位置
    SOUNDEX()  --函数返回一个四位字符码 
        --SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值     
    DIFFERENCE()    --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
        --0 两个SOUNDEX 函数返回值的第一个字符不同
        --1 两个SOUNDEX 函数返回值的第一个字符相同
        --2 两个SOUNDEX 函数返回值的第一二个字符相同
        --3 两个SOUNDEX 函数返回值的第一二三个字符相同
        --4 两个SOUNDEX 函数返回值完全相同
                                         
    
    REPLICATE()     --函数返回一个重复character_expression 指定次数的字符串
    /*select replicate('abc', 3) replicate( 'abc', -2)
    运行结果如下
    ----------------------
    abcabcabc NULL*/
    
    REVERSE()       --函数将指定的字符串的字符排列顺序颠倒
    REPLACE()       --函数返回被替换了指定子串的字符串
    /*select replace('abc123g', '123', 'def')
    运行结果如下
    ----------------------
    abcdefg*/
    
    
    SPACE()   --函数返回一个有指定长度的空白字符串
    STUFF()   --函数用另一子串替换字符串指定位置长度的子串
    
    
    ----数据类型转换函数----
    CAST() 函数语法如下
    CAST() (<expression> AS <data_ type>[ length ])
    CONVERT() 函数语法如下
    CONVERT() (<data_ type>[ length ], <expression> [, style])
    
    select cast(100+99 as char) convert(varchar(12), getdate())
    运行结果如下
    -----------------------
    199   Jan 15 2000
    
    ----日期函数----
    DAY()   --函数返回date_expression 中的日期值
    MONTH()   --函数返回date_expression 中的月份值
    YEAR()   --函数返回date_expression 中的年份值
    DATEADD(<datepart> ,<number> ,<date>)   --函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
    DATEDIFF(<datepart> ,<number> ,<date>)   --函数返回两个指定日期在datepart 方面的不同之处
    DATENAME(<datepart> , <date>)  --函数以字符串的形式返回日期的指定部分
    DATEPART(<datepart> , <date>)  --函数以整数值的形式返回日期的指定部分
    GETDATE()  --函数以DATETIME 的缺省格式返回系统当前的日期和时间
    
    ----系统函数----
    APP_NAME()  --函数返回当前执行的应用程序的名称
    COALESCE()  --函数返回众多表达式中第一个非NULL 表达式的值
    COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
    COL_NAME(<table_id>, <column_id>)   --函数返回表中指定字段的名称即列名
    DATALENGTH() --函数返回数据表达式的数据的实际长度
    DB_ID(['database_name']) --函数返回数据库的编号
    DB_NAME(database_id)  --函数返回数据库的名称
    HOST_ID()     --函数返回服务器端计算机的名称
    HOST_NAME()     --函数返回服务器端计算机的名称
    IDENTITY(<data_type>[, seed increment]) [AS column_name])   --IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
    /*select identity(int, 1, 1) as column_name
     into newtable
     from oldtable*/
    ISDATE()  --函数判断所给定的表达式是否为合理日期
    ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL值用指定值替换
    ISNUMERIC()  --函数判断所给定的表达式是否为合理的数值
    NEWID()   --函数返回一个UNIQUEIDENTIFIER 类型的数值
    NULLIF(<expression1>, <expression2>)   --NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1的值
    
    工欲善其事,必先利其器。
    
    展开全文
  • PLSQL常用函数汇总

    2020-04-28 14:14:15
    常用字符函數 函數 说明 ASCII(X) 返回字符的ASCII碼 CONCAT(X, Y) 連接字符串X, Y INSTR(X, STR[,START][,N]) 从X中查找str,...

    常用字符函數

    函數

    说明

    ASCII(X)

    返回字符的ASCII碼

    CONCAT(X, Y)

    連接字符串X, Y

    INSTR(X, STR[,START][,N])

    从X中查找str,可以指定从start开始,也可以指定从n开始

    LTRIM(X[,TRIM_STR])

    把X的左边截去trim_str字符串,缺省截去空格

    RTRIM(X[,TRIM_STR])

    把X的右边截去trim_str字符串,缺省截去空格

    TRIM([TRIM_STR  FROM]X)

    把X的两边截去trim_str字符串,缺省截去空格

    REPLACE(X,old,new)

    在X中查找old,并替换成new

    SUBSTR(X,start[,length])

    返回X的字串,从start处开始,截取length个字符,缺省length,默认到结尾

    數字函數

    ABS(X)

    X的绝对值

    ACOS(X)

    X的反余弦

    COS(X)

    余弦

    CEIL(X)

    大于或等于X的最小值

    FLOOR(X)

    小于或等于X的最大值

    LOG(X,Y)

    X为底Y的对数

    MOD(X,Y)

    X除以Y的余数

    POWER(X,Y)

    X的Y次幂

    ROUND(X[,Y])

    X在第Y位四舍五入

    SQRT(X)

    X的平方根

    TRUNC(X[,Y])

    X在第Y位截断

    日期函數

    ADD_MONTHS(d,n)

    在某一个日期 d 上,加上指定的月数 n,返回计算后的新日期。

    d 表示日期(SYSDATE),n 表示要加的月数。

    LAST_DAY(d)

    返回指定日期当月的最后一天。

    ROUND(d[,fmt])

    返回一个以 fmt 为格式的四舍五入日期值, d 是日期, fmt 是格式

    模型。默认 fmt 为 DDD,即月中的某一天。

    EXTRACT(fmt FROM d)

    提取日期中的特定部分。

    fmt 为:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配,也可以与 TIMESTAMP 类型匹配;但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配

    轉換函數

    TO_CHAR(d|n[,fmt])

    把日期和数字转换为制定格式的字符串。Fmt是格式化字符串

    TO_DATE(X,[,fmt])

    把一个字符串以fmt格式转换成一个日期类型

    TO_NUMBER(X,[,fmt])

    把一个字符串以fmt格式转换为一个数字

    其他函數

    NVL(X,VALUE)

    如果X为空,返回value,否则返回X

    NVL2(x,value1,value2)

    如果x非空,返回value1,否则返回value2

    聚合函數

    AVG

    平均值

    SUM

    求和

    MIN、MAX

    最小值、最大值

    COUNT

    数据统计

    展开全文
  • PLSQL常用函数【转】

    2017-08-22 09:38:00
    PLSQL常用函数 1)处理字符的函数  || 或 CONCAT---并置运算符。 格式∶CONCAT(STRING1, STRING2) 例:’ABC’|| ’DE’=’ABCDE’ CONCAT(‘ABC’,’DE’) =’ABCDE’ ASCII---返回字符的ASCII码。 例:...
  • plsql常用函数汇总

    千次阅读 2017-05-27 01:14:35
    三,常用 ORACLE 函数 sysdate为系统日期 dual为虚表 一)日期函数[重点掌握前四个日期函数] 1,add_months[返回日期加(减)指定月份后(前)的日期] select sysdate S1,add_months(sysdate,10) S2, add_...
  • PLSQL 常用函数

    2012-09-24 08:46:00
    为什么80%的码农都做不了架构师?>>>   ...--TO_CHAR(date, format): 根据format重新格式...--聚合函数 --AVG MIN MAX SUM COUNT 转载于:https://my.oschina.net/jeffyu/blog/80012
  • Oracle -- plsql常用函数汇总

    千次阅读 2018-02-10 15:44:22
    常用的有: row_number() over(partition by … order by …) rank() over(partition by … order by …) dense_rank() over(partition by … order by …) count() over(partition by … order by …) max() ...
  • sql是关系型数据库,oracle是面向对象的数据库,sql使用语言是tsql,ORACLE使用的是plsql,但是他们都遵从sql99标准   数学函数   1.绝对值   S:select abs(-1) value   O:select abs(-...
  • 常用函数: AVG---求平均值 COUNT---返回查询的行数 MAX---返回查询列的最大值 MIN---返回查询列的最小值 SUM---返回查询列的总和 MAX, MIN常与GROUP BY配套使用 5)转换函数 TO_DATE---转换字符串为日期型...
  • 这个函数实际上是NVL的循环使用,在此就不举例子了 SQL语句基础 --数据操作 SELECT --从数据库表中 检索数据行和列 INSERT --向数据库表 添加新数据行 DELETE --从数据库表中 删除数据行 UPDATE ...
  • 使用 month 作为日期部分时,DATEDIFF 返回两个日期之间(包括第二个日期但不包括第一个日期)出现的月的第一天的数目。 当使用 week 作为日期部分时,DATEDIFF 返回两个日期(包括第二个日期但不包括第一个...
  • plsql常用函数

    千次阅读 2015-03-19 10:18:35
    一、字符函数   1、LOWER (strexp) - 返回字符串,并将所有的字符小写 select lower('ABCDE') from dual   2、UPPER (strexp) 返回字符串,并将所有的字符大写 select upper('abcdf') from dual   3...
  • 数学函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual 3.取整(小) S:select floor(-1.001) value O:...
  • 详细介绍oracle的语法和存储过程,包括plsql基础、oracle常用函数

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,854
精华内容 1,941
关键字:

plsql常用函数使用