Oracle Cursor Practice

-

 Cursor Practice

What is PL/SQL Cursor?

The purpose of the cursor is to make a quarry in a table of a database and change (DML) the table column of a database.


-- Lab Practice

DROP TABLE student;

CREATE TABLE student (roll NUMBER(6) PRIMARY KEY, name VARCHAR(20), sex CHAR(1), sub1 NUMBER(2), sub2 NUMBER(2), total NUMBER(3), result CHAR(2));


-- Data insert into student table

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100000,'RASHID','0',45,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100001,'Md. Abdul Khalek','0',65,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100002,'Most. Dilruba Aktar','1',95,56);

 INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100003,'RASHID','0',45,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100004,'Md. Abdul Khalek','0',65,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100005,'Shahina Sultana','1',95,66);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100006,'RASHID','0',45,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100007,'Md. Abdul Khalek','0',65,36);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100008,'Most. Dilruba Aktar','1',85,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100009,'RASHID','0',45,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100010,'Md. Abdul Khalek','0',45,76);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100011,'Most. Aklima Khatun','1',95,56);

cl scr;

SELECT roll, sub1, sub2, total FROM student;

 -- Asignment for making total marks in table student

SET SERVEROUTPUT ON

DECLARE

               v_total number(3):=0;

               v_result varchar2(2);

               CURSOR student_cursor  IS

                              SELECT roll,sub1,sub2, total

                              FROM student;  

               student_record student_cursor%ROWTYPE;          

BEGIN

               OPEN student_cursor;

               LOOP

               FETCH student_cursor INTO student_record;

               EXIT WHEN student_cursor%ROWCOUNT>70 OR student_cursor%NOTFOUND;

               UPDATE student

               SET student.total=student_record.sub1+student_record.sub2

               WHERE student.roll=student_record.roll;

               END LOOP;

               COMMIT;

               CLOSE student_cursor;

END;

/

cl scr;

SELECT student.roll, student.name, student.total FROM student;



-- Asignment for making grading grading in table student

SET SERVEROUTPUT ON

DECLARE

               v_total number(3):=0;

               v_result varchar2(2);

               CURSOR grade_cursor IS

                              SELECT roll,total

                              FROM student;  

               student_grade grade_cursor%ROWTYPE;              

BEGIN

               OPEN grade_cursor;

               LOOP

               FETCH grade_cursor INTO student_grade;

               EXIT WHEN grade_cursor%ROWCOUNT>70 OR grade_cursor%NOTFOUND;

               v_total:=student_grade.total;

               IF v_total>=140 THEN

                                             v_result:='A+';

               ELSIF v_total>=100 THEN

                                             v_result:='A-';

               ELSE

                                             v_result:='F';

               END IF;

               update student

               set student.result=v_result

               WHERE student.roll=student_grade.roll;

               END LOOP;

               COMMIT;

               CLOSE grade_cursor;

END;

/

CL SCR;

SELECT student.roll, student.name, student.total, student.result FROM student;


The writer is an Oracle Certified Professional, trainer, and PhD Fellow in BUP and can be reached at ‘rashid.eee.cse@gmail.com’.

Comments

Post a Comment

Popular posts from this blog

How to be an Innovative Speaker?

-
1. Innovation in Effective session conduct   Define your message of the session. •         Make it participatory. In your last line, ask questions and teach by name. The final 5 to 10 minutes will be dedicated to participant comments. •         Maintain start and Ending time. •          Concentrate on the audience's needs. Patience is required. Don't retaliate if the participants engage in unwelcome behavior. •         Boost your self-confidence on topics by reading daily papers, books, articles, senior lectures and self-experience. •         Prepare yourself. Write the last update date in a text box and put red color at the top right corner of your slide. •         Do not attend class until you have updated your slide. You won't show any slides that aren't based on current data. Pl...
Read more

Developing Personal Skills

-
  Developing Personal Skills Engr. Md. Abdur Rashid  PhD, NAPD Dear Officers and Staff, you must establish and ensure the long-term growth of your organization. Maintain the following managerial abilities in order to enhance personal skills on a daily basis while working. 1.                  1)       Self-assessment 2)       Creativity 3)       Innovation 4)       Listening and feedback 5)       Find self-satisfaction 6)       Leadership 7)       Team building 8)       Hunt knowledge 9)       Self-reliance and judgment 10)    Resilience 11)    Time management 12)    Stress management 13)    Control over anything 14)    Find satisfa...
Read more

Differences between the terms Science, Technology, and Engineering

-
What are the differences between the terms Science, Technology, and Engineering? Dr. Engr. Md. Abdur Rashid Director (Admin & Finance) NAPD Science Focus: The systematic study of the natural world through observation, experimentation, and analysis. Goal: To understand and explain the universe, its laws, and its phenomena. Methods: Scientific inquiry involves formulating hypotheses, conducting experiments, collecting data, and drawing conclusions. Examples: Physics, chemistry, biology, astronomy, geology. Technology Focus: The application of scientific knowledge to create tools, machines, and systems that solve problems or fulfill human needs. Goal: To improve efficiency, productivity, and quality of life. Methods: Technological development often involves innovation, design, and engineering. Examples: Computers, smartphones, automobiles, medical devices, renewable energy systems. Eng...
Read more