我的存储过程中有好多这样的SQL语句,就t.airfare_fltclass这个字段不一样,t.airfare_fltclass有F、C、Y、B这些情况
SELECT r.airfare_ruletype,t.airfare_sid,t.airfare_fltclass
FROM sheb_airfare t LEFT JOIN sheb_rule r on t.airfare_sid = r.airfare_sid
WHERE t.airfare_airfarestatus = 'Y'
AND (t.airfare_fltclass = 'F');怎么把这些值放在一个集合里,然后循环SQL
SELECT r.airfare_ruletype,t.airfare_sid,t.airfare_fltclass
FROM sheb_airfare t LEFT JOIN sheb_rule r on t.airfare_sid = r.airfare_sid
WHERE t.airfare_airfarestatus = 'Y'
AND (t.airfare_fltclass = 'F');怎么把这些值放在一个集合里,然后循环SQL
WHERE t.airfare_airfarestatus = 'Y'
AND t.airfare_fltclass in (
'F','C','Y','B')
问题是这样的:SELECT cp.* FROM
(SELECT *
FROM
(SELECT r.airfare_ruletype,t.airfare_sid,t.airfare_fltclassFROM sheb_airfare t LEFT JOIN sheb_rule r on t.airfare_sid = r.airfare_sid
WHERE t.airfare_airfarestatus = 'Y'
AND (t.airfare_fltclass = 'F')
ORDER BY TO_NUMBER(r.airfare_endflightno)-TO_NUMBER(r.airfare_startflightno) ASC,t.comn_dtcreatedatetime DESC,t.airfare_favticketprice ASC)
WHERE ROWNUM = 1
UNION ALLSELECT *
FROM
(SELECT r.airfare_ruletype,t.airfare_sid,t.airfare_fltclassFROM sheb_airfare t LEFT JOIN sheb_rule r on t.airfare_sid = r.airfare_sid
WHERE t.airfare_airfarestatus = 'Y'
AND (t.airfare_fltclass = 'C')
ORDER BY TO_NUMBER(r.airfare_endflightno)-TO_NUMBER(r.airfare_startflightno) ASC,t.comn_dtcreatedatetime DESC,t.airfare_favticketprice ASC)
WHERE ROWNUM = 1UNION ALL
)cp;在一存储过程中,怎么才能写一个子查询语句来简化此sql语句.
然后 循环sql 每次取到不同的值?
CURSOR C IS select t.airfare_fltclass from sheb_airfare ;
v_air_flt t.sheb_airfare %type;BEGIN
open c;
fetch c into v_air_flt
exit when c%NOTFOUND;
LOOP
SELECT *
FROM
(SELECT r.airfare_ruletype,t.airfare_sid,t.airfare_fltclassFROM sheb_airfare t LEFT JOIN sheb_rule r on t.airfare_sid = r.airfare_sid
WHERE t.airfare_airfarestatus = 'Y'
AND (t.airfare_fltclass = v_air_flt)
ORDER BY TO_NUMBER(r.airfare_endflightno)-TO_NUMBER(r.airfare_startflightno) ASC,t.comn_dtcreatedatetime DESC,t.airfare_favticketprice ASC)
WHERE ROWNUM = 1
END LOOP;
CLOSE C;
END;我也刚学会写存储过程 可能有些语法或者什么错误 大概就是个思路吧
也不知道这样写对不对哈 (*^__^*)