sql - 在 Oracle Sql Developer 中视图的 WHERE 子句中使用 USER
问题描述
我一直无法找到与此问题相关的任何信息,因为它相当具体。情况是我创建了一个视图,该视图应该根据调用它的用户返回结果。视图如下:
CREATE VIEW V_ClientReward AS
SELECT cl.id, EXTRACT(YEAR FROM r1.recommendationDate) AS
yearRecommended,
COALESCE((SELECT COUNT(*)
FROM Recommendation r2
WHERE r2.client1 = cl.id AND
EXTRACT(YEAR FROM r2.recommendationDate)= EXTRACT(YEAR FROM
r1.recommendationDate)
AND r2.client2 IN(SELECT id FROM Client WHERE registrationDate IS NOT
NULL) GROUP BY r2.client1), 0) AS numberRecommendedContracted,
COALESCE((SELECT COUNT(*)
FROM Recommendation r2
WHERE r2.client1 = cl.id AND
EXTRACT(YEAR FROM r2.recommendationDate)= EXTRACT(YEAR FROM
r1.recommendationDate)
AND r2.client2 NOT IN(SELECT id FROM Client WHERE registrationDate IS
NOT NULL) GROUP BY r2.client1), 0) AS numberRecommendedNotContracted,
((SELECT rewardImport FROM ClientReward WHERE rewardYear = EXTRACT(YEAR
FROM r1.recommendationDate))*COALESCE((SELECT COUNT(*)
FROM Recommendation r2
WHERE r2.client1 = cl.id AND
EXTRACT(YEAR FROM r2.recommendationDate)= EXTRACT(YEAR FROM
r1.recommendationDate)
AND r2.client2 IN(SELECT id FROM Client WHERE registrationDate IS NOT
NULL)
GROUP BY r2.client1), 0)) AS totalEarned
FROM Client cl INNER JOIN Recommendation r1
ON cl.id=r1.client1
INNER JOIN WebClient
ON idUser = cl.id
WHERE nick = USER
GROUP BY cl.id, EXTRACT(YEAR FROM r1.recommendationDate);
这个想法是,在我授予用户SELECT
使用此视图后,通过调用它的简单行为SELECT * FROM P18.V_ClientReward
应该根据该用户的名称获得结果。不幸的是,当我执行它时,我根本没有得到任何结果,只是空字段。我已经尝试直接执行指定相关用户的查询,WHERE nick = 'user_name'
并且它运行良好。
任何帮助都将不胜感激。
谢谢。
解决方案
我没有你的桌子,所以我用自己的一张。
一、表内容: NICK 列用于区分数据库用户(scott & hr)。该表属于用户 SCOTT。
SQL> connect scott/tiger@xe
Connected.
SQL> select id_emp, fname, nick
2 from person;
ID_EMP FNAME NICK
---------- -------------------- --------------------
1 Little HR
2 Charles HR
3 Rio HR
4 Seb SCOTT
5 Romain SCOTT
创建视图,使用where
您使用的子句:
SQL> create or replace view v_person as
2 select id_emp, fname
3 from person
4 where nick = user;
View created.
向用户 HR 授予权限(否则,它将看不到任何内容):
SQL> grant select on v_person to hr;
Grant succeeded.
好的,让我们看看这些用户看到了什么。
SQL> show user
USER is "SCOTT"
SQL> select * from v_person;
ID_EMP FNAME
---------- --------------------
4 Seb
5 Romain
SQL> connect hr/hr@xe
Connected.
SQL> -- including the owner's name:
SQL> select * from scott.v_person;
ID_EMP FNAME
---------- --------------------
1 Little
2 Charles
3 Rio
SQL>
似乎还可以,不是吗?
至于您的问题:您是否有任何机会使用小写的nick值?
SQL> update person set nick = 'scott' where id_emp > 3;
2 rows updated.
SQL> select * From v_person;
no rows selected
SQL>
如果没有,您能否创建一个相同的简单示例,清楚地显示您所做的事情?
推荐阅读
- xamarin - CollectionView Xamarin 如何加速渲染
- plotly-python - 如何更改 plotly.express.bar 中的曲线标签
- sql - 如何统计同一列中SQL中的变体数量
- file - AppleScript 中的简单移动文件
- c# - 通过代码更新c#中datagridview中复选框列的值
- python - 编辑后以同名存储 PILLOW 图像
- react-native - 如何更改 React Navigation 选项卡栏中图标之间的间距?
- android - java.lang.IllegalStateException: Unable to create layer for WebView, RecyclerView after WebView under NestedScrollView
- angular - 想要在不同的应用程序中加载 Angular Elements 构建工件以在此应用程序中使用 Angular Elements
- javascript - 在新窗口中添加下载按钮