Forum Stats

  • 3,780,531 Users
  • 2,254,407 Discussions
  • 7,879,374 Comments

Discussions

PL/SQL PROCEDURE to Function

User_4G3I3
User_4G3I3 Member Posts: 9 Green Ribbon

How can I write this procedure code as a function?

CREATE OR REPLACE PROCEDURE login_sp

 (p_user IN VARCHAR2,

 p_pass IN VARCHAR2,

 p_id OUT NUMBER,

 p_flag OUT CHAR,

 p_mem OUT VARCHAR2 )

 IS

 lv_first_txt bb_shopper.firstname%TYPE;

 lv_last_txt bb_shopper.lastname%TYPE;

BEGIN

 SELECT idShopper, firstname, lastname

 INTO p_id, lv_first_txt, lv_last_txt

 FROM bb_shopper

 WHERE username = p_user

  AND password = p_pass;

 p_flag := 'Y';

 p_mem := memfmt1_sf(p_id, lv_first_txt, lv_last_txt);

EXCEPTION

 WHEN NO_DATA_FOUND THEN

  p_flag := 'N';

END;

Best Answer

  • mathguy
    mathguy Member Posts: 10,218 Blue Diamond
    Accepted Answer

    If all you need is a 'Y' / 'N' answer, you can do something like this:

    create function login_function(p_user varchar2, p_pass varchar2)
      return char
    as
      p_flag char;
    begin
      select case when exists ( select *
                                from   bb_shopper
                                where  username = p_user and password = p_pass
                              )
                  then  'Y' else 'N' end
        into p_flag
        from dual;
      return p_flag;
    end;
    /
    

    Note that functions may also have OUT parameters, in addition to returning a value. So if you need side effects like placing values (like an ID) in a location you provide to the function, that can be done as well. Not a great way to do things (in my opinion), but it is definitely possible.

    By the way, I hope you are just playing around and not doing this for anything that matters. You aren't storing un-hashed passwords in a table, are you?

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,327 Gold Trophy

    Normally a function returns one value, while your procedure has several out parameters, which disqualifies that for being a function.

    Why do you need a function? Generally a function may be called in some SQL statement, while a procedure can't be called in a SQL statement.

    If you insist on having a function, then you must create a type and have the function return that type.

    Like:

    create type t_answer as object (

     id NUMBER,

     flag CHAR(1), --- suppose a flag can't be more than 1 character long

     mem VARCHAR2(4000)

    );

    CREATE OR REPLACE function f_login

     (p_user IN VARCHAR2,

     p_pass IN VARCHAR2) return t_answer is

    v_answer t_answer;

     lv_first_txt bb_shopper.firstname%TYPE;

     lv_last_txt bb_shopper.lastname%TYPE;

    begin

    begin

     SELECT idShopper, firstname, lastname

     INTO v_answer.id, lv_first_txt, lv_last_txt

     FROM bb_shopper

     WHERE username = p_user

      AND password = p_pass;

    v_answer.flag  := 'Y';

     v_answer.mem := memfmt1_sf(p_id, lv_first_txt, lv_last_txt);

    EXCEPTION

     WHEN NO_DATA_FOUND THEN

      v_answer.flag := 'N';

    end;

    return v_answer;

    end;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,370 Red Diamond

    Hi, @User_4G3I3

    How can I write this procedure code as a function?

    That depends on what you want the function to return.

    What's wrong with a procedure? If you did have a function, how would you use it?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,004 Red Diamond
    SQL> declare
      2      v_answer t_answer;
      3  begin
      4      select  1
      5        into  v_answer.id
      6        from  dual;
      7  end;
      8  /
    declare
    *
    ERROR at line 1:
    ORA-06530: Reference to uninitialized composite
    ORA-06512: at line 4
    
    SQL> declare
      2      v_answer t_answer := t_answer(null,null,null);
      3  begin
      4      select  1
      5        into  v_answer.id
      6        from  dual;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    SY.

  • User_4G3I3
    User_4G3I3 Member Posts: 9 Green Ribbon

    The function returns one value. if user name and password exist in the database then the function will return Y and if not function returns N or "invalid login"

  • User_4G3I3
    User_4G3I3 Member Posts: 9 Green Ribbon
    edited Nov 26, 2021 3:02PM

    @Frank Kulash

    Nothing is wrong with the procedure, just want to try out the function with the same capability.

    I understand the current procedure I have outputs multiple values because of this line " p_mem := memfmt1_sf(p_id, lv_first_txt, lv_last_txt);

    "

    But the function will only have one return value such as

     p_flag:= 'Y';

    Return  p_flag;

    Or when no data is found I use the exception handler.

  • User_H3J7U
    User_H3J7U Member Posts: 810 Silver Trophy

    while a procedure can't be called in a SQL statement.

    SQL expression

  • mathguy
    mathguy Member Posts: 10,218 Blue Diamond
    Accepted Answer

    If all you need is a 'Y' / 'N' answer, you can do something like this:

    create function login_function(p_user varchar2, p_pass varchar2)
      return char
    as
      p_flag char;
    begin
      select case when exists ( select *
                                from   bb_shopper
                                where  username = p_user and password = p_pass
                              )
                  then  'Y' else 'N' end
        into p_flag
        from dual;
      return p_flag;
    end;
    /
    

    Note that functions may also have OUT parameters, in addition to returning a value. So if you need side effects like placing values (like an ID) in a location you provide to the function, that can be done as well. Not a great way to do things (in my opinion), but it is definitely possible.

    By the way, I hope you are just playing around and not doing this for anything that matters. You aren't storing un-hashed passwords in a table, are you?

  • User_4G3I3
    User_4G3I3 Member Posts: 9 Green Ribbon

    @mathguy

    Just practicing :)