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_”什么类型。
--测试数据:
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.