Reputation: 89
I need to make a stored function:
This is my code
SELECT count(Dominio) FROM Thogar WHERE DOMINIO='%'
I need to make a stored function where I will write a letter between (U,C,R) and the function will replace the % in the previous code with the selected letter.
How can I do it? Thanks!
Got it working
CREATE FUNCTION `Buscar`(`param` CHAR(1))
RETURNS INT
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE res INT;
SELECT count(Dominio) INTO res FROM Thogar WHERE DOMINIO=param;
RETURN res;
END
Call buscar('C')
Upvotes: 1
Views: 42
Reputation: 7745
This should work:
DROP FUNCTION IF EXISTS myFunc;
DELIMITER $$
CREATE FUNCTION myFunc(
param CHAR(1)
) RETURNS INT;
BEGIN
DECLARE res INT;
SELECT count(Dominio) INTO res FROM Thogar WHERE DOMINIO=param;
RETURN res;
END;
$$
DELIMITER ;
Upvotes: 1
Reputation: 19382
If You want to make stored function with only one sql query, I don't see any normal reason for it.
It will not give You performance gain.
How about simplification?
You can create view:
CREATE VIEW v_dominio_counters AS
SELECT Dominio, count(Dominio) AS counter FROM Thogar GROUP BY Dominio
And then use it:
SELECT counter FROM v_dominio_counters WHERE Dominio = 'U' LIMIT 1;
It will always keep for You ready to use counters that is handy when You have huge table.
Upvotes: 0