`
毛山歌
  • 浏览: 5618 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

oracle 数值型函数,字符型函数,日期函数,转换函数,to_char,自动类型转换,日期类型转换

 
阅读更多
数值型函数
abs:求绝对值函数,如:abs(−5) 5
sqrt:求平方根函数,如:sqrt(2) 1.41421356
power:求幂函数,如:power(2,3) 8
cos:求余弦三角函数,如:cos(3.14159) −1
mod:求除法余数,如:mod(1600, 300) 100
ceil:求大于等于某数的最小整数,如:ceil(2.35) 3
floor:求小于等于某数的最大整数,如:floor(2.35) 2
round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9
round(45.923, 0),结果,46
round(45.923, −1),结果,50
trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9
trunc(45.923),结果,45
trunc(45.923, −1),结果, 40

使用数值型函数练习。
使用求绝对值函数abs。
Sql代码 
SELECT abs(?5) FROM dual; 

SELECT abs(?5) FROM dual;
执行结果:
  ABS(?5)
---------------
        5
说明:求?5的绝对值,结果为5。

字符型函数

ascii:获得字符的ASCII码,如:Ascii('A') 65
chr:返回与ASCII码相应的字符,如:Chr(65) A
lower:将字符串转换成小写,如:lower ('SQL Course') sql course
upper:将字符串转换成大写,如:upper('SQL Course') SQL COURSE
initcap:将字符串转换成每个单词以大写开头,如:initcap('SQL course') Sql Course
concat:连接两个字符串 concat('SQL', ' Course') SQL Course
substr:给出起始位置和长度,返回子字符串,如:substr('String',1,3) Str
length:求字符串的长度 length('Wellcom') 7
instr:给出起始位置和出现的次数,求子字符串在字符串中出现的位置,如:instr('String', 'r',1,1) 3
lpad:用字符填充字符串左侧到指定长度,如:lpad('Hi',10,'-') --------Hi
rpad:用字符填充字符串右侧到指定长度,如:rpad('Hi',10,'-') Hi--------
trim:在一个字符串中去除另一个字符串,如:trim('S' FROM 'SSMITH') MITH
replace:用一个字符串替换另一个字符串中的子字符串,如:replace('ABC', 'B', 'D') ADC

如果不知道表的字段内容是大写还是小写,可以转换后比较。
输入并执行查询:
Sql代码 
SELECT  empno, ename, deptno    FROM emp  
        WHERE  lower(ename) ='blake'; 

SELECT  empno, ename, deptno FROM emp
WHERE  lower(ename) ='blake';
结果为:
    Sql代码 
EMPNO ENAME          DEPTNO  
    --------------- ---------- ------------------------  
             7698 BLAKE              30 

EMPNO ENAME          DEPTNO
--------------- ---------- ------------------------
     7698 BLAKE              30
说明:该查询将表中的雇员名转换成小写,与小写的blake进行比较。

日期函数

months_between:返回两个日期间的
月份,如:months_between ('04-11月-05','11-1月-01'),结果,57.7741935
add_months:返回把月份数加到日期上的新日期,如:add_months('06-2月-03',1),结果,06-3月-03
add_months('06-2月-03',-1),结果,06-1月-03
next_day:返回指定日期后的星期对应的新日期,如:next_day('06-2月-03','星期一'),结果,10-2月-03
last_day:返回指定日期所在的月的最后一天,如:last_day('06-2月-03'),结果,28-2月-03
round:按指定格式对日期进行四舍五入,如:round(to_date('13-2月-03'),'YEAR'),结果,01-1月-03
round(to_date('13-2月-03'),'MONTH'),结果,01-2月-03
round(to_date('13-2月-03'),'DAY'),结果,16-2月-03

(按周四舍五入)
trunc:对日期按指定方式进行截断,如:trunc(to_date('06-2月-03'),'YEAR'),结果,01-1月-03
trunc(to_date('06-2月-03'),'MONTH'),结果,01-2月-03
trunc(to_date('06-2月-03'),'DAY'),结果,02-2月-03

(按周截断)

返回2003年2月的最后一天。
输入并执行查询:
Sql代码 
SELECT last_day('08-2月-03') FROM dual; 

SELECT last_day('08-2月-03') FROM dual;
返回结果为:
Sql代码 
LAST_DAY('  
        ----------------  
        28-2月-03 

LAST_DAY('
----------------
28-2月-03
说明:该函数给定参数为某月份的任意一天,返回时间为该月份的最后一天。本例中,参数为03年2月8号,返回日期为03年2月28日,是该月的最后一天。

假定当前的系统日期是2003年2月6日,求再过1000天的日期。
输入并执行查询:
Sql代码 
SELECT sysdate+1000 AS "NEW DATE" FROM dual; 

SELECT sysdate+1000 AS "NEW DATE" FROM dual;
返回结果为:
Sql代码 
NEW DATE 
        ----------------  
        04-11月-05  

NEW DATE
----------------
04-11月-05
说明:该查询使用到了日期的加法运算,求经过一定天数后的新日期。

假定当前的系统日期是2003年2月6日,显示部门10雇员的雇佣天数。
输入并执行查询:
Sql代码 
SELECT ename, round(sysdate-hiredate) DAYS  
        FROM   emp  
        WHERE  deptno = 10; 

SELECT ename, round(sysdate-hiredate) DAYS
FROM   emp
WHERE  deptno = 10;
返回结果为:
ENAME            DAYS
--------------- ---------------------
CLARK           7913
KING             7752
MILLER          7685
说明:该查询使用日期的减法运算求两个日期的相差天数。用round函数对天数进行四舍五入。

转换函数
Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER

To_char:转换成字符串类型,如:To_char(1234.5, '$9999.9'),结果:$1234.5
To_date:转换成日期类型,如:To_date('1980-01-01', 'yyyy-mm-dd'),结果:01-1月-80
To_number:转换成数值类型,如:To_number('1234.5'),结果:1234.5

自动类型转换
Oracle可以自动根据具体情况进行如下的转换:
* 字符串到数值。
* 字符串到日期。
* 数值到字符串。
* 日期到字符串。
以下是自动转换的训练。
自动转换字符型数据到数值型。
输入并执行查询:
Sql代码 
SELECT '12.5'+11 FROM dual; 

SELECT '12.5'+11 FROM dual;
执行结果为:
Sql代码 
'12.5'+11  
        ------------  
         23.5 

'12.5'+11
------------
   23.5
说明:在本训练中,因为出现+运算符,说明进行的是算术运算,所以字符串'12.5'被自动转换成数值12.5,然后参加运算。

自动转换数值型数据到字符型。
执行以下查询:
Sql代码 
SELECT '12.5'||11 FROM dual; 

SELECT '12.5'||11 FROM dual;
结果为:
Sql代码 
'12.5' 
        ------  
        12.511 

'12.5'
------
12.511
说明:在本训练中,因为出现||运算符,说明进行的是字符串连接运算,数值11被自动转换成字符串'11',然后参加运算。

日期类型转换
将日期型转换成字符串时,可以按新的格式显示。
如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。
主要的日期格式字符的含义

AM、PM:上午、下午,如:08 AM
D:数字表示的星期(1~7),如:1,2,3,4,5,6,7
DD:数字表示月中的日期(1~31),如:1,2,3,…,31
MM:两位数的月份,如:01,02,…,12
Y、YY、YYY、YYYY 年份的后几位,如:3,03,003,2003
RR:解决Y2K问题的年度转换
DY:简写的星期名,如:MON,TUE,FRI,…
DAY:全拼的星期名,如:MONDAY,TUESDAY,…
MON:简写的月份名,如:JAN,FEB,MAR,…
MONTH:全拼的月份名,如:JANUARY,FEBRUARY,…
HH、HH12:12小时制的小时(1~12),如:1,2,3,…,12
HH24:24小时制的小时(0~23),如:0,1,2,…,23
MI:分(0~59),如:0,1,2,…,59
SS:秒(0~59),如:0,1,2,…,59
,./-;: 原样显示的标点符号
'TEXT':引号中的文本原样显示,如:TEXT

将日期转换成带时间和星期的字符串并显示。
执行以下查询:
Sql代码 
SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual; 

SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;
结果为:
Sql代码 
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24  
        ----------------------------------------------------------  
        2004-02-07 15:44:48 下午 星期六 

TO_CHAR(SYSDATE,'YYYY-MM-DD HH24
----------------------------------------------------------
2004-02-07 15:44:48 下午 星期六

说明:该语句中的第一个参数表示要转换的日期,第二个参数是格式字符串,表示转换后的格式,结果类型为字符串。“YYYY”为4位的年份,“MM”为两位的月份,“DD”为两位的日期,“HH24”表示显示24小时制的小时,“MI”表示显示分钟,“SS”表示显示秒,“AM”表示显示上午或下午(本例中为下午),“DY”表示显示星期。“-”、“:”和空格原样显示,用于分割日期和时间。转换出来的系统时间为:2004年2月7日(星期六)下午15点44分48秒。
还可以按其他的格式显示。以下查询中插入中文的年月日,其中原样显示部分区别于外层的单引号,需要用双引号引起。

将日期显示转换成中文的年月日。
输入并执行查询:
Sql代码 
SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual; 

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;
执行结果为:
Sql代码 
TO_CHAR(SYSDAT  
        -------------------------  
        2003年11月18日 

TO_CHAR(SYSDAT
-------------------------
2003年11月18日
说明:双引号中的中文字“年”、“月”、“日”原样显示,单引号为字符串的界定标记,区别于双引号,不能混淆。

时间显示的大小写。
步骤1:执行以下查询:
Sql代码 
SELECT SYSDATE,to_char(SYSDATE,'yyyysp') FROM dual; 

SELECT SYSDATE,to_char(SYSDATE,'yyyysp') FROM dual;
结果为:
Sql代码 
SYSDATE   TO_CHAR(SYSDATE,'YYYYSP')  
        ------------- ----------------------------------------------  
        07-2月 -04  two thousand four 

SYSDATE   TO_CHAR(SYSDATE,'YYYYSP')
------------- ----------------------------------------------
07-2月 -04  two thousand four
步骤2:执行以下查询:
Sql代码 
SELECT to_char(SYSDATE,'Yyyysp') FROM dual; 

SELECT to_char(SYSDATE,'Yyyysp') FROM dual;
结果为:
Sql代码 
SYSDATE    TO_CHAR(SYSDATE,'YYYYSP')  
        -------------- -----------------------------------------------  
        Two Thousand Four 

SYSDATE    TO_CHAR(SYSDATE,'YYYYSP')
-------------- -----------------------------------------------
Two Thousand Four
数值转换符:

9:代表一位数字,如果是正数,前面是空格,如果是负数,前面是−号,如:9999
0:代表一位数字,在相应的位置上如果没有数字则出现0 0000
,:逗号,用作组分隔符,如:99,999
.:小数点,分隔整数和小数,如:999.9
$:$货币符号,如:$999.9
L:本地货币符号,如:L999.99
FM:去掉前后的空格,如:FM999.99
EEEE:科学计数法,如:9.9EEEE
S:负数符号−放在开头,如:S999.9

将数值转换成字符串并按新格式显示。
执行以下查询:
Sql代码 
SELECT TO_CHAR(sal,'$99,999') SALARY FROM emp  
        WHERE ename = 'SCOTT'; 

SELECT TO_CHAR(sal,'$99,999') SALARY FROM emp
WHERE ename = 'SCOTT';
结果为:
Sql代码 
SALARY  
        ------------  
         $4,000 

SALARY
------------
$4,000
说明:格式字符串中“$”表示转换结果前面添加$。“9”表示一位数字,“99,999”表示结果可以显示为5位的数字。“,”表示在相应的位置上添加逗号。如果实际数值位数不足5位,则只显示实际位数,如4000实际位数为4位,则只显示4位。如果实际位数超过5位,则会填充为#号。

其他函数
nvl:空值转换函数,如:nvl(null, '空'),结果:空
decode:实现分支功能,如:decode(1,1, '男', 2, '女'),结果:男
userenv:返回环境信息,如:userenv('LANGUAGE'),结果:SIMPLIFIED CHINESE_CHINA.ZHS16GBK
greatest:返回参数的最大值,如:greatest(20,35,18,9),结果:35
least:返回参数的最小值,如:least(20,35,18,9),结果:9

空值的转换
如果对空值NULL不能很好的处理,就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是,在查询雇员表时,将工资sal字段和津贴字段comm进行相加,如果津贴为空,则相加结果也为空,这样容易引起误解。
使用nvl函数,可以转换NULL为实际值。该函数判断字段的内容,如果不为空,返回原值;为空,则返回给定的值。

如下3个函数,分别用新内容代替字段的空值:
nvl(comm, 0):用0代替空的Comm值。
nvl(hiredate, '01-1月-97'):用1997年1月1日代替空的雇佣日期。
nvl(job, '无'):用“无”代替空的职务。
使用nvl函数转换空值。
执行以下查询:
Sql代码 
SELECT  ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROM     emp; 

SELECT ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROM emp;
结果为:
Sql代码 
ENAME      NVL(JOB,'N NVL(HIREDA NVL(COMM,0)  
        ---------------- ------------------- ------------------ -------------------  
        SMITH      CLERK        17-12月-80            0  
        ALLEN      SALESMAN    20-2月 -81          300 

ENAME      NVL(JOB,'N NVL(HIREDA NVL(COMM,0)
---------------- ------------------- ------------------ -------------------
SMITH      CLERK        17-12月-80            0
ALLEN      SALESMAN    20-2月 -81          300
说明:本例中,空日期将显示为“01-1月-97”,空职务显示为“无”,空津贴将显示为0。

最大、最小值函数
greatest返回参数列表中的最大值,least返回参数列表中的最小值。
这两个函数的参数是一个表达式列表,按表达式列表中的第一个表达式的类型对求值后的表达式求得最大或最小值。对字符的比较按ASCII码的顺序进行。如果表达式中有NULL,则返回NULL。

比较字符串的大小,返回最大值。
执行以下查询:
Sql代码 
SELECT  greatest('ABC','ABD','abc', 'abd') FROM dual; 

SELECT greatest('ABC','ABD','abc', 'abd') FROM dual;
执行结果为:
Sql代码 
GRE  
        ------  
        abd 

GRE
------
abd
说明:在上述四个字符串中,大小关系为abd>abc>ABD>ABC。在ASCII码表中,排在后边的字符大,小写字母排在大写字母之后。字符串的比较原则是,先比较第一位,如果相同,则继续比较第二位,依此类推,直到出现大小关系。
分享到:
评论

相关推荐

    oracle 数值型函数,字符型函数,日期函数,转换函数,to_char

    在Oracle数据库中,掌握数值型函数、字符型函数、日期函数以及转换函数是十分关键的,这些函数能够帮助我们高效地处理数据,实现复杂的数据分析和报表生成需求。以下是对这些函数的详细介绍: ### 一、数值型函数 ...

    oracle函数大全分析函数,聚合函数,转换函数,日期型函数,字符型函数,数值型函数,其他函数.docx

    Oracle 函数大全是对 Oracle 数据库中各种函数的总结和分类,包括分析函数、聚合函数、转换函数、日期型函数、字符型函数、数值型函数和其他函数等。 一、分析函数 Oracle 分析函数是 Oracle 数据库中的一种强大...

    long_to_char.zip_LONG_TO_CHAR_long_long to char

    标题"long_to_char.zip_LONG_TO_CHAR_long_long to char"暗示了我们讨论的主题是关于将`LONG`类型(在某些数据库系统中也可能表示为`BIGINT`或`INTEGER`)的字段转换为`CHAR`类型的字段。这种转换可能出于多种原因,...

    ORACLE 自动类型转换

    Oracle数据库支持多种数据类型,如数值型(NUMBER、INTEGER、BINARY_INTEGER等)、字符型(VARCHAR2、CHAR、CLOB等)、日期时间型(DATE、TIMESTAMP等)以及二进制数据类型(RAW、BLOB等)。在某些情况下,当不同...

    oracle-database-programming.rar_oracle_oracle函数

    5. **转换函数**:如`TO_CHAR`将日期或数字转换为字符串格式,`TO_DATE`将字符串转换为日期,`TO_NUMBER`将字符串转换为数值。 6. **系统信息函数**:如`USER`获取当前用户,`DBMS_METADATA`用于获取数据库对象的元...

    oracle函数大全中文

    转换函数用于不同数据类型的转换,如`TO_NUMBER`将字符串转换为数字,`TO_CHAR`将日期或数字转换为字符串,`CAST`通用类型转换,以及`CONVERT`在特定字符集间转换。 5. 聚组函数: 聚组函数在SQL查询中用于汇总数据...

    oracle函数分类.rar

    首先,我们关注的是**数值型函数**。在处理数字数据时,Oracle提供了多种操作函数,如`ROUND`用于四舍五入,`TRUNC`用于截断小数部分,`MOD`用于计算两个数相除的余数,还有`ABS`用于求绝对值等。这些函数可以帮助...

    oracle 函数大全 参考函数 手册 速查 chm格式

    4. 转换函数:如TO_CHAR(字符串转换为日期或数字)、TO_DATE(日期转换为字符串)等,实现不同数据类型间的转换。 5. 条件函数:如IFNULL(如果为空则替换)、NVL(空值替换)、DECODE(根据条件返回值)等,处理...

    oracle函数大全

    TO_CHAR函数可以将数值或日期转换为字符类型,TO_NUMBER则将字符转换为数值类型。例如: ```sql SELECT TO_CHAR(123) FROM dual; -- 将数值123转换为字符'123' SELECT TO_NUMBER('123') FROM dual; -- 将字符'123'...

    单元十一:数据类型的显式转换TOCHAR用于数值型完美版资料.pptx

    在Oracle数据库系统中,`TO_CHAR`函数被广泛用于将数值型数据转换为字符型数据,以便进行格式化输出或者满足特定的显示需求。本单元主要讲解`TO_CHAR`函数在处理数值型数据时的用法和格式化选项。 `TO_CHAR`函数的...

    oracle常用函数chm版

    处理日期和时间数据时,Oracle提供了一系列的日期函数,如`SYSDATE`返回当前系统日期,`ADD_MONTHS`用于添加月份,`EXTRACT`可以提取日期中的年、月、日等部分,`TO_DATE`和`TO_CHAR`则用于日期和字符串之间的转换。...

    oracle离线函数大全.rar

    "Oracle离线函数大全.rar" 提供了Oracle数据库中所有函数的详细信息,包括分析函数、聚合函数、转换函数、日期型函数、字符型函数、数值型函数和其他各种实用函数。这份资源对于Oracle数据库管理员、开发人员以及...

    oracle最全函数大全

    这些函数可以大致分为以下几类:分析函数、聚合函数、转换函数、日期型函数、字符型函数、数值型函数以及其他函数。本文将详细介绍每种类型的函数,并提供具体的使用场景和示例。 #### 二、分析函数 分析函数主要...

    单元十一:数据类型的显式转换TOCHAR用于数值型(1)完美版资料.pptx

    本单元主要探讨了如何使用`TO_CHAR`函数将数值型数据转换为字符串格式,以满足特定的输出需求。`TO_CHAR`函数是Oracle数据库系统中一个非常重要的函数,它允许我们以用户自定义的格式展示数值数据。 `TO_CHAR...

    Oracle sql 函数大全 比较常用的一些 函数 整理

    4. STR():把数值型数据转换为字符型数据。 二、去空格函数 去空格函数是用于删除字符串中的空格的函数。常用的去空格函数有: 1. LTRIM():把字符串头部的空格去掉。 2. RTRIM():把字符串尾部的空格去掉。 三...

    ORACLE_FUNCTION.rar_oracle

    TO_CHAR可以将日期或数值转换为字符串,而TO_DATE则将字符串转换为日期,TO_NUMBER则是将字符串转换为数值。这些函数在处理不同数据类型的字段交互时非常实用。 二、数学运算函数 Oracle内建了多种数学运算函数,如...

    oracle最常用的函数或方法总结

    - `TO_CHAR()` 和 `TO_DATE()`:将日期时间转换为字符串或反之。 4. **条件判断函数** - `IFNULL()` 或 `NVL()`:如果表达式为NULL,则返回替代值。 - `DECODE()`:根据指定条件返回不同的值。 - `CASE` 语句:...

    oracle数据类型及函数

    Oracle 数据库是一个强大的关系型数据库管理系统,其丰富的数据类型和函数是进行数据存储和处理的基础。在Oracle中,数据类型用于定义字段可以存储的数据种类,而函数则帮助我们对数据进行各种操作。以下是对这些...

Global site tag (gtag.js) - Google Analytics