当前位置：Gxlcms > 数据库问题 > oracle函数、存储过程、序列

oracle函数、存储过程、序列

时间：2021-07-01 10:21:17 帮助过：2人阅读

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; 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 seqEmp
minvalue 0
maxvalue 99999999
start with 1
increment by 1
nocache;

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函数、存储过程、序列

标签：

oracle函数、存储过程、序列

人气教程排行