Mysql stored procedure demo

I have never written a mysql stored procedure. I have written one based on Baidu and previous experience in writing oracle stored procedures, which is quite smooth. Let's give an example.

CREATE DEFINER= `west _ brain` @ `% `PROCEDURE `brain` () BEGIN-Storage tree result processing sql variable DECLARE var_code VARCHAR (1000); DECLARE var_pcode VARCHAR (1000); DECLARE var_name VARCHAR (1000); DECLARE var_count INT;-some tag variables stored DECLARE buf_parents VARCHAR (1000) DEFAULT'' DECLARE buf_names VARCHAR (1000) DEFAULT''; DECLARE buf_code VARCHAR (100) DEFAULT''; DECLARE buf_update INT DEFAULT FALSE; DECLARE buf_is_leaf INT DEFAULT 0;-- Tree-level root is 1 DECLARE buf_tree_level int DEFAULT 0;-- whether leaf node 0 is DECLARE buf_tree_leaf int DEFAULT 1 other than 1 -- variable DECLARE vcode VARCHAR (64) for storing table loop cursors; DECLARE vparent VARCHAR (1000);-- processing variable DECLARE done INT DEFAULT FALSE; for end of cursor-- defines table loop cursor DECLARE mycursor CURSOR FOR (SELECT CODE, parent FROM adm_sys_area_info) -- define cursor overflow processing DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- Open cursor OPEN mycursor;-- define cursor loop tableloop: LOOP-- read a piece of data from a cursor into variables FETCH mycursor INTO vcode, vparent -- if the previous cursor operation does not read the record, the done will be set to TRUE and exit the loop IF done THEN LEAVE tableloop; END IF; named myloop-- record the area code of the current record SET buf_code = vcode -- determine whether you are a leaf node SELECT count (*) into var_count from adm_sys_area_info where parent = vcode; if var_count = 0 then update adm_sys_area_info set tree_leaf = 1 where code = vcode; else update adm_sys_area_info set tree_leaf = 0 where code = vcode; end if; set var_count = 0 -- Loop to find your parent node treeloop: LOOP-- determine whether you have a parent node, and use count to determine SELECT count (*) INTO var_count FROM adm_sys_area_info WHERE CODE = vparent IF var_count = 0 THEN-there is no parent node Then start processing the previously found parent node IF buf_update THEN-- update cached data-buf_update for TRUE means that the parent node has been found-the following two records deal with the problem of multiple commas at the end of the concatenated string IF (length (buf_parents) > 0) THEN SET buf_parents = LEFT (buf_parents CHAR_LENGTH (buf_parents)-1) END IF; IF (length (buf_names) > 0) THEN SET buf_names = LEFT (buf_names, CHAR_LENGTH (buf_names)-1); END IF -- update the parent information of the current node UPDATE adm_sys_area_info SET parents = buf_parents, tree_names = buf_names, tree_level = buf_tree_level WHERE CODE = buf_code ELSE-- the current record is the root node update adm_sys_area_info set tree_level = 1 where code = buf_code; END IF;-- cleanup variable SET buf_parents ='; SET buf_names =''; SET vparent ='' SET buf_code =''; SET buf_update = FALSE; SET buf_tree_level = 1;-- ends the processing cycle of the current record-- finds the parent node SET buf_update = TRUE -- query the parent node information of the current node SELECT CODE, parent, area_name INTO var_code, var_pcode Var_name FROM adm_sys_area_info WHERE CODE = vparent -- connection string SET buf_parents = CONCAT_WS (',', var_code, buf_parents); SET buf_names = CONCAT_WS (',', var_name, buf_names);-- record the parent node that currently found the record code SET vparent = var_pcode SET buf_tree_level = buf_tree_level + 1; END IF; END LOOP; END LOOP; CLOSE mycursor;END