023 - [Oracle PL/SQL] Cursor - For loop cursor

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

 

 

# basic loop cursor 

DECLARE
  CURSOR c_emp_dept30 is
  SELECT employee_id, first_name FROM employees
  where department_id=30 order by employee_id;
  
  v_empno employees.employee_id%type;
  v_first_name employees.first_name%type;

BEGIN
OPEN c_emp_dept30;

  loop
      fetch c_emp_dept30 into v_empno, v_first_name;
      exit when c_emp_dept30%notfound;
      dbms_output.put_line(v_empno||' '||v_first_name);
  end loop;
close c_emp_dept30;
END;
--------------------------
114 Den
115 Alexander
116 Shelli
117 Sigal
118 Guy
119 Karen

 

 

 

# to use same example above but using for loop cursor

DECLARE
  CURSOR c_emp_dept30 is
  SELECT employee_id, first_name FROM employees
  where department_id=30 order by employee_id;
  # 변수선언이 필요없어서 코딩이 간결해진다.

BEGIN

  for i in c_emp_dept30
  loop
    dbms_output.put_line(i.employee_id||' '||i.first_name);
  end loop;

END;
-----------------------
114 Den
115 Alexander
116 Shelli
117 Sigal
118 Guy
119 Karen

 

 

 

# another way is use the select inside the for 

DECLARE

BEGIN

  for i in (SELECT employee_id, first_name FROM employees
  where department_id=30 order by employee_id)
  
  loop
    dbms_output.put_line(i.employee_id||' '||i.first_name);
  end loop;

END;
-----------------------
114 Den
115 Alexander
116 Shelli
117 Sigal
118 Guy
119 Karen