[SQL] 21장. 커서 이용하기

[손에 잡히는 10분 SQL - 벤 포터 지음, 박남혜 옮김] 책의 학습 후 정리자료입니다.

---

📍 커서 이해하기

• SQL 검색 작업은 결과 집합이라고 부르는 여러 행을 반환한다.
• 결과로 가져온 SQL 구문과 일치하는 모든 결과인데, 결과로 한 행도 가져오지 않거나 하나 이상의 결과를 반환할 수 있다.
• 간혹 행을 앞뒤로 이동하며 데이터를 가져와야 할 필요가 있는데, 이럴 때 커서 를 사용할 수 있다.
• 커서는 DBMS 서버에 저장된 쿼리로로서 SELECT 문은 아니지만, SELECT 문으로 가져온 결과 집합이다.
• 커서는 한번 저장되면, 프로그램에서 필요할 때마다 데이터를 상하로 탐색하면서 검색 결과를 가져올 수 있다.

📒 결과 집합

SQL 쿼리로 가져온 결과

• DBMS마다 커서 옵션과 기능을 다르게 지원한다.
• 커서는 사용자가 화면의 데이터를 위, 아래로 탐색하며 필요에 따라 변경할 수 있는 대화형 프로그램에서 자주 사용한다.
• 다음은 일반적으로 자주 사용하는 커서의 옵션과 기능이다.
  • 커서에 읽기 전용으로 표시하여, 데이터를 읽을 수는 있지만 업데이트나 삭제는 못하게 하는 기능
  • 방향과 위치를 제어할 수 있는 기능(전방, 후방, 첫 번째, 마지막, 절대 위치, 상대 위치 등)
  • 특정한 열만 수정할 수 있게 표시하고, 그 외의 열은 수정하지 못하게 하는 기능
  • 커서를 특정한 요청이나 모든 요청에 접근할 수 있게 하는 범위 지정 기능
  • DBMS에서 가져온 데이터를 복사하여, 커서가 연(open) 후 데이터를 사용하는 사이에 데이터가 변경되지 않게 하는 기능

---

📍 커서 사용하기

• 커서는 다음과 같은 방식으로 사용된다.
  • 커서는 반드시 사용하기 전에 선언하여야 한다. 이 절차는 실제 어떤 데이터도 가져오지 않고, 단지 사용할 SELECT 문과 커서 옵션을 정의한다.
  • 선언된 커서를 사용하려면, 먼저 커서를 열어야 한다. 그러면 이전에 정의한 SELECT 문으로 데이터를 가져온다.
  • 필요할 때마다 데이터를 가진 커서에서 개별 행을 가져온다.
  • 커서를 다 사용했으면 커서를 닫고(close), 가능하면 해제시켜야 한다.
• 한번 커서를 선언하면, 필요할 때마다 몇 번이고 다시 열고 닫을 수 있다.
• 또한 커서를 한번 열면 그 안의 데이터를 몇 번이고 가져올(fetch) 수 있다.

---

📌 커서 만들기

• 커서는 DECLARE 문을 사용하여 만들 수 있는데, 이 또한 DBMS마다 다르다.
• DECLARE로 커서 이름을 선안한 다음 SELECT 문을 선언한다.

🧷 Db2, MaraiaDB, MySQL, SQL Server

DECLARE CusCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;

🧷 Oracle, PostgreSQL

DECLARE CURSOR CusCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL;

---

📌 커서 사용하기

• 커서는 OPEN CURSOR 문을 사용하여 동작시킬 수 있는데, 이는 매우 간단할 뿐만 아니라 대부분의 DBMS에서 모두 똑같은 문법을 지원한다.

📒 OPEN CURSOR

OPEN CURSOR CustCursor

• OPEN CURSOR 문이 처리될 때 쿼리가 수행되며, 나중에 탐색하거나 가져오기 위한 데이터를 저장한다.

📒 FETCH

• 이제 FETCH 문을 이용하여 데이터에 접근할 수 있다.
• FETCH는 어떤 행을 가져올지, 어디서부터 가져올지 그리고 어디에 저장할지를 정의한다.

🧷 Oracle

DECLARE TYPE CustCursor IS REF CURSOR
	RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
	OPEN CustCursor;
    FETCH CustCursor INTO CustRecord;
    CLOSE CustCursor;
END;

• FETCH는 현재 행(자동으로 첫 번째 행에서 시작한다)을 가져와서 CustRecord 라는 변수에 저장하였다.

DECLARE TYPE CustCursor IS REF CURSOR
	RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
	OPEN CustCursor;
    LOOP
    FETCH CustCursor INTO CustRecord;
    EXIT WHEN CustCursor%NOTFOUND;
    ...
    END LOOP;
    CLOSE CustCursor;
END;

• FETCH 문은 LOOP 안에 있어서 현재 행을 가져오는 것을 반복한다.
• EXIT WHEN CustCursor%NOTFOUND 코드는 더 가져올 행이 없을 때 루프를 빠져나가게 한다.

🧷 Oracle

DECLARE @cust_id CHAR(10),
		@cust_name CHAR(50),
        @cust_address CHAR(50),
        @cust_city CHAR(50),
        @cust_state CHAR(5),
        @cust_zip CHAR(10),
        @cust_country CHAR(50),
        @cust_contact CHAR(50),
        @cust_email CHAR(255)
OPEN CustCursor
FETCH NEXT FROM CustCursor
	INTO @cust_id, @cust_name, @cust_address,
    	 @cust_city, @cust_state, @cust_zip,
         @cust_country, @cust_contact, @cust_email
...
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM CustCursor
	INTO @cust_id, @cust_name, @cust_address,
    	 @cust_city, @cust_state, @cust_zip,
         @cust_country, @cust_contact, @cust_email
...
END
CLOSE CustCursor

• 이 예제에서는 가져오는 열에 대한 변수를 각각 선언하였고, FETCH 문으로 행을 가져와 변수에 저장하였다.
• WHILE 문은 행을 반복하기 위해 사용되었는데, WHILE @@FETCH_STATUS = 0 조건을 만족하면(더 가져올 행이 없을 때) 반복문을 빠져나온다.

---

📌 커서 닫기

• 생성된 커서는 사용이 끝난 후 닫아야 한다.
• SQL Server와 같은 일부 DBMS에서는 커서가 사용한 리소스를 명시적으로 해제해야 한다.

🧷 Db2, Oracle, PostgreSQL

CLOSE CustCursor

🧷 Microsoft SQL Server

CLOSE CustCursor
DEALLOCATE CURSOR CustCursor