Mysql 5.X Function & Procedure 만들기 기타 쿼리

펑션 및 기본 프로시저 생성

delimiter $$
 
DROP FUNCTION IF EXISTS dbname.string_print$$
 
CREATE FUNCTION dbname.string_print( str VARCHAR(20) ) RETURNS VARCHAR(20)
     BEGIN
          DECLARE copy_str VARCHAR(20);
          SET copy_str = str;
          RETURN copy_str;
     END $$
 
delimiter ;
 
SELECT dbname.string_print('Hello world');



delimiter $$
 
DROP PROCEDURE IF EXISTS dbname.string_print$$
 
CREATE PROCEDURE dbname.string_print( str VARCHAR(20) )
     BEGIN
          DECLARE copy_str VARCHAR(20);
          SET copy_str = str;
          SELECT copy_str;
     END $$
 
delimiter ;
 
CALL dbname.string_print('Hello world');

예외처리

4번라인 파라메터: IN -> input, OUT -> output <가독성을 위한 표시 기능>

7~8번라인 : 예외 핸들링을 위한 변수 선언. 쿼리 실패시 err값은 '-1'

delimiter $$
 
DROP PROCEDURE IF EXISTS db_name.procedure_name$$
CREATE PROCEDURE db_name.procedure_name(IN num INT, IN ch VARCHAR(2))
BEGIN
 
     DECLARE err INT DEFAULT '0'; 
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET err = -1;
    
     START TRANSACTION;
     INSERT INTO table_name VALUES (num, ch);     // 성공
     INSERT INTO table_name VALUES (ch, num);     // 실패
    
     IF err < 0 THEN 
          ROLLBACK; 
     ELSE 
          COMMIT; 
     END IF;
 
END $$
 
delimiter ;

루프

delimiter $$
 
DROP PROCEDURE IF EXISTS db_name.procedure_name$$
CREATE PROCEDURE db_name.procedure_name(IN ch VARCHAR(2))
BEGIN
 
     DECLARE val INT DEFAULT '0'; 
    
     WHILE val < 5 DO
          INSERT INTO table_name VALUES (val, ch);
          SET val = val + 1;
     END WHILE;
    
END $$
 
delimiter ;

커서

delimiter $$
 
DROP PROCEDURE IF EXISTS proc_name$$
CREATE PROCEDURE proc_name()
BEGIN 
DECLARE cur_state INT DEFAULT '0'; 
DECLARE copy_column1 INT DEFAULT '0'; 
DECLARE copy_column2 INT DEFAULT '0'; 
DECLARE result_count INT DEFAULT '0';
 
DECLARE cur CURSOR FOR SELECT column1, column2 FROM table1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cur_state = 1;
 
OPEN cur;
REPEAT
FETCH cur INTO copy_column1, copy_column2; →커서 이동 및 결과 매칭
IF NOT cur_state THEN →커서 상태 체크
UPDATE ...;
SET result_count = result_count + 1;
END IF;
UNTIL cur_state END REPEAT;
CLOSE cur;
 
IF result_count > 0 THEN
SELECT result_count;
ELSE
SELECT 0;
END IF;
END $$
 
delimiter ;

추가 : select 검색 결과를 변수에 넣기

DROP PROCEDURE IF EXISTS proc_name$$
CREATE PROCEDURE proc_name()
BEGIN 
DECLARE result INT DEFAULT '0'; 
 
SELECT column1 INTO result FROM table1 WHERE ...;
SELECT reslut;
END $$
 
delimiter ;

※ MySQL에선 'result = column1'이 안된다.

임시 테이블 생성

CREATE TEMPORARY TABLE IF NOT EXISTS TEMP_TABLE (
       ID BIGINT(20) NOT NULL 
);

Create Temporary Table my_temp_table AS
    Select * From my_permanent_table; 

테이블 생성시 pk 추가 (다중컬럼 복합키)

CREATE TABLE EMP (
EMP_NO CHAR(20) NOT NULL PRIMARY KEY,
EMP_NAME CHAR(20)     NOT NULL,
SALARY    NUMBER(4)     NOT NULL,
JOB   CHAR(10),
EMAIL    VARCHAR(50)
);

또는

예2)
CREATE TABLE EMP (
EMP_NO CHAR(20) NOT NULL,
EMP_NAME CHAR(20)     NOT NULL,
SALARY    NUMBER(4)     NOT NULL,
JOB   CHAR(10),
EMAIL    VARCHAR(50),
PRIMARY KEY(EMP_NO)
);

날짜 및 기타

date_add('20140701',interval 1 day) #날짜 더하기 (월 년, 주 등 더할 수 있다. 해당내용은 해당 상수를 검색)
/* 상수목록
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
*/
DATEDIFF('20140801','20140701') #일짜 빼기(지정한 범위의 일수 를 구함)
CONCAT('A','B'); #문자열 연결
IFNULL(FIELDNAME,EXPR) #널일경우 EXPR 의 내용으로 값 출력
#행번호 처리 방법1
SELECT @ROW_NM := @ROW_NM + 1 AS IDX
FROM TABLENM, (SELECT @ROW_NM := 0) 
#행번호 처리방법2 (조건절이 있을 경우)
SELECT @ROW_NM := @ROW_NM + 1 AS IDX
FROM TABLENM
WHERE (@ROW_NM := 0)=0;
#기타 조인 이나 서브쿼리에서는 (SELECT @ROW_NM := 0) 을 조인절에 넣어 주거나(INNER JOIN (SELECT @ROW_NM := 0) )
#서브테이블에서는 ,(SELECT @ROW_NM := 0) 를 넣어 초기화를 반드시 해주면 된다.

##select 쿼리시 Every derived table must have its own alias 의 오류 가 생길 경우
##해당 오류는 서브 쿼리의 알리아스가 없어서 생기는 오류
## select * from tb1, (select * from tb2) 의 쿼리를 select * from tb1, (select * from tb2) t 이런식으로 명시적으로 이름 표시 하면 해결.

##초성검색 예제
select * from q12 where Q12DES rlike '^(A)' or (Q12DES >= 'A'  and Q12DES < 'C') order by Q12DES
select * from table_name where col rlike '^(ㄴ)' or (col >= '나'  and col < '다') order by col