Oracle 觸發器中使用遊標

為表ANNOUNCEMENT建立插入觸發器，原先設計的庫是先將選擇的所有人員插入到ANNOUNCEMENTORG表中，然後在ANNOUNCEMENT表中插入其他相關資訊。

表ANNOUNCEMENTORG中的ANNOUNCEMENTCODE和ANNOUNCEMENT中的CODE關聯。

create or replace trigger TR_ANNOUNCEMENT_SYIT
after insert on ANNOUNCEMENT
for each row
declare
  v_usercode ANNOUNCEMENTORG.Relationcode%TYPE; --宣告一個臨時變數，用來儲存使用者名稱
  ucode varchar2(200);
  phoneNumber varchar2(20);
  deptName varchar2(50);
  Cursor cur_usercode is
     --宣告一個遊標，用來儲存查詢出來的人員名
     select relationcode from ANNOUNCEMENTORG where ANNOUNCEMENTCODE=:new.CODE; 
begin
   select ORGNAME into deptName from hefeiuums.sys_organization where ORGID=(select ORGID from hefeiuums.sys_User where username=:new.PUBLISHER);
   open cur_usercode;
   loop
      fetch cur_usercode into v_usercode;
      exit when cur_usercode%NOTFOUND; --讀到最後一條語句後退出
       select mobile into phoneNumber from hefeiuums.sys_User where usercode=v_usercode;
       
       --當手機號碼不為空，進行資料插入
       if phoneNumber is not null then
           insert into T_OASMS(phone,content,sender,sendernm) values(phoneNumber,deptName||'通知：'||:new.TITLE||',已傳送到您的數字排水後臺，請登入平臺查收!',:new.PROMUGATOR,:new.PUBLISHER);
       end if;
   end loop;
   close cur_usercode;
end;

為FLOWDOCSTATE表建立熱線流程下一步簡訊通知功能：

create or replace trigger TR_FLOWDOCSTATE_SYIT
after insert on FLOWDOCSTATE
for each row
declare
  v_userphone hefeiuums.sys_User.MOBILE%TYPE; --宣告一個臨時變數，用來儲存手機號
  title varchar2(200); --熱線標題
  phone varchar2(20); --分管領導手機號碼
  v_phone2 varchar2(20); --部門所有人員手機號碼
  uname varchar2(20);
  deptName varchar2(20);
  Cursor cur_userphone is
  select MOBILE from hefeiuums.sys_User where ORGID=:new.ACCOUNTABLEUNIT; --宣告一個遊標，用來儲存根據部門名稱查詢出來的手機號碼
begin
      if :new.WORKFLOWCODE='HotLineCase' then
          select CASETITLE into title from Case where CASECODE=:new.DOCCODE;
          if :new.ACCOUNTABLEUSER is not null then
             select mobile into phone from hefeiuums.sys_User where usercode=:new.ACCOUNTABLEUSER;
             select username into uname from hefeiuums.sys_User where usercode=:new.ACCOUNTABLEUSER;
             if phone is not null then
                 --給流程的下一步接收者傳送訊息
                 insert into T_OASMS(phone,content,sender,sendernm) values(phone,'有新的熱線資訊：'||title||',請登入辦公系統及時處理！',uname,uname);
             end if;
          elsif :new.ACCOUNTABLEUNIT is not null then
             --遊標遍歷
             open cur_userphone;
             loop
                fetch cur_userphone into v_phone2;
                exit when cur_userphone%NOTFOUND; --讀到最後一條語句後退出
                if v_phone2 is not null then
                   --給流程的所有部門接收者傳送訊息
                   select ORGNAME into deptName from hefeiuums.sys_organization where ORGID=:new.ACCOUNTABLEUNIT;
                   insert into T_OASMS(phone,content,sender,sendernm) values(v_phone2,'有新的熱線資訊：'||title||',請登入辦公系統及時處理！',deptName,deptName);
               end if;
            end loop;
          close cur_userphone;
        end if;
    end if;
end;