MS-SQL, Sql Server
- 클라이언트로부터 요청(쿼리문)을 받아 그 결과을 클라이언트에게 전달해주는 데이터베이스 관리시스템(DBMS)
- 표준 SQL 언어인 ANSI SQL을 지원하며, SQL Server 프로그램 언어인 T-SQL(Transact-SQL)도 제공
- SSMS(SQL Server Management Studio)는 SQL Server의 기본 인터페이스 도구
- MS-SQL 은 Microsoft에서 만든 SQL (Structured Query Language)
SQL-Server는 Microsoft에서 개발한 관계형 데이터베이스(RDBMS) (Ms-SQL = SQL Server, 거의 같은 의미)
Microsoft Transact-SQL (T-SQL)
- Microsoft SQL Server에서 사용하는 Language
- T-SQL의 기본적인 문법은 ISO와 ANSI에서 제정한 SQL 표준과 같음 (공식문서)
저장 프로시저 SP(Stored Procedure)
- DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합.
즉, DB에 대한 작업을 정리한 절차를 RDBMS(관계형 데이터 베이스 관리 시스템)에 저장한 쿼리의 집합 - 영구저장모듈이라고도 불림 (여러 쿼리를 하나의 함수로 묶은 것)
- SQL Server에서 제공되는 프로그래밍 기능
- 쿼리문들의 집합으로, 어떤 동작을 여러쿼리를 거쳐서 일괄적으로 처리할 때 사용.
- 자주 사용되는 일반적인 쿼리를 모듈화시켜서 필요할 때마다 호출
- 테이블처럼 각 데이터베이스 내부에 저장
일반 쿼리문과 작동방식 비교
// 일반 sql
SELECT * FROM userTbl WHERE name ='이승기';
SELECT * FROM userTbl WHERE name ='성시경';
SELECT * FROM userTbl WHERE name ='은지원';
-> 해당 쿼리는 where 조건의 값만 다르지만 세 쿼리 모두 다 다른 것으로 인식해버린다.
그렇기 때문에 매번 최적화와 컴파일을 다시 수행해야한다.
// SP
CREATE PROC select_by_name
@Name NVARCHAR(3)
AS
SELECT * FROM userTbl WHERE name =@name;
EXEC select_by_name '이승기';
EXEC select_by_name '성시경';
EXEC select_by_name '은지원';
->첫번째 이승기를 검색하는 과정에서만 최적화 및 컴파일을 수행하고
나머지는 메모리(캐시)에 있는것을 사용문법
- 기본 문법
// CREATE PROCEDURE 프로시저 명으로 시작,
// AS BEGIN 과 END 사이에 원하는 쿼리를 입력하는 방식
CREATE PROCEDURE Schema_Name.Procedure_Name
AS
BEGIN
SELECT 1
END
GO
- 매개변수를 받는 문법
// 프로시저를 호출할 때 프로시저명과 AS 사이 ( ) 로 매개변수를 지정 가능
// 매개변수를 리턴받기 위해서는 타입옆에 OUTPUT 작성
CREATE PROCEDURE Schema_Name.Procedure_Name
(
@site NVARCHAR(500)
,@name NVARCHAR(100)
,@ret INT OUTPUT
)
AS
BEGIN
SELECT @site
END
GO- 실제 사용 예시 - https://gameserverengineer-k.tistory.com/7
SET NOCOUNT ON; // 쿼리 실행 후 영향 받는 행의 수 반환 XCursor
- 데이터 작업을 할 때 테이블에서 집합 단위가 아닌 행 단위로 처리를 해야할 때 사용하는 방식을 - 보통 DML(SELECT, UPDATE, DELETE, INSERT)등으로 이뤄짐
- 집합이 아닌 행 단위로 작업을 수행하기때문에 속도가 느림
명령어
- DECLARE : 커서를 정의하는 등 커서에 관련된 선언을 하는 명령입니다.
- OPEN : 커서가 질의 결과의 첫 번째 튜플을 포인트 하도록 설정하는 명령입니다.
- FETCH : 질의 결과의 튜플들 중 현재의 다음 튜플로 커서를 이동시키는 명령입니다.
- CLOSE : 질의 수행 결과에 대한 처리 종료 시 커서를 닫기 위해 사용하는 명령입니다.
예시
- "PEOPLE" 테이블에서 한 행마다 나이(age)를 1, 2, 3씩 커서문을 사용하여 증가시키기
// table 생성
CREATE TABLE PEOPLE(
NAME VARCHAR(10),
AGE INT,
)
INSERT INTO PEOPLE(NAME,AGE)VALUES('안정균',23)
INSERT INTO PEOPLE(NAME,AGE)VALUES('김산하',25)
INSERT INTO PEOPLE(NAME,AGE)VALUES('이승현',28)
// 예상 결과 ->
Name Age Name Age
1 안정균 23 1 안정균 24 (+1)
2 김산하 25 2 김산하 27 (+2)
3 이승현 28 3 이승현 31 (+3)
// 커서문 진행
declare
@index int,
@name varchar(10),
@age int
set @index = 0;
/* 커서 선언 */
declare cur
/* 조회하고자 하는 select문 선언 */
cursor for select name, age from PEOPLE
/* 커서 오픈 */
open cur
/* 커서의 첫번째 행을 가지고 온다. */
fetch next from cur into @name,@age
/* 커서의 마지막 행이 될 때까지 반복작업을 실행하는 부분 (작업하고자 하는 내용을 아래 입력) */
while @@FETCH_STATUS = 0
/* 작업하고자 하는 내용 Begin */
begin
set @index = @index +1;
update PEOPLE
set age = age + @index
where name = @name
/* 작업하고자 하는 내용 END */
/* 커서의 다음 행을 가지고 온다. */
fetch next from cur into @name, @age
End
/* 커서 닫기 */
close cur
/* 커서 참조 제거 */
deallocate cur
- 커서문은 행 단위로 작업을 하는 방식으로 진행이 되기때문에 데이터가 많아 질수록 속도가 느려지는 것을 피하지 못한다.
- 그렇기에 커서문은 최대한 지양하는 것이 좋으며, 예제와 같이 특수하게 행 별로 작업을 해야할 것이 있을때만 사용을 해야 한다.
트리거
- 데이터의 변경(Insert, Delete, Update)문이 실행될 때 자동으로 실행되는 프로시저를 의미
- 여러번 프로시저를 호출하거나 SQL 명령을 실행할 필요가 없기 때문에 복잡성을 줄일 수 있음
트리거 종류
ALTER 트리거
- 테이블에 insert, update, delete 등의 작업이 일어난 후에 작동
- 테이블에만 작동 (뷰 x)
INSTREAD OF 트리거
- before 트리거라고도 부르며 테이블이나 뷰에 이벤트가 작동하기 전에 작동
- 테이블 뿐아니라 뷰에도 작동, 주로 뷰가 업데이트 가능하도록 사용
- insert, update, delete 세가지 이벤트로 작동
CLR 트리거
- T-SQL 저장 프로시저 대신 .NET Framework에서 생성되는 트리거
생성, 예시
https://blog.naver.com/isaac7263/222247607821
- inserted, deleted 테이블
- 물리적으로 존재하는 테이블 X
- 특정 테이블의 데이터에 변경이 가해졌을 때 트리거를 위해 자동으로 만들어지는 논리적 테이블
Reference
https://velog.io/@sweet_sumin/%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-Stored-Procedure - 저장 프로시저란
https://devkingdom.tistory.com/323 - 일반쿼리와 SP 예시
https://bonohubby.com/entry/Transact-SQLT-SQL-1-%EB%8D%B0%EC%9D%B4%ED%84%B0-%ED%98%95%EC%8B%9D - T-SQL
https://gameserverengineer-k.tistory.com/7 - 실제 사용 예시
https://jeongkyun-it.tistory.com/76 - cursor
https://blog.naver.com/isaac7263/222247607821 - trigger
https://blog.naver.com/PostView.naver?blogId=gluestuck&logNo=221997860995&parentCategoryNo=&categoryNo=28&viewDate=&isShowPopularPosts=true&from=search - 프로시저 값 호출해보기
https://engineeringcode.tistory.com/408 - 프로시저를 호출
하루에 한 개념씩