[MySQL] 프로시저

 

입력 매개변수가 있는 스토어드 프로시저를 생성하고 실행해보기 

 

use sqlDB;
drop PROCEDURE if exists userproc1;
delimiter $$ 
create PROCEDURE userProc1 (IN userName VARCHAR(10))
BEGIN
	select * from usertbl where name = userName;
    End $$
    delimiter ;
    
    call userProc1('조관우');

 

+++

 

    drop PROCEDURE if EXISTS userProc3;
    delimiter $$
    CREATE PROCEDURE userProc3(
		IN txtValue CHAR(10),
		out outValue INT
        )
        begin
        insert INTO testTBL values(null,txtValue);
        select MAX(id) into outvalue from testTBL;
        end $$
        delimiter ;
        create table if not exists testTBL(
        id int AUTO_INCREMENT PRIMARY key,
        txt char(10)
        );
        
        call userProc3 ('테스트값', @myValue);
        select concat('현재 입력된 ID 값 ==>', @myValue);

 

+++

 

        drop PROCEDURE if exists ifelseproc;
        delimiter $$
        create procedure ifelseproc(
			in userName VARCHAR(10)
            )
            begin
            DECLARE bYear INT;
            select birthYEar into bYear FROM userTBL
            where name = userName;
            if(bYear >= 1980) then
            select '아직 젊군요..';
            else 
				select '나이가 지긋하시네요.';
			end if;
            end $$
            delimiter ;
            call ifelseproc('조용필');

 

 

+++

 

            drop PROCEDURE if exists caseProc;
            delimiter $$ 
            create PROCEDURE caseProc(
				IN userName VARCHAR(10)
                )
                BEGIN
                DECLARE bYear INT;
                DECLARE tti CHAR(3);
                select birthYEar INTO bYear from userTbl
                where name = userName;
                case
					when (bYear%12 = 0) then set tti = '원숭이';
                    when (bYear%12 = 1) THEN set tti = '닭';
                    when (bYear%12 = 2) then set tti = '개';
                    when (bYear%12 = 3) then set tti = '돼지';
                    when (bYear%12 = 4) then set tti = '쥐';
                    when (bYear%12 = 5) then set tti = '소';
                    when (bYear%12 = 6) then set tti = '호랑이';
                    when (bYear%12 = 7) then set tti = '토끼';
                    when (bYear%12 = 8) then set tti = '용';
                    when (bYear%12 = 9) then set tti = '뱀';
                    when (bYear%12 = 10) then set tti = '말';
                    else set tti = '양';
                    end case;
                    select concat(userName, '의 띠 ==>', tti);
                    end $$
                    delimiter $$
                    
                    call caseProc ('김범수');

 

 

+++

 

set global log_bin_trust_function_creators = 1;
use sqldb;
drop function if exists userfunc;
delimiter $$
create function userfunc(value1 INT, value2 INT)
returns int
begin 
	return value1 + value2;
    end $$
    delimiter ;
    
    select userFunc(100, 200);

 

+++

 

    use sqldb;
    drop function if exists getAageFunc;
    delimiter $$
    create function getAgeFunc(bYear INT)
		returns INT
        begin
			DECLARE age INT;
			set age = year(curdate()) - bYear;
            return age;
	end $$ 
    delimiter ;
    
    select getAgeFunc(1979);

 

+++

 

    select getAgeFunc(1979) into @age1979;
    select getAgeFunc(1997) into @age1989;
    select concat('1997년과 1979년의 나이차 ==> ', (@age1979-@age1989));