ms-SQL 열 두 번째 수업

프로시저

select * from 주문
select * from 주문세부

1. 주문번호, 고객번호, 사원번호, 주문일을 매개변수로 받아서
주문테이블에 삽입이 되는 프로시저를 작성

select * from 주문세부

alter proc ldh_proc
 @주문번호 as char(5),
 @고객번호 as char(5),
 @사원번호 as int,
 @주문일 as datetime
as begin
 declare @결과 int
 
 select @결과=count(*) from 주문 where 주문번호=@주문번호
 
 if @결과 = 0
  insert into 주문(주문번호, 고객번호, 사원번호, 주문일) values(@주문번호, @고객번호, @사원번호, @주문일)
  
  select * from 주문 where 주문번호=@주문번호
 else
  print str(@주문번호) + '가 이미 존재합니다'
end

drop proc ldh_proc

 

2. 주문테이블에 주문이 될 때마다 고객의 마일리지를 10점씩 누적시키는 트리거를 작성

create trigger 마일리지누적_ldh
 on 주문
 for insert
as begin
 declare @고객번호 varchar(5)
 
 select @고객번호=고객번호 from inserted
 update 고객 set 마일리지 = 마일리지+10
  where 고객번호=@고객번호
end

ldh_proc 84294, 'ALFKI', 1, '2014-06-09'

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

프로시저 : do actions
함수 : do actions and return a value

 함수
  built-in 함수
   - 문자열, 숫자형, 날짜형, 시스템
   
  user-defined 함수
  
함수 만드는 문법
 create function 함수명(@매개변수...)
  returns 반환타입
  as
  begin
   .....
   return 반환값
  end
  
나이를 계산해주는 함수
 나이ldh
 
create function 나이ldh(@생일 datetime)
 returns int
as
begin
 return datediff(yy, @생일, getdate())
end
 
 
select year(getdate())
select 이름, 생일, dbo.나이ldh(생일), year(생일) from 사원

select * from 사원

1. 생일을 입력하면 나이대를 구분해주는 함수를 작성하세요
 (23) 20대 초반

alter function 나이ldh(@생일 datetime)
 returns varchar(200)
as
begin
    declare @결과 varchar(200)
    
    if((datediff(yy, @생일, getdate()) % 10) between 0 and 2)
        set @결과 = str(datediff(yy, @생일, getdate())/10*10)+'대 초반'
    else if((datediff(yy, @생일, getdate()) % 10) between 3 and 7)
        set @결과 = str(datediff(yy, @생일, getdate())/10*10)+'대 중반'
    else if((datediff(yy, @생일, getdate()) % 10) between 8 and 9)
        set @결과 = str(datediff(yy, @생일, getdate())/10*10)+'대 후반'
    else
        set @결과 = ''
 
 return @결과
end

select dbo.나이ldh(생일) from 사원
 
2. 이름과 직업을 입력받으면 xx의 직업 xx입니다 라는 결과로
 보이는 함수를 작성

create function 설명ldh(@값1 varchar(100), @값2 varchar(100))
 returns varchar(200)
as
begin
 return @값1 + '의 직업은 ' + @값2+'입니다'
end
 
3. 숫자를 입력하면 원둘레를 구하는 함수를 작성하세요

create function 원둘레ldh(@원둘레 float)
 returns float
as
begin
 return 2*PI()*@원둘레
end

select * from 주문세부
두 수의 곱이라는 함수
재고 * 단가 = 재고금액
주문수량 * 단가 = 주문금액
가로 * 세로 => 사각형 넓이

create function 곱ldh(@값1 float, @값2 float)
 returns float
as
begin
 return @값1 * @값2
end

select *, dbo.곱ldh(단가, 재고수량) 주문금액 from 주문세부
select *, dbo.곱ldh(재고, 단가) 재고금액 from 제품

소스 확인:
sp_helptext 곱ldh

sp_helptext sp_help

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

조인 - 4가지
 이퀴조인, 비이퀴조인, 셀프조인, 외부조인
 
뷰, 이너뷰(from (select....)
프로시저, 함수, 트리거 작성
함수 사용
인덱스

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

인덱스 : 검색성능을 높이기 위해서 만듦

도서, 대여, 회원

create index 인덱스명 on 테이블명(필드명)