[MySQL] 내장 함수, 세션 변수, 프로시저, 함수 예제

1. MySQL 내장함수 예제

 

-- 내장함수

select abs(-78);	/*절대값 내장함수*/
select round(4.875, 1);	/*소수점 반올림해서 첫째자리까지 표현함*/ 


select * from book;
/*책 이름 중에 '야구'를 '농구'로 변환해 result grid에 출력*/
select bookid, replace(bookname, '야구', '농구') bookname
from book;												/*원본 데이터는 변하지 않는다.*/


select * from customer;
/*성을 따로 잘라내어 표시하고 해당 성을 가진 사람이 몇 명인지 표시하는 코드*/
select substr(name, 1, 1) as '성', count(*) as '인원'
from customer
group by substr(name, 1,1);		/*count같은 집계함수는 group by로 어떤 항목을 집계할건지 정해줘야한다.*/

/*orderDate 컬럼의 Date형식 자료를 받아와 형식지정자를 사용해 포메팅하는 코드*/
select * from orders;
select orderid, str_to_date(orderdate, '%Y-%m-%d') as '날짜' from orders;
select orderid, orderdate as 날짜 from orders;

select sysdate(), date_format(sysdate(), '%Y/%m/%d %M %h:%s');

select date_format(orderdate, '%y년 %m월 %d일') from orders;
select * from customer;

/*is not null 널이 아닌 값을 where로 조건을 만들 때 사용*/
select * from customer where phone is not null;

/*ifnull 해당 컬럼이 null이라면 두번째 인자로 변환해 출력한다.*/
select name, ifnull(phone, '연락처 없음') as '전화번호' from customer;

 

 

 

2. 세션 변수 예제

 

/*@ 세선변수 지정자, := 사용해서 값을 초기화한다.*/
set@seq:=0;

select (@seq:=@seq+1) as '순번', custid, name, phone
from customer
where @seq<3;

 

 

3.  프로시저 예제

 

use jin;
delimiter //			

CREATE PROCEDURE repeater(p1 INT)	/*p1은 정수형 매개변수를 의미함.*/
BEGIN	/*프로시저 시작점, 시작점~종료점 사이에서 ctrl+enter 키를 눌러야 프로시저가 만들어진다.*/
	SET @x =0;
    REPEAT SET @x = @x+1; UNTIL @x > p1 END REPEAT;
END		/*프로시저 종료점*/
//
delimiter ;
/*delimiter는 구분자를 의미함. 이 부분까지 하나의 코드로 인식해서 실행하겠다는 뜻*/
CALL repeater(100);
select @x;	/*101이 출력 됨*/

drop procedure if exists InsertBook; /*해당 프로시저가 있다면 지우겠다.*/

/*Book테이블에 새로운 행을 추가하는 프로시저*/
delimiter //
create procedure InsertBook(
myBookID integer,
myBookName varchar(40),
myPublisher varchar(40),
myPrice int)
BEGIN
	DECLARE myCount INTEGER;	/*myCount 정수형 변수를 선언한다.*/
    SELECT count(*) into myCount FROM book WHERE bookName LIKE myBookName;	/*select ~ into ~: 해당 값을 변수에 저장하겠다.*/
    IF MyCount != 0 THEN
		UPDATE book SET price = myPrice WHERE bookName LIKE myBookName;
	ELSE		/*myCount=0이면~*/		
		INSERT INTO Book(bookId, bookName, publisher, price)
		VALUES (myBookID, myBookName, myPublisher, myPrice);
	END IF;
END;
//
delimiter ;

/*call로 프로시저를 불러 실행한다. */
SET SQL_SAFE_UPDATES = 0;
call InsertBook(99, '오늘의 스포츠', 'KBS', 20000);
/*같은 pk값이 중복되면 값을 수정한다.*/
call InsertBook(99, '오늘의 스포츠', 'KBS', 50000);
select * from book;
call InsertBook(98, '내일의 스포츠', 'KBS', 50000);


/*평균값을 반환하는 프로시저*/
drop procedure if exists AvgPrice;
delimiter //
CREATE procedure AvgPrice(OUT val INTEGER) /*정수형으로 반환 값을 갖는 프로시저*/
BEGIN
	select avg(price) into val
    from book where price is not null;    
END
//
delimiter ;

/*@지정자를 사용해 변수에 값을 반환받는다. set @myval = call AvgPrice();가 될것 같지만 SQL에선 좀 다르다.*/
call AvgPrice(@myval);
select @myval;

 

 

4.  함수 예제

 

/*10% 할인된 가격을 구하는 방법*/
select bookid, bookname, publisher, price, price*0.9 '할인된 가격' from book;

/*외부에서 자료를 수정하는 것(log_bin)을 기본적으론 허용하지 않지만 이 코드로 허용하게 한다.*/
set global log_bin_trust_function_creators = ON;
/*price가 3만원 이상이면 10%할인, 아니면 5%를 할인하는 펑션. 리턴값으로 정수형을 갖는다.*/
delimiter //
CREATE function fnc_price(price integer) returns INT
BEGIN
	declare myPrice int;
	if price >= 30000 then set myPrice = price * 0.9;
    else set myPrice = price * 0.95;
    end if;
    return myPrice;
END;
//
delimiter ;

/*함수를 반환받아 컬럼에 추가하는 select문 */
select bookid, bookname, publisher, price, fnc_price(price) as 'salePrice'
from book;

 

• 함수는 단일 스칼라 값만 return으로 반환 받을 수 있다. 
• 테이블을 리턴으로 반환하거나 여러 유형을 반환할 수 없음을 유의하자. 
• where, if 절에서 값을 비교하거나 select 컬럼을 하나씩 추가하는 용도로 사용할 수 있다.