<>1. Define user variables ( Or assignment )
set @ Variable name = value
-- Creating custom user variables , It disappears when the current user disconnects set @value=1;
<>2. Query user variable value
select @ variable
-- Query defined data select @value;
<>3. Use variables in the current stored procedures and functions
* Defining variables ：
declare Variable name [, Variable name 2...] Variable type [default Default value ]
* Assignment variable ：
set Variable name 1= Variable value 1( Or expressions )[ , Variable name 2= Variable value 2( Or expressions )]
* Using variables ：
select Listing [, Listing ...] into Variable name 1[, Variable name two ...]
<>4. Creating stored procedures for updating data
Create table mytest
-- Create updated stored procedures DELIMITER // CREATE PROCEDURE updateMyTest(uid INT,newMoney DOUBLE
) BEGIN DECLARE isexists INT DEFAULT 0; -- The definition variable must be in the current begin in SELECT COUNT(1) INTO
isexistsFROM mytest WHERE id=uid ; -- Using the (into) Method assignment IF isexists<>0 THEN --
The first condition IF newMoney>0 THEN -- The second condition UPDATE mytest SET money=newMoney WHERE id=uid;
SET isexists=1; -- use set Method to assign a value to a variable ELSE SET isexists=-1; END IF; -- Close the second condition END IF;
-- Close the first condition SELECT (CASE isexists WHEN -1 THEN ' Execution failed ' WHEN 0 THEN 'id non-existent ' ELSE
' Successful implementation ' END)AS ' results of enforcement ' ;-- use case when then else end Of switch condition END // DELIMITER ;
test ：CALL updateMyTest(1,10000);
<>5. Create a function to input grades and return results
-- Get the result of the score according to the score entered DELIMITER // CREATE FUNCTION getScoreResult(score INT)
RETURNS CHAR(2) BEGIN DECLARE result CHAR(2) DEFAULT ''; IF score>90 THEN SET
result=' excellent '; ELSEIF score>80 THEN SET result=' good '; ELSEIF score>70 THEN SET result
=' commonly '; ELSE SET result=' difference '; END IF; -- end if condition RETURN result; END // DELIMITER ;
test ：SELECT getScoreResult(50);
<>6. summary
1. Define user variable usage set @ variable , The variable disappears as the user is disconnected
2. All assignments are made using set Variable name = value ; Make sure you add a semicolon
3. use case variable when value then … else end , among else amount to switch In default , It must be added end end case grammar
4. use if-elseif-else perhaps if-else You need to add it at the end end if Used to end if The grammar of
The above is purely personal opinion , If you have any questions, please contact me !
Technology