No_data_found exception handling when selecting into in Oracle triggers

Source: Internet
Author: User

The red part is used to handle exceptions where no data is found.

Create or replace trigger TIG_MONITOR_ALARM

After insert on t_monitor_real_minute
For each row
Declare
-- Standard value
Standvalue number;
-- Alarm fact table id
Liveid number;
Begin
--
-- Trigger function: monitors the real-time data table and compares whether the monitoring data exceeds the standard. If the data exceeds the standard, the system records the data in the alarm table that exceeds the standard.
--
Standvalue: =-1;
Liveid: =-1;
Select nvl (t. bzz,-1) into standvalue from t_monitor_factor t where t. jcdbm =: new. STATION_ID and t. jcxmbm =: new. INFECTANT_ID;

-- If the data of the input detection item is greater than the standard value, the data is stored in the alarm information table.
If standvalue>-1 then
If: new. M_VALUE> standvalue then
-- Input data into historical alarm data
Insert into t_alarm_history (id, jcdbm, jcxmbm, mvalue, mtime)
Values (SEQ_ALARM_HISTORY.NEXTVAL,: new. STATION_ID,: new. INFECTANT_ID,: new. M_VALUE,: new. M_TIME );

-- Exception judgment. If no data is found
Begin
Select r. id into liveid from t_alarm_real r where r. jcdbm =: new. STATION_ID and r. jcxmbm =: new. INFECTANT_ID;
-- No data found
EXCEPTION
WHEN no_data_found THEN
-- If the alarm does not exist, enter the new alarm status.
Insert into t_alarm_history (id, jcdbm, jcxmbm, mvalue, mtime)
Values (SEQ_ALARM_REAL.NEXTVAL,: new. STATION_ID,: new. INFECTANT_ID,: new. M_VALUE,: new. M_TIME );
End;
-- Whether the alarm information of this factor exists in the alarm status
If liveid>-1 then
Update t_alarm_real r1 set r1.mvalue =: new. M_VALUE, r1.mtime =: new. M_TIME, r1.status = 0 where r1.id = liveid;
Else
-- If the alarm does not exist, enter the new alarm status.
Insert into t_alarm_history (id, jcdbm, jcxmbm, mvalue, mtime)
Values (SEQ_ALARM_REAL.NEXTVAL,: new. STATION_ID,: new. INFECTANT_ID,: new. M_VALUE,: new. M_TIME );
End if;
End if;
End if;

EXCEPTION
WHEN no_data_found THEN
Null;

End TIG_MONITOR_ALARM;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.