[MySQL] PL

PL (Procedural Language)

• sql을 function으로 만들어 놓은 것.
• SQL을 확장한 순차적 언어
• → procedure(처리자), Function, Trigger
• 변수, 제어문(if, while, loop)

 

-- autocommit 확인
select @@autocommit;

-- autocommit off
set autocommit = false;

autocommit 설정. 설정했다가 mysql 껐다가 키면 다시 초기화 되어있다.

 

-- 변수
set @counter = 10;
select @counter;

set @message = '하이';
select @message;

set @height = 178.3 + 11;
select @height;

변수 설정

 

procedure 형식

delimiter $$
create procedure 프로시저명 ( IN 매개변수명 자료형, OUT 매개변수명 자료형 )
begin
         쿼리문 + 제어처리문
end$$
delimiter ;

• delimiter 뒤 ; 사이에 공백이 필요함
• IN ↔ OUT

 

procedure 생성

delimiter $$
create procedure sp_hello_world ()
begin
	-- 프로시저 안에 변수 선언할 때 declare 붙여야 함
	declare message varchar(20);
	set message := ' hello PL ';
	select concat('message = ', message);
end$$
delimiter ;

delimiter //
create procedure myproc(in inum int, out onum int)
begin
	select inum;
	set onum = 333;
end;
//
delimiter ;

delimiter //

create procedure add_dept(in deptno int, in deptname varchar(30), in deptloc int)
begin
	-- error checking
	declare exit handler for 1365
	begin
		select 'insert fail';
	end;
	insert into departments(department_id, department_name, location_id)
	values(deptno, deptname, deptloc);
    
end ;
//
delimiter ;

 

procedure 삭제

-- procedure 삭제
drop procedure if exists sp_hello_world;

 

procedure 호출

-- procedure 호출
call sp_hello_world();

call myproc(123, @outNum);
select @outNum;

call add_dept(300, '관리부', 1400);

 

예제 )

-- 사원번호를 입력받고 그 사원의 급여에 1000을 더한 값으로 갱신하라
-- 사원번호와 갱신된 급여를 출력하라
delimiter //
create procedure update_salary_1000(in v_empno int)
begin
	declare emp_sal decimal(8,2);
	update employees set salary = salary + 1000 where employee_id = v_empno;
	select salary into emp_sal from employees where employee_id = v_empno;
	select v_empno as 사원번호, emp_sal as 급여;
end;
//
delimiter ;

call update_salary_1000(150);