两个Oracle存储过程程序实例

1、create or repl ace functionsp_re place _prop erty_value(v_chvarchar2,v_fromvarchar2,/*v_to varchar2) return varchar2creator:danchencreate time:2008-4-19function:re placetaobao'propertyname and propertyvalue id as groupv_ch属性串;v_from 源属性；v_to目标属性，目标属性可为空，则变成删除属性*/ as-定义返回的返回的属性字符串 result_v varcha

2、r2(200):="-定义剩余属性字符串变量 temp_v varchar2(200):="-定义分号位置 fenhao_address number;-定义临时属性对变量 v_pv varchar2(20);begin if v ch is null or v from is null then return 'error'end if;if instr(v_ch,':') = 0 or instr(v_from,':')= 0 then return 'error'end if;temp_v := v_ch

3、丄loop fenhao_address := instr(temp_v,'');if fenhao address=0 then-没有找到分号，则为最后一组属性名：属性值 v_pv := temp_v;-检查属性是否是要替换的属性 if v_pv != v_from then result_v := result_v|''|v_ pv ;else if v to is not null then result_v := result_v|''|v_to;end if;end if;-跳出循环 exit;else-取出属性对 v_pv := su

4、bstr(temp_v,1,instr(temp_v,'')-1);-检查属性是否是要替换的属性 if v_pv != v_from then result_v := result_v|''|v_ pv ;else if v to is not null then result_v := result_v|''|v_to;end if;end if;-得到剩余的属性对 temp_v := substr(temp_v,instr(temp_v,'')+1);end if;end loop;-对结果进行处理,去掉最左侧的分号 if s

5、ubstr(result_v,1,1)='' thenresult_v := substr(result_v,2);end if;-返回结果return result_v;end sp_re pl ace_ prop erty_value;第一个存储过程使用示例:SQL> select sp_re place_ prop erty_value('33392:118167;33393:107054;33391:118167','33393:107054',”)from dual;SP_RE PLACEPROP ERTY_VALUE('

6、33392:118167;33393:107054;33391:118167','33393:10705433392:118167;33391:118167SQL> select sp_re place_ prop erty_value('33392:118167;33393:107054;33391:118167','33393:107054','33393:100') from dual;SP_RE PLACEPROP ERTY_VALUE('33392:118167;33393:107054;33391:118

7、167','33393:10705433392:118167;33393:100;33391:118167第二个存储过程，检查相关属性对在目标属性串是否存在，常用于select查询语句（使用oracle提供的like查询会不准确）create or repl ace function sp_exist_ pro perty(v_strpvvarchar2,v_ pv varchar2) return number /* creator:danchen create time:2008-4-20function:检查v_pv 在属性串v_strpv中是否全部存在*/ as typ

8、e t_pvs is table of varchar2(50);-保存分解后v_strpv v_pvs t_p vs:=t_ pvs();-保存分解后v_pvs_pvs t_p vs:=t_ pvs();-定义剩余属性字符串变量last_ pvs varchar2(200):="-临时属性变量 temp_ pv varchar2(50);-定义分号位置 fenhao_address number;-定义比较结果,0不存在；1存在 v_check number;v_result number;beginif (v_str pv is null) or (v_ pv is null)

9、then return -1;end if;if instr(v_str pv,':')=0 or instr(v_ pv,':')=0 then return -2;end if;-分解 v_strpv last_ pvs := v_str pv;loop fenhao_address := instr(last_ pvs,'');if fenhao address=0 then-没有找到分号，则为最后一组属性名：属性值；取出属性对 temp_ pv := last_ pvs;v_p vs.extend;v_P vs(v_ pvs.last)

10、:= temp_ pv;-跳出循环 exit;else-取出属性对 temp_ pv := substr(last_ pvs,1,instr(last_ pvs,'')-1);-写入数组 v_p vs.extend;v_P vs(v_ pvs.last) := temp_ pv;-得到剩余的属性对 last_ pvs := substr(last_ pvs,instr(last_ pvs,'')+1);end if;end loop;-分解v_pv last_ pvs := v_pv;loop fenhao_address := instr(last_ pvs,

11、'');if fenhao address=0 then-没有找到分号，则为最后一组属性名：属性值；取出属性对 temp_ pv := last_ pvs;s_p vs.extend;s_p vs(s_ pvs.last) := temp_ pv;-跳出循环 exit;else-取出属性对 temp_ pv := substr(last_ pvs,1,instr(last_ pvs,'')-1);-写入数组 s_p vs.extend;s_p vs(s_ pvs.last) := temp_ pv;-得到剩余的属性对 last_ pvs := substr(la

12、st_ pvs,instr(last_ pvs,'')+1);end if;end loop;-检查是否存在 for i in 1.s_ pvs.count loop v_check := 0;for j in 1.v_ pvs.count loop if v_p vs(j)=s_ pvs(i) then v_check := 1;exit;end if;end loop;-如果在本轮循环中，没有一次为真，则返回 ，不用再继续比较 if v check=0 then v_result := 0;exit;end if;end loop;-如果循环执行完 if v check=1

13、 then v_result := 1;end if;-返回结果 return v_result;end sp_exist_ prop erty;第二个存储过程使用示例:SQL> select sp_exist_ prop erty('33392:118167;33393:107054;33391:118167','1:118167') from dual;SP_EXIST_ PROP ERTY('33392:118167;33393:107054;33391:118167','1:118167')SQL> selec

14、t sp_exist_ prop erty('33392:118167;33393:107054;33391:118167','33393:107054') from dual;SP_EXIST_ PROP ERTY('33392:118167;33393:107054;33391:118167','33393:107054')SQL> select sp_exist_ prop erty('33392:118167;33393:107054;33391:118167','33392:118167;3

15、3393:107054') from dual;SP_EXIST_ PROP ERTY('33392:118167;33393:107054;33391:118167','33392:118167;33393:1SQL> select sp_exist_ prop erty('33392:118167;33393:107054;33391:118167','33392:118167;33393:10705') from dual;SP_EXIST_ PROP ERTY('33392:118167;33393:107054;33391:118167','33392:118167;33393:1SQL