oracle函數、預存程序、序列

標籤：

一. 函數

　　1. c2b函數：clob轉blob　

create or replace function c2b (b in clob default empty_clob())return blobis    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;

View Code

　　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;

View Code

　　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;

View Code

 

二. 序列

　　1. 建立序列

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

View Code

　　

　　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;

View Code

 

oracle函數、預存程序、序列