oracle函數、存儲過程、序列

一. 函數

　　1. c2b函數：clob轉blob　

create or replace function c2b (b in clob default empty_clob())
return blob
is
    res blob;
    bLen number := dbms_lob.getlength(b);
    destOffset1 number := 1;
    srcOffset1 number := 1;
    amountC integer := dbms_lob.lobmaxsize;
    blobCsid number := dbms_lob.default_csid;
    langCtx integer := dbms_lob.default_lang_ctx;
    warning integer;
begin
    if bLen > 0 then
        dbms_lob.createtemporary(res, true);
        dbms_lob.open(res, dbms_lob.lob_readwrite);
        dbms_lob.convertToBlob(res, b, amountC, destOffset1, srcOffset1, blobCsid, langCtx, warning );
    else
        select empty_blob() into res from dual;
    end if;

    return res;
end c2b;

　　2. hexToDec函數：十六進位字元串轉數值型字元串

create or replace function hexToDec(icHex   in   varchar2)
return   varchar2   
is
    iDecimal   integer;
    cNewHex   varchar2(1);
    iHexLen   integer;
    result   integer;
begin
    result   :=0;
    iHexLen   :=   length(icHex);
    for  i  in  1..iHexLen   loop
        cNewHex   :=substr(icHex,iHexLen   -   i   +   1,1);
        select   decode(cNewHex,'A',10,'B',11,'C',12,'D',13,'E',14,'F',15,to_number(cNewHex))
            into   iDecimal   from   dual;
        result   :=   result   +   iDecimal   *   power(16,(i-1));
    end   loop;
    return(to_char(result));
end   hexToDec;

　　3. decToHex函數：數值型字元串轉十六進位字元串

create or replace function decToHex(iDecimal   in   varchar2)
return   varchar2   
is
    nDecimal   integer;
    quotient   integer;
    residue   integer;
    result   varchar2(50);
begin
    nDecimal   :=   to_number(iDecimal);
    loop
        quotient   :=   floor(nDecimal/16);
        residue   :=   nDecimal   mod   16;
        select   decode(residue,10,'A',11,'B',12,'C',13,'D',14,'E',15,'F',to_char(residue))   ||   result
            into   result   from   dual;
        exit   when   quotient   =   0;
        nDecimal   :=   quotient;
    end   loop;
    return(result);
end   decToHex;

二. 序列

　　1. 創建序列

create sequence seqEmp
minvalue 0
maxvalue 99999999
start with 1
increment by 1
nocache;

　　2. seqReset存儲過程：重置序列

create or replace procedure seqReset(vSeqName varchar2)
is
    n number(10);
    tSql varchar2(100);
begin
    execute immediate 'select '||vSeqName||'.nextval from dual' into n;
    n:=-n;
    tSql:='alter sequence '||vSeqName||' increment by '||n;
    execute immediate tSql;
    execute immediate 'select '||vSeqName||'.nextval from dual' into n;
    tSql:='alter sequence '||vSeqName||' increment by 1';
    execute immediate tSql;
end seqReset;