mysql FUNCTION, PROCEDURE 예제

-- 함수 확인
show function status where db = 'iu';

-- 프로시저 확인
show procedure status where db = 'iu';

# 단일 값 조회
DELIMITER //

DROP FUNCTION IF EXISTS iu.FUNC_Test_Select;

CREATE FUNCTION FUNC_Test_Select ( setid INT )
RETURNS varchar(30)
COMMENT 'Select Function (test)'
BEGIN
	DECLARE rValue varchar(30);
	SET rValue = null;

	select mb_id INTO rValue from test where id=setid limit 1;

	RETURN rValue;
    
END//
DELIMITER ;


select FUNC_Test_Select (800);

# 값 저장
DELIMITER //

DROP FUNCTION IF EXISTS iu.FUNC_Test_Insert;

CREATE FUNCTION FUNC_Test_Insert ( cnt INT )
RETURNS INT
COMMENT 'Insert Function (test)'

BEGIN

	DECLARE i INT;
	DECLARE str varchar(50);

	SET i = 1;
	SET str = '';

	label1: WHILE i <= cnt DO
		SET str = CONCAT('테스트', cast(i as char(10)));
		INSERT INTO test (value1, value2) VALUES (i, str);
		SET i = i + 1;
	END WHILE label1;

	RETURN (i-1);

END//

DELIMITER ;



select FUNC_Test_Insert (2);

 

 

 

# IP 만들기  프로시저

 

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

오류 발생시 COLLATE utf8_unicode_ci 추가

DELIMITER //

DROP PROCEDURE IF EXISTS iu.PROC_IP_Create;

CREATE PROCEDURE PROC_IP_Create(ip3 varchar(11), cnt int)
COMMENT 'IP 생성 프로시저'
BEGIN

	DECLARE i INT;
	DECLARE str varchar(50);

	SET i = 1;
	SET str = '';

	label1: WHILE i <= cnt DO
		SET str = CONCAT(ip3, '.', cast(i as char(10)));

		#없을 경우에만 저장
		if not exists(select id from test where ip_address = str COLLATE utf8_unicode_ci) then
   			INSERT INTO test (ip_address, datetime)
   			VALUES (str, NOW());
		end if;

		SET i = i + 1;
	END WHILE label1;

	select * from test order by id desc;

END //
DELIMITER ;



call PROC_IP_Create ('192.168.13', 12);