样本数据-“在执行包含更新和插入语句的存储过程时发出" [英] Sample data - "Issue while executing stored procedure which consists both update and insert statements"
问题描述
下面是我在执行包含更新和插入语句的存储过程时发出的问题"中提出的问题的示例表和文件详细信息.下面是执行该过程之前要执行的步骤.
Below are the sample table and file details for the question which I have asked on "Issue while executing stored procedure which consists both update and insert statements". Below are the steps I am following before executing the Procedure.
-
我将从供应商处获取一个文件，其中包含以下格式的数据.
I will get a file from the Vendor which contains the data in the below format.
6437,,01/01/2017,3483.92,,
14081,,01/01/2017,8444.23,,
我正在将这些数据加载到表NMAC_PTMS_NOTEBK_SG中.在上面的文件中，第一列将是资产.
I am loading these data to the table NMAC_PTMS_NOTEBK_SG. In the above file 1st column will be the asset.
我正在使用该资产的名称为lse_id的额外列更新该表.现在NMAC_PTMS_NOTEBK_SG表将具有以下格式的数据.
I am updating the table with extra column with name lse_id with respect to that asset. Now the NMAC_PTMS_NOTEBK_SG table will have the data in the below format.
LSE_ID AST_ID PRPRTY_TAX_DDCTN_CD LIEN_DT ASES_PRT_1_AM ASES_PRT_2_AM
5868087 5049 Null 01-01-2017 3693.3 NULL
现在，我的过程将开始.在我的过程中，逻辑应采用以下方式:我需要从NMAC_PTMS_NOTEBK_SG中获取lse_id并在MJL表(此处为lse_id = app_lse_s)中进行比较.下面是MJL表的结构.
Now my procedure will start. In my procedure the logic should be in a way I need to take the lse_id from NMAC_PTMS_NOTEBK_SG and compare the same in MJL table (here lse_id = app_lse_s). Below is the structure for MJL table.
CREATE TABLE LPR_LP_TEST.MJL
(
APP_LSE_S CHAR(10 BYTE) NOT NULL,
DT_ENT_S TIMESTAMP(3) NOT NULL,
DT_FOL_S TIMESTAMP(3),
NOTE_TYPE_S CHAR(4 BYTE) NOT NULL,
PRCS_C CHAR(1 BYTE) NOT NULL,
PRIO_C CHAR(1 BYTE) NOT NULL,
FROM_S CHAR(3 BYTE) NOT NULL,
TO_S CHAR(3 BYTE) NOT NULL,
NOTE_TITLE_S VARCHAR2(41 BYTE) NOT NULL,
INFO_S VARCHAR2(4000 BYTE),
STAMP_L NUMBER(10) NOT NULL,
PRIVATE_C CHAR(1 BYTE),
LSE_ACC_C CHAR(1 BYTE),
COL_STAT_S CHAR(4 BYTE),
INFO1_S VARCHAR2(250 BYTE),
INFO2_S VARCHAR2(250 BYTE),
INFO3_S VARCHAR2(250 BYTE),
INFO4_S VARCHAR2(250 BYTE),
NTBK_RSN_S CHAR(4 BYTE)
)
TABLESPACE LPR_LP_TEST
PCTUSED 0
PCTFREE 25
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX LPR_LP_TEST.MJL_IDX0 ON LPR_LP_TEST.MJL
(APP_LSE_S, DT_ENT_S)
LOGGING
TABLESPACE LPR_LP_TEST
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER LPR_LP_TEST."MT_MJL_AIUD"
AFTER INSERT OR UPDATE OR DELETE ON mjl
BEGIN
mpkg_trig_mjl.mp_mjl_aiud;
END mt_mjl_aiud;
/
CREATE OR REPLACE TRIGGER LPR_LP_TEST."MT_MJL_AIUDR"
AFTER INSERT OR UPDATE OR DELETE ON mjl FOR EACH ROW
BEGIN
mpkg_trig_mjl.mp_mjl_aiudr (INSERTING, UPDATING, DELETING,
:NEW.app_lse_s, :NEW.prcs_c, :NEW.note_type_s,
:OLD.app_lse_s, :OLD.prcs_c, :OLD.note_type_s);
END mt_mjl_aiudr;
/
CREATE OR REPLACE TRIGGER LPR_LP_TEST."MT_MJL_BIUD"
BEFORE INSERT OR UPDATE OR DELETE ON mjl
BEGIN
mpkg_trig_mjl.mp_mjl_biud;
END mt_mjl_biud;
/
CREATE OR REPLACE TRIGGER LPR_LP_TEST."MT_MJL_OBIUR"
BEFORE INSERT OR UPDATE ON mjl FOR EACH ROW
BEGIN
IF INSERTING THEN
:NEW.stamp_l := mpkg_util.mp_time_ticker;
ELSE
IF :OLD.stamp_l > 999999990 THEN
:NEW.stamp_l := 1;
ELSE
:NEW.stamp_l := :OLD.stamp_l + 1;
END IF;
END IF;
END mt_mjl_obiur;
/
下面是您在上一篇文章中提供的过程，对我来说几乎可以正常工作.
Below is the procedure I am using which you have provided in previous post and it is almost working good for me.
CREATE OR REPLACE PROCEDURE LPR_LP_TEST.SP_PTMS_NOTES
(
p_app_lse_s IN mjl.app_lse_s%TYPE,
--p_dt_ent_s IN mjl.dt_ent_s%TYPE,
--p_note_type_s IN mjl.note_type_s%TYPE,
--p_prcs_c IN mjl.prcs_c%TYPE,
--p_prio_c IN mjl.prio_c%TYPE,
--p_note_title_s IN mjl.note_title_s%TYPE,
--p_info1_s IN mjl.info1_s%TYPE,
--p_info2_s IN mjl.info2_s%TYPE
)
AS
--v_rowcount_i number;
--v_lien_date mjl.info1_s%TYPE;
--v_lien_date NMAC_PTMS_NOTEBK_SG.LIEN_DT%TYPE;
--v_asst_amount mjl.info2_s%TYPE;
v_app_lse_s mjl.app_lse_s%TYPE;
BEGIN
v_app_lse_s := trim(p_app_lse_s);
-- I hope this dbms_output line is for temporary debug purposes only
-- and will be removed in the production version!
dbms_output.put_line(app_lse_s);
merge into mjl tgt
using (select lse_s app_lse_s,
sysdate dt_ent_s,
'SPPT' note_type_s,
'Y' prcs_c,
'1' prio_c,
'Property Tax Assessment' note_title_s,
lien_dt info1_s,
ases_prt_1_am info2_s
from nmac_ptms_notebk_sg
where lse_id = v_app_lse_s) src
on (trim(tgt.app_lse_s) = trim(src.app_lse_s))
-- and tgt.dt_ent_s = src.dt_ent_s)
when matched then
update set --tgt.dt_ent_s = src.dt_ent_s,
tgt.note_title_s = src.note_title_s,
tgt.info1_s = src.info1_s,
tgt.info2_s = src.info2_s
where --tgt.dt_ent_s != src.dt_ent_s
tgt.note_title_s != src.note_title_s
or tgt.info1_s != src.info1_s
or tgt.info2_s != src.info2_s
when not matched then
insert (tgt.app_lse_s,
tgt.dt_ent_s,
tgt.note_type_s,
tgt.prcs_c,
tgt.prio_c,
tgt.from_s,
tgt.to_s,
tgt.note_title_s,
tgt.info1_s,
tgt.info2_s)
values (src.app_lse_s,
src.dt_ent_s,
src.note_type_s,
src.prcs_c,
src.prio_c,
src.from_s,
src.to_s,
src.note_title_s,
src.info1_s,
src.info2_s);
commit;
end;
- 现在逻辑应该是我需要从我传递的文件中传递
lse_id已经保存到过程中了. - 如果我要传递的
lse_id与中的app_lse_s匹配mjl表，然后我需要更新该行和一些已编码的 我正在修正的字段. - 如果
lse_id不匹配，那么我必须为此插入新行 租约和硬编码字段. - 我所面临的问题是
mjl表中的dt_ent_s唯一约束.
- Now the logic should be I need to pass
lse_idfrom the file which I have already saved to the procedure. - If the
lse_idwhich I am passing is matching with theapp_lse_sin themjltable then I need to update that row and some of the harcoded fields which I am doing it correclty. - If the
lse_idis not matching then I have to insert a new row for that lease and the hardcoded fields. - The issue which I am facing is the
dt_ent_sin themjltable is a unique constraint.
请告诉我以上内容是否对您有意义...
Please let me know if the above is making any sense to you...
推荐答案
我面临的问题是mjl表中的dt_ent_s是唯一的约束."
"The issue which I am facing is the dt_ent_s in the mjl table is a unique constraint."
实际上不是，它是复合唯一键的一部分.因此，实际上您的ON子句应该在
Actually it's not, it's part of a compound unique key. So really your ON clause should match on
on (tgt.app_lse_s = src.app_lse_s
and tgt.dt_ent_s = src.dt_ent_s)
顺便说一句，在ON子句中使用trim()令人担忧，尤其是trim(tgt.app_lse_s).如果您要插入带有尾部或前导空格的值，则在修剪它们时，唯一键"将产生多个匹配.从文件加载数据并在表中插入修剪后的值时，应修剪空格.
Incidentally, the use of trim() in the ON clause is worrying, especially trim(tgt.app_lse_s). If you're inserting values with trailing or leading spaces your "unique key" will produce multiple hits when you trim them. You should trim the spaces when you load the data from the file and insert trimmed values in your table.
"ORA-00001:违反了唯一约束(LPR_LP_TEST.MJL_IDX0)"
"ORA-00001: unique constraint (LPR_LP_TEST.MJL_IDX0) violated"
MJL_IDX0必须是唯一索引.这意味着您在考虑唯一记录时需要包括其列.
MJL_IDX0 must me a unique index. That means you need to include its columns in any consideration of unique records.
很明显，直接的INSERT逻辑和MERGE INSERT逻辑之间存在差异.您需要比较这两个语句并找出区别.
Clearly there is a difference between your straight INSERT logic and your MERGE INSERT logic. You need to compare the two statements and figure out what the difference is.
这篇关于样本数据-“在执行包含更新和插入语句的存储过程时发出"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
