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;

二. 函数

　　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(数字)：对给定的数字取整数位

 

　　2. c2b函数：clob转blob　

create 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;

　　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;

　　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;

 　　

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

create or replace function splitCount (vSource in varchar2, vDelimiter in varchar2)
return integer
is
    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;

 

　　6. TRUNC()：类似截取函数，按指定的格式截取输入的数据

1. 处理日期
select trunc(sysdate) from dual;--2017/2/13,返回当前时间
select trunc(sysdate,'yy') from dual;--2017/1/1,返回当年第一天
select trunc(sysdate,'mm') from dual;--2017/2/1,返回当月的第一天
select trunc(sysdate,'d') from dual;--2017/2/12,返回当前星期的第一天，即星期天
select trunc(sysdate,'dd') from dual;--2017/2/13,返回当前日期,今天是2017/2/13
select trunc(sysdate ,'HH24') from dual;--2017/2/13 15:00:00,返回本小时的开始时间
select trunc(sysdate ,'MI') from dual;--2017/2/13 15:13:00,返回本分钟的开始时间
select trunc(sysdate-5) from dual;--2017/2/8,返回前五天的时间
select trunc(sysdate-interval'5'month,'mm') from dual;--2016/9/1,返回前5个月的第一天
select trunc(sysdate-interval'5'year,'yy') from dual;--2012/1/1,返回前5年的第一天
select trunc(sysdate)+1-1/86400 from dual;--2017/2/13 23:59:59,返回当天的最后一秒


2. 处理number型数字，截取时并不对数据进行四舍五入
select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去;
select trunc(123.567,-2) from dual;--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
select trunc(123.567) from dual;--123,默认截去小数点后面的部分;

 

三. 序列

　　1. 创建序列

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

　　

　　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;

 

四. 包

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

　　　　a. 包头

CREATE OR REPLACE PACKAGE base_convert AS

FUNCTION 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;

　　　　b. 包体

CREATE OR REPLACE PACKAGE BODY base_convert AS

FUNCTION 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 IS
BEGIN
  RETURN dec_to_bin(hex_to_dec(hexin));
END hex_to_bin;

FUNCTION bin_to_hex (binin IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  RETURN dec_to_hex(bin_to_dec(binin));
END bin_to_hex;

FUNCTION oct_to_bin (octin IN NUMBER) RETURN NUMBER IS
BEGIN
  RETURN dec_to_bin(oct_to_dec(octin));
END oct_to_bin;

FUNCTION bin_to_oct (binin IN NUMBER) RETURN NUMBER IS
BEGIN
  RETURN dec_to_oct(bin_to_dec(binin));
END bin_to_oct;

FUNCTION oct_to_hex (octin IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  RETURN dec_to_hex(oct_to_dec(octin));
END oct_to_hex;

FUNCTION hex_to_oct (hexin IN VARCHAR2) RETURN NUMBER IS
BEGIN
  RETURN dec_to_oct(hex_to_dec(hexin));
END hex_to_oct;

END base_convert;

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