[ 데이터베이스 ] 10. Stored procedure

Stored procedure

: 사용자가 정의한 프로시저

 

구체적인 하나의 task를 수행한다.

 

 

예제1) 두 정수의 곱셈 결과를 가져오는 프로시저 작성하기

 

▶ delimiter $$

▶ Create procedure product(In a int, In b int, OUT result int) 

                                         // Input parameter랑 Output parameter 를 명시해야한다. (In은 생략 가능)

▶ Begin

▶       Set result = a*b;

▶ END

▶ $$

 

▶ delimiter ;

 

(사용법)

▶ call product( 5 , 7 , @result);

▶ select @result;

 

 

---

 

예제2) 두 정수의 맞바꾸는 프로시저 작성하기

 

▶ delimiter $$

▶ Create procedure change( INOUT a int , INOUT b int )    // INOUT = 전달 받으면서도 return도 가능

▶ Begin

▶        declare tmp;

▶        Set tmp = a;

▶        set a = b;

▶        set b = tmp;

▶ End

▶ $$

 

▶delimiter ;

 

▶ set @a =5 , @b=7;

▶ call change (@a, @b);

▶ select @a, @b;

 

 

---

예제 3) 각 부서별 평균 연봉을 가져오는 프로시저 작성하기

 

▶ delimiter $$

▶ Create procedure get_dept_avg_salary()

▶ Begin

▶        select dept_id, avg(salary)

▶        from employee

▶        group by dept_id;

▶End

▶$$

 

▶delimiter ; 

 

 

---

예제 4) 프로필 닉네임을 바꾸면 , 이전 닉네임을 로그에 저장 / 새 닉네임으로 업데이트 하는 프로시저 작성하기

 

이렇게 만들어야함

 

▶ delimiter $$

▶ Create procedure change_nickname(user_id INT , new_nick varchar(30))

▶ Begin

▶          Insert into nickname_logs(                

▶                                 select id, nickname, now() from users where id=user_id

▶             );

▶           Update users set nickname = new_nick where id= user_id;

▶End

▶$$

 

▶ delimiter ;

 

▶ call change_nickname(1,강아지);

 

(확인하는 건)

▶select * from users;

▶select * from nickname_logs;

 

 

---

 

Stored Procedure   VS   Stored Function

 

	Stored Procedure	Stored Function
Create 문법	CREATE PROCEDURE ...	CREATE FUNCTION ...
'Return' 키워드로 반환	불가능 (Output parameter로 반환)	가능
Parameter를 반환	가능	가능하긴 한데...
값을 꼭 return해야 함?	ㄴㄴ	ㅇㅇ
SQL statement에서 호출	불가능	가능
Transaction 사용	가능	대부분 불가능
주된 사용 목적	Business logic	Computation

 

 

 

SQL statement에서 호출 

product function을 저렇게 부르는 건 가능

하지만 procedure는 안 된다

 

 

 

Transaction : 여러 SQL문을 하나로 묶어서 실행,

                     그 SQL문에서 문제가 생기면 그냥 아무 일도 안 일어나고, 문제가 없으면 다 실행됨

                     ALL or Nothing 

 

 

(출처)

유튜브 쉬운코드