proc5.sql

一个挺好的招生管理系统

--院校自主投档存储过程PROC5
CREATE OR REPLACE PROCEDURE PROC5(P_YXBH NUMBER)
		AS
		 V_ZSRS NUMBER(3);
		 V_LQFSX NUMBER(3);
 		V_YXMC VARCHAR2(30);
                V_LQRS NUMBER(3);
 		V_COUNT NUMBER(3);
		 CURSOR STU_CURSOR IS SELECT * FROM STUDENT 
 		WHERE 一志愿=P_YXBH AND 录取状态=0 ORDER BY 总分 DESC;
		BEGIN
SELECT 录取人数,招生人数,录取分数线,院校名称 INTO V_LQRS,V_ZSRS,V_LQFSX,V_YXMC FROM COLLEGE WHERE 院校编号=P_YXBH;--取院校信息
 		 V_COUNT:=V_LQRS;
 		 DBMS_OUTPUT.PUT_LINE('院校名称:'||V_YXMC||'一志愿投档开始');
  		DBMS_OUTPUT.PUT_LINE('--------------------- ----------------------------');
		  FOR STU_REC IN STU_CURSOR LOOP
		   EXIT WHEN V_COUNT>=V_ZSRS;
  		 IF(STU_REC.总分>=V_LQFSX) THEN
  		 UPDATE STUDENT SET 录取状态='1',录取志愿='4',录取院校=P_YXBH,录取日期=SYSDATE,
		操作人=USER WHERE 编号= STU_REC.编号;
  		 DBMS_OUTPUT.PUT_LINE(' 编号:'|| STU_REC.编号||' 姓名:'||STU_REC.姓名||' 总分:'|| STU_REC.总分);
	V_COUNT:=V_COUNT+1;
		   END IF;    
  		END LOOP;
 		 DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
UPDATE COLLEGE SET 录取人数=V_COUNT WHERE 院校编号=P_YXBH;
  		COMMIT;
		END;
/