ms-SQL 열 한 번째 수업

사원의 이름을 매개변수로 입력하면
해당사원에 대해
 - 이름, 생일, 나이, 입사일, 입사한 일 수, 입사한 지 10000일 째 되는 날
  을 보임
 - 이 사원이 주문받은 주문 횟수
 
alter proc proc_ldh
 @이름 varchar (50) -- 매개변수
 as
begin
 select 이름, 생일, datediff(yy, 생일, getdate()) 나이,
 입사일, datediff(dd, 입사일, getdate()) 입사일수,
 dateadd(dd, 10000, 입사일) '10000일째 되는 날'
 from 사원
 where 이름 = @이름
 
 select datename(qq, 주문일), count(*)
 from 사원, 주문
 where
 사원.사원번호 = 주문.사원번호 and
 이름 = @이름
 group by datename(qq, 주문일)
 order by 1 asc
end

proc_ldh 박에녹

제품명의 일부를 입력하면
 - 제품명, 단가, 재고, 재고금액
 - 검색한 결과로 나오는 레코드가 5건 이상이면
  5건 이상 검색이 되었습니다.
  
alter proc proc_ldh
 @제품명 as varchar(100)
 as
begin
 declare @제품수 int
 declare @정확한이름 varchar(100)
 
 select @정확한이름 = 제품명
 from 제품
 where 제품명 like '%'+@제품명+'%'
 
 select 제품명, 단가, 재고, sum(단가*재고) 재고금액
 from 제품
 where 제품명 like '%'+@제품명+'%'
 group by 제품명, 단가, 재고
 
 set @제품수 = @@rowcount
 if @제품수 >=2
  print  @제품명 + ' 키워드를 가진 제품, ' + ltrim(str(@제품수))+ '건 이상이 검색 되었습니다.'
 else if @제품수 =1
  print @정확한이름 + '제품이 정확히 입력되었습니다.'
 else
  print '결과가 없습니다.'
 
end

proc_ldh '유미'

4가지 조인

 

---

 

트리거 : 자동 실행 프로그램
 a테이블에 이벤트 insert, update, delete 가 발생하면
 b테이블에 자동으로 행위가 유발

create trigger 트리거이름
 on user 가 사용하는 테이블 명
 for insert_update_delete
as
 begin
 
 end
 

회원 테이블 생성
 -회원 레코드를 삽입할 때 마다 삽입한 레코드를 백업이라는
  테이블에 자동으로 삽입
  

 create table 회원(
  아이디 char(5) primary key,
  이름 varchar(10),
  마일리지 int default 1000,
  등록일 datetime default getdate())
  
 create table 백업(
  아이디 char(5),
  이름 varchar(10),
  --마일리지 int,
  등록일 datetime)
  
회원 레코드를 삽입할 때 마다 삽입한 레코드를 백업이라는 테이블에 자동으로 삽입

create trigger 트리거_백업
 on 회원
 for insert
 as begin
  declare @아이디 varchar(5)
  declare @이름 varchar(10)
  declare @등록일 datetime
  
  select @아이디=아이디, @이름=이름, @등록일=등록일
   from inserted
  
  insert into 백업(아이디, 이름, 등록일)
   values (@아이디, @이름, @등록일)
 end
 
drop trigger 트리거_백업
  
확인 : 회원테이블에 insert해봄
insert into 회원(아이디, 이름) values('apple', '김사과')
insert into 회원(아이디, 이름) values('banan', '김바나나')

drop table 회원
drop table 백업

select * from 회원
select * from 백업

- 게시판에 글을 쓸 때 마다 회원의 마일리지에 100점 씩 자동으로 누적
 이 되는 트리거를 작성
 
create table 게시판(
 번호 int identity,
 제목 varchar(20),
 내용 varchar(50),
 작성일 datetime default getdate(),
 아이디 varchar(5))
 
create trigger 트리거_마일리지_누적
 on 게시판
 for insert
 as begin
  declare @아이디 varchar(5)
  select @아이디 = 아이디 from inserted
  
  update 회원 set 마일리지=마일리지+100 where 아이디=@아이디
end
 
실행여부? 게시판에 글을 써서 확인

insert into 게시판(제목, 내용, 아이디)
values('sql', 'sql트리거 연습', 'apple')

확인 :
select * from 회원

select * from 게시판

게시판에 글을 삭제하면 삭제 날짜와 번호, 작성자를 로그테이블에
자동저장하는 트리거를 작성

create table 로그(
 번호 int identity,
 삭제일 datetime default getdate(),
 아이디 varchar(5),
 제목 varchar(20))
 
create trigger 트리거_삭제_기록
 on 게시판
 for delete
 as begin
  declare @아이디 varchar(5)
  declare @제목 varchar(20)
  
  select @아이디 = 아이디, @제목 = 제목 from deleted
  
  insert into 로그(아이디, 제목)
  values(@아이디, @제목)
end

select * from 게시판
select * from 로그

delete from 게시판 where 아이디 = 'apple'
delete from 로그 where 번호 = 2

 

 

---

회원테이블에서 이름을 변경할 때 마다 변경전 이름과 변경 후 이름을
저장하는 트리거를 생성하세요

create table 변경내역(
 번호 int identity,
 변경일 datetime default getdate(),
 변경전 varchar(10),
 변경후 varchar(10),
 아이디 varchar(5))

alter trigger 트리거_변경_기록
 on 회원
 for update
 as begin
  declare @아이디 varchar(5)
  declare @변경전 varchar(10)
  declare @변경후 varchar(10)
  
  select @변경전 = 이름
  from deleted
  
  select @아이디 = 아이디, @변경후 = 이름
  from inserted
  
  insert into 변경내역(변경전, 변경후, 아이디)
  values(@변경전, @변경후, @아이디)
end

select * from 회원

select * from 변경내역

update 회원 set 이름='김**'
where 아이디='apple'

 

 

이퀴 비이퀴조인 셀프조인 외부조인

뷰 함수 프로시저 트리거