sql query summary

use schemaTestDB;
create schema schema_A;
create schema schema_B;
create table schema_A.TBL_A(no int);
create table schema_B.TBL_B(id int);
create table schema_A.TBL_AA(code int);
SELECT * from schema_A.TBL_A;
Create table department(deptNo int not null,
deptName nvarchar(20),
location nchar(20))
select * into departmentNew from department Where 1=0;--무조건 거짓,테이블 구조만 복사
create sequence dept_seq
start with 10
increment by 10;--시퀀스를 넣어서 번호 자동 생성
insert departmentNew values(next value for dept_Seq,'경기부','서울');
insert departmentNew values(next value for dept_Seq,'인사부','인천');
insert departmentNew values(next value for dept_Seq,'전산부','수원');
insert departmentNew values(next value for dept_Seq,'영업부','대전');
select * from departmentNew;
--시퀀스현재값
select current_value from sys.sequences where name='dept_seq';
--시퀀스의 시작값을 다시 수정하기 위한 구문
alter sequence dept_seq restart with 15;
insert departmentNew values(next value for dept_Seq,'생산부','안산');
insert departmentNew values(next value for dept_Seq,'회계부','광주');
select * from departmentNew;
alter sequence dept_seq restart with 35 increment by 5;
insert departmentNew values(next value for dept_Seq,'회계부1','광주');
insert departmentNew values(next value for dept_Seq,'회계부2','광주');
select * from departmentNew;
--시퀀스의 반복
create sequence dept_seq1
start with 100 increment by 100
minvalue 100
maxvalue 500
cycle; --반복설정
insert departmentNew values(next value for dept_Seq1,'생산부1','서울');
insert departmentNew values(next value for dept_Seq1,'인사부1','인천');
insert departmentNew values(next value for dept_Seq1,'전산부1','수원');
insert departmentNew values(next value for dept_Seq1,'영업부1','대전');
insert departmentNew values(next value for dept_Seq1,'영업부2','대전');
insert departmentNew values(next value for dept_Seq1,'영업부3','대전');
select * from departmentNew;
--테이블 생성시에 시퀀스 활용법
create sequence auto_seq
start with 1
increment by 1;

create table seqTest(no int default(next value for auto_seq), name nchar(30));

insert seqTest(name) values('김동길');
insert seqTest(name) values('김말길');
insert seqTest(name) values('김동말');
select * from seqTest

--변수선언
/* 변수선언형식:
Declare @변수명 데이터형식;
변수에 값대입:
@변수명 = 값;
변수의값출력:
select @변수이름;
*/

Declare @aaa int;
declare @bbb smallint, @ccc decimal(4,2);
declare @ddd nchar(20);
set @aaa=10;
set @bbb =5;
set @ccc=10.33;
set @ddd = '이름:';
select @aaa;
select @bbb + @ccc;
select @aaa,* from departmentNew;
select top(@bbb) deptName,* from departmentNew;

--if 문

declare @var1 int;
set @var1 = 10;
if @var1 = 10
begin
print '@var1이 10이다'
end
else
begin
print '@var1이 10이아니다'
end;

--변수 초기화
declare @var2 int =100;
select @var2;

declare @score int =78,@res nchar(1)
if @score >= 90
set @res = 'A'
else if @score >= 80
set @res='B'
else if @score >= 70
set @res='C'
else if @score >= 60
set @res='D'
else
set @res='F'
print '점수:' + cast(@score as nchar(30));
print N'학점:' + @res; --한글이기때문에 N을 넣어야함.

--case
declare @score int = 100,@res  nchar(1);
set @res =
case 
when(@score >= 90) then 'A'
when(@score >= 80) then 'B'
when(@score >= 70) then 'C'
when(@score >= 60) then 'D'
else 'F'
end
print '점수:' + cast(@score as nchar(30));
print N'학점:' + @res; --한글이기때문에 N을 넣어야함.


select name,price,case 
when (amount >=80) then '최고인기상품'
when (amount >=50) then '인기상품'
when (amount >= 30) then '추천상품'
else '보통상품'
end as '상품등급'
from products;

