-- Active: 1752718919967@@192.1.2.61@5236@EQM_CESHI SELECT * FROM tServiceSetInfo; #获取所有的对比项信息 SELECT * FROM tACACompareItems LEFT JOIN tACACompareItemRoad ON tACACompareItems.ItemID = tACACompareItemRoad.ItemID; #只获取对比项信息 SELECT * FROM tACACompareItems; #获取对比项的通道信息 SELECT * FROM tACACompareItemRoad; #插入一条对比项信息 INSERT INTO tACACompareItems ( ItemID, ItemName, ItemEnable, RoadCount, SilentEnable, SilentThreshold, SilentDuration, SilentSensitivity, OverloadEnable, OverloadThreshold, OverloadDuration, OverloadSensitivity, PhaseEnable, PhaseThreshold, PhaseDuration, PhaseSensitivity ) VALUES ( @itemID, @itemName, @itemEnable, @roadCount, @silentEnable, @silentThreshold, @silentDuration, @silentSensitivity, @overloadEnable, @overloadThreshold, @overloadDuration, @overloadSensitivity, @phaseEnable, @phaseThreshold, @phaseDuration, @phaseSensitivity ); #插入一条对比项通道信息 INSERT INTO tACACompareItemRoad ( ItemID, RoadNum, RoadName, RoadRecordEnable, SoundCardName, SoundCardPCMName) VALUES ( @itemID, @roadNum, @roadName, @roadRecordEnable, @soundCardName, @soundCardPCMName); #通过ID更新对比项信息 UPDATE tACACompareItems SET ItemName = @itemName, ItemEnable = @itemEnable, RoadCount = @roadCount, SilentEnable = @silentEnable, SilentThreshold = @silentThreshold, SilentDuration = @silentDuration, SilentSensitivity = @silentSensitivity, OverloadEnable = @overloadEnable, OverloadThreshold = @overloadThreshold, OverloadDuration = @overloadDuration, OverloadSensitivity = @overloadSensitivity, PhaseEnable = @phaseEnable, PhaseThreshold = @phaseThreshold, PhaseDuration = @phaseDuration, PhaseSensitivity = @phaseSensitivity WHERE ItemID = @itemID; #通过ItemID和RoadNum更新对比项通道信息 UPDATE tACACompareItemRoad SET RoadName = @roadName, RoadRecordEnable = @roadRecordEnable, SoundCardNum = @soundCardNum, SoundCardID = @soundCardID, SoundCardName = @soundCardName, SoundCardRoadNum = @soundCardRoadNum, ChannelID = @channelID, ChannelName = @channelName WHERE ItemID = @ItemID AND RoadNum = @roadNim; #删除一个对比项信息 DELETE FROM tACACompareItems WHERE ItemID = @itemID; #删除一个对比项的所有通道信息 DELETE FROM tACACompareItemRoad WHERE ItemID = @itemID; #删除一个对比项单个通道信息 DELETE FROM tACACompareItemRoad WHERE ItemID = @itemID AND RoadNum = @roadNum; CREATE OR REPLACE PROCEDURE update_compare_item_and_road( v_ItemID INT, v_ItemName VARCHAR, v_ItemEnable BIT, v_RoadCount INT, v_RoadNum INT, v_RoadName VARCHAR ) AS BEGIN -- 修改主表 UPDATE tACACompareItems SET ItemName = v_ItemName, ItemEnable = v_ItemEnable, RoadCount = v_RoadCount WHERE ItemID = v_ItemID; -- 修改子表，新增、修改或删除 FOR rec IN (SELECT * FROM tACACompareItemRoad WHERE ItemID = v_ItemID) LOOP IF rec.RoadNum = v_RoadNum THEN -- 更新已存在的通道信息 UPDATE tACACompareItemRoad SET RoadName = v_RoadName WHERE ItemID = v_ItemID AND RoadNum = v_RoadNum; RETURN; END IF; END LOOP; -- 如果没有找到对应的通道，则插入新的通道信息 INSERT INTO tACACompareItemRoad (ItemID, RoadNum, RoadName) VALUES (v_ItemID, v_RoadNum, v_RoadName); -- 如果需要删除其他通道，可以在这里添加逻辑 END; CALL update_compare_item_and_road(1, '新名称', 1, 2, 1, '主通道'); #查看tACASystemConfig表的所有数据 SELECT * FROM tACASystemConfig; #插入一个数据到 tACASystemConfig 表 INSERT INTO tACASystemConfig (ConfigKey, ConfigValue, ConfigDesc) VALUES ('ExampleKey', 'ExampleValue', 'This is an example configuration'); #更新一条数据，没有则插入数据 MERGE INTO tACASystemConfig t USING (SELECT 1 FROM dual) s ON (t.ConfigKey = @configKey) WHEN MATCHED THEN UPDATE SET ConfigValue = @configValue, ConfigDesc = @configDesc, UpdateTime = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (ConfigKey, ConfigValue, ConfigDesc) VALUES (@configKey, @configValue, @configDesc); #GBase版本 MERGE INTO tACASystemConfig AS t USING (SELECT @configKey AS ConfigKey, CAST(@configValue AS TEXT) AS ConfigValue, @configDesc AS ConfigDesc) AS s ON (t.ConfigKey = s.ConfigKey) WHEN MATCHED THEN UPDATE SET t.ConfigValue = s.ConfigValue, t.ConfigDesc = s.ConfigDesc, t.UpdateTime = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (ConfigKey, ConfigValue, ConfigDesc) VALUES (s.ConfigKey, s.ConfigValue, s.ConfigDesc); -- 先尝试更新 UPDATE tACASystemConfig SET ConfigValue = @configValue, ConfigDesc = @configDesc, UpdateTime = CURRENT_TIMESTAMP WHERE ConfigKey = @configKey; -- 如果没有更新到行，则插入 IF DB_ROWCOUNT() = 0 THEN INSERT INTO tACASystemConfig (ConfigKey, ConfigValue, ConfigDesc) VALUES (@configKey, @configValue, @configDesc); END IF; #获取系统设置信息 SELECT * FROM tACASystemConfig; #删除一条系统设置信息 DELETE FROM tACASystemConfig WHERE PKID = 1; #获取检测时段 SELECT * FROM tACADetectPeriod; #插入检测时段 INSERT INTO tACADetectPeriod (ItemID, IsDetect, WeekType, CDate, TimeStart, TimeEnd, ApplySlient, ApplyOverload, ApplyPhase, ApplyNoise) VALUES (@itemID, @isDetect, @weekType, @cDate, @timeStart, @timeEnd, @applySlient, @applyOverload, @applyPhase, @applyNoise); #删除检测时段 DELETE FROM tACADetectPeriod WHERE ItemID = @itemID; UPDATE tACASystemConfig (ConfigKey, ConfigValue, ConfigDesc) VALUES ('ACAS_SoundCardInfo', '[{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Discard all samples (playback) or generate zero samples (capture)","PCMName":"null","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Playback/recording through the PulseAudio sound server","PCMName":"default","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Default Audio Device","PCMName":"sysdefault","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"IEC958 (S/PDIF) Digital Audio Output","PCMName":"iec958","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Libav/FFmpeg Library","PCMName":"lavrate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Samplerate Library","PCMName":"samplerate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Speex Resampler","PCMName":"speexrate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"JACK Audio Connection Kit","PCMName":"jack","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Open Sound System","PCMName":"oss","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"PulseAudio Sound Server","PCMName":"pulse","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Plugin for channel upmix (4,6,8)","PCMName":"upmix","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Plugin for channel downmix (stereo) with a simple spacialization","PCMName":"vdownmix","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect hardware device without any conversions","PCMName":"hw:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nDirect hardware device without any conversions","PCMName":"hw:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nHardware device with all software conversions","PCMName":"plughw:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nHardware device with all software conversions","PCMName":"plughw:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDefault Audio Device","PCMName":"sysdefault:CARD=AudioPCI","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nFront output / input","PCMName":"front:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nRear speakers","PCMName":"rear:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\n4.0 Surround output to Front and Rear speakers","PCMName":"surround40:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nIEC958 (S/PDIF) Digital Audio Output","PCMName":"iec958:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect sample mixing device","PCMName":"dmix:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nDirect sample mixing device","PCMName":"dmix:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Input","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect sample snooping device","PCMName":"dsnoop:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI\nUSB Stream Output","PCMName":"usbstream:CARD=AudioPCI","SubDevNum":"未知设备编号"}]', '系统的声卡信息'); UPDATE tACASystemConfig SET ConfigValue = '[{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Discard all samples (playback) or generate zero samples (capture)","PCMName":"null","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Playback/recording through the PulseAudio sound server","PCMName":"default","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Default Audio Device","PCMName":"sysdefault","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"IEC958 (S/PDIF) Digital Audio Output","PCMName":"iec958","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Libav/FFmpeg Library","PCMName":"lavrate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Samplerate Library","PCMName":"samplerate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Speex Resampler","PCMName":"speexrate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"JACK Audio Connection Kit","PCMName":"jack","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Open Sound System","PCMName":"oss","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"PulseAudio Sound Server","PCMName":"pulse","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Plugin for channel upmix (4,6,8)","PCMName":"upmix","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Plugin for channel downmix (stereo) with a simple spacialization","PCMName":"vdownmix","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect hardware device without any conversions","PCMName":"hw:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nDirect hardware device without any conversions","PCMName":"hw:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nHardware device with all software conversions","PCMName":"plughw:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nHardware device with all software conversions","PCMName":"plughw:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDefault Audio Device","PCMName":"sysdefault:CARD=AudioPCI","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nFront output / input","PCMName":"front:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nRear speakers","PCMName":"rear:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\n4.0 Surround output to Front and Rear speakers","PCMName":"surround40:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nIEC958 (S/PDIF) Digital Audio Output","PCMName":"iec958:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect sample mixing device","PCMName":"dmix:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nDirect sample mixing device","PCMName":"dmix:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Input","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect sample snooping device","PCMName":"dsnoop:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI\nUSB Stream Output","PCMName":"usbstream:CARD=AudioPCI","SubDevNum":"未知设备编号"}]', ConfigDesc = '系统的声卡信息' WHERE ConfigKey = 'ACAS_SoundCardInfo';