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实现啊……跪谢了!
@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实现啊……跪谢了!
, 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;
, “IN” = r_in -- this line may not be updated because of keyword 'in'
即可双号内区分大小写。
好好优化优化吧
上面那位shiyiwan 写的存储过程不知道测试过没有?还有就是注意事务的提交和会滚,还有事务种类的选择问题。不要一味的问这些傻瓜型问题。
赞同部分观点,这样的问题并不是本质上问题,看看plsql的语法结构就基本上知道怎么转换了,不过 都是经常在csdn上泡的,大家说话还是不要带消极的语气色彩。和谐和谐
先插入,出错了再抛出异常