How to loop in sql?

I dont want to use the "loop" related keyword, how can I implement loop with basic sql command in oracle ?

I have two table :

A:  
ID, Color    
B,   
ID, AID, Type  

I want to loop开发者_JAVA百科 all records in B, and if ID = AID, then set the A.Color = B.Type

Thanks in advance !

Looping is, by definition, a procedural construct.

SQL is declarative: tell the database what you want done, not how to do it.

If you're absolutely convinced that you need to program such a thing, then write it in PL/SQL, Oracle's procedural language.

Bu I'm sure that it's possible to do what you want in SQL using an UPDATE with a WHERE clause.

Something like this (corrected per NullUserException):

UPDATE A SET A.Color = (SELECT B.Type FROM B WHERE A.ID = B.AID)

An alternate method:

MERGE INTO a
USING b
ON (b.aid = a.id)
WHEN MATCHED THEN UPDATE SET a.color = b.type;

You could just do:

UPDATE tablea a
   SET a.color = (SELECT b.type 
                    FROM tableb b
                   WHERE b.aid = a.id)

See this SQL script.

To do that you will have to write a stored procedure using PL/SQL. Here is the oracle page with some info and papers on the topic.

As others pointed out, you can probably solve your problem with a normal DML statement, without any looping involved. But to give you some basics on how to accomplish what you asked for in PL/SQL, here's an example...

DECLARE
    CURSOR c IS 
        SELECT id, aid, type FROM b;
    statement VARCHAR2(200);
BEGIN
    FOR iterator IN c LOOP
        IF iterator.id = iterator.aid THEN
            statement := 'UPDATE a SET color = ' || iterator.type || 'WHERE id = ' || iterator.id;
            EXECUTE IMMEDIATE statement;
        END IF;
    END LOOP;
END;

This anonymous PL/SQL block will iterate through each record in table b, and if b.id = b.aid, it will update table a and set a.color = b.type where a.id = b.id.

This seems to be what you were asking for. It's not exactly an efficient way to go about doing things, since you're firing off one DML statement per row in table b that has b.id=b.aid. But I wanted more to give this as a syntax example. This is just one way to iterate through a cursor by the way; you can also explicitly open cursors and fetch records, but it's easier this way if you don't need to do anything but iterate over the entire result set.