/*
Function calculates internal marks for a student based on his attendance.
Showcase:
1. Function with a parameter and returning INT value.
2. IF...ELSE construct.
3. RETURN clause
*/ 
CREATE OR REPLACE FUNCTION INTERNAL_MARKS_CALCULATOR
(
	ROLL                       IN      	INT DEFAULT NULL
)
RETURN INT
AS
	VAR                        FLOAT;
	RET                        INT;  
BEGIN  	
	 SELECT CAST(NUMBER_OF_DAYS_PRESENT AS FLOAT) / CAST(TOTAL_DAYS AS FLOAT) INTO VAR 
	FROM  STUDENT_ATTENDANCE_VIEW 
	WHERE	 ROLL_NO  = INTERNAL_MARKS_CALCULATOR.ROLL; 
	IF( VAR > 0.7) THEN  	
	RET  :=  20;
	NULL;
	ELSE  	
	RET  :=  10;
	NULL;
	END IF; 
	RETURN  RET;
END;
/



 /*
Function calculates date of payment of FEES based on Date of Joining.
Showcase:
1. Function returning DATE data type.
*/ 
CREATE OR REPLACE FUNCTION FEES_PAYMENT_DATE
(
	ROLL                       IN      	INT DEFAULT NULL
)
RETURN DATE
AS
	VAR                        DATE;  
BEGIN  	
	 SELECT (TO_DATE(DOJ) + ( 1 * 10 ) ) INTO VAR 
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL; 
	RETURN  VAR;
END;
/



 /*
Procedure to compute total marks of a student.
Showcase:
1. Procedure with multiple parameters
2. DECLARE statements
3. Function call in a SET statement
*/ 
CREATE OR REPLACE PROCEDURE COMPUTE_TOTAL_MARKS
(
	ROLL                       IN      	INT DEFAULT NULL,
	MARKS                      IN      	INT DEFAULT NULL
)
AS
	VAR                        INT;
	TOTAL                      INT;
BEGIN  	
	VAR  :=  INTERNAL_MARKS_CALCULATOR(ROLL); 	
	TOTAL  :=  VAR + MARKS; 
	DBMS_OUTPUT.PUT_LINE(  'TOTAL MARKS '  || TO_CHAR(TOTAL));
END;
/



 /*
Assigning a class for a student who is enrolling afresh.
Showcase:
1. DECLARE statements (data types DATETIME and VARCHAR)
2. DATEDIFF User-defined function
3. GETDATE() User-defined function
4. SIMPLE CASE function
5. PRINT statement
6. INSERT with VALUES statement.
7. SELECT statement with TOP clause
*/ 
CREATE OR REPLACE PROCEDURE ASSIGN_CLASS_FOR_STUDENT
(
	ROLL                       IN      	INT DEFAULT NULL
)
AS
	DOB                        DATE;
	AGE                        INT;
	CLASS                      VARCHAR2(10);
	CID                        INT;
	SEC                        VARCHAR2(10);
BEGIN  	
	 SELECT DOB INTO DOB 
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL; 	
	AGE  :=  ROUND(MONTHS_BETWEEN((SYSDATE), DOB)  / 12); 	
	 SELECT 
				CASE AGE 
					 WHEN 3  THEN 'PRE-KG'
					 WHEN 4  THEN 'UKG'
					 WHEN 5  THEN 'LKG'
					 WHEN 6  THEN 'CLASS-1'
					 WHEN 7  THEN 'CLASS-2'
					 WHEN 8  THEN 'CLASS-3'
					 WHEN 9  THEN 'CLASS-4'
					 WHEN 10  THEN 'CLASS-5'
					 WHEN 11  THEN 'CLASS-6'
					 WHEN 12  THEN 'CLASS-7'
					 WHEN 13  THEN 'CLASS-8'
					 WHEN 14  THEN 'CLASS-9'
					 WHEN 15  THEN 'CLASS-10'
					 WHEN 16  THEN 'CLASS-11'
					 ELSE 'CLASS-12'
				 END INTO CLASS 
	FROM  SYS.DUAL; 
	DBMS_OUTPUT.PUT_LINE(  'CLASS ASSIGNED FOR '  || TO_CHAR(ROLL)  || ' IS '  || CLASS); 	
	 SELECT CLASS_ID INTO CID 
	FROM  CLASS 
	WHERE	 CLASS_NAME  = CLASS; 	
	 SELECT SECTION_NAME INTO SEC 
	FROM  CLASS_SECTION 
	WHERE	 CLASS_ID  = CID
	 AND	ROWNUM  < 2;
	
	INSERT INTO  STUDENT_CLASS    
	 VALUES 		( ASSIGN_CLASS_FOR_STUDENT.ROLL , 
			ASSIGN_CLASS_FOR_STUDENT.CID , 
			ASSIGN_CLASS_FOR_STUDENT.SEC );
