在一个系统里有这样的一个语句 修改密码,先查询密码是否正确,在执行UPDATE动作,
原来语句是这样的
select password,username from developer_user where 1= 1 and username =''and password=’’updata developer_user set password =’ ‘ where 1=1 and username = ‘ “+ username +”’现在想写成一个存储过程或者是写个函数...
原来语句是这样的
select password,username from developer_user where 1= 1 and username =''and password=’’updata developer_user set password =’ ‘ where 1=1 and username = ‘ “+ username +”’现在想写成一个存储过程或者是写个函数...
小心丢失更新啊
即两个人同事查询了第一条select语句
然后又都执行了第二条语句,这样先执行的update的结果会被更新掉
rsCursor SYS_REFCURSOR;begin
OPEN rsCursor for
select t.developer_user_id from developer_user t where t.username = 'mingjian' and t.password ='123';
if rsCursor%FOUND then
begin
update developer_user t set t.password = '321'where t.username='mingjian';
end;
end if;
end updatepassword;
这样写存储过程对不对呀
create or replace procedure updatepassword(username in varchar2,
oldPwd in varchar2,
newPwd in varchar2) is
rsCursor SYS_REFCURSOR;begin
OPEN rsCursor for q ! 'select t.developer_user_id from developer_user t where t.username = :0 and t.password =:1 for update of password nowait' !
using username, oldPwd;
if rsCursor%FOUND then
begin
update developer_user t
set t.password = newPwd
where t.username = username;
end;
end if;
commit;
close rsCursor;
exception
when others then
rollback;
raise;
end updatepassword;