最近打算把9i 的OCP教程看一遍，作一些简单的笔记，作为备忘。
PL/SQL看似简单，但实际使用起来还是有很多技巧和误区的。以下记录的是复习过程中想到的、和容易出现错误的地方。
这些东西在教程大部分是没有记载的。
第三章：单行函数
第三章：单行函数
3.1 单行函数的概念
接受一个或多个参数，为每一行记录返回一个值。
3.2 字符函数
1） 一般的字符函数都是以字符作为计算单位的，如果要用字节作为单位，则在对应的字符函数后加上”B”就可以，如LENGTHB。字符函数的入参是字符串，返回值可以是字符型、数值类型等。（对含有汉字的字符串有意义）
2） LOWER(column|expression)
把字符串换成小写形式。
3） UPPER(column|expression)
将字符串换成大写形式。
4） INITCAP(column|expression)
将字符串的所有单词的第一个字符转换成大写，其余小写。
5） CONCAT(column1|expression1,column2|expression2)
连接两个字符串，作用相当于||
6） SUBSTR(column|expression,m[,n])
Ø 返回字符串的第m到n个字符（包含m、n边界），其中：m不可省略，n可以省略。如果省略n，则截取从第m个开始的所有字符。
Ø 如果m是正数，则从头开始数第m个开始截取；如果m为负数，则从尾往前|m|个字符开始，截取的方向还是从头至尾
Ø N不能为负数；n是负数时返回空串
7） LENGTH(column|expression)
返回字符串的长度，单位是字符，如果需要返回单位为字节的话用LENGTHB。如可以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。
8） INSTR(column|expression,’string’, [,m], [n] )
Ø 返回从m开始，第n次出现’string’的位置
Ø M、n默认都是1，表示从头开始，第一次出现’string’的位置。
Ø 如果m为负数，则从字符串尾部开始反方向的第m个字符开始，找第n次出现’string’的位置（找的方向也是反方向），得到的位置仍是从头开始数的。
Ø N不能为负数
9） LPAD(column|expression, n,'string')
Ø 如果n大于column|expression的长度，则用’string’左补齐，直到返回长度达到n；
Ø 如果n小于column|expression的长度，则返回column|expression的前n个字符
10） RPAD(column|expression, n,'string')
Ø 如果n大于column|expression的长度，则用’string’右补齐，直到返回长度达到n；
Ø 如果n小于column|expression的长度，则返回column|expression的前n个字符
11） TRIM(leading|trailing|both trim_character FROM trim_source)
Ø 去掉trim_source开头|末尾|两边的’trim_character’
Ø 如果trim_source或trim_character任意一个为NULL，则返回结果为NULL
Ø 如果不指定leading/trailing/both，则默认为both
Ø 如果省略trim_character，则trim_character默认为空格
12） TRIM(trim_source)
Trim函数的简化，作用是取出trim_source左右两边的空格
13） REPLACE(text,search_string,replacement_string)
将text中所有的search_string替换成replacement_string
3.3 数值函数
1) ROUND(column|expression, n)
对数值执行四舍五入操作，n默认为0，也就是四舍五入到个位；如果n为负数，则对整数部分四舍五入到10的n次方。
2) TRUNC(column|expression,n)
对数值执行截断操作，保留n位小数（不执行四舍五入）；如果n位负数，则对截断整数部分到10的n次方。
3) MOD(m,n)
对m用n进行求模。
3.4 日期函数
1) 日期格式的存储
Ø 在oracle内部，日期都是以数值形式存储的，但它的显示格式取决于nls_date_format参数。
Ø 默认显示格式为：DD-Mon-RR
Ø 显示为07-Jun-94在库中实际可能是这样存储的：
Century year month day hour minute second
19 94 06 07 5 10 43
2) Sysdate
Sysdate返回服务器端的时间，如果是分布式事务，它返回的是远程数据库的时间，它包含日期和时间两个部分。
3) 日期运算
Ø 日期格式的数据加上或者减去一个数值后，返回数据的仍然是日期型
Ø 两个日期型相减得到两个日期相差的天数（可能包含小数）
4) 日期函数
Ø MONTHS_BETWEEN(date1, date2)
得到两个日期相差的月份，返回值可以为整数、负数和小数。
Ø ADD_MONTHS(date, n)
在date的基础上加上n个月。N可以为正整数、负整数。如果n带小数，则小数部分会被忽略，只取整数部分（类似floor操作）
Ø NEXT_DAY(date,n| 'char')
求下一个星期几的日期。其中后一个参数可以为数字、字符串；当是数字时，1代表星期天，7代表星期六；当是字符串时，它可以是英文、汉字，取决于nls_date_language的设置。且当字符串是英文时，它可以是缩写，也可以是全拼。
该函数的返回值的时间部分与date一样。
Ø LAST_DAY(date)
取date所在月份的最后一天。
Ø ROUND(date[,'fmt'])
Ø TRUNC(date[, 'fmt'])
【注意】除month_between是返回数值外，其他日期函数返回值都是日期格式
3.5 类型转换函数
1） 隐式转换
Ø 特定数据下如下数据类型之间可以进行隐式转换
Varchar2/char -> number
Varchar2/char -> date
Number -> varchar2
Date -> varchar2
Varchar2 -> rowed
Ø 当date类型隐式转换成varchar2时，得到的varchar2值与date的显示格式一样，它取决于nls_date_format和nls_languaage
Ø Nls_date_format和nls_language共同决定了日期的显示格式，所以，如果想要字符串能隐式转换成日期格式，则该字符串必须完全符合日期的显示格式。
Ø Char/varchar2转换为number时，字符串必须是数字和小数点，且小数点个数不能大于1个。
2） TO_CHAR(DATE ,[fmt],[nlsparams])函数
Ø Fmt可以多个个数混用，甚至可以与常量混用，如：
SQL> select to_char(sysdate,'"今天是" yyyy-mm-dd " "day') date_info from dual;
DATE_INFO
----------------------------
今天是 2006-06-20 Tuesday
【注意】如果与常量混用，则常量要用双引号括起来；如果多种格式混用，则格式之间应用空格分开，否则可能出错。
Ø 在设定格式时可以加一些后缀，使得它以特定形式显式数字，后缀包括：
TH 例如4表示为4th
SP 例如4表示为four
SPTH/THSP 例如4表示为fourth
例子：
SQL> select to_char(sysdate,'ddspth') from dual;
TO_CHAR(SYSDATE,'DDSPTH')
-------------------------
twentieth
Ø Nlsparams可以是多个nls参数中的一个或多个，如nls_date_format、nls_language等。如果省略该参数，则会用当前session的参数设置。
Ø TO_CHAR(DATE)函数得到结果的长度
TO_CHAR函数最终得到的结果是CHAR类型的，所以，同一类格式得到的长度一样的（比如用TO_CHAR得到MONTH的长度都是9，MON的长度都是2），这个长度是由同一类格式的长度最大的值来决定的，如MONTH最大长度是September，长度是9，所以默认情况下用TO_CHAR得到英文月份的长度都是9。长度不足部分ORACLE会用空格填补。
Ø 如果需要返回没有空格的字符（类似VARCHAR2），则在TO_CHAR函数的格式元素前加上“FM”；如果包含多个格式元素，则应在各个需要去掉空格的元素前都加上FM，如：
SQL> select to_char(sysdate,'month') month from dual;
MONTH
---------
may
SQL> select length(to_char(sysdate,'month')) month from dual;
MONTH
----------
9
SQL> select length(to_char(sysdate,'fmmonth')) month from dual;
MONTH
----------
3
SQL> select length(to_char(sysdate,'fmmonth fmday')) from dual;
LENGTH(TO_CHAR(SYSDATE,'FMMONT
------------------------------
13
SQL> select length(to_char(sysdate,'month day')) from dual;
LENGTH(TO_CHAR(SYSDATE,'MONTHD
------------------------------
19
注意：如果在格式元素字符串中加上空格或者其他字符，则得到的字符串的总长度也会加上额外的字符的长度。如：
SQL> select length(to_char(sysdate,' month')) month from dual;
MONTH
----------
10
3） TO_CHAR(NUMBER [.Fmt])函数
Ø 格式
元素 | 作用 | 举例 |
9 | 以数字的一般格式显示 | SQL> select to_char(1234.12,'9999999999') from dual; TO_CHAR(1234.12,'9999999999') ----------------------------- 1234 |
0 | 对整数部分，如果原数字的整数部分大于设定格式的整数部分，则返回的字符串会在在前面加’0’；如果是小数部分，则它相当于’9’的作用 | SQL> select to_char(1234.1,'099999.999') new_char from dual; NEW_CHAR ---------- 001234.100 只要0出现的位置大于原数字的整数部分，返回值的整数部分长度就是0出现的位置；（不管0之外的是什么数字） SQL> select to_char(1234.1,'9909999.999') new_char from dual; NEW_CHAR ------------ 01234.100 |
. | 在返回的字符串上加小数点 | |
, | 在返回的字符串的整数部分加’,’；它不能在小数部分设置 | |
L | 在返回的字符串前加上本地的货币符号 | SQL> select to_char(1234,'l9999') new_char from dual; NEW_CHAR --------------- RMB1234 |
$ | 在返回字符串前加上美圆符号 |
注意：
Ø 如果数字整数部分的长度大于设定的格式的整数的长度，则结果会不能正常显示，而是用#####表示；如果数字的小数部分的长度大于设定格式的小数部分，则会对数字在设定的小数部分执行四舍五入，最后返回小数长度为设定格式的长度。
Ø 返回字符串的长度为设定的格式的长度+1
SQL> select to_char(12345,'99.000') from dual;
TO_CHAR(12345,'99.000')
-----------------------
#######
SQL> select to_char(0.12345,'999.9999') from dual;
TO_CHAR(0.12345,'999.9999')
---------------------------
.1235
SQL> select length(to_char(1234.12,'9999999999')) from dual;
LENGTH(TO_CHAR(1234.12,'999999
------------------------------
11
Ø fm对TO_CHAR(NUMBER [,FMT])也有用，其作用可以参考TO_CHAR(DATE [,FMT])；如果不加fm，对数字长度小于FMT的情况会在返回的字符串上前用空格补全。
Ø 对TO_CHAR(NUMBER [,FMT])函数，只要设置了格式，在返回结果中得到的字符串就会自动在前面补上一个空格，返回的字符串的长度就是FMT设定的长度+1。
4） TO_DATE和TO_NUMBER函数
Ø 如果在格式前加上FX，则表明字符串和格式需要精确匹配；没有FX则ORACLE会尽量匹配。
SQL> select to_date('2005-1-1','fxyyyy-mm-dd') from dual;
select to_date('2005-1-1','fxyyyy-mm-dd') from dual
ORA-01862: the numeric value does not match the length of the format item
SQL> select to_date('2005-01-01','fxyyyy-mm-dd') from dual;
TO_DATE('2005-01-01','FXYYYY-M
------------------------------
2005-01-01
SQL> select to_date('2005-1-1','yyyy-mm-dd') from dual;
TO_DATE('2005-1-1','YYYY-MM-DD
------------------------------
2005-01-01
3.6 几个常用函数
1） NVL(EXP1,EXP2)
Ø 如果EXP1为空，则返回EXP2，否则返回EXP1。返回值的数据类型一定与EXP1相同。
Ø EXP1和EXP2的数据类型原则上要求一致，如果EXP2的数据类型可以隐式转换成EXP1数据类型也可以。
Ø 第一个参数可以是任意表达式、或子查询
举例：如果A中存在b与变量值相等则按条件取值,否则取全部的值
select * from t where a=nvl((select a from t where t.a=2 and rownum<=1),a);
2） NVL2(EXP1,EXP2,EXP3)
Ø 如果EXP1非空，则返回EXP2；否则，返回EXP3。返回值的内容一定与EXP1相同。
Ø EXP1、EXP2、EXP3的数据类型原则上要求一致，但如果EXP2、EXP3的数据类型可以隐式转换成EXP1的数据类型也可以。
3） NULLIF(EXP1,EXP2)
Ø 如果EXP1等于EXP2，则返回NULL，否则返回EXP1。
Ø EXP1、EXP2的数据类型严格要求一致，否则即使EXP2的数据类型可以隐式转换为EXP1的数据类型也会出错。
Ø EXP1不可以是NULL，但EXP2可以为null。
4） COALESCE(EXP1,EXP2...EXPN)
Ø 返回第一个非空的表达式的值。
Ø 要求所有的非空的EXP的数据类型一样，否则即使其他非空的EXP的数据类型可以隐式转换为EXP1的数据类型也会出错。
3.7 CASE 子句
1） 语法
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
2） ELSE子句是可选的，不一定需要。
3） return_expr和else_expr可以部分为NULL，但不能全为NULL
4） 如果指定了EXPR，则comparison_expr只能是数值或者不包含比较运算符的表达式；如果EXPR为空，则comparison_expr必须都是包含比较运算符的表达式。
5） 所有的return_exprn、else_expr必须是相同的数据类型。
3.8 DECODE函数
1） 语法
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
2） 函数的参数个数必须>=3个，三个以上的任意个参数都可以
3） result1和result2等可以是不同的数据类型
4） 如果不指定default值，且没有符合条件的表达式，则返回NULL
5） 第一个参数可以是常量、列名、表达式，甚至可以是子查询
SQL> select distinct id,decode((select t2.id from t2 where t2.id=t1.id and rownum=1),null,'null',t1.id) "if_t2_in_t1"from t1;
ID if_t2_in_t1
---------- ----------------------------------------
1 1
2 null
4 4
5 5
null
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63770/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/231499/viewspace-63770/
6058
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
