The Oracle row type concept can be accomplished with the record type in PostgreSQL.
Example:
Oracle:
CURSOR travel_cur IS
SELECT c.travel_id FROM TRAVEL_CON c, TRAVEL_PAT p
WHERE c.travel_id = p.travel_id AND p.pat_id = source;
travel_rec travel_cur%ROWTYPE; <---- here travel_rec holds multiple records at a time.
BEGIN
OPEN travel_cur;
LOOP
FETCH travel_cur INTO travel_rec;
EXIT WHEN travel_cur%NOTFOUND;
sql_stmt := 'delete from TRAVEL_PAT where travel_id = :1';
EXECUTE IMMEDIATE sql_stmt USING travel_rec.travel_id;
END LOOP;
CLOSE travel_cur;
END;
PostgreSQL:
SELECT c.travel_id FROM TRAVEL_CON c, TRAVEL_PAT p
WHERE c.travel_id = p.travel_id AND p.pat_id = source;
travel_rec travel_cur%ROWTYPE; <---- here travel_rec holds multiple records at a time.
BEGIN
OPEN travel_cur;
LOOP
FETCH travel_cur INTO travel_rec;
EXIT WHEN travel_cur%NOTFOUND;
sql_stmt := 'delete from TRAVEL_PAT where travel_id = :1';
EXECUTE IMMEDIATE sql_stmt USING travel_rec.travel_id;
END LOOP;
CLOSE travel_cur;
END;
PostgreSQL:
DECLARE
sql_stmt varchar(1000);
travel_rec record; <---- here travel_rec holds only a single record at a time.
BEGIN
FOR travel_rec in SELECT c.travel_id FROM TRAVEL_CON c, TRAVEL_PAT p
WHERE c.travel_id = p.travel_id AND p.pat_id = source;
LOOP
sql_stmt := 'delete from TRAVEL_PAT where travel_id = :1';
EXECUTE IMMEDIATE sql_stmt USING travel_rec.travel_id;
END LOOP;
END;
No comments:
Post a Comment