oracle 存储过程 很多实例

oracle 存储过程 很多实例

--绩点排名

--创建临时表
create global temporary table temp_rank(pname VARCHAR2(20),get_GPA float) on commit delete rows;


--存储过程show_rank
create or replace procedure show_rank
as
    cursor per_cursor is
    select pname,get_GPA from temp_rank where get_GPA is not null order by get_GPA desc ;
        cur per_cursor%rowtype;
        begin
            for cur in per_cursor loop
                DBMS_OUTPUT.put_line('名字--'||cur.pname||'   绩点--'||cur.get_GPA);
            end loop;
            execute immediate 'truncate table temp_rank';
        end;


--存储过程queryPer_GPA_for_rank
create or replace procedure queryPer_GPA_for_rank(pno in Student.Sno%type,pname out Student.Sname%type,get_GPA out float)
as
    cursor per_cursor is select distinct course.cname,curriculum.grade,course.ccredit,student.sname from curriculum,course,student where curriculum.sno=pno and course.cno=curriculum.cno and student.sno=pno;
    ans float :=0;
    result0 float :=0;
    zongGPA float :=0;
    tempGPA float :=0;
    flag boolean:=true;
        begin
            for cur in per_cursor loop
                exit when per_cursor%notfound;
                zongGPA:=zongGPA+cur.ccredit;
                if cur.grade is null then begin zongGPA:=zongGPA-cur.ccredit;end;
                elsif (cur.grade between 0 and 59) then begin ans:=ans+0;tempGPA:=0;end;
                elsif (cur.grade between 61 and 63) then begin ans:=ans+1.3*cur.ccredit;tempGPA:=1.3;end;
                elsif (cur.grade between 64 and 64) then begin ans:=ans+1.5*cur.ccredit;tempGPA:=1.5;end;
                elsif (cur.grade between 65 and 67) then begin ans:=ans+1.7*cur.ccredit;tempGPA:=1.7;end;
                elsif (cur.grade between 68 and 71) then begin ans:=ans+2*cur.ccredit;  tempGPA:=2;  end;
                elsif (cur.grade between 72 and 74) then begin ans:=ans+2.3*cur.ccredit;tempGPA:=2.3;end;
                elsif (cur.grade between 75 and 77) then begin ans:=ans+2.7*cur.ccredit;tempGPA:=2.7;end;
                elsif (cur.grade between 78 and 81) then begin ans:=ans+3*cur.ccredit;  tempGPA:=3;  end;
                elsif (cur.grade between 82 and 84) then begin ans:=ans+3.3*cur.ccredit;tempGPA:=3.3;end;
                elsif (cur.grade between 85 and 89) then begin ans:=ans+3.7*cur.ccredit;tempGPA:=3.7;end;
                elsif (cur.grade between 90 and 94) then begin ans:=ans+4*cur.ccredit;  tempGPA:=4;  end;
                elsif (cur.grade between 95 and 100) then begin ans:=ans+4.3*cur.ccredit;tempGPA:=4.3;end ;
                end if;
                if flag then
                    begin
--                         DBMS_OUTPUT.put_line('姓名'||cur.sname);
                        pname:=cur.sname;
                        flag:=false;
                    end;
                end if ;
            end loop;
            if zongGPA=0 then
                get_GPA :=null;
            else
--                 DBMS_OUTPUT.put_line('总绩点为--'||round(ans/zongGPA,4));
                get_GPA :=round(ans/zongGPA,4);
            end if;
        end;



--存储过程GPA_rank
create or replace procedure GPA_rank
as
    cursor per_cursor is
    select distinct sno from curriculum;
        cur per_cursor%rowtype;
    get_GPA_0 float;
    pname_0 Student.Sname%type;
    begin
        for cur in per_cursor loop
            queryPer_GPA_for_rank(cur.sno,pname_0,get_GPA_0);
            insert into temp_rank(pname,get_GPA) values (pname_0,get_GPA_0);
        end loop ;
        show_rank();
    end;


--调用

call GPA_rank();

create procedure course_scheduling(p2 in course.cname%type,p3 in course.ccredit%type,p4 in course.type%type)
as
    p11 course.cno%type ;
    begin
        select max(cno)+1 into p11 from course ;
        insert into course(cno, cname, ccredit,TYPE) VALUES (p11,p2,p3,p4);
        DBMS_OUTPUT.put_line('插入成功');
    end;