--반복문 : while / break / continue / return
/*
while 조건
begin
SQL명령문
end
*/
declare @i int = 0 ,@sum int =0
while(@i<=100)
begin
set @sum += @i
set @i += 1
end

select @sum;


declare @i int = 1,@sum int =0;

while(@i <=100)
begin
if(@i % 3 =0)
begin
print '3의배수:' + cast(@i as char(3))
set @i += 1
continue --while end문으로 이동
end
set @sum  += @i --3의배수시 실행않됨
if(@sum>2000) break --3의배수시 실행않됨,while문을 빠져나옴
set @i += 1 --3의배수시 실행않됨
end;

print '합계:' + cast(@sum as char(10))



--goto : 제한적으로사용,지정한위치로 이동

declare @i int = 1,@sum int =0;

while(@i <=100)
begin
if(@i % 3 =0)
begin
print '3의배수:' + cast(@i as char(3))
set @i += 1
continue --while end문으로 이동
end
set @sum  += @i --3의배수시 실행않됨
if(@sum>2000) goto aa --3의배수시 실행않
set @i += 1 --3의배수시 실행않됨
end

aa:

print '합계:' + cast(@sum as char(10))

--waitfor 실행을 잠시 멈추고 기다림
--waitfor time,waitfor delay

begin 
waitfor delay '00:00:03'
print '3초간 일시정지후 실행합니다..'
end
begin 
waitfor time '12:30'
print '정해진시간에 실행합니다...'
end
--예외 처리(try catch문)
/*
begin try
sql 명령
end try
begin
catch
오류시실행될 명령
end
catch
*/
begin try
insert departmentNew values('테스트','','');
print '정상처리되었습니다..'
end try

begin catch
print '오류발생'
print '오류번호:'
print error_number()
print '오류메세지'
print error_message()
print '오류라인'
print error_line() 
end catch
--오류발생시 사용할 함수 제공
/*
.error_number():오류번호
.error_message():오류메세지
.error_serverity():오류심각성
.error_state():오류 상태
.error_line():오류라인
.error_procesude():오류발생 프로시저나 트리거 이름을 알려줌.
*/

--오류를 강제로 발생시키는 함수
--raiserror,throw
--오류의 심각도(0~18까지,기본값은 16)
--raiserror(메세지,심각도,상태)
--throw(메세지,상태)

raiserror('오류발생',16,1);
throw 50000,'오류발생',1;--예외번호는 50000이상

--execute(exec),스파이스열,임시테이블
--exec, execute:SQL문을 실행시키는 명령어
declare @sql varchar(100);
set @sql = 'select * from departmentnew where deptno = 10' 
exec(@sql);

--테이블의 명칭 : tbl_04_12,tbl_04_13,동적으로 테이블을 만들어서 사용가능

declare @todayDate Date
declare @Month varchar(2)
declare @day varchar(2)
declare @sql varchar(100)
set @todayDate = getDate()
set @Month = month(@todayDate)
set @day = day(@todayDate);
set @sql = 'create table test_' + @Month + '_' + @day
set @sql += '(no int,name char(10))'
exec(@sql);

--스파스열 : null값이 많이 들어가는 열의 저장공간을 낭비하지 않게 하기위해서 지정하는 열
--키워드 : sparse   5만건 기준으로 약 1/5수준으로 저장공간을 사용

create table aaa(
no int identity,
name char(100) null
);
go
create table sparseTbl(
no int identity,
name char(100) sparse null
);

declare @i int =0
while @i<10000
begin
insert aaa values(null);
insert aaa values(null);
insert aaa values(null);
insert aaa values(null);
insert aaa values(REPLICATE('s',100));
--sparse테이블
insert sparseTbl values(null);
insert sparseTbl values(null);
insert sparseTbl values(null);
insert sparseTbl values(null);
insert sparseTbl values(REPLICATE('s',100));
set @i += 1;
end


