CREATE OR REPLACE PROCEDURE "ALL_COURSE_TYPE_REPORT"
AS
ACTR_COURSE_ID VARCHAR2(20); --科目编号
ACTR_COURSE_NAME VARCHAR2(8); --科目名称
ACTR_YEAR VARCHAR2(4); --年份
ACTR_EXAM_TIMES VARCHAR2(20);--考试次数
ACTR_EXAM_TIME VARCHAR2(100);--考试时间
ACTR_APPLY_TIMES VARCHAR2(8); --报考科次
ACTR_EXAM_COURSE_TIMES VARCHAR2(8);--考试科次
ACTR_PASS_TIMES VARCHAR2(8); --通过科次
ACTR_PASS_PERCENT VARCHAR2(8); --通过率
ACTR_JOIN_COUNT VARCHAR2(8); --参考人数
ACTR_JOIN_PERCENT VARCHAR2(8); --参考率
/**
查询所有科目
*/
CURSOR COURSE IS SELECT SCI.SCI_COURSE_ID,SCI.SCI_COURSE_NAME FROM SAC_COURSE_INFO SCI;
BEGIN
/**
查询当前年份
*/
SELECT TO_CHAR(SYSDATE,'YYYY') INTO YEAR_NO FROM DUAL;
/**
循环结果集
*/
FOR CS IN COURSE LOOP
/**
赋值
*/
ACTR_COURSE_ID := CS.SCI_COURSE_ID;
ACTR_COURSE_NAME := CS.SCI_COURSE_NAME;
/**
查询具体科目
*/
SELECT SUBSTR(SSG.SSG_EXAM_TIME,1,7) AS EXAMTIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||YEAR_NO||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID GROUP BY SUBSTR(SSG.SSG_EXAM_TIME,1,7);
FOR EXAM IN EXAMALL LOOP
ACTR_EXAM_TIME:=EXAMALL.EXAMTIMES;
/**
考试次数
*/
SELECT COUNT(DISTINCT SSG.SSG_EXAM_TIME) AS ACTR_EXAM_TIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID;
/**
报考科次
*/
SELECT COUNT(SSG.SSG_GREAD_ID) AS ACTR_APPLY_TIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID;
/**
考试科次
*/
SELECT COUNT(SSG.SSG_GREAD_ID) AS ACTR_EXAM_COURSE_TIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID AND SSG.SSG_IS_ADD='01';
/**
通过次数
*/
SELECT COUNT(SSG.SSG_GREAD_ID) AS ACTR_PASS_TIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%' AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID AND SSG.SSG_IS_PASS='01';
/**
通过率
*/
SELECT ROUND(ACTR_PASS_TIMES/ACTR_EXAM_COURSE_TIMES*100,2) || '%' AS ACTR_PASS_PERCENT FROM DUAL;
/**
参考人数
*/
SELECT COUNT(DISTINCT SSG.SSG_SSI_STUDENT_ID) AS ACTR_JOIN_COUNT FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID AND SSG.SSG_IS_ADD='01';
/**
参考率
*/
SELECT ROUND(ACTR_JOIN_COUNT/(SELECT COUNT(DISTINCT SSG.SSG_SSI_STUDENT_ID) AS TOTAL_COUNT FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID)*100,2) || '%' AS ACTR_JOIN_PERCENT FROM DUAL;
/**
插入数据库
*/
INSERT INTO ALL_COURSE_TYPE_REPORT (ACTR_ID, ACTR_YEAR,ACTR_COURSE_ID, ACTR_COURSE_NAME, ACTR_EXAM_TIMES, ACTR_EXAM_TIME, ACTR_APPLY_TIMES,ACTR_EXAM_COURSE_TIMES,ACTR_PASS_TIMES,ACTR_PASS_PERCENT,ACTR_JOIN_COUNT,ACTR_JOIN_PERCENT) VALUES (SEQ_ALL_COURSETYPE_REPORT.NEXTVAL, ACTR_YEAR,ACTR_COURSE_ID, ACTR_COURSE_NAME, ACTR_EXAM_TIMES, ACTR_EXAM_TIME, ACTR_APPLY_TIMES,ACTR_EXAM_COURSE_TIMES,ACTR_PASS_TIMES,ACTR_PASS_PERCENT,ACTR_JOIN_COUNT,ACTR_JOIN_PERCENT);
END LOOP;
END LOOP;
COMMIT;
END ALL_COURSE_TYPE_REPORT;
AS
ACTR_COURSE_ID VARCHAR2(20); --科目编号
ACTR_COURSE_NAME VARCHAR2(8); --科目名称
ACTR_YEAR VARCHAR2(4); --年份
ACTR_EXAM_TIMES VARCHAR2(20);--考试次数
ACTR_EXAM_TIME VARCHAR2(100);--考试时间
ACTR_APPLY_TIMES VARCHAR2(8); --报考科次
ACTR_EXAM_COURSE_TIMES VARCHAR2(8);--考试科次
ACTR_PASS_TIMES VARCHAR2(8); --通过科次
ACTR_PASS_PERCENT VARCHAR2(8); --通过率
ACTR_JOIN_COUNT VARCHAR2(8); --参考人数
ACTR_JOIN_PERCENT VARCHAR2(8); --参考率
/**
查询所有科目
*/
CURSOR COURSE IS SELECT SCI.SCI_COURSE_ID,SCI.SCI_COURSE_NAME FROM SAC_COURSE_INFO SCI;
BEGIN
/**
查询当前年份
*/
SELECT TO_CHAR(SYSDATE,'YYYY') INTO YEAR_NO FROM DUAL;
/**
循环结果集
*/
FOR CS IN COURSE LOOP
/**
赋值
*/
ACTR_COURSE_ID := CS.SCI_COURSE_ID;
ACTR_COURSE_NAME := CS.SCI_COURSE_NAME;
/**
查询具体科目
*/
SELECT SUBSTR(SSG.SSG_EXAM_TIME,1,7) AS EXAMTIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||YEAR_NO||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID GROUP BY SUBSTR(SSG.SSG_EXAM_TIME,1,7);
FOR EXAM IN EXAMALL LOOP
ACTR_EXAM_TIME:=EXAMALL.EXAMTIMES;
/**
考试次数
*/
SELECT COUNT(DISTINCT SSG.SSG_EXAM_TIME) AS ACTR_EXAM_TIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID;
/**
报考科次
*/
SELECT COUNT(SSG.SSG_GREAD_ID) AS ACTR_APPLY_TIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID;
/**
考试科次
*/
SELECT COUNT(SSG.SSG_GREAD_ID) AS ACTR_EXAM_COURSE_TIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID AND SSG.SSG_IS_ADD='01';
/**
通过次数
*/
SELECT COUNT(SSG.SSG_GREAD_ID) AS ACTR_PASS_TIMES FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%' AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID AND SSG.SSG_IS_PASS='01';
/**
通过率
*/
SELECT ROUND(ACTR_PASS_TIMES/ACTR_EXAM_COURSE_TIMES*100,2) || '%' AS ACTR_PASS_PERCENT FROM DUAL;
/**
参考人数
*/
SELECT COUNT(DISTINCT SSG.SSG_SSI_STUDENT_ID) AS ACTR_JOIN_COUNT FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID AND SSG.SSG_IS_ADD='01';
/**
参考率
*/
SELECT ROUND(ACTR_JOIN_COUNT/(SELECT COUNT(DISTINCT SSG.SSG_SSI_STUDENT_ID) AS TOTAL_COUNT FROM SAC_STUDENT_GRADE SSG WHERE SSG.SSG_EXAM_TIME LIKE '%'||EXAMALL.EXAMTIMES||'%'AND SSG.SSG_EXAM_COURSE_CODE=CS.SCI_COURSE_ID)*100,2) || '%' AS ACTR_JOIN_PERCENT FROM DUAL;
/**
插入数据库
*/
INSERT INTO ALL_COURSE_TYPE_REPORT (ACTR_ID, ACTR_YEAR,ACTR_COURSE_ID, ACTR_COURSE_NAME, ACTR_EXAM_TIMES, ACTR_EXAM_TIME, ACTR_APPLY_TIMES,ACTR_EXAM_COURSE_TIMES,ACTR_PASS_TIMES,ACTR_PASS_PERCENT,ACTR_JOIN_COUNT,ACTR_JOIN_PERCENT) VALUES (SEQ_ALL_COURSETYPE_REPORT.NEXTVAL, ACTR_YEAR,ACTR_COURSE_ID, ACTR_COURSE_NAME, ACTR_EXAM_TIMES, ACTR_EXAM_TIME, ACTR_APPLY_TIMES,ACTR_EXAM_COURSE_TIMES,ACTR_PASS_TIMES,ACTR_PASS_PERCENT,ACTR_JOIN_COUNT,ACTR_JOIN_PERCENT);
END LOOP;
END LOOP;
COMMIT;
END ALL_COURSE_TYPE_REPORT;