SQL is a set-based language, which means it processes data collectively in batches. For example, consider submitting an SQL query like below:
mployees;
This statement updates 34 rows existing in the employees table. When you submit this query to Oracle database, the SQL engine parses it and updates all 34 applicable rows in a batch, rather than one by one in a procedural manner. However, PL/SQL procedure development frequently requires determining how many rows were processed in the resulting row set. Furthermore, there is often a need to handle each row individually.
A PL/SQL cursor is a pointer that references a memory area (known as the PRIVATE SQL memory area) storing the result set of the executed SQL statement. Since SQL operates on sets of data, queries usually process multiple rows. The PL/SQL cursor facilitates traversal through these rows in the query's result set, allowing access to each row one at a time. Cursors are essential when sequential processing of query results is required, such as iterating through each row to perform specific operations or calculations.
A PL/SQL cursor goes through three main states during its lifecycle:
- Open: In this state, the cursor is initialized and the query associated with it is executed. The result set is made available for fetching rows.
- Fetch: Once the cursor is opened, rows from the result set can be retrieved one at a time using fetch operations. This allows the PL/SQL program to process each row sequentially.
- Close: After all rows have been processed or if the cursor is no longer needed, it can be closed. Closing the cursor releases associated resources and frees up memory.
When you open a declared PL/SQL cursor,
dfa
Implicit Cursors and Cursor Attributes
PL/SQL implicit cursors are a type of cursor that are automatically created and managed by PL/SQL for INSERT, UPDATE, MERGE, DELETE, and SELECT embedded in PL/SQL blocks. You might have noticed, but up until now, every SQL statements used in PL/SQL blocks has been executed with an implicit cursor created internally. While we cannot directly manipulate these cursors, we can take advantage of their attributes to gather pertinent information.
For example, when performing INSERT, UPDATE, or DELETE statements, we can find out how many records were affected - how many records were inserted, updated, or deleted. For instance, let's execute the following anonymous block to update the 34 employees whose department_id is 80 in the employees table.
Explicit Cursors
An explicit cursor in PL/SQL is a named cursor that you define and manage explicitly within your code. Unlike implicit cursors, which are automatically managed by PL/SQL for certain SQL statements, explicit cursors provide more control and flexibility when working with result sets that contain multiple rows or when you need to explicitly manage the cursor's lifecycle.
반면 명시적 커서는 사용자가 직접 정의해서 사용하는 커서를 말한다. 명시적 커서를 사용하기 위해서는 open - fetch - close의 3 단계를 직접 정의해야 한다. 즉 사용할 커서에 이름을 부여하고 이 커서에 대한 쿼리를 선언해야 한다. 명시적 커서란 것이 결과 데이터 집합을 로우 별로 참조해서 무언가 작업을 하기 위한 용도이므로, 당연히 커서를 정의해 사용하는 문장은 SELECT 문이다. 커서의 선언 형태는 다음과 같다.
CURSOR 커서명 [(parameter1, parameter2, ...)]
IS
SELECT
커서명은 사용자가 직접 정의하고 매개변수는 생략이 가능한데, 매개변수를 명시할 때 이들은 SELECT 문의 WHERE 절에서 조건을 체크하는 변수로 사용된다.
Opening a Cursor
커서를 선언한 뒤, 해당 커서를 사용하려면 먼저 커서를 열어야 한다.
OPEN 커서명 [(매개변수1, 매개변수2, ... )]
Fetching a Cursor
정의한 커서를 열고 난 후에야 SELECT 문의 결과로 반환되는 로우에 접근할 수 있다. 결과 집합의 로우 수는 보통 1개 이상이므로 개별 로우에 접근하기 위해서는 앞서 살펴보았던 반복문을 사용해야 하는데, LOOP, WHILE, FOR 문에서 모두 사용 가능하다. 예를들어 LOOP무넹서는 다음과 같이 사용한다.
LOOP
FETCH 커서명 INTO 변수1, 변수2, ...;
EXIT WHEN 커서명%NOTFOUND;
END LOOP;
FETCH...INTO를 통해 커서에서 반환되는 각 컬럼 값을 변수에 할당할 수 있다. 이 때, 변수는 반환된 컬럼 수와 타입이 일치해야 한다. 즉 커서를 선언할 때 연결했던 SELECT 문의 컬럼 수만큼 변수를 명시해야 하고 타입도 맞춰 줘야 한다. 또한 반복문을 사용하므로 해당 커서의 참조가 모두 끝났을 때 반복문을 빠져 나와야 하는데, 이 때 커서명%NOTFOUND 커서 속성을 사용해서 루프를 벗어난다. 묵시적 커서와 달리 명시적 커서는 SQL%대신 선언했던 커서명 % 형태로 속성을 참조한다.
패치 작업이 끝나고 반복문을 빠져 나오면 커서 사용이 모두 끝났으므로 다음과 같이 반드시 커서를 닫아야 한다.
CLOSE 커서명;
그럼 실제로 명시적 커서를 사용해보자. 90번 부에서 속한 사원의 이름 총 (3명)을 출력하는 익명 블록을 만들어 보자.
0 Comments