| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 2/28/01 - PL/SQL Syntax Request
Well, it sounds like what you want to do is read the table into a cursor and if there is nothing else unique about the rows, the rowid. Then loop through the cursor checking for your true condition and if it is, then update the row, commit and continue until the cursor is empty.
DECLARE
CURSOR blah IS
SELECT * FROM table;
BEGIN
FOR v_whatever IN blah LOOP
IF v_whatever.column_name = xyz THEN
UPDATE table SET column_name = new_whatever WHERE condition
identifying this row as unique;
COMMIT;
END IF;
Dan
-----Original Message-----
Sent: Wednesday, February 28, 2001 11:41 AM
To: Multiple recipients of list ORACLE-L
I need help. I'm sorry if this is a cross-posting for you.
I'd like to see some example PL/SQL code, perhaps a procedure, that will
pass
through each record of a table, test for combinations of column values and
based upon
a specific value (which would be determined by an IF... THEN... matrix), set
a variable.
The value stored in this variable would then be used to update a different
column of the
very same table from which the original record was read.
The matrix:
(IF) (THEN) Key A M C one two three four five CA IND 1 1 CZ STA OFF BOO AD CA IND 3 1 CZ STA OFF REP AD CA IND 1 4 CZ STA OFF AV AD
...
PH IND 1 1 PH STA OFF BOO PO PH OUT 1 1 PH REF NULL RBO PO
The data table IN (before processing):
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
CA IND 3 1 CA IND 3 1 PH IND 1 1 CA IND 1 4 PH OUT 1 1 CA IND 3 1
The data table OUT (after processing):
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
CA IND 3 1 CZ STA OFF REP
AD
CA IND 3 1 CZ STA OFF REP
AD
PH IND 1 1 PH STA OFF BOO
PO
CA IND 1 4 CZ STA OFF AV
AD
PH OUT 1 1 PH REF NULL RBO
PO
CA IND 3 1 CZ STA OFF REP
AD
...
...
I'm looking for the PL/SQL code syntax to perform a task such as this.
Wow. That was a mouthful. Good discipline.
Any and all help will be appreciated.
Pat Gorden-Ozgul BNL-ISD Systems gorden_at_bnl.gov 631-344-5159
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gorden-Ozgul, Patricia E INET: gorden_at_bnl.gov Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _____________________________________________________________________ This message has been checked for all known viruses by UUNET delivered through the MessageLabs Virus Control Centre. For further information visit http://www.uk.uu.net/products/security/virus/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dasko, Dan INET: Dan.Dasko_at_cdicorp.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Feb 28 2001 - 13:16:24 CST
 |
 |