Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL ROW Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 135 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?