Oracle 触发器,函数,过程调用出问题了 帮帮忙
我导入数据到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;
更新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;
...全文
相关推荐
oracle 触发器实时调用java 中http接口
oracle insert数据成功之后调用触发器,触发器调用存储过程,存储实时调用java http
在ORACLE触发器里调用JAVA程序
因为项目需要,有一个已经写好的JAVA程序,想要在ORACLE某个表的触发器中调用,以使得每次数据更新时,调用这个JAVA程序,来修改后台某个数据。现将过程记录如下:1。编写JAVA程序
oracle触发器调用存储过程
oracle触发器调用存储过程,最简单的例子适用于初学者
oracle 存储过程、函数和触发器用法实例详解
本文实例讲述了oracle 存储过程、函数和触发器用法。分享给大家供大家参考,具体如下: 一、存储过程和存储函数 指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。 创建存储过程 用CREATE PROCEDURE命令建立存储过程。 语法: create [or replace] procedure 过程名(参数列表) as PLSQL子程序体; --给指定员工涨工资 create procedure addSal(empid in number) as psal emp.sal%type; begin select sal into psal from emp wh
个人亲测oracle触发器调用java程序
个人亲测oracle触发器调用java程序


