如下,我检查了几遍应该没错啊。总是提示行12有错。
CREATE PROCEDURE `P_INSERT_MENU`()
BEGIN
DECLARE pAUTOIDA INT;
DECLARE pAUTOIDB INT;
DECLARE pUSERID INT;
DECLARE pLAST INT;
SELECT ModeFID INTO pAUTOIDA FROM t_info_systemmode
WHERE ModeCode='WebPage_Shipment_Ship_Cost_Edit_btnAcc'
AND ModeType='Shipment';
IF pAUTOIDA IS NULL then
INSERT INTO t_info_systemmode(ModeCode,ModeName,ModeType)
VALUES('WebPage_Shipment_Ship_Cost_Edit_btnAcc',
'Export Doc Approve','Shipment');
SELECT ModeFID INTO pAUTOIDA FROM t_info_systemmode
WHERE ModeCode='WebPage_Shipment_Ship_Cost_Edit_btnAcc'
AND ModeType='Shipment';
END IF;
DECLARE CURUSER CURSOR
FOR SELECT UserFID FROM t_system_user;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET pLAST=1;
OPEN CURUSER;
REPEAT
FETCH CURUSER INTO pUSERID;
SELECT SystemUserDtl_FID INTO pAUTOIDB FROM t_system_user_dtl WHERE SystemUserID=pUSERID AND SystemModeID=pAUTOIDA;
IF pAUTOIDB IS NULL THEN
INSERT INTO t_system_user_dtl(SystemUserID,SystemModeID,Can_Add,Can_View,Can_Edit,Can_Del)
VALUES(pUSERID,pAUTOIDB,0,0,0,0);
END IF;
UNTIL pLAST=1
END REPEAT;
CLOSE CURUSER;
END感觉MYSQL超不好用,以前用MSSQL熟得要命在前面加一句DROP PROCEDURE IF EXISTS xxx 也会错。无言!
还有在MYSQL QUERY BROWSER如何像MSSQL里选定来执行多行语句。
CREATE PROCEDURE `P_INSERT_MENU`()
BEGIN
DECLARE pAUTOIDA INT;
DECLARE pAUTOIDB INT;
DECLARE pUSERID INT;
DECLARE pLAST INT;
SELECT ModeFID INTO pAUTOIDA FROM t_info_systemmode
WHERE ModeCode='WebPage_Shipment_Ship_Cost_Edit_btnAcc'
AND ModeType='Shipment';
IF pAUTOIDA IS NULL then
INSERT INTO t_info_systemmode(ModeCode,ModeName,ModeType)
VALUES('WebPage_Shipment_Ship_Cost_Edit_btnAcc',
'Export Doc Approve','Shipment');
SELECT ModeFID INTO pAUTOIDA FROM t_info_systemmode
WHERE ModeCode='WebPage_Shipment_Ship_Cost_Edit_btnAcc'
AND ModeType='Shipment';
END IF;
DECLARE CURUSER CURSOR
FOR SELECT UserFID FROM t_system_user;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET pLAST=1;
OPEN CURUSER;
REPEAT
FETCH CURUSER INTO pUSERID;
SELECT SystemUserDtl_FID INTO pAUTOIDB FROM t_system_user_dtl WHERE SystemUserID=pUSERID AND SystemModeID=pAUTOIDA;
IF pAUTOIDB IS NULL THEN
INSERT INTO t_system_user_dtl(SystemUserID,SystemModeID,Can_Add,Can_View,Can_Edit,Can_Del)
VALUES(pUSERID,pAUTOIDB,0,0,0,0);
END IF;
UNTIL pLAST=1
END REPEAT;
CLOSE CURUSER;
END感觉MYSQL超不好用,以前用MSSQL熟得要命在前面加一句DROP PROCEDURE IF EXISTS xxx 也会错。无言!
还有在MYSQL QUERY BROWSER如何像MSSQL里选定来执行多行语句。
DECLARE pAUTOIDA INT;
DECLARE pAUTOIDB INT;
DECLARE pUSERID INT;
DECLARE pLAST INT;
DECLARE CURUSER CURSOR
DECLARE pAUTOIDB INT;
DECLARE pUSERID INT;
DECLARE pLAST INT;
DECLARE CURUSER CURSOR FOR SELECT UserFID FROM t_system_user;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET pLAST=1;
OPEN CURUSER; SELECT ModeFID INTO pAUTOIDA FROM t_info_systemmode
WHERE ModeCode='WebPage_Shipment_Ship_Cost_Edit_btnAcc'
AND ModeType='Shipment';
IF pAUTOIDA IS NULL then
INSERT INTO t_info_systemmode(ModeCode,ModeName,ModeType)
VALUES('WebPage_Shipment_Ship_Cost_Edit_btnAcc',
'Export Doc Approve','Shipment');
SELECT ModeFID INTO pAUTOIDA FROM t_info_systemmode
WHERE ModeCode='WebPage_Shipment_Ship_Cost_Edit_btnAcc'
AND ModeType='Shipment';
END IF;
REPEAT
FETCH CURUSER INTO pUSERID;
SELECT SystemUserDtl_FID INTO pAUTOIDB FROM t_system_user_dtl WHERE SystemUserID=pUSERID AND SystemModeID=pAUTOIDA;
IF pAUTOIDB IS NULL THEN
INSERT INTO t_system_user_dtl(SystemUserID,SystemModeID,Can_Add,Can_View,Can_Edit,Can_Del)
VALUES(pUSERID,pAUTOIDB,0,0,0,0);
END IF;
UNTIL pLAST=1
END REPEAT;
CLOSE CURUSER;
END$$