server1/SQL/CreateProcedures.sqlbook

-- SQLBook: Code
-- Active: 1756343447531@@192.1.2.49@32124@eqm
-- Active: 1756343447531@@192.1.2.49@32124@eqm_ceshi
#写入报警数据库的存储过程

-- 达梦数据库
CREATE OR REPLACE PROCEDURE ACAS_InsertTwoAlarmInfo(
    itemID1 IN INT, itemName1 IN VARCHAR(100), alarmType1 IN INT,
    soundCardName1 IN VARCHAR(100), soundCardPCMName1 IN VARCHAR(100),
    compareRoadNum1 IN INT, compareRoadName1 IN VARCHAR(100), compareRoadType1 IN INT,
    alarmStartTime1 IN DATETIME, alarmEndTime1 IN DATETIME, alarmDuration1 IN INT,
    alarmFilePath1 IN VARCHAR(255), fileAlarmStartPos1 IN INT, fileState1 IN INT,
    itemID2 IN INT, itemName2 IN VARCHAR(100), alarmType2 IN INT,
    soundCardName2 IN VARCHAR(100), soundCardPCMName2 IN VARCHAR(100),
    compareRoadNum2 IN INT, compareRoadName2 IN VARCHAR(100), compareRoadType2 IN INT,
    alarmStartTime2 IN DATETIME, alarmEndTime2 IN DATETIME, alarmDuration2 IN INT,
    alarmFilePath2 IN VARCHAR(255), fileAlarmStartPos2 IN INT, fileState2 IN INT
)
AS
    pkid INT;
BEGIN
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardName, SoundCardPCMName, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, FileState
    ) VALUES (
        itemID1, itemName1, alarmType1, soundCardName1, soundCardPCMName1, compareRoadNum1, compareRoadName1, compareRoadType1,
        alarmStartTime1, alarmEndTime1, alarmDuration1, alarmFilePath1, fileAlarmStartPos1, fileState1
    );
    SELECT MAX(PKID) INTO pkid FROM tACAAlarmInfo;
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardName, SoundCardPCMName, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, MainRoadPKID, FileState
    ) VALUES (
        itemID2, itemName2, alarmType2, soundCardName2, soundCardPCMName2, compareRoadNum2, compareRoadName2, compareRoadType2,
        alarmStartTime2, alarmEndTime2, alarmDuration2, alarmFilePath2, fileAlarmStartPos2, pkid, fileState2
    );
END;

# 调用存储过程
CALL ACAS_InsertTwoAlarmInfo(
    @itemID1, @itemName1, @alarmType1,
    @soundCardName1, @soundCardPCMName1,
    @compareRoadNum1, @compareRoadName1, @compareRoadType1,
    @alarmStartTime1, @alarmEndTime1, @alarmDuration1,
    @alarmFilePath1, @fileAlarmStartPos1, @fileState1,
    @itemID2, @itemName2, @alarmType2,
    @soundCardName2, @soundCardPCMName2,
    @compareRoadNum2, @compareRoadName2, @compareRoadType2,
    @alarmStartTime2, @alarmEndTime2, @alarmDuration2,
    @alarmFilePath2, @fileAlarmStartPos2, @fileState2
);

#GBase版本的存储过程
CREATE PROCEDURE ACAS_InsertTwoAlarmInfo(
    itemID1 INT, itemName1 VARCHAR(100), alarmType1 INT,
    soundCardName1 VARCHAR(100), soundCardPCMName1 VARCHAR(100),
    compareRoadNum1 INT, compareRoadName1 VARCHAR(100), compareRoadType1 INT,
    alarmStartTime1 VARCHAR(32), alarmEndTime1 VARCHAR(32), alarmDuration1 INT,
    alarmFilePath1 VARCHAR(255), fileAlarmStartPos1 INT, fileState1 INT,
    itemID2 INT, itemName2 VARCHAR(100), alarmType2 INT,
    soundCardName2 VARCHAR(100), soundCardPCMName2 VARCHAR(100),
    compareRoadNum2 INT, compareRoadName2 VARCHAR(100), compareRoadType2 INT,
    alarmStartTime2 VARCHAR(32), alarmEndTime2 VARCHAR(32), alarmDuration2 INT,
    alarmFilePath2 VARCHAR(255), fileAlarmStartPos2 INT, fileState2 INT
)
DEFINE pkid INT;
BEGIN
    
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardName, SoundCardPCMName, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, FileState
    ) VALUES (
        itemID1, itemName1, alarmType1, soundCardName1, soundCardPCMName1, compareRoadNum1, compareRoadName1, compareRoadType1,
        alarmStartTime1, alarmEndTime1, alarmDuration1, alarmFilePath1, fileAlarmStartPos1, fileState1
    );
    SELECT MAX(PKID) INTO pkid FROM tACAAlarmInfo;
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardName, SoundCardPCMName, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, MainRoadPKID, FileState
    ) VALUES (
        itemID2, itemName2, alarmType2, soundCardName2, soundCardPCMName2, compareRoadNum2, compareRoadName2, compareRoadType2,
        alarmStartTime2, alarmEndTime2, alarmDuration2, alarmFilePath2, fileAlarmStartPos2, pkid, fileState2
    );
END
END PROCEDURE


#MySQL版
DELIMITER $$
CREATE PROCEDURE ACAS_InsertTwoAlarmInfo(
    IN itemID1 INT, IN itemName1 VARCHAR(100), IN alarmType1 INT,
    IN soundCardName1 VARCHAR(100), IN soundCardPCMName1 VARCHAR(100),
    IN compareRoadNum1 INT, IN compareRoadName1 VARCHAR(100), IN compareRoadType1 INT,
    IN alarmStartTime1 VARCHAR(32), IN alarmEndTime1 VARCHAR(32), IN alarmDuration1 INT,
    IN alarmFilePath1 VARCHAR(255), IN fileAlarmStartPos1 INT, IN fileState1 INT,
    IN itemID2 INT, IN itemName2 VARCHAR(100), IN alarmType2 INT,
    IN soundCardName2 VARCHAR(100), IN soundCardPCMName2 VARCHAR(100),
    IN compareRoadNum2 INT, IN compareRoadName2 VARCHAR(100), IN compareRoadType2 INT,
    IN alarmStartTime2 VARCHAR(32), IN alarmEndTime2 VARCHAR(32), IN alarmDuration2 INT,
    IN alarmFilePath2 VARCHAR(255), IN fileAlarmStartPos2 INT, IN fileState2 INT
)
BEGIN
    DECLARE pkid INT;

    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardName, SoundCardPCMName, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, FileState
    ) VALUES (
        itemID1, itemName1, alarmType1, soundCardName1, soundCardPCMName1, compareRoadNum1, compareRoadName1, compareRoadType1,
        alarmStartTime1, alarmEndTime1, alarmDuration1, alarmFilePath1, fileAlarmStartPos1, fileState1
    );

    SET pkid = LAST_INSERT_ID();

    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardName, SoundCardPCMName, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, MainRoadPKID, FileState
    ) VALUES (
        itemID2, itemName2, alarmType2, soundCardName2, soundCardPCMName2, compareRoadNum2, compareRoadName2, compareRoadType2,
        alarmStartTime2, alarmEndTime2, alarmDuration2, alarmFilePath2, fileAlarmStartPos2, pkid, fileState2
    );
END $$
DELIMITER ;