[실무] 프리페어리드 스테이트먼트
EXPLAIN EXTENDED
SELECT @reply_time:=reply_time FROM cs_partner FORCE INDEX(IDX_MID_DEALID) WHERE deal_id = 128335 AND order_id = 35656659
SELECT @reply_time FROM cs_partner WHERE deal_id= 128335 AND @reply_time = 1
SHOW INDEX FROM cs_partner
SELECT COUNT(*) FROM cs_partner
SET @var_user_id = 'Fantine';
SET @var_user_name = 'Anne';
SET @var_query = CONCAT('explain extended SELECT @cs_seq:=cs_seq, @deal_name:=deal_name FROM cs_partner WHERE cs_seq = @var_user_id OR order_id = @var_user_name');
PREPARE stmt1 FROM @var_query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
1. 입력 변수
1) MySQL Reference Manual 에 따르면, 아래와 같이 EXECUTE ... USING ... 형식으로 지정한다.
SET @var_user_id = 'Fantine';
SET @var_user_name = 'Anne';
SET @var_query = CONCAT('SELECT * FROM user_info WHERE user_id = ? OR user_name = ?');
PREPARE stmt1 FROM @var_query;
EXECUTE stmt1 USING @var_user_id, @var_user_name;
DEALLOCATE PREPARE stmt1;
2) 그런데, 그냥 다음과 같이 직접 써도 된다. 음...
SET @var_user_id = 'Fantine';
SET @var_user_name = 'Anne';
SET @var_query = CONCAT('SELECT * FROM user_info WHERE user_id = @var_user_id OR user_name = @var_user_name');
PREPARE stmt1 FROM @var_query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
3) 변수를 DECLARE 구문으로 선언한 경우에는 다음의 방법만 가능하다.
DECLARE var_user_id nvarchar(255);
DECLARE var_user_name nvarchar(255);
SET var_user_id = 'Fantine';
SET var_user_name = 'Anne';
SET @var_query = CONCAT('SELECT * FROM user_info WHERE user_id = ''', var_user_id, ''' OR user_name = ''', var_user_name, '''');
-- 또는
-- SET @var_query = CONCAT('SELECT * FROM user_info WHERE user_id = \'', var_user_id, '\' OR user_name = \'', var_user_name, '\'');
PREPARE stmt1 FROM @var_query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1
2. 출력 변수
1) 변수에 직접 지정 한다.
SET @var_out_param = ''; -- (변수 초기화가 필요없으면) 생략 가능함!!!
SET @var_query = CONCAT('SELECT user_name INTO @var_out_param FROM user_info WHERE user_id = ''Fantine''');
-- 또는
-- SET @var_query = CONCAT('SELECT user_name INTO @var_out_param FROM user_info WHERE user_id = \'Fantine\'');
PREPARE stmt1 FROM @var_query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
-- 결과 확인
-- SELECT @var_out_param;
2) 변수를 DECLARE 구문으로 선언한 경우에는 다음의 방법만 가능하다.
DECLARE var_user_name nvarchar(255);
SET @var_out_param = ''; -- (변수 초기화가 필요없으면) 생략 가능함!!!
SET @var_query = CONCAT('SELECT user_name INTO @var_out_param FROM user_info WHERE user_id = ''Fantine''');
-- 또는
-- SET @var_query = CONCAT('SELECT user_name INTO @var_out_param FROM user_info WHERE user_id = \'Fantine\'');
PREPARE stmt1 FROM @var_query;
EXECUTE stmt1;
SET var_user_name = @var_out_param;
DEALLOCATE PREPARE stmt1;
-- 결과 확인
-- SELECT var_user_name;