CREATE  proc wj_excelrs @r_user_no varchar(20),@r_name varchar(10),@r_dep_name varchar(30),
@r_sex varchar(2) ,@r_birthday varchar(15),@r_in varchar(20),@r_tel varchar(20),
@r_educational varchar(5),
@r_positions varchar(10),@r_address varchar(30),@r_id_card varchar(20),@login_id int
as
  declare  @user_no varchar(20),@no_count int 
  declare  @dep_name varchar(20),@dep_count int,@dep_id int,@sex varchar
  set @sex = @r_sex
  set @user_no=@r_user_no
  set @dep_name=@r_dep_name
  if @sex='男'
 begin
   set @sex='0'
 end
 else
 begin
  set @sex='1'
 end  select @no_count = count (user_no) from users where user_no=@user_no
  if @no_count=0
    begin
     select @dep_count = count([name]) from department where [name]=@dep_name
     if @dep_count=0
     begin
        insert into department values(@dep_name,0) 
        select @dep_id = [id] from department where [name]=@dep_name order by [id] 
        insert into users values(@r_user_no,'123',@r_name,@dep_id,@sex,@r_birthday,@r_in,@r_tel,@r_educational,@r_positions,@r_address,@r_id_card,null,0,'','','',@login_id)
     end
     else 
     begin
        select @dep_id = [id] from department where [name]=@dep_name order by [id]
        insert into users values(@r_user_no,'123',@r_name,@dep_id,@sex,@r_birthday,@r_in,@r_tel,@r_educational,@r_positions,@r_address,@r_id_card,null,0,'','','',@login_id)
     end
     end
  else
     begin
     select @dep_count = count([name]) from department where [name]=@dep_name
     if @dep_count=0 
     begin
     insert into department values(@dep_name,0)
     select @dep_id = [id] from department where [name]=@dep_name order by [id]
     update users set user_no=@r_user_no,name=@r_name,dep_id=@dep_id,
     sex=@r_sex,birthday=@r_birthday,[in]=@r_in,tel=@r_tel,
     educational=@r_educational,positions=@r_positions,address=@r_address,
     id_card=@r_id_card, SignNo=@login_id where user_no=@r_user_no
     end
     else
     begin
      select @dep_id = [id] from department where [name]=@dep_name order by [id]
     update users set user_no=@r_user_no,name=@r_name,
     sex=@r_sex,birthday=@r_birthday,[in]=@r_in,tel=@r_tel,
     educational=@r_educational,positions=@r_positions,address=@r_address,
     id_card=@r_id_card,SignNo=@login_id where user_no=@r_user_no 
     end
     end
GO
有哪位高人能帮我把这个SQL Server的存储过程用oracle实现啊……跪谢了!

解决方案 »

  1.   

    CREATE OR REPLACE PROCEDURE wj_excelrs ( r_user_no       in varchar2(20)
                                           , r_name          in varchar2(10)
                                           , r_dep_name      in varchar2(30)
                                           , r_sex           in varchar2(2)
                                           , r_birthday      in varchar2(15)
                                           , r_in            in varchar2(20)
                                           , r_tel           in varchar2(20)
                                           , r_educational   in varchar2(5)
                                           , r_positions     in varchar2(10)
                                           , r_address       in varchar2(30)
                                           , r_id_card       in varchar2(20)
                                           , login_id        in number
                                           ) 
    AS
       v_no_count     number      := 0;
       v_dep_count    number      := 0;
       v_dep_id       number      := 0;
       v_sex          varchar2(2) := '1';BEGIN   if r_sex = '男' then
          v_sex := '0';
       end if;
       
       select count(user_no) into v_no_count from users where user_no = r_user_no;   if v_no_count = 0 then
          select count(name) into v_dep_count from department where name = r_dep_name;
          
          if v_dep_count = 0 then
             insert into department values(r_dep_name, 0);
          end if;
          
          select id into v_dep_id from department where name = r_dep_name;
          insert into users values( r_user_no
                                  ,'123'
                                  , r_name
                                  , v_dep_id
                                  , v_sex
                                  , r_birthday
                                  , r_in
                                  , r_tel
                                  , r_educational
                                  , r_positions
                                  , r_address
                                  , r_id_card
                                  , null
                                  , 0
                                  , ' '
                                  , ' '
                                  , ' '
                                  , login_id);   else 
          select count(name) into v_dep_count from department where name = r_dep_name;
          
          if v_dep_count = 0 then
             insert into department values(r_dep_name, 0);
          end if;
          
          select id into v_dep_id from department where name = r_dep_name;
          update users set user_no      = r_user_no
                         , name         = r_name
                         , dep_id       = v_dep_id
                         , sex          = v_sex
                         , birthday     = r_birthday
                     --  , in           = r_in  -- this line may not be updated because of keyword 'in'
                         , tel          = r_tel
                         , educational  = r_educational
                         , positions    = r_positions
                         , address      = r_address
                         , id_card      = r_id_card
                         , signno       = login_id
                     where user_no      = r_user_no;   end if;exception 
       when others then
          dbms_output.put_line(substr(sqlerrm,1,254));
          raise;end wj_excelrs;
      

  2.   

    --  , in           = r_in  -- this line may not be updated because of keyword 'in'改成
    , “IN”           = r_in  -- this line may not be updated because of keyword 'in'
    即可双号内区分大小写。
      

  3.   

    写得垃圾,本来10句就可以实现你的功能的,你看你用了多少行?
    好好优化优化吧
    上面那位shiyiwan 写的存储过程不知道测试过没有?还有就是注意事务的提交和会滚,还有事务种类的选择问题。不要一味的问这些傻瓜型问题。
      

  4.   


    赞同部分观点,这样的问题并不是本质上问题,看看plsql的语法结构就基本上知道怎么转换了,不过 都是经常在csdn上泡的,大家说话还是不要带消极的语气色彩。和谐和谐
      

  5.   

    不要用 count 去做存在性检测 这样很没效率的建议用乐观的方法:
    先插入,出错了再抛出异常