我无法找到与此问题相关的任何信息，因为它相当具体。情况是，我创建了一个视图，该视图应该根据调用它的用户返回结果。该视图如下：
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您使用的using子句创建视图：
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>
看起来还可以，不是吗？
关于您的问题：您是否有机会使用小写的昵称值？
SQL> update person set nick = 'scott' where id_emp > 3;
2 rows updated.
SQL> select * From v_person;
no rows selected
SQL>
如果不是，您是否可以创建相同的简单示例，清楚地说明您所做的工作？
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句