ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OCP 复习笔记之PL/SQL (3)

OCP 复习笔记之PL/SQL (3)

原创 Linux操作系统 作者:space6212 时间:2019-07-20 18:57:01 0 删除 编辑

最近打算把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])

Ø 返回字符串的第mn个字符(包含mn边界),其中: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’的位置

Ø Mn默认都是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_sourcetrim_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为负数,则对整数部分四舍五入到10n次方。

2) TRUNC(column|expression,n)

对数值执行截断操作,保留n位小数(不执行四舍五入);如果n位负数,则对截断整数部分到10n次方。

3) MOD(m,n)

mn进行求模。

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_formatnls_languaage

Ø Nls_date_formatnls_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_formatnls_language等。如果省略该参数,则会用当前session的参数设置。

Ø TO_CHAR(DATE)函数得到结果的长度

TO_CHAR函数最终得到的结果是CHAR类型的,所以,同一类格式得到的长度一样的(比如用TO_CHAR得到MONTH的长度都是9MON的长度都是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

Ø fmTO_CHAR(NUMBER [,FMT])也有用,其作用可以参考TO_CHAR(DATE [,FMT]);如果不加fm,对数字长度小于FMT的情况会在返回的字符串上前用空格补全。

Ø TO_CHAR(NUMBER [,FMT])函数,只要设置了格式,在返回结果中得到的字符串就会自动在前面补上一个空格,返回的字符串的长度就是FMT设定的长度+1

4) TO_DATETO_NUMBER函数

Ø 如果在格式前加上FX,则表明字符串和格式需要精确匹配;没有FXORACLE会尽量匹配。

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相同。

Ø EXP1EXP2的数据类型原则上要求一致,如果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相同。

Ø EXP1EXP2EXP3的数据类型原则上要求一致,但如果EXP2EXP3的数据类型可以隐式转换成EXP1的数据类型也可以。

3) NULLIF(EXP1,EXP2)

Ø 如果EXP1等于EXP2,则返回NULL,否则返回EXP1

Ø EXP1EXP2的数据类型严格要求一致,否则即使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_exprelse_expr可以部分为NULL,但不能全为NULL

4) 如果指定了EXPR,则comparison_expr只能是数值或者不包含比较运算符的表达式;如果EXPR为空,则comparison_expr必须都是包含比较运算符的表达式。

5) 所有的return_exprnelse_expr必须是相同的数据类型。

3.8 DECODE函数

1) 语法

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

2) 函数的参数个数必须>=3个,三个以上的任意个参数都可以

3) result1result2等可以是不同的数据类型

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    166248