oracle存储过程、函数、序列、包

一. 存储过程

　　1. 语法

create or replace procedure procedureName(seqName varchar2)is  /*声明变量*/  n number(10);  cursor cur is select * from tableName;  /*用来放置游标中的一行*/  cRow cur%rowtype;begin  /*变量赋值*/  n := 5;  /*循环方式一*/  for i in 1..n loop    /*做点什么*/  end loop;  /*循环方式二*/  loop    exit when n = 0;    n := n - 1;  end loop;    /*循环方式三*/  while i < n loop    exit;  end loop;    /*游标用法一：隐式打开和关闭*/  for c in cur loop    /*做点什么*/  end loop;  /*游标用法一：显式打开和关闭*/  open cur;  loop    fetch cur into cRow;    exit when cur%notfound;  end loop;  close cur;  /*修改游标的所在行*/  update tableName set columnName=columnValue where current of cur;  /*判断*/  if (n = 0) then    /*动态执行sql语句*/    execute immediate 'select '||seqName||'.nextval from dual' into n;  else    /*控制台输出*/    dbms_output.put_line(n);  end if;  commit;end procedureName;

View Code

二. 函数

　　1. 常用函数

to_char(timestamp, ''yyyy-mm-dd hh:mm:ss'')：把 timestamp 转换成字符串length(字符串)：计算字符串所占的字符长度，返回字符串长度instr（源字符串, 目标字符串, 开始位置, 第几次出现）：在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。substr(字符串, 截取开始位置, 截取长度)：返回截取的字decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)：它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。power(x,y)：计算x^y次方to_number(字符串)：将字符串转化为数字floor(数字)：对给定的数字取整数位

View Code

 

　　2. 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

　　3. 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

　　4. 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

 　　

　　5. splitCount函数：查找字符串中包含指定字符的个数

create or replace function splitCount (vSource in varchar2, vDelimiter in varchar2)return integeris  j integer;  i integer;  len integer;  delimLen integer;  cnt integer;begin  j := 0;  i := 1;  len := 0;  delimLen := 0;  cnt := 0;  len := length(vSource);  delimLen := length(vDelimiter);  while j < len loop    j := instr(vSource, vDelimiter, i);    if j = 0 then      j := len;      if i >= len then        exit;      end if;    else      i := j + delimLen;      cnt := cnt+1;    end if;  end loop;  return cnt;end splitArrayLength;

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

 

四. 包

　　1. base_convert：常用类型转换函数包

　　　　a. 包头

CREATE OR REPLACE PACKAGE base_convert ASFUNCTION hex_to_dec (hexin IN VARCHAR2) RETURN NUMBER;PRAGMA restrict_references (HEX_TO_DEC,WNDS,RNDS,WNPS,WNPS);FUNCTION dec_to_hex (decin IN NUMBER) RETURN VARCHAR2;PRAGMA restrict_references (DEC_TO_HEX,WNDS,RNDS,WNPS,WNPS);FUNCTION oct_to_dec (octin IN NUMBER) RETURN NUMBER;PRAGMA restrict_references (OCT_TO_DEC,WNDS,RNDS,WNPS,WNPS);FUNCTION dec_to_oct (decin IN NUMBER) RETURN VARCHAR2;PRAGMA restrict_references (DEC_TO_OCT,WNDS,RNDS,WNPS,WNPS);FUNCTION bin_to_dec (binin IN NUMBER) RETURN NUMBER;PRAGMA restrict_references (BIN_TO_DEC,WNDS,RNDS,WNPS,WNPS);FUNCTION dec_to_bin (decin IN NUMBER) RETURN VARCHAR2;PRAGMA restrict_references (DEC_TO_BIN,WNDS,RNDS,WNPS,WNPS);FUNCTION hex_to_bin (hexin IN VARCHAR2) RETURN NUMBER;PRAGMA restrict_references (HEX_TO_BIN,WNDS,RNDS,WNPS,WNPS);FUNCTION bin_to_hex (binin IN NUMBER) RETURN VARCHAR2;PRAGMA restrict_references (BIN_TO_HEX,WNDS,RNDS,WNPS,WNPS);FUNCTION oct_to_bin (octin IN NUMBER) RETURN NUMBER;PRAGMA restrict_references (OCT_TO_BIN,WNDS,RNDS,WNPS,WNPS);FUNCTION bin_to_oct (binin IN NUMBER) RETURN NUMBER;PRAGMA restrict_references (BIN_TO_OCT,WNDS,RNDS,WNPS,WNPS);FUNCTION oct_to_hex (octin IN NUMBER) RETURN VARCHAR2;PRAGMA restrict_references (OCT_TO_HEX,WNDS,RNDS,WNPS,WNPS);FUNCTION hex_to_oct (hexin IN VARCHAR2) RETURN NUMBER;PRAGMA restrict_references (HEX_TO_OCT,WNDS,RNDS,WNPS,WNPS);END base_convert;

View Code

　　　　b. 包体

