oracle中使用简单函数总结

1、    使用单行函数

a)         字符函数

                         i.             ASSII(x)：返回字符x的ASSII码

                        ii.             CHR(x)：返回ASSII码为x的字符

                       iii.             CONCAT(x,y)：将y添加在x上，并将得到的字符作为结果返回

                      iv.             INITCAP(x)：将x中每个单词的首字母转换成大写，并返回得到的字符

                       v.             INSTR(x,find_string[,start][,occurrence])：在x中查找find_string，然后返回find_string所在的位置。可以提供一个可选的start位置来指定该函数从这个位置开始查找。同样，也可以指定一个可选的occurrence参数，来说明应该返回find_string第几次出现的位置

                      vi.             LENGTH(x)：返回x中字符的个数

                     vii.             LOWER(x)：将x中的字母转换成小写，并返回得到的字符

                    viii.             LPAD(x,width[,pad_string])：将x的左边补齐空格，得到总长为width个字符串。Pad_string可以任意字符，代替空格

                       ix.             LTRIM(x[,trim_string])：从x的左边截去一些字符。可以使用一个可选的参数trim_string来指定要截去的字符；如果不指定trim_string参数，则默认截去空格

                        x.             NANVL(x,value)：如果x匹配NAV这个特殊值（非数字），就返回value；否则返回x

                       xi.             NVL(x,value)：如果x为空，就返回value；否则返回x

                      xii.             NVL2(x,value1,value2)：如果为非空，就返回value1，否则返回value2

                     xiii.             REPLACE(x,search_string,replace_string)：也LPAD类似，不过要对x的右边补齐

                    xiv.             RPAD(x,width,[,pad_string])：与RPAD类似，不过要对x的右边截去

                     xv.             RTRIM(x,search_string,replace_string)： 与LTRIM（）类似，不过要对x的右边截去

                    xvi.             SOUNDEX(x)：返回一个包含x的发音的字符串，这个函数用于对英文发音不同但却比较接近的单词进行比较

                   xvii.             SUBSTR(x,start[,length])：返回x中的一个子字符串。这个字符串从start处开始，还可以为这个子字符串指定一个可选的length参数

                  xviii.             TRIM([trim_charFROM]x)：从x的左边和右边同时截去一些字符。可以使用一个可选的参数trim_string来指定要截去的字符；如果不指定trim_string参数，则默认截去空格

                     xix.             UPPER(x)：将x中的字母转换为大写，并返回得到的字符串。

b)         数字函数

                         i.             ABS()

c)          转换函数

                         i.             ASCIISTR(x)：将x转换成一个ASCII字符串，其中x可以是由字符集中的任意字符组成的字符串

                        ii.             BIN_TO_NUM(x)：将二进制数字x转换成number型

                       iii.             CAST(xAS type)：将x转换为type所指定的兼容数据类型

                      iv.             CHARTOROWID(x)：将x转换成rowid类型

                       v.             COMPOSE(x)：将x转换成一个Unicode编码的字符串，字符串采用与x完全相同的字符集

                      vi.             CONVERT(x,source_char_set,dest_char_set)：将x从source_char_set转换为dest_char_set

                     vii.             DECODE(x,search,result,default)：将x与search中的值进行比较；如果相等，decode（） 就返回result的值，否则返回default的值

                    viii.             DECOMPOSE(x)：先对x进行分解，再将其转换为一个Unicode字符串，字符串使用与x完全相同的字符集

                       ix.             HEXTORAW(x)：将包含十六进制数字的字符x转换为一个二进制数字。这个函数返回raw类型的数字

                        x.             NUMTODSINTERVAL(x)：将数字x转换为一个INTERVAL DAY TOSECOND类型。

                       xi.             NUMTOYMINTERVAL(x)：将数字x转换成一个INTERVAL YEAR TOMONTH类型

                      xii.             RAWTOHEX(x)：将二进制数字（RAW）x转换为一个VARCHAR2类型的字符串，值为等价的十六进制数字

                     xiii.             RAWTONHEX(x)：将二进制数字（RAW）x转换为一个NVARCHAR2类型的字符串，值为等价的十六进制数字

                    xiv.             ROWIDTOCHAR(x)：将ROWIDx转换为一个VARCHAR2字符串

                     xv.             ROWIDTONCHAR(x)：将ROWIDx转换为一个NVARCHAR2类型

                    xvi.             TO_BINARY_DOUBLE(x)：将x转换为一个BINARY_DOUBLE类型

                   xvii.             TO_BINARY_FLOAT(x):将x转换为一个BINARY_DOUBLE类型

                  xviii.             TO_BLOB(x)：将x转换为一个二进制大对象（BLOB）类型。

                     xix.             TO_CHAR(x[,format])：将x转换为一个VARCHAR2类型。可以指定一个可选参数format来说明x的格式

                      xx.             TO_CLOB(x)：将x转换为一个字符大对象，CLOB用于保存大量的字符数据

                     xxi.             TO_DATE(x[,format])：将x转换为一个DATE类型

                    xxii.             TO_DSINTERVAL(x)：将x转换为一个INTERVAL DAY TOSECOND类型

                   xxiii.             TO_MULTI_BYTE(x)：将x中的单字节字符转换为对应的多字节字符。返回类型与x的类型相同

                  xxiv.             TO_NCHAR(x)：将数据库字符集中的x转换为一个NVARCHAR2字符串

                   xxv.             TO_NCLOB(x)：将x转换为一个NCLOB类型

                  xxvi.             TO_NUMBER(x[,format])：将x转换为一个number类型

                 xxvii.             TO_SINGLE_BYTE(x)：将x中的多字节字符转换为对应的单字节字符

                xxviii.             TO_TIMESTAMP(x)：将字符串x转换为一个TIMESTAMP类型

                   xxix.             TO_TIMESTAMP_TZ(x)：将字符串x转换为一个TIMESTAMP WITHTIME ZONE类型

                    xxx.             TO_YMINTERVAL(x)：将字符串x转换为一个INTERVAL YEAR TOMONTH 类型

                   xxxi.             TRANSLATE(x,from_string,to_string)：将x中所有的from_string转换为to_string

                  xxxii.             UNISER(x)：将x中的字符转换为国家语言字符集