--임시테이블
--#테이블명 하나 사용 : 생성한 사용자만 사용가능 --쿼리차이 닫히면 사라짐.
--##테이블명 두개 사용 : 모든 사용자가 사용가능  --쿼리창이 닫히고 사용자가 없으면 사라짐.

create table #tempTbl1 
(
no int,name nchar(10)
);
create table ##tempTbl2(
no int,name nchar(10)
);

insert #tempTbl1 values(1,'테이블#1');
insert ##tempTbl2 values(1,'테이블#2');

select * from #tempTbl1
select * from ##tempTbl2
--뷰테이블
select * into copy_departmentnew from departmentnew --테이블 복사
select * from copy_departmentnew
select deptNo,DeptName,location from departmentNew
create view depart_View as 
select deptNo,DeptName,location from departmentNew 
where location ='서울'
select * from depart_View
exec sp_helptext depart_View; --뷰에 정의된 내용을 보수 있음.
Insert depart_View values(1100,'회계부','서울') --뷰를 통해서도 저장이됨.

--복잡한 쿼리문을 단순화해서 사용하기 위해서도 뷰를 사용
select deptNo,DeptName,location from departmentNew
create table emp(no int default(next value for auto_seq), name nchar(30),deptNo int);
insert emp(name,deptNo) values('김동길1','10');
insert emp(name,deptNo) values('김동길2','20');
insert emp(name,deptNo) values('김동길3','30');
insert emp(name,deptNo) values('김동길4','40');
insert emp(name,deptNo) values('김동길5','50');
insert emp(name,deptNo) values('김동길6','60');
insert emp(name,deptNo) values('김동길7','70');
insert emp(name,deptNo) values('김동길8','80');
insert emp(name,deptNo) values('김동길11','100');
insert emp(name,deptNo) values('김동길12','15');
insert emp(name,deptNo) values('김동길13','25');
insert emp(name,deptNo) values('김동길14','35');
insert emp(name,deptNo) values('김동길15','40');
insert emp(name,deptNo) values('김동길16','100');
insert emp(name,deptNo) values('김동길17','300');
insert emp(name,deptNo) values('김동길18','400');

select e.no,e.name,d.deptNo,d.deptName,d.location from emp e, departmentNew d where e.deptNo = d.deptNo

create view emp_dept_view as
select e.no,e.name,d.deptNo,d.deptName,d.location from emp e, departmentNew d 
where e.deptNo = d.deptNo and d.deptName = '영업부'

select * from emp_dept_view
exec sp_helptext emp_dept_view; --뷰에 정의된 내용을 보수 있음.
exec sp_help emp_dept_view;--뷰에 구조를 보여줌
create view emp_enc with encryption as
select e.no,e.name,d.deptNo,d.deptName,d.location from emp e, departmentNew d 
where e.deptNo = d.deptNo and d.deptName = '영업부'
exec sp_helptext emp_enc; --암호화 되어있어 뷰를 볼 수 없음.
exec sp_help emp_enc; --암호화되어있어도 구조는 확인가능
drop view emp_dept_view--뷰삭제
select * from sys.sql_modules --정의된 모든 뷰를 볼수 있음,테이블이름이 않나옴..
select object_name(object_id) as emp_dept_view,definition,* from sys.sql_modules --테이블명까지 나오나 암호화되어있은 뷰는 못 봄.

--뷰를 수정
alter view emp_dept_view as
select * from emp Where deptNo=40 with check Option; --40번에 해당하는 건만 수정,저장 가능.

--인덱스 : 데이터의 변경 작업이 자주 일어날 경우에는 성능이 나빠질 수 있다.
--Page,extent
/*
페이지 : 데이터를 저장하는 기본단위(8kb)
헤더,Data rows,Row Offset으로 구성

헤더:이전페이지와 가음페이지의 정보가 포함.
Data rows:실제 데이터가 저장되는 영역
Row Offset: 각행의 첫 번째 바이트가 페이지의 시작에서 얼마나 떨어져 있는지에 대한 정보를 포함하고 있다.

페이지 종류
.데이터 페이지 : 데이터를 저장하기 위한 페이지
.인덱스 페이지 : 인데스를 저장하는 페이지

익스텐트(extent)
:테이블이나 인덱스가 저장되는 기본단위, 8개의 페이지가 모여서 하나의 익스텐트를 이룬다.

단일 익스텐트 : 하나의 개체만 할당된 익스텐트
혼합 익스텐트 : 하나 이상의 개체의 페이지가 들어 있는 익스텐트

--인덱스의 구조
:인덱스는 기본적으로 정렬 되어있다.
인덱스는 B-tree구조로 구성되어 있다.
*/
--인덱스 유형

