你的位置:首页 > 数据库

[数据库]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