Orcale存储过程完整例子

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;

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;

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;

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;

LOOP

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

end zgg_test_get_comment;

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

LOOP

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

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

end zgg_test_autocomputer;

OPEN rsCursor for select stdId,math,article,language,music,sport from zgg_test_students  where zgg_test_students.step = step;

Ｖsql varchar(2000);

Vsql:=’ select stdId,math,article,language,music,sport from zgg_test_students  where zgg_test_students.step =’|| step;

OPEN rsCursor for Vsql;

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;

--判断如果评价结果为Ａ则加上相应的成绩

      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;

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;