[Mysql] 프로시저 사용 예

프로그래밍/SQL & PHP

[Mysql] 프로시저 사용 예

님투 2009. 1. 1. 10:58

테이블 생성

DROP TABLE IF EXISTS `test`.`tbl_sp`;
CREATE TABLE `test`.`tbl_sp` (
`SEQ` int(10) unsigned NOT NULL auto_increment,
`DATA` varchar(45) default NULL,
PRIMARY KEY (`SEQ`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

데이터 생성

INSERT INTO test.tbl_sp(DATA)
VALUES
    ('mysql'),
    ('java'),
    ('hibernate'),
    ('spring'),
    ('zapatec'),
    ('jexcel'),
    ('iReport'),
    ('json')
;

프로시저 작성(spDynamicQuery.sql)

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`spDynamicQuery` $$
CREATE PROCEDURE `test`.`spDynamicQuery` (IN szData VARCHAR(50))
BEGIN

SET @query='SELECT * FROM test.tbl_sp WHERE DATA LIKE ?';
SET @data = CONCAT('%',szData,'%');

PREPARE stmt FROM @query;
EXECUTE stmt USING @data;
DROP PREPARE stmt;

END $$

DELIMITER ;