[MSSQL]MS-SQL 3일차 - Transaction / View / StoreProcedure

트랜잭션이란? 

데이터베이스의 논리적인 작업의 처리 단위이다.

여기서 작업이라 함은 일반적으로 데이터베이스에서의 데이터 입력，수정，

삭제 등을 말하는 것으로 데이터베이스에 변경사항을 유빌하는 행위를 말한다.

그리고 하나의 입력, 수정, 삭제 직업이 개별적인 트랜잭션으로 처리될 수도 있지만,

하나의 트랜잭션 내에서 여러 작업이 포함될 수도 있다.

그래서 트랜잭션읕 물리적이 아닌 논리적인 작업의 처리단위라고 하는 것이다.

그리고 트랜잭션의 기본 개념은 All or Nothing 이다.

 # 트랜잭션

-- 명시적 트랜잭션   : Begin(O),Commit(O)

-- 암시적 트랜잭션   : Begin(X),commit(O) ==> 오라클

-- 자동커밋 트랜잭션 : Begin(X),commit(X) ==> MS SQL Server

-- set implicit_transactions on

-- AA 창 --

select * from TC

begin tran

 update TC set id = 11 where id = 10

 update TC set id = 22 where id = 20

 update TC set id = 33 where id = 30

commit tran

select @@TRANCOUNT

-- BB 창 --

 use master

 Alter database sqlDB set allow_snapshot_isolation on

 set Transaction isolation level snapshot

  -- 잠금에 걸린 Tabble이라도 메모리(캐시)가 아닌 

-- 데이터 파일의 내용을 무조건 읽어올수 있도록 만들어 줌

select * from TC

# 체크포인트

체크 포인트(Check Point) 프로세스는 현재까지 메모리에서 진행된 모든 내용을 데이터 파 일과 로그 파일에 기록하는 프로세스로이다. 마치 워드나 엑셀 같은 프로그램에서 저장버튼 을 누르는 것과 같은 역할을 수행한다. 미러한 체크 포인트는 기본적으로 SQL Server가 1분마다 상황을 체크해서 많은 작업(많은 작업에 대한 판단은 SQL Server가 한다)이 있었던 경우 발생하기 때문에 궁극적으로 비주 기적으로 발생된다고 할 수 있다. 다시 말해서 데이터베이스에서 많은 작업이 일어나면 체 크 포인트가 발생하는 주기는 1분이 될 것이며, 데이터베이스에서 일어나는 작업이 많지 않 으면 체크 포인트가 발생하는 주기를 예측할 수 없다는 말이 된다.

# 레이지 라이터 & 로그라이터

 - 레이지 라이터(Lazy Writer)

레이지 라이터(Lazy Writer)라는 프로세스는 메모리 상의 프로시저 캐시에서 시용 빈도가

낮은 실행 계획을 메모리에서 제거하고 버퍼 캐시에 있는 더티 페이지(Dirty Page)들을 디

스크에 기록함으로써 메모리상에 빈 공간을 확보하기 위해서 동작하며, 이 역시 백그라운드

프로세 스로 주기적으로 동작한다.

 - 로그 라이터(Log Writer)라는 프로세스는 트랜잭션이 완료(Commit, 커밋)되면 자동으로

커밋된 트랜잭션에 대한 로그를 트랜잭션 로그 파일에 기록하는 프로세스이다.

### 참조 제약 조건

create table byTbl

(no <-- 숫자 증가값

 name --> uTbl(userid)

 prodname

 groupname

 price

 amount

 ------------------------------------------

  create Table byTbl

(num int Identity Not Null Primary Key,

 userid nchar(8) Not Null 

Foreign Key References uTbl(userid),

 prodName nchar(6) Not Null,

 groupName nchar(4),

 price int Not Null,

 amount smallint Not Null

)

go

### JOIN 사용

-- userTbl,buyTbl에서 하세요

--1) 키가 180 이상인 사람의 userid, 이름, 거주지는?

select userID, name, addr

from userTbl

where height >= 180

--2) 핸드폰 번호가 011,018 인 사람의 userid, 이름, 거주지는?

select userID,name,addr,mobile1

from userTbl

where mobile1 in ('011','018')

--3) 이름이 박주영인 사람의 구매한 목록, 즉

-- 이름,userid, 상품명, 가격, 수량을 구하시요.

