oracle plsql动态查询语句过程问题，求解解决

[qq_35253687]

如何取出filter_code列中的表达式，当表达式全部满足的时候返回‘0’，失败的时候把表达式的内容给全部返回出去，存在另一张表中，并逐条插入失败原因，返回结果，看那个条件有问题，示例如下
/**
case when user_status_id=1
and x_cust_count=1
and call_duration_nofree>10
and arrear_all_fee<1
and (z_call_count>1 or bill_cust_count_last=1) and user_status_id=1
and x_cust_count=1 and arrear_all_fee<1
and (z_call_count>1 or bill_cust_count_last=1) then '0' else '1' end

*/

我自己写的代码请大神看一下是否正确，没思路了，谢谢

create or replace procedure sp_ceshi_case_lmx

is
--声明变量
v integer;
v_sql varchar2(1000);

begin

for v in (select to_number(t.condition_id)condition_id,t.rule_id,t.filter_code
from TB_EW_M_PAY_CONDIION_bak t
group by to_number(t.condition_id) ,t.rule_id,t.filter_code
order by to_number(t.condition_id) ,t.rule_id,t.filter_code)

loop

v_sql:=v_sql||v.filter_code ||' ' ;


end loop;

execute immediate v_sql;
/**往临时表中插入数据*/
insert into LMX_TB_EW_M_PAY_CONDIION
(
rule_id,
filter_name
)
/**success 返回值'0',fail 返回值 '1'*/
select
rule_id,
case when user_status_id=1
and x_cust_count=1
and call_duration_nofree>10
and arrear_all_fee<1
and (z_call_count>1 or bill_cust_count_last=1) and user_status_id=1
and x_cust_count=1 and arrear_all_fee<1
and (z_call_count>1 or bill_cust_count_last=1) then '0' else v_sql end



from TB_EW_M_PAY_CONDIION_bak;

dbms_output.put_line(v_sql);

end sp_ceshi_case_lmx;

[嘚嗒搬运工]

试试这个看，看我理解的对不对

Select t.Rule_Id,
       Listagg(t.Filter_Code, '') Within Group(Order By t.Condition_Id)
  From Tb_Ew_m_Pay_Condiion_Bak t
 Group By t.Rule_Id;

[qq_35253687]

这个是成功时的例子，所有语句插入表中一个字段，失败的时候要拆分开，一个语句插入一行，假如成功的连接条件是5条，一旦语句执行失败了，就往新表中插入这五条数据，一个条件一行数据，怎么破，大牛，求解

[嘚嗒搬运工]

引用 2 楼 qq_35253687 的回复:

这个是成功时的例子，所有语句插入表中一个字段，失败的时候要拆分开，一个语句插入一行，假如成功的连接条件是5条，一旦语句执行失败了，就往新表中插入这五条数据，一个条件一行数据，怎么破，大牛，求解

看你截图像是以Rule_Id进行分组处理。过程中通过Rule_Id进行循环，执行失败就直接通过Rule_Id从B_EW_M_PAY_CONDIION_bak表中取数据插入新表； 至于取数逻辑看你截图猜测 1.如果Is_Must为1,filter_code取Operatino_Code为1的值（And） 2.如果Is_Must为0,filter_code取Operatino_Code为1的值（Or）。

[嘚嗒搬运工]

引用 3 楼 A_A_apan 的回复:

[quote=引用 2 楼 qq_35253687 的回复:] 这个是成功时的例子，所有语句插入表中一个字段，失败的时候要拆分开，一个语句插入一行，假如成功的连接条件是5条，一旦语句执行失败了，就往新表中插入这五条数据，一个条件一行数据，怎么破，大牛，求解

看你截图像是以Rule_Id进行分组处理。过程中通过Rule_Id进行循环，执行失败就直接通过Rule_Id从B_EW_M_PAY_CONDIION_bak表中取数据插入新表； 至于取数逻辑看你截图猜测 1.如果Is_Must为1,filter_code取Operatino_Code为1的值（And） 2.如果Is_Must为0,filter_code取Operatino_Code为1的值（Or）。 [/quote] 如果没有这个规律，你也可以对拼接好后的Listagg(t.Filter_Code, '') Within Group(Order By t.Condition_Id) 进行截取拆分“And” 为截取点

[qq_35253687]

这个是我写的脚本，请帮忙看一下，怎么修改，写不下去了 ，成功条件的语句已经写好了，就差失败的条件怎么插入新表中LMX_TB_EW_M_PAY_CONDIION



我写的脚本


create or replace procedure sp_ceshi_case_lmxAAA

is
--声明变量
v integer;
v_sql varchar2(1000);
v_sql2 varchar2(1000);
v_sql3 varchar2(1000);
exc_error exception;
v_condition_id number;
begin
/** 定义一个游标vv*/
for vv in(select t.rule_id
from TB_EW_M_PAY_CONDIION_bak t
group by t.rule_id
order by t.rule_id)
---------------------------条件执行成功-------------------------------------------------------
loop --循环开始

/**定义表中字段游标v*/
for v in (select to_number(t.condition_id)condition_id,t.rule_id,t.filter_code
from TB_EW_M_PAY_CONDIION_bak t
where t.rule_id=vv.rule_id
group by to_number(t.condition_id) ,t.rule_id,t.filter_code
order by to_number(t.condition_id) ,t.rule_id,t.filter_code)
loop--内部循环开始

v_sql:=v_sql||v.filter_code ||' ' ;
--变量v_sql赋值TB_EW_M_PAY_CONDIION_bak表中filter_code的值
end loop;--内部循环结束


execute immediate 'truncate table LMX_TB_EW_M_PAY_CONDIION'; --清空表中数据

