先帖上开发时写的一段存储过程
create
or
replace
procedure
PROC_TO_T_PARAMETER
is
l_airBestCity varchar2 ( 50 );
l_currentDay date;
l_air30daysI varchar2 ( 100 );
l_errorcode varchar2 ( 100 );
begin
-- author sunchengliang
-- 当天时间
select max (tt.oper_date) into l_currentDay from city_day tt;
-- 当天空气质量最好的城市
select t.city
into l_airBestCity
from city_day t
where t.oper_date = l_currentDay
and t.pollution_indeces =
( select min (ttt.pollution_indeces)
from city_day ttt
where ttt.oper_date = l_currentDay);
-- 30内一级天数最多的城市
select tt.city
into l_air30daysI
from ( select t.city, sum (decode(grade, ' Ⅰ ' , 1 , 0 )) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city
order by total1 desc ) tt
where tt.total1 = ( select max ( sum (decode(grade, ' Ⅰ ' , 1 , 0 ))) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city) and rownum = 1 ;
-- 更新到db
update T_PARAMETER t
set t.p_content = ' 今天空气量最好的城市是: ' ¦ ¦ l_airBestCity ¦ ¦ ' # ' ¦ ¦
' 最近30天内空气质量一级天数最多的城市: ' ¦ ¦l_air30daysI
where t.p_type = ' 1 ' ;
Commit ;
exception
When Others Then
l_errorcode : = sqlerrm;
Rollback ;
end PROC_TO_T_PARAMETER;
l_airBestCity varchar2 ( 50 );
l_currentDay date;
l_air30daysI varchar2 ( 100 );
l_errorcode varchar2 ( 100 );
begin
-- author sunchengliang
-- 当天时间
select max (tt.oper_date) into l_currentDay from city_day tt;
-- 当天空气质量最好的城市
select t.city
into l_airBestCity
from city_day t
where t.oper_date = l_currentDay
and t.pollution_indeces =
( select min (ttt.pollution_indeces)
from city_day ttt
where ttt.oper_date = l_currentDay);
-- 30内一级天数最多的城市
select tt.city
into l_air30daysI
from ( select t.city, sum (decode(grade, ' Ⅰ ' , 1 , 0 )) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city
order by total1 desc ) tt
where tt.total1 = ( select max ( sum (decode(grade, ' Ⅰ ' , 1 , 0 ))) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city) and rownum = 1 ;
-- 更新到db
update T_PARAMETER t
set t.p_content = ' 今天空气量最好的城市是: ' ¦ ¦ l_airBestCity ¦ ¦ ' # ' ¦ ¦
' 最近30天内空气质量一级天数最多的城市: ' ¦ ¦l_air30daysI
where t.p_type = ' 1 ' ;
Commit ;
exception
When Others Then
l_errorcode : = sqlerrm;
Rollback ;
end PROC_TO_T_PARAMETER;
请注意格式
create
or
replace
procedure
PROC_TO_T_PARAMETER
is
-- your temporary variables
....
begin
-- your sql here
...
commit ;
excption
When Others Then
l_errorcode : = sqlerrm;
Rollback ;
end ;
-- your temporary variables
....
begin
-- your sql here
...
commit ;
excption
When Others Then
l_errorcode : = sqlerrm;
Rollback ;
end ;
当sql语句出现异常时,就会跳到exception代码段,sqlerrm会显示出错误信息.也可以把它赋给out参数,返回给用户。并回滚。如果sql没有异常,commit,完成操作.