存储过程入门实例

1、存储过程备忘1 存储过程入门：-本例的作用：将一个变量赋值，再输出到屏幕（红字为必须结构）create or replace procedure TEST is v_details number(8); begin v_details :=1; dbms_output.put_line('stn是:'|v_details);exception when others thendbms_output.put_line('其他异常出现!');end TEST;2 两张汇总表的存储过程create or replace procedure WLRC_T_SUM is

2、begin-以下过程为删除两张汇总表中 当天之前已做过的统计数据delete from t_bnds_in_out_sum where to_char(s_date,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd'); delete from t_bnds_org_sum where to_char(s_date,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd');-插入两张汇总表：将细目表的当日数据汇总，按站名、日期、上下行将数据分别相加。 inse

3、rt into T_BNDS_IN_OUT_SUM select stn_code, s_date, train_dir, in_adm, out_adm, sum(total_cars) totalcar, sum(e_cars) ecar, sum(l_cars) lcar, sum(l_af_cars) lafcar, sum(l_no_stn_cars) lnostncar, sum(af_d_cars) afdcar, sum(af_ad_cars) afadcar, sum(af_z_cars) afzcar, sum(af_fz_cars) affzcar, sum(af_sz_

4、cars) afszcar FROM T_BNDS_IN_OUT_DETAIL WHERE to_char(s_date,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd') GROUP BY stn_code, s_date, train_dir, in_adm, out_adm; insert into T_BNDS_ORG_SUM select stn_code, s_date, train_dir, in_adm, out_adm, org_adm,dest_adm, sum(l_cars) lcar, sum(l_

5、af_cars) lafcar, sum(af_d_cars) afdcar, sum(af_ad_cars) afadcar, sum(af_z_cars) afzcar, sum(af_fz_cars) affzcar, sum(af_sz_cars) afszcar FROM T_BNDS_ORG_DEST_DETAIL WHERE to_char(s_date,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd') GROUP BY stn_code, s_date, train_dir, in_adm, out_ad

6、m,org_adm,dest_adm;-提交以前执行的过程。注意：重要！ 否则直接rollback，不插入数据。 commit;-异常处理：exception when no_data_found thendbms_output.put_line('出现异常,无数据!');when others thenrollback;dbms_output.put_line('其他异常出现!');end WLRC_T_SUM;3 存储过程中的循环实例本例作用说明：存在细目表DETAILS，字段为：站代码stn统计日期sdate车次上下行标志traindir(0/1)接入局交

7、出局车总数total违流数afcar1BFF2010-12-1298765431NF4852AYF2010-12-1223456780FP5263AYF2010-12-1212345670FP5054AYF2010-12-1234567891PF5145BFF2010-12-1245678901NF49106BFF2010-12-1232112341NF522将细目表中内容按站名，统计日期，上下行进行累加统计，得出某站某天某个方向通过的车的总数及违流数。统计表sums，字段为：站代码stn统计日期sdate上下行标志traindir(0/1)车总数total违流数afcar运行以下三种循环的结

8、果sums均为：站代码stn统计日期sdate上下行标志traindir(0/1)车总数total违流数afcar1AYF2010-12-120102112BFF2010-12-121149173AYF2010-12-1215143.1 一般循环-创建ORACLE存储过程TEST,定义变量v_sqlstrcreate or replace procedure TEST is v_sqlstr varchar(1000);-定义游标c，指向从细目表中取出的按站名、日期、上下行标志汇总的车数 cursor c is SELECT stn, sdate, traindir, sum(total) t

9、otal, sum(afcar) afcar FROM DETAILS GROUP BY STN,SDATE,TRAINDIR;-定义结构体变量v_details,类型与c游标中的变量们相同 v_details c%rowtype;-开始处理过程：将本日之前的汇总记录删除，打开游标开始循环，将游标中的数放入结构体变量中-再将游标中的数插入sums表中，执行提交，关闭游标。BEGIN delete from sums where to_char(sdate,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd');- delete

10、 from sums where to_char(sdate,'yyyy-mm-dd') = to_char(to_date('2010-12-09','yyyy-mm-dd'),'yyyy-mm-dd');-删除某日期的记录 OPEN C; LOOP FETCH c INTO v_details; EXIT WHEN c%NOTFOUND;- v_sqlstr:='insert into sums (stn,sdate,traindir,total,afcar) ' |'values (''

11、;'|v_details.stn|''','''|v_details.sdate|''', '''|v_details.traindir|''','''|v_details.total|''', '''|v_details.afcar|''')' EXECUTE immediate v_sqlstr; commit;-or-另一种插入写法，此方法不用定义v_sqls

12、tr- insert into sums (stn,sdate,traindir,total,afcar) values (v_details.stn,v_details.sdate, v_details.traindir,v_details.total, v_details.afcar); commit;- END LOOP;CLOSE c;3.2 WHILE循环create or replace procedure TEST is cursor c is SELECT stn, sdate, traindir, sum(total) total, sum(afcar) afcar FROM

13、 DETAILS GROUP BY STN,SDATE,TRAINDIR;v_details c%rowtype;BEGIN delete from sums where to_char(sdate,'yyyy-mm-dd') = to_char(to_date('2010-12-12','yyyy-mm-dd'),'yyyy-mm-dd'); OPEN c; FETCH c INTO v_details; WHILE c%FOUND LOOP insert into sums (stn,sdate,traindir,total,

14、afcar) values (v_details.stn,v_details.sdate, v_details.traindir,v_details.total, v_details.afcar); COMMIT; FETCH C INTO v_details; END LOOP;CLOSE c;EXCEPTION when no_data_found thendbms_output.put_line('出现异常,无数据!');when others thendbms_output.put_line('其他异常出现!');end TEST;3.3 FOR循环用法

15、create or replace procedure TEST is cursor c is select stn, sdate, traindir, sum(total) total, sum(afcar) afcar FROM DETAILS GROUP BY STN,SDATE,TRAINDIR; v_details c%rowtype;BEGIN delete from sums where to_char(sdate,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd'); FOR v_details IN c L

16、OOP insert into sums (stn,sdate,traindir,total,afcar) values (v_details.stn,v_details.sdate, v_details.traindir,v_details.total, v_details.afcar); commit; END LOOP; exception when no_data_found thendbms_output.put_line('出现异常,无数据!');when others thendbms_output.put_line('其他异常出现!');roll

17、back;end TEST;-另一种写法，不定义游标-create or replace procedure TEST isBEGIN delete from sums where to_char(sdate,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd'); FOR v_details IN (select stn, sdate, traindir, sum(total) total, sum(afcar) afcar FROM DETAILS GROUP BY STN,SDATE,TRAINDIR) LOOP insert into sums (stn,sdate,traindir,total,afcar) values (v_details.stn,v_details.sdate, v_details.traindir,v_details.total, v_details.afcar); commit; END LOOP;end