Quick Search:
 
 The Oracle PL/SQL ROW Keyword      [Return To Index] Jump to:  

Term: ROW

Definition:
In Oracle PL/SQL, The keyword ROW is used in UPDATE statements to modify a complete record of a table. This feature was introduced in Oracle 9i release 2. The updating record type must exactly match in structure with the record structure of the table.

Example Syntax:

UPDATE [TABLE NAME]
SET ROW = [RECORD TYPE]


Example Usage:

The code example below updates the NAME column in the TEST table by updating the complete record in the table.

SQL> SELECT * FROM TEST;

ID NAME LNAME
---------- -------------------- ----------
1 CLUB ORACLE
2 SCOTT HR

SQL> DECLARE
TYPE TAB_TEST IS TABLE OF TEST%ROWTYPE;
L_TAB_TEST TAB_TEST;
CURSOR C1 IS
SELECT *
FROM TEST;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO L_TAB_TEST;
CLOSE C1 ;
FOR I IN L_TAB_TEST.FIRST..L_TAB_TEST.LAST
LOOP
L_TAB_TEST(I).NAME := 'Updating Name '||L_TAB_TEST(I).ID;
UPDATE TEST
SET ROW = L_TAB_TEST(I)
WHERE ID = L_TAB_TEST(I).ID;
END LOOP;
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST;

ID NAME LNAME
---------- -------------------- ----------
1 Updating Name 1 ORACLE
2 Updating Name 2 HR


Related Code Snippets:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org