Cursors in oracle for updating all records examples

It is created on a SELECT Statement which returns more than one row. General Syntax for creating a cursor is as given below: In the above example we are creating a cursor ‘emp_cur’ on a query which returns the records of all the employees with salary greater than 5000. When the data is fetched it is copied to the record or variables and the logical pointer moves to the next row and it becomes the current row.

Here ‘emp_tbl’ in the table which contains records of all the employees. On every fetch statement, the pointer moves to the next row.

SQL SELECT * FROM employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 10000 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 10000 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 10000 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 10000 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 10000 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 10000 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 10000 New York Manager 08 James Cat 17-SEP-96 15-APR-02 10000 Vancouver Tester 8 rows selected.

An explicit cursor is defined in the declaration section of the PL/SQL Block. General Syntax to open a cursor is: When a cursor is opened, the first row becomes the current row.

Cursor For Loop Example DECLARE CURSOR c1 IS SELECT ename, sal, deptno FROM emp; ...

create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.

Points to remember while fetching a row: · We can fetch the rows in a cursor to a PL/SQL Record or a list of variables created in the PL/SQL Block.

· If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.

The set of rows the cursor holds is called the set.

My method of using FORALL to update many columns is by splitting a PL table of a string using SUBSTR in fixed places.

This seems a messy way of getting around the problem. COUNT UPDATE profile SET quotopurdays = LTRIM(SUBSTR(people_tab(bulk Num),0,6)), maildays = LTRIM(SUBSTR(people_tab(bulk Num),7,9)), ... What we (well, you) want to do is ARRAY fetch N records, do your thing to them, and ARRAY update N records (or less). (i don't like that substr trick, just adds overhead). The following example shows how to accomplish this.

They must be created when you are executing a SELECT statement that returns more than one row.

Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row.

A Cursor For Loop implicitly declares its loop index as a record that represents a row fetched from the database.

You must have an account to comment. Please register or login here!