MSSQL(3)

ALTER PROC dbo.usp_GetEmployee
@DeptID char(3) = NULL,
@FromDate date = '2024-01-30',
@ToDate date = '2024-01-31',
@RowNum int OUTPUT
AS
SET NOCOUNT ON
-- 부서코드가 NULL값이면 오류 메시지 표시 후 종료
IF @DeptID IS NULL
BEGIN
RAISERROR('부서코드가 NULL입니다.',10, 1)
RETURN -1
END
-- 정상적인 경우
SELECT e.EmpID, e.EmpName, e.Gender, e.DeptID, e.HireDate, e.Phone, e.EMail
FROM dbo.Employee e
WHERE e.DeptID = @DeptID
AND
e.HireDate BETWEEN @FromDate AND @ToDate
SET @RowNum = @@ROWCOUNT
RETURN 0
GO

DECLARE @Return int
DECLARE @Rows int

EXECUTE @Return = dbo.usp_GetEmployee
@DeptID = 'SYS',
@FromDate = '2014-01-01',
@ToDate = '2014-03-01',
@RowNum = @Rows OUTPUT

CREATE TABLE #Employee (
EmpID char(5) PRIMARY KEY,
EmpName nvarchar(4),
Gender char(1),
DeptID char(3),
HireDate date,
Phone char(13),
Email varchar(50)
)

-- 변수 선언
DECLARE @Return int
DECLARE @Rows int

-- 저장 프로시저 실행결과를 테이블에 기록
INSERT INTO #Employee EXEC @Return = dbo.usp_GetEmployee
@DeptID = 'SYS',
@FromDate = '2014-01-01',
@ToDate = '2014-12-31',
@RowNum = @Rows OUTPUT

SELECT e.EmpID, e.EmpName, e.HireDate, e.Email
FROM
#Employee e
GO
살짝 의아한건디 보면
@RowNum = @Rows OUTPUT을 보면
@RowNum은 프로시저에서 보내는 값임. 근데 그걸 @Rows로 반환하는 거다. 뒤에 붙은 OUTPUT키워드는 이 매개변수가 저장 프로시저의 외부로 값을 전달한다는 것을 나타낸다.
헷깔릴 수 있는게 원래 값의 대입은 오른쪽에서 왼쪽으로 가는게 일반적인데 이건 좀 흐름에서 벗어난 것 같긴하다.

• SQL은 튜링완전언어는 아니라고 한다.
   그래도 간단한 제어정도는 가능한 것 같다
   EX) 저장 프로시저 : 널처리
   과제

• CREATE PROCEDURE dbo.usp_GetVacation

• @EmployeeID char(5),

• @StartDate date,

• @EndDate date

• AS

• BEGIN

• SELECT 

• 	e.EmpID AS 사원아이디,

• 	e.EmpName AS 이름,

• 	e.EngName AS 영문이름,

• 	e.HireDate AS 입사일,

• 	e.Phone AS 연락처,

• 	v.BeginDate AS 휴가시작일,

• 	v.EndDate AS 휴가종료일,

• 	v.Reason AS 휴가사유

• FROM 

• dbo.Employee AS e

• INNER JOIN

• dbo.Vacation AS v

• ON

• e.EmpID = v.EmpID

• WHERE

• e.EmpID = @EmployeeID 

• AND

• v.BeginDate BETWEEN @StartDate AND @EndDate

• ORDER BY

• v.EndDate DESC

• END

• GO

• EXECUTE dbo.usp_GetVacation 'S0001','2011-01-01', '2011-08-30'

• GO

• 과제 체크사항 적혀있던거
   데이터를 기록하는 저장 프로시저를 만드려면 대상 테이블의 구조를 정확히 알고 있어야 한다.
   참고로 Vacation 테이블의 휴가번호(pk, identity)는 identity 속성 열이므로 값을 전달할 필요가 없음
   Duration 또한 계산된 열이므로 역시 값을 전달할 필요가 없음

• 실행계획을 분석해보쟝 .. 어렵다..

• 
   일단 클러스터 인덱스 스캔이 엥 왜 6of 3?? 3of 6이 아니라 저건 무슨말이지..
   아마 3 of 6이 맞는 표현 같은데 잘못 표시가 된 것 같다..
   아닌데... 200%로 보니까 6of 3가 맞다.. 뭐지
   일단 보류..

• 실행계획 재사용 및 재컴파일
   “우리는 쿼리문에서 우리가 원하는 것이 무언인지를 정의하는 것이지 어떻게 가져올지 정의하는게 아니다”
   “그건 SQL SERVER 가 결정한다”
   SET STATISTICS IO ON
   스캔수, 읽기 표시

• 사용자정의함수
   뷰는 FROM 절에 오기 때문에
   SELECT절과 WHERE절을 사용해서 원하는 데이터만 필터링해서 볼 수 있는 장점
   매개변수가 없어서 다른 형태의 데이터를 원하면 그 형태를 보여주는 뷰를 다시 만들어야 하는 단점
   SELECT문만 가질 수 있는 것도 뷰의 단점
   저장 프로시저
   매개 변수를 가지고 여러 유형의 쿼리문으로 온갖 처리를 할 수 있는 장점
   FROM절에 사용할 수 없기 때문에 주는 결과를 그대로 받아야하는 단점
   쿼리문의 일부 (SELECT / WHERE 절)로 사용할 수 없는 것도 단점
   매개변수도 있고, 뷰처럼 FROM절에도 오고, 저장 프로시저처럼 다양한 쿼리 구문을 가지고 있으면서
   쿼리문의 일부에도 사용할 수 있는
   사용자정의함수
   사용자 정의함수는 크게 3가지로 구문됨
   스칼라
   성능에 좋지 않다. WHERE 절 조건 검사마다 다시 실행된다.
   스칼라 함수를 실행하는 쿼리의 시간복잡도는 못해도 O(N)이다.
   DB는 계속해서 커지는 방향을 가지고 있을텐데 그러한 측면에서 보면 안좋다.
   단일문 (인라인)
   가장 쉬움
   다중문
   편해.. 이게 제일 쓰기가 좋은 것 같아..

