Cursors in teradata

Database
Enthusiast

Cursors in teradata

Hi,

I am having a stored procedure containing cursor declaration.
I have fetched employee ID's in the cursor ..

I need to loop through the cursor variable..

If some one knows, please let me know.......

Thanks,
4 REPLIES
Teradata Employee

Re: Cursors in teradata

Hello,

You need to have a loop to do this, following is the example:

DECLARE Var_EmployeeID INTEGER;
DECLARE VarCount INTEGER;

DECLARE Cur1 CURSOR FOR
SELECT Employee_ID FROM Table1 FOR READ ONLY;

SELECT COUNT(*) INTO VarCount FROM Table1;

IF VarCount <> 0 THEN --if you dont put this and the table is empty, you are in a never ending loop, alternatively you can use less than operator :)

OPEN Cur1;
Label1:
Loop

FETCH Cur1 INTO Var_EmployeeID;

--do whatever you like here

SELECT VarCount-1 INTO VarCount;

IF VarCount = 0 THEN
LEAVE Label1;
End IF;

END LOOP Label1;

CLOSE Cur1;

END IF;

HTH.

Regards,

Adeel

Re: Cursors in teradata

Thanks a lot..

Re: Cursors in teradata

Thanks from me too.

Just a little remark:

SELECT COUNT(*) - 1 INTO VarCount FROM Table1;

is better with "- 1", because if not, the last row is FETCH-ed twice/ two times.

Regards.

Teradata Employee

Re: Cursors in teradata

@mdgmilen

I was implementing a simialr thing kindly let me know, why the last row is fetched twice?