021 - [Oracle PL/SQL] Cursor - Explicit Cursor

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

 

 

Every SQL statement executed by the Oracle server has an associated individual cursor:
*   Implicit cursors: Declared and managed by PL/SQL for all
DML and PL/SQL SELECT statements
*   Explicit cursors: Declared and managed by the programmer


Explicit Cursor Operations
You declare explicit cursors in PL/SQL when you have a SELECT statement that returns multiple rows. You can process each row returned by the SELECT statement.
Explicit cursor functions:
*   Can perform row-by-row processing beyond the first row returned by a query
*   Keep track of the row that is currently being processed
*   Enable the programmer to manually control explicit cursors in the PL/SQL block

 

 

 

 

Cursor Attributes

 

 

 

Hints when declaring a cursor
• Do not include the INTO clause in the cursor declaration because it appears later in the FETCH statement.
• If processing rows in a specific sequence is required, use the ORDER BY clause in the query.
• The cursor can be any valid SELECT statement, including joins, subqueries, and so on.

 

 

 

Hints when Opening a cursor
The OPEN statement executes the query associated with the cursor, identifies the active set, and positions the cursor pointer at the first row. The OPEN statement is included in the executable section of the PL/SQL block.
OPEN is an executable statement that performs the following operations:
1.    Dynamically allocates memory for a context area
2.    Parses the SELECT statement
3.    Binds the input variables (sets the values for the input variables by obtaining their memory addresses)
4.    Identifies the active set (the set of rows that satisfy the search criteria). Rows in the active set are not retrieved into variables when the OPEN statement is executed. Rather, the FETCH statement retrieves the rows from the cursor to the variables.
5.    Positions the pointer to the first row in the active set
Note: If a query returns no rows when the cursor is opened, PL/SQL does not raise an exception.
You can find out the number of rows returned with an explicit cursor by using the <cursor name >  &ROWCOUNT attribute.

 

 

 

Hints when fetching data from a cursor
Fetching Data from the Cursor
The FETCH statement retrieves the rows from the cursor one at a time. After each fetch, the cursor advances to the next row in the active set. You can use the &NOTFOUND attribute to determine whether the entire active set has been retrieved.

The FETCH statement performs the following operations:
1. Reads the data for the current row into the output PL/SQL variables
2. Advances the pointer to the next row in the active set

 

 

 

Hints when closing the Cursor
Closing the Cursor
The CLOSE statement disables the cursor, releases the context area, and "undefines" the active set.
Close the cursor after completing the processing of the FETCH statement. You can reopen the cursor if required. A cursor can be reopened only if it is closed. If you attempt to fetch data from a cursor after it has been closed, then an INVALID_CURSOR exception will be raised.
Note: Although it is possible to terminate the PL/SQL block without closing cursors, you should make it a habit to close any cursor that you declare explicitly to free up resources.
There is a maximum limit on the number of open cursors per session, which is determined by the
OPEN_ CURSORS parameter in the database parameter file. (OPEN_CURSORS = 50 by default.)

 

 

 

# create cursor to print employee_id, first_name for department_id =30 
# method 1
# select문에 필요한 필드별로 각각의 변수를 선언하는 방식.

DECLARE
  CURSOR c_emp_dept30 is
  SELECT employee_id, first_name FROM employees
  where department_id=30;
  
  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); -- the exit should before output
  end loop;

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

 

 

 

# method 2
# rowtype으로 변수를 선언하여 테이블 전체의 필드를 변수로 할당.

DECLARE
  CURSOR c_emp_dept30 is
  SELECT  employee_id, first_name FROM employees
  where department_id=30;
  
   v_emp_rec employees%rowtype;
  
BEGIN
OPEN c_emp_dept30;

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

 

 

 

# method 3
# rowtype으로 변수를 선언하여 커서 전체를 변수로 할당. 테이블 전체를 할당하는것 보단 메모리가 적게 사용됨.
# pl/sql records based on cursor, this is the 3rd method for creating pl/sql records
# 1 is programmer, 2 is %rowtype/records, 3 is cursor records

DECLARE
  CURSOR c_emp_dept30 is
  SELECT  employee_id, first_name FROM employees
  where department_id=30;
  
   v_emp_rec c_emp_dept30%rowtype;   
BEGIN
OPEN c_emp_dept30;

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

 

 

 

--update the salaries for employees in  dept30 using cursor ( +100 for each one )

DECLARE
  CURSOR c_emp_dept30 is
  SELECT employee_id, first_name,salary FROM employees
  where department_id=30;
  
  v_empno employees.employee_id%type;
  v_first_name employees.first_name%type;
  v_salary employees.salary%type;
BEGIN
OPEN c_emp_dept30;
 loop
      fetch c_emp_dept30 into v_empno, v_first_name,v_salary;
      exit when c_emp_dept30%notfound;
      update employees
      set salary=salary+100
      where employee_id=v_empno;
  end loop; 
  close c_emp_dept30;
  commit;
END;
------------------------------------------------------------------------


--original data

select employee_id, first_name,salary from employees
where department_id=30;

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        114 Den                       11000
        115 Alexander                  3100
        116 Shelli                     2900
        117 Sigal                      2800
        118 Guy                        2600
        119 Karen                      2500

6 rows selected. 

--after cursor

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        114 Den                       11100
        115 Alexander                  3200
        116 Shelli                     3000
        117 Sigal                      2900
        118 Guy                        2700
        119 Karen                      2600

6 rows selected.