Oracle 触发器，函数，过程调用出问题了帮帮忙

Smile_Liber 2011-03-26 07:39:53

我导入数据到userinfo_temp后触发触发器USERINFO_TEMP_ADD
更新USERINFO表

出现错误：
ORA-04091: 表 RONGHE.USERINFO 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "RONGHE.SELECT_USE_ITEMS_RECORD", line 8
ORA-06512: 在 "RONGHE.PR_USERINFO_NEW_ADD", line 3
ORA-06512: 在 "RONGHE.USERINFO_TEMP_ADD", line 2
ORA-04088: 触发器 'RONGHE.USERINFO_TEMP_ADD' 执行过程中出错

触发器---------------------------------------------------
create or replace TRIGGER USERINFO_TEMP_ADD
AFTER INSERT ON USERINFO_TEMP
BEGIN
pr_userinfo_new_add;
END;
过程------------------------------------------------------
create or replace PROCEDURE PR_USERINFO_NEW_ADD AS
BEGIN

MERGE INTO userinfo u
USING userinfo_temp ut
ON (u.name = ut.name and u.USE_ITEMS = ut.USE_ITEMS )
WHEN MATCHED THEN
UPDATE
SET u.sex = ut.sex, u.cred_name = ut.cred_name, u.cred_number = ut.cred_number,
u.MARITAL_STATUS = ut.MARITAL_STATUS,u.ANNUAL_INCOME=ut.ANNUAL_INCOME,u.CONTACT_STATUS = ut.CONTACT_STATUS,
u.USE_ITEMS_RECORD = select_use_items_record(ut.NAME,ut.USE_ITEMS,ut.USE_ITEMS)
WHERE u.cred_number = ut.cred_number
when not matched then
insert (u.NAME, u.SEX, u.CRED_NAME, u.CRED_NUMBER, u.MARITAL_STATUS, u.ANNUAL_INCOME, u.CONTACT_STATUS, u.USE_ITEMS)
values(ut.NAME, ut.SEX, ut.CRED_NAME, ut.CRED_NUMBER, ut.MARITAL_STATUS, ut.ANNUAL_INCOME, ut.CONTACT_STATUS, ut.USE_ITEMS)
where ut.name not in (select name FROM userinfo);
END PR_USERINFO_NEW_ADD;

函数------------------------------------------------------
create or replace FUNCTION SELECT_USE_ITEMS_RECORD
( IN_NAME IN VARCHAR2, IN_USE_ITEMS IN VARCHAR2, IN_USE_ITEMS_RECORD IN VARCHAR2
) RETURN VARCHAR2 AS
userinfo_id INTEGER;
sel_use_items_record VARCHAR2(40);
instr_number INTEGER;
BEGIN
select id INTO userinfo_id FROM userinfo WHERE name = in_name AND use_items = in_use_items;
select use_items_record INTO sel_use_items_record FROM userinfo WHERE id= userinfo_id;
select instr(sel_use_items_record, in_use_items_record) INTO instr_number from dual;
if instr_number = 0 then
begin
sel_use_items_record := sel_use_items_record || '-' || in_use_items_record;
end;
end if;
RETURN sel_use_items_record;
END SELECT_USE_ITEMS_RECORD;

...全文