SHARE
TWEET

Untitled

a guest May 23rd, 2018 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PROCEDURE CHANGE_PWD
  2.         (P_USERNAME IN USERS.USERNAME %TYPE,
  3.         P_OLD_PW  IN USERS.PASSWORD %TYPE,
  4.         P_NEW_PW IN USERS.PASSWORD %TYPE,
  5.         P_SUCCES OUT BOOLEAN) IS
  6.  
  7.  
  8.     BEGIN
  9.         SELECT USERS.PASSWORD INTO P_OLD_PW FROM USERS
  10.         WHERE USERS.USERNAME = p_username;
  11.  
  12.     IF p_old_pw = USERS.PASSWORD
  13.  
  14.  
  15.     THEN
  16.         UPDATE USERS
  17.         SET PASSWORD = p_new_pw
  18.         WHERE USERS.USERNAME = p_username;
  19.  
  20.  
  21.  
  22.     ELSE RETURN <error message>;
  23.  
  24.     END IF;
  25.  
  26.     END CHANGE_PWD;
  27.    
  28. SQL> create table users
  29.   2    (username varchar2(30),
  30.   3     password varchar2(20));
  31.  
  32. Table created.
  33.  
  34. SQL> insert into users values ('LITTLEFOOT', '2cellos');
  35.  
  36. 1 row created.
  37.    
  38. SQL> create or replace procedure change_pwd
  39.   2    (p_username   in users.username%type,
  40.   3     p_old_pw     in users.password%type,
  41.   4     p_new_pw     in users.password%type,
  42.   5     p_success   out varchar2
  43.   6    )
  44.   7  is
  45.   8    l_old_pw  users.password%type;
  46.   9  begin
  47.  10    select password
  48.  11      into l_old_pw
  49.  12      from users
  50.  13      where username = p_username;
  51.  14
  52.  15    if l_old_pw = p_old_pw then
  53.  16       -- OK, old password matches
  54.  17       update users set
  55.  18         password = p_new_pw
  56.  19         where username = p_username;
  57.  20       p_success := 'Password changed';
  58.  21    else
  59.  22       p_success := 'Invalid old password';
  60.  23    end if;
  61.  24  exception
  62.  25    when no_data_found then
  63.  26      p_success := 'Invalid username';
  64.  27  end;
  65.  28  /
  66.  
  67. Procedure created.
  68.    
  69. SQL> set serveroutput on;
  70. SQL> declare
  71.   2    l_succ varchar2(100);
  72.   3  begin
  73.   4    change_pwd('LITTLEFOOT', 'xyz', 'newpwd', l_succ);
  74.   5    dbms_output.put_line(l_succ);
  75.   6
  76.   7    change_pwd('bigFOOT', 'abc', 'newpwd', l_succ);
  77.   8    dbms_output.put_line(l_succ);
  78.   9
  79.  10    change_pwd('LITTLEFOOT', '2cellos', 'newpwd', l_succ);
  80.  11    dbms_output.put_line(l_succ);
  81.  12  end;
  82.  13  /
  83. Invalid old password
  84. Invalid username
  85. Password changed
  86.  
  87. PL/SQL procedure successfully completed.
  88.  
  89. SQL>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top