d)         日期函数

e)         正则表达式函数

                         i.             REGEXP_LINE(x,pattern[,math_option])：从x中搜索pattern参数定义的正则表达式。可以使用match_option修改默认匹配选项，该函数可以被设置为：

1.         ’c’：说明在匹配时区分大小写

2.         ’I’：说明在匹配时不区分大小写

3.         ’n’：允许使用可以匹配任意字符的操作符

4.         ’m’：将x作为一个包含多行的字符串

                        ii.             REGEXP_INSTR(x,parttern[,start[,occurrence[,return_option[,math_option[,subexp_option]]]]])：在x中查找pattern，并返回pattern所在位置。可以指定一下的可选参数：

1.         Start：开始查找的位置。默认是1，指x的第一个字符

2.         Occurrence说明应该返回pattern第几次出现的位置。默认是1，这意味着函数返回pattern第一次在x中出现的位置。

3.         Return_option：说明应该返回什么整数。若该参数为0，则说明要返回的整数是x中的第一个字符的位置；若该参数为非0 的整数，则说明要返回的整数位x中出现在pattern之后的字符的位置

4.         Match_option：修改默认的匹配设置，其工作方式与REGEXP_LIKEK()中指定的相同

5.         subexp_option：是Oracle11g中新增加的，其工作方式如下：对于具有子表达式的模式，subexp_option是0~9之间的非负数，指出pattern中的哪个子表达式是函数的目标

2、    使用聚合函数

a)         AVG(x)：返回x的平均值

b)         COUNT(x)：返回一个包含x的查询回话的函数

c)          MAX(x)：返回x的最大值

d)         MEDIAN(x)：返回x的中间值

e)         MIN(x)：返回x的最小值

f)          STDDEV(x)：返回x的标准差

g)         SUM(x)：返回x的和

h)         VARIANCE(x)：返回x的方差

3、    对行进行分组

a)         使用GROUP BY子句对行进行分组

                         i.             在分组中使用多列

                        ii.             对行组使用聚合函数

b)         使用HAVING子句过滤行组

c)          组合使用WHERE和GROUP BY子句

d)         组合使用WHERE、 GROUP BY和HAVING子句

注意：oracle中是sql语句

1、    where/groupby/having子句中只能直接使用栏目或者常量，而不能使用栏目的别名，除非这个别名来自子查询之中，如select … from (select col1 ccc from table)where ccc>1

2、    而order by 则可以直接使用别名，比如select col1 ccc from table order by ccc

这和sql的执行顺序有关。

 

Oracle中sql语句的执行顺序

Sql语法的分析是从右到左

a)         Sql语句执行步骤

                                       i.             语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义

                                      ii.             语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限

                                     iii.             视图转换，将涉及视图的查询语句转换为相应的对象基表查询语句

                                    iv.             表达式转换，将复杂的sql表达式转换为较为简单的等效连接表达式

                                     v.             选择优化器，不同的优化器一般产生不同的执行计划

                                    vi.             选择连接方式，oracle有三种连接方式，对多表连接oracle可选择适当的连接方式

                                   vii.             选择连接顺序，对多表连接oracle选择哪一对表先连接，选择这两表中哪个表作为源数据表

                                  viii.             选择数据的搜索路径，根据以上调节选择合适的搜索路径，如是使用全表搜索还是使用索引搜索或其他方式

                                     ix.             运行计划

b)         Sql select语句完整的执行顺序

                                       i.             From子句组装来自不同的数据源的数据

                                      ii.             Where子句基于指定的条件对记录进行筛选

                                     iii.             Groupby子句将数据划分为多个分组

                                    iv.             使用聚集函数进行计算

                                     v.             使用having子句筛选分组

                                    vi.             计算所有的表达式

                                   vii.             Select的字段

                                  viii.             使用order by对结果集进行排序