oracle
oracle 十進(jìn)制與十六進(jìn)制的轉(zhuǎn)換
摘要: 十進(jìn)制與十六進(jìn)制的轉(zhuǎn)換
8i以上版本：
十進(jìn)制-->十六進(jìn)制
select to_char(100,'XX') from dual;
十六進(jìn)制-->十進(jìn)制
select to_number('7D','XX') from dual;
其中XX為格式，注意XX的位數(shù)不能小于傳入的參數(shù)。
閱讀全文
查出不連續(xù)，中斷的流水碼
摘要: select b.SEQ - a.SEQ, b.SEQ, a.SEQ from
( select SEQ , ROWNUM RN from (select SEQ from LWH_TEST order by SEQ) ) a,
( select SEQ , ROWNUM RN from (select SEQ from LWH_TEST order by SEQ) ) b
where a.RN+1 = b.RN
and b.SEQ - a.SEQ > 1
閱讀全文
Oracle中如何進(jìn)行進(jìn)制轉(zhuǎn)換(轉(zhuǎn))
摘要: create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_ba
閱讀全文
oracle 10G正則表達(dá)式
摘要: begin
if REGEXP_LIKE('%123456kdAAAAa89879456kjdd', '^[[:alnum:]]+$') then
dbms_output.PUT_LINE('ok');
else
dbms_output.PUT_LINE('NG');
end if;
end;
閱讀全文
oracle計(jì)算一年中53個(gè)周分別的起始與結(jié)束日期
摘要: 取得一年中53個(gè)周的開始和結(jié)束日期
SELECT tab_1.COLUMN_VALUE,
NEXT_DAY(TO_DATE('0101', 'mmdd'),'星期一') + tab_1.COLUMN_VALUE * 7 - 7 AS first_day,
NEXT_DAY(TO_DATE('0101', 'mmdd'),'星期一') + tab_1.COLUMN_VALUE * 7 - 1 AS LAST_DAY
FROM TABLE
(SELECT SPLIT ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53', ',')
FROM DUAL
) tab_1
閱讀全文
oracle nvl(), cont(), decode()函數(shù), case表達(dá)式結(jié)合使用的一條SQL語句
摘要: SELECT nvl(i.tools_model, 'ALL'),
COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN','REPAIR', 'REPAIR', null)) as "維修總次數(shù)",
COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN', null)) as "正常維修次數(shù)",
COUNT(decode(r.maintain_no, 'REPAIR', 'REPAIR', null)) as "返修次數(shù)",
case COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN','REPAIR', 'REPAIR', null))
when 0 then 0
else
COUNT(decode(r.maintain_no, 'REPAI
閱讀全文
慎用Oracle的not in (轉(zhuǎn))
摘要: oracle中和null比較的返回值是unkown
閱讀全文
oracle使用Decode函數(shù)統(tǒng)計(jì)次數(shù)
摘要: count(*) "總次數(shù)",
count(decode(c.status,0,0,null)) "通過次數(shù)",
count(decode(c.status,1,1,null)) "異常次數(shù)"
閱讀全文
oracle 使用多表插入數(shù)據(jù)
摘要: Insert all Insert_into_clause [value_clause] subquery;
閱讀全文
rowconcat 函數(shù)
摘要: 函數(shù)的參數(shù)為SQL語句，查詢結(jié)果為多列，但返回的結(jié)果為一行一列，值以逗號(hào)隔開
閱讀全文
not exists
摘要: SELECT NUMINDEX,STRITEMNO,NUMTYPE,STRMODELNAME,TO_CHAR(DTUPDATE,'YYYY/MM/DD HH24:MI:SS') AS DTUPDATE
FROM ECSM_ITEMNO_INFORMATION e
where not exists ( select 1 from TBL_SMT_ITEMNO i where i.STRITEMNO=e.STRITEMNO and i.CREATEDATE=e.DTUPDATE )
閱讀全文
多行一列轉(zhuǎn)成一行多列的SQL語句
摘要: select max(decode(item,'M',content,'')) "料號(hào)",
max(decode(item,'D',content,'')) DC,
max(decode(item,'C',content,'')) LOTNO,
max(decode(item,'V',content,'')) "廠商名稱",
max(decode(item,'N',content,'')) "廠商代碼" from T_DISENABLE_MATERIAL_INFO where DOCUMENT_ID=4329
閱讀全文
Java調(diào)用Oracle函數(shù)
摘要: Java調(diào)用Oracle函數(shù)
閱讀全文