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

Question

----- 一个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;
/

Answer 1

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

Answer 2

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;
--呵呵：终于搞定了！难怪是永假条件进入循环！

Answer 3

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

Answer 4

把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;
/