触发器如下:
SQL> create or replace trigger tri_pc_fl_we_mo after insert on PC_FLOODING_WELL_MOTION
2 --当PC_FLOODING_WELL_MOTION表中有insert命令时执行触发器
3 for each row
4 --遍历每一行
5 declare
6 temp_l1 number(10,4);--声明一个临时变量,用来存储一级下限
7 temp_l2 number(10,4);--声明一个临时变量,用来存储二级下限
8 temp_l3 number(10,4);--声明一个临时变量,用来存储三级下限
9 temp_u1 number(10,4);--声明一个临时变量,用来存储一级上限
10 temp_u2 number(10,4);--声明一个临时变量,用来存储二级上限
11 temp_u3 number(10,4);--声明一个临时变量,用来存储三级上限
12 begin
13 --下面为油压报警判断
14 select lower_limit_1,lower_limit_2,lower_limit_3,upper_limit_1,upper_limit_2,upper_limit_3 into temp_l1,temp_l2,temp_l3,temp_u1,temp_u2,temp_u3 from pc_thesshold where well_id = :new.well_id and alert_name = '油压报警';
15 --把pc_thesshold表中的123级报警上下限分别赋值给临时变量,通过well_id和alert_name关联
16 if (:new.tubing_pres > temp_u3 or :new.tubing_pres < temp_l3) then
17 --如果油压超过3级报警的上限或下限,则向pc_alarm_record_beta表中插入数据,未处理,报警级别三
18 insert into pc_alarm_record_beta (well_id,alert_name,alarm_time,if_deal,alert_level) values(:new.well_id,'油压报警',sysdate,'0','3');
19 else if ((:new.tubing_pres > temp_u2 and :new.tubing_pres < temp_u3 ) or (:new.tubing_pres < temp_l2 and :new.tubing_pres > temp_u3 )) then
20 --如果油压超过2级报警的上限或下限,则向pc_alarm_record_beta表中插入数据,未处理,报警级别二
21 insert into pc_alarm_record_beta (well_id,alert_name,alarm_time,if_deal,alert_level) values(:new.well_id,'油压报警',sysdate,'0','2');
22 else if ((:new.tubing_pres > temp_u1 and :new.tubing_pres < temp_u2 ) or (:new.tubing_pres < temp_l1 and :new.tubing_pres > temp_u3 )) then
23 --如果油压超过1级报警的上限或下限,则向pc_alarm_record_beta表中插入数据,未处理,报警级别一
24 insert into pc_alarm_record_beta (well_id,alert_name,alarm_time,if_deal,alert_level) values(:new.well_id,'油压报警',sysdate,'0','1');
25 end if;
26 end;
27 /错误信息如下:
LINE/COL ERROR
-------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10/220 PL/SQL: ORA-00911: 无效字符
10/2 PL/SQL: SQL Statement ignored
15/2 PLS-00103: 出现符号 "ELSE"在需要下列之一时: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe
22/4 PLS-00103: 出现符号 ";"在需要下列之一时: if 寻求各位的帮助,谢谢了
SQL> create or replace trigger tri_pc_fl_we_mo after insert on PC_FLOODING_WELL_MOTION
2 --当PC_FLOODING_WELL_MOTION表中有insert命令时执行触发器
3 for each row
4 --遍历每一行
5 declare
6 temp_l1 number(10,4);--声明一个临时变量,用来存储一级下限
7 temp_l2 number(10,4);--声明一个临时变量,用来存储二级下限
8 temp_l3 number(10,4);--声明一个临时变量,用来存储三级下限
9 temp_u1 number(10,4);--声明一个临时变量,用来存储一级上限
10 temp_u2 number(10,4);--声明一个临时变量,用来存储二级上限
11 temp_u3 number(10,4);--声明一个临时变量,用来存储三级上限
12 begin
13 --下面为油压报警判断
14 select lower_limit_1,lower_limit_2,lower_limit_3,upper_limit_1,upper_limit_2,upper_limit_3 into temp_l1,temp_l2,temp_l3,temp_u1,temp_u2,temp_u3 from pc_thesshold where well_id = :new.well_id and alert_name = '油压报警';
15 --把pc_thesshold表中的123级报警上下限分别赋值给临时变量,通过well_id和alert_name关联
16 if (:new.tubing_pres > temp_u3 or :new.tubing_pres < temp_l3) then
17 --如果油压超过3级报警的上限或下限,则向pc_alarm_record_beta表中插入数据,未处理,报警级别三
18 insert into pc_alarm_record_beta (well_id,alert_name,alarm_time,if_deal,alert_level) values(:new.well_id,'油压报警',sysdate,'0','3');
19 else if ((:new.tubing_pres > temp_u2 and :new.tubing_pres < temp_u3 ) or (:new.tubing_pres < temp_l2 and :new.tubing_pres > temp_u3 )) then
20 --如果油压超过2级报警的上限或下限,则向pc_alarm_record_beta表中插入数据,未处理,报警级别二
21 insert into pc_alarm_record_beta (well_id,alert_name,alarm_time,if_deal,alert_level) values(:new.well_id,'油压报警',sysdate,'0','2');
22 else if ((:new.tubing_pres > temp_u1 and :new.tubing_pres < temp_u2 ) or (:new.tubing_pres < temp_l1 and :new.tubing_pres > temp_u3 )) then
23 --如果油压超过1级报警的上限或下限,则向pc_alarm_record_beta表中插入数据,未处理,报警级别一
24 insert into pc_alarm_record_beta (well_id,alert_name,alarm_time,if_deal,alert_level) values(:new.well_id,'油压报警',sysdate,'0','1');
25 end if;
26 end;
27 /错误信息如下:
LINE/COL ERROR
-------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10/220 PL/SQL: ORA-00911: 无效字符
10/2 PL/SQL: SQL Statement ignored
15/2 PLS-00103: 出现符号 "ELSE"在需要下列之一时: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe
22/4 PLS-00103: 出现符号 ";"在需要下列之一时: if 寻求各位的帮助,谢谢了
14 select lower_limit_1,lower_limit_2,lower_limit_3,upper_limit_1,upper_limit_2,upper_limit_3 into temp_l1,temp_l2,temp_l3,temp_u1,temp_u2,temp_u3 from pc_thesshold where well_id = :new.well_id and alert_name = '油压报警';;英文分号
要比elseif少一个e