Oracle涓or寰娇ㄦ娉

MaryDrymn

Oracle for in loop 寰幆鐨勪竴浜涘疄渚,浠ヤ綔瀛︿範鍜屽姞寮轰娇鐢ㄧ啛缁冨害鍙婂満鏅簲鐢.
涓浜涙妧宸

for 璇彞鍚庨潰鐨 loop end loop 鍙互绫绘瘮鎴 c#/java 绛夌紪绋嬭瑷 for 璇彞鍚庨潰鐨勮鍙ュ潡绗﹀彿(鑺辨嫭鍙穥}), 浠ヤ究鐞嗚В鍜屼娇鐢.
涓浜涘疄渚

寰幆杈撳嚭鏁板

-- 寰幆杈撳嚭鏁板
begin
  for i in 1 .. 100 loop
    sys.dbms_output.put_line(i);
  end loop;
end;寰幆绱姞

-- 寰幆绱姞
declare
  var_sum number := 0;
begin
  for i in 1 .. 100 loop
    var_sum := var_sum + i;
  end loop;
  sys.dbms_output.put_line(var_sum); -- 5050
end;鎵归噺鎻掑叆

-- 鎵归噺鎻掑叆
begin
  for i in 1 .. 100 loop
    -- todo: insert into some_table(field1, field2, ..., fieldN) values(valie1, value2, ..., valueN);
    sys.dbms_output.put_line('insert: ' || to_char(i));
  end loop;
  commit;
  sys.dbms_output.put_line('澶氭insert,缁熶竴commit');
end;鎵归噺鏇存柊

-- 鎵归噺鏇存柊
begin
  for i in 1 .. 100 loop
    -- todo: update some_table set fieldN = valueN where fieldN = valueN;
    sys.dbms_output.put_line('update: ' || to_char(i));
  end loop;
  commit;
  sys.dbms_output.put_line('澶氭update,缁熶竴commit');
end;寰幆杈撳嚭鐢ㄦ埛琛ㄤ俊鎭

-- 鏌ヨ浣犺兘绠＄悊鐨勬墍鏈夌敤鎴蜂俊鎭
select user_id, username, created from all_users;

-- 寰幆杈撳嚭鐢ㄦ埛琛ㄤ俊鎭
begin
  for cur_row in (select user_id, username, created from all_users) loop
    sys.dbms_output.put_line(cur_row.username);
  end loop;
end;
-- 褰撳惊鐜璞℃槸姣旇緝闀夸覆鐨凷QL鏃,寤鸿鎻愬彇娓告爣,鏂逛究鍚庣画鏌ョ湅鍜岀淮鎶寰幆杈撳嚭鐢ㄦ埛琛ㄤ俊鎭(娓告爣鎻愬彇)

-- 寰幆杈撳嚭鐢ㄦ埛琛ㄤ俊鎭(娓告爣鎻愬彇)
declare
  -- 鎻愬彇娓告爣瀵硅薄,鏂逛究鍗曠嫭缁存姢SQL
  cursor cursor_all_users is
    select user_id, username, created from all_users;
  -- 娓告爣瀛愰」(鏇村鐨勬椂鍊,琛ㄦ槸鍔ㄦ佽〃,鎵浠ヨ繖鍙ュ０鏄庝篃鍙互鐪佺暐)
  -- cursor_user all_users%rowtype;
begin
  for cursor_user in cursor_all_users loop
    dbms_output.put_line(cursor_user.username);
  end loop;
end;浠ヤ笂灏辨槸杩欑瘒鏂囩珷鐨勫叏閮ㄥ唴瀹逛簡锛屽笇鏈涙湰鏂囩殑鍐呭瀵瑰ぇ瀹剁殑瀛︿範鎴栬呭伐浣滃叿鏈変竴瀹氱殑鍙傝冨涔犱环鍊硷紝璋㈣阿澶у瀵逛腑鍥界孩瀹㈣仈鐩熺殑鏀寔銆傚鏋滀綘鎯充簡瑙ｆ洿澶氱浉鍏冲唴瀹硅鏌ョ湅涓嬮潰鐩稿叧閾炬帴