CREATE OR REPLACE
PROCEDURE PROC_STU_TEA
AS
v_tea_id number(19);--班主任对应id
v_class_id number(19);--班级表的id
v_stu_count int;--统计学生信息数量
v_claz_count int;--统计班级数据
v_cla_count int;--统计班级数据
v_tea_count int;--统计教职工数据
v_sso_count int;--统计sso登陆信息数
v_sso_name varchar(20);--sso登陆名
BEGIN
--显示游标使用，使用for循环
--定义游标，名称为cur_tea
declare cursor cur_tea is
select BIRTH_DATE,SEX,STAFF_ID,name,NATION,SCHOOL_CD from
sxdcp.HR_M_STAFF_BASE@SXDCP order by sid;
begin
--循环游标数据
for teatch in
cur_tea
loop
--根据查询条件，检索ACCT_PERSON表中是否存在相同记录。
select count(*) into v_tea_count from ACCT_PERSON where IDNUMBER =
teatch.STAFF_ID;
--统计判断是否存在sso登录名的教师
select count(*) into v_sso_count from sxdcp.sys_m_user@SXDCP where RELATION_PERSON_CD=teatch.STAFF_ID;
--判断是否有相应数据，如果有则update此记录，否则insert数据。
if v_tea_count = 0 and v_sso_count =0
then
--插入教职工信息
insert into ACCT_PERSON
(ID,BIRTHDAY,GENDER,IDNUMBER,NAME,NATIONALITY,SCHOOLCODE,TYPE)
values(PERSON_SEQ.nextval,teatch.BIRTH_DATE,teatch.SEX,teatch.STAFF_ID,teatch.name,teatch.NATION,teatch.SCHOOL_CD,2);
elsif v_tea_count = 0 and v_sso_count =1 then
--获取教师sso登陆名
select USER_PIN into v_sso_name from sxdcp.sys_m_user@SXDCP where RELATION_PERSON_CD=teatch.STAFF_ID AND rownum
=1;
--插入教职工信息
insert into ACCT_PERSON
(ID,BIRTHDAY,GENDER,IDNUMBER,LOGINNAME,NAME,NATIONALITY,SCHOOLCODE,TYPE)
values(PERSON_SEQ.nextval,teatch.BIRTH_DATE,teatch.SEX,teatch.STAFF_ID,v_sso_name,teatch.name,teatch.NATION,teatch.SCHOOL_CD,2);
elsif v_tea_count = 1 and v_sso_count =1 then
--更新教职工信息
update ACCT_PERSON set
BIRTHDAY=teatch.BIRTH_DATE,GENDER=teatch.SEX,IDNUMBER=teatch.STAFF_ID,LOGINNAME=v_sso_name,NAME=teatch.name,NATIONALITY=teatch.NATION,SCHOOLCODE=teatch.SCHOOL_CD,TYPE=2
where IDNUMBER=teatch.STAFF_ID;
elsif v_tea_count = 1 and v_sso_count =0 then
--更新教职工信息
update ACCT_PERSON set
BIRTHDAY=teatch.BIRTH_DATE,GENDER=teatch.SEX,IDNUMBER=teatch.STAFF_ID,NAME=teatch.name,NATIONALITY=teatch.NATION,SCHOOLCODE=teatch.SCHOOL_CD,TYPE=2
where IDNUMBER=teatch.STAFF_ID;
end if;
end
loop;
--自动关闭游标
end;
--显示游标使用，使用for循环
--定义游标，名称为cur_clazz
declare cursor cur_clazz is
select
s.CLASS_ID,s.CLASS_NM,s.CLASS_CR_DATE,s.CLASS_EXPIRED_DATE,s.GRADE_ID,gr.GRADE_NAME,s.CLASS_LEARNING_PERIOD,s.SCHOOL_CD,s.HEAD_TEACHER
from sxdcp.STU_M_CLASS@SXDCP s, sxdcp.STU_M_GRADE@SXDCP gr,sxdcp.HR_M_STAFF_BASE@SXDCP th where
s.GRADE_ID = gr.GRADE_ID and
th.STAFF_ID=s.HEAD_TEACHER;
begin
--循环游标数据
for clazz in cur_clazz
loop
--根据查询条件，检索ACCT_CLAZZ表中是否存在相同记录。
select count(*) into v_claz_count from ACCT_CLAZZ where
CLASSCODE=clazz.CLASS_ID;
--根据查询条件，检索ACCT_PERSON表中是否存在班主任信息。
select count(*) into v_tea_count from ACCT_PERSON where
IDNUMBER=clazz.HEAD_TEACHER;
--判断是否有相应数据，如果有则update此记录，否则insert数据。
if v_claz_count=0 and v_tea_count=0 then
--插入班级信息记录。
insert into ACCT_CLAZZ
(ID,CLASSCODE,CLASSNAME,CREATEDATE,EXPIREDDATE,GRADECODE,GRADENAME,LEARNINGPERIOD,SCHOOLCODE,SCHOOLYEARCODE)
values(CLAZZ_SEQ.nextval,clazz.CLASS_ID,clazz.CLASS_NM,clazz.CLASS_CR_DATE,clazz.CLASS_EXPIRED_DATE,clazz.GRADE_ID,clazz.GRADE_NAME,clazz.CLASS_LEARNING_PERIOD,clazz.SCHOOL_CD,'');
elsif v_claz_count=0 and v_tea_count=1 then
--检索班主任信息id。
select id into v_tea_id from ACCT_PERSON where
IDNUMBER=clazz.HEAD_TEACHER;
--插入班级信息记录。
insert into ACCT_CLAZZ
(ID,CLASSCODE,CLASSNAME,CREATEDATE,EXPIREDDATE,GRADECODE,GRADENAME,LEARNINGPERIOD,SCHOOLCODE,SCHOOLYEARCODE,COORDINATOR_ID)
values(CLAZZ_SEQ.nextval,clazz.CLASS_ID,clazz.CLASS_NM,clazz.CLASS_CR_DATE,clazz.CLASS_EXPIRED_DATE,clazz.GRADE_ID,clazz.GRADE_NAME,clazz.CLASS_LEARNING_PERIOD,clazz.SCHOOL_CD,'',v_tea_id);
--更新人员类型
update ACCT_PERSON set TYPE=1 where
IDNUMBER=clazz.HEAD_TEACHER;
elsif v_claz_count=1 and v_tea_count=0 then
--更新班级信息记录。
update ACCT_CLAZZ set
CLASSCODE=clazz.CLASS_ID,CLASSNAME=clazz.CLASS_NM,CREATEDATE=clazz.CLASS_CR_DATE,EXPIREDDATE=clazz.CLASS_EXPIRED_DATE,GRADECODE=clazz.GRADE_ID,GRADENAME=clazz.GRADE_NAME,LEARNINGPERIOD=clazz.CLASS_LEARNING_PERIOD,SCHOOLCODE=clazz.SCHOOL_CD,SCHOOLYEARCODE=''
where CLASSCODE=clazz.CLASS_ID;
elsif v_claz_count=1 and v_tea_count=1 then
--检索班主任信息id。