plsql

plsql.sql

DECLARE
    CURSOR c1 IS
        SELECT ABC.col1, ABC.col2, BCD.col3
        FROM ABC
        LEFT JOIN BCD 
          ON ABC.col1 = BCD.col1 
          AND ABC.col2 = BCD.col2
        ORDER BY ABC.col1;
    
    v_max_col3 BCD.col3%TYPE;
    v_row_num INTEGER := 0;
    v_col3 BCD.col3%TYPE;
BEGIN
    -- 最大のシーケンス値を取得
    SELECT MAX(BCD.col3) INTO v_max_col3
    FROM ABC  left outer join BCD 
    ON ABC.col1 = BCD.col1 AND ABC.col2 = BCD.col2;
    -- シーケンス値が無い場合、開始は０
    if v_max_col3 is null then
      v_max_col3 := 0;
    end if;
    FOR r IN c1 LOOP
        IF r.col3 IS NULL THEN
            v_row_num := v_row_num + 1;
            v_col3 := v_max_col3 + v_row_num;
        ELSE
            v_col3 := r.col3;
        END IF;
        
        -- Output the result
        DBMS_OUTPUT.PUT_LINE('col1: ' || r.col1 || ', col3: ' || v_col3);
    END LOOP;
END;
/