Oracle functions, stored procedures, sequences

A. function

1. c2b function: Clob to BLOB

Create or Replace functionC2B (binchClobdefaultEmpty_clob ())returnblob isres blob; Blen Number:=Dbms_lob.getlength (b); DestOffset1 Number:= 1; SrcOffset1 Number:= 1; AMOUNTCinteger:=dbms_lob.lobmaxsize; Blobcsid Number:=Dbms_lob.default_csid; Langctxinteger:=Dbms_lob.default_lang_ctx; Warninginteger;begin    ifBlen> 0  ThenDbms_lob.createtemporary (res, true); Dbms_lob.Open(res, dbms_lob.lob_readwrite);    Dbms_lob.converttoblob (res, B, AMOUNTC, DestOffset1, SrcOffset1, Blobcsid, Langctx, warning); Else        SelectEmpty_blob () intoRes fromdual; End if; returnRes;EndC2B;

View Code

2. Hextodec function: Hexadecimal string to numeric type string

Create or Replace functionHextodec (Ichexinch   varchar2)return   varchar2    isIdecimalinteger; Cnewhexvarchar2(1); Ihexleninteger; Resultinteger;beginResult:=0; Ihexlen:=length (Ichex);  forIinch  1.. Ihexlen Loop Cnewhex:=SUBSTR (Ichex,ihexlen-I+   1,1); SelectDecode (Cnewhex,'A',Ten,'B', One,'C', A,'D', -,'E', -,'F', the, To_number (Cnewhex)) intoIdecimal fromdual; Result:=Result+Idecimal*   Power( -, (i-1)); EndLoop; return(To_char (Result));EndHextodec;

View Code

3. Dectohex function: Numeric string-to-hexadecimal string

Create or Replace functionDecToHex (Idecimalinch   varchar2)return   varchar2    isNdecimalinteger; Quotientinteger; Residueinteger; Resultvarchar2( -);beginNdecimal:=To_number (Idecimal); Loop quotient:=    Floor(Ndecimal/ -); Residue:=Ndecimal MoD -; SelectDecode (Residue,Ten,'A', One,'B', A,'C', -,'D', -,'E', the,'F', To_char (residue))||result intoResult fromdual; Exit    whenQuotient=   0; Ndecimal:=quotient; EndLoop; return(result);EndDecToHex;

View Code

Two. Sequence

1. Create a sequence

Create0 99999999 with 1 by 1 NoCache;

View Code

　　

2. Seqreset stored procedure: Reset sequence

Create or Replace procedureSeqreset (vseqnamevarchar2) isN Number(Ten); TSQLvarchar2( -);begin    ExecuteImmediate'Select'||Vseqname||'. Nextval from dual'  intoN; N:=-N; TSQL:='Alter sequence'||Vseqname||'Increment by'||N; Executeimmediate TSQL; ExecuteImmediate'Select'||Vseqname||'. Nextval from dual'  intoN; TSQL:='Alter sequence'||Vseqname||'Increment by 1'; Executeimmediate TSQL;EndSeqreset;

View Code

Oracle functions, stored procedures, sequences