Find the names of the students, who have failed and write the details to another table.
Answer
declare
student_row student1_a5518%rowtype;
cursor cur1 is select * from student1_a5518;
remarks1 varchar2(20);
begin
open cur1;
delete from student2_a5518;
loop
fetch cur1 into student_row;
exit when cur1%notfound;
A student table has column names as given below :-
STUDENT_NO INTEGER(3)
STUDENT_NAME VARCHAR2(20)
TOTAL_MARKS NUMBER(6,2)
REMARKS VARCHAR2(25)
And another table, Marks, contains the following columns :-
STUDENT_NO INTEGER(3)
MARK1 NUMBER(5,2)
MARK2 NUMBER(5,2)
MARK3 NUMBER(5,2)
Read one record at a time and find the total marks of each student and update the total marks in Student table. If total_marks is greater than or equal to 150, update the remarks with ‘Passed’, oth erwise raise an exception and update remarks with ‘Failed’.
Answer
declare
marks_row student1_marks_a5518%rowtype;
cursor cur1 is select * from student1_marks_a5518;
total number(4);
begin
open cur1;
loop
fetch cur1 into marks_row;
exit when cur1%notfound;
A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.
Cursor without parameters (simplest)
The basic syntax for a cursor without parameters is:
CURSOR cursor_name
IS
SELECT_statement;
Cursor with parameters
The basic syntax for a cursor with parameters is:
CURSOR cursor_name (parameter_list)
IS
SELECT_statement;
Cursor with return clause
The basic syntax for a cursor with a return clause is:
CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;
April 5th, 2013 
