MySQL5.0 存储过程

一、存储过程定义

DROP PROCEDURE IF EXISTS get_child_organiz;
DROP TABLE IF EXISTS tmplist;
DELIMITER $$
CREATE PROCEDURE get_child_organiz (IN parent_organiz_id INT)
BEGIN
DROP TABLE IF EXISTS tmplist;
CREATE TABLE tmplist(
id INT(11),
NAME VARCHAR(35),
parentid INT(11)
);
INSERT INTO tmplist SELECT organization_id,organization_name,parent_organization_id FROM T_ORGANIZATION WHERE organization_id=parent_organiz_id;
WHILE ROW_COUNT() > 0 DO
INSERT INTO tmplist SELECT a.organization_id,a.organization_name,a.parent_organization_id FROM T_ORGANIZATION a,tmplist b
WHERE a.parent_organization_id=b.id AND ((SELECT COUNT(*) FROM tmplist tmp WHERE tmp.id = a.organization_id )< 1);
END WHILE;
SELECT * FROM tmplist;
DROP TABLE IF EXISTS tmplist;
END;$$
DELIMITER;

 

 

二、存储过程在命令行中调用

CALL get_child_organiz(1);