oracle java数据类型转换函数_oracle中数据类型及函数

1. 字符串类型及函数

------------------------------

create table t_user3(

id number,

username char(20),

password varchar2(20),

stuno varchar2(20)

);

insert into t_user3 (id, useramne, password, stuno) values (1, 'user1001 ', '123456', '110');

------------------------------

字符类型分3种：char(n) varchar(n) varchar2(n)；

char(n)固定长度字符串，假如长度不足n，右边空格补齐；

varchar(n)可变长度字符串，假如长度不足n，右边不会补齐；

varchar2(n)可变长度字符串，Oracle官方推荐使用，向后兼容性好；

char(n) VS varchar2(n)

char(n)查询效率相对较高，varchar2(n)存储空间相对较小；

SQL> select length(userName) as charlength,length(password) as varchar2length from t_user3;

lpad() 向左补全字符串

SQL> select lpad(stuno,6,'0') from t_user3;

rpad() 向右补全字符串

SQL> select rpad(stuno,6,'0') from t_user3;

lower() 返回字符串小写

SQL> select lower(userName) from t_user3;

upper() 返回字符串大写

SQL> select upper(userName) from t_user3;

initcap() 单词首字符大写

SQL> select initcap(userName) from t_user3;

length() 返回字符串长度

SQL> select length(password) from t_user3;

substr() 截取字符串

SQL> select substr(userName,1,2) from t_user3;

instr() 获取字符串出现的位置

SQL> select instr(password,'23') from t_user3;

SQL> select instr(password,'23',2) from t_user3;

SQL> select instr(password,'23',2,1) from t_user3;

ltrim() 删除左侧空格

SQL> select ltrim(userName) from t_user3;

rtrim() 删除右侧空格

SQL> select rtrim(userName) from t_user3;

trim() 删除两侧空格

SQL> select trim(userName) from t_user3;

concat() 串联字符串

SQL> select concat(userName,password) from t_user3;

reverse() 反转字符串

SQL> select reverse(userName) from t_user3;

2. 数值类型及函数

------------------------------

create table t_number(

id number,

n1 number(7,3),

n2 number(5,-3)

);

insert into t_number(id,n1,n2) values (1, -123.456, 0);

insert into t_number(id,n1,n2) values (2, 23.4564, 0);

insert into t_number(id,n1,n2) values (3, 23.4564, 123456);

insert into t_number(id,n1,n2) values (4, 23.4564, 123789);

insert into t_number(id,n1,n2) values (5, 23.4567, 123789);

insert into t_number(id,n1,n2) values (6, 23.4564, 123789);

------------------------------

number是oracle中的数据类型: number(precision,scale);

precision，scale均可选；

precision代表精度，sacle代表小数位的位数;

precision 范围[1,38] scale 范围[-84,127];

举例：12345.678中precision是8;scale是3;

abs() 求绝对值

SQL> select abs(n1) from t_number;

round() 四舍五入

SQL> select round(n1,2) from t_number where id=1;

ceil() 向上取整

SQL> select ceil(n1) from t_number where id=2;

floor 向下取整

SQL> select floor(n1) from t_number where id=2;

mod()取模

SQL> select mod(5,3) from dual;

sign()正负性

SQL> select sign(n1) from t_number where id=1;

sqrt()求平方根

SQL> select sqrt(9) from dual;

power()求乘方

SQL> select power(2,3) from dual;

trunc()截取

SQL> select trunc(123.456,2) from dual;

to_char() 格式化数值

常见的字符匹配有 0、9、，、$、FM、L、C

SQL> select to_char(123.45,'0000.000') from dual;

SQL> select to_char(123.45,'9999.999') from dual;

SQL> select to_char(123123,'99,999,999.99') from dual;

SQL> select to_char(123123.3,'FM99,999,999.99') from dual;

SQL> select to_char(123123.3,'$99,999,999.99') from dual;

SQL> select to_char(123123.3,'L99,999,999.99') from dual;

SQL> select to_char(123123.3,'99,999,999.99C') from dual;

3. 日期类型及函数

------------------------------

create table t_date(

id number,

d1 date,

d2 timestamp(6)

);

insert into t_date values(1,sysdate,systimestamp);

alter table t_date add(d3 date);???

update t_date set d3=to_date('2016-12-20','YYYY-MM-DD') where id=1;

update t_date set d3=to_date('2016-12-20 18:31:34','YYYY-MM-DD HH24:MI:SS') where id=1;

------------------------------

date和timestamp(时间戳)

date包含信息century(世纪信息) year年 month月 day日 hour小时 minute分钟 second秒

timestamp一般用于日期时间要求非常精确的情况，精确到毫秒级；

date类型的常用函数：

SQL> select sysdate from dual;

SQL> select systimestamp from dual;

add_months添加月份

SQL> select add_months(d1,2) from t_date where id=1;

last_day返回指定日期月份的最后一天

SQL> select last_day(d1) from t_date where id=1;

months_between返回两个日期的相差月数

SQL> select months_between(d1,d3) from t_date where id=1;

next_day返回特定日期之后的一周内的日期

SQL> select next_day(d1,2) from t_date where id=1;

trunc截取日期

SQL> select trunc(d1,'YYYY') from t_date where id=1;

SQL> select trunc(d1,'MM') from t_date where id=1;

SQL> select trunc(d1,'DD') from t_date where id=1;

SQL> select trunc(d1,'HH') from t_date where id=1;

SQL> select trunc(d1,'MI') from t_date where id=1;

extract返回日期的某个域

SQL> select extract(year from sysdate) from dual;

SQL> select extract(month from sysdate) from dual;

SQL> select extract(day from sysdate) from dual;

SQL> select extract(hour from systimestamp) from dual;

SQL> select extract(minute from systimestamp) from dual;

SQL> select extract(second from systimestamp) from dual;

to_char将日期转换成字符串

SQL> select to_char(d1,'YYYY-MM-DD') from t_date where id=1;

SQL> select to_char(d1,'YYYY-MM-DD HH24:MI:SS') from t_date where id=1;

4. 其他常用处理函数

max求最大值

SQL> select max(sal) from emp;

min求最小值

SQL> select min(sal) from emp;

avg求平均值

SQL> select avg(sal) from emp;

sum求和

SQL> select sum(sal) from emp;

count统计记录数

SQL> select count(ename) from emp;

nvl空值处理

SQL> select ename,nvl(sal,0) from emp;

rownum行号

SQL> select e.*, rownum from emp e;

oracle分页

SQL> select * from (select a.*,rownum rn from (select * from emp) A where rownum<=10) where rn>5;

oracle中的运算

SQL> select 2+1 from dual;

SQL> select 2-1 from dual;

SQL> select 2*1 from dual;

SQL> select 2/1 from dual;

条件判断式

between and范围查询

SQL> select * from emp where sal between 900 and 1500;

SQL> select * from emp where sal>=900 and sal<=1500;

in集合范围

SQL> select ename,hiredate from emp where ename in (select distinct ename from bonus);

like模糊查询

SQL> select * from emp where ename like '%M%';

SQL> select * from emp where ename like 'M%';

SQL> select * from emp where ename like '_M%';

分享到：

2018-12-19 09:22

浏览 8

分类:数据库

评论