[MYSQL / Database] 프로시저 (Procedure)

728x90

프로시저(PROCEDURE)

- 쿼리문의 집합
- 프로그래밍 할 때 우리가 만드는 함수와 거의 비슷하다.
- 반환값이 없는 함수

변수 사용

- 무조건 변수명 앞에 @를 붙여줘야 함 <예> set @my = 1; 또는 set @my := 1
- 프로시저, 사용자 정의 함수 만들때 변수 선언 많이 사용함

프로시저 특징

- 보안을 강화할 수 있다.(뷰와 같음)
- 유지보수가 편하다.
- 프로시저 호출시 처음 1회 컴파일 이후 메모리에 로딩되어서 성능 향상이 있다.
- 긴 수십줄의 쿼리 문자열을 생성할 필요없이 프로시저 이름만 호출

프로시저 생성 /제거

<기본 설정>

DROP DATABASE IF EXISTS sqldb; -- 만약 sqldb가 존재하면 우선 삭제한다.
CREATE DATABASE sqldb;

USE sqldb;
CREATE TABLE usertbl -- 회원 테이블
( userID  	CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  name    	VARCHAR(10) NOT NULL, -- 이름
  birthYear   INT NOT NULL,  -- 출생년도
  addr	  	CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  mobile1	CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등)
  mobile2	CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 키
  mDate    	DATE  -- 회원 가입일
);
CREATE TABLE buytbl -- 회원 구매 테이블(Buy Table의 약자)
(  num 		INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
   userID  	CHAR(8) NOT NULL, -- 아이디(FK)
   prodName 	CHAR(6) NOT NULL, --  물품명
   groupName 	CHAR(4)  , -- 분류
   price     	INT  NOT NULL, -- 단가
   amount    	SMALLINT  NOT NULL, -- 수량
   FOREIGN KEY (userID) REFERENCES usertbl(userID)
);

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL  , NULL      , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL  , NULL      , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,  1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,  5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,  10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'    , '서적', 15,   5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);

SELECT * FROM usertbl;
SELECT * FROM buytbl;

drop procedure if exists [프로시저이름] ; -- 기본 프로지저 있으면 제거(주석없이 실행하기)
delimiter $$ -- 종료문자 $$로 변경
create procedure [프로시저 이름]() -- 프로시저 생성(주석없이 실행하기)
begin
select * from usertbl where height > 170;
end $$ delimiter ; -- 종료 문자 원래대로 복구(주석없이 실행하기)
call select_proc(); -- 프로시저 실행(주석없이 실행하기)

drop procedure if exists select_proc;

delimiter $$

create procedure select_proc()
begin
	select * from sqldb.usertbl where height > 170;
end $$
delimiter ;


call select_proc();

in : 매개변수는 참조만 가능하면 값을 할당할 수 없다.

out : 매개변수에 값을 전달할 수 있지만 의미는 없다.

out,in : out매개변수에는 디폴트 값을 설정할 수 없다.

in 매개변수에는 변수나 상수 각 데이터 유형에 따른 값을 전달할 수 있지만, out , in out 매개변수를 전달할 때는 반드시 변수 형태로 값을 넘겨줘야한다.

in 매개변수 사용하여 호출하기

drop procedure if exists select_proc;

delimiter $$
create procedure select_proc(in param_userid varchar(20))
begin
	select * from usertbl where userid = param_userid;
end $$
delimiter ;

call select_proc('BBK');
call select_proc('SSK');

param_userid

=> select_proc()의 괄호 안의 값을 받으면 usertbl의 userid에서 가져온다는 의미

out 매개변수 사용

프로시저에서 결과를 반환받을 때 사용한다.

프로시저와 함수의 가장 큰 차이점은 반환 값의 존재 여부다.

그런데! 프로시저에서도 값을 반환하는 방법이 있는데 바로 OUT 매개변수를 통해서 실현할 수 있다.

OUT 매개변수란 프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당한다. 그리고 나서 실행이 끝나면 전달한 변수를 참조해 값을 가져올 수 있는 것이다.

프로시저 생성 시 매개변수명과 데이터 타입만 명시하면 디폴트로 IN 매개변수가 되지만 OUT 매개변수는 반드시 OUT 키워드를 명시해야 한다.

select_proc에서 이름을 받는 OUT 매개변수를 추가해 보자.

DROP PROCEDURE IF EXISTS SELECT_PROC;

DELIMITER $$
CREATE PROCEDURE SELECT_PROC(IN PARAM_USERID VARCHAR(20), OUT RET_NAME VARCHAR(20))
BEGIN
	SELECT NAME INTO RET_NAME FROM USERTBL WHERE USERID=PARAM_USERID;
END $$
DELIMITER ;

CALL SELECT_PROC('BBK',@RET_NAME);
SELECT @RET_NAME;

in out 매개변수 사용

프로시저에서 매개변수를 받을 때 그리고 결과값을 받을 때 둘다 사용가능

DROP PROCEDURE IF EXISTS SELECT_PROC;

