oracle常见函数的使用

--求各人姓名的长度
--规律：函数+"B":表示按字节计算，如lengthB
select LengthB('abcd朗沃') from dual
select t.stu_name,length(t.stu_name) from t_stu t
--大小写转换
select  upper(t.stu_name),lower(t.stu_name) from t_stu t
--应用:用户名(不区别大小写)
select count(*) from t_stu where lower(stu_name)=lower('mike')
select count(*) from t_stu where stu_name='mike'
--字符串处理
--Substr(字符串，从哪开始取，取几个)
select Substr('123abc456', 2, 3) from dual
--Instr(字符串，子串，从哪开始算，算第几次出现的
select Instr('1ab3ab', 'ab', 3, 1)from dual

--要求姓名不显示LOVO
select t.stu_id,substr(t.stu_name,5) stu_name from t_stu t
where t.stu_name like 'LOVO%'
union all
select t.stu_id,t.stu_name from t_stu t
where t.stu_name not like 'LOVO%'

--类型转化
--数字转化为字符串
--要求：编号以数字打头，但有前置0保持为5位
select to_char(78,'******') from dual
select lpad('78',6,'*') from dual

--日期转化为字符串(sysdate:system date,系统日期)
select to_char(sysdate,'yyyy.mm.dd') from dual
select to_date('2007.1.23','yyyy.mm.dd') from dual

--转化为数字
select cast('1.156' as number(3,2)) from dual
select cast(1/3 as number(3,2)) from dual
--数据导入导出时常用
select to_number('$6,102.23','$9,999.99') from dual;

--规律：要什么类型，就“to_”什么类型。

评论

1 楼 java1573 2007-08-01  

--测试数据：

prompt PL/SQL Developer import file
prompt Created on 2007-07-31 by Administrator
set feedback off
set define off
prompt Dropping T_STU...
drop table T_STU cascade constraints;
prompt Creating T_STU...
create table T_STU
(
  STU_ID   NUMBER not null,
  STU_NAME VARCHAR2(10),
  STU_SFZ  VARCHAR2(20),
  AGE      NUMBER,
  DATE_REG DATE,
  FEN_YW   NUMBER(5,2),
  FEN_SX   NUMBER(5,2)
)
;
comment on column T_STU.STU_ID
  is '学号';
comment on column T_STU.STU_NAME
  is '姓名';
comment on column T_STU.STU_SFZ
  is '身份证';
comment on column T_STU.AGE
  is '年龄';
comment on column T_STU.DATE_REG
  is '注册日期';
comment on column T_STU.FEN_YW
  is '语文';
comment on column T_STU.FEN_SX
  is '数学';
alter table T_STU
  add constraint PK_STU primary key (STU_ID);
alter table T_STU
  add constraint CHK_1111
  check (age>16);

prompt Disabling triggers for T_STU...
alter table T_STU disable all triggers;
prompt Loading T_STU...
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (107, 'LOVO王1', '100004', 29, null, 90, null);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (108, '123朱', '100005', 39, null, 80, 60);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (109, 'LOVO王2', '100006', 49, null, 70.5, 80);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (110, 'LOVO李', '100007', 99, null, 80, 50);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (104, 'LOVO赵', '100001', null, to_date('14-01-1990 11:12:13', 'dd-mm-yyyy hh24:mi:ss'), 58, null);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (105, '王3', '100002', null, to_date('14-02-1985', 'dd-mm-yyyy'), null, 60);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (106, '%孙', '100003', 19, to_date('14-03-1980', 'dd-mm-yyyy'), null, 70);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (117, 'mary', null, null, null, null, null);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (118, 'MIKE', null, null, null, null, null);
commit;
prompt 9 records loaded
prompt Enabling triggers for T_STU...
alter table T_STU enable all triggers;
set feedback on
set define on
prompt Done.