有下面一张表,有下面这些字段
cardNo、acLogic、AggrCode、srLogic、SourceSystem、ouiLogic、Offshore Unit Indicator
cardNo | acLogin | AggrCode |srLogic |SourceSystem | ouiLogic | Offshore Unit Indicator
1 LIKE 0100100% = FLX = ACU
1 LIKE 9999990% = AX = BU
2 = 00001000 = FLX = ACU
现在要根据这个表的记录组装出来一个SQL语句出来比如对于第一行拼装出来的SQL是
select 'select * from AA t where ' ||
case when AggrCode is not null then 'AggrCode'||acLogin||AggrCode
end
||...................
from BB b
where b.id = 3现在由于cardNo为1的在表里会有多条记录,如果有两条的话,那么就SQL语句就是最终的样子就是
select * from AA t where (1) or (2)如果cardNo为1的有三条件记录的话,那么where条件就有三个or的大项了
select * from AA t where (1) or (2) or (3)
,由于要写一个通用的SQL,
cardNo相同可能有多条记录,这样以来我就得用取出cardNo相同的记录的条数,然后遍历去拼装我的SQL
我想这样以来就必须要用到循环或者游标之类的SQL语句才拼装起来,由于之前没接触这方面东西,现在又得
写,请求大家帮帮忙
cardNo、acLogic、AggrCode、srLogic、SourceSystem、ouiLogic、Offshore Unit Indicator
cardNo | acLogin | AggrCode |srLogic |SourceSystem | ouiLogic | Offshore Unit Indicator
1 LIKE 0100100% = FLX = ACU
1 LIKE 9999990% = AX = BU
2 = 00001000 = FLX = ACU
现在要根据这个表的记录组装出来一个SQL语句出来比如对于第一行拼装出来的SQL是
select 'select * from AA t where ' ||
case when AggrCode is not null then 'AggrCode'||acLogin||AggrCode
end
||...................
from BB b
where b.id = 3现在由于cardNo为1的在表里会有多条记录,如果有两条的话,那么就SQL语句就是最终的样子就是
select * from AA t where (1) or (2)如果cardNo为1的有三条件记录的话,那么where条件就有三个or的大项了
select * from AA t where (1) or (2) or (3)
,由于要写一个通用的SQL,
cardNo相同可能有多条记录,这样以来我就得用取出cardNo相同的记录的条数,然后遍历去拼装我的SQL
我想这样以来就必须要用到循环或者游标之类的SQL语句才拼装起来,由于之前没接触这方面东西,现在又得
写,请求大家帮帮忙
2、10g以上可以使用wm_concat函数来聚合字段值(多行并一行)。
3、如果要想得现成的存储过程,给出建表、插入数据的脚本和想要的结果详细描述。
v_count NUMBER:= 0;
CURSOR get_cardNo IS
SELECT DISTINCT cardNo
FROM table_name;
CURSOR get_aggrCode(lv_cardNo IN VARCHAR2) IS
SELECT cardNo, aggrCode
FROM table_name
WHERE cardNo = lv_cardNo;
BEGIN
v_str := 'select * from table_name t';
FOR v_cardNo IN get_cardNo
LOOP
FOR record1 IN get_aggrCode(v_cardNo) LOOP
IF record1.aggrCode IS NOT NULL THEN
v_count = v_count + 1;
IF v_count = 1 THEN
v_str = v_str || ' where aggrCode = '|| record1.aggrCode;
ELSE
v_str = v_str || ' or aggrCode = '|| record1.aggrCode;
END IF;
END IF
END LOOP;
END LOOP; ......
END
大侠们,帮看看这个,求教了
http://topic.csdn.net/u/20101130/21/a01e1625-9ca2-4a38-b74c-2a04dc79d0b3.html?63300
best wishes !
type type_record is record(
k_je number,
k_bs number,
k_pzh varchar2(2000)
);
type_record_user type_record; type type_array is table of type_record_user%type index by binary_integer;
var_array type_array; select k_jf,1,b.k_pzzj bulk collect into var_array from gl_pz a,gl_pzx b where a.k_pzid=b.k_pzzj and a.k_pzzbm=2 and b.k_dfkm like '1001%' and (substr(b.k_kmdm,1,4) not in ('2211','1002') and b.k_kmdm not in ('66029001','66029002')) and b.k_kjnd=cur_year and b.k_kjqj=to_number(cur_month) and b.k_dwdm=dwbh;
for i in 1..var_array.count loop
if (var_array(i).k_je>1000) then
je:=je+var_array(i).k_je;
bs:=bs+var_array(i).k_bs;
sTemp:=sTemp||var_array(i).k_pzh||',';
end if;
end loop;