it 수업 7주차 (25) Procedure

 

----------------------------
 Stored Procedure
-----------------------------

-- 데이터베이스에서 실행 가능한 저장 프로그램
-- SQL문들의 논리적인 묶음
-- Function(함수)와 유사하나 특정부분에서의 차이점이 존재한다.

   (함수 : 수를 받아서 처리하는 논리적 공간)

 

----------------------------------------------------------------
Stored Procedure와 Function 과의 공통점
----------------------------------------------------------------

01 재사용성
02 모듈화
03 매개변수의 존재
04 흐름제어 처리(if, case, while 사용가능)
05 트랜잭션 처리

06 커서 사용
07 변환값 존재
08 동적 SQL문 실행가능(prepare - execute)

 

 

-----------------------------------------------------------------
Stored Procedure 와 Function 과의 차이점
----------------------------------------------------------------- 

-- 반환값
     프로시저에서는 반환값이 필수는 아니다
     함수에서는 항상 값을 반환한다.

-- 허용되는 문맥
     프로시저는 SELECT, INSERT, UPDATE, DELETE문과 같은 SQL문 내에서 직접호출 가능
     함수는 주로 SELECT 문이나 WHERE절에서 호출되어 사용, SQL문에서 직접 호출되는 경우가 적음
    
-- 트랜잭션
     프로시저 : 트랙잭션 내에서 여러 SQL문을 실행 할 수 있다.
     함수 : 주로 읽기 전용 작업에 사용되며, 트랜잭션에서 변경사항을 가지지 않도록 설계
    
-- 예외처리
     프로시저 : 프로시저 내에서 예외처리
     함수 : 예외처리가 가능하지만 주로 SELECT문을 사용하기 때문에(조회) 예외처리를 적용하는 경우가 적음

 

 

 

 

 

 

- 프로시저 상태보기

 

show procedure status;

 

 

- 원하는 테이블만 보기

show procedure status where db='shopdb';

 

 

01 기본 프로시저

데이터를 외부에서 받을 일 없으니까 pro1()에 괄호 안에 아무것도 안넣어도 된다

 

 

pro1의 설정에 들어가면 밑의 사진처럼 타이핑했던 sql문을 볼 수 있다.

 

 

 

 

02 프로시저 (Argument - Parameter)

 

파라미터(매개변수) : 소프트웨어나 시스템상의 작동에 영향을 미치며, 외부로부터 투입되는 데이터

여기서는 외부에서 데이터를 받아와야하니까 pro2()의 괄호 안에 데이터 타입을 선언해준다.

외부에서 데이터를 받아왔으니까 call 할때도 원하는 값을 넣어줘야 한다.

 

call pro2(105);

 

call pro2(100);

 

 

 

 

03 프로시저 (테이블에 적용)

 

call pro3(4);

 

 

 

04 프로시저 테이블에적용(if)

 

 

call pro4(4,0);

amount 값이 4보다 작거나 같은 값들이 나오게 된다.

 

call pro4(4,1);

amount 값이 4보다 크거나 같은 값들이 나오게 된다.

 

 

 

 

 

05 프로시저 평균 구하기

 

 

 

< 연습 문제 >

1.

 

delimiter $$
create procedure older(in param int)
begin
select * from usertbl where birthyear < param;
end $$
delimiter ; 

call older(1970);

 

 

 

2.

 

delimiter $$
create procedure exam2()
begin
select *, ceil(datediff(curdate(),mDate)) as '가입일로부터 N일' from usertbl;
end $$
delimiter ; 

call exam2();

 

(cile()함수는 부동 소수점을 반올림 해주는 함수이다.)

 

 

3.

 

select * from usertbl;
delimiter $$
create procedure fast(in param int)
begin
select * from usertbl where year(mdate) < param;
end $$
delimiter ; 

call fast(2010);

 

 

 

 

 

06 인자 2개 사용

 

call pro6(170, 180);

170cm와 180cm사이의 값 구하기

 

 

 

 

 

07 인자 3개 사용

 

인자 3개를 이용해 위의 값이 나오도록 프로시저를 만들어 볼 것이다.

 

 

 

call pro7(10,5,1); (여기 값은 바꿔 넣을 수 있다.)

원하는 값들을 넣어 회원등급을 나눠본다.

 

 

 

 

 

08 프로시저 + 반복문

 

HELLOWOLRD가 10개 출력되는 것이 아니라 출력 페이지가 10개 나온다.

 

 

 

 

 

08_2 외부에서 인자받기

 

밑의 결과 페이지가 3개 나오는 것을 볼 수 있다.

 

 

 

 

 

 

09_01 1~10의 합 구하기

 

 

 

 

09_02 1~n 합 구하기

 

 

 

 

 

09_03 n~m 합 구하기 (n < m)

 

n이 m보다 크면 안되므로 set 하기 전에 먼저 if문으로 임시공간을 만든 뒤 스왑을 시켜주는 문장을 만들어 준다.

 

 

 

 

09_04 2단 구구단 만들기

 

 

 

 

 

 

 

 

09_05 n단 구구단 만들기

 

 

 

 

 

 

 

 

 

● 반환값 지정 (OUTPUT)

 

 

 

 

하지만 height 값이 중복이 있으면 리턴 값이 2개 이상이 돼 에러가 뜨게 된다. 

height 값 중복