select u.name, u.userID, b.prodName, b.price, b.amount

from userTbl u, buyTbl b

where u.userID = b.userid

  and u.name = '박주영'

  

SELECT     userTbl.userID, userTbl.name, buyTbl.prodName, buyTbl.price, buyTbl.amount

FROM         buyTbl INNER JOIN

             userTbl ON buyTbl.userid = userTbl.userID

WHERE     (userTbl.name = N'박주영')

--4) 키가 182 이상인 사람들의 이름,키,userid, 

-- 상품명,가격,수량은?

select u.name, u.height, u.userID, b.prodName, b.price, b.amount

from userTbl u, buyTbl b

where u.userID = b.userid

and u.height >= 182

--5) 키가 182 이상인 사람별 이름, 총구매금액 중 1000이상인

-- 것만 나타내시오

select u.name, sum(b.price*b.amount) as 총구매금액

from userTbl u, buyTbl b

where u.userID = b.userid

and u.height >= 182

group by u.name

having sum(b.price*b.amount) >= 1000

--6) 안정환보다 키가 큰 사람의 이름,birthyear,addr,height ?

--1> 안정환의 키?

select height

from userTbl

where name = '안정환' --> 182

--2> 그 키보다 큰사람의 ~~~~

select name,birthYear,addr,height

from userTbl

where height > 182

--3> 문장 합치기

select name,birthYear,addr,height

from userTbl

where height > (select height

from userTbl

where name = '안정환')

--7) 김남일보다 키가 큰 사람의 이름과 거주지,키

--   구매상품명,가격,수량은?

--1> 김남일의 키는?

select height

from userTbl

where name = '김남일' -- 183

--2> 그 키보다 큰 사람의 ~~~?

select u.name,u.addr,u.height,b.prodName,b.price,b.amount

from userTbl u, buyTbl b

where u.userID = b.userid

and u.height > 183

--3> 문장합치기

select u.name,u.addr,u.height,b.prodName,b.price,b.amount

from userTbl u, buyTbl b

where u.userID = b.userid

and u.height > (select height

from userTbl

where name = '김남일')

### VIEW 설명

 # 뷰(View)

 1) 정의 : Select 문장의 덩어리 

(가상의 논리적 테이블)

 2) 목적 : 편의성, 보안성, 성능향상

 3) 종류 :

- 일반 View

- Index View

- Partition View

drop view v_userTbl

create view v_userTbl

as

 select userid,name,addr from userTbl

select * from v_userTbl 

where name='이천수'

select * from userTbl

update v_userTbl set addr='ZZ' where name = '이천수'

update v_userTbl set height=100 where name = '이천수'

insert into v_userTbl values ('KKK','김금갑','PP')

insert into v_userTbl (userid,name,addr) 

values ('KKK','kkKim','PP')

create View v_userTbl3

 as

 select userid,name,birthyear,addr from userTbl

insert into v_userTbl3 values ('KKK','kkKim','1977','PP')

select * from v_userTbl3

delete v_userTbl3 where userid='KKK'

select * from userTbl

create View v_userTbl4

 as

 select * from userTbl where height >= 180

select * from v_userTbl4

delete v_userTbl4 where height < 180 

select * from userTbl

update v_userTbl4 set birthyear ='1111' 

where height < 180

insert into v_userTbl4 

values ('TTT','ggHong','4444','GG',010,0101,190)

insert into v_userTbl4 

values ('ZZZ','ggHong','4444','GG',010,0101,100)

select * from userTbl

alter view v_userTbl4

as

 select * from userTbl where height >= 180

 with check option

select * from sys.views

Exec sp_helptext 'v_userTbl4'

alter View v_userTbl4

 with Encryption

as

 select * from userTbl where height >= 180

with check option

use sqldb

select * from userTbl --> userTbl2

select * into userTbl2 from userTbl

select * from userTbl2

-- view를 생성 

-- (userid, name, birthyear, addr, height)

create view v_uTbl2

as

select userid,name,birthyear,addr,height

from userTbl2

select * from v_uTbl2

-- userTbl2 에서 height 컬럼을 제거해보세요

alter table usertbl2

drop column height

go

select * from userTbl2

select * from v_uTbl2 <-- 실행되는지요?

drop view v_uTbl3

