纪念项目中写的第一个存储过程

因为项目需要，需要提供存储过程，我之前从来没写过存储过程，真心不会写啊，但是老爷们不能说不，百度一下，给他写一个。

create or replace procedure pro_drgs_weight(starttime varchar2,endtime varchar2,hid number,p_cur out sys_refcursor)
as
  allNum number;
begin
    select count(*) into allNum from t_drgs_grouprecord
    where fouthostime between to_date(''||starttime||' 00:00:00','yyyy/mm/dd hh24:mi:ss') and to_date(''||endtime||' 23:59:59','yyyy/mm/dd hh24:mi:ss');

open p_cur for
select g.fgroupcode,n.fname,nvl(w.fweight,0) fweight,
case when w.fweight>10 then 'RW>10'
     when w.fweight>5 then '5<RW≤10'
     when w.fweight>2 then '2<RW≤5'
     when w.fweight>=1 then '1≤RW<2'
     when w.fweight<0 then '0<RW<1'
     else 'RW=0' end weight,
count(*) groupnum,decode(allNum,0,0,count(*)/allNum) groupPer,avg(fday) avgday,avg(fmoney) avgmoney
from t_drgs_grouprecord g
left join t_drgs_group_name n on g.fgroupcode=n.fcode
left join (select * from t_drgs_group_weight where fsctime is null and fhid=hid) w on g.fgroupcode=w.fgroupid
where g.fgroupstate='1' and g.fouthostime between to_date(''||starttime||' 00:00:00','yyyy/mm/dd hh24:mi:ss')
and to_date(''||endtime||' 23:59:59','yyyy/mm/dd hh24:mi:ss')
group by g.fgroupcode,n.fname,w.fweight;

end pro_drgs_weight;

在写第一个时间那块，错了好长时间，最后瞎写了几个引号，然后竟然编译通过了。

这是在plsql里写的存储过程，不会在plsql里执行，想到sqlplus了，在里边测试一下执行结果；同样的，之前也没用过sqlplus，继续百度，查查怎么用sqlplus执行存储过程。
1.打开命令提示符，输入sqlplus /nolog，回车
2.输入connect username/password@sid，回车
3.输入var r refcursor，回车
4.输入exec pro_drgs_weight(‘2017/01/01’,’2017/01/31’,1,:r)，回车
5.输入print r，回车
6.显示查询结果，完美！

把这个存储过程提供过去了，竟然告诉我他调用不了，需要放到方法里，最好再放到包里，我的天，这不是强人所难么，得了，继续查，继续写吧。

create or replace package pag_drgs_weight is

  type myResult is ref cursor;

  procedure pro_drgs_getWeight(starttime varchar2,endtime varchar2,hid number,p_cur out myResult);

  function fun_drgs_getWeight(starttime varchar2,endtime varchar2,hid number) return myresult;
end pag_drgs_weight;

create or replace package body pag_drgs_weight is

procedure pro_drgs_getWeight(starttime varchar2,endtime varchar2,hid number,p_cur out myResult)
is
  allNum number;
begin
    select count(*) into allNum from t_drgs_grouprecord
    where fouthostime between to_date(''||starttime||' 00:00:00','yyyy/mm/dd hh24:mi:ss')
          and to_date(''||endtime||' 23:59:59','yyyy/mm/dd hh24:mi:ss');

open p_cur for
select g.fgroupcode,n.fname,nvl(w.fweight,0) fweight,
case when w.fweight>10 then 'RW>10'
     when w.fweight>5 then '5<RW≤10'
     when w.fweight>2 then '2<RW≤5'
     when w.fweight>=1 then '1≤RW<2'
     when w.fweight<0 then '0<RW<1'
     else 'RW=0' end weight,
count(*) groupnum,decode(allNum,0,0,count(*)/allNum) groupPer,avg(fday) avgday,avg(fmoney) avgmoney
from t_drgs_grouprecord g
left join t_drgs_group_name n on g.fgroupcode=n.fcode
left join (select * from t_drgs_group_weight where fsctime is null and fhid=hid) w on g.fgroupcode=w.fgroupid
where g.fgroupstate='1' and g.fouthostime between to_date(''||starttime||' 00:00:00','yyyy/mm/dd hh24:mi:ss')
and to_date(''||endtime||' 23:59:59','yyyy/mm/dd hh24:mi:ss')
group by g.fgroupcode,n.fname,w.fweight;
  end pro_drgs_getWeight;

function fun_drgs_getWeight(starttime varchar2,endtime varchar2,hid number)
return myResult is
  p_cur myResult;
  allNum number;
begin
    select count(*) into allNum from t_drgs_grouprecord
    where fouthostime between to_date(''||starttime||' 00:00:00','yyyy/mm/dd hh24:mi:ss')
          and to_date(''||endtime||' 23:59:59','yyyy/mm/dd hh24:mi:ss');

open p_cur for
select g.fgroupcode,n.fname,nvl(w.fweight,0) fweight,
case when w.fweight>10 then 'RW>10'
     when w.fweight>5 then '5<RW≤10'
     when w.fweight>2 then '2<RW≤5'
     when w.fweight>=1 then '1≤RW<2'
     when w.fweight<0 then '0<RW<1'
     else 'RW=0' end weight,
count(*) groupnum,decode(allNum,0,0,count(*)/allNum) groupPer,avg(fday) avgday,avg(fmoney) avgmoney
from t_drgs_grouprecord g
left join t_drgs_group_name n on g.fgroupcode=n.fcode
left join (select * from t_drgs_group_weight where fsctime is null and fhid=hid) w on g.fgroupcode=w.fgroupid
where g.fgroupstate='1' and g.fouthostime between to_date(''||starttime||' 00:00:00','yyyy/mm/dd hh24:mi:ss')
and to_date(''||endtime||' 23:59:59','yyyy/mm/dd hh24:mi:ss')
group by g.fgroupcode,n.fname,w.fweight;

return p_cur;
end fun_drgs_getWeight;
end pag_drgs_weight;

仍然使用sqlplus测试结果
执行包里的存储过程和上面的步骤几乎一样，只是在第四步的时候，在存储过程名称前面加上包名称
exec pag_drgs_weight.pro_drgs_getweight(‘2017/01/01’,’2017/01/31’,1,:r)

执行包里的方法
第一种：
1.var r refcursor
2.exec :r :=pag_drgs_weight.fun_drgs_getweight(‘2017/01/01’,’2017/01/31’,1)
3.print r
4.显示查询结果

第二种：
1.select pag_drgs_weight.fun_drgs_getweight(‘2017/01/01’,’2017/01/31’,1) from dual;
2.显示查询结果
注意要写分号，如果不写分号，会出现下面这种结果

这样的话，接着输入一个斜杠（/），然后回车，仍然会显示查询结果，完美！

PS：关于Oracle的游标，和这次没有涉及到的Oracle的type，仍然不会用，还需学习，fighting！