CREATE OR REPLACE PACKAGE BODY base_convert ASFUNCTION hex_to_dec (hexin IN VARCHAR2) RETURN NUMBER IS v_charpos NUMBER; v_charval CHAR(1); v_return NUMBER DEFAULT 0; v_power NUMBER DEFAULT 0; v_string VARCHAR2(2000);BEGIN v_string := UPPER(hexin); v_charpos := LENGTH(v_string); WHILE v_charpos > 0 LOOP  v_charval := SUBSTR(v_string,v_charpos,1);  IF v_charval BETWEEN '0' AND '9' THEN   v_return := v_return + TO_NUMBER(v_charval) * POWER(16,v_power);  ELSE   IF v_charval = 'A' THEN    v_return := v_return + 10 * POWER(16,v_power);   ELSIF v_charval = 'B' THEN    v_return := v_return + 11 * POWER(16,v_power);   ELSIF v_charval = 'C' THEN    v_return := v_return + 12 * POWER(16,v_power);   ELSIF v_charval = 'D' THEN    v_return := v_return + 13 * POWER(16,v_power);   ELSIF v_charval = 'E' THEN    v_return := v_return + 14 * POWER(16,v_power);   ELSIF v_charval = 'F' THEN    v_return := v_return + 15 * POWER(16,v_power);   ELSE    raise_application_error(-20621,'Invalid input');   END IF;  END IF;  v_charpos := v_charpos - 1;  v_power := v_power + 1; END LOOP; RETURN v_return;END hex_to_dec;FUNCTION dec_to_hex (decin IN NUMBER) RETURN VARCHAR2 IS v_decin NUMBER; v_next_digit NUMBER; v_result varchar(2000);BEGIN v_decin := decin; WHILE v_decin > 0 LOOP  v_next_digit := mod(v_decin,16);  IF v_next_digit > 9 THEN   IF v_next_digit = 10 THEN v_result := 'A' || v_result;   ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;   ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;   ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;   ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;   ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;   ELSE raise_application_error(-20600,'Untrapped exception');   END IF;  ELSE   v_result := to_char(v_next_digit) || v_result;  END IF;  v_decin := floor(v_decin / 16); END LOOP; RETURN v_result;END dec_to_hex;FUNCTION oct_to_dec (octin IN NUMBER) RETURN NUMBER IS v_charpos NUMBER; v_charval CHAR(1); v_return NUMBER DEFAULT 0; v_power NUMBER DEFAULT 0; v_string VARCHAR2(2000);BEGIN v_string := TO_CHAR(octin); v_charpos := LENGTH(v_string); WHILE v_charpos > 0 LOOP  v_charval := SUBSTR(v_string,v_charpos,1);  IF v_charval BETWEEN '0' AND '7' THEN   v_return := v_return + TO_NUMBER(v_charval) * POWER(8,v_power);  ELSE   raise_application_error(-20621,'Invalid input');  END IF;  v_charpos := v_charpos - 1;  v_power := v_power + 1; END LOOP; RETURN v_return;END oct_to_dec;FUNCTION dec_to_oct (decin IN NUMBER) RETURN VARCHAR2 IS v_decin NUMBER; v_next_digit NUMBER; v_result varchar(2000);BEGIN v_decin := decin; WHILE v_decin > 0 LOOP  v_next_digit := mod(v_decin,8);  v_result := to_char(v_next_digit) || v_result;  v_decin := floor(v_decin / 8); END LOOP; RETURN v_result;END dec_to_oct;FUNCTION bin_to_dec (binin IN NUMBER) RETURN NUMBER IS v_charpos NUMBER; v_charval CHAR(1); v_return NUMBER DEFAULT 0; v_power NUMBER DEFAULT 0; v_string VARCHAR2(2000);BEGIN v_string := TO_CHAR(binin); v_charpos := LENGTH(v_string); WHILE v_charpos > 0 LOOP  v_charval := SUBSTR(v_string,v_charpos,1);  IF v_charval BETWEEN '0' AND '1' THEN   v_return := v_return + TO_NUMBER(v_charval) * POWER(2,v_power);  ELSE   raise_application_error(-20621,'Invalid input');  END IF;  v_charpos := v_charpos - 1;  v_power := v_power + 1; END LOOP; RETURN v_return;END bin_to_dec;FUNCTION dec_to_bin (decin IN NUMBER) RETURN VARCHAR2 IS v_decin NUMBER; v_next_digit NUMBER; v_result varchar(2000);BEGIN v_decin := decin; WHILE v_decin > 0 LOOP  v_next_digit := mod(v_decin,2);  v_result := to_char(v_next_digit) || v_result;  v_decin := floor(v_decin / 2); END LOOP; RETURN v_result;END dec_to_bin;FUNCTION hex_to_bin (hexin IN VARCHAR2) RETURN NUMBER ISBEGIN RETURN dec_to_bin(hex_to_dec(hexin));END hex_to_bin;FUNCTION bin_to_hex (binin IN NUMBER) RETURN VARCHAR2 ISBEGIN RETURN dec_to_hex(bin_to_dec(binin));END bin_to_hex;FUNCTION oct_to_bin (octin IN NUMBER) RETURN NUMBER ISBEGIN RETURN dec_to_bin(oct_to_dec(octin));END oct_to_bin;FUNCTION bin_to_oct (binin IN NUMBER) RETURN NUMBER ISBEGIN RETURN dec_to_oct(bin_to_dec(binin));END bin_to_oct;FUNCTION oct_to_hex (octin IN NUMBER) RETURN VARCHAR2 ISBEGIN RETURN dec_to_hex(oct_to_dec(octin));END oct_to_hex;FUNCTION hex_to_oct (hexin IN VARCHAR2) RETURN NUMBER ISBEGIN RETURN dec_to_oct(hex_to_dec(hexin));END hex_to_oct;END base_convert;

View Code

　　　　c. 包中函数的调用，如：base_convert.hex_to_dec(hexString)

原标题：oracle存储过程、函数、序列、包

关键词：oracle