-- 给个例子给你,看是不是楼主所需要的:(类似于QQ里面的添加好友) hll@SZTYORA> desc MobileFrends 名称 是否为空? 类型 ---------------------------------------- -------- ------------------- MOBILE NOT NULL VARCHAR2(20) FRENDMOBILE NOT NULL VARCHAR2(20) CDATE NOT NULL DATE UDATE NOT NULL DATE FRENDNAME VARCHAR2(100)mobile :宿主手机号; frendmobile :好友手机号; cdate :创建日期; udate :更新日期; frendname :好友名称;-- 主键:mobile、frendmobile /************************************* * 快速添加好友 *************************************/ procedure addfriendsfast( v_mobile in varchar2, v_list in varchar2) is begin -- v_list 传入的格式: 手机号,手机号,手机号,.... if v_mobile is not null then insert into MobileFrends(mobile, frendmobile, cdate, udate) with a as( select distinct substr(','||v_list||',',instr(','||v_list||',',',',1,level)+1, instr(','||v_list||',',',',1,level+1)-instr(','||v_list||',',',',1,level)-1) frendmobile from dual connect by level <= length(','||v_list||',') - length(replace(','||v_list||',', ',', ''))-1 ), b as(select trim(v_mobile) mobile, trim(a.frendmobile) frendmobile from a) -- 将手机号前后空格去掉 select b.mobile, b.frendmobile, sysdate, sysdate from b where not exists (select 1 from MobileFrends c where c.mobile=b.mobile and c.frendmobile=b.frendmobile) and b.frendmobile is not null; -- and b.frendmobile<>b.mobile; -- 自己不能将自己添加为好友 commit; end if; end addfriendsfast; /************************************* * 添加好友 *************************************/ procedure add_friends( v_mobile in varchar2, v_list in varchar2) as begin -- v_list 传入的格式: 手机号:名称;手机号:名称;手机号:名称;...;手机号:名称 if v_mobile is not null and lengthb(v_mobile)<=20 then insert into add_friends_mem(mobile, frendmobile, frendname) with a as ( select substr(chr(10)||v_list||chr(10),instr(chr(10)||v_list||chr(10),chr(10),1,level)+1, instr(chr(10)||v_list||chr(10),chr(10),1,level+1)-instr(chr(10)||v_list||chr(10),chr(10),1,level)-1)||':' frendmobile from dual connect by level <= length(chr(10)||v_list||chr(10))-length(replace(chr(10)||v_list||chr(10),chr(10),''))-1), b as (select substr(a.frendmobile,1,instr(a.frendmobile,':',1,1)-1) as frendmobile, substr(a.frendmobile,instr(a.frendmobile,':',1,1)+1, instr(a.frendmobile,':',1,2)-instr(a.frendmobile,':',1,1)-1) as frendname from a ) select trim(v_mobile), trim(b.frendmobile), trim(b.frendname) frendname -- 考虑手机号前后带有空格的情况 from b where lengthb(trim(b.frendmobile))<=20 and (lengthb(b.frendname)<=100 or b.frendname is null) and b.frendmobile is not null and trim(v_mobile) is not null; -- and b.frendmobile<>trim(v_mobile); -- 不能将自己添加为自己的好友 -- 去除重复数据: delete from add_friends_mem a1 where rowid not in (select max(rowid) from add_friends_mem a2 group by a2.frendmobile); merge into MobileFrends a using add_friends_mem b on (a.mobile=b.mobile and a.frendmobile=b.frendmobile) when matched then update set a.frendname=nvl(b.frendname,a.frendname),a.udate=decode(b.frendname,null,a.udate,sysdate) where a.frendname<>b.frendname or a.frendname is null when not matched then insert (mobile,frendmobile,cdate,udate,frendname) values (b.mobile,b.frendmobile,sysdate,sysdate,b.frendname); commit; end if; exception when others then rollback; end;
hll@SZTYORA> desc MobileFrends 名称 是否为空? 类型 ---------------------------------------- -------- ------------------- MOBILE NOT NULL VARCHAR2(20) FRENDMOBILE NOT NULL VARCHAR2(20) CDATE NOT NULL DATE UDATE NOT NULL DATE FRENDNAME VARCHAR2(100)mobile :宿主手机号; frendmobile :好友手机号; cdate :创建日期; udate :更新日期; frendname :好友名称;-- 主键:mobile、frendmobile /************************************* * 快速添加好友 *************************************/ procedure addfriendsfast( v_mobile in varchar2, v_list in varchar2) is begin -- v_list 传入的格式: 手机号,手机号,手机号,.... if v_mobile is not null then insert into MobileFrends(mobile, frendmobile, cdate, udate) with a as( select distinct substr(','||v_list||',',instr(','||v_list||',',',',1,level)+1, instr(','||v_list||',',',',1,level+1)-instr(','||v_list||',',',',1,level)-1) frendmobile from dual connect by level <= length(','||v_list||',') - length(replace(','||v_list||',', ',', ''))-1 ), b as(select trim(v_mobile) mobile, trim(a.frendmobile) frendmobile from a) -- 将手机号前后空格去掉 select b.mobile, b.frendmobile, sysdate, sysdate from b where not exists (select 1 from MobileFrends c where c.mobile=b.mobile and c.frendmobile=b.frendmobile) and b.frendmobile is not null; -- and b.frendmobile<>b.mobile; -- 自己不能将自己添加为好友 commit; end if; end addfriendsfast; /************************************* * 添加好友 *************************************/ procedure add_friends( v_mobile in varchar2, v_list in varchar2) as begin -- v_list 传入的格式: 手机号:名称;手机号:名称;手机号:名称;...;手机号:名称 if v_mobile is not null and lengthb(v_mobile)<=20 then insert into add_friends_mem(mobile, frendmobile, frendname) with a as ( select substr(';'||v_list||';',instr(';'||v_list||';',';',1,level)+1, instr(';'||v_list||';',';',1,level+1)-instr(';'||v_list||';',';',1,level)-1)||':' frendmobile from dual connect by level <= length(';'||v_list||';')-length(replace(';'||v_list||';',';',''))-1), b as (select substr(a.frendmobile,1,instr(a.frendmobile,':',1,1)-1) as frendmobile, substr(a.frendmobile,instr(a.frendmobile,':',1,1)+1, instr(a.frendmobile,':',1,2)-instr(a.frendmobile,':',1,1)-1) as frendname from a ) select trim(v_mobile), trim(b.frendmobile), trim(b.frendname) frendname -- 考虑手机号前后带有空格的情况 from b where lengthb(trim(b.frendmobile))<=20 and (lengthb(b.frendname)<=100 or b.frendname is null) and b.frendmobile is not null and trim(v_mobile) is not null; -- and b.frendmobile<>trim(v_mobile); -- 不能将自己添加为自己的好友 -- 去除重复数据: delete from add_friends_mem a1 where rowid not in (select max(rowid) from add_friends_mem a2 group by a2.frendmobile); merge into MobileFrends a using add_friends_mem b on (a.mobile=b.mobile and a.frendmobile=b.frendmobile) when matched then update set a.frendname=nvl(b.frendname,a.frendname),a.udate=decode(b.frendname,null,a.udate,sysdate) where a.frendname<>b.frendname or a.frendname is null when not matched then insert (mobile,frendmobile,cdate,udate,frendname) values (b.mobile,b.frendmobile,sysdate,sysdate,b.frendname); commit; end if; exception when others then rollback; end;
insert into .... select .........
另外如果对集合类型等,可以使用
bulk collect
forall
等语句
hll@SZTYORA> desc MobileFrends
名称 是否为空? 类型
---------------------------------------- -------- -------------------
MOBILE NOT NULL VARCHAR2(20)
FRENDMOBILE NOT NULL VARCHAR2(20)
CDATE NOT NULL DATE
UDATE NOT NULL DATE
FRENDNAME VARCHAR2(100)mobile :宿主手机号;
frendmobile :好友手机号;
cdate :创建日期;
udate :更新日期;
frendname :好友名称;-- 主键:mobile、frendmobile /*************************************
* 快速添加好友
*************************************/
procedure addfriendsfast(
v_mobile in varchar2,
v_list in varchar2)
is
begin
-- v_list 传入的格式: 手机号,手机号,手机号,....
if v_mobile is not null then
insert into MobileFrends(mobile, frendmobile, cdate, udate)
with a as( select distinct substr(','||v_list||',',instr(','||v_list||',',',',1,level)+1,
instr(','||v_list||',',',',1,level+1)-instr(','||v_list||',',',',1,level)-1) frendmobile
from dual
connect by
level <= length(','||v_list||',') - length(replace(','||v_list||',', ',', ''))-1 ),
b as(select trim(v_mobile) mobile, trim(a.frendmobile) frendmobile from a) -- 将手机号前后空格去掉
select b.mobile, b.frendmobile, sysdate, sysdate
from b
where not exists (select 1 from MobileFrends c where c.mobile=b.mobile and c.frendmobile=b.frendmobile)
and b.frendmobile is not null;
-- and b.frendmobile<>b.mobile; -- 自己不能将自己添加为好友
commit;
end if;
end addfriendsfast; /*************************************
* 添加好友
*************************************/
procedure add_friends(
v_mobile in varchar2,
v_list in varchar2) as
begin
-- v_list 传入的格式: 手机号:名称;手机号:名称;手机号:名称;...;手机号:名称
if v_mobile is not null and lengthb(v_mobile)<=20 then
insert into add_friends_mem(mobile, frendmobile, frendname)
with a as (
select substr(chr(10)||v_list||chr(10),instr(chr(10)||v_list||chr(10),chr(10),1,level)+1,
instr(chr(10)||v_list||chr(10),chr(10),1,level+1)-instr(chr(10)||v_list||chr(10),chr(10),1,level)-1)||':' frendmobile
from dual
connect by
level <= length(chr(10)||v_list||chr(10))-length(replace(chr(10)||v_list||chr(10),chr(10),''))-1),
b as (select substr(a.frendmobile,1,instr(a.frendmobile,':',1,1)-1) as frendmobile,
substr(a.frendmobile,instr(a.frendmobile,':',1,1)+1,
instr(a.frendmobile,':',1,2)-instr(a.frendmobile,':',1,1)-1) as frendname
from a )
select trim(v_mobile), trim(b.frendmobile), trim(b.frendname) frendname -- 考虑手机号前后带有空格的情况
from b
where lengthb(trim(b.frendmobile))<=20 and (lengthb(b.frendname)<=100 or b.frendname is null)
and b.frendmobile is not null
and trim(v_mobile) is not null;
-- and b.frendmobile<>trim(v_mobile); -- 不能将自己添加为自己的好友 -- 去除重复数据:
delete from add_friends_mem a1
where rowid not in (select max(rowid) from add_friends_mem a2 group by a2.frendmobile); merge into MobileFrends a
using add_friends_mem b on (a.mobile=b.mobile and a.frendmobile=b.frendmobile)
when matched then
update set a.frendname=nvl(b.frendname,a.frendname),a.udate=decode(b.frendname,null,a.udate,sysdate)
where a.frendname<>b.frendname or a.frendname is null
when not matched then
insert (mobile,frendmobile,cdate,udate,frendname)
values (b.mobile,b.frendmobile,sysdate,sysdate,b.frendname);
commit;
end if;
exception when others then
rollback;
end;
名称 是否为空? 类型
---------------------------------------- -------- -------------------
MOBILE NOT NULL VARCHAR2(20)
FRENDMOBILE NOT NULL VARCHAR2(20)
CDATE NOT NULL DATE
UDATE NOT NULL DATE
FRENDNAME VARCHAR2(100)mobile :宿主手机号;
frendmobile :好友手机号;
cdate :创建日期;
udate :更新日期;
frendname :好友名称;-- 主键:mobile、frendmobile /*************************************
* 快速添加好友
*************************************/
procedure addfriendsfast(
v_mobile in varchar2,
v_list in varchar2)
is
begin
-- v_list 传入的格式: 手机号,手机号,手机号,....
if v_mobile is not null then
insert into MobileFrends(mobile, frendmobile, cdate, udate)
with a as( select distinct substr(','||v_list||',',instr(','||v_list||',',',',1,level)+1,
instr(','||v_list||',',',',1,level+1)-instr(','||v_list||',',',',1,level)-1) frendmobile
from dual
connect by
level <= length(','||v_list||',') - length(replace(','||v_list||',', ',', ''))-1 ),
b as(select trim(v_mobile) mobile, trim(a.frendmobile) frendmobile from a) -- 将手机号前后空格去掉
select b.mobile, b.frendmobile, sysdate, sysdate
from b
where not exists (select 1 from MobileFrends c where c.mobile=b.mobile and c.frendmobile=b.frendmobile)
and b.frendmobile is not null;
-- and b.frendmobile<>b.mobile; -- 自己不能将自己添加为好友
commit;
end if;
end addfriendsfast; /*************************************
* 添加好友
*************************************/
procedure add_friends(
v_mobile in varchar2,
v_list in varchar2) as
begin
-- v_list 传入的格式: 手机号:名称;手机号:名称;手机号:名称;...;手机号:名称
if v_mobile is not null and lengthb(v_mobile)<=20 then
insert into add_friends_mem(mobile, frendmobile, frendname)
with a as (
select substr(';'||v_list||';',instr(';'||v_list||';',';',1,level)+1,
instr(';'||v_list||';',';',1,level+1)-instr(';'||v_list||';',';',1,level)-1)||':' frendmobile
from dual
connect by
level <= length(';'||v_list||';')-length(replace(';'||v_list||';',';',''))-1),
b as (select substr(a.frendmobile,1,instr(a.frendmobile,':',1,1)-1) as frendmobile,
substr(a.frendmobile,instr(a.frendmobile,':',1,1)+1,
instr(a.frendmobile,':',1,2)-instr(a.frendmobile,':',1,1)-1) as frendname
from a )
select trim(v_mobile), trim(b.frendmobile), trim(b.frendname) frendname -- 考虑手机号前后带有空格的情况
from b
where lengthb(trim(b.frendmobile))<=20 and (lengthb(b.frendname)<=100 or b.frendname is null)
and b.frendmobile is not null
and trim(v_mobile) is not null;
-- and b.frendmobile<>trim(v_mobile); -- 不能将自己添加为自己的好友 -- 去除重复数据:
delete from add_friends_mem a1
where rowid not in (select max(rowid) from add_friends_mem a2 group by a2.frendmobile); merge into MobileFrends a
using add_friends_mem b on (a.mobile=b.mobile and a.frendmobile=b.frendmobile)
when matched then
update set a.frendname=nvl(b.frendname,a.frendname),a.udate=decode(b.frendname,null,a.udate,sysdate)
where a.frendname<>b.frendname or a.frendname is null
when not matched then
insert (mobile,frendmobile,cdate,udate,frendname)
values (b.mobile,b.frendmobile,sysdate,sysdate,b.frendname);
commit;
end if;
exception when others then
rollback;
end;