oracle-single row function
1.概念
单行函数：single row funcation
指一行数据输入，返回一个值的函数，常见的有
字符函数（如：substr）
日期函数（如：months_between）
数字函数（如：MOD）
转换函数（如：to_char）
通用函数（如：NVL）
多行函数 ：muti row function
指多行数据输入，返回一个值的函数
常见的有sum 、 max等
2.single row function
They always return a single row for every row of a queried table
They can return a data type value different from the one that is referenced. 可以返回与引用数据类型不一致的返回值
3.常见单行函数用法
3.1.CONCAT
concat（char1,char2）
CONCATreturnschar1concatenated withchar2. Bothchar1andchar2can be any of the data typesCHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB, orNCLOB. The string returned is in the same character set aschar1. Its data type depends on the data types of the arguments.
3.2.MOD
mod(n2,n1)
MODreturns the remainder ofn2divided byn1. Returnsn2ifn1is 0.
This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.
3.3.CEIL
ceil(n)
CEILreturns the smallest integer that is greater than or equal ton. The numberncan always be written as the difference of an integerkand a positive fractionfsuch that 0 <=f< 1 andn=k-f. The value ofCEILis the integerk. Thus, the value ofCEILisnitself if and only ifnis precisely an integer.
This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.
3.4.FLOOR
floor(n)
FLOORreturns the largest integer equal to or less thann. The numberncan always be written as the sum of an integerkand a positive fractionfsuch that 0 <=f< 1 andn=k+f. The value ofFLOORis the integerk. Thus, the value ofFLOORisnitself if and only ifnis precisely an integer.
3.5.TRUNC
TRUNC(n2,n1)
TheTRUNC(number) function returnsn1truncated ton2decimal places. Ifn2is omitted, thenn1is truncated to 0 places.n2can be negative to truncate (make zero)n2digits left of the decimal point.
3.6.INITCAP
INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap(smith) upp from dual;
UPP
-----
Smith
3.7.substr
字符串截取函数
SQL> select substr('abcdefg',2,1) from dual;
SU
--
b
3.8.INSTR
select instr('helloworld','l',2,2) from dual; --返回结果：4 也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
================================================================================================================================
ocp考题：
Choose two.
Which two statements are true about single row functions?
A) CONCAT: can be used to combine any number of values
B) MOD: returns the quotient of a division operation
C) CEIL: can be used for positive and negative numbers
D) FLOOR: returns the smallest integer greater than or equal to a specified number
E) TRUNC: can be used with NUMBER and DATE values
Correct Answer: CE
Which two statements are true about single row functions?
A) CONCAT: can be used to combine any number of values
B) FLOOR: returns the smallest integer greater than or equal to a specified number
C) CEIL: can be used for positive and negative numbers
D)TRUNC: can be used with NUMBER and DATE values
E) MOD: returns the quotient of a division operation
Correct Answer:CD
Which three statements are true about single row functions?
A. They can be used only in the where clause of a select statement.
B. They can accept only one argument.
C. They return a single result row per table.
D. The argument can be a column name, variable, literal or an expression.
E. They can be nested to any level.
F. The date type returned can be different from the data type of the argument.
Correct Answer:DEF
优质内容筛选与推荐>>1、简化SSH框架的整合
2、3、基本类型
3、第04次作业-树
4、猛料来啦！Autodesk全线产品二次开发视频录像下载！！
5、原创：ESXi5.1安装实验2