--clustered 인덱스(물리적으로 데이터를 정렬 시킴)
/*
:원하는 컬럼에 클러스터드 인덱스를 만들게 되면 기본적으로 데이터를 정렬을 시킨다.(오름차순으로 정렬)
.한테이블에 하나의 클러스터드 인덱스만 만들수있다.
.기본키를 만들게 되면 기본키에 클러스터드 인덱스가 만들어진다.
(기본키에는 넌클러스터드 인덱스를 만들 수 도 있다.)
.수정삭제 발생시 논클러스터드 보다 느리다.(데이터 재정렬 때문)
*/

--non clustered 인데스(논리적으로 데이터를 정렬 시킴)
/*
:원하는 컬럼에 넌클러스터드 인덱스를 만들게 되면, 데이터 페이지의 위치정보를 인덱스로 구성하게된다.
.한테이블에 만들 수 있는 넌 클러스터드 인덱스는 여러개가 가능
.기본적으로 클러스터드 인덱스 보다 검색 속도가 느리다.
*/
create table tbl01(aa int primary key, bb int, cc int);
--인덱스 정보확인 프로시져
exec sp_helpIndex tbl01

create table tbl02(aa int primary key, bb int unique, cc int unique,dd int);
exec sp_helpIndex tbl02 --aa는 클러스터드 인덱스,bb,cc는 넌 클러스터드 인덱스가 자동으로 생성

--기본키에도 넌 클러스터드 인덱스 설정 가능
create table tbl03(aa int primary key nonclustered,bb int unique,cc int unique,dd int);
exec sp_helpindex tbl03;

create table tbl04(aa int primary key nonclustered,bb int unique clustered,cc int unique,dd int)
exec sp_helpindex tbl04;

create table clusteredTbl(
id char(8) not null,
name NVARCHAR(10) not null);

insert clusteredTbl values('LL','김말똥');
insert clusteredTbl values('FF','홍길동');
insert clusteredTbl values('AA','김말똥2');
insert clusteredTbl values('BB','김말똥3');
insert clusteredTbl values('DD','김말똥4');
insert clusteredTbl values('HH','김말똥5');
insert clusteredTbl values('MM','김말똥6');
insert clusteredTbl values('OO','김말똥7');
insert clusteredTbl values('JJ','김말똥8');
insert clusteredTbl values('BB','김말똥9');
insert clusteredTbl values('II','김말똥10');
delete from clusteredTbl where name = '김말똥9'
select * from clusteredTbl

Alter table clusteredTbl
add constraint PK_clu_id primary key(id);
select * from clusteredTbl

create table noncluTbl(
id char(8) not null,
name nVarchar(8) not null
)

insert noncluTbl values('LL','김말똥');
insert noncluTbl values('FF','홍길동');
insert noncluTbl values('AA','김말똥2');
insert noncluTbl values('BB','김말똥3');
insert noncluTbl values('DD','김말똥4');
insert noncluTbl values('HH','김말똥5');
insert noncluTbl values('MM','김말똥6');
insert noncluTbl values('OO','김말똥7');
insert noncluTbl values('JJ','김말똥8');
insert noncluTbl values('KK','김말똥9');
insert noncluTbl values('II','김말똥10');
select * from noncluTbl
alter table noncluTbl
add constraint UK_non_id unique(id)
insert clusteredTbl values('gg','조관우')
insert clusteredTbl values('HA','김장비')
select * from clusteredTbl
insert noncluTbl values('gg','조관우')
insert noncluTbl values('HA','김장비')
select * from noncluTbl


