MySQL数据库中编写创建权限（业务权限，非数据库权限）的存储过程

MySQL数据库中编写创建权限（业务权限，非数据库权限）的存储过程

注：是业务权限而非数据库权限，代码中的数据库表都是创建的业务表。

创建后台权限的存储过程：

DROP PROCEDURE bgCreateAuth;
DELIMITER $
CREATE PROCEDURE bgCreateAuth(IN objkeya VARCHAR(100), IN actkeya VARCHAR(100))
labelpro:
BEGIN
DECLARE objidv INT UNSIGNED;
DECLARE objnamev VARCHAR(50);
DECLARE actidv INT UNSIGNED;
DECLARE actnamev VARCHAR(50);
DECLARE authkeyv VARCHAR(100);
DECLARE authremarkv VARCHAR(100);
DECLARE authidv INT UNSIGNED DEFAULT NULL;
DECLARE message VARCHAR(100);
SELECT AOBJID, NAME INTO objidv, objnamev FROM AUTHOBJECT WHERE OBJKEY=objkeya;
IF(objidv IS NULL) THEN
    SET message = CONCAT("Failed! NO the authority object: ", objkeya);
    SELECT message;
    LEAVE labelpro;
ELSEIF (actkeya IS NOT NULL) THEN
    SELECT AACTID, NAME INTO actidv, actnamev FROM AUTHACTION WHERE ACTKEY=actkeya;
    IF(actidv IS NULL) THEN
        SET message = CONCAT("Failed! NO the authority action: ", actkeya);
        SELECT message;
        LEAVE labelpro;
    END IF; 
    SET authkeyv = CONCAT(objkeya, '-', actkeya);
    SET authremarkv = CONCAT(objnamev, '-', actnamev);
ELSE
    SET actidv = 0;
    SET authkeyv = objkeya;
    SET authremarkv = objnamev;
END IF;
SELECT AUTHID INTO authidv FROM AUTHORITY WHERE AUTHKEY=authkeyv;
IF(authidv IS NOT NULL) THEN
    SET message = CONCAT("This authority already exists: ", authkeyv);
    SELECT message;
    LEAVE labelpro;
END IF;
INSERT INTO AUTHORITY (AUTHKEY, AOBJID, AACTID, REMARK) VALUES (authkeyv, objidv, actidv, authremarkv);
END$
DELIMITER ;

-------------------------------------------------------------------------------

创建前台权限的存储过程：

DROP PROCEDURE fgCreateAuth;
DELIMITER $
CREATE PROCEDURE fgCreateAuth(IN objkeya VARCHAR(100), IN actkeya VARCHAR(100))
labelpro:
BEGIN
DECLARE objidv INT UNSIGNED;
DECLARE objnamev VARCHAR(50);
DECLARE actidv INT UNSIGNED;
DECLARE actnamev VARCHAR(50);
DECLARE authkeyv VARCHAR(100);
DECLARE authremarkv VARCHAR(100);
DECLARE authidv INT UNSIGNED DEFAULT NULL;
DECLARE message VARCHAR(100);
SELECT AOBJID, NAME INTO objidv, objnamev FROM AUTHOBJECTF WHERE OBJKEY=objkeya;
IF(objidv IS NULL) THEN
    SET message = CONCAT("Failed! NO the authority object: ", objkeya);
    SELECT message;
    LEAVE labelpro;
ELSEIF (actkeya IS NOT NULL) THEN
    SELECT AACTID, NAME INTO actidv, actnamev FROM AUTHACTIONF WHERE ACTKEY=actkeya;
    IF(actidv IS NULL) THEN
        SET message = CONCAT("Failed! NO the authority action: ", actkeya);
        SELECT message;
        LEAVE labelpro;
    END IF; 
    SET authkeyv = CONCAT(objkeya, '-', actkeya);
    SET authremarkv = CONCAT(objnamev, '-', actnamev);
ELSE
    SET actidv = 0;
    SET authkeyv = objkeya;
    SET authremarkv = objnamev;
END IF;
SELECT AUTHID INTO authidv FROM AUTHORITYF WHERE AUTHKEY=authkeyv;
IF(authidv IS NOT NULL) THEN
    SET message = CONCAT("This authority already exists: ", authkeyv);
    SELECT message;
    LEAVE labelpro;
END IF;
INSERT INTO AUTHORITYF (AUTHKEY, AOBJID, AACTID, REMARK) VALUES (authkeyv, objidv, actidv, authremarkv);
END$
DELIMITER ;