END;
/



 /*
Add a student to the database
Showcase:
1. DEFAULT values for parameters in procedure
2. @@IDENTITY global varaible
3. Calling another PROCEDURE with argument from within procedure
4. Function call in SET clause
*/ 
CREATE OR REPLACE PROCEDURE ADD_STUDENT
(
	FNAME                      IN      	VARCHAR2 DEFAULT NULL,
	LNAME                      IN      	VARCHAR2 DEFAULT NULL,
	ADDR                       IN      	VARCHAR2 DEFAULT NULL,
	PH                         IN      	INT DEFAULT NULL,
	SEX                        IN      	VARCHAR2 DEFAULT 'MALE',
	DOB                        IN      	DATE DEFAULT NULL,
	BLOOD                      IN      	VARCHAR2 DEFAULT NULL
)
AS
	IDENT                      INT;
	FEES_DATE                  DATE;
BEGIN 
	
	INSERT INTO  STUDENT   
			( firstname , 
			lastname , 
			address , 
			phone , 
			sex , 
			dob , 
			doj , 
			bloodgroup )  
	 VALUES 		( ADD_STUDENT.FNAME , 
			ADD_STUDENT.LNAME , 
			ADD_STUDENT.ADDR , 
			ADD_STUDENT.PH , 
			ADD_STUDENT.SEX , 
			ADD_STUDENT.DOB , 
			(SYSDATE) , 
			ADD_STUDENT.BLOOD ); 	
	IDENT  :=  GLOBALPKG.IDENTITY; 
		 ASSIGN_CLASS_FOR_STUDENT( IDENT); 	
	FEES_DATE  :=  FEES_PAYMENT_DATE(IDENT); 
	DBMS_OUTPUT.PUT_LINE(  'FEES SHOULD BE PAID ON OR BEFORE '  || TO_CHAR(FEES_DATE, 'Mon DD YYYY HH:MIAM'));
END;
/



 /*
Add an examination schedule to database
Showcase:
1. RAISERROR statement
*/ 
CREATE OR REPLACE PROCEDURE ADD_EXAM_SCHEDULE
(
	CLASS                      IN      	INT DEFAULT NULL,
	NAME                       IN      	VARCHAR2 DEFAULT NULL,
	E_DATE                     IN      	DATE DEFAULT NULL
)
AS
BEGIN  
	IF CLASS < 0 THEN  
	RAISE_APPLICATION_ERROR(-20001,  'CLASS ID CANNOT BE LESS THAN 0.' );
	END IF;
	
	INSERT INTO  EXAM_SCHEDULE   
			( class_id , 
			exam_name , 
			exam_date )  
	 VALUES 		( ADD_EXAM_SCHEDULE.CLASS , 
			ADD_EXAM_SCHEDULE.NAME , 
			ADD_EXAM_SCHEDULE.E_DATE );
END;
/


BEGIN 
EXECUTE IMMEDIATE 'DROP TABLE TEMP_MARKS CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/


CREATE GLOBAL TEMPORARY TABLE TEMP_MARKS 
(
	ROLL_NO int  , 
	MARKS int  , 
	TOTAL int  , 
	GRADE char (10) 
)
	ON COMMIT PRESERVE ROWS
