带 IF 语句的 PL/SQL Oracle 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15948224/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL Oracle Query With IF Statement
提问by Ibrahiem Rafiq
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.
但是，我有另一列名为 User_Type，用户可以是类型 1、2 或 3。如果我的用户类型为 1，我希望查询也返回所有表记录，因为用户类型 1 是管理员。
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.
但它在 APEX Oracle PLSQL 中不起作用。
Any suggestions?
有什么建议？
回答by Rachcha
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.
输出存储在嵌套表变量中tbl_emp。
EDIT:
编辑：
It can be achieved using pure SQL also, like this:
也可以使用纯 SQL 来实现，如下所示：
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.
选择最适合您的那个。
回答by drquicksilver
You want all records from users with either UPPER(username)being v('APP_USER')or User_Typebeing 1? Then just use OR:
您希望用户的所有记录UPPER(username)都为 1v('APP_USER')或User_Type为 1？然后只需使用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?
如果这不是你的意思，那么你能解释得更清楚吗？
回答by drquicksilver
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')
