MySQL Function 사용법 및 Tips

Function 사용 예제 DROP FUNCTION IF EXISTS getTotals # CREATE FUNCTION getTotals(d_id VARCHAR(50), start_date VARCHAR(30), end_date VARCHAR(30)) RETURNS INT(10) BEGIN DECLARE totals INT(10); SET totals = ( SELECT COUNT(*) AS total FROM device_info WHERE DATE_FORMAT(CONVERT_TZ(device_info.create_time, "UTC", "Asia/Seoul"), "%Y-%m-%d %H:%i:%S")>=start_date AND DATE_FORMAT(CONVERT_TZ(device_info.create_time, "UTC", "Asia/Seoul"), "%Y-%m-%d %H:%i:%S")<=end_date AND device_info.device_id = d_id ); RETURN totals; END # SELECT […]

MySQL Procedure Function 예제

mysql> mysql> CREATE TABLE Employee( -> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> ); Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) -> values (1,'Jason', 'Martin', '19960725', […]