In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
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
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.