Orcale存储过程完整例子
1.1 学生成绩计算基本信息
1.1.1 创建学生成绩表
现假设存在两张表,一张是学生成绩表(zgg_test_students) ,字段为:
stdId,math,article,language,music,sport,total,average,step
创建表语句:
create table ZGG_TEST_STUDENTS
(
STDID NUMBER(38),
MATH NUMBER,
ARTICLE NUMBER,
LANGUAGE NUMBER,
MUSIC NUMBER,
SPORT NUMBER,
TOTAL NUMBER,
AVERAGE NUMBER,
STEP NUMBER
)
插入数据:
delete from XNDB_DATA_USER.ZGG_TEST_STUDENTS;
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (1, 64, 71, 94, 94, 63, null, null, 24);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (2, 75, 46, 38, 31, 0, null, null, 23);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (3, 11, 89, 8, 29, 2, null, null, 22);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (4, 3, 50, 52, 81, 62, null, null, 21);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (5, 42, 45, 65, 56, 63, null, null, 20);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (6, 43, 93, 58, 49, 99, null, null, 19);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (7, 57, 31, 28, 2, 65, null, null, 18);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (8, 69, 9, 55, 72, 81, null, null, 17);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (9, 93, 57, 31, 92, 79, null, null, 16);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (10, 57, 6, 76, 43, 69, null, null, 15);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (11, 65, 78, 48, 18, 1, null, null, 14);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (12, 96, 89, 38, 15, 33, null, null, 13);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (13, 80, 67, 51, 27, 13, null, null, 12);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (14, 32, 15, 59, 46, 39, null, null, 11);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (15, 47, 15, 0, 81, 9, null, null, 10);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (16, 49, 44, 28, 95, 61, null, null, 9);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (17, 22, 5, 17, 53, 36, null, null, 8);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (18, 79, 41, 11, 5, 54, null, null, 7);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (19, 99, 93, 99, 41, 86, null, null, 6);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (20, 23, 43, 59, 30, 7, null, null, 5);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (21, 76, 42, 3, 33, 76, null, null, 4);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (22, 97, 49, 35, 46, 19, null, null, 3);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (23, 95, 1, 21, 76, 57, null, null, 2);
insert into XNDB_DATA_USER.ZGG_TEST_STUDENTS (STDID, MATH, ARTICLE, LANGUAGE, MUSIC, SPORT, TOTAL, AVERAGE, STEP)
values (24, 0, 20, 29, 95, 64, null, null, 1);
commit;
1.1.2 创建一个学生课外成绩表
(ZGG_TEST_PARCTICE), 字段为:stdId,parctice,comm
create table ZGG_TEST_PARCTICE
(
STDID NUMBER(38),
PARCTICE NUMBER,
COMM VARCHAR2(4)
)
插入数据
delete from XNDB_DATA_USER.ZGG_TEST_PARCTICE;
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (1, 90, 'A');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (2, 70, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (3, 71, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (4, 60, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (5, 92, 'A');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (6, 56, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (7, 60, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (8, 51, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (9, 68, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (10, 91, 'A');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (11, 75, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (12, 80, 'B');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (13, 80, 'B');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (14, 81, 'B');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (15, 52, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (16, 90, 'A');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (17, 51, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (18, 58, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (19, 59, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (20, 98, 'A');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (21, 89, 'B');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (22, 64, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (23, 60, 'C');
insert into XNDB_DATA_USER.ZGG_TEST_PARCTICE (STDID, PARCTICE, COMM)
values (24, 87, 'B');
commit;
1.1.3 需求
要进行的处理为通过存储过程自动计算出每位学生的总成绩和平均成绩,单步执行输入参数step达到对单个学生成绩的计算,如果学生在课外课程中获得的评价为A,就在总成绩上加200分。
1.2 网上方法
1.2.1 建立存储过程程序包:
create or replace package zgg_test_myPackage is
type stdInfo is record(stdId int,comm varchar(4));
type myArray is table of stdInfo index by binary_integer;
end zgg_test_myPackage;
1.2.2 建立取得评价的存储过程:
create or replace procedure zgg_test_get_comment(commentArray out zgg_test_myPackage.myArray) is
--recommentArray zgg_test_myPackage.myArray;
rs SYS_REFCURSOR;
record zgg_test_myPackage.stdInfo;
stdId varchar(30);
comm varchar(10);
i number;
begin
open rs for select stdId,comm from ZGG_TEST_PARCTICE;
i := 1;
fetch rs into stdId,comm;
exit when rs%NOTFOUND;
record.stdId := stdId;
record.comm := comm;
commentArray(i) := record;
dbms_output.put_line(commentArray(i).stdid);
i:=i + 1;
end
end zgg_test_get_comment;
1.2.3 建立处理的存储过程:
create or replace procedure zgg_test_autocomputer(step in number ) is
rsCursor SYS_REFCURSOR;
commentArray zgg_test_myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId int;
record zgg_test_myPackage.stdInfo;
vsql varchar(2000);
nsql varchar(2000);
i int;
j int;
begin
i := 1;
j:=1;
zgg_test_get_comment(commentArray); -- 调用名为get_comment() 的存储过程获取学生课外评分信息
--dbms_output.put_line('total: ' || zgg_test_get_comment(commentArray));
/*nsql:='select stdId,math,article,language,music,sport from zgg_test_students t where t.step = '||step;
dbms_output.put_line('nsql: ' || j);*/
OPEN rsCursor for select stdId,math,article,language,music,sport from zgg_test_students where zgg_test_students.step = step;
dbms_output.put_line('step: ' || step);
j:=j+1;
dbms_output.put_line('j: ' || j);
fetch rsCursor into stdId,math,article,language,music,sport;
exit when rsCursor%NOTFOUND;
dbms_output.put_line('rsCursor: ' || stdid);
total := math + article + language + music + sport;
for i in 1..commentArray.count LOOP
record := commentArray(i);
if stdId = record.stdId then
if record.comm = 'A' then
total := total + 200;
dbms_output.put_line(record.stdid ||' '||record.comm||' '||total);
end if;
end if;
end
<<continue>> average := total / 5;
vsql:='update zgg_test_students t set t.total='||total||',average='||average||' where t.stdId ='|| stdId ;
execute immediate vsql;
commit;
end
end zgg_test_autocomputer;
由于上面不知为啥输入的参数step并没有起到应的作用,每次执行都是对所有的数据进行修改。
1.3 改进方法
改进之外在于将
OPEN rsCursor for select stdId,math,article,language,music,sport from zgg_test_students where zgg_test_students.step = step;
改为:
Vsql varchar(2000);
Vsql:=’ select stdId,math,article,language,music,sport from zgg_test_students where zgg_test_students.step =’|| step;
OPEN rsCursor for Vsql;
通过上述修改可以达到需求的目的。
1.4 简化方法
直接创建一个存储过程对其进行处理:
create or replace procedure zgg_get_sqh(Vstep zgg_test_students.step%type) is
-------以zgg_test_students中的字段创建对应的变量
Vstdid zgg_test_students.stdid%type;
Vtotal zgg_test_students.total%type;
Vaverage zgg_test_students.average%type;
Vmath zgg_test_students.math%type;
Varticle zgg_test_students.article%type;
Vmusic zgg_test_students.music%type;
Vsport zgg_test_students.sport%type;
Vlanguage zgg_test_students.language%type;
-----------以zgg_test_parctice中的comm字段创建对应的变量
Vcomm zgg_test_parctice.comm%type;
-------------
Vsql varchar(2000);
-----------
cursor rs is select stdId,math,article,language,music,sport from zgg_test_students where zgg_test_students.stdid=Vstep;
---------------
begin
open rs;--打开游标
loop
--将游标的值分别赋给相应的变量
fetch rs into Vstdid,Vmath,Varticle,Vlanguage,Vmusic,Vsport;
exit when rs%notfound;
--取出stdid的评价
select p.comm into Vcomm from zgg_test_parctice p where p.stdid=Vstdid;
Vtotal:=0;
Vtotal:=Vstdid+Vmath+Varticle+Vlanguage+Vmusic+Vsport;
--判断如果评价结果为A则加上相应的成绩
if Vcomm='A' then
Vtotal:=Vtotal+200;
end if;
Vaverage:=Vtotal/5;
Vsql:='update zgg_test_students t set t.total='||Vtotal||',t.average='||Vaverage||' where t.stdid='||Vstdid;
dbms_output.put_line('Vsql:'||Vsql);
execute immediate Vsql;
commit;
end loop;
end;
对上述方法再进一步改进,当如果step值为时则对所有的学生进行处理。
create or replace procedure zgg_get_sqh(Vstep zgg_test_students.step%type) is
Vstdid zgg_test_students.stdid%type;
Vtotal zgg_test_students.total%type;
Vaverage zgg_test_students.average%type;
Vmath zgg_test_students.math%type;
Varticle zgg_test_students.article%type;
Vmusic zgg_test_students.music%type;
Vsport zgg_test_students.sport%type;
Vlanguage zgg_test_students.language%type;
Vcomm zgg_test_parctice.comm%type;
Vsql varchar(2000);
cursor rs is select stdId,math,article,language,music,sport from zgg_test_students where zgg_test_students.stdid=Vstep;
cursor ars is select stdId,math,article,language,music,sport from zgg_test_students;
begin
if Vstep=0 then
open ars;
loop
fetch ars into Vstdid,Vmath,Varticle,Vlanguage,Vmusic,Vsport;
exit when ars%notfound;
select p.comm into Vcomm from zgg_test_parctice p where p.stdid=Vstdid;
Vtotal:=0;
Vtotal:=Vstdid+Vmath+Varticle+Vlanguage+Vmusic+Vsport;
if Vcomm='A' then
Vtotal:=Vtotal+200;
end if;
Vaverage:=Vtotal/5;
Vsql:='update zgg_test_students t set t.total='||Vtotal||',t.average='||Vaverage||' where t.stdid='||Vstdid;
dbms_output.put_line('Vsql:'||Vsql);
execute immediate Vsql;
commit;
end loop;
else
open rs;
loop
fetch rs into Vstdid,Vmath,Varticle,Vlanguage,Vmusic,Vsport;
exit when rs%notfound;
select p.comm into Vcomm from zgg_test_parctice p where p.stdid=Vstdid;
Vtotal:=0;
Vtotal:=Vstdid+Vmath+Varticle+Vlanguage+Vmusic+Vsport;
if Vcomm='A' then
Vtotal:=Vtotal+200;
end if;
Vaverage:=Vtotal/5;
Vsql:='update zgg_test_students t set t.total='||Vtotal||',t.average='||Vaverage||' where t.stdid='||Vstdid;
dbms_output.put_line('Vsql:'||Vsql);
execute immediate Vsql;
commit;
end loop;
end if;
end;
1.5 处理结果
|
STDID |
MATH |
ARTICLE |
LANGUAGE |
MUSIC |
SPORT |
TOTAL |
AVERAGE |
STEP |
1 |
1 |
64 |
71 |
94 |
94 |
63 |
587 |
117.4 |
24 |
2 |
2 |
75 |
46 |
38 |
31 |
0 |
192 |
38.4 |
23 |
3 |
3 |
11 |
89 |
8 |
29 |
2 |
142 |
28.4 |
22 |
4 |
4 |
3 |
50 |
52 |
81 |
62 |
252 |
50.4 |
21 |
5 |
5 |
42 |
45 |
65 |
56 |
63 |
476 |
95.2 |
20 |
6 |
6 |
43 |
93 |
58 |
49 |
99 |
348 |
69.6 |
19 |
7 |
7 |
57 |
31 |
28 |
2 |
65 |
190 |
38 |
18 |
8 |
8 |
69 |
9 |
55 |
72 |
81 |
294 |
58.8 |
17 |
9 |
9 |
93 |
57 |
31 |
92 |
79 |
361 |
72.2 |
16 |
10 |
10 |
57 |
6 |
76 |
43 |
69 |
461 |
92.2 |
15 |
11 |
11 |
65 |
78 |
48 |
18 |
1 |
221 |
44.2 |
14 |
12 |
12 |
96 |
89 |
38 |
15 |
33 |
283 |
56.6 |
13 |
13 |
13 |
80 |
67 |
51 |
27 |
13 |
251 |
50.2 |
12 |
14 |
14 |
32 |
15 |
59 |
46 |
39 |
205 |
41 |
11 |
15 |
15 |
47 |
15 |
0 |
81 |
9 |
167 |
33.4 |
10 |
16 |
16 |
49 |
44 |
28 |
95 |
61 |
493 |
98.6 |
9 |
17 |
17 |
22 |
5 |
17 |
53 |
36 |
150 |
30 |
8 |
18 |
18 |
79 |
41 |
11 |
5 |
54 |
208 |
41.6 |
7 |
19 |
19 |
99 |
93 |
99 |
41 |
86 |
437 |
87.4 |
6 |
20 |
20 |
23 |
43 |
59 |
30 |
7 |
382 |
76.4 |
5 |
21 |
21 |
76 |
42 |
3 |
33 |
76 |
251 |
50.2 |
4 |
22 |
22 |
97 |
49 |
35 |
46 |
19 |
268 |
53.6 |
3 |
23 |
23 |
95 |
1 |
21 |
76 |
57 |
273 |
54.6 |
2 |
24 |
24 |
0 |
20 |
29 |
95 |
64 |
232 |
46.4 |
1 |