I am trying to define and initialize a MySQL variable for a query.
I have the following:
DECLARE @countTotal INT;
SET @countTotal = SELECT COUNT(*) FROM nGrams;
I am using MySQL in Netbeans and it tells me I have an error. What/where is my error?
How can I fix this?
MySQL has two different types of variable:
local variables (which are not prefixed by @) are strongly typed and scoped to the stored program block in which they are declared. Note that, as documented under DECLARE Syntax:
DECLAREis permitted only inside aBEGIN ... ENDcompound statement and must be at its start, before any other statements.
user variables (which are prefixed by @) are loosely typed and scoped to the session. Note that they neither need nor can be declared—just use them directly.
Therefore, if you are defining a stored program and actually do want a "local variable", per the wording in your question, you will need to drop the @ character and ensure that your DECLARE statement is at the start of your program block. Otherwise, to use a "user variable", drop the DECLARE statement.
Furthermore, you will either need to surround your query in parentheses in order to execute it as a subquery:
SET @countTotal = (SELECT COUNT(*) FROM nGrams);
Or else, you could use SELECT ... INTO:
SELECT COUNT(*) INTO @countTotal FROM nGrams;
Try this:-
select @countTotal := COUNT(*) from nGrams;
:= assignment operator, rather than the = equality operator?Function example:
DROP FUNCTION IF EXISTS test;
DELIMITER $$
CREATE FUNCTION test(in_number INT) RETURNS INT
BEGIN
DECLARE countTotal INT;
SET countTotal = SELECT COUNT(*) FROM nGrams;
RETURN countTotal + in_number;
END $$
DELIMITER ;
According to DECLARE Syntax, declare must be inside a begin...end block.
You can set local variables with DECLARE statement as shown below:
DELIMITER $$
CREATE FUNCTION my_func() RETURNS INT
DETERMINISTIC
BEGIN
DECLARE v1, v2 INT DEFAULT 2; -- v1 and v2 are 2.
DECLARE v3, v4 INT DEFAULT v1 + v2; -- v3 and v4 are 4.
DECLARE result INT; -- result is NULL.
SELECT v3 + v4 INTO result; -- result is 8.
RETURN result; -- 8 is returned.
END$$
DELIMITER ;
*Memos:
You can initialize local variables with DEFAULT.
The initial values of the local variables without DEFAULT are NULL.
DECLARE block can be set within the top of BEGIN ... END statement within the stored programs functions, procedures, events and triggers.
Local variables are deleted after the execution of stored programs.
Local variables are not influenced by transaction so their changed values cannot be rollbacked. *My question and the answers explain it in detail.
Then, 8 is returned as shown below:
mysql> SELECT my_func();
+-----------+
| my_func() |
+-----------+
| 8 |
+-----------+
Be careful, if DECLARE block is not set within the top of BEGIN ... END statement within stored programs as shown below:
DELIMITER $$
CREATE FUNCTION my_func() RETURNS INT
DETERMINISTIC
BEGIN
SELECT 2 + 3; -- Because of this
DECLARE v1, v2 INT DEFAULT 2; -- Not the top
DECLARE v3, v4 INT DEFAULT v1 + v2; -- Not the top
DECLARE result INT; -- Not the top
SELECT v3 + v4 INTO result;
RETURN result;
END$$
DELIMITER ;
Or:
DELIMITER $$
CREATE FUNCTION my_func() RETURNS INT
DETERMINISTIC
BEGIN
DECLARE v1, v2 INT DEFAULT 2;
DECLARE v3, v4 INT DEFAULT v1 + v2;
SELECT 2 + 3; -- Because of this
DECLARE result INT; -- Not the top
SELECT v3 + v4 INTO result;
RETURN result;
END$$
DELIMITER ;
Then, there is the error below:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...
DECLARE rather than an answer to the specific question asked. There was no need to post it 11 years later. Also please consider not restyling other people's code according to your personal preference.