mysql视图加参数_如何在mysql视图中传递动态参数

I have created this 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)

now i want make " empId " and " emailType " dynamic.I mean pass the value at select time.

what need to change in code??

thanx in advance

解决方案

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'

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