PL/SQL Oracle Query With IF Statement

Question

I want to implement a query that only returns the logged in user and displays there record only, which I have done as follows and it works:

SELECT * FROM EMPLOYEE
WHERE UPPER(username) = v('APP_USER')

However, I have another column called User_Type, and a user can be type 1, 2 or 3. If I have a user type of 1, I want the query to also return all the tables records too as user type 1 is an admin.

I thought about doing it like this:

BEGIN
SELECT * FROM Employee 
WHERE upper(username) = v('APP_USER')
IF User_Type = 1
THEN SELECT * FROM Employee
END IF;
END;
/

But it doesn't work in APEX Oracle PLSQL.

Any suggestions?

Rachcha · Accepted Answer · 2013-04-11 12:32:00Z

From what I understand you need to try this:

DECLARE
  emp employee%ROWTYPE; -- Create a record type
  tbl_emp IS TABLE OF emp;
  -- ^^^ Create a table of that record type
  v_user_type employee.user_type%TYPE;
  -- ^^^ Variable to store user type
BEGIN
  SELECT user_type
    INTO v_user_type
    FROM Employee 
   WHERE upper(username) = v('APP_USER');

  IF v_user_type = 1 THEN
    SELECT *
           BULK COLLECT INTO tbl_emp
      FROM employee;
    -- ^^ Returns the entire table
  ELSE
    SELECT *
           BULK COLLECT INTO tbl_emp
      FROM employee;
     WHERE upper(username) = v('APP_USER');
    -- ^^ Returns the row related to the user.
  END IF;
END;
/

The output is stored in the nested table variable tbl_emp.

EDIT:

It can be achieved using pure SQL also, like this:

SELECT *
  FROM employee e
 WHERE EXISTS (SELECT 1
                 FROM employees e_in
                WHERE e_in.user_type = 1
                  AND UPPER(e_in.username) = v('APP_USER'))
    OR UPPER(e.username) = v('APP_USER')

Choose whichever is best suited for you.

drquicksilver · Accepted Answer · 2013-04-11 12:01:31Z

2

You want all records from users with either UPPER(username) being v('APP_USER') or User_Type being 1? Then just use OR:

SELECT * FROM Employee WHERE upper(username) = v('APP_USER') OR User_Type = 1

If that's not what you mean, then can you explain more clearly?

answered Apr 11, 2013 at 12:01

drquicksilver

1,6359 silver badges12 bronze badges

2 Comments

Ibrahiem Rafiq Over a year ago

when doing just with app_user, only return that username records, unless that user has a '1' user type then return the whole table.

drquicksilver Over a year ago

I don't understand. There could be many records satisfying upper(username) = v('APP_USER')?

user359040 · Accepted Answer · 2013-04-11 12:12:52Z

1

Try:

select distinct e2.*
from employee e1
join employee e2 on (e1.username = e2.username or e1.User_Type = 1)
where UPPER(e1.username) = v('APP_USER')

answered Apr 11, 2013 at 12:12

user359040

2 Comments

Rachcha Over a year ago

Very understandable and compact but I'm just a little concerned about the performance as I'm a big distinctphobic. Can you please explain the performance impacts?

user359040 Over a year ago

The distinct is included on the chance that more than one employee record satisfies the UPPER(e1.username) = v('APP_USER') condition - if that will never happen then the distinct can be removed. It would probably have a significant performance impact when querying against a reasonably large table - say, several hundred thousand records - but employee tables are usually relatively small, so the performance impact is likely to be relatively small.

Collectives™ on Stack Overflow

PL/SQL Oracle Query With IF Statement

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related