oracle 存储过程示例

最新推荐文章于 2025-12-20 11:22:15 发布

原创最新推荐文章于 2025-12-20 11:22:15 发布 · 827 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #存储 #os #date #office #air

本文展示了两个Oracle存储过程示例，分别涉及日期处理和数据更新。PROC_CITY_DATE2T_CITY_ORDERS存储过程用于根据日期插入数据，PROC_TO_T_PARAMETER则计算并更新最近30天和365天内的空气质量最佳城市。这两个过程涉及到日期函数、聚合函数以及多表查询。

CREATE OR REPLACE PROCEDURE PROC_CITY_DATE2T_CITY_ORDERS IS

l_maxOper_date date;

l_maxId number;

l_errorcode varchar2(100);

BEGIN

-- author sunchengliang

SELECT max(oper_date) into l_maxOper_date FROM city_day;

select count(cityid) into l_maxId from t_city_orders;

--插入

INSERT INTO t_city_orders

(oper_date,

stat_type,

cityid,

city,

excellent_days,

fine_days,

nogood_days,

orders,

l_excellent_days,

l_fine_days,

l_nogood_days,

l_orders,

r_excellent_days,

r_fine_days,

r_nogood_days,

r_orders,

oper_time)

SELECT l_maxOper_date,

'30天内',

l_maxId + 1,

aaa.city,

aaa.excellent_days,

aaa.fine_days,

aaa.nogood_days,

aaa.orders,

ccc.l_excellent_days,

ccc.l_fine_days,

ccc.l_nogood_days,

ccc.l_orders,

bbb.r_excellent_days,

bbb.r_fine_days,

bbb.r_nogood_days,

bbb.r_orders,

sysdate

FROM (SELECT aa.*,

ROW_NUMBER() OVER(ORDER BY aa.excellent_days DESC) orders

FROM (SELECT city,

SUM(DECODE(grade, 'Ⅰ', 1, 0)) excellent_days,

SUM(DECODE(grade, 'Ⅱ', 1, 0)) fine_days,

SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) nogood_days

FROM city_day a

WHERE oper_date BETWEEN TRUNC(l_maxOper_date) - 30 AND

TRUNC(l_maxOper_date)

group by city) aa) aaa,

(SELECT aa.*,

ROW_NUMBER() OVER(ORDER BY aa.r_excellent_days DESC) r_orders

FROM (SELECT city,

SUM(DECODE(grade, 'Ⅰ', 1, 0)) r_excellent_days,

SUM(DECODE(grade, 'Ⅱ', 1, 0)) r_fine_days,

SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) r_nogood_days

FROM city_day a

WHERE oper_date BETWEEN

ADD_MONTHS(TRUNC(l_maxOper_date), -1) - 30 AND

ADD_MONTHS(TRUNC(l_maxOper_date), -1)

group by city) aa) bbb,

(SELECT aa.*,

ROW_NUMBER() OVER(ORDER BY aa.l_excellent_days DESC) l_orders

FROM (SELECT city,

SUM(DECODE(grade, 'Ⅰ', 1, 0)) l_excellent_days,

SUM(DECODE(grade, 'Ⅱ', 1, 0)) l_fine_days,

SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) l_nogood_days

FROM city_day a

WHERE oper_date BETWEEN

ADD_MONTHS(TRUNC(l_maxOper_date), -12) - 30 AND

ADD_MONTHS(TRUNC(l_maxOper_date), -12)

group by city) aa) ccc

WHERE aaa.city = bbb.city(+)

AND aaa.city = ccc.city(+);

Commit;

exception

When Others Then

l_errorcode := sqlerrm;

Rollback;

END;

create or replace procedure PROC_TO_T_PARAMETER is

l_airBestCity varchar2(50);

l_currentDay date;

l_air30daysI varchar2(100);

l_air30daysII varchar2(100);

l_air365daysI varchar2(100);

l_air365daysII 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;

--最近30天内二级天数最多的城市

select tt.city

into l_air30daysII

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;

--最近1年内一级天数最多的城市

select tt.city

into l_air365daysI

from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1

from city_day t

where t.oper_date > l_currentDay - 365

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 - 365

and t.oper_date < l_currentDay

group by t.city)

and rownum = 1;

--最近1年内二级天数最多的城市

select tt.city

into l_air365daysII

