oracle 使用遊標

 

oracle  使用遊標進行多個表聯集查詢

declare    --定義遊標     cursor mycur is             select t.dept_id,m.station_id,m.name from person_inform t            left join contacts m            on t.dept_id=m.id            order by t.dept_id;                 --臨時欄位    dept_id person_inform.dept_id%type;      station_id contacts.station_id%type;    name contacts.name%type;          begin     open mycur;     loop            fetch mycur into dept_id,station_id,name;            exit when mycur%notfound;            dbms_output.put_line(dept_id||'  '||station_id||'  '||name);     end loop;          close mycur;end;

單表查詢

declare    --遊標     cursor mycur2 is  select t.* from person_inform t;                          --行變數     onerow person_inform%rowtype;         begin     open mycur2;     loop            fetch mycur2 into onerow;            exit when mycur2%notfound;            dbms_output.put_line(onerow.id||'  '||onerow.name);     end loop;          close mycur2;end;

單表查詢時,執行SQL語句.

 

declare    --遊標     cursor mycur2 is  select t.* from person_inform t;                          --行變數     onerow person_inform%rowtype;         begin     open mycur2;          loop            fetch mycur2 into onerow;            exit when mycur2%notfound;                         if   onerow.name='a'   then                                      update person_inform set name='aaa' where id=onerow.id;                  dbms_output.put_line('執行成功__11');                               elsif onerow.name='ssss'   then                                      update person_inform set name='s' where id=onerow.id;                  dbms_output.put_line('執行成功__22');                                             else                   dbms_output.put_line(onerow.id||'  '||onerow.name);                                end if;                end loop;          close mycur2;end;

 

 執行語句

declare    --定義遊標    cursor mycur is        select m.station_id, m.name, t.dept_id, t.name, t.id        from   person_inform t        left   join contacts m        on     t.dept_id = m.id        order  by t.dept_id;    --臨時欄位    m_station_id contacts.station_id%type;    m_name       contacts.name%type;    t_dept_id    person_inform.dept_id%type;    t_name       person_inform.name%type;    t_id         person_inform.id%type;begin    open mycur;    loop        fetch mycur            into m_station_id, m_name, t_dept_id, t_name, t_id;        exit when mycur%notfound;            if m_station_id > 0 then            dbms_output.put_line('----' || m_station_id || '  ' || m_name || '  ' ||                                 t_dept_id || '  ' || t_name || '   ' || t_id);                    update person_inform            set    dept_id = m_station_id            where  id = t_id;        end if;        end loop;    close mycur;end;