inix存储过程.doc

INFORMIX数据库存储过程编写讲义一、存储过程概念sql语句执行的时候要先编译，然后执行。存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。二、标准语法格式1、创建带参数存储过程Create PROCEDURE npmuser.sp_sx_sum_ctr_traf_proc(tch_datetime datetime year to second , i_time integer)returning varchar(40);- 作 者：- 创建时间：- 功能简述：统计农村话务量，改过程需要配置在服务器上，每个小时定时执行- 提取一个小时的话务量并按所属行政区域分组（农村、城市、县城）- 参数描述：tch_datetime传入时间，i 提取传入时间前i个小时的数据- 引用到的其他存储过程或函数： - 被以下存储过程引用过：- 修改历史：- 修 改 人：- 修改时间：- 修改原因：select first_result,parent_ne_id,ne_id,related_bsc,related_msc,region_id,province_idfrom tcc_ne_snap awhere a.first_result = tch_datetime and a.ne_type = 300and confirmed not in (2,5) and resource_status =1into temp te_ne_snap;。return te_result;end PROCEDURE2、创建不带参数存储过程Create PROCEDURE npmuser.sp_sx_sum_ctr_traf_proc()returning varchar(40);define TchDatetime datetime year to second;define snap_date datetime year to second;define Schema_id integer;。return te_result;end PROCEDURE3、临时表第一种方法，先建立临时表，再向临时表插入数据。Create temp te_table (col varchar(100);Insert into 第二种方法，直接利用查询结果创建临时表。注意查询结果的列必须有列名。Select col_a,col_b,col_cFrom tableInto temp te_table;4、变量赋值let snap_date = extend(extend(TchDatetime, year to day),year to second);let Schema_id = -1121449820;5、调用及删除存储过程Execute procedure sp_sx_sum_ctr_traf_proc();调用drop procedure sp_sx_sum_ctr_traf_proc();删除存储过程可以互相调用。6、游标游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。foreach select ne_id,c into v_neid,v_c from te_radio_ne where region_name =v_region_name order by c desc-拥塞率大于5if v_c 5 then let v_id_1= v_id_1+1; update te_radio_ne set i_flag = 1 where ne_id= v_neid ; -更新row_id列 update te_radio_ne set row_id = v_id_1 where ne_id= v_neid ; end if let v_neid = 0 ; let v_c = 0 ;end foreach;三、网管三期报表实例讲解1、农村话务量报表2、统计市区基站整体指标(c1)报表3、按地市提取TOPN小区退服总时长及平均时长四、特殊的存储过程1、函数INFORMIX中存储过程与函数的区分界限十分模糊。而在其他数据库中，函数（FUNCTION）的建立语法与存储过程区分明显。函数实际上是抽象出一部分使用比较频繁，通用性比较强的存储过程来被其他过程所调用。实例：除法函数create procedure npmuser.sfb_divfloat_1(a float,b float,ret0 float default 0.00,ret1 float default -1.00)returning float;define res float;if b = 0 and a = 0 thenreturn ret0;end if;if b = 0 and a0 thenreturn ret1;end if;let res = a/b;return res;end procedure;提取网元名称函数create procedure npmuser.soa_get_nename(id int, type int)returning varchar(64,0);define nename varchar(64,0);define usname varchar(64,0);define zhname varchar(64,0);define cityname varchar(32,0);define provid int;define provname varchar(32,0);define type_flag integer;let provid=-1;select distinct province_id into provid from system_setup;if provid is null or provid=-1 thenreturn unknown province;end if;select userlabel into provname from tic_ne_defwhere ne_type = 10000 and ne_id = provid;if provname is null thenlet provname=no name;end if;if type = 10000 thenlet nename=provname1,4;return nename;end if;select distinct zh_label,userlabel into zhname,usname from tcc_ne_framewhere ne_type = type and ne_id = id and province_id=provid;if (zhname is null) or (zhname = ) thenlet nename=usname;elselet nename=zhname;end if;if nename is null thenlet nename = no_name;end if;if (type = 10004 or type = 10003) thenlet nename = provname1,4 | - | nename;return nename;end if;select count(*) into type_flag from tia_type_def where type_id = type;if (type_flag = 0) thenlet nename=unknown type;end if;return nename;end procedure; 2、触发器触发器是在特定条件下执行的特殊的存储过程。特定条件指触发器所依附的表，发生插入、删除、修改等事件时。其中INSERT是一个触发器事件，表中插入行时触发器被激活，一个表只有一个INSERT触发器，DELETE是一个触发器事件，表中删除行时触发器被激活，一个表只有一个DELETE触发器。UPDATE是一个触发器事件，表中根性列时触发器被激活，如果包括列清单，则更新列清单中的列时触发器被激活。否则更形表中任何列时触发器被激活，一个表可以有多个UPDATE触发器。但是列清单要相互排斥。触发器包含两种二维数组或者说两条行记录。一种为旧数据（OLD），表示被删除的数据，或者被更新前数据。一种为新数据（NEW），表示新增数据，或更新后数据。实例：删除触发器create trigger tg_delete_dc_bsc_infodelete on dc_bsc_inforeferencing old as afor each row(delete from dc_bsc_info_his where start_time = a.start_time and int_id = _id and compress_date = press_date),(insert into dc_bsc_info_his values (a.insert_time,a.start_time,a.stop_time,a.fill_time,a.ne_class,a. ne_name,_id,a.city_id,a.sdchh_avail_carrie,a.sdcch_traffic,a.sdcch_call_att,a.sdchh_assi_fail_num,a.sdchh_call_drop,a.sdchh_call_block,a.tch_avail_carrier,a.tch_traffic,a.tch_att_num,a.tch_overflow_num,a.tch_assi_failed_num,a.tch_unsuc_num,a.tch_seize_num,a.tch_call_att,a.tch_call_block,a.tch_failed_assi,a.tch_call_seize,a.tch_call_drop,a.gsm1800_traf,a.ho_req,a.ho_succs,a.worst_cell,a.avail_cell,a.acc_cell,a.sdcch_seize_att,a.tch_unsuc_call,a.att_sdcch_seiz,a.flag,press_date,a.vendor_id);新增触发器create trigger tg_insert_dc_bsc_infoinsert on dc_bsc_inforeferencing new as afor each row(delete from dc_bsc_info_bak where start_time = a.start_time and int_id = _id and compress_date = press_date),(insert into dc_bsc_info_bak values (a.insert_time,a.start_time,a.stop_time,a.fill_time,a.ne_class,a. ne_name,_id,a.city_id,a.sdchh_avail_carrie,a.sdcch_traffic,a.sdcch_call_att,a.sdchh_assi_fail_num,a.sdchh_call_drop,a.sdchh_call_block,a.tch_avail_carrier,a.tch_traffic,a.tch_att_num,a.tch_overflow_num,a.tch_assi_failed_num,a.tch_unsuc_num,a.tch_seize_num,a.tch_call_att,a.tch_call_block,a.tch_failed_assi,a.tch_call_seize,a.tch_call_drop,a.gsm1800_traf,a.ho_req,a.ho_succs,a.worst_cell,a.avail_cell,a.acc_cell,a.sdcch_seize_att,a.tch_unsuc_call,a.att_sdcch_seiz,a.flag,press_date,a.vendor_id);更新触发器create trigger tg_update_alarmupdate of alarm_state on alarmreferencing new as a old as bfor each rowwhen (a.alarm_state0)(INSERT INTO LucentSDH_WG_ALARM values( a.alarm_number,a.node_name,a.fault_location,a.a1_time,a.alarm_state,a.fault_type,current,0);五、其他（关于连接的各种用法）表test1、表test2结构:test1:test2:node1 property1node2 property21100a1100e1200a1200b1300b1300a1400d1500f1、内连接select *from test1 a,test2 bwhere a.node1=b.node2结果集：1100a1100e1200a1200b1300b1300a2、左连接select *from test1 a left join test2 b on a.node1=b.node2结果集：1100a1100e1200a1200b1300b1300a1400d3、表a左连接表b,且表b加条件结果集：表b的条件有效且左连接失效(相当于内连接)select *from test1 a left join test2 b on a.node1=b.node2where perty2a结果集：1100a1100e1200a1200b4、表a外连接表b,且表b加条件结果集：表b的条件有效且左连接有效select *from test1 a,outer test2 bwhere perty2aand a.node1=b.node2结果集：1100a1100e1200a1200b1300b1400d5、右连接select *from test1 a right join test2 b on a.node1=b.node2结果集：test2 test11100e1100a1200b1200a1300a1300b1500f6、表a右连接表b,且表a有条件结果集：表a条件有效且右连接失效select *from test1 a right join test2 b on a.node1=b.node2where perty1a结果集：1300a1300b7、表b外连接等价于表a右连接表b,且表a有条件结果集：表a条件有效且右连接有select *from test2 b,outer test1 awhere perty1aand a.node1=b.node2