勿传网上！严禁谋利！ Oracle学习笔记

常彦博

六、单行函数的使用

SQL函数的两种类型：单行函数、多行函数（组函数）。

单行函数：数值类型、日期类型、字符类型、转换函数。处理一列数据，返回一个结果。

6.1数值类型

1）定义：create table tabname

( c1 number,c2 number(6),c3 number(4,3),c4 number(3,-3),c5 number(2,4) ); 2）数值类型说明

①number：不写数值，表可写38位数 ②number(6)：6位整数 999999.1 999999 ③number(4,3)：数字4位，小数点占3位，四舍五入 1.234567 1.235 ④number(3,-3)：小数点前三位不写数，四舍五入，然后有效位3位 1234 1000 ⑤number(2,4)：小数点后4位，有效位2位 0.00991 0.0099 3）数值函数：参数类型为number ①round()：四舍五入函数，“缺省转成数字”；也可对日期 ②trunc()：截取函数（不管多大值直接舍去）；也可对日期 eg：round和trunc round(45.923，2)：45.92 trunc(45.923，2)：45.92 round(45.923，0)：46 round(45.923，-1)：50 trunc(45.923)：45 trunc(45.923，-1)：40 6.2日期类型 1）Oracle用7个字节来存储日期和时间：世纪、年、月、日、时、分、秒。Date不存在定宽度，就是7个字节。 2）缺省（默认）日期格式为DD-MON-RR，格式敏感。 3）sysdate是一个系统函数，返回当前系统时间和日期。 4）改变session（会话）中的日期格式：session和connection是同时建立的，两者是对同一件事情的不同层次的描述。connection是物理上的客户机同服务器端的通信链路；session是逻辑上的用户同服务器的通信交互，SQL语句的运行环境。 eg：显示的日期包含世纪、年、月、日、时、分、秒 alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss'; 5）日期格式 yyyy mm dd hh24 h12 mi ss D day month mon

用数字表达的四位年（2013年） 用数字表达的两位月（01月） 用数字表达的两月日（01日） 用数字表达的24进制的小时（20点） 用数字表达的12进制的小时（8点） 用数字表达的分钟（30分） 用数字表达的小时（30秒） 用数字表达的一周内的第几天（周日：1） 用全拼表达的星期几（sunday） 用全拼表达的月（march） 用简拼表达的月（mar） 11

勿传网上！严禁谋利！ Oracle学习笔记

eg：案例 select to_char(sysdate,'DDD') from dual;年中的第几天 select to_char(sysdate,'DD') from dual;月中的第几天 select to_char(sysdate,'D') from dual;星期中的第几天 6）在数据库中如何处理日期类型 create table test(c1 date); insert into test values ('01-JAN-08'); insert into test values ('2008-08-08');（报错） insert into test values (to_date('2008-08-08','yyyy-mm-dd')); select c1 from test; select to_char(c1,'yyyy-mm-dd') from test; 常彦博

在create table中定义日期类型date时一定不能指定宽度。日期在数据库中用固定的7个字节存储，表示世纪、年、月、日、时、分、秒。缺省的日期格式为'DD-MON-RR'，'01-JAN-08'符合缺省日期格式可以插入表中，因为系统会自动调用to_date函数将它转成日期。 '2008-08-08'插入时报错，原因是不符合缺省格式，需要手工使用函数to_date对字符串的格式进行说明，如'2008-08-08'的格式说明串为'yyyy-mm-dd'。select时日期按缺省日期格式显示，若用指定日期格式，需要使用to_char函数。 7）日期与字符串相互转换： to_date(char,date)函数：将字符串转换成一个日期值。对应java中parse。 to_char(date,char)函数：第一个参数为要处理的日期，第二个参数为格式；可获取一个日期的任意一部分信息；对应java中format。 eg：创建一张表，包含date类型的列，插入2008年8月8日8点8分8秒并显示。 insert into test values (to_date('2008-08-08 08:08:08','yyyy-mm-dd hh24:mi:ss')); select to_char(c1,'yyyy-mm-dd hh24:mi:ss') from test; ? 注意事项： ? 格式必须用单引号括起来，并且大小写敏感。 ? 必须是有效的日期格式。 ? fm能去掉前导0和两端的空格。 ? 对日期去重复问题，to_char获取当天日期即可，时分秒忽略，加上distinct即可做到 eg：案例 where to_char(create_date,'mm')='03'; 若等式右边写成‘3’，‘03’ =‘3’不成立，需要在‘mm’前增加‘fm’。 where to_char(create_date,'fmmm')='3' where to_number(to_char(create_date,'mm')) = 3; 若等式右边写成3，‘03’= 3成立，‘03’是字符类型，3是数字类型，等式两边相等，说明系统做了隐式数据转换，缺省做法将字符转化为number。 8）日期函数：参数类型为date

①add_months()：一个日期加、减一个月。

