declare
cursor orderTypeId is
select order_type_id from order_type where order_type_id in
(select distinct order_type_id
from business_2_flow_template
where prod_spec_id in ('650020001','650025381'));
cursor areaId is
select distinct area_id
from dynamic_rule
where prod_spec_id in ('650020001','650025381') and area_id like'%00%';
begin
for o in orderTypeId loop
for d in areaId loop
if (o.order_type_id=21538)or(o.order_type_id=21)or(o.order_type_id=7539)or(o.order_type_id=7538) then
insert into dynamic_rule
(area_id,prod_spec_id,order_type_id,tache_ids,seq,rule_string,priority,dynamic_type)
values (d.area_id,'650020001','','DT0735','1','10045='+o.order_type_id,'1','A');
insert into dynamic_rule
(area_id,prod_spec_id,order_type_id,tache_ids,seq,rule_string,priority,dynamic_type)
values (d.area_id,'650025381','','DT0735','1','10045='+o.order_type_id,'1','A');
else
insert into dynamic_rule
(area_id,prod_spec_id,order_type_id,tache_ids,seq,rule_string,priority,dynamic_type)
values (d.area_id,'650020001','','DT0733','1','10045='+o.order_type_id,'1','A');
insert into dynamic_rule
(area_id,prod_spec_id,order_type_id,tache_ids,seq,rule_string,priority,dynamic_type)
values (d.area_id,'650025381','','DT0733','1','10045='+o.order_type_id,'1','A');
end if;
end loop;
end loop;
end;
报错信息如下:
ORA-01722无效数字
考虑到可能是数据类型问题,特给出关键字段数据类型如下:
Name Type Nullable Default Comments
AREA_ID NUMBER(5)
PROD_SPEC_ID NUMBER(9)
ORDER_TYPE_ID NUMBER(6)
ITEM_SPEC_ID NUMBER(10)
NEW_ACTION NUMBER(5) Y
JOB_SPEC_ID VARCHAR2(6)
NEW_VALUE_SET VARCHAR2(2000) Y
OLD_VALUE_SET VARCHAR2(2000) Y
RELA_TYPE CHAR(1) Y
RELA_ITEM_SPEC_ID NUMBER(5) Y
RELA_NEW_VALUE_SET VARCHAR2(200) Y
RELA_OLD_VALUE_SET VARCHAR2(200) Y
RELA_NEW_ACTION NUMBER(5) Y
希望各路神仙施予援手,谢谢!
追加说明:
问题出在begin下面的插入语句中
insert into dynamic_rule
(area_id,prod_spec_id,order_type_id,tache_ids,seq,rule_string,priority,dynamic_type)
values (d.area_id,'650020001','','DT0735','1','10045='+o.order_type_id,'1','A');
你先执行一些这条插入语句,d.area_id,o.order_type_id, 按照你的规则先编一下;
-- '650020001',-- prod_spec_id 应该是这个的问题