|
View previous topic :: View next topic
|
| Author |
Message |
subramsa
New User
Joined: 15 Apr 2005
Posts: 16
Location: Bangalore, India
|
|
|
|
Assume that i have a table
DEV.EMPLOYEE TABLE which contains
GEO_CODE CHAR(2)
COUNTRY_CODE CHAR(3)
EMP_NO CHAR(7)
EMP_NAME CHAR(40)
SALARY DEC(15,2)
DECLARE C1 CURSOR FOR SELECT GEO_CODE, COUNTRY, EMP_NO, EMP_NAME, SALARY FROM DEV.EMPLOYEE WHERE GEO_CODE = 'AP'
OPEN C1
FETCH C1
Then i am doing following Update inside the CURSOR loop
UPDATE DEV.EMPLOYEE SET SALARY = SALARY + 100 where COUNTRY = '744';
Now my question is
when we declare the cursor and open it, does the records qualified for the cursor are transferred to temp memory area?
can i update/delete the same table which are declared in the cursor inside the FETCH loop
after the UPDATE or DELETE inside the fetch operation where will be cursor pointer
 |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Quote: |
when we declare the cursor and open it, does the records qualified for the cursor are transferred to temp memory area?
|
Its not physical transfer ....
| Quote: |
can i update/delete the same table which are declared in the cursor inside the FETCH loop
|
Absolutely YES ...
| Quote: |
after the UPDATE or DELETE inside the fetch operation where will be cursor pointer
|
The current row which was fetched .... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
are you issuing any COMMIT's during this process.
How many times are you executing the SQL to update where COUNTRY = '744'?
| Quote: |
| when we declare the cursor and open it, does the records qualified for the cursor are transferred to temp memory area? |
A simple answer to your question will not make you conversant with the technical details.
Suggest that you read the Application Programmers SQL Guide for your site's version of DB2; there is a very good discussion about cursors. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| UPDATE DEV.EMPLOYEE SET SALARY = SALARY + 100 where COUNTRY = '744'; |
I do not see anywhere the update/query related to the cursor processing
for employees with country code 744 the salary will be raised by ( 100 * number of iterations )
It would be wise to provide better details to clarify the sequence of fetch/updates/..... |
|
| Back to top |
|
|
|
|
