最近2018中文字幕在日韩欧美国产成人片_国产日韩精品一区二区在线_在线观看成年美女黄网色视频_国产精品一区三区五区_国产精彩刺激乱对白_看黄色黄大色黄片免费_人人超碰自拍cao_国产高清av在线_亚洲精品电影av_日韩美女尤物视频网站

--ASCII碼與字符轉(zhuǎn)換

成都創(chuàng)新互聯(lián)公司為您提適合企業(yè)的網(wǎng)站設(shè)計(jì)?讓您的網(wǎng)站在搜索引擎具有高度排名，讓您的網(wǎng)站具備超強(qiáng)的網(wǎng)絡(luò)競(jìng)爭(zhēng)力！結(jié)合企業(yè)自身，進(jìn)行網(wǎng)站設(shè)計(jì)及把握，最后結(jié)合企業(yè)文化和具體宗旨等，才能創(chuàng)作出一份性化解決方案。從網(wǎng)站策劃到網(wǎng)站制作、成都網(wǎng)站建設(shè)， 我們的網(wǎng)頁(yè)設(shè)計(jì)師為您提供的解決方案。

select chr(65)||chr(66)||chr(67) ABC,chr(54678) from dual;

select ascii('諙'),ascii('ABC') from dual;

--獲取字符串長(zhǎng)度函數(shù)

select length('ABCDE我FGHI') from dual;

--字符串截取

select substr('ABCDE我FGHI',5,2),substr('ABCDE我FGHI',-5,2) from dual;

--字符串連接

select concat('Hello',' World!') from dual;

--字符串搜索

select instr('this is a 測(cè)試! ','測(cè)'),instr('this is a 測(cè)試! ','s',-1) from dual;

--字母大小寫(xiě)轉(zhuǎn)換

select upper('this is a test') from dual;

select lower('THIS IS A TEST') from dual;

select initcap('this is a test') from dual;

--為指定參數(shù)排序函數(shù)

select * from productinfo order by nlssort(productname,'NLS_SORT=SCHINESE_PINYIN_M'); --根據(jù)productname字段按拼音排序

--替換字符串函數(shù)

select replace('this is a test','tes','resul') from dual;

--字符串填充函數(shù)

select rpad('test',8,'*rpad'),rpad('test',15,'*rpad'),rpad('test',4,'*rpad') from dual;

--刪除字符串首尾指定字符的函數(shù)

select trim(trailing 't' from 'test'),trim(' test    ') from dual;

select rtrim('test    '),rtrim('test*ffs','fs*') from dual;

select ltrim('     ftest','f') from dual;

--字符集名稱和ID互換

select nls_charset_id('US7ASCII') from dual;

select nls_charset_name(1) from dual;

---

--系統(tǒng)日期、時(shí)間函數(shù)

select to_char(sysdate,'yyyy-MM-dd hh34:mi:ss') from dual;

select systimestamp from dual;

--得到數(shù)據(jù)庫(kù)時(shí)區(qū)函數(shù)

select dbtimezone from dual;

--為日期加上指定月份函數(shù)

select to_char(add_months(to_date('2009-1-30','yyyy-mm-dd'),1),'yyyy-mm-dd') from dual;

--返回當(dāng)前會(huì)話時(shí)區(qū)

select sessiontimezone from dual;

--返回指定月份最后一天

select last_day(sysdate) from dual;

--返回指定日期后一周的日期

select sysdate,next_day(sysdate,'星期一') from dual;

--返回會(huì)話所在時(shí)區(qū)當(dāng)前日期

select sessiontimezone,to_char(current_date,'yyyy-mm-dd hh34:mi:ss') from dual;

--提取指定日期特定部分

select extract(year from sysdate) year,

       extract(minute from timestamp '2010-6-18 12:23:10 ') min,

       extract(second from timestamp '2010-6-18 12:23:10 ') sec

  from dual;

