Oracle DB 使用单行函数定制输出.docx
- 文档编号:17718368
- 上传时间:2023-08-03
- 格式:DOCX
- 页数:16
- 大小:22.48KB
Oracle DB 使用单行函数定制输出.docx
《Oracle DB 使用单行函数定制输出.docx》由会员分享,可在线阅读,更多相关《Oracle DB 使用单行函数定制输出.docx(16页珍藏版)》请在冰点文库上搜索。
OracleDB使用单行函数定制输出
•描述SQL提供的各类函数
•在SELECT语句中使用字符、数字和日期函数
∙SQL函数
函数是SQL的一项非常强大的功能,可用于执行以下操作:
•执行数据计算
•修改单个数据项
•处理成组行的输出
•设置日期和数字的显示格式
•转换列数据类型
SQL函数有时接受多个参数,但始终返回一个值。
∙两种类型的SQL函数
有两种类型的函数:
•单行函数
•多行函数
单行函数
这些函数仅对单行进行处理,为每行返回一个结果。
单行函数具有多种不同类型。
介绍以下几种函数:
•字符
•数字
•日期
•转换
•常规
多行函数
这些函数可以处理成组的行,为每组行返回一个结果。
∙单行函数
单行函数:
•处理数据项
•接受参数并返回一个值
•对每个返回行进行处理
•为每行返回一个结果
•可能会修改数据类型
•可以嵌套
•接受参数,这些参数可以是列或表达式
function_name[(arg1,arg2,...)]
在该语法中:
function_name:
是函数的名称
arg1,arg2:
是函数使用的任意参数,可以是列名称或表达式。
单行函数用于处理数据项。
其接受一个或多个参数,并对查询返回的每一行返回一个值。
参数可以是下列对象之一:
•用户提供的常量
•变量值
•列名
•表达式
单行函数的特点包括:
•对查询中返回的每一行进行处理
•为每行返回一个结果
•可能会返回一个与所引用类型不同的数据值
•可能需要一个或多个参数
•可用于SELECT、WHERE和ORDERBY子句中;也可以嵌套。
将介绍以下单行函数:
•字符函数:
接受字符输入,可以返回字符值和数字值
•数字函数:
接受数字输入,可以返回数字值
•日期函数:
对DATE数据类型的值进行处理(所有日期函数都会返回一个DATE数据类型的值,只有MONTHS_BETWEEN函数返回一个数字。
)
•转换函数:
将值从一种数据类型转换为另一种数据类型
•常规函数:
-NVL
-NVL2
-NULLIF
-COALESCE
-CASE
-DECODE
∙字符函数
单行字符函数接受的输入是字符数据,可以返回字符值和数字值。
字符函数可以分为以下几类:
•大小写转换函数
•字符处理函数
注:
以下是一些完全或部分符合SQL:
2003的函数:
UPPER
LOWER
TRIM
LENGTH
SUBSTR
INSTR
∙ 大小写转换函数
以下函数用于转换字符串的大小写:
LOWER、UPPER和INITCAP是三个大小写转换函数。
•LOWER:
将大小写混合或大写的字符串转换为小写
•UPPER:
将大小写混合或小写的字符串转换为大写
•INITCAP:
将每个单词的首字母转换为大写,其余字母保留为小写
hr@TEST0924>SELECT'Thejobidfor'||UPPER(last_name)||'is'||LOWER(job_id)AS"EMPLOYEEDETAILS"FROM employees;
EMPLOYEEDETAILS
------------------------------------------------------
ThejobidforABELissa_rep
ThejobidforANDEissa_rep
ThejobidforATKINSONisst_clerk
∙使用大小写转换函数
使用以下语句可显示雇员Higgins的雇员编号、姓名和部门编号:
hr@TEST0924>SELECTemployee_id,last_name,department_idFROM employeesWHERE last_name='higgins';
norowsselected
第一个SQL语句的WHERE子句将雇员姓名指定为higgins。
因为EMPLOYEES表中的所有数据都是以正常大小写形式存储的,所以姓名higgins在表中没有找到匹配项,因此不会选择任何行。
hr@TEST0924>SELECTemployee_id,last_name,department_idFROM employeesWHERE LOWER(last_name)='higgins';
EMPLOYEE_IDLAST_NAME DEPARTMENT_ID
-------------------------------------------------
205Higgins 110
第二个SQL语句中的WHERE子句指定将EMPLOYEES表中的雇员姓名与higgins进行比较,但是先将LAST_NAME列转换为小写,然后再进行比较。
因为两个姓名都是小写的,因此找到了匹配项,从而选择了一行。
可以按以下方式重写WHERE子句,产生的结果相同:
...WHERElast_name='Higgins'
输出中姓名的显示格式与其存储在数据库中的一样。
要以大写形式显示姓名,可以在SELECT语句中使用UPPER函数。
hr@TEST0924>SELECTemployee_id, UPPER(last_name), department_idFROM employeesWHERE INITCAP(last_name)='Higgins';
EMPLOYEE_IDUPPER(LAST_NAME) DEPARTMENT_ID
-------------------------------------------------
205HIGGINS 110
∙字符处理函数
下面的函数用于处理字符串:
CONCAT、SUBSTR、LENGTH、INSTR、LPAD、RPAD和TRIM是要介绍的字符处理函数。
•CONCAT:
将值联接在一起(CONCAT函数中只能使用两个参数)
•SUBSTR:
提取确定长度的字符串
•LENGTH:
以数字值的形式显示字符串的长度
•INSTR:
查找指定字符串的数字位置
•LPAD:
返回一个表达式,左边使用一个字符表达式填充到n个字符的长度
•RPAD:
返回一个表达式,右边使用一个字符表达式填充到n个字符的长度
•TRIM:
截去字符串首字符或尾字符(或者两者都截去)(如果trim_character或trim_source是一个字符文字,则必须将其放在单引号内)
注:
可以将UPPER和LOWER等函数与&替代变量组合使用。
例如,如果使用UPPER('&job_title'),用户就不必以特定的大小写形式输入职务了。
instr(string1,string2,start_position,nth_appearance)
string1
源字符串,要在此字符串中查找。
string2
要在string1中查找的字符串。
start_position
代表string1的哪个位置开始查找。
此参数可选,如果省略默认为1.字符串索引从1开始。
如果此参数为正,从左到右开始检索,如果此参数为负,从右到左检索,返回要查找的字符串在源字符串中的开始索引。
nth_appearance
代表要查找第几次出现的string2.此参数可选,如果省略,默认为1.如果为负数系统会报错。
∙使用字符处理函数
hr@TEST0924>l
1 SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,'a')"Contains'a'?
"
2*FROM employeesWHERE SUBSTR(job_id,4)='REP'
hr@TEST0924>/
EMPLOYEE_IDNAME JOB_ID LENGTH(LAST_NAME)Contains'a'?
------------------------------------------------------------------------------------------------
202PatFay MK_REP 3 2
203SusanMavris HR_REP 6 2
204HermannBaer PR_REP 4 2
示例中显示从其职务ID的第四个位置开始包含字符串REP的所有雇员的以下信息:
联接在一起的名字和姓氏、雇员姓氏的长度,以及字母a 在雇员姓氏中的数字位置。
示例:
修改幻灯片中的SQL语句,以显示姓氏以字母“n”为结尾的雇员的数据。
hr@TEST0924>SELECTemployee_id,CONCAT(first_name,last_name)NAME,LENGTH(last_name),INSTR(last_name,'a')"Contains'a'?
"
2 FROM employeesWHERE SUBSTR(last_name,-1,1)='n';
EMPLOYEE_IDNAME LENGTH(LAST_NAME)Contains'a'?
--------------------------------------------------------------------------------------
102LexDeHaan 7 5
105DavidAustin 6 0
∙数字函数
•ROUND:
将值舍入到指定的小数位
•TRUNC:
将值截断到指定的小数位
•MOD:
返回除法运算的余数
数字函数接受数字形式的输入并返回数字值。
∙使用ROUND函数
hr@TEST0924>SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROM DUAL;
ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)
----------------------------------------------
45.92 46 50
ROUND函数用于将列、表达式或值舍入到n位小数位。
如果第二个参数为0 或者缺失,则会将值舍入为整数。
如果第二个参数为2,则会将值舍入到2 位小数位。
相反,如果第二个参数为-2,则会将值舍入到小数点左边2 位(即舍入到最近的百位)。
ROUND函数还可与日期函数一起使用。
在本课稍后您将看到相应的示例。
DUAL表
DUAL表属于SYS用户,但所有用户都可以访问它。
它包含一个DUMMY列和一个值为X的行。
如果某个值只需返回一次,例如,不是从含有用户数据的表中导出的常量值、伪列值或表达式值,则DUAL表非常有用。
DUAL表通常用于保持SELECT子句语法的完整性,因为SELECT和FROM子句都是必需的,而有些计算并不需要从实际表中进行选择。
∙使用TRUNC函数
hr@TEST0924>SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1)FROM DUAL;
TRUNC(45.923,2)TRUNC(45.923)TRUNC(45.923,-1)
--------------------------------------------
45.92 45 40
TRUNC函数用于将列、表达式或值截断到n位小数位。
TRUNC函数使用参数的方式与ROUND函数非常类似。
如果第二个参数为0 或者缺失,则会将值截断为整数。
如果第二个参数为2,则会将值截断到2 位小数位。
相反,如果第二个参数为-2,则会将值截断到小数点左边2 位。
如果第二个参数为-1,则将值截断到小数点左边1 位。
与ROUND函数一样,TRUNC函数也可与日期函数一起使用。
--Oracletrunc()函数的用法
/**************日期********************/
1.selecttrunc(sysdate)fromdual --2011-3-18 今天的日期为2011-3-18
2.selecttrunc(sysdate,'mm') from dual --2011-3-1 返回当月第一天.
3.selecttrunc(sysdate,'yy')fromdual --2011-1-1 返回当年第一天
4.selecttrunc(sysdate,'dd')fromdual --2011-3-18 返回当前年月日
5.selecttrunc(sysdate,'yyyy')fromdual --2011-1-1 返回当年第一天
6.selecttrunc(sysdate,'d')fromdual --2011-3-13(星期天)返回当前星期的第一天
7.selecttrunc(sysdate,'hh')fromdual --2011-3-1814:
00:
00 当前时间为14:
41
8.selecttrunc(sysdate,'mi')fromdual --2011-3-1814:
41:
00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC(number,num_digits)
Number需要截尾取整的数字。
Num_digits用于指定取整精度的数字。
Num_digits的默认值为0。
TRUNC()函数截取时不进行四舍五入
*/
9.selecttrunc(123.458)fromdual --123
10.selecttrunc(123.458,0)fromdual --123
11.selecttrunc(123.458,1)fromdual --123.4
12.selecttrunc(123.458,-1)fromdual --120
13.selecttrunc(123.458,-4)fromdual --0
14.selecttrunc(123.458,4)fromdual --123.458
15.selecttrunc(123)fromdual --123
16.selecttrunc(123,1)fromdual --123
17.selecttrunc(123,-1)fromdual --120
∙使用MOD函数
针对职务为销售代表的所有雇员,计算薪金除以5,000 后的余数。
hr@TEST0924>SELECTlast_name,salary,MOD(salary,5000)FROM employeesWHERE job_id ='SA_REP';
LAST_NAME SALARYMOD(SALARY,5000)
---------------------------------------------------
Tucker 10000 0
Bernstein 9500 4500
Hall 9000 4000
MOD函数将返回第一个参数除以第二个参数之后的余数。
在示例中,针对职务ID 为SA_REP的所有雇员计算薪金除以5,000后的余数。
注:
MOD函数经常用于确定一个值是奇数还是偶数。
MOD函数也是Oracle 散列函数。
∙处理日期
•OracleDB 以内部数字格式存储日期:
世纪、年、月、日、小时、分钟和秒。
•默认的日期显示格式为DD-MON-RR。
–通过仅指定年份的后两位,可以在20世纪存储21世纪的日期
–同样,也可以在21世纪存储20世纪的日期
hr@TEST0924>SELECTlast_name,hire_dateFROM employeesWHERE hire_date<'01-FEB-01';
LAST_NAME HIRE_DATE
-------------------------------------------
DeHaan 13-JAN-01
OracleDB以内部数字格式存储日期,以此代表世纪、年、月、日、小时、分钟和秒。
任何日期的默认显示格式和输出格式均为DD-MON-RR。
有效的Oracle 日期介于公元前4712年1 月1 日和公元9999年12月31日之间。
在示例中,HIRE_DATE列输出以默认格式DD-MON-RR显示。
但是,在数据库中并不以这种格式存储日期,而是存储了日期和时间的所有组成部分。
所以,尽管17-JUN-87 之类的HIRE_DATE只显示出日、月和年,但是数据库中还存在与该日期相关联的时间和世纪信息。
完整数据可能是June17,1987,5:
10:
43PM。
∙RR日期格式
表中汇总了RR元素的行为。
RR日期格式与YY元素类似,但是可以使用它指定不同的世纪。
请使用RR日期格式元素代替YY,这样返回值的世纪会根据指定的两位数年份和当前年份的后两位数而变化。
请注意显示在上表倒数两行中的值。
当接近世纪中期时,RR的行为大概不是你所希望看到的。
此数据在内部按以下格式进行存储:
CENTURYYEARMONTHDAY HOUR MINUTESECOND
19 87 06 17 17 10 43
世纪和2000年
将包含日期列的记录插入到表中时,系统会从SYSDATE函数中获取世纪信息。
但是,在屏幕上显示日期列时,默认情况下却不显示世纪的相应部分。
DATE数据类型用2 个字节存储年份信息:
1 个字节存储世纪,1 个字节存储年。
无论是否指定或显示世纪值,其始终会包含在内。
这种情况下,RR决定INSERT中世纪的默认值。
∙使用SYSDATE函数
SYSDATE是返回以下对象的函数:
•日期
•时间
hr@TEST0924>SELECTsysdateFROM dual;
SYSDATE
------------------
17-OCT-13
SYSDATE是一个日期函数,它返回当前数据库服务器的日期和时间。
可以像使用任何其它列名称一样使用SYSDATE。
例如,通过从表中选择SYSDATE,可以显示当前日期。
通常从称为DUAL的公用表中选择SYSDATE。
注:
SYSDATE将返回为数据库所在操作系统设置的当前日期和时间。
因此,如果本人位于澳大利亚,但连接到位于美国(U.S.) 某地的远程数据库,则sysdate函数将返回美国的日期和时间。
在这种情况下,可以使用CURRENT_DATE函数返回会话时区的当前日期。
∙与日期有关的运算
•对日期加上或减去一个数字可以获得一个新的日期值。
•将两个日期相减可以得出它们之间的天数。
•将小时数除以24可以将小时添加到日期中。
因为数据库将日期作为数字进行存储,因此可以使用算术运算符(如加和减)执行计算。
可以加减数字常量和日期。
可以执行下列操作:
∙使用算术运算符处理日期
r@TEST0924>SELECTlast_name,(SYSDATE-hire_date)/7ASWEEKSFROM employeesWHERE department_id=90;
LAST_NAME WEEKS
-----------------------------------
King 539.384281
Kochhar 421.241424
DeHaan 665.812852
示例中显示部门90中所有雇员的姓氏和聘用周数。
它用当前日期(SYSDATE) 减去聘用雇员的日期,然后用所得的结果除以7 计算出雇员的聘用周数。
注:
SYSDATE是一个SQL函数,它用于返回当前日期和时间。
在运行SQL查询时,返回的结果将随为本地数据库所在操作系统设置的日期和时间而变化。
如果从较早的日期中减去较晚的日期,差值将是一个负数。
∙日期处理函数
日期函数用于处理Oracle 日期。
所有日期函数都返回一个DATE数据类型的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle DB 使用单行函数定制输出 使用 单行 函数 定制 输出