Sql

#PlSql kodu ile kullanıcı eklenmesi

CREATE OR REPLACE PROCEDURE UNI_DEV.addUser(
       i_username   IN USERS.USERNAME%TYPE,
       i_password   IN USERS.PASSWORD%TYPE,       
       i_email      IN USERS.EMAIL%TYPE, 
       r_output     OUT VARCHAR2)  
IS  
    FUNCTION exist (column_case IN NUMBER,i_for_row IN VARCHAR2)
        RETURN BOOLEAN
    IS
        c_value NUMBER;
        v_return BOOLEAN;
    BEGIN
 
        IF column_case = 1
        THEN
            SELECT COUNT(*) INTO c_value FROM users WHERE username = i_for_row;
        ELSE
            SELECT COUNT(*) INTO c_value FROM users WHERE email = i_for_row;
        END IF;
 
 
        IF c_value >= 1
        THEN
            v_return := TRUE;
        ELSE
            v_return := FALSE;
        END IF;
 
        RETURN v_return;     
    END;
 
BEGIN
 
  IF exist(1, i_username) OR exist(2, i_email)
  THEN
    r_output := 'username/email not avaible  ';
  ELSE
    INSERT INTO users(username,password,email) VALUES(i_username,i_password,i_email);
    r_output := 'success  ';
  END IF;
EXCEPTION
    WHEN OTHERS THEN
        r_output := SQLERRM;
END addUser;
/

Advertisements
Standard