才开始学习存储过程。如下sql如何改成存储过程并返回最后的结果集

[hb79924]

{set isolation to dirty read;
select b.claimno,caseno,notifydate,deptgroup,insurantname,telephone,mobile,manageunit,
(case when b.lawstatus <> '0' then '诉讼案件' when b.replevystatus <> '0' then '追偿案件' when b.repaystatus <> '0' then '补赔案件' else
(case when b.endno is not null and b.endno <> '' then '已归档' else
(case when b.endstatus = '2' then '注销完成' when b.endstatus = '3' then '拒赔完成' when b.endstatus = '5' then '作废案件' when b.endstatus = '4' then '已结案' else
(case when b.ifrefuse = '1' then '拒赔处理中' when b.ifrefuse = '2' then '注销处理中' else
(case when c.notify <> '2' then '报案抄单中' else
(case when c.dispatchSurvey in ('0','1','3') then '查勘调度中' else
(case when c.estimate in ('0','1','3') then '查勘估损中' else
(case when c.surveyreport in ('0','1','3') then '查勘估损中' else
(case when c.auditPrice in ('0','1','3') then '本地核价中' else
(case when c.lhauditprice in ('0','1') then '总公司核价中' else
(case when c.voucher = '1' then '单证收集中' else
(case when c.pay in ('0','1','3') then '理算缮制中' else
(case when c.checkPay in ('0','1','3') then '理算复核中' else
(case when c.auditing in ('0','1','3') then '本地核赔中' else
(case when c.lhauditing in ('0','1') then '总公司核赔中' else '未知状态'
end) end) end) end) end) end) end) end) end) end) end) end) end) end) end) as claim_status
from c_claim b,c_processstatus c
where b.claimno=c.claimno
and endstatus='0'
into temp a with no log;
select unique a.*,losstype
from a a,outer c_lossitem b
where a.caseno=b.caseno
and losstype='2'
into temp b with no log;
select a.*,max(dispatchtime) dispatchtime
from b a,outer c_surveytask b
where a.caseno=b.caseno
and tasktype='2'
group by 1,2,3,4,5,6,7,8,9,10
into temp c with no log;
select a.*,surveydeptgroup,surveydept,surveyor1code
from c a,outer c_surveytask b
where a.caseno=b.caseno
and taskserialno='1'
into temp d with no log;
select a.*,max(endtime) endtime
from d a,outer c_surveytask b
where a.caseno=b.caseno
and a.dispatchtime=b.dispatchtime
and tasktype='2'
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14
into temp e with no log;
select a.*,sum(gsje) gsje
from e a,outer datacenter@onlinegztb1:estiloss b
where claimno=b.caseno
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
into temp f with no log;}
select claimno,caseno,notifydate,deptgroup,insurantname,telephone,mobile,manageunit,claim_status,losstype,dispatchtime,surveydeptgroup,surveydept,staff_name,endtime,sum(gsje) gsje
from f a,outer ids6@pguz:rydm_t b
where surveyor1code=staff_code
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
into temp g with no log;
select a.*,jbr
from g a,outer datacenter@onlinegztb1:regcase b
where claimno=b.caseno
into temp h with no log;
select a.*,b.staff_name staff_name1
from h a,outer ids6@pguz:rydm_t b
where jbr=staff_code
into temp i with no log;
select a.*,allbilltime
from i a,outer c_property b
where a.claimno=b.claimno
and ifallbill='1'
into temp j with no log;
select a.*,max(finishtime) finishtime
from j a,outer c_taskaudit b
where a.claimno=b.claimno
and status='4'
and resultcode='1'
and audittype='7'
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
into temp k with no log;
select a.*,max(b.finishtime) finishtime1
from k a,outer c_taskaudit b
where a.claimno=b.claimno
and status='4'
and resultcode='1'
and audittype='3'
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
into temp l with no log;
select a.*,max(b.finishtime) finishtime2
from l a,outer c_taskaudit b
where a.caseno=b.claimno
and status='4'
and resultcode in ('1','B')
and audittype='1'
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
into temp m with no log;
unload to wjmx.txt
select * from m

[hb79924]

补充：就是最后的unload .....中的select是返回结果集。

[阿呆哥]

写个shell script，在此过程中调用。