ms-SQL 여덟 번째 수업 - Stored Procedure 시작

-- 뷰 : 가상 테이블
  sql문에 이름을 붙여 놓은 것

create view 뷰이름
 as select .....

오라클
create or replace view 뷰이름
 as select ..

뷰를 만드는 목적

1) 단순성

2) 보안

1) 사원의 이름, 집 전화, 상사의 이름, 상사의 집 전화를 보이는 뷰를 작성

create view view_ldh as
select s1.이름 사원이름, s1.집전화 사원전화, s2.이름 상사이름, s2.집전화 상사전화
from 사원 s1, 사원 s2
where s1.상사번호=s2.사원번호

-뷰의 소스를 확인
sp_helptext view_ldh

select * from view_ldh

2) 뷰를 수정
 제품명별로 주문수량합을 보이세요. 이때 주문이 한번도 일어난 적이 없는 제품의
 이름도 보이도록 뷰를 수정
 
t-sql

select 제품명, sum(주문수량)
from 주문세부, 제품
where 주문세부.제품번호 = 제품.제품번호
group by 제품명
order by 2 desc

ansi-sql
-- 외부조인은 주문이 없는 제품의 정보도 보여준다.
select 제품명, sum(주문수량)
from 제품 left outer join 주문세부
on 제품.제품번호 = 주문세부.제품번호
group by 제품명
order by 2 desc
 
alter view view_ldh as
select 제품명, sum(주문수량) 주문수량합
from 제품 left outer join 주문세부
on 제품.제품번호 = 주문세부.제품번호
group by 제품명

select * from 주문

select * from view_ldh
where 주문수량합 is null
 
3) 마일리지 등급이 C인 고객의 담당자명, 고객회사명, 마일리지를 보이는 뷰를 수정하세요

alter view view_ldh as
select 담당자명, 고객회사명, 마일리지
from 고객, 마일리지등급
where 마일리지 between 하한값 and 상한값
and 등급명='C'

select * from view_ldh

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

고객
고객번호, 담당자명, 전화번호, 도시+주소, 우편번호

-- 뷰를 통해서 데이터 넣는것은 가능하다

alter view view_ldh as
select 고객번호, 담당자명, 전화번호, 도시+' '+주소 주소, 우편번호
from 고객

-- 변형된 테이블(도시+주소)에는 데이터를 넣을 수 없다.

insert into view_ldh(고객번호, 담당자명, 주소)
values('99999', '홍길동', '중랑구 면목동') --x

alter view view_baeey
as
select 고객번호, 담당자명, 전화번호, 주소, 우편번호
from 고객

insert into view_ldh
values('777777', '홍길동', '555-4444', '중랑구 면목동', '111-222') --x

alter view view_ldh
as
select 고객번호, 담당자명, 전화번호, 주소, 우편번호, 마일리지
from 고객

insert into view_ldh
values('91123', '홍길동', '555-4444', '중랑구 면목동', '111-222', '100') --x

뷰를 수정 : 부서명별로 사원수를 보이는 뷰로 수정

alter view view_ldh as
select 부서명, count(*) 사원수
from 사원, 부서
where 사원.부서번호=부서.부서
group by 부서명

위의 뷰를 통해서 레코드를 삽입할 수 있을까?
select * from view_ldh

alter view view_ldh as
select 사원번호, 이름, 집전화, 부서번호
from 사원
where 부서번호 = 'A'

뷰를 수정
사원번호, 이름, 집전화, 부서번호를 보이는 뷰를 작성
이때  A부서만 보이도록

위 뷰를 통해 B부서 사원 한명을 추가하세요

insert into view_ldh(사원번호, 이름, 부서번호)
values('123456', '임대현', 'B')

select * from view_ldh

with check option : 뷰를 만들 때 where 조건에 위배되는 정보는
뷰를 통해서 삽입이나 수정을 할 수 없도록 지정

alter view view_ldh as
select 사원번호, 이름, 집전화, 부서번호
from 사원
where 부서번호 = 'A'
with check option

insert into view_ldh(사원번호, 이름, 부서번호)
values(456, '박태환', 'A')

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

프로시저 - pre-defined 프로시저
   user-defined 프로시저
   
한석규 고객(담당자명)이 주문한 주문일자를 보이세요

select 주문일
from 고객, 주문
where 고객.고객번호=주문.고객번호
and 고객.담당자명 = '한석규'

create proc proc_ldh
as
begin
 select * from 고객
 select * from 사원
end

execute proc_ldh
exec proc_ldh
proc_ldh

입력받은 담당자의 기본정보와 주문일을 보이고 , 제품명과 주문수량합

alter proc proc_ldh
 @담당자명 varchar(20)
as
begin
 select * from 고객 where 담당자명=@담당자명
 
 select 주문.주문일
 from 고객, 주문
 where 고객.고객번호=주문.고객번호
 and 고객.담당자명 = @담당자명
 
 select 제품명, sum(주문수량)
 from 고객, 주문, 주문세부, 제품
 where 고객.고객번호=주문.고객번호
 and 주문.주문번호=주문세부.주문번호
 and 제품.제품번호=주문세부.제품번호
 and 담당자명=@담당자명
 group by 제품명
end

select * from 고객
proc_ldh '한석규'

제품명을 입력하면 제품에 대한 정보, 제품명별로 주문수량합, 주문금액합을 보이는 프로시저를 작성(수정)

alter proc proc_ldh
 @제품명 varchar(100)
as
begin
 select * from 제품
 where 제품명 like '%'+@제품명+'%'
   
 select 제품명, sum(주문수량) 수량합, sum(주문세부.단가*주문수량) 금액합
 from 주문세부, 제품
 where 제품.제품번호=주문세부.제품번호
 and 제품명 like '%'+@제품명+'%'
 group by 제품명
end

select * from 제품
proc_ldh '아이스'