现在,创建一个函数来说明某人是否是经理。让它返回1(如果不是)(为什么不是0(为什么不是布尔值?您不能在select语句中使用它))。
SQL> create or replace function f_is_manager(par_app_user in varchar2)
2 return number
3 is
4 retval number := 0;
5 begin
6 select 1
7 into retval
8 from apex_roles
9 where e_mail = par_app_user
10 and role = 'M';
11
12 return retval;
13 exception
14 when no_data_found then
15 -- user isn't a manager
16 return retval;
17 end;
18 /
Function created.
SQL>现在在报表的where子句中使用该功能;假设此表包含您要显示的数据:
SQL> select * from my_data;
E_MAIL JOB SAL
-------------------- --------- ----------
CLARK@gmail.com MANAGER 2450
KING@gmail.com PRESIDENT 5000
MILLER@gmail.com CLERK 1300
lf@gmail.com MANAGER 2200 --> manager in APEX_ROLES table
kamal@yahoo.com CLERK 1500kamal运行报告会怎样?该用户只能看到自己的数据:
SQL> select job, sal
2 from my_data
3 where e_mail = case when f_is_manager('&&APP_USER') = 1 then e_mail
4 else '&&APP_USER'
5 end;
Enter value for app_user: kamal@yahoo.com
JOB SAL
--------- ----------
CLERK 1500但是,如果经理运行它,它将看到每个人的数据:
SQL> select job, sal
2 from my_data
3 where e_mail = case when f_is_manager('&&APP_USER') = 1 then e_mail
4 else '&&APP_USER'
5 end;
Enter value for app_user: lf@gmail.com
JOB SAL
--------- ----------
MANAGER 2450
PRESIDENT 5000
CLERK 1300
MANAGER 2200
CLERK 1500
SQL>因为它是Apex,您实际上会使用
select job, sal
from my_data
where e_mail = case when f_is_manager(:APP_USER) = 1 then e_mail
else ':APP_USER'
end;