Oracle-存储过程
1： 非动态
1 -------------------------------------------------------- 2 -- 文件已创建 - 星期一-二月-01-2016 3 -------------------------------------------------------- 4 -------------------------------------------------------- 5 -- DDL for Procedure PD_SOURCEMONITOR_ALARM 6 -------------------------------------------------------- 7 set define off; 8 9 CREATE OR REPLACE PROCEDURE "APP_BUSI"."PD_SOURCEMONITOR_ALARM" (timetype in varchar2,pageNum in int ,pageSize in int ,totalCount out int,cursor_a out sys_refcursor) 10 as 11 12 /* 编写人：闫菁 13 本存储过程为“特定信源监测-告警表格”编制。 14 15 此存储过程 采用分析函数lag（）获得上一条数据，然后进行环比 16 特定信源监测-告警表 17 ipaddr 信源的ip 18 timeype 从时间粒度取 19 pageNum 产品工厂传递的分页页码。 20 pageSize 产品工厂传递的分页条目 21 思路：一：获得一个时间段内PSV_KEY_SITE_INFO的数据。 22 二：得到所有时间点，每个site_name 的 sum(p.tcp_success)/sum(p.session_count) = tcp_rate 23 三：使用 lag( tcp_rate,1, tcp_rate) over (partition by site_name order by gather_time ) 24 得到按照site_name进行分组后，并且按照时间排序后，上一个时间点的 tcp_rate。 25 得到的数据如下： 26 site_name gather_time tcp_rate last_tcp_rate 27 百度 2015-11-13 09:45:00 98 89 28 百度 2015-11-13 09:30:00 89 90 29 百度 2015-11-14 09:15:00 90 77 30 31 四：筛选得到比上一次连接成功率下降20%的数据 tcp_rate <= last_tcp_rate -0.2 32 */ 33 34 35 36 37 v_begin_time DATE; --保存计算长度开始时间 38 v_end_time DATE; --保存计算长度开始时间 39 v_guid varchar2(64); -- 保存guid 40 41 begin 42 43 if timetype = '1' then --如果时间类型为1， 使用15分钟粒度。 44 v_begin_time := trunc(sysdate, 'hh24') + (round(to_number(to_char(sysdate, 'mi'))/15,0)-1)/96-14/96; 45 v_end_time := trunc(sysdate, 'hh24') + (round(to_number(to_char(sysdate, 'mi'))/15,0)-1)/96; 46 47 elsif timetype = '2' then --如果时间类型为1， 使用60分钟粒度。 48 v_begin_time := trunc(sysdate, 'mi') - 14/24; 49 v_end_time := trunc(sysdate, 'mi'); 50 else --如果时间类型为3， 使用24小时粒度。 51 v_begin_time := trunc(sysdate, 'dd') -14; 52 v_end_time := trunc(sysdate, 'mi'); 53 end if; 54 55 56 57 --dbms_output.put_line(v_begin_time ||'--' || v_end_time ); 58 --告警列表, 在tcp_success_rate 下降 20% 就发生告警 59 --使用lag函数得到上一条数据的值，然后将当前条和上一条数据进行差值比较。 差值大于等于20%的发生告警 60 61 62 --输出总的条目数量-- 返回给产品工厂的表格。 表格控件需要此数据来做分页数据处理。 63 select count(*) into totalCount from ( 64 select a.gather_time, a.site_name 65 from 66 ( select gather_time,site_name , tcp_rate, 67 lag( tcp_rate,1, tcp_rate) over (partition by site_name order by gather_time ) as last_tcp_rate 68 from ( 69 select gather_time,site_name, 70 case when sum(session_count)=0 then 0 71 else round(sum(p.tcp_success)/sum(p.session_count),2) end as tcp_rate 72 from PSV_KEY_SITE_INFO p 73 where gather_time >= v_begin_time --页面上显示15个粒度周期 74 and gather_time <= v_end_time 75 group by gather_time, site_name 76 order by gather_time desc,site_name 77 )b 78 ) a 79 where tcp_rate <= (last_tcp_rate - (select property_value 80 from app_base.busi_property_conf 81 where property_name = 'tcp_success_rate_alarm')) ); 82 83 84 85 --返回时间段内，环比的数据。 其中pageNum 和 pageSize 均为产品工厂传递的分页变量。 86 87 88 open cursor_a for select to_char(gather_time, 'yyyy-mm-dd hh24:mi') as 告警时间 , 89 site_name as 站点名, 90 '连接成功率' as 异常指标, 91 value as 异常值 92 -- sysdate as 时间 93 from 94 ( 95 select rs, gather_time, site_name, value 96 from 97 ( 98 select rownum as rs, gather_time, site_name, value 99 from ( 100 select a.gather_time, 101 a.site_name , 102 round(a.tcp_rate,2) *100 || '%' || '（环比下降'|| (round(a.last_tcp_rate-a.tcp_rate,2) *100) ||'%）' as value 103 from 104 ( select gather_time, 105 site_name , 106 tcp_rate, 107 lag( tcp_rate,1, tcp_rate) over (partition by site_name order by gather_time ) as last_tcp_rate -- 使用lag函数，得到环比的上一次的数据 108 from ( 109 select gather_time,site_name, 110 case when sum(session_count)=0 then 0 111 else round(sum(p.tcp_success)/sum(p.session_count),2) 112 end as tcp_rate --计算出tcp_rate的值 113 from PSV_KEY_SITE_INFO p 114 where gather_time >= v_begin_time --当前时间粒度，往前推14个粒度。 115 and gather_time <= v_end_time -- 当前时间的，前一个粒度。 116 group by gather_time, site_name 117 order by gather_time desc,site_name 118 )b 119 ) a 120 where tcp_rate <= (last_tcp_rate - (select property_value -- 从conf表中得到告警阈值，得到符合下降比例的数据。 121 from app_base.busi_property_conf 122 where property_name = 'tcp_success_rate_alarm')) 123 order by gather_time desc,site_name 124 )result1 --得到符合下降20%的数据，并排序 125 )t1 --对排序后的数据集合添加上排序号 126 where t1.rs <=(pageNum)*pageSize 127 ) t2 -- 取到第N页前所有页的数据。 128 where rs > (pageNum-1)*pageSize ; -- 取到具体第N页的数据 129 130 131 end; 132 133 /
2：动态
1 -------------------------------------------------------- 2 -- 文件已创建 - 星期一-二月-01-2016 3 -------------------------------------------------------- 4 -------------------------------------------------------- 5 -- DDL for Procedure PD_FLOWSERVICE_CYLINDER 6 -------------------------------------------------------- 7 set define off; 8 9 CREATE OR REPLACE PROCEDURE "APP_BUSI"."PD_FLOWSERVICE_CYLINDER" (in_is_wireless in varchar2,in_isp_group_id in varchar2, in_timetype in varchar2, in_xtime in varchar2, o_cursor_a out sys_refcursor) 10 as 11 12 /* 编写人：闫菁 13 本存储过程为“企业间流量分业务占比-百分比柱形图”编制。 14 15 in_is_wireless: 是否只显示移动互联网。 0 代表否。1代表是。 16 in_isp_group_id: 互联单位，-1 代表全部。从下拉互联单选获得。 得到的数据格式为 源ip_目的ip，例如1_2,3_4 。当选择不是全部的时候，需要从这个字符串，截取出源运营商和目的运营商。 17 timetype: 时间轴类型。 1代表15分钟类型，2代表60分钟类型，3代表24小时类型。 18 begintime: 开始时间。从时间轴获得。 19 endtime: 结束时间。从时间轴获得。 20 21 步骤：一：取到具体的时间点（从父图折线图获得）的，所有业务分组的占比。 二：进行数据筛选，只选择占比大于百分之一的数据。 22 RATIO_TO_REPORT(field) OVER () ， 针对field列，每行记录占所有sum（field）的占比。*/ 23 24 25 v_sql varchar2(3000); --存储sql 26 v_s_isp number(10); -- 源运营商 27 v_d_isp number(10); -- 目的运营商 28 v_xtime_time date; --存储从父图传递的时间点 29 30 begin 31 32 select to_number(substr(in_isp_group_id,1,instr(in_isp_group_id,'_',1,1)-1)) into v_s_isp from dual; -- 获得源运营商 33 select to_number(substr(in_isp_group_id,instr(in_isp_group_id,'_',-1,1)+1)) into v_d_isp from dual; --获得目的运营商 34 v_xtime_time := TO_DATE(in_xtime,'yyyy-mm-dd hh24:mi:ss'); -- 将传递的时间点，从字符串转为时间类型 35 36 37 -- sql 第一行查询结果返回符合图表个数的数据 38 -- sql 第二行子查询中得到具体的业务字段 39 v_sql := ' select rownum as orderNum, service_group_id as service_group_id , service_group_name as name, percent_flow as value,'||''''||in_xtime||''''||' as xtime from 40 ( select service_group_id,service_group_name, round((RATIO_TO_REPORT(sum(ip_flow)) OVER ()),4) * 100 AS percent_flow 41 '; 42 43 -- 时间粒度为1， 使用15分钟表的gather_time 进行数据筛选。 其他类型使用stat_time进行数据筛选。其中1 为时间点v_xtime_time的占位。 44 if in_timetype = '1' then 45 v_sql := v_sql || ' from app_busi.PSV_GROUP_TREND t where t.gather_time = :A'; 46 elsif in_timetype = '2' then 47 v_sql := v_sql || ' from app_busi.PSV_GROUP_TREND_HM t where t.stat_time = :B'; 48 else 49 v_sql := v_sql || ' from app_busi.PSV_GROUP_TREND_DM t where t.stat_time = :C'; 50 end if; 51 52 --如果仅仅移动互联网，拼接相关筛选条件 53 if in_is_wireless = '1' then 54 v_sql := v_sql || ' and t.is_wireless_flag = 1'; 55 end if; 56 57 -- 如果不是全部，拼接具体的其实运营商和目的运营商条件。 其中2为v_s_isp的占位, 3 为v_d_isp 的占位。 58 if in_isp_group_id <> '-1' then 59 v_sql := v_sql || ' and t.s_isp_id = ' || v_s_isp || ' and t.d_isp_id = ' || v_d_isp; 60 end if; 61 62 -- 拼接查询结果的分组条件。只选择百分比大于1的数据。 63 v_sql :=v_sql || ' group by service_group_id, service_group_name 64 order by percent_flow desc 65 ) result1 where percent_flow >=0.1 '; 66 67 open o_cursor_a for v_sql using v_xtime_time; -- 替换时间点, 源运营商,目的运营商。进行查询，并将结束返回给游标。 68 69 -- dbms_output.put_line(v_sql); 70 71 end; 72 73 /
3： 使用临时表
1 -------------------------------------------------------- 2 -- 文件已创建 - 星期一-二月-01-2016 3 -------------------------------------------------------- 4 -------------------------------------------------------- 5 -- DDL for Procedure PD_LINK_FLOW_DETAIL 6 -------------------------------------------------------- 7 set define off; 8 9 CREATE OR REPLACE PROCEDURE "APP_BUSI"."PD_LINK_FLOW_DETAIL" (link_type in varchar2, link_id in varchar2,timetype in varchar2, begintime in varchar2, endtime in varchar2, cursor_a out sys_refcursor) 10 as 11 12 13 /* 编写人：闫菁 14 本存储过程为“互联链路流量详情-上下折线图”编制。 15 16 link_type： 1代表逻辑链路，2代表物理链路。在逻辑链路中使用的表为 PSV_LLINK_TRAFFIC,PSV_LLINK_TRAFFIC_HM,PSV_LLINK_TRAFFIC_DM 17 link_type： 1代表逻辑链路，2代表物理链路。在物理链路中使用的表为 PSV_PLINK_TRAFFIC,PSV_PLINK_TRAFFIC_HM,PSV_PLINK_TRAFFIC_DM 18 link_id: 链路的id 19 timetype: 时间轴的时间类型，1代表15分钟，2代表60分钟，3代表24小时 20 begintime: 时间轴的时间轴传递的开始时间 21 endtime: 时间轴传递的结束时间 22 23 思路： 24 图表上显示了选择的链路和对应链路的数据。获得对应链路基础数据。通过对应链路通过 当前链路的源ip = 对应链路的目的ip，当前链路的目的ip = 对应链路的源ip获得。 25 在临时表中，插入一些数据作为图标的横坐标。 26 求取出当前链路和对应链路的数据。 27 然后采用left join 和前面求出的 链路数据进行关联。 将数据组织成产品工厂格式返回给图表。 28 其中，选择物理链路，查询语句使用物理链路表， 选择逻辑链路，查询语句使用逻辑链路表。 选择时间粒度后，查询语句选择对应的粒度表。 29 */ 30 v_linkid number(10); 31 v_llink_name1 varchar2(100); -- 保存选择的逻辑链路name 32 v_llink_id2 number(10); -- 保存对应逻辑链路id 33 v_llink_name2 varchar2(100); -- 保存对应逻辑链路name 34 35 v_plink_name1 varchar2(100); -- 保存选择的物理链路name 36 v_plink_id2 number(10); -- 保存对应的物理链路id 37 v_plink_name2 varchar2(100); -- 保存对应逻辑链路name 38 39 v_begin_time date; 40 v_begin_time2 date; 41 v_end_time date; 42 v_guid varchar(50); 43 44 begin 45 46 v_linkid := TO_NUMBER(link_id); --将linkid 转化为number 类型 47 v_begin_time := TO_DATE(begintime,'yyyy-mm-dd hh24:mi:ss');--将输入的时间段从字符转成时间类型, 用于进行生成横轴 48 v_begin_time2 := TO_DATE(begintime,'yyyy-mm-dd hh24:mi:ss');--将输入的时间段从字符转成时间类型-- 用于进行数值比较 49 v_end_time := TO_DATE(endtime,'yyyy-mm-dd hh24:mi:ss'); ----将输入的时间段从字符转成时间类型-- 用于进行生成横轴 50 51 v_guid := SYS_GUID() ; --得到用户guid 52 53 54 if link_type = '1' then -- 得到逻辑链路对应的链路id和name 55 select a.logical_link_id, a.logical_link_name, b.logical_link_name into v_llink_id2, v_llink_name2, v_llink_name1 56 from APP_BASE.LOGICAL_LINK a 57 inner join APP_BASE.LOGICAL_LINK b on a.d_border_router_id = b.s_border_router_id and a.s_border_router_id = b.d_border_router_id 58 where b.logical_link_id = v_linkid; 59 else -- 得到物理链路对应的id 和name 60 select a.phy_link_id, a.phy_link_name, b.phy_link_name into v_plink_id2, v_plink_name2, v_plink_name1 61 from APP_BASE.PHYSICAL_LINK a 62 inner join APP_BASE.PHYSICAL_LINK b on a.source_ip = b.destination_ip and b.destination_ip = a.source_ip 63 where b.phy_link_id = v_linkid; 64 end if; 65 66 67 68 if timetype = '1' then -- 如果时间类型是1， 在临时表中插入15分钟粒度的数据，作为图表的横轴。 69 loop 70 exit when v_begin_time >= v_end_time ; 71 insert into temp_time_list(stat_time,guid) values(v_begin_time,v_guid); 72 v_begin_time := v_begin_time + 1/24/4; 73 end loop; 74 75 elsif timetype = '2' then -- 如果时间类型是2， 在临时表中插入60分钟粒度的数据作为图表的横轴。 76 loop 77 exit when v_begin_time >= v_end_time; 78 insert into temp_time_list(stat_time,guid) values(v_begin_time,v_guid); 79 v_begin_time := v_begin_time + 1/24; 80 end loop; 81 else -- 如果时间类型是3， 在临时表中插入天时间粒度的数据，作为图表的横轴。 82 loop 83 exit when v_begin_time >= v_end_time; 84 insert into temp_time_list(stat_time,guid) values(v_begin_time,v_guid); 85 v_begin_time := v_begin_time + 1; 86 end loop; 87 end if; 88 89 90 91 if link_type = '1' then -- 逻辑链路 92 if timetype = '1' then -- 此部分，使用15分钟的逻辑链路表进行查询。 93 open cursor_a for select nvl(result1.bd_usage_rate*100,0) as bd_usage_rate_up, -- 带宽利用率 94 nvl(round(result1.ip_flow_rate/1024,2),0) as llinkspeed_up , -- 速率 95 nvl(v_llink_name1,' ') as name1, -- 链路名称 96 nvl(result2.bd_usage_rate*100,0) as bd_usage_rate_down, -- 对应的带宽利用率 97 nvl(round(result2.ip_flow_rate/1024,2),0) as llinkspeed_down , --对应的带宽速率 98 nvl(v_llink_name2,' ') as name2, -- 对应的链路名称 99 to_char(t.stat_time,'yyyy-mm-dd hh24:mi')as xtime -- 横轴上的时间点 100 from temp_time_list t 101 left join 102 ( 103 select bd_usage_rate ,ip_flow_rate,gather_time as xtime--to_char(gather_time, 'yyyy-mm-dd hh24:mi:ss') as xtime 104 from APP_BUSI.PSV_LLINK_TRAFFIC p 105 where p.logic_link_id = v_linkid --link_id 为用户页面选择的链路id 106 and p.gather_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 107 and p.gather_time <= v_end_time - 1/96 -- 结束时间为时间轴结束时间往前推一个粒度 108 109 )result1 on t.stat_time = result1.xtime 110 left join 111 ( select bd_usage_rate ,ip_flow_rate, gather_time as xtime--to_char(gather_time, 'yyyy-mm-dd hh24:mi:ss') as xtime 112 from APP_BUSI.PSV_LLINK_TRAFFIC p 113 where p.logic_link_id = v_llink_id2 --v_llink_id2为前面获得的对应链路id 114 and p.gather_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 115 and p.gather_time <= v_end_time - 1/96 -- 结束时间为时间轴结束时间往前推一个粒度 116 ) result2 117 on t.stat_time = result2.xtime 118 where t.guid = v_guid 119 order by t.stat_time ; 120 121 122 elsif timetype = '2' then -- 此部分，使用60分钟的逻辑链路表进行查询。 123 124 open cursor_a for select nvl(result1.bd_usage_rate*100,0) as bd_usage_rate_up, 125 nvl(round(result1.ip_flow_rate/1024,2),0) as llinkspeed_up , 126 nvl(v_llink_name1,' ') as name1, 127 nvl(result2.bd_usage_rate*100,0) as bd_usage_rate_down, 128 nvl(round(result2.ip_flow_rate/1024,2),0) as llinkspeed_down , 129 nvl(v_llink_name2,' ') as name2, 130 to_char(t.stat_time,'yyyy-mm-dd hh24:mi')as xtime 131 from temp_time_list t -- 从临时表为驱动表开始关联数据 132 left join 133 ( 134 select bd_usage_rate ,ip_flow_rate,stat_time as xtime--to_char(stat_time, 'yyyy-mm-dd hh24:mi:ss') as xtime 135 from APP_BUSI.PSV_LLINK_TRAFFIC_HM p 136 where p.logic_link_id = v_linkid 137 and p.stat_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 138 and p.stat_time <= v_end_time - 1/24 -- 结束时间为时间轴结束时间往前推一个粒度 139 )result1 on t.stat_time = result1.xtime 140 left join 141 ( select bd_usage_rate ,ip_flow_rate,stat_time as xtime--to_char(stat_time, 'yyyy-mm-dd hh24:mi:ss') as xtime 142 from APP_BUSI.PSV_LLINK_TRAFFIC_HM p 143 where p.logic_link_id = v_llink_id2 --v_llink_id2为前面获得的对应链路id 144 and p.stat_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 145 and p.stat_time <= v_end_time - 1/24 -- 结束时间为时间轴结束时间往前推一个粒度 146 ) result2 on t.stat_time = result2.xtime 147 where t.guid = v_guid 148 order by t.stat_time ; 149 150 151 else -- -- 此部分，使用24小时逻辑链路表进行查询。 152 open cursor_a for select nvl(result1.bd_usage_rate*100,0) as bd_usage_rate_up, 153 nvl(round(result1.ip_flow_rate/1024,2),0) as llinkspeed_up , 154 nvl(v_llink_name1,' ') as name1, 155 nvl(result2.bd_usage_rate*100,0) as bd_usage_rate_down, 156 nvl(round(result2.ip_flow_rate/1024,2),0) as llinkspeed_down , 157 nvl(v_llink_name2,' ') as name2, 158 to_char(t.stat_time,'yyyy-mm-dd hh24:mi')as xtime 159 from temp_time_list t 160 left join ( 161 select bd_usage_rate ,ip_flow_rate,stat_time as xtime 162 from APP_BUSI.PSV_LLINK_TRAFFIC_DM p 163 where p.logic_link_id = v_linkid 164 and p.stat_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 165 and p.stat_time <= v_end_time - 1 -- 结束时间为时间轴结束时间往前推一个粒度 166 )result1 on t.stat_time = result1.xtime 167 left join 168 ( select bd_usage_rate ,ip_flow_rate,stat_time as xtime 169 from APP_BUSI.PSV_LLINK_TRAFFIC_DM p 170 where p.logic_link_id = v_llink_id2 --v_llink_id2为前面获得的对应链路id 171 and p.stat_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 172 and p.stat_time <= v_end_time - 1 -- 结束时间为时间轴结束时间往前推一个粒度 173 ) result2 174 on t.stat_time = result2.xtime 175 where t.guid = v_guid 176 order by t.stat_time ; 177 end if; 178 179 else 180 if timetype = '1' then -- -- 此部分，使用15分钟的物理链路表进行查询。 181 open cursor_a for select 182 nvl(result1.bd_usage_rate*100,0) as bd_usage_rate_up, 183 nvl(round(result1.ip_flow_rate/1024,2),0) as llinkspeed_up , 184 nvl(v_plink_name1,' ') as name1, 185 nvl(result2.bd_usage_rate*100,0) as bd_usage_rate_down, 186 nvl(round(result2.ip_flow_rate/1024,2),0) as llinkspeed_down , 187 nvl(v_plink_name2,' ') as name2, 188 to_char(t.stat_time,'yyyy-mm-dd hh24:mi')as xtime 189 from temp_time_list t 190 left join 191 ( 192 select bd_usage_rate ,ip_flow_rate,gather_time as xtime 193 from APP_BUSI.PSV_PLINK_TRAFFIC p 194 where p.plink_id = v_linkid 195 and p.gather_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 196 and p.gather_time <= v_end_time - 1/96 -- 结束时间为时间轴结束时间往前推一个粒度 197 )result1 on t.stat_time = result1.xtime 198 left join 199 ( select bd_usage_rate ,ip_flow_rate,gather_time as xtime 200 from APP_BUSI.PSV_PLINK_TRAFFIC p 201 where p.plink_id = v_plink_id2 --v_plink_id2为前面获得的对应链路id 202 and p.gather_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 203 and p.gather_time <= v_end_time - 1/96 -- 结束时间为时间轴结束时间往前推一个粒度 204 ) result2 205 on t.stat_time = result2.xtime 206 where t.guid = v_guid 207 order by t.stat_time ; 208 209 210 elsif timetype = '2' then -- 此部分，使用60分钟的物理链路表进行查询。 211 open cursor_a for select nvl(result1.bd_usage_rate*100,0) as bd_usage_rate_up, 212 nvl(round(result1.ip_flow_rate/1024,2),0) as llinkspeed_up , 213 nvl(v_plink_name1,' ') as name1, 214 nvl(result2.bd_usage_rate*100,0) as bd_usage_rate_down, 215 nvl(round(result2.ip_flow_rate/1024,2),0) as llinkspeed_down , 216 nvl(v_plink_name2,' ') as name2, 217 to_char(t.stat_time,'yyyy-mm-dd hh24:mi')as xtime 218 from temp_time_list t 219 left join ( 220 select bd_usage_rate ,ip_flow_rate,stat_time as xtime 221 from APP_BUSI.PSV_PLINK_TRAFFIC_HM p 222 where p.plink_id = v_linkid 223 and p.stat_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 224 and p.stat_time <= v_end_time - 1/24 -- 结束时间为时间轴结束时间往前推一个粒度 225 )result1 on t.stat_time = result1.xtime 226 left join 227 ( select bd_usage_rate ,ip_flow_rate,stat_time as xtime 228 from APP_BUSI.PSV_PLINK_TRAFFIC_HM p 229 where p.plink_id = v_plink_id2 --v_plink_id22为前面获得的对应链路id 230 and p.stat_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 231 and p.stat_time <= v_end_time - 1/24 -- 结束时间为时间轴结束时间往前推一个粒度 232 ) result2 233 on t.stat_time = result2.xtime 234 where t.guid = v_guid 235 order by t.stat_time ; 236 237 else -- 此部分，使用24小时物理链路表进行查询。 238 open cursor_a for select nvl(result1.bd_usage_rate*100,0) as bd_usage_rate_up, 239 nvl(round(result1.ip_flow_rate/1024,2),0) as llinkspeed_up , 240 nvl(v_plink_name1,' ') as name1, 241 nvl(result2.bd_usage_rate*100,0) as bd_usage_rate_down, 242 nvl(round(result2.ip_flow_rate/1024,2),0) as llinkspeed_down , 243 nvl(v_plink_name2,' ') as name2, 244 to_char(t.stat_time,'yyyy-mm-dd hh24:mi')as xtime 245 from temp_time_list t 246 left join ( 247 select bd_usage_rate ,ip_flow_rate,stat_time as xtime 248 from APP_BUSI.PSV_PLINK_TRAFFIC_DM p 249 where p.plink_id = v_linkid 250 and p.stat_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 251 and p.stat_time <= v_end_time - 1 -- 结束时间为时间轴结束时间往前推一个粒度 252 )result1 on t.stat_time = result1.xtime 253 left join 254 ( select bd_usage_rate ,ip_flow_rate,stat_time as xtime 255 from APP_BUSI.PSV_PLINK_TRAFFIC_DM p 256 where p.plink_id = v_plink_id2 --v_plink_id2为前面获得的对应链路id 257 and p.stat_time >= v_begin_time2 -- 开始时间为时间轴取到的开始时间 258 and p.stat_time <= v_end_time - 1 -- 结束时间为时间轴结束时间往前推一个粒度 259 ) result2 260 on t.stat_time = result2.xtime 261 where t.guid = v_guid 262 order by t.stat_time ; 263 end if; 264 end if; 265 266 end; 267 268 /
4: Oracle 游标返回多个列
1 SELECT多个字段： 2 3 CREATE OR REPLACE PROCEDURE BRPMS.P_PREWORK_GZZB(V_REC_NUM IN NUMBER, 4 V_JOBTYPE_ID IN VARCHAR2, 5 V_DEV_TYPE IN VARCHAR2, 6 V_DEV_MODAL IN VARCHAR2, 7 V_SPEC_CODE IN VARCHAR2, 8 V_PERWORK_TYPE IN VARCHAR2) AS 9 TYPE R_TYPE IS RECORD( 10 V_REC_NUM BRPMS.TB_WIT_INSTRUCT_PREWORK.REC_NUM%TYPE, 11 V_JOBTYPE_ID BRPMS.TB_WIT_INSTRUCT_PREWORK.JOBTYPE_ID%TYPE, 12 V_DEV_TYPE BRPMS.TB_WIT_INSTRUCT_PREWORK.DEV_TYPE%TYPE, 13 V_DEV_MODAL BRPMS.TB_WIT_INSTRUCT_PREWORK.DEV_MODAL%TYPE, 14 V_SPEC_CODE BRPMS.TB_WIT_INSTRUCT_PREWORK.SPEC_CODE%TYPE, 15 V_PERWORK_TYPE BRPMS.TB_WIT_INSTRUCT_PREWORK.PERWORK_TYPE%TYPE); 16 17 V_R1 R_TYPE; 18 CURSOR MYCURSOR IS 19 SELECT MAX(P.REC_NUM), 20 P.JOBTYPE_ID, 21 P.DEV_TYPE, 22 P.DEV_MODAL, 23 P.SPEC_CODE, 24 P.PERWORK_TYPE 25 FROM BRPMS.TB_WIT_INSTRUCT_PREWORK P 26 WHERE P.PERWORK_TYPE = '0600301' 27 AND P.CONTENT != '工器具已准备完毕，材料、备品已落实。' 28 GROUP BY P.JOBTYPE_ID, 29 P.DEV_TYPE, 30 P.DEV_MODAL, 31 P.SPEC_CODE, 32 P.PERWORK_TYPE; 33 BEGIN 34 OPEN MYCURSOR; 35 LOOP 36 FETCH MYCURSOR 37 INTO V_R1; 38 EXIT WHEN MYCURSOR%NOTFOUND; 39 INSERT INTO BRPMS.TB_WIT_INSTRUCT_PREWORK 40 (GUID, 41 JOBTYPE_ID, 42 DEV_TYPE, 43 DEV_MODAL, 44 SPEC_CODE, 45 PERWORK_TYPE, 46 REC_NUM, 47 CONTENT) 48 VALUES 49 (SYS_GUID(), 50 V_R1.V_JOBTYPE_ID, 51 V_R1.V_DEV_TYPE, 52 V_R1.V_DEV_MODAL, 53 V_R1.V_SPEC_CODE, 54 '0600301', 55 V_R1.V_REC_NUM + 1, 56 '工器具已准备完毕，材料、备品已落实。'); 57 COMMIT; 58 END LOOP; 59 CLOSE MYCURSOR; 60 END;
5: 调用存储过程
1 create or replace procedure ZXT_YXR(cursors out sys_refcursor) 2 3 is 4 5 TYPE R_TYPE IS RECORD( 6 name1 varchar(20), 7 value1 varchar(20), 8 info1 varchar(20)); 9 V_R1 R_TYPE; 10 11 BEGIN 12 13 open cursors for 14 15 select 'aa' name, 10 as value, 'dd' as info from dual 16 union all 17 select 'bb' name, 20 as value, 'zz' as info from dual; 18 19 loop 20 fetch cursors into V_R1; 21 exit when cursors%notfound; 22 dbms_output.put_line('------'||V_R1.name1 ||' , '|| to_char(V_R1.value1)); 23 end loop; 24 close cursors; 25 26 END;
SET serveroutput ON; --打开客户端输出功能 declare aa sys_refcursor; begin ZXT_YXR(aa); end;
浙公网安备 33010602011771号