create table ccc(
id char(8) not null,
name nvarchar(10) not null,
address nchar(10)
)

insert ccc values('LL','김말똥'  ,'서울 강서구1' );
insert ccc values('FF','홍길동'  ,'서울 강서구2' );
insert ccc values('AA','김말똥2'  ,'서울 강서구3');
insert ccc values('BB','김말똥3'  ,'서울 강서구4');
insert ccc values('DD','김말똥4'  ,'서울 강서구5');
insert ccc values('HH','김말똥5'  ,'서울 강서구6');
insert ccc values('MM','김말똥6'  ,'서울 강서구7');
insert ccc values('OO','김말똥7'  ,'서울 강서구8');
insert ccc values('JJ','김말똥8'  ,'서울 강서구9');
insert ccc values('KK','김말똥9'  ,'서울 강서구10');
insert ccc values('II','김말똥10' ,'서울 강서구11');
select * from ccc 
alter table ccc
add constraint PK_mix_id
primary key(id)

alter table ccc 
add constraint UK_mix_name
unique(name)
exec sp_helpindex ccc


select address from ccc where name = '김말똥'

--알아두어야할 용어
/*
Table scan : 데이터페이지를 처음부터 끝까지 검색하는 방식
인덱스가 없을 경우에 주로 사용하는 방식
Index seek : 넌클러스터드 인덱스에서 데이터를 찾는 방식

RID lookup : 넌클러스터드 인덱스에서 키를 검색 한 후, 실제 데이터 페이지를 찾는 다라는 의미

Clustered Index seek : 클러스터드 인덱스에서 데이터를 찾는 방식

Key lookup : 넌 클러스터드 인덱스에서 키를 검색한 후 클러스터드 인덱스에서 데이터를 찾는 방식

Clustered Index Scan : table scan방식과 비슷한 개념으로 전체를 찾아본다는 의미
 
*/

select * from ccc where id = 'aa'
select * from ccc

--인덱스를 직접 생성하는 방법
/*
create [unique][clustered | nonclustered] index 인덱스명 
on 테이블명(column [asc | desc]) 

PAD_INDEX : 기본값은 OFF,ON으로 설정되면 FILLFACTOR에 페이지 여유분을 설정할 수 있다.
FILLFACTOR : 페이지의 여유분을 설정한다.

예>with fillfactor 80 ->페이지에 20%의 여유분을 두겠다는 의미

    .sort_in_tempDB : 기본값은 OFF,ON설정하면 디스크의 분리효과가 생긴다.
.online : 기본값은 OFF,ON 설정시 인덱스 생성중에서 기본 테이블에 쿼리가 가능하도록 하는 옵션


*/
--인덱스를 변경하는 방법
/*
alter index [인덱스 명 | all] on 테이블명 rebuild 옵션:인덱스를 삭제하고 다시 생성하는 옵션
예> alter index all on ccc
rebuild
with (online = on)

reorganize : 인덱스를 다시 구성하는 옵션(삭제하지 않고, 즉 조각모음)
*/

--인덱스 삭제
/*
delete index 테이블명.인덱스명
.primary key, unique 제약조건으로 자동생성된 인덱스는 drop index로 제거 할 수 없다.
이경우에는 alter table 구문으로 제약조건을 삭제하면 인덱스가 삭제된다.
.시스템 테이블의 인덱스는 drop index구문으로 삭제 할 수없다.
.혼합형 테이블에서 인덱스를 제거할 경우에는 넌클러스터드 인덱스를 제거하는 것이 좋다.
*/
select * from ccc

exec sp_helpindex ccc;

alter table ccc
drop UK_mix_name;--인덱스 삭제

create index idx_name on ccc(name)
exec sp_helpindex ccc;

create unique index idx_name on ccc(name); --네임에 유니크를 추가

--트랜잭션 : 하나의 논리적인 작업단위로 여러명령의 묶음.
--여러명령의 묶음 : insert/update/delete 명령들의 묶음이라도 봐도 된다.