/



 /*
Prepares grade for students
Showcase:
1. Temporary tables within procedure
2. DECLARE CURSOR statements
3. OPEN statement
4. FETCH from CURSOR statement
5. WHILE statement
6. @@FETCH_STATUS usage
7. BREAK statement
8. CLOSE and DEALLOCATE cursor statements
9. CONVERT in-built function usage
10. SEARCHED CASE function
*/ 
CREATE OR REPLACE PROCEDURE ANNUAL_GRADE
(
	RCT1                        IN  OUT 	GLOBALPKG.RCT1
)
AS
	ROLL                       INT;
	MARKS                      INT;
	TOTAL                      INT;
	VAR                        FLOAT;
	GRAD                       CHAR(10);
	
		CURSOR  MARKS_CURSOR
		 IS
SELECT
		 ROLL_NO,
		 MARKS_SCORED,
		 TOTAL_MARKS
FROM  STUDENT_MARKS_IN_EXAMS 
;
BEGIN 
	
	EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_MARKS';
 
	OPEN MARKS_CURSOR; 
FETCH  MARKS_CURSOR INTO ROLL, MARKS, TOTAL; 
<< LABEL4 >>

	WHILE  MARKS_CURSOR%FOUND
	LOOP  
	BEGIN 
		
		SELECT  CAST(ANNUAL_GRADE.MARKS AS FLOAT) / CAST(ANNUAL_GRADE.TOTAL AS FLOAT)  INTO  ANNUAL_GRADE.VAR  FROM DUAL; 
		IF( VAR > 1) THEN 
		EXIT;
		END IF; 	
		 SELECT 
					CASE
						 WHEN VAR  > 0.9 THEN 'A'
						 WHEN VAR  > 0.8 THEN 'B'
						 WHEN VAR  > 0.7 THEN 'C'
						 WHEN VAR  > 0.6 THEN 'D'
						 WHEN VAR  > 0.5 THEN 'E'
						 ELSE 'F'
					 END INTO GRAD 
		FROM  SYS.DUAL;
		
		INSERT INTO  TEMP_MARKS    
		 VALUES 		( ANNUAL_GRADE.ROLL , 
				ANNUAL_GRADE.MARKS , 
				ANNUAL_GRADE.TOTAL , 
				ANNUAL_GRADE.GRAD ); 
FETCH  MARKS_CURSOR INTO ROLL, MARKS, TOTAL;
	END;
	
	END LOOP; 
	CLOSE MARKS_CURSOR;
	
	OPEN RCT1 FOR 
	SELECT *
	FROM  TEMP_MARKS;
END;
/



 /*
Showcase:
1. RETURN statement in procedure
2. GOTO statement
3. LABEL defintion
*/ 
CREATE OR REPLACE PROCEDURE SET_ATTENDANCE
(
	ROLL                       IN      	INT DEFAULT NULL,
	ATTENDED                   IN      	INT DEFAULT NULL
)
AS
	VAR                        INT;
BEGIN  
	IF ATTENDED = 1 THEN  
	BEGIN 
		
		INSERT INTO  STUDENT_ATTENDANCE    
		 VALUES 		( SET_ATTENDANCE.ROLL , 
				(SYSDATE) , 
				SET_ATTENDANCE.ATTENDED ); 
		RETURN;
	END;
	ELSE  
	GOTO ABSENT;
	END IF; 
	<<ABSENT>> 	
	 SELECT COUNT(*) INTO VAR 
	FROM  STUDENT_ATTENDANCE 
	WHERE	 ROLL_NO  = ROLL
	 AND	ATTENDED  = 0; 	
	VAR  :=  VAR +1; 
	DBMS_OUTPUT.PUT_LINE(  'STUDENT WITH ROLL NUMBER : '  || TO_CHAR(ROLL)  || ' HAS BEEN ABSENT FOR '  || TO_CHAR(VAR)  || ' DAYS TILL DATE');
	
	INSERT INTO  STUDENT_ATTENDANCE    
	 VALUES 		( SET_ATTENDANCE.ROLL , 
			(SYSDATE) , 
			SET_ATTENDANCE.ATTENDED );
