常用的存储过程和触发器范例

一些常用的数据库脚本，需要的时候可以照葫芦画瓢

CREATE OR REPLACE PROCEDURE mdmc_spDeleteRole(v_RoleID number, v_tablepostfix varchar2)
AS
        v_Pos         number(12);
  v_postfix     varchar2(100);
  v_postfixes   varchar2(1000);
  v_sql         varchar2(3000);
  v_tablename   varchar2(1000);
  v_separator   varchar2(1);
BEGIN
  v_separator   := CHR(7);

  delete from ECONF_ROLE where id = v_RoleID;

  v_postfixes   := v_tablepostfix;
  v_pos         := INSTR(v_tablepostfix, v_separator);

  WHILE (v_pos >1) LOOP
    v_postfix   := SUBSTR(v_postfixes,1,v_pos -1);
    v_postfixes := SUBSTR(v_postfixes,v_pos + 1);
    v_tablename := 'econf_rolevisual_'||v_postfix;
    v_sql       := 'delete from '||v_tablename||' where id = '||TO_CHAR(v_RoleID)||'';
    execute immediate v_sql;
    v_pos       := INSTR(v_postfixes, v_separator);
  END LOOP;

  delete from ECONF_ROLE_PERMISSION where ROLEID = v_RoleID;

  COMMIT;
  EXCEPTION
        WHEN others THEN
                rollback;
END mdmc_spDeleteRole;


create or replace procedure p_tnamemap
(
    i_OldName       in      varchar2,   --
    o_NewName       out     varchar2    --
)
authid current_user
as
    i_Count         integer;
    i_Flag          integer;
    i_Count2        integer;
    i_Count3        integer;
    v_NewName       varchar2(100);

begin
    --
    i_Count2 := length(i_OldName);

    if (i_Count2 > 2) then
        i_Count3 := 2;
    else
        i_Count3 := i_Count2;
    end if;

    --
    --lock table t_tnamemap in exclusive mode;

    select count(*)
      into i_count
      from t_tnamemap
     where oldname =  upper(i_oldname);

    --
    if i_Count = 0 then
        select trigger_name_se.nextval
          into i_flag
          from dual;
        v_NewName := substr(i_OldName, 1, i_Count3);
        v_NewName := v_NewName || to_char(i_Flag);

        insert into t_tnamemap
                  ( oldname,
                    newname
                  )
             values
                  ( upper(i_oldname),
                    upper(v_newname)
                   );
        o_NewName := v_NewName;
    elsif i_Count = 1 then
        select newname
          into v_NewName
          from t_tnamemap
         where oldname = upper(i_OldName);
        o_NewName := v_NewName;
    end if;

    commit;
end;


//带返回值存储过程
create or replace procedure P_CLEAN_ROUTER
(
    i_OprID         in      integer,    --
    o_RerurnCode    out     integer     --
)
as
  v_Size         integer;

begin
    select count(*) into v_size
    from  t_router_control
    where oprid = i_OprID;

    if  v_Size = 0 then
        delete from t_syncdata_see where oprid = i_OprID;
    end if;

    o_RerurnCode := 0;
    commit;

exception
  when others then
  o_RerurnCode := -1;
end;


//自动插入数据
create or replace procedure autoadd_met_record(v_addnum in number) as
  v_temp number(12);

begin
  v_temp := 0;

  while (v_temp < v_addnum) loop
        insert into cw_mtg
    values
      (seq_econf_cw_mtg.NEXTVAL,
       'test' || seq_econf_cw_mtg.currval,
       '',
       0,
       '100000' || seq_econf_cw_mtg.currval,
       '111111',
       0,
       '1',
       0,
       0,
       208,
       71,
       71,
       71,
       0,
       '2011-11-10 04:50:00',
       '2011-11-10 05:50:00',
       '2011-11-10 06:50:00',
       30,
       0,
       2000,
       16,
       16,
       8,
       1,
       1000,
       '02',
       '00100',
       '100011110000',
       'test',
       '',
       '000000000011110000',
       '',
       0,
       '1',
       '15',
       1,
       '46005|14397|35881',
       1,
       0,
       0,
       0,
       '',
       '',
       0,
       0,
       0,
       'F2011111010419',
       '',
       '12345678',
       '',
       0,
       0,
       'UTC+0800');
    v_temp := v_temp + 1;
  end loop;
  commit;
EXCEPTION
  WHEN others THEN
    rollback;
END autoadd_met_record;



create or replace procedure autoadd_met_record(v_addnum in number)
as
v_temp      number(12);
v_seq       number(12);
begin
v_temp := 0;
while(v_temp < v_addnum) loop
select seq_econf_cw_mtg.NEXTVAL into v_seq from dual;
insert into cw_mtg values(v_seq,'test','testpwd',0,'111111',0,'1',5,0,208,0,6,6,6,0,sysdate,sysdate,sysdate,30,0,2000,16,16,8,1,1000,'02','02100','100011110000','test','','000000000011110000','',0,'1','15',1,'46005|14397|35881',1,0,30,0,'','',0,0,0,'0','','12345678','',0,0,'UTC+0800');
v_temp := v_temp + 1;
end loop;
commit;
EXCEPTION
WHEN others THEN
rollback;
END autoadd_met_record;


//自动插入主键ID
CREATE OR REPLACE TRIGGER BATCHLOGID_TRIG
        BEFORE INSERT ON ECONF_IMS_BATCHLOG
        REFERENCING NEW AS new OLD AS old FOR EACH ROW
BEGIN

        SELECT ims_batchlog_seq.NextVal INTO :new.IDX FROM dual;

END BATCHLOGID_TRIG;

触发器
//一些典型的操作

create or replace trigger econf_enterprise_t1 after insert or delete
on ECONF_ENTERPRISE_INFO
for each row

begin
  if inserting then
   update LBU_SYS_CONFACCOUNT set ISOPEN=1 where ACCOUNTID=:NEW.account;
  end if;

  if deleting then
         update LBU_SYS_CONFACCOUNT set ISOPEN=0 where ACCOUNTID=:OLD.account;
       delete from ECONF_ACCOUNT_INFO where ENTERPRISEID= :OLD.ENTERPRISEID;
       delete from contacts where username = :OLD.account;
       delete from groups where username = :OLD.account;
               delete from groupmembers where username= :OLD.account;
  end if;
end;