pl/sql 触发器

   

变量和类型

Declare

Studentname varchar2(20);

currentDate Date;

 

条件语句

Declare

Totalstudents NUMBER;

BEGIN

 SELECT COUNT(*)

     INTO Totalstudents

     From students;

IF totalstudents=0 THEN

 INSERT INTO temp_table(char_col)

     VALUES(‘There are no students ‘);

ELSIF Totalstudents<5 THEN

 INSERT INTO temp_talbe(char_col)

     VALUES(‘There are only a few students’);

ELSE

 INSERT INTO temp_table(char_col)

     VALUES(there are many students’);

END IF;

END;

 

循环结构

DECLARE

 Loopcounter BINARY_INTEGER :=1;

BEGIN

 LOOP

INSERT INTO temp_table(num_col)

 Values(loopcounter);

 Loopcounter:=voopcounter_1;

EXIT WHEN loopcounter>50;

 END LOOP;

END;

 

BEGIN

 FOR loopcounter IN 1..50 LOOP

     INSERT INTO temp_talbe(num_col)

        VALUES(loopcounter);

 END LOOP;

END;

 

游标

DECLARE

 Firstname VARCHAR2(20);

 Lastname VARCHAR2(20);

 CURSOR c_students IS

SELECT firstname,lastname

 From students;

BEGIN

 OPEN c_students;

 LOOP

FETCH c_students INTO firstname,lastname;

EXIT WHEN c_students%NOTFOUND;

 END LOOP;

 CLOSE c_students;

END;

 

Create or replace function classinfo(

p_department classes.department%type,

p_sourse classes.course%type)

return varchar2 is

v_currentsudents number;

v_maxstudents number;

v_percentfull number;

begin

select current_students,max_students into v_currentstudents,v_maxstudents

from classes

where department=p_department

and course=p_sourse=p_course;

v_percentfull :=v_currentsudents/v_maxstudents*100;

if v_percentfull=100 then

 return ‘full’;

elsif v_percentfull>80 then

 return ‘some room’ ;

elsif v_percentfull>60 then

 return ‘more room’;

else

 return ‘empty’;

end if;

end classinfo;

CREATE OR REPLACE TRIGGER TI_EAPT_BANKRECORD

AFTER UPDATE

OF GIVEAMOUT

       ,REVAMOUT

       ,REMAINAMOUT

ON EADB.EAPT_BANKRECORD

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

DECLARE

       IF(UPDATING)

       THEN

              IF(NVL(:NEW.REMAINAMOUT,0)>NVL(:NEW.GIVEAMOUNT,0)

                     AND NVL(:NEW.REMAINAMOUNT,0)>NVL(:NEW.RCVAMOUNT,0)

                     )

              THEN

                     RAISE_APPLICATION_ERROR(-20000,’[ERROR.BANKRECORD.REMAINAMOUNT]’);

END IF;

END IF;

EXCEPTION
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END ti_eapt_bankrecord;