DELIMITER $$
CREATE PROCEDURE SELECT_PROC(INOUT VAL VARCHAR(20))
BEGIN
	SELECT NAME INTO VAL FROM USERTBL WHERE USERID=VAL;
END $$
DELIMITER ;

SET @VAL = 'BBK';
CALL SELECT_PROC(@VAL);

SELECT @VAL;

SELECT INTO Syntax
Copy all columns into a new table:

실습해보기

< 선행 조건 >

db01

t1
---------------------------------
id(p.k)   name   addr
---------------------------------
1 홍길동 부산
2 서길동 광주
3 남길동 서울
4 북길동 인천
----------------------------------
view01 : user1/select    -> id>3
proc01 : user2/execute   -> id<4

create database db01;
use db01;

create table t1(
	id int primary key,
    name varchar(30),
    addr varchar(30)
    );
    
insert into t1 values 
	(1,'홍길동','부산'),
	(2,'서길동','광주'),
	(3,'남길동','서울'),
	(4,'북길동','인천');
    
create role view1;
create role proc1;

grant execute on db01.* to proc1;
grant select on db01.* to view1;

create user user1@'%' identified by '1234';
create user user2@'%' identified by '1234';

grant view1 to user1@'%';
grant proc1 to user2@'%';

< user1 접속시>

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -h 192.168.1.133 -u user1 -p
mysql> set role all;
Query OK, 0 rows affected (0.00 sec)

mysql> use db01;
Database changed
mysql> select * from v1;
+----+--------+------+
| id | name   | addr |
+----+--------+------+
|  4 | 북길동 | 인천 |
+----+--------+------+
1 row in set (0.01 sec)

mysql>
mysql> call porc();
ERROR 1370 (42000): execute command denied to user 'user1'@'%' for routine 'db01.porc'

<user2 접속시 >

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -h 192.168.1.133 -u user2 -p
mysql> set role all;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> use db01;
Database changed
mysql>
mysql> call porc();
+----+--------+------+
| id | name   | addr |
+----+--------+------+
|  1 | 홍길동 | 부산 |
|  2 | 서길동 | 광주 |
|  3 | 남길동 | 서울 |
+----+--------+------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select * from v1;
ERROR 1142 (42000): SELECT command denied to user 'user2'@'DESKTOP-7EQF3M8' for table 'v1'

728x90

저작자표시 비영리 변경금지

'Database > mysql' 카테고리의 다른 글

[MYSQL / Database] view 생성 방법 (0)	2023.04.14
[MYSQL / Database] 제약조건 설정 방식 (0)	2023.04.13
[MYSQL /Database] Foreign key 구성 동작 방식 (0)	2023.04.13

CodeJin

[MYSQL / Database] 프로시저 (Procedure)

프로시저(PROCEDURE)

변수 사용

- 무조건 변수명 앞에 @를 붙여줘야 함 <예> set @my = 1; 또는 set @my := 1
- 프로시저, 사용자 정의 함수 만들때 변수 선언 많이 사용함

프로시저 특징

- 보안을 강화할 수 있다.(뷰와 같음)
- 유지보수가 편하다.
- 프로시저 호출시 처음 1회 컴파일 이후 메모리에 로딩되어서 성능 향상이 있다.
- 긴 수십줄의 쿼리 문자열을 생성할 필요없이 프로시저 이름만 호출

프로시저 생성 /제거

in 매개변수 사용하여 호출하기

out 매개변수 사용

in out 매개변수 사용

실습해보기

< user1 접속시>

<user2 접속시 >

'Database > mysql' 카테고리의 다른 글

댓글

티스토리툴바

[MYSQL / Database] 프로시저 (Procedure)

프로시저(PROCEDURE)

변수 사용

- 무조건 변수명 앞에 @를 붙여줘야 함 <예> set @my = 1; 또는 set @my := 1- 프로시저, 사용자 정의 함수 만들때 변수 선언 많이 사용함

프로시저 특징

- 보안을 강화할 수 있다.(뷰와 같음)- 유지보수가 편하다.- 프로시저 호출시 처음 1회 컴파일 이후 메모리에 로딩되어서 성능 향상이 있다.- 긴 수십줄의 쿼리 문자열을 생성할 필요없이 프로시저 이름만 호출

프로시저 생성 /제거

in 매개변수 사용하여 호출하기

out 매개변수 사용

in out 매개변수 사용

실습해보기

< user1 접속시>

<user2 접속시 >

'Database > mysql' 카테고리의 다른 글

관련글

댓글

티스토리툴바

- 무조건 변수명 앞에 @를 붙여줘야 함 <예> set @my = 1; 또는 set @my := 1
- 프로시저, 사용자 정의 함수 만들때 변수 선언 많이 사용함

- 보안을 강화할 수 있다.(뷰와 같음)
- 유지보수가 편하다.
- 프로시저 호출시 처음 1회 컴파일 이후 메모리에 로딩되어서 성능 향상이 있다.
- 긴 수십줄의 쿼리 문자열을 생성할 필요없이 프로시저 이름만 호출