oracle据库存储包,[数据库]oracle存储过程、函数、序列、包

[数据库]oracle存储过程、函数、序列、包

0 2016-07-02 19:00:05

一. 存储过程

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)

转载请保留本文网址：http://www.shaoqun.com/a/235567.html

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：admin@shaoqun.com。

oracle

0