create procedure optional_course(p1 in curriculum.sno%type,p2 in curriculum.cno%type)
as
    cursor per_cursor is
    select distinct teach.week, teach.day,teach.time,course.type from teach,course where teach.cno=p2 and teach.cno=course.cno and course.type like '%选修%';
        cur per_cursor%rowtype;
    cursor com_cursor is
    select distinct week,day,time from curriculum where sno=p1;
        com com_cursor%rowtype;
    begin
        for cur in per_cursor loop
            exit when per_cursor%notfound;
            DBMS_OUTPUT.put_line(cur.week||cur.day||cur.time);
            for com in com_cursor loop
                exit when com_cursor%notfound;
                if cur.time=com.time and cur.day=com.day and cur.week=com.week then
                    raise_application_error(-20005,'时间冲突，不可选！');
                end if;
            end loop;
        end loop;
        for cur in per_cursor loop
            exit when per_cursor%notfound;
            insert into curriculum(sno, cno, week, day, time) values (p1,p2,cur.week,cur.day,cur.time);
        end loop;
    end;

create procedure queryclass_sche(pno in Student.classname%type)
as
    cursor per_cursor is
        select course.cname,curriculum.day,curriculum.time,curriculum.week from curriculum,student,course
    where student.classname=pno and course.type in('通识类必修课程','专业必修课程') and curriculum.cno=course.cno;
--     select course.cname,curriculum.day,curriculum.time,curriculum.week from curriculum,student,course
--     where  student.sno=curriculum.sno and trim(student.classname) <> trim(pno) and curriculum.type in('通识类必修课程','专业必修课程') and curriculum.cno=course.cno;
       cur per_cursor%rowtype;
    begin
        DBMS_OUTPUT.put_line('pno:'||pno);
        --DBMS_OUTPUT.put_line('课程:'||cur.cname||'  星期：'||cur.day||'  节次：'||cur.time||'  上课周数：'||cur.week);
        for cur in per_cursor loop
--             exit when per_cursor%notfound;
            DBMS_OUTPUT.put_line('课程:'||cur.cname||'  星期：'||cur.day||'  节次：'||cur.time||'  上课周数：'||cur.week);
            exit when per_cursor%notfound;
        end loop;
    end;

create procedure queryPer_GPA(pno in HELLO.Student.Sno%type)
as
    cursor per_cursor is select distinct HELLO.course.cname,HELLO.curriculum.grade,HELLO.course.ccredit from HELLO.curriculum,HELLO.course where HELLO.curriculum.sno=pno and HELLO.course.cno=HELLO.curriculum.cno;
    ans float :=0;
    result0 float :=0;
    zongGPA float :=0;
    tempGPA float :=0;
        begin
            for cur in per_cursor loop
                exit when per_cursor%notfound;
                zongGPA:=zongGPA+cur.ccredit;
                if cur.grade is null then begin zongGPA:=zongGPA-cur.ccredit;end;
                elsif (cur.grade between 0 and 59) then begin ans:=ans+0;tempGPA:=0;end;
                elsif (cur.grade between 61 and 63) then begin ans:=ans+1.3*cur.ccredit;tempGPA:=1.3;end;
                elsif (cur.grade between 64 and 64) then begin ans:=ans+1.5*cur.ccredit;tempGPA:=1.5;end;
                elsif (cur.grade between 65 and 67) then begin ans:=ans+1.7*cur.ccredit;tempGPA:=1.7;end;
                elsif (cur.grade between 68 and 71) then begin ans:=ans+2*cur.ccredit;  tempGPA:=2;  end;
                elsif (cur.grade between 72 and 74) then begin ans:=ans+2.3*cur.ccredit;tempGPA:=2.3;end;
                elsif (cur.grade between 75 and 77) then begin ans:=ans+2.7*cur.ccredit;tempGPA:=2.7;end;
                elsif (cur.grade between 78 and 81) then begin ans:=ans+3*cur.ccredit;  tempGPA:=3;  end;
                elsif (cur.grade between 82 and 84) then begin ans:=ans+3.3*cur.ccredit;tempGPA:=3.3;end;
                elsif (cur.grade between 85 and 89) then begin ans:=ans+3.7*cur.ccredit;tempGPA:=3.7;end;
                elsif (cur.grade between 90 and 94) then begin ans:=ans+4*cur.ccredit;  tempGPA:=4;  end;
                elsif (cur.grade between 95 and 100) then begin ans:=ans+4.3*cur.ccredit;tempGPA:=4.3;end ;
                end if;
                if cur.grade is not null then
                    begin
                       DBMS_OUTPUT.put_line(cur.cname||'   绩点--'||tempGPA);
                    end;
                end if ;

                --DBMS_OUTPUT.put_line('累计学分为--'||zongGPA);
            end loop;
            if zongGPA=0 then
                DBMS_OUTPUT.put_line('成绩还未录入，请耐心等候!');
            else
                DBMS_OUTPUT.put_line('总学分为--'||zongGPA);
                DBMS_OUTPUT.put_line('ans--'||ans);
                DBMS_OUTPUT.put_line('总绩点为--'||round(ans/zongGPA,4));

            end if;
        end;
