oracle
oracle 十进制与十六进制的转换
摘要: 十进制与十六进制的转换
8i以上版本：
十进制-->十六进制
select to_char(100,'XX') from dual;
十六进制-->十进制
select to_number('7D','XX') from dual;
其中XX为格式，注意XX的位数不能小于传入的参数。
阅读全文
posted @
2014-07-30 17:35 Ke 阅读(1425) |
评论 (0) 编辑
查出不连续，中断的流水码
摘要: 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
阅读全文
posted @
2011-02-23 15:55 Ke 阅读(585) |
评论 (3) 编辑
Oracle中如何进行进制转换(轉)
摘要: 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
阅读全文
posted @
2010-05-18 09:44 Ke 阅读(522) |
评论 (0) 编辑
oracle 10G正則表達式
摘要: begin
if REGEXP_LIKE('%123456kdAAAAa89879456kjdd', '^[[:alnum:]]+$') then
dbms_output.PUT_LINE('ok');
else
dbms_output.PUT_LINE('NG');
end if;
end;
阅读全文
posted @
2010-02-20 08:48 Ke 阅读(302) |
评论 (0) 编辑
oracle計算一年中53個周分別的起始與結束日期
摘要: 取得一年中53個周的開始和結束日期
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
阅读全文
posted @
2010-01-22 09:38 Ke 阅读(1793) |
评论 (0) 编辑
oracle nvl(), cont(), decode()函數, case表達式結合使用的一條SQL語句
摘要: SELECT nvl(i.tools_model, 'ALL'),
COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN','REPAIR', 'REPAIR', null)) as "維修總次數",
COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN', null)) as "正常維修次數",
COUNT(decode(r.maintain_no, 'REPAIR', 'REPAIR', null)) as "返修次數",
case COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN','REPAIR', 'REPAIR', null))
when 0 then 0
else
COUNT(decode(r.maintain_no, 'REPAI
阅读全文
posted @
2009-10-29 15:22 Ke 阅读(667) |
评论 (0) 编辑
慎用Oracle的not in (轉)
摘要: oracle中和null比较的返回值是unkown
阅读全文
posted @
2009-10-20 16:05 Ke 阅读(257) |
评论 (0) 编辑
oracle使用Decode函數統計次數
摘要: count(*) "總次數",
count(decode(c.status,0,0,null)) "通過次數",
count(decode(c.status,1,1,null)) "異常次數"
阅读全文
posted @
2009-10-16 14:03 Ke 阅读(622) |
评论 (0) 编辑
oracle 使用多表插入數據
摘要: Insert all Insert_into_clause [value_clause] subquery;
阅读全文
posted @
2009-09-12 09:24 Ke 阅读(898) |
评论 (0) 编辑
rowconcat 函數
摘要: 函數的參數為SQL語句，查詢結果為多列，但返回的結果為一行一列，值以逗號隔開
阅读全文
posted @
2009-09-08 08:12 Ke 阅读(283) |
评论 (0) 编辑
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 )
阅读全文
posted @
2009-09-07 20:15 Ke 阅读(215) |
评论 (0) 编辑
多行一列轉成一行多列的SQL語句
摘要: select max(decode(item,'M',content,'')) "料號",
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
阅读全文
posted @
2009-09-07 20:12 Ke 阅读(2850) |
评论 (0) 编辑
Java调用Oracle函数
摘要: Java调用Oracle函数
阅读全文
posted @
2009-05-12 10:27 Ke 阅读(6361) |
评论 (2) 编辑