Oracle函数使用实例

create table void_test(id number(9) notnull primary key,name varchar2(1000));

insert into void_test(id,name)values(1,'a');

insert into void_test(id,name) values(2,'b');

-----------------字符类函数--------------------

--1.ASCII

select ascii('A') big_a,ascii('a') small_afrom dual;

select * from void_test;

select ascii(name) name_ascii fromvoid_test where id=2;

--2.CHR

select chr(97) from dual;

insert into void_test(id,name)values(99,'chr');

select chr(id) from void_test wherename='chr';

--3.CONCAT

select concat('Oracle','11g') from dual;

select concat(id,name) from void_test;

--4.INITCAP

select initcap('oracle universalinstaller') name from dual;

insert into void_test(id,name)values(3,'the name is true!');

select initcap(name) from void_test;

--5.INSTR

select instr('Moisossoppo','o',3,3) fromdual;

select instr('Moisossoppo','o',-2,3) fromdual;

select instr(name,'e',3,2) from void_test;

--6.LENGTH

select length('Oracle 11g') from dual;

select length(name) from void_test;

--7.LOWER

insert into void_test(id,name)values(4,'The Oracle Version is ?');

select lower(name) from void_test;

--8.LTRIM

insert into void_test(id,name)values(5,'the name is false!');

select ltrim(name,'t') from void_test;

--9.REPLACE

select replace(name,'true','TRUE') fromvoid_test;

--10.SUBSTR

select substr(name,4,6) from void_test;

-----------------数字类函数--------------------

create table void_num_test(id number(9) notnull primary key,numb number(6),arithmetic number(*,5),calculate number(*,-4));

insert intovoid_num_test(id,numb,arithmetic,calculate) values(1,16,12.4,123456);

insert intovoid_num_test(id,numb,arithmetic,calculate) values(2,19,0.6,123456);

insert into void_num_test(id,numb,arithmetic,calculate)values(3,19,-2,16);

select * from void_num_test;

--1.ABS

select abs(arithmetic) from void_num_test;

--2.ACOS

insert intovoid_num_test(id,numb,arithmetic,calculate) values(4,19,-1,16);

insert intovoid_num_test(id,numb,arithmetic,calculate) values(5,19,1,16);

insert intovoid_num_test(id,numb,arithmetic,calculate) values(6,19,0.5,16);

select acos(arithmetic) from void_num_testwhere arithmetic>=-1 and arithmetic<=1;

--3.ASIN

select asin(arithmetic) from void_num_testwhere arithmetic>=-1 and arithmetic<=1;

--4.ATAN

insert intovoid_num_test(id,numb,arithmetic,calculate) values(7,180,0.5,16);

insert intovoid_num_test(id,numb,arithmetic,calculate) values(8,270,0.5,16);

insert intovoid_num_test(id,numb,arithmetic,calculate) values(9,360,0.5,16);

insert intovoid_num_test(id,numb,arithmetic,calculate) values(10,30,0.5,16);

select atan(numb) from void_num_test;

select sin(3.14/6),asin(1),sinh(1) fromdual;

--5.CEIL

select ceil(arithmetic) from void_num_test;

--6.COS

select cos(arithmetic) from void_num_test;

--7.COSH

select cosh(arithmetic) from void_num_test;

--8.EXP

select exp(id) from void_num_test;

--9.FLOOR

select floor(arithmetic) fromvoid_num_test;

--10.LN

select ln(id) from void_num_test;

--11.LOG

select log(numb,id) from void_num_test;

--12.MOD

select mod(numb,id) from void_num_test;

--13.POWER

select power(numb,id) from void_num_test;

--14.ROUND

select round(numb,id) from void_num_test;

--15.SIGN

select sign(arithmetic) from void_num_test;

--16.SIN

select sin(numb) from void_num_test;

--17.SINH

select sinh(id) from void_num_test;

--18.SQRT

select sqrt(calculate) from void_num_test;

--19.TAN

select tan(id) from void_num_test;

--20.TANH

select tanh(id) from void_num_test;

--21.TRUNC

select trunc(calculate,id) from void_num_test;

-----------------日期类函数----------------------

--1.ADD_MONTHS

selectadd_months(to_date('20170101','yyyymmdd'),1) from dual;

--2.LAST_DAY

select last_day(systimestamp) from dual;

selectlast_day(to_date('20170101','yyyymmdd')) from dual;

--3.MONTHS_BETWEEN

selectmonths_between(to_date('20170101','yyyymmdd'),to_date('20160101','yyyymmdd'))from dual;

selectmonths_between(to_date('20160101','yyyymmdd'),to_date('20170101','yyyymmdd'))from dual;

selectmonths_between(to_date('20170101','yyyymmdd'),to_date('20170101','yyyymmdd'))from dual;

--4.NEW_TIME

select dbtimezone from dual;

select to_char(sysdate,'yyyy/mm/ddhh24:mi:ss') 北京,to_char(new_time(sysdate,'EST','GMT'),'yyyy/mm/dd hh24:mi:ss') 格林威治 fromdual;

--5.SYSDATE

select sysdate from dual;

select systimestamp from dual;--精确到毫秒

select zone from dual;

 

-----------------转换类函数----------------------

create table void_change(id number(9) notnull primary key,changed date);

insert into void_change(id,changed) values(1,to_date('2017-05-03','yyyy-mm-dd'));

--1.CHARTORWID

 

--2.CONVERT

 

--3.ROWIDTOCHAR

select rowidtochar(rowid) from dual;

--4.TO_CHAR

select to_char(sysdate) s1 from dual;

--5.TO_DATE

insert into void_change(id,changed)values(2,to_date('2017-04-03','yyyy-mm-dd'));

--6.TO_MULTI_BYTE

select to_multi_byte('abcdefg') s1 fromdual;

--7.TO_NUMBER

select to_number(to_char(changed,'mm'))from void_change;

--8.TO_SINGLE_BYTE

 

----------------------聚合类函数----------------------

create table test_polymerilation(idnumber(8) not null primary key,together number(*,1) );

insert intotest_polymerilation(id,together) values(1,24);

insert into test_polymerilationvalues(2,36)

insert into test_polymerilationvalues(3,46.3);

select * from test_polymerilation;

--1.AVG

select avg(together) fromtest_polymerilation;

--2.COUNT

select count(*) from test_polymerilation;

--3.MAX

select max(together) fromtest_polymerilation;

--4.MIN

select min(together) fromtest_polymerilation;

--6.SUM

select sum(together) from test_polymerilation;

--7.VARIANCE

select variance(together) fromtest_polymerilation;