Question

EX:NO:7 PLISOL PROCEDURE & FUNCTION AIM To develop the package for finding the number of students under each percentage for individual courses using PLISQL procedure and function. DATABASE SCHEMA STUDENT (ST_ID, NAME, COURSECODE, COURSENAME, TOTAL MARKS, PERCENTAGE) PROCEDURE STEP 1: Start STEP 2: Create a table with table name student. STEP 3: Insert at least 4 records into the table. STEP 4: Writ a PL/SQL procedure to find the number of students ranging from 100- 70%, 69-60%, 59-50% & below 49% in each course from the student table given by the procedure as parameter STEP 5: Write a PLISQL function to display the student name and course name whose total marks is 350. STEP 6: Execute the procedure & function. STEP 7: Stop

The procedure is a sample and needs to be modified.
The software used is oracle 11g.
Provide screen shot of the program running.

Answer 1

create table student(ST_ID int primary key,
                   NAME varchar(25),
                   COURSECODE CHAR(4),
                   COURSENAME varchar(20),
                   TOTAL_MARKS int,
                   PERCENTAGE int);
                  
insert into student values(1,'peter','CS82','DBMS',350,75);
insert into student values(2,'john','CS82','DBMS',150,35);
insert into student values(3,'joe','CS82','DBMS',300,59);
insert into student values(4,'William','CS82','DBMS',360,55);
                  
insert into student values(5,'Harry','CS81','python',550,95);
insert into student values(6,'pinky','CS81','python',100,30);
insert into student values(7,'john','CS81','python',320,61);
insert into student values(8,'jane','CS81','python',350,65);

select * from student;

SQL> SQL> select*From student; ST ID NAME COUR COURSENAME TOTAL MARKS PERCENTAGE 350 158 300 360 550 100 320 350 75 35 1 peter CS82 DBMS 2 john 3 joe 4 William 5 Harry 6 pinky 7 john 8 jane CS82 DBMS CS82 DBMS 59 CS82 DBMS 95 30 61 65 CS81 python CS81 python CS81 python CS81 python 8 rows se「ected.

set serveroutput on;
CREATE or replace PROCEDURE MYPROCEDURE4(COURSE_NAME VARCHAR)
aS
count1 NUMBER :=0;
count2 number :=0;
count3 number :=0;
count4 number :=0;

   CURSOR C1 IS
   SELECT PERCENTAGE
   FROM STUDENT
   WHERE COURSENAME=COURSE_NAME;
  
begin  
   for p in c1
   loop
   if p.percentage>=70 AND P.PERCENTAGE<=100 THEN
       count1 := count1 + 1;
      
   elsif p.percentage >= 60 and p.percentage <70 THEN
       count2 :=count2 + 1;
      
   elsif p.percentage >=50 and p.percentage <59 THEN
       count3 :=count3 + 1;
      
   else
       count4 :=count4 + 1;
  
   END IF;
   END LOOP;
   dbms_output.put_line('TOTAL NUMBER OF STUDENT FOR COURSE '||COURSE_NAME||' RANGING FROM 70%-100% IS '||COUNT1);  
   dbms_output.put_line('TOTAL NUMBER OF STUDENT FOR COURSE '||COURSE_NAME||' RANGING FROM 60%-69% IS '||COUNT2);
   dbms_output.put_line('TOTAL NUMBER OF STUDENT FOR COURSE '||COURSE_NAME||' RANGING FROM 50%-59% IS '||COUNT3);
   dbms_output.put_line('TOTAL NUMBER OF STUDENT FOR COURSE '||COURSE_NAME||' BELOW 49% IS '||COUNT4);
end;
/

EXEC MYPROCEDURE4('DBMS');
EXEC MYPROCEDURE4('python');

SQL> EXEC MYPROCEDURE4'DBMS); TOTAL NUMBER OF STUDENT FOR COURSE DBMS RANGING FROM 70%-100% IS 1 TOTAL NUMBER OF STUDENT FOR COURSE DBMS RANGING FROM óO%-69% IS 0 TOTAL NUMBER OF STUDENT FOR COURSE DBMS RANGING FROM 50%-59% IS 1 TOTAL NUMBER OF STUDENT FOR COURSE DBMS BELOW 49% IS 2 PL/SQL procedure successfully completed SQL> EXEC MYPROCEDURE4 python'); TOTAL NUMBER OF STUDENT FOR COURSE python RANGING FROM 70%-100% IS 1 TOTAL NUMBER OF STUDENT FOR COURSE python RANGING FROM 60%-69% IS 2 TOTAL NUMBER OF STUDENT FOR COURSE python RANGING FROM 50%-59% IS TOTAL NUMBER OF STUDENT FOR COURSE python BELOW 49% IS 1 PL/SQL procedure successfully completed SQL> |

set serveroutput on;
CREATE or replace function MYfunction5 return varchar2
as
   output varchar2(50);
   CURSOR C1 IS
   SELECT ST_ID,COURSENAME,NAME,TOTAL_MARKS
   FROM STUDENT;  
begin  
   for p in c1
   loop
   IF p.TOTAL_MARKS=350 THEN
       output := output || ' ( ' ||p.COURSENAME|| ' ' ||p.NAME||')';
   END IF;
   END LOOP;
   return output;
end;
/

select MYfunction5 from dual;

SQL> set serveroutput on; SQL〉 CREATE or replace function MY function5 return varchar2 2 as 3 output varchar2(50); 4CURSOR C1 IS 5 SELECT ST ID, COURSENAME, NAME, TOTAL MARKS 6 FROM STUDENT 7 begin 8 for p in c1 9 10op 10 IF p.TOTAL MARKS-350 THEN 1 outputoutputlIp.COURSENAMEIIp.NAMEI I)' 12 END IF; 13 END LOOP; 14 return output; 15 end; 16 / Function created SQL> SQL> select MYfunction5 from dual; MYFUNCTIONS ( DBMS peter python jane)