PROCEDURE T_BANJIE_gen( p_ywlsh in Varchar2,
p_sjbbh in Varchar2,
p_spsxbh in Varchar2,
p_spsxzxbh in Varchar2,
p_yxtywlsh in Varchar2,
p_bjjgzzjgdm in Varchar2,
p_bjjgmc in Varchar2,
p_bjsj in Varchar2,
p_bjjg in Varchar2,
p_bljgms in Varchar2,
p_zfthyy in Varchar2,
p_zjmc in Varchar2,
p_zjbh in Varchar2,
p_zjyxqx in Varchar2,
p_fzdw in Varchar2,
p_sfje in Varchar2,
p_jedwdm in Varchar2,
p_bz in Varchar2,
p_byzda in Varchar2,
p_byzdb in Varchar2,
p_byzdc in Varchar2,
p_byzdd in Varchar2 ) IS
m_bjsj DATE;
m_byzdd DATE;
err_code T_SYS_SQL_ERR.ERR_CODE%TYPE;
err_text T_SYS_SQL_ERR.ERR_TEXT%TYPE;
m_count NUMBER;
BEGIN
IF p_BJSJ IS NULL OR p_BJSJ = 'null' THEN
m_bjsj := NULL;
ELSE
m_bjsj := TO_timestamp(p_BJSJ, 'yyyy-mm-dd hh24:mi:ss.ff');
END IF; IF p_byzdd IS NULL OR p_byzdd = 'null' THEN
m_byzdd := NULL;
ELSE
m_byzdd := TO_timestamp(p_byzdd, 'yyyy-mm-dd hh24:mi:ss.ff');
END IF; -- 先写到数据交换备份表中
INSERT INTO T_EX_XZXK_BANJIE
(YWLSH,
SJBBH,
SPSXBH,
SPSXZXBH,
YXTYWLSH,
BJJGZZJGDM,
BJJGMC,
BJSJ,
BJJG,
BLJGMS,
ZFTHYY,
ZJMC,
ZJBH,
ZJYXQX,
FZDW,
SFJE,
JEDWDM,
BZ,
BYZDA,
BYZDB,
BYZDC,
BYZDD,
STATUS,
INPUTMODE,
sptime)
VALUES
(P_YWLSH,
P_SJBBH,
P_SPSXBH,
P_SPSXZXBH,
P_YXTYWLSH,
P_BJJGZZJGDM,
P_BJJGMC,
m_bjsj, --
P_BJJG,
P_BLJGMS,
P_ZFTHYY,
P_ZJMC,
P_ZJBH,
P_ZJYXQX,
P_FZDW,
P_SFJE,
P_JEDWDM,
P_BZ,
P_BYZDA,
P_BYZDB,
P_BYZDC,
m_BYZDD,
'1',
'0',
SYSDATE); -- 记录接收的时间
-- 检查一下这笔数据是否在历史表中已经存在
SELECT COUNT(1)
INTO m_count
FROM h_JC_XZXK_BANJIE
WHERE ywlsh = P_YWLSH
AND sjbbh = P_SJBBH;
IF m_count < 1 THEN
INSERT INTO T_JC_XZXK_BANJIE
(YWLSH,
SJBBH,
SPSXBH,
SPSXZXBH,
YXTYWLSH,
BJJGZZJGDM,
BJJGMC,
BJSJ,
BJJG,
BLJGMS,
ZFTHYY,
ZJMC,
ZJBH,
ZJYXQX,
FZDW,
SFJE,
JEDWDM,
BZ,
BYZDA,
BYZDB,
BYZDC,
BYZDD,
STATUS,
INPUTMODE,
sptime)
VALUES
(P_YWLSH,
P_SJBBH,
P_SPSXBH,
P_SPSXZXBH,
P_YXTYWLSH,
P_BJJGZZJGDM,
P_BJJGMC,
m_bjsj,
P_BJJG,
P_BLJGMS,
P_ZFTHYY,
P_ZJMC,
P_ZJBH,
P_ZJYXQX,
P_FZDW,
P_SFJE,
P_JEDWDM,
P_BZ,
P_BYZDA,
P_BYZDB,
P_BYZDC,
m_BYZDD,
'1',
'0',
SYSDATE); -- 记录接收的时间
ELSE
INSERT INTO T_SYS_SQL_ERR(ID, MODEL_NAME, ERR_CODE, ERR_TEXT, TIME, OTHERS)
VALUES (SEQ_SQL_ERR.nextval,
'EX.T_BANJIE_gen',
'-100',
'该业务数据在对应的历史表中已经存在,不能再次写入。',
sysdate,
p_YWLSH || ' ' || p_SJBBH);
END IF; EXCEPTION
when OTHERS then
err_code := sqlcode;
err_text := sqlerrm;
INSERT INTO T_SYS_SQL_ERR(ID, MODEL_NAME, ERR_CODE, ERR_TEXT, TIME, OTHERS)
VALUES (SEQ_SQL_ERR.nextval,
'EX.T_BANJIE_gen',
err_code,
err_text,
sysdate,
p_YWLSH || ' ' || p_SJBBH);
END T_BANJIE_gen;
p_sjbbh in Varchar2,
p_spsxbh in Varchar2,
p_spsxzxbh in Varchar2,
p_yxtywlsh in Varchar2,
p_bjjgzzjgdm in Varchar2,
p_bjjgmc in Varchar2,
p_bjsj in Varchar2,
p_bjjg in Varchar2,
p_bljgms in Varchar2,
p_zfthyy in Varchar2,
p_zjmc in Varchar2,
p_zjbh in Varchar2,
p_zjyxqx in Varchar2,
p_fzdw in Varchar2,
p_sfje in Varchar2,
p_jedwdm in Varchar2,
p_bz in Varchar2,
p_byzda in Varchar2,
p_byzdb in Varchar2,
p_byzdc in Varchar2,
p_byzdd in Varchar2 ) IS
m_bjsj DATE;
m_byzdd DATE;
err_code T_SYS_SQL_ERR.ERR_CODE%TYPE;
err_text T_SYS_SQL_ERR.ERR_TEXT%TYPE;
m_count NUMBER;
BEGIN
IF p_BJSJ IS NULL OR p_BJSJ = 'null' THEN
m_bjsj := NULL;
ELSE
m_bjsj := TO_timestamp(p_BJSJ, 'yyyy-mm-dd hh24:mi:ss.ff');
END IF; IF p_byzdd IS NULL OR p_byzdd = 'null' THEN
m_byzdd := NULL;
ELSE
m_byzdd := TO_timestamp(p_byzdd, 'yyyy-mm-dd hh24:mi:ss.ff');
END IF; -- 先写到数据交换备份表中
INSERT INTO T_EX_XZXK_BANJIE
(YWLSH,
SJBBH,
SPSXBH,
SPSXZXBH,
YXTYWLSH,
BJJGZZJGDM,
BJJGMC,
BJSJ,
BJJG,
BLJGMS,
ZFTHYY,
ZJMC,
ZJBH,
ZJYXQX,
FZDW,
SFJE,
JEDWDM,
BZ,
BYZDA,
BYZDB,
BYZDC,
BYZDD,
STATUS,
INPUTMODE,
sptime)
VALUES
(P_YWLSH,
P_SJBBH,
P_SPSXBH,
P_SPSXZXBH,
P_YXTYWLSH,
P_BJJGZZJGDM,
P_BJJGMC,
m_bjsj, --
P_BJJG,
P_BLJGMS,
P_ZFTHYY,
P_ZJMC,
P_ZJBH,
P_ZJYXQX,
P_FZDW,
P_SFJE,
P_JEDWDM,
P_BZ,
P_BYZDA,
P_BYZDB,
P_BYZDC,
m_BYZDD,
'1',
'0',
SYSDATE); -- 记录接收的时间
-- 检查一下这笔数据是否在历史表中已经存在
SELECT COUNT(1)
INTO m_count
FROM h_JC_XZXK_BANJIE
WHERE ywlsh = P_YWLSH
AND sjbbh = P_SJBBH;
IF m_count < 1 THEN
INSERT INTO T_JC_XZXK_BANJIE
(YWLSH,
SJBBH,
SPSXBH,
SPSXZXBH,
YXTYWLSH,
BJJGZZJGDM,
BJJGMC,
BJSJ,
BJJG,
BLJGMS,
ZFTHYY,
ZJMC,
ZJBH,
ZJYXQX,
FZDW,
SFJE,
JEDWDM,
BZ,
BYZDA,
BYZDB,
BYZDC,
BYZDD,
STATUS,
INPUTMODE,
sptime)
VALUES
(P_YWLSH,
P_SJBBH,
P_SPSXBH,
P_SPSXZXBH,
P_YXTYWLSH,
P_BJJGZZJGDM,
P_BJJGMC,
m_bjsj,
P_BJJG,
P_BLJGMS,
P_ZFTHYY,
P_ZJMC,
P_ZJBH,
P_ZJYXQX,
P_FZDW,
P_SFJE,
P_JEDWDM,
P_BZ,
P_BYZDA,
P_BYZDB,
P_BYZDC,
m_BYZDD,
'1',
'0',
SYSDATE); -- 记录接收的时间
ELSE
INSERT INTO T_SYS_SQL_ERR(ID, MODEL_NAME, ERR_CODE, ERR_TEXT, TIME, OTHERS)
VALUES (SEQ_SQL_ERR.nextval,
'EX.T_BANJIE_gen',
'-100',
'该业务数据在对应的历史表中已经存在,不能再次写入。',
sysdate,
p_YWLSH || ' ' || p_SJBBH);
END IF; EXCEPTION
when OTHERS then
err_code := sqlcode;
err_text := sqlerrm;
INSERT INTO T_SYS_SQL_ERR(ID, MODEL_NAME, ERR_CODE, ERR_TEXT, TIME, OTHERS)
VALUES (SEQ_SQL_ERR.nextval,
'EX.T_BANJIE_gen',
err_code,
err_text,
sysdate,
p_YWLSH || ' ' || p_SJBBH);
END T_BANJIE_gen;
但是T_ex_xzxk_banjie却插不进去这是为何????高手解决 急急急~~~
你的语句太过庞杂,建议使用MERGE INTO优化下...
你是指的 那个一步一步 调试 看代码按照什么顺序运行的?
就是说 本来 这个存储过程是 可以向 t_ex_xzxk_banjie 和t_jc_xzxk_banjie 同时插入数据的但是t_jc_xzxk_banjie 数据插不进 必须手动从 t_ex_xzxk_banjie 复制数据 到t_jc_xzxk_banjie才行
t_jc_xzxk_banjie要有数据进去 才会触发 另外一个触发器的
这步存储过程出了问题 所以尴尬了、。、、也不报错 头疼啊
SELECT COUNT(1)
INTO m_count
FROM h_JC_XZXK_BANJIE
WHERE ywlsh = P_YWLSH
AND sjbbh = P_SJBBH;
IF m_count < 1 THEN说明这个得到的结果没有执行下去,m_count < 1条件不成立.m_count 的值,你调试下..
t_jc_xzxk_banjie否则 触发器 trig_jc_xzxk_banjie是不会被触发的
表的触发器无效,你怎么能往这个表写入数据?
当然会有影响啊...
create or replace trigger TRIG_JC_XZXK_BANJIE
after insert or update or delete on t_jc_xzxk_banjie
for each row
declare
M_COUNT NUMBER;
M_ACTION NUMBER(1); -- 此条数据表示的操作动作:1:新增 2:修改 3:删除
-- local variables here
begin
IF INSERTING THEN
M_ACTION := 1;
ELSIF UPDATING THEN
-- 如果更新了交换的字段
IF UPDATING('ywlsh') or UPDATING('sjbbh') or
UPDATING('spsxbh') or UPDATING('spsxzxbh') or
UPDATING('yxtywlsh') or UPDATING('bjjgzzjgdm') or
UPDATING('bjjgmc') or UPDATING('bjsj') or
UPDATING('bjjg') or UPDATING('bljgms') or
UPDATING('zfthyy') or UPDATING('zjmc') or
UPDATING('zjbh') or UPDATING('zjyxqx') or
UPDATING('fzdw') or UPDATING('sfje') or
UPDATING('jedwdm') or UPDATING('bz') or
UPDATING('byzda') or UPDATING('byzdb') or
UPDATING('byzdc') or UPDATING('byzdd') or
UPDATING('status') or UPDATING('inputmode') or
UPDATING('net') or UPDATING('makeperson ') or
UPDATING('maketime') or UPDATING('spperson') or
UPDATING('sptime') or UPDATING('spstatus') or
UPDATING('dealresult') or UPDATING('makepername') THEN
M_ACTION := 2;
ELSE
RETURN;
END IF;
ELSIF DELETING THEN
M_ACTION := 3;
END IF;
IF M_ACTION = 3 THEN
-- 删除时,要从 :OLD 里取数据
-- 先去历史表里找找,如果有,表示当前做的是“当前表移到历史表”,则不需要做省、市联网交换
SELECT COUNT(1)
INTO M_COUNT
FROM h_jc_xzxk_banjie
WHERE ywlsh = :OLD.YWLSH
and sjbbh = :OLD.SJBBH; IF M_COUNT > 0 THEN
-- 在历史表找到数据
RETURN; -- 退出,什么都不做
END IF;
DZJC_UP_N4.PRO_T_BANJIE(:OLD.ywlsh,
:OLD.sjbbh,
:OLD.spsxbh,
:OLD.spsxzxbh,
:OLD.yxtywlsh,
:OLD.bjjgzzjgdm,
:OLD.bjjgmc,
:OLD.bjsj,
:OLD.bjjg,
:OLD.bljgms,
:OLD.zfthyy,
:OLD.zjmc,
:OLD.zjbh,
:OLD.zjyxqx,
:OLD.fzdw,
:OLD.sfje,
:OLD.jedwdm,
:OLD.bz,
:OLD.byzda,
:OLD.byzdb,
:OLD.byzdc,
:OLD.byzdd,
:OLD.status,
:OLD.inputmode,
:OLD.net,
:OLD.makeperson,
:OLD.maketime,
:OLD.spperson,
:OLD.sptime,
:OLD.spstatus,
:OLD.dealresult,
:OLD.makepername,
SYSDATE, -- 数据交换时间
M_ACTION); -- 此条数据表示的操作动作:1:新增 2:修改 3:删除
ELSE DZJC_UP_N4.PRO_T_BANJIE(:NEW.ywlsh,
:NEW.sjbbh,
:NEW.spsxbh,
:NEW.spsxzxbh,
:NEW.yxtywlsh,
:NEW.bjjgzzjgdm,
:NEW.bjjgmc,
:NEW.bjsj,
:NEW.bjjg,
:NEW.bljgms,
:NEW.zfthyy,
:NEW.zjmc,
:NEW.zjbh,
:NEW.zjyxqx,
:NEW.fzdw,
:NEW.sfje,
:NEW.jedwdm,
:NEW.bz,
:NEW.byzda,
:NEW.byzdb,
:NEW.byzdc,
:NEW.byzdd,
:NEW.status,
:NEW.inputmode,
:NEW.net,
:NEW.makeperson,
:NEW.maketime,
:NEW.spperson,
:NEW.sptime,
:NEW.spstatus,
:NEW.dealresult,
:NEW.makepername,
SYSDATE, -- 数据交换时间
M_ACTION); -- 此条数据表示的操作动作:1:新增 2:修改 3:删除
END IF;
end TRIG_JC_XZXK_BANJIE;编译报错:
ORA-01001: 无效的游标
ORA-06512: 在 "DZJC_UP_N4.PRO_T_BANJIE", line 39
ORA-06512: 在 "DZJC.TRIG_JC_XZXK_BANJIE", line 84
ORA-04088: 触发器 'DZJC.TRIG_JC_XZXK_BANJIE' 执行过程中出错