/

create procedure queryPer_Grade(pno in Student.Sno%type)
as
    cursor per_cursor is
    select distinct cname,grade from course,curriculum where sno=pno and curriculum.cno=course.cno;
        cur per_cursor%rowtype;
--         open per_cursor;
    begin
--         open per_cursor;
        for cur in per_cursor loop
            exit when per_cursor%notfound;
            --成绩为空取零
            --DBMS_OUTPUT.put_line('科目--'||cur.cname||'   成绩--'||nvl(cur.grade,0));
            --成绩为空不操作
            DBMS_OUTPUT.put_line('科目--'||cur.cname||'   成绩--'||cur.grade);
            --成绩为空替换为‘未录入’，还没想到
        end loop;
--         close per_cursor;
    end;
/

create procedure queryPer_Sche(pno in Student.Sno%type)
as
    cursor per_cursor is
    select * from curriculum where sno=pno;
        cur per_cursor%rowtype;
    begin
        for cur in per_cursor loop
            exit when per_cursor%notfound;
            DBMS_OUTPUT.put_line('数据是：'||'学号--'||cur.sno||'班号--'||cur.cno||'day--'||cur.day||'type--'||cur.type||'grade--'||cur.grade||'time--'||cur.time||'week--'||cur.week);
        end loop;
    end;
/

create procedure querystu_info(p1 in teach.tno%type,p2 in teach.cno%type)
as
    cursor per_cursor is
    select distinct curriculum.sno,sname  from curriculum,teach,student
    where teach.tno=p1 and teach.cno=p2 and curriculum.cno=teach.cno and curriculum.sno=student.sno;
--     and curriculum.day=teach.day and curriculum.week=teach.week and curriculum.time=teach.time;
        cur per_cursor%rowtype;
    begin
        for cur in per_cursor loop
            exit when per_cursor%notfound;
            DBMS_OUTPUT.put_line('上课学生学号：'||cur.sno||'  学生姓名：'||cur.sname);
        end loop;
    end;
/

create procedure queryteacher_sche(pno in teacher.tno%type)
as
    cursor per_cursor is
    select * from teach where tno=pno;
        cur per_cursor%rowtype;
    begin
        for cur in per_cursor loop
            exit when per_cursor%notfound;
            DBMS_OUTPUT.put_line('教工号：'||cur.tno||' 星期：'||cur.day||' 节次：'||cur.time||' 周数：'||cur.week);
       end loop;
    end;
/

create procedure record(p1 in teach.cno%type,p2 in curriculum.sno%type,p3 in curriculum.grade%type)
as
    grade1 curriculum.grade%type :=0;
    cname1 course.cname%type :=null;
    sname1 student.sname%type :=null;
    begin
    select distinct student.sname,course.cname,curriculum.grade into sname1,cname1,grade1
    from student,curriculum,teach,course where teach.cno=p1
    and curriculum.sno=p2
    and curriculum.sno=student.sno
    and teach.cno=curriculum.cno
    and curriculum.cno=course.cno;
    DBMS_OUTPUT.put_line(' 学生名字： '||sname1||' 课程：'||cname1);
    DBMS_OUTPUT.put_line(' 原成绩：'||grade1);
    update curriculum set grade=p3 where sno=p2 and cno =p1;
    DBMS_OUTPUT.put_line(' 现成绩：'||p3);
end;
/

--end