mysql数据库如何定义函数_mysql数据库-定义函数-存储过程写法

DELIMITER $$

USE `iwmsdb`$$

DROP PROCEDURE IF EXISTS `P_EAP_UI_DELETEINDEXMENU`$$

CREATE DEFINER=`mysqladmin`@`%` PROCEDURE `P_EAP_UI_DELETEINDEXMENU`(IN APP_ID INT)

BEGIN

DECLARE MENU_COUNT INT;

DECLARE MENU_ID VARCHAR(36);

DECLARE PARENT_MENU_ID VARCHAR(36);

SELECT COUNT(1) INTO MENU_COUNT FROM T_CP_UI_SYSTEMAPP A , T_EAP_SYS_MENU B WHERE A.FUNCTION_ID=B.MENU_ID;

IF MENU_COUNT> 0THEN

BEGIN

SELECT FUNCTION_ID INTO MENU_ID FROM T_CP_UI_SYSTEMAPP WHERE APP_ID=APP_ID;

SELECT PARENT_MENU_ID INTO PARENT_MENU_ID FROM T_EAP_SYS_MENU WHERE MENU_ID=MENU_ID;

SELECT COUNT(1) INTO MENU_COUNT FROM T_EAP_SYS_MENU WHERE PARENT_MENU_ID=PARENT_MENU_ID;

IF MENU_COUNT=1THEN

BEGIN

DELETE FROM T_EAP_SYS_MENU WHERE MENU_ID IN (MENU_ID,PARENT_MENU_ID);

DELETE FROM T_CP_UI_FOLDER WHERE FOLDER_ID=PARENT_MENU_ID;

DELETE FROM T_CP_UI_FOLDERREFUSER WHERE FOLDER_ID=PARENT_MENU_ID;

DELETE FROM T_EAP_SYS_USER_PRIV WHERE OBJECT_ID IN (MENU_ID,PARENT_MENU_ID);

END;

ELSE

BEGIN

DELETE FROM T_EAP_SYS_MENU WHERE MENU_ID=MENU_ID;

DELETE FROM T_EAP_SYS_USER_PRIV WHERE OBJECT_ID=MENU_ID;

END;

END IF;

END;

END IF;

END$$

DELIMITER ;