Oracle学习笔记 下载本文

勿传网上!严禁谋利! 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