Name:Sahil Changede
Roll No. 13129
Practical No. 6 Stored Function
StoredFunction.sql
CREATE FUNCTION proc_Grade(marks NUMBER)
RETURN VARCHAR2 IS
category VARCHAR2(50);
BEGIN
IF marks >= 990 AND marks <= 1500 THEN
category := 'Distinction';
ELSIF marks >= 900 AND marks <= 989 THEN
category := 'First Class';
ELSIF marks >= 825 AND marks <= 899 THEN
category := 'Higher Second Class';
ELSE
category := 'Fail';
END IF;
RETURN category;
END proc_Grade;
StoredFunction_.sql
DECLARE
v_roll NUMBER;
v_name VARCHAR2(50);
v_total_marks NUMBER;
v_class VARCHAR2(50);
BEGIN
FOR student_rec IN (SELECT * FROM Stud_Marks) LOOP
v_roll := student_rec.roll;
v_name := student_rec.name;
v_total_marks := student_rec.total_marks;
v_class := proc_Grade(v_total_marks);
INSERT INTO Result (Roll, Name, Class) VALUES (v_roll, v_name, v_class);
END LOOP;
COMMIT;
END;
Output:
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Oct 10 03:37:30 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Enter user-name: SYSTEM
Enter password:
Last Successful login time: Tue Oct 10 2023 03:03:43 -07:00
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> CREATE TABLE Result (Roll int, Name varchar(20), class varchar(20));
Table created.
SQL> CREATE TABLE Stud_Marks(Name varchar(20), total_marks int);
Table created.
SQL> INSERT INTO Stud_Marks(Name, total_marks) VALUES ('SIDDESH', 9700);
1 row created.
SQL> INSERT INTO Stud_Marks(Name, total_marks) VALUES ('SAHIL', 1000);
1 row created.
SQL> INSERT INTO Stud_Marks(Name, total_marks) VALUES ('PRANAV', 9800);
1 row created.
SQL> SET SERVEROUTPUT ON;
SQL> @ StoredFunction.sql
Function created.
SQL> DROP TABLE STUD_MARKS;
Table dropped.
SQL> CREATE TABLE Stud_Marks(ROLL int, Name varchar(20), total_marks int);
Table created.
SQL> INSERT INTO Stud_Marks(Roll, Name, total_marks) VALUES (101,'SIDDESH', 9700);
1 row created.
SQL> INSERT INTO Stud_Marks(Roll, Name, total_marks) VALUES (102,'Sahil', 1000);
1 row created.
SQL> INSERT INTO Stud_Marks(Roll, Name, total_marks) VALUES (103,'Shubham', 9500);
1 row created.
SQL> INSERT INTO Stud_Marks(Roll, Name, total_marks) VALUES (104,'Kiran', 8900);
1 row created.
SQL> @StoredFunction_.sql
PL/SQL procedure successfully completed.
SQL> select * from Result;
ROLL NAME CLASS
---------- -------------------- --------------------
101 SIDDESH Fail
102 Sahil Distinction
103 Shubham Fail
104 Kiran Fail
SQL> INSERT INTO Stud_Marks(Roll, Name, total_marks) VALUES (103,'Akhil', 900);
1 row created.
SQL> INSERT INTO Stud_Marks(Roll, Name, total_marks) VALUES (105,'Priya', 826);
1 row created.
SQL> @StoredFunction_.sql
PL/SQL procedure successfully completed.
SQL> select * from Result;
ROLL NAME CLASS
---------- -------------------- --------------------
101 SIDDESH Fail
102 Sahil Distinction
103 Shubham Fail
104 Kiran Fail
103 Akhil First Class
105 Priya Higher Second Class
6 rows selected.
SQL>