v_sql2:=
'insert into LMX_TB_EW_M_PAY_CONDIION
(
rule_id,
filter_name
)'|| 'values( '''||vv.rule_id||''','||''' case when '||v_sql||
' then 0 else 1 end '''||')';

dbms_output.put_line(v_sql2);--打印输出v_sql2

execute immediate v_sql2; --执行动态sql

end loop;--循环结束
commit;
-----------------------------条件执行失败---------------------------------------------------------------
/**定义表中字段游标vv*/
for vv in(select t.rule_id
from TB_EW_M_PAY_CONDIION_bak t
group by t.rule_id
order by t.rule_id)
loop --循环开始
/**给变量v_condition_id赋值*/
select condition_id into v_condition_id from TB_EW_M_PAY_CONDIION_bak t ;

/**根据变量v_condition_id值给v_sql赋值TB_EW_M_PAY_CONDIION_bak中filter_code的值*/
if v_condition_id=1 then
select t.filter_code into v_sql
from TB_EW_M_PAY_CONDIION_bak t
where condition_id=1;

elsif v_condition_id=3 then
select t.filter_code into v_sql
from TB_EW_M_PAY_CONDIION_bak t
where condition_id=1;

elsif v_condition_id=5 then
select t.filter_code into v_sql
from TB_EW_M_PAY_CONDIION_bak t
where condition_id=1;

elsif
v_condition_id=9 then
select t.filter_code into v_sql
from TB_EW_M_PAY_CONDIION_bak t
where condition_id=1;
end if;


/*
loop

v_sql:=v_sql||t.filter_code ||' ' ;

end loop;*/

execute immediate 'truncate table LMX_TB_EW_M_PAY_CONDIION';--清空表中数据

v_sql3:=
'insert into LMX_TB_EW_M_PAY_CONDIION
(
rule_id,
filter_name
)'|| 'values( '''||vv.rule_id||''','||''' case when '||v_sql||
' then 0 else 1 end '''||')';

dbms_output.put_line(v_sql3);
execute immediate v_sql3;

end loop;
commit;

end sp_ceshi_case_lmxAAA;






主表创建语句


create table TB_EW_M_PAY_CONDIION
(
PAYMENTITEM_ID VARCHAR2(3),
RULE_ID VARCHAR2(100),
CONDITION_ID VARCHAR2(1000),
FILTER_NAME VARCHAR2(1000),
FILTER_CODE VARCHAR2(1000),
FILTERITEM_ID VARCHAR2(10),
OPERATION_CODE VARCHAR2(10),
IS_MUST VARCHAR2(2)
) ;
-- Add comments to the table
comment on table TB_EW_M_PAY_CONDIION
is '酬金规则计提条件保存表';
-- Add comments to the columns
comment on column TB_EW_M_PAY_CONDIION.PAYMENTITEM_ID
is '酬金科目';
comment on column TB_EW_M_PAY_CONDIION.RULE_ID
is '规则ID';
comment on column TB_EW_M_PAY_CONDIION.CONDITION_ID
is '条件序列号';
comment on column TB_EW_M_PAY_CONDIION.FILTER_NAME
is '条件名称';
comment on column TB_EW_M_PAY_CONDIION.FILTER_CODE
is '条件代码';
comment on column TB_EW_M_PAY_CONDIION.FILTERITEM_ID
is '条件ID';
comment on column TB_EW_M_PAY_CONDIION.OPERATION_CODE
is '操作类型1：条件；2：操作符';
comment on column TB_EW_M_PAY_CONDIION.IS_MUST
is '1：必须；0：可选';

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '1', '当月话费＞10元', 'call_duration_nofree>10', '', '1', '1');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '2', '', ' and ', '', '2', '1');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '3', '客户状态是否正常', 'user_status_id=1', '', '1', '1');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '4', '', ' and ', '', '2', '1');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '5', '是否有效客户', 'x_cust_count=1', '', '1', '1');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '6', '', 'and', '', '2', '1');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '7', '欠费金额', 'arrear_all_fee<1', '', '1', '1');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '8', '', 'and', '', '2', '1');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '9', '', '(', '', '2', '0');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '10', '主叫通话次数', 'z_call_count>1 ', '', '1', '0');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '11', '', 'or ', '', '2', '0');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '12', '上月是否计费用户', 'bill_cust_count_last=1', '', '1', '0');

insert into tb_ew_m_pay_condiion (PAYMENTITEM_ID, RULE_ID, CONDITION_ID, FILTER_NAME, FILTER_CODE, FILTERITEM_ID, OPERATION_CODE, IS_MUST)
values ('P01', 'P013710001', '13', '', ')', '', '2', '0');

[嘚嗒搬运工]

引用 5 楼 qq_35253687 的回复:

这个是我写的脚本，请帮忙看一下，怎么修改，写不下去了 ，成功条件的语句已经写好了，就差失败的条件怎么插入新表中LMX_TB_EW_M_PAY_CONDIION

最后还是没看明白，这一串很明显都不是你提供的表里面的字段，判断失败的原则是什么？查询无数据？.....

call_duration_nofree>10  and  user_status_id=1  and  x_cust_count=1 and arrear_all_fee<1 and ( z_call_count>1  or  bill_cust_count_last=1 )

[qq_35253687]

TB_EW_M_PAY_CONDIION_bak这个表中的字段filter_code连接起来有一个不成立及判定失败

[嘚嗒搬运工]

引用 7 楼 qq_35253687 的回复:

TB_EW_M_PAY_CONDIION_bak这个表中的字段filter_code连接起来有一个不成立及判定失败

给个不成立的例子，然后把你想要的结果贴出来！！！