--得到兩個(gè)日期之間的月份數(shù)

select months_between(to_date('2010-7-1', 'yyyy-mm-dd'),

                      to_date('2010-6-1', 'yyyy-mm-dd'))

  from dual;

--時(shí)區(qū)時(shí)間轉(zhuǎn)換

select to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss') one,

       to_char(new_time(sysdate, 'PDT', 'EST'), 'yyyy-mm-dd hh34:mi:ss') two from dual;

--日期四舍五入、截取函數(shù)

select to_char(round(to_date('2010-5-1 21:00:00', 'yyyy-mm-dd hh34:mi:ss')),

               'yyyy-mm-dd hh34:mi:ss')

  from dual;

select to_char(trunc(to_date('2010-5-1 09:00:00', 'yyyy-mm-dd hh34:mi:ss')),

               'yyyy-mm-dd hh34:mi:ss')

  from dual;

--字符串轉(zhuǎn)ASCII類型字符串

select asciistr('這是測(cè)試!') from dual;

--二進(jìn)制轉(zhuǎn)十進(jìn)制

select bin_to_num(1),bin_to_num(1,0,0),bin_to_num(1,1,1) from dual;

--數(shù)據(jù)類型轉(zhuǎn)換函數(shù)

select cast('123' as integer) as vhr,

       cast(123 as varchar2(8)) as num,

       cast(sysdate as varchar2(12)) as dt

  from dual;

--字符串和rowid相互轉(zhuǎn)換

select chartorowid('AAARXnAABAAAVgggAB') from dual;

select rowidtochar('AAARXnAABAAAVggAAB') from dual;

--字符串在字條集間轉(zhuǎn)換

select convert('測(cè)試','US7ASCII') from dual;

--十六進(jìn)制字符串與RAW類型相互轉(zhuǎn)換

select hextoraw('4d') from dual;

select rawtohex('4D') from dual;

select rawtonhex('4D') from dual;

--數(shù)值轉(zhuǎn)換成字符

select to_char(16.89,'99.9'),to_char(16.89) from dual;

select to_char(sysdate, 'yyyy-mm-dd'),

       to_char(sysdate, 'hh34:mi:ss'),

       to_char(sysdate, 'month', 'NLS_DATE_LANGUAGE=ENGLISH')

  from dual;

--字符轉(zhuǎn)日期

select to_char(to_date('2010-7-1','yyyy-mm-dd'),'month') from dual;

select to_char(to_date('2010-7-1','yyyy-mm-dd'),'yyyy-mm-dd') from dual;

--字符串轉(zhuǎn)數(shù)據(jù)

select to_number('2456.304','9999.999') from dual;

--全角轉(zhuǎn)半角

select to_single_byte('Ｔｈｉｓ ｉｓ ａ Ｔｅｓｔ') from dual;

--返回表達(dá)式為NULL的函數(shù)

select coalesce(null,9-9,null) from dual;

--排除指定條件函數(shù)

select * from productinfo where lnnvl(quantity>70);

--替換NULL值函數(shù)

select nvl(null,0) from dual;

select nvl2('true',1,3) from dual;

---

--求平均值函數(shù)

select avg(productprice) from productinfo group by category;

--求記錄量

select count(*) from productinfo;

--最大、最小值函數(shù)

select * from productinfo where productprice=(select max(productprice) from productinfo);

--求和函數(shù)

select sum(all quantity),category from productinfo group by category;

---

--返回登錄名

select user from dual;

--返回會(huì)話以及上下文信息

select userenv('isdba') from dual;

select sys_context('userenv','session_user') session_user from dual;

--表達(dá)式匹配函數(shù)

select productname,quantity,decode(sign(quantity-80),1,'充足',-1,'不足',0,'不足') from productinfo;

分享題目：Oracle內(nèi)置函數(shù)
網(wǎng)站地址：http://fisionsoft.com.cn/article/jgchgd.html