学生管理系统sql

create or replace package TEST_SCM_PKG is-- Author : 刘存

-- Created : 2012-5-6 11:00:00

-- Purpose : 选课、成绩批量录⼊使⽤type t_cursor is ref cursor;--构建选课表数据

procedure Insert_Test_SC; --构建成绩表数据

procedure Insert_Test_SS;

--构建选课临时表数据

procedure Qry_Select_Course(cur_info out t_cursor);--构建成绩临时表数据

procedure Qry_STU_SCORE(stuName in varchar2,cur_info out t_cursor); end TEST_SCM_PKG;

create or replace package body TEST_SCM_PKG is-- Author : 刘存

-- Created : 2012-5-6

-- Purpose : 选课、成绩录⼊--插⼊选课表

procedure Insert_Test_SC isbegin

--更新选课表

insert into test_stu_course (CARD_NO,course_id,is_selected)select aa.card_no,aa.course_id,aa.is_req as is_selected from (select st.card_no,c.course_id,c.is_req from test_stu stleft join test_course c on 1=1) aa

where aa.card_no || to_char(aa.course_id) not in (select sc.card_no || to_char(sc.course_id) from test_stu_course sc);

--将必选课选中

update test_stu_course sc set sc.is_selected=1 where sc.course_id in (select c.course_id from test_course c where c.is_req=1);--将未选中置0

update test_stu_course t set t.is_selected=0 where t.is_selected is null;commit;

end Insert_Test_SC;

--构建成绩表数据

procedure Insert_Test_SS isbegin

--更新成绩表,选中的课程成绩置0

insert into test_stu_score (CARD_NO,course_id,score)

select aa.card_no,aa.course_id,decode(aa.is_selected,1,0,null) as score from (select st.card_no,sc.course_id,sc.is_selected from test_stu stleft join test_stu_course sc on st.card_no = sc.card_no) aa

where aa.card_no || to_char(aa.course_id) not in (select ss.card_no || to_char(ss.course_id) from test_stu_score ss);

-- 将is_selected=1,score=null的的score改为0.

update test_stu_score ss set ss.score=0 where ss.card_no || to_char(ss.course_id) in (select sc.card_no || to_char(sc.course_id) fromtest_stu_course sc where sc.is_selected=1) and ss.score is null;

--将未选修课程分数值设置为null

update test_stu_score ss set ss.score=null where ss.card_no || to_char(ss.course_id) in (select sc.card_no || to_char(sc.course_id) fromtest_stu_course sc where sc.is_selected=0);commit;

end Insert_Test_SS;

--构建选课临时表数据

procedure Qry_Select_Course(cur_info out t_cursor) iscur_course t_cursor;

strDelTable varchar2(4000); --删除临时表字符串iCount number(38); --定义统计课程数变量cID number(5); --定义学号

cName varchar2(50); --定义课程名strQrySql varchar2(4000);strUpdSql varchar2(4000);

begin

strDelTable := 'delete from TEST_STU_COURSE_TMP ';--使⽤前清空临时表

execute immediate strDelTable;

-- 向临时表中插⼊学号、姓名

insert into TEST_STU_COURSE_TMP (CARD_NO,Stu_Name)select st.card_no,st.stu_namefrom test_stu st; -- 获取游标

open cur_course forselect t.course_idfrom test_course torder by t.course_id;

-- 更新临时表课程字段iCount := 0;loop

FETCH cur_course INTO cID;

EXIT WHEN cur_course%NOTFOUND;

strUpdSql := 'update TEST_STU_COURSE_TMP t set t.fld'|| to_char(iCount) || '= (select sc.is_selected from test_stu_course sc wheret.card_no=sc.card_no and sc.course_id=' || cID || ')';execute immediate strUpdSql;iCount := iCount + 1;end loop;

------------- 获取临时表数据 ------------------------ 获取游标

open cur_course forselect t.course_namefrom test_course torder by t.course_id;

strQrySql := 'select t.card_no as 学号, t.stu_name as 姓名';iCount := 0;loop

FETCH cur_course INTO cName;

EXIT WHEN cur_course%NOTFOUND;

strQrySql := strQrySql || ', t.fld'|| to_char(iCount) || ' as ' || cName;iCount := iCount + 1;end loop;

strQrySql := strQrySql || ' from TEST_STU_COURSE_TMP t';commit;

open cur_info for strQrySql;close cur_course;

end Qry_Select_Course ;

--构建成绩临时表数据

procedure Qry_STU_SCORE(stuName in varchar2,cur_info out t_cursor) iscur_course t_cursor;

strDelTable varchar2(4000); --删除临时表字符串iCount number(38); --定义统计课程数变量cID number(5); --定义学号

cName varchar2(50); --定义课程名strQrySql varchar2(4000);strUpdSql varchar2(4000);sSumSql varchar2(4000);

begin

strDelTable := 'delete from TEST_STU_COURSE_TMP ';

sSumSql :='update TEST_STU_COURSE_TMP t set t.Fld19=0';--此处要加0--使⽤前清空临时表

execute immediate strdelTable;

-- 向临时表中插⼊学号、姓名

insert into TEST_STU_COURSE_TMP (CARD_NO,Stu_Name)select st.card_no,st.stu_namefrom test_stu st; --获取游标

open cur_course forselect t.course_idfrom test_course torder by t.course_id;

--更新临时表课程字段iCount := 0;loop

FETCH cur_course INTO cID;

EXIT WHEN cur_course%NOTFOUND;

strUpdSql := 'update TEST_STU_COURSE_TMP t set t.fld'|| to_char(iCount) || '= (select ss.score from test_stu_score ss wheret.card_no=ss.card_no and ss.course_id=' || cID || ')';execute immediate strUpdSql;

if 't.fld' || to_char(iCount) is not null then

sSumSql := sSumSql || '+t.Fld' || to_char(iCount);end if;

iCount := iCount + 1;end loop;

execute immediate sSumSql;

------------- 获取临时表数据 ------------------------ 获取游标

open cur_course forselect t.course_namefrom test_course torder by t.course_id;

strQrySql := 'select t.card_no as 学号, t.stu_name as 姓名';iCount := 0;loop

FETCH cur_course INTO cName;

EXIT WHEN cur_course%NOTFOUND;

strQrySql := strQrySql || ', t.fld'|| to_char(iCount) || ' as ' || cName;iCount := iCount + 1;end loop;

--strQrySql := strQrySql || ' from TEST_STU_COURSE_TMP t';

strQrySql := strQrySql || ',t.fld19 as 总分 from TEST_STU_COURSE_TMP t where 1=1';

--strQrySql := strQrySql || ',t.fld19 as 总分 from TEST_STU_COURSE_TMP t where t.stu_name=' || stuName;if stuName is not null then-- 字符串匹配，类似于

strQrySql := strQrySql || 'and instr(t.stu_name,''' || trim(stuName) || ''')>0 ';end if;commit;

open cur_info for strQrySql;close cur_course; end Qry_STU_SCORE;end TEST_SCM_PKG;