END;
/



 /*
Showcase:
1. Update statement with MONEY data type.
*/ 
CREATE OR REPLACE PROCEDURE INCREASE_FEES
(
	CLASS                      IN      	INT DEFAULT NULL,
	INC                        IN      	DECIMAL DEFAULT NULL
)
AS
	G1_COL1                    FEES.FEES%TYPE;
	G1_ROWID                   ROWID;
	
CURSOR  G1_CURSOR IS
SELECT
		 FEES.ROWID ,
		 FEES + INC
FROM  FEES 
WHERE	 CLASS_ID  = CLASS
;
BEGIN 
	
	OPEN G1_CURSOR;
	LOOP
	FETCH G1_CURSOR INTO G1_ROWID,G1_COL1;
 	EXIT WHEN G1_CURSOR%NOTFOUND;
	 BEGIN 
		UPDATE  FEES   
		SET	FEES = G1_COL1 
		WHERE  ROWID  = G1_ROWID;
 	END; 
	 END LOOP; 
	 CLOSE G1_CURSOR;

END;
/



 /*
Showcase:
1. DELETE statement
2. IF EXISTS statement
3. @@ROWCOUNT usage
4. @@ERROR usage
*/ 
CREATE OR REPLACE PROCEDURE REMOVE_STUDENT
(
	ROLL                       IN      	INT DEFAULT NULL
)
AS
	ROW_ADV                    INT;
	ASSIGNMENTVARIABLE0           NUMBER(2,0) := 0;
BEGIN  
	
		BEGIN
		SELECT 1 INTO ASSIGNMENTVARIABLE0 FROM DUAL WHERE  EXISTS
	(SELECT *
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL
	);
	EXCEPTION
			WHEN NO_DATA_FOUND THEN 
				ASSIGNMENTVARIABLE0 := 0;
		END;

	IF(ASSIGNMENTVARIABLE0 != 0 ) THEN
	 
	BEGIN 
		
		DELETE FROM   STUDENT   
		WHERE  ROLL_NO  = REMOVE_STUDENT.ROLL; 	
		ROW_ADV  :=  SQL%ROWCOUNT; 
		DBMS_OUTPUT.PUT_LINE(  'NUMBER OF ROWS DELETED : '  || TO_CHAR(ROW_ADV));
	END;
	ELSE
		  
	BEGIN  	
		ROW_ADV  :=  SQLCODE; 
		DBMS_OUTPUT.PUT_LINE(  'STUDENT WITH THE SPECIFIED ROLL NUMBER NOT FOUND'  || TO_CHAR(ROW_ADV)  || '.');
	END;
	END IF;
END;
/



 /*
Showcase:
1. MAX in-built function
2. MIN in-built function
1. AVG in-built function
*/ 
CREATE OR REPLACE PROCEDURE ANALYZE_MARKS
(
	SUBJECT                    IN      	VARCHAR2 DEFAULT NULL,
	EXAM                       IN      	INT DEFAULT NULL
)
AS
	MAXI                       INT;
	MINI                       INT;
	AVG_ADV                    FLOAT;
BEGIN  	
	 SELECT MAX(MARKS_OBTAINED) INTO MAXI 
	FROM  STUDENT_MARKS 
	WHERE	 EXAM_ID  = EXAM
	 AND	SUBJECT_NAME  = SUBJECT; 	
	 SELECT MIN(MARKS_OBTAINED) INTO MINI 
	FROM  STUDENT_MARKS 
	WHERE	 EXAM_ID  = EXAM
	 AND	SUBJECT_NAME  = SUBJECT; 	
	 SELECT AVG(MARKS_OBTAINED) INTO AVG_ADV 
	FROM  STUDENT_MARKS 
	WHERE	 EXAM_ID  = EXAM
	 AND	SUBJECT_NAME  = SUBJECT; 
	DBMS_OUTPUT.PUT_LINE(  'MAXIMUM SCORE IN '  || SUBJECT  || ' IS '  || TO_CHAR(MAXI)); 
	DBMS_OUTPUT.PUT_LINE(  'MINIMUM SCORE IN '  || SUBJECT  || ' IS '  || TO_CHAR(MINI)); 
	DBMS_OUTPUT.PUT_LINE(  'AVERAGE SCORE IN '  || SUBJECT  || ' IS '  || TO_CHAR(AVG_ADV));
