看楼主的意思是要返回一个记录集,那就要使用游标变量了。 1、在存储过程中定义一个游标变量 2、然后用select具体的select,我试着写写,共同进步: select A.cusidt,B.Q_CLCAMTC_C,B.Q_clctel from tr01r A,lq14r B where A.clcnum=B.Q_CLCNUM and A.NORGIDT in ( 'LAAE', 'LAAB') and A.CHDATE='200409' and A.BAKCDE='8820' and B.Q_APCODE1 in ('010002', '010010')
qiaozhiwei(乔)可以给一个~不用select直接得到结果~用游标和变量做的例子么?应为实际用的时候 select写的超长了~用不了~~~我可以粘出来看一下~~ select rownum as 序号, g.* from(select lq11r.q_cusnam AS 企业名称,lq11r.NORGIDT as 所属分行,c.* from(select f.id,f.hangye as 所属行业,round((f.allrmb/10000),0) as 贷款总余额,round((f.allshun/10000),0) as 不良贷款余额 from(select e.id,e.allrmb,e.allshun,e.hy,hydaima.zwname as hangye from(select d.*,lq11r.q_busty1 as hy from(select d1.id,(d2.usdtormb+d1.rmb) as allrmb, (d2.usdshun+d1.rmbshun) as allshun from(select s2.id, nvl(usdtormb,0) as usdtormb , nvl((uciDai1+uciDai2+uciDai3),0) as usdshun from (SELECT DISTINCT q_cusidt AS id FROM lq14r) s2, (SELECT Q_CUSIDT AS id, SUM(Q_OSDAMTC_C) AS usdall, SUM(Q_OSDAMTC_C * q_usdcrt / 100) AS usdtormb, --查找出所有非人民币的不良贷款(以客户号分组) SUM(decode(Q_AUDCLA, '2', Q_OSDAMTC_C * q_usdcrt / 100, 0)) AS uciDai1, --Q-AUDCLA结果分类:2次级,3可疑,4损失 SUM(decode(Q_AUDCLA, '3', Q_OSDAMTC_C * q_usdcrt / 100, 0)) AS uciDai2, SUM(decode(Q_AUDCLA, '4', Q_OSDAMTC_C * q_usdcrt / 100, 0)) AS uciDai3 FROM LQ14R WHERE Q_curcde <> '001' AND q_clcnum IN --查找tr04r表中产品码PRDCDE为LAAA,LAAB,LAAD,LAAE,LAAH 的客户合同号 (SELECT clcnum FROM tr04r WHERE PRDCDE IN ('LAAA', 'LAAB', 'LAAD','LAAE','LAAH')) and substr(chdate,1,6) = '200409' GROUP BY Q_cusidt ORDER BY Q_cusidt)b where s2.id = b.id(+))d2, (select s1.id , nvl(a.rmball,0) as rmb , nvl((ciDai1+ciDai2+ciDai3),0) as rmbshun from(SELECT DISTINCT q_cusidt AS id --查找出所有人民币的不良贷款(以客户号分组) FROM lq14r) s1, (SELECT Q_CUSIDT AS id, SUM(Q_OSDAMTC_C) AS rmball, SUM(decode(Q_AUDCLA, '2', Q_OSDAMTC_C, 0)) AS ciDai1, SUM(decode(Q_AUDCLA, '3', Q_OSDAMTC_C, 0)) AS ciDai2, SUM(decode(Q_AUDCLA, '4', Q_OSDAMTC_C, 0)) AS ciDai3 FROM LQ14R WHERE Q_curcde = '001' AND q_clcnum IN (SELECT clcnum FROM tr04r WHERE PRDCDE IN ('LAAA', 'LAAB', 'LAAD','LAAE','LAAH')) and substr(chdate,1,6) = '200409' GROUP BY Q_cusidt ORDER BY Q_cusidt)a where s1.id = a.id(+))d1 where d1.id = d2.id)d,lq11r where lq11r.q_cusidt=d.id and substr(lq11r.chdate,1,6) = '200409')e,hydaima --通过与lq11r.q_cusidt=d.id关联取得满足要求的客户的行业代码 where e.hy=trim(hydaima.q_buscde))f --通过e.hy=trim(hydaima.q_buscde的关联~得到行业名称 ORDER BY allshun DESC)c,lq11r --按照不良贷款总余额排序 where c.id = lq11r.q_cusidt and substr(lq11r.chdate,1,6) = '200409' order by 不良贷款余额 desc)g where rownum < 21
用游标返回一个记录集: DECLARE TYPE TEST_REF_CURSOR IS REF CURSOR; c_CURSOR TEST_REF_CURSOR; BEGIN IF NOT c_CURSOR%ISOPEN THEN OPEN c_CURSOR FOR SELECT 语句; END IF; END;
1、在存储过程中定义一个游标变量
2、然后用select具体的select,我试着写写,共同进步:
select A.cusidt,B.Q_CLCAMTC_C,B.Q_clctel
from tr01r A,lq14r B
where A.clcnum=B.Q_CLCNUM and
A.NORGIDT in ( 'LAAE', 'LAAB') and
A.CHDATE='200409' and A.BAKCDE='8820' and
B.Q_APCODE1 in ('010002', '010010')
select写的超长了~用不了~~~我可以粘出来看一下~~
select rownum as 序号, g.*
from(select lq11r.q_cusnam AS 企业名称,lq11r.NORGIDT as 所属分行,c.*
from(select f.id,f.hangye as 所属行业,round((f.allrmb/10000),0) as 贷款总余额,round((f.allshun/10000),0) as 不良贷款余额
from(select e.id,e.allrmb,e.allshun,e.hy,hydaima.zwname as hangye
from(select d.*,lq11r.q_busty1 as hy
from(select d1.id,(d2.usdtormb+d1.rmb) as allrmb, (d2.usdshun+d1.rmbshun) as allshun
from(select s2.id, nvl(usdtormb,0) as usdtormb , nvl((uciDai1+uciDai2+uciDai3),0) as usdshun
from (SELECT DISTINCT q_cusidt AS id
FROM lq14r) s2,
(SELECT Q_CUSIDT AS id, SUM(Q_OSDAMTC_C) AS usdall,
SUM(Q_OSDAMTC_C * q_usdcrt / 100) AS usdtormb, --查找出所有非人民币的不良贷款(以客户号分组)
SUM(decode(Q_AUDCLA, '2', Q_OSDAMTC_C * q_usdcrt / 100, 0)) AS uciDai1, --Q-AUDCLA结果分类:2次级,3可疑,4损失
SUM(decode(Q_AUDCLA, '3', Q_OSDAMTC_C * q_usdcrt / 100, 0)) AS uciDai2,
SUM(decode(Q_AUDCLA, '4', Q_OSDAMTC_C * q_usdcrt / 100, 0)) AS uciDai3
FROM LQ14R
WHERE Q_curcde <> '001' AND q_clcnum IN --查找tr04r表中产品码PRDCDE为LAAA,LAAB,LAAD,LAAE,LAAH 的客户合同号
(SELECT clcnum
FROM tr04r
WHERE PRDCDE IN ('LAAA', 'LAAB', 'LAAD','LAAE','LAAH'))
and substr(chdate,1,6) = '200409'
GROUP BY Q_cusidt
ORDER BY Q_cusidt)b
where s2.id = b.id(+))d2,
(select s1.id , nvl(a.rmball,0) as rmb , nvl((ciDai1+ciDai2+ciDai3),0) as rmbshun
from(SELECT DISTINCT q_cusidt AS id --查找出所有人民币的不良贷款(以客户号分组)
FROM lq14r) s1,
(SELECT Q_CUSIDT AS id,
SUM(Q_OSDAMTC_C) AS rmball,
SUM(decode(Q_AUDCLA, '2', Q_OSDAMTC_C, 0)) AS ciDai1,
SUM(decode(Q_AUDCLA, '3', Q_OSDAMTC_C, 0)) AS ciDai2,
SUM(decode(Q_AUDCLA, '4', Q_OSDAMTC_C, 0)) AS ciDai3
FROM LQ14R
WHERE Q_curcde = '001' AND q_clcnum IN
(SELECT clcnum
FROM tr04r
WHERE PRDCDE IN ('LAAA', 'LAAB', 'LAAD','LAAE','LAAH'))
and substr(chdate,1,6) = '200409'
GROUP BY Q_cusidt
ORDER BY Q_cusidt)a
where s1.id = a.id(+))d1
where d1.id = d2.id)d,lq11r
where lq11r.q_cusidt=d.id and substr(lq11r.chdate,1,6) = '200409')e,hydaima --通过与lq11r.q_cusidt=d.id关联取得满足要求的客户的行业代码
where e.hy=trim(hydaima.q_buscde))f --通过e.hy=trim(hydaima.q_buscde的关联~得到行业名称
ORDER BY allshun DESC)c,lq11r --按照不良贷款总余额排序
where c.id = lq11r.q_cusidt and substr(lq11r.chdate,1,6) = '200409'
order by 不良贷款余额 desc)g
where rownum < 21
DECLARE
TYPE TEST_REF_CURSOR IS REF CURSOR;
c_CURSOR TEST_REF_CURSOR;
BEGIN
IF NOT c_CURSOR%ISOPEN THEN
OPEN c_CURSOR FOR SELECT 语句;
END IF;
END;