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;
解决方案 »
- 我要调用sqlplus执行sql脚本,问如何断开当前已经连接的进程
- java动态插入数据问题
- oracle odbc 怎么设置能 支持中文????
- 查询统计求合计问题
- 这个sql为什么用不上索引?
- Oracle 查询日期连续的结果集
- 触发器:A,B两表结构相同,在插入A表记录之后,需要同时插入B表同一条记录,
- 触发器中:old值无法正确读取的问题?
- 怎样修改默认的客户端环境变量?
- oracle数据库自动启动的问题-------解决马上给分!
- oracle 中 insert into a select * from b 不想写字段,实现全表复制?
- pl/sql develope中能否select时显示comments的内容
但是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' 执行过程中出错