1.存储过程
“存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。”
2.优点&缺点
https://www.cnblogs.com/rose0705/articles/8972438.html
3.如何使用
- workbench
1.创建
2.编辑
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_2`()
BEGIN
select * from
(select A.*, D.parent as tree_parent, D.treeName as tree_Name, D.treeID as tree_ID from boxes as A
left join scenes as B on A.sceneID = B.sceneID
left join devices as C on A.deviceID=C.deviceID
left join structuretree as D on C.structureTreeID=D.treeID) as E left join (select G.treeID as tree_ID,
if (G.parent != 0 or G.parent!=null, get_tree_2(G.treeID), '0') as '设备树'
from structuretree as G
) as F on E.tree_ID = F.tree_ID;
END
3.提交
4.使用
call suchen_server_items.test_2();