programs in sql and DBMS cse department programs

CURSOR

AIM:

      To write a program in sql for implementing the concept of cursor.

ALGORITHM:

 FOR % FOUND:

1. Start the program.

2. Create the table student and insert values into the tables.

3. Declare the cursor named ”a”.

4. In the loop fetch the cursor “a” into student roll no from the student.

5. Update the student table in the mark2 fields.

6. Set marks in the corresponding sno.

7. Exit from the loop when the mark is updated.

8. Print the line “table updated”.

9. Close the cursor “a”.

10. Stop the process.

FOR  % ROW COUNT:

1. Start the process.

2. Row count the number of rows in the student name.

3. Declare a cursor “b”.

4. Open the cursor.

5. In the loop fetch the cursor “b” into sno.

6. Count the no of rows in the student table.

7. Exit from the loop after the rollno is counting.

8. Print the line "fetched the no of rows into the student table”.

9. Close the cursor.

10. Stop the process.

FOR % IS OPEN:

1. Start the process.

2. Check whether the cursor is opened or closed.

3. Declare a cursor named “c”.

4. Check the condition if the cursor is open or not using % is open.

5. If it is not open print “cursor is not yet opened”.

6. Open the cursor.

7. If it is open print “cursor is opened now.

8. Close the cursor.

9. Stop the process.

SYNTAX:

Cursor<cursor name>is<select statement>

OPEN:

Open<cursor name>;

FETCH:

Fetch<cursor name>into variable list,record name.

CLOSE:

Close<cursor name>;

CURSOR FOR LOOP:

For<record name>in<cursor name>loop……………loop statements.

End loop:

CURSOR WITH UPDATE:

Cursor<cursor name>is select<column name>from<table name>

[where condition]

For update[of column names][no wait]

TABLE CREATION:

Create table student (sno number(5),sname varchar2(10),mark1 number(3),mark2 number(3));

TO INSERT THE VALUES INTO THE TABLE:

Sql>insert into student values(&sno,’&sname’,&mark1,&mark2);

 PROGRAM CODING FOR % FOUND:

Declare

Sno student.sno %type;

Cursor a is select sno from student where sno=2;

Begin

Open a;

Loop

Fetch a into sno;

Update student set mark2=75 where sno=2;

Exit when a %-not found;

End loop;

Dbms_output.put_line(‘table updated’);

Close a;

End;

PROGRAM CODING FOR  % ROW COUNT:

Declare

Cursor b is select *from student where mark1>60;

Sno student %row type;

Begin

Open b;

Loop

Fetch b into sno;

Exit when b % not found;

End loop;

Dbms_output.put_line(‘fetched’//b %row count//’from table’);

Close b;

End;

PROGRAM CODING FOR % IS OPEN:

Declare

Cursor c is select *from student;

Begin

If not c% is open then

Dbms_output.put_line(‘cursor is not yet opened’);

End if;

Open c;

If c% is open then

Dbms_output.put_line(‘cursor is open now’);

End if;

Close c;

End;

OUTPUT:

FOR % FOUND:

Table  created.

Sql>select *from student;

Sno	Sname	Mark1	Mark2
1	Rose	196	186
2	Jas	175	173
3	Maga	165	156
4	Tamil	198	179

Row count.

FOR %ROW COUNT:

Fetched 4 to from table

Pl/sql procedure successfully  completed.

FOR %ISOPEN:

Cursor is not yet opened.

Cursor is open now

Pl/sql procedure successfully completed.

   

RESULT:

   Thus the cursor commands are executed successfully.

Posted in: dbms programs