②months_between()：两个日期之间相差多少个月。

③last_day()：同一个月的最后一天 ④next_day()：根据参数，出现下一个的日期。 eg1：昨天，今天，明天 alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss'; select sysdate-1,sysdate,sysdate+1 from dual; 12

勿传网上！严禁谋利！ Oracle学习笔记

eg2：十分钟之后 alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss'; select sysdate,sysdate + 1/144 from dual; eg3：每台unix服务器上的os帐号开通了多长时间（以天为单位） 常彦博

select unix_host,os_username,create_date,round(sysdate - create_date) days from service; eg4：上个月的今天，今天，下个月的今天 alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss'; select add_months(sysdate,-1),sysdate,add_months(sysdate,1) from dual; eg5：当前月的最后一天 alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss'; select last_day(sysdate) from dual; eg6：用户注册多长时间了 select trunc(months_between(sysdate,create_date))from service; 6.3字符类型 1）定义：create table tabname ( c1 char(10) , c2 varchar2(10) ); 2）char和varchar2区别： ①varchar2必须定义长度，按字符串的实际长度存，最大长度4000字节，更省空间。 ②char可以不定义长度，默认为1，按定义长度存，最大长度2000字节，操作更快。 ③列的取值是定长，定义成char类型。 ④列的取值长度不固定，定义成varchar2。 ? 注意事项： ? 在字符串比较中，varchar2按实际字符串比，对空格是敏感的，对大小些敏感。 ? char会将短字符串补齐后，再与字符串比，对空格不敏感。 ? varchar类型是ANSI定义的，varchar2类型是Oracle定义的，目前是等价的。但如果ANSI对varchar类型定义有变化，则Oracle varchar2类型不变。 eg：案例 varchar2(10)：'abc'='abc' yes ; 'abc'='abc ' no char(10)：'abc '='abc' yes ; 'abc '='abc ' yes 3）字符函数：参数类型为字符 ①upper()：函数将字符串转换成大写。②lower()：函数将字符串转换成小写。 ③initcap()：函数将字符串转换成首字符大写（是将列中的值大小写转换然后去和等号后的字符串比，而不是把转字符串转换去和列比）。

④length()：字符串的长度。⑤rpad()、lpad()：将字符补成同样长度，l和r表左右。 ⑥rtrim()、ltrim()：压缩字符，l和r表左右，与fm相同效果。 ⑦concat()：拼接函数与“| |”相似。⑧substr()：求子串函数。 eg：相关操作 select rpad('FEBRARY',9, '*') from dual; where to_char(create_date,'fmMONTH')='MARCH'; where rtrim(to_char(create_date,'MONTH'))='MARCH'; select concat('ab ','c')from dual; ↓从左往右 ↓从右往左 select os_username,substr(os_username,1,2),substr(os_username,-2,2)from service;

13

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

6.4转换函数

1）to_number()函数：将字符（串）转换成number数值类型，这也是系统的缺省做法，即to_number('03')=3。

? 注意事项：若to_number函数处理的字符串为'ab'，则系统报错，若转换后的值是十

进制的，则要求字符串必须是数字字符。

select to_number('ab') from dual;（报错invalid number） 2）to_char(date,char)函数：第一个参数为要处理的日期，第二个参数为格式；可获取一个日期的任意一部分信息；对应java中format。 3）函数格式说明： 9 0 $ L ． ， 代表数字位 定义宽度大于实际宽度时，0会被强制显示在前面，以补齐位数 美元符号 本地货币符号 小数点 每千位显示一个逗号 ? 注意事项：如果显示位数不足（定义宽度小于实际宽度），用#代替。 eg1：相关操作 select to_char(base_cost,'L99.99') from cost; select to_char(base_cost,'L00.00') from cost; select to_char(base_cost,'$00.00') from cost; eg2：显示月固定费用，单位费用，单位费用为null，显示no unit cost select base_cost,nvl(to_char(unit_cost),'no unit cost') unit_cost 3）to_date(char,date)函数：将字符串转换成一个日期值。对应java中parse。 4）number、字符、data间的转化 ①to_char()：number->字符 date->字符 ②to_number()：字符-> number ③to_date()：字符->date 5）显式隐式转换 ①隐式数据类型转换，系统调用转换函数 where create_date like '%3%';隐式 ②显式数据类型转换，用户调用转换函数 where to_char(create_date,'mm')='03';显式 6.5其他注意事项

1）insert into 表名 values(1，2，3，4，null)有多列时，插入值必须都写，没值的也要写null

2）insert into 表名(C5) values(1234) 表名最多30个字符且不能有特殊字符 3）alter session set nls_language='AMERICAN';28-MAY-13

alter session set nls_language='SIMPLIFIED CHINESE';28-5月 -13 4）alter session set nls_territory = 'AMERICA'; alter session set nls_territory = 'CHINA';

14