END;
/



 /*
Showcase:
1. LEFT in-built function
2. RIGHT in-built function
3. LOWER in-built function
4. UPPER in-built function
5. SUBSTRING in-built function
6. LEN in-built function
*/ 
CREATE OR REPLACE PROCEDURE FORMAT_NAME
(
	ROLL                       IN      	INT DEFAULT NULL
)
AS
	RESULT                     VARCHAR2(100);
	VAR                        INT;
BEGIN  	
	 SELECT SUBSTR(FIRSTNAME, 0, 3) INTO RESULT 
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL; 
	DBMS_OUTPUT.PUT_LINE(  'RESULT OF LEFT FUNCTION : '  || RESULT); 	
	 SELECT SUBSTR(FIRSTNAME, LENGTH(FIRSTNAME) - 3 +1, 3) INTO RESULT 
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL; 
	DBMS_OUTPUT.PUT_LINE(  'RESULT OF RIGHT FUNCTION : '  || RESULT); 	
	 SELECT LOWER(FIRSTNAME) INTO RESULT 
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL; 
	DBMS_OUTPUT.PUT_LINE(  'RESULT OF LOWER FUNCTION : '  || RESULT); 	
	 SELECT UPPER(FIRSTNAME) INTO RESULT 
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL; 
	DBMS_OUTPUT.PUT_LINE(  'RESULT OF UPPER FUNCTION : '  || RESULT); 	
	 SELECT SUBSTR(FIRSTNAME, 1, 2) INTO RESULT 
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL; 
	DBMS_OUTPUT.PUT_LINE(  'RESULT OF SUBSTRING FUNCTION : '  || RESULT); 	
	 SELECT LENGTH(RTRIM(FIRSTNAME)) INTO VAR 
	FROM  STUDENT 
	WHERE	 ROLL_NO  = ROLL; 
	DBMS_OUTPUT.PUT_LINE(  'RESULT OF LEN FUNCTION : '  || TO_CHAR(VAR));
END;
/



 /*
Showcase:
1. CAST in-built function
2. ROUND in-built function
3. BITWISE AND operator
4. BITWISE OR operator
5. BITWISE XOR operator
6. Function returning multiple result set
*/ 
CREATE OR REPLACE PROCEDURE FUNCTION_TEST
(
	RCT1                        IN  OUT 	GLOBALPKG.RCT1,
	RCT2                        IN  OUT 	GLOBALPKG.RCT1,
	RCT3                        IN  OUT 	GLOBALPKG.RCT1
)
AS
	VAR                        INT;
BEGIN  	
	 SELECT CAST(ROUND(MARKS_OBTAINED / TOTAL_MARKS, 0) AS INT) INTO VAR 
	FROM  STUDENT_MARKS 
	WHERE	 ROLL_NO  = 1; 
	DBMS_OUTPUT.PUT_LINE(  'VALUE OF VAR WITH CAST AND ROUND WITH 0 AS ARGUMENT : '  || TO_CHAR(VAR)); 	
	 SELECT CAST(ROUND(MARKS_OBTAINED / TOTAL_MARKS, 1) AS INT) INTO VAR 
	FROM  STUDENT_MARKS 
	WHERE	 ROLL_NO  = 1; 
	DBMS_OUTPUT.PUT_LINE(  'VALUE OF VAR WITH CAST AND ROUND WITH 1 AS ARGUMENT : '  || TO_CHAR(VAR));
	
	OPEN RCT1 FOR 
	SELECT   BITAND(2,3)  
	FROM  SYS.DUAL;
	
	OPEN RCT2 FOR 
	SELECT   ((2+3) - BITAND(2,3))  
	FROM  SYS.DUAL;
	
	OPEN RCT3 FOR 
	SELECT   ((2+3) - BITAND(2,3)*2)  
	FROM  SYS.DUAL;
END;
/