如何将动态参数传递给 MySQL 视图 [英] How to pass dynamic parameters to a MySQL view

问题描述

我在 MySQL 中创建了以下视图:

I have created the following view in MySQL:

CREATE VIEW MYVIEW AS (
SELECT A.FNAME
     , A.LNAME
     , B.EMAIL
FROM EMPLOYEE A, EMPEMAIL B
WHERE A.EID = :empId
  AND A.EID = B.EID
AND B.EMAILTYP = :emailType)

我必须使 empId 和 emailType 动态化，即获得 empId 和 emailType 所需值的结果> 在运行时.

I have to make empId and emailType dynamic i.e. get the result for the desired values of empId and emailType at runtime.

应该对代码进行哪些更改?

What changes should be made in the code?

提前致谢.

推荐答案

只需创建不带参数的视图(即只处理连接):

Just create the view without the parameters (i.e., to take care of the join only):

CREATE VIEW MYVIEW AS (
SELECT A.FNAME
     , A.LNAME
     , B.EMAIL
     , A.EID AS EID             -- added to be used in the WHERE
     , B.EMAILTYP AS EMAILTYP   -- added to be used in the WHERE
FROM EMPLOYEE A, EMPEMAIL B
WHERE A.EID = B.EID)

并在查询时应用动态参数:

And apply the dynamic parameters when you query:

SELECT FNAME, LNAME, EMAIL
FROM   my_view
WHERE  eid = 'your_empId' AND emailtyp = 'your_emailType'

注意上面显示的 WHERE，它使用了 VIEW

Note the WHERE shown above, it uses the two extra fields declared in the VIEW

这篇关于如何将动态参数传递给 MySQL 视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！