create view v_uTbl3

with schemabinding

as

select userid,name,birthyear,addr,mobile2

from dbo.userTbl2 --> table명 앞에 dbo 스키마를 선언

go

select * from v_uTbl3

alter table usertbl2

drop column mobile2

go

## SQL Injection

 ID     [  admin' or 1=1 !--  ] 

 PWD    [  1234  ]  

select * from where id ='swhong' or 1=1 

   select * from where id='admin' or 1=1 1!--' and pwd = '1234'

  Exec sp_userid('jane','1234')

 ### 저장프로시저 (Stored Procedure)

 1) 정의 : - Transact-SQL 문장의 집합 

 2) 용도 :

- 어떠한 동작을 절차적 일괄처리 작업하는데 사용

- SQL Server에서 사용하는 프로그래밍 기능

 3) 특징 :

- SQL Server의 성능향상 (첫 실행시 컴파일, 재컴파일 안함)

- 모듈식 프로그래밍 가능

- 보안강화 (저장프로시저 마다의 권한할당 가능)

- 네트워크 전송량 감소 (긴 쿼리문장의 단순화)

 4) 장점 :

- 빠르다

- 파라미터를 사용할 수 있다

- 여러 어플리케이션과 공유할수 있다

------------------------------------------------------------------

 # 일반적인 T SQL 구문 실행절차

1. 첫실행시

 1) 구문분석

 2) 개체검사

 3) 권한확인

 4) 최적화경로확인

 5) 컴파일(with 실행계획) --> 메모리 상주

 6) 실행

2. 두번째실행시 (구문이 같아야)

 1) 기 컴파일 사용

 2) 실행

 # 저장 프로시저

1. 첫실행시

 1) 구문분석

 2) 개체검사

 3) 권한확인

 4) 최적화경로확인

 5) 컴파일(with 실행계획) --> 메모리 상주

 6) 실행

2. 두번째실행시 (구문이 같지않아도)

 1) 기 컴파일 사용

 2) 실행

 use sqldb

create Procedure usp_userTbl

as

 select * from userTbl

Exec usp_userTbl

-- View 일 경우 --

create view v_Test 

as

   select * from userTbl

   

select * from v_Test where userID = 'AJH'

create Proc usp_userTbl2

 @userid nchar(8)

as

 select * from userTbl where userid = @userid

go

Exec usp_userTbl2 'AJH'

create Proc usp_userTbl3

 @a int,

 @b int

as

 select * from userTbl where birthyear > @a and birthyear <= @b

go

Exec usp_userTbl3 1978,1982

create Proc usp_userTbl4

@userid nchar(8)

as

declare @byear int

select @byear = birthyear from userTbl where userid = @userid

if (@byear >= 1978)

Begin

Print 'Low Age'

end

else

Begin

Print 'High Age'

end

go

Exec usp_userTbl4 'AJH'

-- Input Value, Output Value

create Proc usp_userTbl5

@userid nchar(8),

@outAge int OutPut

as

Declare @bYear int

select @bYear = birthyear from userTbl where userid = @userid

select @outAge = Year(getdate()) - @bYear

-- Execute SP

declare @iAge int

Exec usp_userTbl5 'AJH', @iAge Output

Print 'Your Age ==> ' + cast(@iAge as char(5))

select @@NESTLEVEL

### 사용자 정의 함수 (User Defined Function)

 1) 정의 : 리턴값을 갖는 프로시저의 종류

 2) 유형 : 

- 스칼라(Scalar) 함수 

- 테이블 함수

  - 인라인 테이블 값 (Inline Table Valued)함수

  - 다중문 테이블 값 (Multi Statement Table Valued)함수

 3) 제약사항 :

- 함수 내부에 Try Catch 문을 사용할 수 없다

- 함수 내부에 DDL문(Create/Alter/Drop)을 사용할 수 없다

 @ 스칼라 함수: Return문에 의해서 하나의 단일값을 돌려주는 함수

 @ 인라인 테이블값 함수 : Select 문장의 결과 집합을 돌려주는 함수

 @ 다중문 테이블 값 함수 : 정의된 임시 테이블 자체를 돌려주는 함수

 create #tmpTbl (id txt) : 사용할 수 없음

Inline_Fuction.sql

Muti_Fuction.sql

Scalar_Fuction.sql