[SQL] Basics of SQL Cursor

[Learning Subject]

Basics of SQL Cursor

 

[Objectives]

- Understadning concepts of SQL cursor

- Learning grammar and commands related to SQLcursor

- Being able to manipluate SQL cursor

 

[Core knowledge]

SQL cursor is similar to C pointer, indicating memory for data processing.

Generally SQL restrives data from a query all at once but SQL cursor can visit each record and manipulate data.

 

Key concepts of SQL cursor is as below

- Declaration

- Opening

- Fetching

- Processing

- Closing

 

 

[Learning with Examples]

/*
📝 과제 1: 이름 출력하기
목표: Cursor를 이용해 모든 사원의 이름(이름)을 한 줄씩 출력하자.
*/
-- DECLARING
DECLARE @userName VARCHAR(10)

DECLARE mc CURSOR FOR
select name from USRS_temp

-- OPENING
OPEN mc

-- FETCHING
FETCH NEXT FROM mc into @userName
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @userName
		FETCH NEXT FROM mc INTO @userName
	END

-- CLOSING
CLOSE mc
DEALLOCATE mc

/*
📝 과제 2: 핸드폰 번호 없는 사원만 출력하기
목표: 전화번호(전화번호)가 없는 사원의 이름과 사번을 출력하자.
select top 10 * from usrs_temp
*/
-- DECLARING
DECLARE @userName VARCHAR(10)
DECLARE @employeeID VARCHAR(5)
DECLARE @phoneNumber VARCHAR(15)

DECLARE mc CURSOR FOR
select name, emid, Phon from USRS_temp
where Phon is null or Phon = ''

-- OPENING
OPEN mc

-- FETCHING
FETCH NEXT FROM mc into @userName, @employeeID, @phoneNumber
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @userName+' '+@employeeID
		FETCH NEXT FROM mc INTO @userName, @employeeID, @phoneNumber
	END

-- CLOSING
CLOSE mc
DEALLOCATE mc


/*
📝 과제 3: AD 부서 직원 이름에 “(AD)” 붙여 출력하기
목표: 부서코드(부서코드)가 "AD"인 사원의 이름 뒤에 " (AD)"를 붙여 출력
예시 출력: 이승현 (AD)
*/
-- DECLARING
DECLARE @userName VARCHAR(10)
DECLARE @dept VARCHAR(5)


DECLARE mc CURSOR FOR
select name, dept from USRS_temp
where Dept = 'AD'

-- OPENING
OPEN mc

-- FETCHING
FETCH NEXT FROM mc into @userName, @dept
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @userName+'('+@dept+')'
		FETCH NEXT FROM mc INTO @userName, @dept
	END

-- CLOSING
CLOSE mc
DEALLOCATE mc

/*
📝 과제 4: 중복된 이름 찾기
목표: 이름이 중복된 사원을 Cursor로 순회하면서, 중복된 이름만 출력
*/
-- DECLARING
DECLARE @userName VARCHAR(10)

DECLARE mc CURSOR FOR
select name from USRS_temp
Group by name having COUNT(*) >1

-- OPENING
OPEN mc

-- FETCHING
FETCH NEXT FROM mc into @userName
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @userName
		FETCH NEXT FROM mc INTO @userName
	END

-- CLOSING
CLOSE mc
DEALLOCATE mc

/*
📝 과제 5: 전화번호 맨 뒷자리 기준 정렬 출력
목표: 전화번호 마지막 4자리(예: 5779)를 기준으로 오름차순 정렬해서 이름과 함께 출력
*/
-- DECLARING
DECLARE @userName VARCHAR(10)
DECLARE @phoneNumber VARCHAR(15)

DECLARE mc CURSOR FOR
select name,phon from USRS_temp
order by RIGHT(phon, 4)

-- OPENING
OPEN mc

-- FETCHING
FETCH NEXT FROM mc into @userName, @phoneNumber
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @userName+' '+@phoneNumber
		FETCH NEXT FROM mc INTO @userName, @phoneNumber
	END

-- CLOSING
CLOSE mc
DEALLOCATE mc

 

[Summary]

- SQL cursor is a data object for visitting each sql outcome record

- Key concept of SQL cursor is declaring, opening, fetching, processing and closing