为插入到oracle中无效表中的每个无效记录添加无效备注 [英] Add invalid remarks for each invalid records inserted into the invalid table in oracle
问题描述
我有一个cursor，它根据多个条件将有效数据插入有效表中，并将无效数据插入无效表中.下面是我的光标逻辑.
I have a cursor which inserts the valid data into valid table and invalid data into invalid table based on several criteria. Below is my cursor logic for the same.
create or replace PROCEDURE FIP_VAL_INV_DATA AS
l_state_name r4g_lb.mantainenceboundary_evw.jiostatename%type;
l_maint_zone_code r4g_lb.mantainenceboundary_evw.maintenancezonecode%type;
l_maint_zone_name r4g_lb.mantainenceboundary_evw.maintenancezonename%type;
l_state_code r4g_lb.mantainenceboundary_evw.jiostatecode%type;
begin
for cur_r in (select rj_span_id,
rj_maintenance_zone_name,
rj_maintenance_zone_code,
rj_state_name,
rj_network_category,
rj_network_type,
rj_construction_methodology,
inventory_status_code,
rj_route_name,
rj_intracity_link_id,
calculated_length
from app_fttx.transmedia@sat
where --rownum < 100 and
jumper_flag is null
)
loop
select max(jiostatename)
into l_state_name
from r4g_lb.mantainenceboundary_evw
where jiostatename = cur_r.rj_state_name
and rownum = 1;
--dbms_output.put_line('test');
select max(maintenancezonecode), max(maintenancezonename)
into l_maint_zone_code, l_maint_zone_name
from r4g_lb.mantainenceboundary_evw
where maintenancezonecode = cur_r.rj_maintenance_zone_code
and maintenancezonename = cur_r.rj_maintenance_zone_name
and rownum = 1;
if length(cur_r.rj_span_id) = '21'
and cur_r.inventory_status_code = 'IPL'
and regexp_like(cur_r.rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$')
and NVL(INSTR(cur_r.RJ_INTRACITY_LINK_ID, '_'), 1) > 0
and cur_r.rj_maintenance_zone_code = l_maint_zone_code
and cur_r.rj_maintenance_zone_name = l_maint_zone_name
and cur_r.rj_state_name = l_state_name
then
begin
INSERT INTO tbl_fiber_valid_trans_data
(span_id, maintenance_zone_name, maintenance_zone_code, r4g_state_name, inventory_status_code, network_category, network_type, construction_methodology, route_name,intracity_link_id, calculated_length, last_updated_by)
values
(cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code, cur_r.rj_state_name, cur_r.inventory_status_code, cur_r.rj_network_category, cur_r.rj_network_type, cur_r.rj_construction_methodology, cur_r.rj_route_name, cur_r.rj_intracity_link_id, cur_r.calculated_length, 'Test');
end;
elsif LENGTH(cur_r.rj_intracity_link_id) > 8
AND LENGTH(cur_r.rj_intracity_link_id) < 21
and cur_r.inventory_status_code = 'IPL'
and cur_r.rj_maintenance_zone_code = l_maint_zone_code
and cur_r.rj_maintenance_zone_name = l_maint_zone_name
and cur_r.rj_state_name = l_state_name
then
begin
INSERT INTO tbl_fiber_valid_trans_data
(span_id, maintenance_zone_name, maintenance_zone_code, r4g_state_name, inventory_status_code, network_category, network_type, construction_methodology, route_name,intracity_link_id, calculated_length, last_updated_by)
values
(cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code, cur_r.rj_state_name, cur_r.inventory_status_code, cur_r.rj_network_category, cur_r.rj_network_type, cur_r.rj_construction_methodology, cur_r.rj_route_name, cur_r.rj_intracity_link_id, cur_r.calculated_length, 'Test');
end;
else begin
INSERT INTO TBL_FIBER_INVALID_TRANS_DATA
(span_id, maintenance_zone_name, maintenance_zone_code, r4g_state_name, inventory_status_code, network_category, network_type, construction_methodology, route_name,intracity_link_id, calculated_length, last_updated_by, remarks)
values
(cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code, cur_r.rj_state_name, cur_r.inventory_status_code, cur_r.rj_network_category, cur_r.rj_network_type, cur_r.rj_construction_methodology, cur_r.rj_route_name, cur_r.rj_intracity_link_id, cur_r.calculated_length, 'Test', dynamic remarks based upon the error);
end;
end if;
end loop;
END FIP_VAL_INV_DATA;
所以现在我想要的是，如果一行无效并被插入到无效表中，即TBL_FIBER_INVALID_TRANS_DATA.我还想插入注释，因为什么原因导致记录无效.
So now what I want is, If one row is invalid and gets inserted into the invalid table i.e TBL_FIBER_INVALID_TRANS_DATA. I want also to insert the remarks for what reason the record is invalid.
出于其他原因，我有上述几种情况，
For adding reason, i have several conditions mentioned above like,
if length(cur_r.rj_span_id) = '21'假定长度不等于21.我们可以添加备注作为The length is not equal to 21 characters.
if length(cur_r.rj_span_id) = '21' suppose the length is not equal to 21. We can add remarks as The length is not equal to 21 characters.
像这样，我们可以在无效表的备注"列中动态添加多个备注.
Like this we can add several remarks in remarks column of invalid table dynamically.
请建议如何实现.
更新
推荐答案
为每个游标记录定义一个变量l_invalid_reason.分别执行每个验证，如果验证失败，则将相应的消息写入l_invalid_reason.
For each cursor record, define a variable l_invalid_reason. Perform each validation individually and, if one fails, write the appropriate message into l_invalid_reason.
然后，如果l_invalid_reason是null，则将其插入有效"表中.否则，插入到无效"表中，并在remarks列中传入l_invalid_reason.这是执行此操作的代码的精简版本，带有注释.
Then, if l_invalid_reason is null, insert it into the "valid" table. Otherwise, insert into the "invalid" table, passing l_invalid_reason in for the remarks column. Here is an abridged version of your code that does this, with comments.
create or replace PROCEDURE FIP_VAL_INV_DATA AS
begin
-- Open your cursor loop
for cur_r in (select ... ) loop
declare
l_invalid_reason VARCHAR2(100);
begin
-- Do various select into commands to get extra data needed (e.g. l_state_name, l_maint_zone_code, and l_maint_zone_name
select max(jiostatename) into l_state_name...
...
...
-- Do each validation individually
-- Notice you are checking for INVALID conditions, not VALID ones, so the
-- logic is reversed from your posting and you may need to account for possible nulls,
-- as in some of the examples below.
if length(cur_r.rj_span_id) != 21 then
l_invalid_reason := 'Length is not 21 characters exactly';
else if nvl(cur_r_inventory_status_code,'XXX') != 'IPL' then
l_invalid_reason := 'Inventory status code is not IPL';
else if not regexp_like(cur_r.rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$') then
l_invalid_reason := 'Span ID does not match accepted format';
else if .... (additional validations) ...
end if;
-- Check to see whether there is an invalid reason
if l_invalid_reason is null then
-- insert into good table
else
-- insert into invalid table
INSERT INTO TBL_FIBER_INVALID_TRANS_DATA
(span_id,
maintenance_zone_name,
maintenance_zone_code,
...
remarks)
values
(cur_r.rj_span_id,
cur_r.rj_maintenance_zone_name,
cur_r.rj_maintenance_zone_code,
...
l_invalid_reason); -- put the PL/SQL variable holding the invalid reason into the INSERT statement
end if;
end;
end loop;
END FIP_VAL_INV_DATA;
这篇关于为插入到oracle中无效表中的每个无效记录添加无效备注的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
