CREATE OR REPLACE PROCEDURE SINGLSCHOOL

(

PICIID IN VARCHAR2

, SCHOOLID IN NUMBER

, SCHETYPE IN number

, SCHENAME OUT VARCHAR2

, ISCORE OUT VARCHAR2

, ISTHRE OUT VARCHAR2

, ISBASE OUT VARCHAR2

, ISONLINE OUT VARCHAR2

, TEACHTO OUT VARCHAR2

, CLASSCOUNT OUT VARCHAR2

, STHCOUNT OUT VARCHAR2

, SCHEID OUT VARCHAR2

) AS

--按照条件查询课程信息

SCHENAMETMP t_app_dic.f_dic_value%TYPE;

CORETMP t_app_dic.f_dic_value%TYPE;

THRETMP t_app_dic.f_dic_value%TYPE;

BASETMP t_app_dic.f_dic_value%TYPE;

ONLINETMP t_app_dic.f_dic_value%TYPE;

TEACHOTMP t_app_dic.f_dic_value%TYPE;

CLASCTMP NUMBER;

STUCTMP NUMBER;

SCHEIDTMP T_APP_SUB_SCHE.ID%TYPE;

 

 

cursor sches (SCHOOLID organization.id%type ,PICIID t_app_pici.id%type,SCHETYPE t_app_sub_sche.f_sche_type%type) is

select sche.f_sche_name,(select f_dic_value from t_app_dic where f_dic_num= sche.f_sche_core and f_dic_name='核心课程') ,

(select f_dic_value from t_app_dic where f_dic_num= sche.f_thre_prac and f_dic_name='理论/实践') ,

(select f_dic_value from t_app_dic where f_dic_num= sche.f_base_pro and f_dic_name='基础/专业') ,

(select f_dic_value from t_app_dic where f_dic_num= sche.F_ONLINE and f_dic_name='是否开网络课程') ,

(select f_dic_value from t_app_dic where f_dic_num= sche.F_TEACH_OBJE and f_dic_name='培养对象') ,

count(sc.f_class_id),sum(c.F_STU_COUNT),SCHE.ID

from t_app_sub_sche sche,t_app_sche_class sc,t_app_class c

where c.id(+)=sc.f_class_id and sche.id= sc.f_sche_id(+) and sche.f_validate = '0' and sche.f_subunit_code=(SCHOOLID||'') and sche.f_subunit_pici_pre = PICIID and sche.f_sche_type=SCHETYPE

group by SCHE.ID,sche.f_sche_name,sche.f_sche_core, sche.f_base_pro, sche.F_ONLINE, sche.F_TEACH_OBJE,sche.f_thre_prac;

BEGIN

SCHENAME:=' ';

ISCORE:=' ';

ISTHRE:=' ';

ISBASE:=' ';

ISONLINE:=' ';

TEACHTO:=' ';

CLASSCOUNT:=' ';

STHCOUNT:=' ';

SCHEID:=' ';

open sches(SCHOOLID,PICIID,SCHETYPE);

LOOP

fetch sches into SCHENAMETMP,CORETMP,THRETMP, BASETMP , ONLINETMP,TEACHOTMP, CLASCTMP, STUCTMP,SCHEIDTMP;

exit when sches%notfound;

SCHENAME:=SCHENAME||SCHENAMETMP||' ,';

ISCORE:=ISCORE||CORETMP||' ,';

ISTHRE:=ISTHRE||THRETMP||' ,';

ISBASE:=ISBASE||BASETMP||' ,';

ISONLINE:=ISONLINE||ONLINETMP||' ,';

TEACHTO:=TEACHTO||TEACHOTMP||' ,';

CLASSCOUNT:=CLASSCOUNT||CLASCTMP||' ,';

STHCOUNT:=STHCOUNT||STUCTMP||' ,';

SCHEID:=SCHEID||SCHEIDTMP||' ,';

end loop;

close sches;

DBMS_OUTPUT.PUT_LINE(SCHENAME||'::'||ISCORE||'::'||ISTHRE||'::'||ISBASE||'::'||ISONLINE||'::'||CLASSCOUNT||'::'||STHCOUNT||'::'||TEACHTO);

END SINGLSCHOOL;