proc2.sql

一个挺好的招生管理系统

--二志愿投档存储过程PROC2
CREATE OR REPLACE PROCEDURE PROC2(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_ZSRS,V_LQFSX,V_LQRS,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',录取志愿='2',录取院校=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;
/