트랜잭션이란?
데이터베이스의 논리적인 작업의 처리 단위이다.
여기서 작업이라 함은 일반적으로 데이터베이스에서의 데이터 입력，수정，
삭제 등을 말하는 것으로 데이터베이스에 변경사항을 유빌하는 행위를 말한다.
그리고 하나의 입력, 수정, 삭제 직업이 개별적인 트랜잭션으로 처리될 수도 있지만,
하나의 트랜잭션 내에서 여러 작업이 포함될 수도 있다.
그래서 트랜잭션읕 물리적이 아닌 논리적인 작업의 처리단위라고 하는 것이다.
그리고 트랜잭션의 기본 개념은 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
Script.zip
참고
