1 Reply Latest reply: Dec 29, 2003 3:13 AM by 3314 RSS

    Oracle PL/SQL Stored Procedure using IF statement

    403084
      Hi,

      I'm having trouble using a PL/SQL stored procedure that takes two input parameters and I want to use an IF statement.

      This is what I have so far (doesn't work)

      CREATE OR REPLACE PROCEDURE CheckPassword

      (txtusername in varchar2, txtpassword in varchar2)

      AS

      BEGIN

      IF EXISTS
      (
      SELECT User_ID FROM UserList WHERE User_Username =
      txtusername AND User_Password = txtpassword;
      )
      THEN
      RETURN 0;

      END IF;

      IF EXISTS
      (
      SELECT User_ID FROM UserList WHERE User_Username = txtusername
      )

      THEN

      RETURN 1;
      ELSE
      RETURN 2;

      END IF;

      END;

      Here's my oracle table that I'm using:

      SQL> select * from userlist;

      USER_ID USER_USERNAME USER_PASSWORD
      ---------- -------------------- -------------------
      1 Jones clouds
      2 Elena soccer

      I want to replace the 'EXISTS' with an If condition such as If (txtusername == "Jones") THEN do the select statement..

      how do I go about creating this condition in an oracle PL/SQL stored procedure?

      I'm also trying to locate some examples.

      Any suggestions would be appreciated.

      This stored procedure will authenticate the user that logs in to an application.

      The parameters above will be assigned the values from the textboxes of the Login page.

      oracleSelectCommand1.Parameters[ "txtusername" ].Value = txtUsername.Text;

      oracleSelectCommand1.Parameters[ "txtpassword" ].Value = txtPassword.Text;

      Thanks.

      bebop




        • 1. Re: Oracle PL/SQL Stored Procedure using IF statement
          3314
          Hi,

          Below is one way you can achieve your requirement.
          CREATE OR REPLACE FUNCTION CheckPassword(txtusername in varchar2, txtpassword in varchar2)
          RETURN NUMBER 
          IS
          chk NUMBER;
          temp NUMBER;
          BEGIN
            BEGIN
              SELECT 1 INTO chk FROM UserList 
              WHERE User_Username = txtusername;
              IF chk=1 THEN
                BEGIN
                  SELECT 1 INTO temp FROM UserList
                  WHERE User_Username = txtusername 
                        AND User_Password = txtpassword;
                  IF temp=1 THEN
                    return 0;
                  END IF;
                EXCEPTION  
                  WHEN NO_DATA_FOUND THEN
                  return 1;
                END;
              END IF;
            EXCEPTION 
              WHEN NO_DATA_FOUND THEN
              return 2;
            END;
          END;
          For example, you can check the above function from the sqlplus using a PL/SQL block like below:
          DECLARE
           cnt number(3);
          BEGIN
           cnt := CheckPassword('scott','tiger');
           dbms_output.put_line('cnt is: '||to_char(cnt));
          END;
          Hope that helps.
          Savitha.