I'd like to create a stored procedure or a normal query with values passed with an array.
Example:
CREATE PROCEDURE proc()
BEGIN
DECLARE cont INTEGER;
DECLARE var ARRAY;
SET cont = 0;
SET var = ("hi", "hello", "good", ...)
WHILE cont < 12 DO
SELECT * FROM tablex
WHERE name = var[cont];
SET cont = cont + 1;
END WHILE;
END;
Obviously this is will not work, but I'd like to know how to achieve this.
Try to do it without stored routine -
SET @arr = 'hi,hello,good'; -- your array
SELECT COUNT(*) FROM tablex
WHERE FIND_IN_SET (name, @arr); -- calculate count
Neither of existing answers worked for me, so I ended up implementing my very own (and very first) MySQL procedure.
PROCEDURE db.loop_through_array()
BEGIN
DECLARE var varchar(150) DEFAULT 'hi,hello,good';
DECLARE element varchar(150);
WHILE var != '' DO
SET element = SUBSTRING_INDEX(var, ',', 1);
SELECT * FROM tablex WHERE name = element;
IF LOCATE(',', var) > 0 THEN
SET var = SUBSTRING(var, LOCATE(',', var) + 1);
ELSE
SET var = '';
END IF;
END WHILE;
END
Try something like this:
CREATE PROCEDURE proc()
BEGIN
DECLARE cont INTEGER;
SET cont = 0;
CREATE TEMPORARY TABLE array_table (idx INT, value VARCHAR(20));
INSERT INTO array_table (idx, value) VALUES (1,"hi"),(2,"hello"),(3,"good"),...;
WHILE cont < 12 DO
SELECT * FROM tablex
WHERE name IN (SELECT value FROM array_table WHERE idx = cont);
SET cont = cont + 1;
END WHILE;
END;
an example of WHILE loop inside stored procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS WhileLoopProc$$
CREATE PROCEDURE WhileLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
WHILE x <= 5 DO
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE;
SELECT str;
END$$
DELIMITER ;
you can check this article for examples of arrays.
Relational databases don't do arrays, they do scalars, rows and tables. SQL is largely a declarative, rather than procedural, language.
To count entries in a table, use the COUNT aggregate function:
SELECT COUNT(*)
FROM tablex
WHERE name IN ("hi", "hello", "good", ...)
If you need to handle a variable number of values to match against in a single statement, you can create a temporary table to hold the values instead of using IN:
SELECT COUNT(*)
FROM tablex
JOIN names ON tablex.name=names.name
If you can create a table to store the array values you can do it without writing a loop. Use in() operator.
CREATE TABLE test_strings (element CHAR(6));
INSERT INTO test_strings (element) VALUES ('hi'),('hello'),('good');
SELECT * FROM tablex t
WHERE name IN(SELECT element FROM test_strings)
ORDER BY t.name;
I guess that you just want to:
SELECT * FROM tablex
WHERE name IN ('hi', 'hello', 'good', ...)
Do you have a problem with how to pass an array to a procedure?
