--Oracle存储过程咋就这么难写呢？--

[luoyoumou]

----- 一个SQL Server 2005的存储过程，将其改写为Oracle的,写了半天，还是执行看不到效果！----

---麻烦哪位大侠帮我看看！谢谢！

----------------------------------------------------------------------------------
--/////////////////////////////////////////////////////////////////////////////////////////
---------------------------SQL Server 2005-------------------------------------------------
create table mobileFrends(
mobile varchar(20) not null,
frendMobile varchar(20) not null,
cdate datetime not null default getdate(),
udate datetime not null default getdate());
---------------------------SQL Server 2005-------------------------------------------------
alter procedure  --增加好友手机号
mobileFrends_insert_proc
@mobile varchar(20),
@frendsMobile varchar(max)

/*
exec  mobileFrends_insert_proc '13691147537','13691147547,13699154784,13888888888'
select * from mobileFrends;
*/

as
begin
	CREATE TABLE #TMP_DB(frendMobile varchar(20));

	declare @frendsMobile_str VARCHAR(max); --传入的好友手机字符串
	declare @frendMobile varchar(20);       --分析出来当前的好友手机号
	declare @dot_var int;                       --当前字串中第一个逗号所在位置

	SET @frendsMobile_str=@frendsMobile+',';

	WHILE(len(@frendsMobile_str)>1)
	begin
		SET @dot_var=charindex(',',@frendsMobile_str);
		SET @frendMobile= substring(@frendsMobile_str,1,@dot_var-1);
		SET @frendsMobile_str=substring(@frendsMobile_str,@dot_var+1,len(@frendsMobile_str)-@dot_var);
		insert into #TMP_DB(frendMobile) values(@frendMobile);
	end

	insert into mobileFrends(mobile,frendMobile)
	select @mobile, t.frendMobile
	from #Tmp_DB t
	where isnull(t.frendMobile,'')<>''
	  and t.frendMobile<>@mobile
	  and not exists ( select m.mobile, m.frendMobile 
			     from mobileFrends m 
			    where m.mobile=@mobile
			      and m.frendMobile=t.frendMobile );

	drop table #TMP_DB;

end
------------------------------------------------------------------------
--/////////////////////////////////////////////////////////////////////////////////////////
---------------------------  Oracle 10g   -------------------------------------------------
create table mobileFrends(
mobile varchar2(20) not null,
frendMobile varchar2(20) not null,
cdate date,
udate date);

--临时表
Create Global Temporary Table mobileFrends_tmp_proc
(frendMobile varchar2(20)) On Commit Delete Rows;

-------------------------------------------------------------------------------------------
create or replace procedure  --增加好友手机号
mobileFrends_insert_proc(
v_mobile varchar,
v_frendsMobile clob )

as
  v_frendsMobile_str clob;
  v_frendMObile varchar2(20);
  v_dot_var NUMBER(32,0);
  v_len number(32,0);
begin

  v_frendsMobile_str := v_frendsMobile||',';

  while v_len>1 loop
    v_len := length(v_frendsMobile_str);
    v_dot_var := instr(v_frendsMobile_str,',',1,1);
    v_frendMobile := substr(v_frendsMobile_str,1,v_dot_var-1);
    v_frendsMobile_str := substr(v_frendsMobile_str,v_dot_var+1,length(v_frendsMobile_str)-v_dot_var);
    insert into mobileFrends_tmp_proc(frendMobile) values(v_frendMobile);
  end loop;

  insert into mobileFrends(mobile,frendMobile)
  select v_mobile, t.frendMobile
   from mobileFrends_tmp_proc t
   where nvl(t.frendMobile,'')<>''
     and t.frendMobile<>v_mobile
     and not exists ( select m.mobile, m.frendMobile 
		        from mobileFrends m 
		       where m.mobile=v_mobile
		         and m.frendMobile=t.frendMobile );

	commit;

end mobileFrends_insert_proc;
/

[TheEighthDay]

没学过SQL2005,不便评论,帮顶一下

[duqiangcise]

SQL Server上学时，学过理论课，没怎么实验过。不懂！

[sql_xeppp]

先收藏 明天看一下

[luoyoumou]

create or replace package pkg_mobileFrends_insert
as
  procedure mobileFrends_insert_proc(v_mobile varchar2,v_frendsMobile clob);
end pkg_mobileFrends_insert;
/

create or replace package body pkg_mobileFrends_insert
as
   procedure mobileFrends_insert_proc(v_mobile varchar2,v_frendsMobile clob)
   is
    v_frendsMobile_str clob;
    v_frendMObile varchar2(20);
    v_dot_var NUMBER(18,0);
  begin

    v_frendsMobile_str := v_frendsMobile||',';

    while length(v_frendsMobile_str)>1 loop
    begin
      v_dot_var := instr(v_frendsMobile_str,',',1,1);
      v_frendMobile := substr(v_frendsMobile_str,1,v_dot_var-1);
      v_frendsMobile_str := substr(v_frendsMobile_str,v_dot_var+1,length(v_frendsMobile_str)-v_dot_var);
      insert into mobileFrends_tmp_proc(mobile,frendMobile) values(v_mobile,v_frendMobile);
    end;
    end loop;

  insert into mobileFrends(mobile,frendMobile)
  select t.mobile, t.frendMobile
   from mobileFrends_tmp_proc t
   where not exists ( select m.mobile, m.frendMobile 
		        from mobileFrends m 
		       where m.mobile=t.mobile
		         and m.frendMobile=t.frendMobile );

   commit;

  end mobileFrends_insert_proc;
end pkg_mobileFrends_insert;
/

set serveroutput on;
exec pkg_mobileFrends_insert.mobileFrends_insert_proc('13691147539','13498076654');
select * from mobileFrends;

--呵呵：终于搞定了！难怪是永假条件进入循环！

[小灰狼W]

呵呵，你自己先发现了
while v_len>1 loop
前面缺了v_len的定义。v_len为空时不能用>进行比较，不会进入loop
nvl(t.frendMobile,'')<>''
也要改成t.frendmobile is not null

[crazylaa]

[Quote=引用 5 楼 wildwave 的回复:]
呵呵，你自己先发现了
while v_len>1 loop
前面缺了v_len的定义。v_len为空时不能用>进行比较，不会进入loop
nvl(t.frendMobile,'') <>''
也要改成t.frendmobile is not null
[/Quote]
真细心啊。。

[angello11]

学习

[shiyiwan]

把mobileFrends的(mobile,frendMobile)设置为联合主键，然后取消那个临时表，代码可以改成：

create or replace package body pkg_mobileFrends_insert
as
   procedure mobileFrends_insert_proc(v_mobile varchar2,v_frendsMobile clob)
   is
    v_frendsMobile_str clob;
    v_frendMObile varchar2(20);
    v_dot_var NUMBER(18,0);
  begin

    v_frendsMobile_str := v_frendsMobile||',';

    while length(v_frendsMobile_str)>1 loop
    begin
      v_dot_var := instr(v_frendsMobile_str,',',1,1);
      v_frendMobile := substr(v_frendsMobile_str,1,v_dot_var-1);
      v_frendsMobile_str := substr(v_frendsMobile_str,v_dot_var+1,length(v_frendsMobile_str)-v_dot_var);
      insert into mobileFrends(mobile,frendMobile) values(v_mobile,v_frendMobile);
    exception
      when DUP_VAL_ON_INDEXCREATE then
         null;
    end;
    end loop;

   commit;
  end mobileFrends_insert_proc;
end pkg_mobileFrends_insert;
/