CREATE PROCEDURE IR_rb_User_UpdateUser
(
@UserID int,
@Name nvarchar(50),
@Email nvarchar(100),
@Password nvarchar(20),
@RoleID int,
@iSuccess int output
)
ASset @iSuccess = -1 --判断是否成功有错误应为插入重复键IF EXISTS (SELECT Email FROM rb_Users WHERE Email = @Email and UserID<>@UserID)
BEGIN
set @iSuccess = -2
return
ENDBEGIN TRAN UPDATE rb_Users SET Name = @Name, Email = @Email, Password = @Password WHERE UserID = @UserID IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END
set @iSuccess = -3 update rb_UserRoles set RoleID=@RoleID where UserID=@UserID IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
ENDCOMMIT TRANset @iSuccess = 1
GO
解决方案 »
- PL/SQL如何在异常处理中捕获异常发生的位置
- 存储过程的导出路径?
- 遇到了一个难题,百思不得其解 ORA-01453错误
- ●●●大家来盖高楼,祝贺penitent,bzszp步步高升,每人5分,加贴结帐●●●
- sql語句中union求助!
- 在oracle 中 这个SQL语句怎么写
- 一个与数据库连接问题,请大侠帮忙。在线等待。
- 来鸟问题--在win2000 sever下无法安装oracle 9i
- 可以把sql文的结果输出到制定的文件吗?
- 刚学用Oracle请高手指定一下,并留QQ方便请教
- spool 数据输出格式的问题.
- 请问在oracle触发的执行动作中,可不可以执行DDL操作?请大家帮我看看,我里面的问题吧。谢谢。
(
p_UserID in integer ,
p_Name in nvarchar2(50),
p_Email in nvarchar2(100),
p_Password in nvarchar2(20),
p_RoleID in integer ,
p_iSuccess out integer
)
is
v_i integer;
beginp_iSuccess := -1 ; --判断是否成功有错误应为插入重复键select count(*) into v_i
FROM rb_Users WHERE Email = p_Email and UserID<>p_UserID;IF v_i>0 then
p_iSuccess := -2;
return;
END if;BEGIN TRAN UPDATE rb_Users SET Name = p_Name, Email = p_Email, Password = p_Password WHERE UserID = p_UserID; p_iSuccess = -3; update rb_UserRoles set RoleID=p_RoleID where UserID=p_UserID;p_iSuccess := 1;
commit;
exception when others then
rollback;end;