from (select t.city, sum(decode(grade, 'Ⅱ', 1, 0)) total1

from city_day t

where t.oper_date > l_currentDay - 365

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 - 365

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 || '#' ||

'二级天数最多的城市:' || l_air30daysII || '#' ||

'最近1年内空气质量一级天数最多的城市:' || l_air365daysI || '#' ||

'二级天数最多的城市:' || l_air365daysII

where t.p_type = '1';

Commit;

exception

When Others Then

l_errorcode := sqlerrm;

Rollback;

end PROC_TO_T_PARAMETER;

CREATE OR REPLACE PROCEDURE WEBPM_SI_BROWSE

(

is_ulp in varchar2,

os_seq out varchar2,

os_si_code out varchar2,

os_si_name out varchar2,

os_unit_name out varchar2,

os_ulp out varchar2,

os_office_tel out varchar2,

os_fax out varchar2,

os_address out varchar2,

os_cont_type out varchar2,

os_frame out varchar2,

os_add_frame out varchar2,

os_add_item out varchar2,

os_sign_date out varchar2,

os_contact_person out varchar2,

os_contact_tel out varchar2,

os_contact_msn out varchar2,

os_e_mail out varchar2,

os_post_code out varchar2,

os_eff_date out varchar2,

os_arg_no out varchar2,

os_year_flag out varchar2,

os_es_prop out varchar2,

os_ms_prop out varchar2,

os_icspe_flag out varchar2,

os_icspe_list out varchar2,

os_icspe_amount out varchar2,

os_reason out varchar2,

os_ms_set_mode out varchar2,

os_es_set_mode out varchar2,

os_register_man out varchar2,

os_register_date out varchar2,

os_remarks out varchar2,

os_status out varchar2, -- 16 返回值

os_result out varchar2 -- 17 返回提示

)

ll_os_office_tel number;

ll_os_fax number;

ll_os_sign_date date;

ll_os_eff_date date;

ll_os_icspe_amount number;

ll_os_register_date date;

begin

if is_ulp is null then

os_status := '110 ';

os_result := '单位法人不能为空 ';

return;

end if;

end;

os_status := '100 ';

os_result := '开始查询局向信息 ' ;

begin

select

seq ,

si_code ,

si_name ,

unit_name ,

ulp ,

office_tel ,

fax ,

address ,

cont_type ,

frame ,

add_frame ,

add_item ,

sign_date ,

contact_person ,

contact_tel ,

contact_msn ,

e_mail ,

post_code ,

eff_date ,

arg_no ,

year_flag ,

es_prop ,

ms_prop ,

icspe_flag ,

icspe_list ,

icspe_amount ,

reason ,

ms_set_mode ,

es_set_mode ,

register_man ,

register_date ,

remarks

into

os_seq ,

os_si_code ,

os_si_name ,

os_unit_name ,

os_ulp ,

ll_os_office_tel ,

ll_os_fax ,

os_address ,

os_cont_type ,

os_frame ,

os_add_frame ,

os_add_item ,

ll_os_sign_date ,

os_contact_person ,

os_contact_tel ,

os_contact_msn ,

os_e_mail ,

os_post_code ,

ll_os_eff_date ,

os_arg_no ,

os_year_flag ,

os_es_prop ,

os_ms_prop ,

os_icspe_flag ,

os_icspe_list ,

ll_os_icspe_amount ,

os_reason ,

os_ms_set_mode ,

os_es_set_mode ,

os_register_man ,

ll_os_register_date ,

os_remarks

from WEBPM_SI where ulp = trim(is_ulp) and rownum=1;

os_status := '100 ';

os_result := 'hhh成功查询集成商信息! 集成商名称: '||is_ulp;

exception

when others then

os_status := '110 ';

os_result := '无此集成商信息！ '|| is_ulp || ' '||sqlerrm ;

rollback;

end;

/*输出时间*/

begin

os_sign_date := to_char(ll_os_sign_date, 'yyyy/MM/dd HH24:mi:ss ');

exception

when others then

os_status := '110 ';

os_result := '数据转换错误！ll_os_sign_date= '||ll_os_sign_date|| ' '||sqlerrm ;

return;

end;

/* 集成商编码 */

begin

os_office_tel:=to_number(ll_os_office_tel);

exception

when others then

os_status := '110 ';

os_result := '数据转换错误! ll_os_office_tel= '||ll_os_office_tel;

end;

begin

os_fax:=to_number(ll_os_fax);

exception

when others then

os_status := '110 ';

os_result := '数据转换错误! ll_os_fax= '||ll_os_fax;

end;

begin

os_icspe_amount:=to_number(ll_os_icspe_amount);

exception

when others then

os_status := '110 ';

os_result := '数据转换错误! ll_os_icspe_amount= '||ll_os_icspe_amount;

end;

begin

os_eff_date := to_char(ll_os_eff_date, 'yyyy/MM/dd HH24:mi:ss ');

exception

when others then

os_status := '110 ';

os_result := '数据转换错误！ll_os_eff_date= '||ll_os_eff_date|| ' '||sqlerrm ;

return;

end;

begin

os_register_date := to_char(ll_os_register_date, 'yyyy/MM/dd HH24:mi:ss ');

exception

when others then

os_status := '110 ';

os_result := '数据转换错误！ll_os_register_date= '||ll_os_register_date|| ' '||sqlerrm ;

return;

end;

exception

when others then

return ;

end; -- procedure