oracle函數、存儲過程、序列，oracle存儲過程
一. 存儲過程
1. 語法
![]()
create or replace procedure procedureName(seqName varchar2)
is
/*聲明變量*/
n number(10);
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;
/*判斷*/
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 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
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 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;
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