• INSTEAD OF 트리거
   SQL SERVER에서 제공하는 특별한 유형의 트리거
   기존의 데이터조작명령(DML) (INSERT, UPDATE, DELETE)을 대신하여 다른 작업을 수행하도록 설계되었음.
   이 트리거는 특히 뷰에 대한 작업을 처리할때 유용하며, 테이블에도 사용될 수 있습니다.
   INSTEAD OF 트리거의 기능
   명령 대체 기능
  이 트리거는 기본 DML명령이 실제로 실행되는 대신에, 트리거에 정의된 사용자 지정로직을 실행한다.
  즉 트리거가 DML 명령의 실행을 ‘대신’한다.
   복잡한 로직 구현을 하는데에 사용
  복잡한 데이터 검증 , 다중 테이블 갱신 , 비즈니스 규칙의 적용 등 복잡한 로직을 구현하는데에 사용될 수 있음
   뷰에 대한 작업처리
  뷰에 INSERT나 UPDATE, DELETE 같은 작업을 수행할 때, 뷰가 여러 테이블로 구성된 경우 해당 작업을 처리하기 어려울 수 있 는데, INSTEAD OF 트리거는 이런 작업을 사용자 정의 방식으로 처리할 수 있게 해준다.

• 이제 DDL 트리거
  자 GPT와 함께 알아보자.
  DDL 트리거는 데이터 정의 언어 이벤트 ( Data Definition Language ) 예를 들어서 CREATE, ALTER, DROP 등등...
  테이블, 뷰, 인덱스, 스키마 등 데이터베이스 객체의 생성 변경 삭제와 같은 작업을 수행할때 발생하는 이벤트에 반응하여 실행되는 특수한 유형의 SQL SERVER 트리거이다.
  DDL 트리거는 데이터베이스 또는 서버 수준에서 정의할 수 있으며, 데이터베이스 스키마의 변경을 관리하고 감사하는데에 유용하게 사용된다.

주요기능을 살펴보자!
1. 변경관리
A. 데이터베이스 스키마 변경사항을 감시하고
B. 무단 변경을 방지한다.
C. 예시로 특정 테이블의 변경을 제한하거나, 스키마 변경 시에 자동으로 관리자에게 알림을 보낼 수 있다.
2. 감사로깅
A. 데이터베이스 객체에 대한 모든 변경 사항을 기록하여, 누가 언제 어떤 변경을 했는지 추적할 수 있다.
i. 이것은 데이터베이스의 무결성을 유지하는데에 중요한 역할을 한다.
B. 자동화작업
i. 특정 DDL 이벤트에 반응하여 자동으로 추가 작업을 수행하도록 설정할 수 있다.
1. 예시로, 테이블이 생성될 때 자동으로 관련된 보안 정책을 적용할 수 있다.
GPT가 알려주는 예시 쿼리문을 보자
CREATE TRIGGER 이름 쏼라
ON DATABASE -> DB수준
FOR CREATE_TABLE -> 이 DDL 이벤트에 대해서 ( 테이블 생성 )
AS
BEGIN
DECLARE @EventData XML = EVENTDATA()
INSERT INTO dbo.TableCreationAudit(ChangeDate, EventData)
VALUES ( GETDATE(), @EventData )
END
GO

(xml은 아직 안했는데.. ) 어쨋든

주의사항 한번 살펴보자

• DDL 트리거는 데이터베이스나 서버 수준의 중요한 변경을 관리하므로, 신중하게 사용해야 한다.
• 트리거로 인한 성능 저하가 발생하지 않도록, 트리거 내의 로직은 가능한 간결하게 유지해야 한다.
• DDL 트리거의 사용은 변경 관리 프로세스와 감사정책의 일부로 통합될 수 있으며, 이는 조직의 데이터 거버넌스 전략을 지원하는데 기여할 수 있다.

 DDL (data definition language) 이벤트가 발생하면 시작되는 트리거
 CREATE ALTER DROP.. ETC
 데이터베이스 영역
 데이터 베이스 레벨에서 체크하는거라고 보면 된다
 예를 들어서, 맘대루 테이블을 삭제하려고 하거나 (예를 들어서 나같은 신입이 실수로 지울 수 있으니...)
 변경하려고 할때 방지할 수 있다. 만드는건 요로코롬
CREATE TRIGGER 트리거 이름
ON DATABASE -> 이거 중요 이게 어떻게 보면 DDL트리거 - DB레벨 이라는걸 인지시켜주는 역할
FOR ALTER_TABLE, DROP_TABLE -> 이걸 하려고 하믄
AS
PRINT ‘’
ROLLBACK TRANSACTION
GO
 한가지 알아야할 게 있는데, 외래키 제약에 대한 충돌이 DDL 트리거 (DB레벨) 보다 우선한다.
 체크를 우선적으로 한다는겨 그래서 에러메시지가 생각과 달랐던 것이어따

 서버 영역
 ON ALL SERVER