Can I create view with parameter in MySQL?

Question

I have a view like this:

CREATE VIEW MyView AS    SELECT Column FROM Table WHERE Value = 2; 

I'd like to make it more generic, it means to change 2 into a variable. I tried this:

CREATE VIEW MyView AS    SELECT Column FROM Table WHERE Value = @MyVariable; 

But MySQL doesn't allow this.

I found an ugly workaround:

CREATE FUNCTION GetMyVariable() RETURNS INTEGER DETERMINISTIC NO SQL BEGIN RETURN @MyVariable; END| 

And then the view is:

CREATE VIEW MyView AS    SELECT Column FROM Table WHERE Value = GetMyVariable(); 

But it looks really crappy, and the usage is also crappy - I have to set the @MyVariable before each usage of the view.

Is there a solution, that I could use like this:

SELECT Column FROM MyView(2) WHERE (...) 

The concrete situation is as follows: I have a table storing information about the denied request:

CREATE TABLE Denial (     Id INTEGER UNSIGNED AUTO_INCREMENT,         PRIMARY KEY(Id),     DateTime DATETIME NOT NULL,     FeatureId MEDIUMINT UNSIGNED NOT NULL,         FOREIGN KEY (FeatureId)             REFERENCES Feature (Id)             ON UPDATE CASCADE ON DELETE RESTRICT,     UserHostId MEDIUMINT UNSIGNED NOT NULL,         FOREIGN KEY (UserHostId)             REFERENCES UserHost (Id)             ON UPDATE CASCADE ON DELETE RESTRICT,     Multiplicity MEDIUMINT UNSIGNED NOT NULL DEFAULT 1,     UNIQUE INDEX DenialIndex (FeatureId, DateTime, UserHostId) ) ENGINE = InnoDB; 

A multiplicity is a number of identical requests recorded in the same second. I want to display a list of denials, but sometimes, when the application gets denied, it retries a couple times just to make sure. So usually, when the same user gets denial 3 times on the same feature in a couple seconds it is actually one denial. If we'd have one more resource, to fulfill this request, the next two denials would not happen. So we want to group the denials in report allowing the user to specify the timespan in which denials should be grouped. E.g. if we have denials (for user 1 on feature 1) in timestamps: 1,2,24,26,27,45 and user wants to group denials that are closer to each other than 4 sec, he should get something like this: 1 (x2), 24 (x3), 45 (x1). We can assume, that spaces between real denials are much bigger than between duplications. I solved the problem in the following way:

CREATE FUNCTION GetDenialMergingTime()     RETURNS INTEGER UNSIGNED     DETERMINISTIC NO SQL BEGIN     IF ISNULL(@DenialMergingTime) THEN         RETURN 0;     ELSE         RETURN @DenialMergingTime;     END IF; END|  CREATE VIEW MergedDenialsViewHelper AS     SELECT MIN(Second.DateTime) AS GroupTime,         First.FeatureId,         First.UserHostId,         SUM(Second.Multiplicity) AS MultiplicitySum     FROM Denial AS First          JOIN Denial AS Second              ON First.FeatureId = Second.FeatureId                 AND First.UserHostId = Second.UserHostId                 AND First.DateTime >= Second.DateTime                 AND First.DateTime - Second.DateTime < GetDenialMergingTime()     GROUP BY First.DateTime, First.FeatureId, First.UserHostId, First.Licenses;  CREATE VIEW MergedDenials AS     SELECT GroupTime,          FeatureId,         UserHostId,          MAX(MultiplicitySum) AS MultiplicitySum     FROM MergedDenialsViewHelper     GROUP BY GroupTime, FeatureId, UserHostId; 

Then to show denials from user 1 and 2 on features 3 and 4 merged every 5 seconds all you have to do is:

SET @DenialMergingTime := 5; SELECT GroupTime, FeatureId, UserHostId, MultiplicitySum FROM MergedDenials WHERE UserHostId IN (1, 2) AND FeatureId IN (3, 4); 

I use the view because in it it's easy to filter data and to use it explicitly in the jQuery grid, automatically order, limit number of records and so on.

But it's just an ugly workaround. Is there a proper way to do this?

Answer 1

Actually if you create func:

create function p1() returns INTEGER DETERMINISTIC NO SQL return @p1; 

and view:

create view h_parm as select * from sw_hardware_big where unit_id = p1() ; 

Then you can call a view with a parameter:

select s.* from (select @p1:=12 p) parm , h_parm s; 

I hope it helps.

Answer 2

CREATE VIEW MyView AS    SELECT Column, Value FROM Table;   SELECT Column FROM MyView WHERE Value = 1; 

Is the proper solution in MySQL, some other SQLs let you define Views more exactly.

Note: Unless the View is very complicated, MySQL will optimize this just fine.