1. Variable declaration and assignment
All input and output variables, or variables in use, are either declared with set or declare before they are used
Otherwise, it will result in an undefined variable error.
Univariate assignment
DECLARE @id int(16); set @a = '12'; set @b = (select count(*) from user);
Multivariate assignment
DECLARE @id int(16); DECLARE @name varchar(128); select id,name into @id,@name from user where id=1; select @id:=id,@name:=name from user where id=1;
2. Use of loops
Recycling in stored procedures
DELIMITER $$ DROP PROCEDURE IF EXISTS 'delete_session' $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_session`(IN top int) BEGIN DECLARE done INT DEFAULT 0; DECLARE temp_id INT; DECLARE cur CURSOR for( SELECT id from user); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; FETCH cur INTO temp_id; WHILE done <> 1 DO DELETE FROM user_session WHERE id = temp_id; FETCH cur INTO temp_id; END WHILE; CLOSE cur; END $$ DELIMITER;
Circular use in functions
-- Gets the root class name in the root class delimiter // drop function if exists 'getRootCateName' // CREATE FUNCTION getRootCateName(cate_id varchar(32)) RETURNS varchar(128) DETERMINISTIC BEGIN select pid,category into @p_id,@cate_name from system_category where id=cate_id; set @temp_id = @p_id; while @temp_id > 0 do select pid,category into @p_id,@cate_name from system_category where id=@temp_id; set @temp_id = @p_id; end while; RETURN @cate_name; end// delimiter ;
call
select getRootCateName(28);
3. Use of selection statements
delimiter // create function getScoreName(score int(10)) RETURNS varchar(128) DETERMINISTIC BEGIN if score>=90 then set @grade ='A'; elseif score<90 and score>=80 then set @grade ='B'; elseif score<80 and score>=70 then set @grade ='C'; elseif score<70 and score>=60 then set @grade ='D'; else set @grade ='E'; end if; RETURN @grade; END // delimiter ;
call
select getScoreName(91);
4. Use of branch statements
delimiter // create function getScoreName2(score int(10)) RETURNS varchar(128) DETERMINISTIC BEGIN SELECT case when score>=90 then 'nail' when score>=80 then 'B' when score>=70 then 'C' when score>=60 then 'D' else 'difference' end as grade into @commont_grade ; RETURN @commont_grade; END // delimiter ;
call
select getScoreName2(98);
5. Use of stored procedure input and output variables
DELIMITER $$ DROP PROCEDURE IF EXISTS `user_banlance` $$ CREATE PROCEDURE user_banlance (IN `in_userId` INT , IN `in_banlance` INT, OUT out_code INT, OUT out_message VARCHAR(100)) _return:BEGIN DECLARE _userId bigint(20); DECLARE _banlance int; -- balance DECLARE _verison INT DEFAULT 0; DECLARE _error INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1; -- exception handling SET out_code = -1; SET out_message = 'Failed to execute'; #User account does not exist IF NOT EXISTS (select id from user_banlance where id=in_userId) THEN SET out_code = 1; SET out_message = 'user does not exist'; LEAVE _return; END IF; select verison,banlance into _verison, _banlance from user_banlance where id=in_userId; START TRANSACTION; -- Use of Open Transaction Optimistic Lock update user_banlance set banlance = banlance - in_banlance,verison = verison + 1 where id = in_userId and verison = _verison; SET @ret_update = ROW_COUNT(); IF @ret_update = 0 THEN ROLLBACK; SET out_code = -4; SET out_message = 'System Error'; LEAVE _return; END IF; IF _error <> 0 THEN ROLLBACK; SET out_code = -3; SET out_message = 'System Error'; LEAVE _return; ELSE COMMIT; SET out_code = 1; SET out_message = ''; END IF; END $$ DELIMITER ;
--Call to note that the output parameter must be a declared variable or an error will be reported
set @b = 0; set @c = ''; call user_banlance(1,10,@b,@c); select @b,@c
6. Use of stored procedures (query cursors)
DELIMITER $$ DROP PROCEDURE IF EXISTS `user_banlance` $$ CREATE PROCEDURE user_banlance (IN `in_userId` INT , IN `in_banlance` INT) BEGIN #Traversal data end flag DECLARE done INT DEFAULT FALSE; DECLARE _id INT; DECLARE _banlance BIGINT(20); #cursor DECLARE cur CURSOR FOR SELECT id,banlance FROM user_banlance where id > in_userId and banlance > in_banlance ; #Bind end flag to cursor DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- Building tables tb_temp DROP TABLE IF EXISTS tb_temp; CREATE TEMPORARY TABLE tb_temp( `id` int UNSIGNED NOT NULL , `banlance` bigint(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MYISAM DEFAULT charset = utf8 ; -- open OPEN cur; -- Start cycle read_loop: LOOP -- Extract data from cursor FETCH cur INTO _id,_banlance; IF done THEN LEAVE read_loop; ELSE replace into tb_temp( id ,banlance) values(_id,_banlance); -- Replace identical records with unique index and primary key index END IF; END LOOP; -- Close Cursor CLOSE cur; select * from tb_temp; DROP TABLE IF EXISTS tb_temp; END $$ DELIMITER ;
call
call user_banlance(2,1500);
Another way to implement cursor loops
-- open OPEN cur; -- Start cycle REPEAT -- Extract data from cursor FETCH cur INTO _id,_banlance; IF not done THEN insert into tb_temp( id ,banlance) values(_id,_banlance); -- Replace identical records with unique index and primary key index END IF; UNTIL done END REPEAT; -- Close Cursor CLOSE cur;
[Note]:
[1] fetch must be called before judgment to avoid duplication of the last record (fetch failed the last time it was traversed through a loop, and if it continues processing at that time, you will insert the previous value.)
[2] Statements such as drop and create must be used after the declare declaration statement or errors will occur
6. Differences between stored procedures and functions
Call the stored procedure call procedure_name(arges, arges1...);
Function select function_name(arges);
Return stored procedures can return multiple values.
Functions can only have one return value