server1/SQL/CreateProcedures.sqlbook

-- SQLBook: Code
#写入报警数据库的存储过程

-- 达梦数据库
CREATE OR REPLACE PROCEDURE ACAS_InsertTwoAlarmInfo(
    itemID1 IN INT, itemName1 IN VARCHAR(100), alarmType1 IN INT,
    soundCardNum1 IN INT, soundCardName1 IN VARCHAR(100), soundCardRoadNum1 IN INT,
    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,
    itemID2 IN INT, itemName2 IN VARCHAR(100), alarmType2 IN INT,
    soundCardNum2 IN INT, soundCardName2 IN VARCHAR(100), soundCardRoadNum2 IN INT,
    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
)
AS
    pkid INT;
BEGIN
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos
    ) VALUES (
        itemID1, itemName1, alarmType1, soundCardNum1, soundCardName1, soundCardRoadNum1, compareRoadNum1, compareRoadName1, compareRoadType1,
        alarmStartTime1, alarmEndTime1, alarmDuration1, alarmFilePath1, fileAlarmStartPos1
    );
    SELECT MAX(PKID) INTO pkid FROM tACAAlarmInfo;
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, MainRoadPKID
    ) VALUES (
        itemID2, itemName2, alarmType2, soundCardNum2, soundCardName2, soundCardRoadNum2, compareRoadNum2, compareRoadName2, compareRoadType2,
        alarmStartTime2, alarmEndTime2, alarmDuration2, alarmFilePath2, fileAlarmStartPos2, pkid
    );
END;

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

#GBase版本的存储过程
CREATE PROCEDURE ACAS_InsertTwoAlarmInfo(
    IN itemID1 INT, IN itemName1 VARCHAR(100), IN alarmType1 INT,
    IN soundCardNum1 INT, IN soundCardName1 VARCHAR(100), IN soundCardRoadNum1 INT,
    IN compareRoadNum1 INT, IN compareRoadName1 VARCHAR(100), IN compareRoadType1 INT,
    IN alarmStartTime1 DATETIME, IN alarmEndTime1 DATETIME, IN alarmDuration1 INT,
    IN alarmFilePath1 VARCHAR(255), IN fileAlarmStartPos1 INT,
    IN itemID2 INT, IN itemName2 VARCHAR(100), IN alarmType2 INT,
    IN soundCardNum2 INT, IN soundCardName2 VARCHAR(100), IN soundCardRoadNum2 INT,
    IN compareRoadNum2 INT, IN compareRoadName2 VARCHAR(100), IN compareRoadType2 INT,
    IN alarmStartTime2 DATETIME, IN alarmEndTime2 DATETIME, IN alarmDuration2 INT,
    IN alarmFilePath2 VARCHAR(255), IN fileAlarmStartPos2 INT
)
DEFINE pkid INT;
BEGIN
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos
    ) VALUES (
        itemID1, itemName1, alarmType1, soundCardNum1, soundCardName1, soundCardRoadNum1, compareRoadNum1, compareRoadName1, compareRoadType1,
        alarmStartTime1, alarmEndTime1, alarmDuration1, alarmFilePath1, fileAlarmStartPos1
    );
    LET pkid = dbinfo('sqlca.sqlerrd1');
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, MainRoadPKID
    ) VALUES (
        itemID2, itemName2, alarmType2, soundCardNum2, soundCardName2, soundCardRoadNum2, compareRoadNum2, compareRoadName2, compareRoadType2,
        alarmStartTime2, alarmEndTime2, alarmDuration2, alarmFilePath2, fileAlarmStartPos2, pkid
    );
END PROCEDURE;