需求:根据主表一个字段,更新子表的字段; 1、主表与子表可能一对多; 2、主表数据更新后需要更新子表; 3、子表记录较多,注意性能; /**************************************************************************/ --Desc:此存储过程用来更新关联表,(如:根据主表更新多个子表的行政区) --Author: --Date:2009-8-7 --Attention:syn_rel_data_err_recode是存储异常信息的表 /**************************************************************************/ create or replace procedure syn_rel_data(p_tab_name in varchar2, --主表的表名 p_col_rel in varchar2, --主表的关联字段 p_col_upd in varchar2, --主表的更新子表的字段 s_tab_name in varchar2, --子表的表名 s_col_pk in varchar2, --子表的主键 s_col_rel in varchar2, --子表的关联字段 s_col_upd in varchar2) --子表的待更新字段 authid current_user is str varchar2(2000); --sql语句变量 flag int; --临时表是否已存在的标志 err_code varchar2(32); --ora异常码 err_message varchar2(2000); --ora异常提示信息 begin --step1:delete if temporary object exists select count(*) into flag from dual where exists(select tname from tab where tname =UPPER(s_tab_name || '_T')) ; if flag>0 then str := 'drop table ' || s_tab_name || '_t'; execute immediate str; end if; --step2:create temporary table str := 'create table ' || s_tab_name || '_t as (select s.' || s_col_pk || ', p.' || p_col_upd || ' from ' || p_tab_name || ' p , ' || s_tab_name || ' s where p.' || p_col_rel || ' = s.' || s_col_rel || ' and p.' || p_col_upd || ' is not null and p.' || p_col_upd || ' != nvl(s.' || s_col_upd || ',9999) )';--9999无实际意义,只是用做与null比较的处理。 execute immediate str; --step3:create constraint str := 'alter table ' || s_tab_name || '_t add constraint ' || s_tab_name || '_t_PK primary key (' || s_col_pk || ')'; execute immediate str; --step4:update relation data in sub-table str := 'update (select s.' || s_col_upd || ' col_upd_to, t.' || p_col_upd || ' col_upd_from from ' || s_tab_name || ' s, ' || s_tab_name || '_t t where s.' || s_col_pk || ' = t.' || s_col_pk || ') set col_upd_to = col_upd_from'; execute immediate str; str := 'commit'; execute immediate str; --step5:delete if temporary object exists select count(*) into flag from dual where exists(select tname from tab where tname =UPPER(s_tab_name || '_T')) ; if flag>0 then str := 'drop table ' || s_tab_name || '_t'; execute immediate str; end if; EXCEPTION WHEN OTHERS THEN err_code := SQLCODE; err_message := SQLERRM; INSERT INTO syn_rel_data_err_recode --存储异常信息 VALUES (p_tab_name, s_tab_name, err_code, err_message, sysdate); commit; end; / 异常信息表: -- Create table create table SYN_REL_DATA_ERR_RECODE ( ERR_P_TAB VARCHAR2(30), ERR_S_TAB VARCHAR2(30), ERR_CODE VARCHAR2(32), ERR_MESSAGE VARCHAR2(2000), ERR_TIME DATE ) tablespace JGDZ pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column SYN_REL_DATA_ERR_RECODE.ERR_P_TAB is '主表名称'; comment on column SYN_REL_DATA_ERR_RECODE.ERR_S_TAB is '子表名称'; comment on column SYN_REL_DATA_ERR_RECODE.ERR_CODE is 'oracle异常码'; comment on column SYN_REL_DATA_ERR_RECODE.ERR_MESSAGE is 'oracle异常信息'; comment on column SYN_REL_DATA_ERR_RECODE.ERR_TIME is '发生错误时间';