Oracle ²éѯÓû§ÃûÓëÃÜÂëÏàͬµÄËã·¨

Óû§ÃûÓë¿ÚÁîÏàͬʹµÃ¿ÚÁîÒ×ÓÚ¼ÇÒäºÍ²Â²â£¬µ«¿ÚÁîÒ²ÈÝÒ×±»ÆÆÒ룬Òò´Ë×÷ΪÊý¾Ý¿â¹ÜÀíÔ±Ó¦¸Ã¼°Ê±¼ì²éÓû§µÄÉèÖ㬱ÜÃâÓû§ÃûÓë¿ÚÁîÏàͬ£¬Ïû³ý²»°²ÒòËØ¡£
´´½¨²éѯ×Ó³ÌÐò£º
SQL> create or replace procedure sys.find_the_same as
hex_password varchar2(30);
trans_password varchar2(30);
v_username varchar2(30);
cursor c1 is select username, password from dba_users;
begin
for i in c1 loop
--±£´æÓû§×î³õµÄ¿ÚÁîºÍÓû§Ãû£¬¿ÚÁîÒÔÊ®Áù½øÖƱíʾ¡£
hex_password:=i.password;
v_username:=i.username;
--½«¿ÚÁî¸Ä³ÉÓû§Ãû£¬ÏµÍ³½«Æä¼ÓÃܺóÒÔÊ®Áù½øÖƱíʾ¡£
execute immediate 'alter user' || v_username || 'identified by' || v_username;
--²éѯÐ޸ĺóµÄÒÔÊ®Áù½øÖƱíʾµÄÓû§¿ÚÁî¡£
select password into trans_password from dba_users where username=v_username;
--±È½ÏÐÞ¸ÄÇ°ºÍÐ޸ĺóµÄ¿ÚÁî¡£
if trans_password=hex_password then
dbms_output.put_line(v_username);
else
--½«¿ÚÁî¸Ä»ØÔ­À´µÄÖµ
execute immediate 'alter user' || v_username || 'identified by values''' || hex_password || '''';
end if;
end loop;
end;