--예
begin transaction --자동으로 sql server에서 붙인다.
update ccc set address='경기도 시흥' where id = 'aaa';
commit transaction --자동으로 sql server에서 붙인다.

--commit : 데이터베이스에 반영을 하겠다는 명령 


--예
begin transaction --사용자가 명시
update ccc set address='경기도 시흥1' where id = 'aaa';
update ccc set address='경기도 시흥2' where id = 'bbb';
update ccc set address='경기도 시흥3' where id = 'ddd';
commit transaction --사용자가 명시
--commit : 데이터베이스에 반영을 하겠다는 명령

create table cccc(
number int 
);

insert cccc values(10);
insert cccc values(20);
insert cccc values(30);

begin tran 
Update cccc set number =10 where number=100;
Update cccc set number =20 where number=200;
Update cccc set number =30 where number=300;
commit tran

select * from cccc

--현재 실행 중인 트랜잭션의 갯수를 알아보기위한 함수
--시스템 함수 @@trancount
select @@TRANCOUNT

--다른 쿼리창에서 select * from cccc 실행해도 트랜잭션이 진행중일 경우 않보임
--트랜잭션 중일 경우에는 해당 테이블에 Lock을 발생시킴.
--따라서 다른 사용자가 접근 할 수 없게 된다.
--ctrl + alt + del키를 누른다.
--만약 LOCK을 해제하면 볼수 있는데 해제하는 방법은 
--alter database sampleDB set allow_snapshot_isolation on;
--set transaction isolation level snapshot;
--원상태는
--alter database sampleDB set allow_snapshot_isolation off;

--구문형식
/*
begin tran
SQL 명령...
Commit tran(=commit work)

.commit tran : 트랜잭션의 이름을 지정해서 commit을 시킬 수 있다.
.commit work : 현재 실행중인 트랜잭션 중에서 가장 가까운 트랜잭션만을 커밋 시킨다.
.rollback tran: 트랜잭션을 취소
트랜잭션의 내용이 길 경우 중간에 위치를 지정해서
그 지점까지의 저장 할 수 있다.
.save tran 저장지점
*/

--트랜잭션의 종류
/*
자동 커밋 트랜재션 : 자동적으로 begin tran 과 commit tran을 명시하는 방식
SQL Server에서는 자동 커밋을 디폴트로 사용한다.
명시적 트랜잭션 : 사용자가 직접 begin tran과 commit tran,rollback tran을 명시하는 방식.
묵시적(암시적(트랜잭션):쿼리문을 실행했을 때 자동으로 begin tran을 명시하지만, commit tran,rollback tran은
자동으로 명시하지 않는 방식(사용자가 직접 commit tran을 결정하는 방식)

설정방법 : set implict transaction on 

alter table, fetch, revoke, create, grant, select, delete, insert,update,truncate table,drop,open
암시적 트랜잭션이 한번이 수행되면,commit이나 rollback명령이 수행되기 전까지는 암시적트랜잭션이 중복되어 시작되지 않는다.
*/

create table tran_ex2(
name varchar(10),
money int,

constraint CK_money check(money >= 0)
);

INSERT tran_ex2 values('유비',1000);
INSERT tran_ex2 values('장비',2000);

select * from tran_ex2

--유비가 장비에게 500원을 송금하는 예
begin
update tran_ex2 set money=money-500 where name='유비';
commit tran

begin
update tran_ex2 set money=money + 500 where name='장비';
commit tran
--유비가 장비에게 800원을 송급처리
begin
update tran_ex2 set money = money - 800 where name='유비';
update tran_ex2 set money = money + 800 where name='장비';
commit tran

--check 조건:money >=0 ->논리적인 오류 rollback이 실행되지 않는다.
--이러한 논리적인 오류가 발생했을 경우에는 
--예외 처리를 한다. begin try ~end try begin catch ~ end catch를 이용한다.
begin try 
begin tran
update tran_ex2 set money = money - 500 where name='유비';
update tran_ex2 set money = money + 500 where name='장비';
commit tran
print '송금처리가 되었습니다.'
end try
begin catch
rollback tran
print '잔액이 부족합니다.'
end catch
-------이상 5강------------
--단순한 레코드 증가용 테이블
create table tran_ex3(
no int identity 
);
--트랜잭션의 갯수를 저장하는 테이블
create table tran_ex4(
no int,
tranCnt int
);


--트리거 만들기
CREATE TRIGGER trancnt
on tran_ex3
for insert
as
declare @no int;
select @no = no from inserted;
insert tran_ex4 values(@no,@@TranCount);

insert tran_ex3 default values;

SELECT * from tran_ex3
SELECT * from tran_ex4

begin tran
begin tran
print '트랜잭션의수:' + cast(@@tranCount as char(3));
commit tran
print '트랜잭션의 수:' + cast(@@trancount as nchar(3));
commit tran

print '트랜잭션의 수:' + cast(@@trancount as nchar(3));



CREATE TABLE TRAN_ROLLback_EX1(
NO INT
);
INSERT TRAN_ROLLBACK_EX1 VALUES(10);

BEGIN TRAN --모든 TRAN 롤백
UPDATE TRAN_ROLLBACK_EX1 SET NO = 100;
BEGIN TRAN
UPDATE TRAN_ROLLBACK_EX1 SET NO =200;
SELECT * FROM TRAN_ROLLBACK_EX1;
ROLLBACK TRAN
SELECT * FROM TRAN_ROLLBACK_EX1;
ROLLBACK TRAN
SELECT * FROM TRAN_ROLLbACK_EX1;

--SAVA TRAN 문응용 : TRAN별로 롤백
BEGIN TRAN
UPDATE TRAN_ROLLback_EX1 SET NO =100;
SAVE TRAN [POS1]
BEGIN TRAN
UPDATE TRAN_ROLLBACK_EX1 SET NO = 200;
SELECT * FROM TRAN_ROLLback_EX1;
ROLLBACK TRAN[POS1]
SELECT * FROM TRAN_ROLLback_EX1;
ROLLBACK TRAN
SELECT * FROM TRAN_ROLLback_EX1
--이상6강

SET  IMPLICIT_transactions on;

CREATE table Tran_tbl( --암시적트랜잭션이 시작되는 지점
no int
);
go

insert Tran_tbl values(10);
insert Tran_tbl values(20);

select * from Tran_tbl --create부터 같이 실행시 다른쿼리창에서 select문이 실행이 않됨.
rollback tran

SELECT @@TRANCOUNT;
SELECT * FROM Tran_tbl;

begin tran
INSERT Tran_tbl values(10)
select @@TRANCOUNT;
commit tran;
begin tran
INSERT Tran_tbl values(20)
select @@TRANCOUNT;
rollback tran; --위의 tran까지 취소

SELECT @@TRANCOUNT;

set implicit_transactions off;

--이상 7강
/*
CREATE PROCEDURE 프로시져이름
as
SQL ..

프로시져 실행
exe 프로시져명

프로시져 수정/삭제
alter procedure /drop procedure 

매개변수 : 프로시저를 생성할때 매개변수를 지정한다.

입력매개변수 지정
@입력 매개 변수이름 데이터 형식

입력매개변수가 있는 프로시저 실행
execute 프로시져명 [매개변수에 전달 될 값]

출력매개변수 지정
@출력변수이름 데이터형식 output

출력매개변수가 있는 프로시저를 실행
execute 프로시저명 @변수명 output
*/


SELECT * FROM seqTest

--프로시저 생성
create procedure pr_1
@empName nvarchar(10)

as
SELECT * FROM seqTest ;

--프로시저 수정
alter procedure pr_1
@empName nvarchar(10)

as
SELECT * FROM seqTest WHERE name like @empName;

exec pr_1 ''

create procedure pr_4
@text nchar(10),
@outNum int Output
as
insert pr_Tbl values(@text);
select @outNum = IDENT_CURRENT('pr_tbl'); --pr_Tbl의 현재 identity 값

create table pr_tbl(
no int identity,
text char(10)
);

declare @n int;

exec pr_4 'number 1', @n output

SELECT * from pr_Tbl

--이상 8강