Today
- SQL 프로그래밍
-- 2024-02-21
-- 지난 시간 돌아보기
/*
- FK가 설정되어있을 경우 FK UPDATE/DELETE 설정에 따라
1) CASCADE : 자식 테이블에서도 싹다 업데이트/삭제 됨
* DBMS마다 동작성이 조금 씩 다름 MariaDB의 경우 경고 없이 그냥 삭제/수정 됨
다른 DBMS의 경우 경고 띄워서 확인하게 하고 처리하는 경우도 있음
2) SET NULL : 자식 테이블에서 해당 값이 NULL로 변경 됨
3) RESTRICT : 외래키 설정되어있는 컬럼 값은 못 삭제함
따라서 DB에서 테이블을 삭제할 때는
1) 관련 테이블 구조를 모두 확인 후 작업 해야함
2) DELETE / UPDATE 쿼리를 날릴 때 NOT EXSITS 등 으로 자식 테이블에 없는 경우만 DELETE/UPDATE만 한다든지 제한 작업이 반드시 필요합니다.
★★★★★ 무조건 DELETE/UPDATE 작업 시에는 TRANSACTION 걸고 작업해야함!!! => 엄청 강조하심!
[사용법] (MariaDB 버전)
SET AUTOCOMMIT = 0; => MariaDB는 AUTOCOMMIT이 자동 활성화되어있어 해당 부분 비활성화 처리
START TRANSACTION; => TRANSACTION 시작 키워드, 다른 DBMS는 좀 다름 (ex : BEGIN TRANSACTION)
COMMIT; => TRANSACTION 작업 다 완료 후 확정하는 작업, 확정 이후에는 롤백 불가
ROLLBACK; => TRANSACTION 시작 시점으로 돌아가는 키워드!
*/
# DELETE / UPDATE 쿼리를 날릴 때 NOT EXSITS 사용 예시
DELETE
FROM tbl_goodsinfo AS GD
WHERE GD.goodsCode = 'GDS006'
AND NOT exists (
SELECT * FROM tbl_orderdetail AS OD WHERE OD.goodsCode = GD.goodsCode
);
-- INDEX 확인하기
/*
[사용법]
SHOW INDEX FROM 테이블명
◆ 테이블에 설정된 인덱스 설정정보를 확인할 수 있습니다.
*/
SHOW INDEX FROM tbl_orderinfo;
-- 임시 테이블(Temporary Table)
/*
[사용법]
CREATE TEMPORARY TABLE 테이블명 (
열 정의~~
)
;
◆ 생성 방법은 일반 테이블 생성법과 동일 하나 CREATE 키워드 위에 TEMPORARY 로 임시테이블로 구분해주는 부분이 다릅니다.
◆ WITH 테이블명으로 만든 COMMON TABLE의 경우 임시테이블 느낌이긴 한데, 복사해와서 만드는 느낌에 가까우며,
쿼리문을 실행하는 동안만 사용가능하다는 점에서 차이점이 있습니다.
◆ MariaDB에 연결되어있는 세션 내에서만 존재하며, 세션이 닫힐 경우 자동 삭제됩니다. (DB이동만 해도 세션은 끊김)
또한 임시테이블의 경우 생성한 사용자만 접근할 수 있습니다.
◆ 임시테이블도 DROP TABLE 문을 이용하여 사용자가 자체적으로 삭제할 수도 있습니다.
◆ 일반테이블과 이름이 같은경우 임시테이블에 먼저 접근합니다. (임시 테이블의 경우 메모리에 올라와있기 떄문)
다만 이런경우를 만들지 않는게 좋습니다.
◆ 임시 테이블은 실무에서 정말 많이 사용합니다.
*/
# 임시테이블에 넣을 데이터 조회해보기
SELECT from_date, SUM(salary) AS sum_sal, AVG(salary) AS avg_sal
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-08-31'
GROUP BY from_date;
# 데이터 타입을 맞추기 위해 원본 테이블 스키마 조회
DESC salaries;
# 임시테이블 생성
CREATE TEMPORARY TABLE TMP_salaries (
from_date DATE NOT NULL,
sum_sal INT DEFAULT 0,
avg_sal INT DEFAULT 0
)
;
# 임시테이블 내 데이터 추가 (+ TRANSACTION 사용 습관화를 위해 INSERT / DELETE / UPDATE 쿼리 사용시 계속 TRANSACTIO 사용예정)
SET autocommit = 0;
START TRANSACTION;
INSERT INTO TMP_salaries # TMP_salaries 테이블 내 컬럼 구조와 동일하게 아래 select문이 작성되어있어서, 컬럼지정은 생략함.
SELECT from_date, SUM(salary) AS sum_sal, AVG(salary) AS avg_sal
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-08-31'
GROUP BY from_date;
ROLLBACK;
COMMIT;
# 잘 만들어졌나 확인
SHOW TABLES; # 여기서는 위에 만든 임시테이블 안나옴!!! 메모리 상에만 존재함
SELECT * FROM TMP_salaries;
-- 실습1
/*
TMP_salaries 에서
1. 월별 합계금액, 평균 금액을 출력하는 쿼리를 작성하시오.
2. Common Table Expression을 이용하여 위와 동일한 결과가 나오도록 쿼리를 작성하시오.
*/
# 1번
SELECT
concat(to_char(from_date, 'MM'),'월') AS MON
, SUM(sum_sal) AS SUM
, AVG(sum_sal) AS AVG
FROM TMP_salaries
GROUP BY to_char(from_date, 'MM')
;
# 2번
WITH TMP_test(MON, SUM, AVG) AS (
SELECT
concat(to_char(from_date, 'MM'),'월') AS MON
, SUM(sum_sal) AS SUM
, AVG(sum_sal) AS AVG
FROM TMP_salaries
GROUP BY to_char(from_date, 'MM'))
SELECT * FROM TMP_test
;
-- 주석 : SQL문의 실행과 상관없이 쿼리에 대한 설명 등을 작성할 때 사용합니다.
/*
[사용법]
1. 한줄주석
-- 내용
# 내용
2. 여러줄 주석
/*내용*/
*/
-- 실행 가능한 주석 : SQL 실행과 함께 사용합니다.
/*
[실행 가능한 주석]
/*! 내용*/
/*M! 내용*/
/*M!100900 내용*/
*/
-- 변수
/*
[사용법] MariaDB버전
1. 선업법
SET @변수명 = 값;
2. 사용법
SELECT @변수명;
◆ SQL에서는 따로 선언하는 작업이 없어도, 사용시 자동으로 선언되어 사용할 수 있습니다.
◆ SELECT문에서 값을 대입할 떄는 '@변수명 := 값'으로 대입합니다.
SELECT문에서 값을 대입할 때는 변수에는 단일 값만 저장할 수 있으므로 이점을 유의해야합니다.
◆ 변수 사용 시는 묵시적 형변환을 주의하자
[변수명 사용 규칙] MariaDB버전
1. @기호로 변수명을 시작합니다.(왕 중요 지금 쓸때마다 까먹음)
2. 영문(대소문자 구분 X), 숫자, $, _, .을 사용할 수 있습니다.
3. 이외에 문자를 사용할 경우 '' / `` / "" 으로 변수명을 묶어서 사용할 수 있습니다.
이중 ``(백틱)의 경우 MariaDB에서만 사용할 수 있으며, 가능한 묶어서 사용하지 않도록 하는게 좋습니다.
*/
-- 사용예시
SET @A = 100;
SET @B = 200;
SELECT @A, @B, @A+@B; # 결과 : 100, 200, 300
SET @C = 'A100';
SET @D = '200';
SELECT @C+@D, @C, @D; # 결과 : 200, A100, 200
SELECT @id := memberId, @nm := NAME
FROM tbl_member
WHERE memberId = 'test';
SELECT @id, @nm;
SELECT @orderNo := orderNo, @amount := aumount, @cancel := cancelYN
FROM tbl_orderinfo
WHERE memberId = @id;
# 응용예시, 이런식으로 값체크 후 업데이트 날려주거나 할 때 사용! (실행은 안해봄)
if @cancel = 'N' then
SET autocommit = 0;
START TRANSACTION;
UPDATE tbl_orderDetail
SET orderedCnt = 5, amount = 10000
WHERE orderNo = @orderNo;
if ROW_COUNT > 0 then
COMMIT;
else
ROLLBACK;
END if;
# 응용예시
SELECT memberId, name
INTO @memberId, @NAME
FROM tbl_member
WHERE memberId = 'gee1';
SELECT @memberId, @NAME;
# 응용예시
SELECT @id := memberId, @nm := NAME
FROM tbl_member;
SELECT @id, @nm;
# 위 처럼 다항 결과셋을 변수에 대딥하려 하면 대입하는 쿼리에서 마지막으로 대입된 값만 변수에 최종 저정 됨(변수에는 단항만 저장할 수 있기 때문)
SELECT CONVERT(orderDate, CHAR(7)) # 데이터가 긴데 char(7)로 변환하면 글자 짤린다고 뭐라하는 에러메시지 발생함 => 확인하고 넘기면됨.
INTO @orderYYYYMM
FROM tbl_orderinfo
WHERE orderNo = '202301008';
SELECT @orderYYYYMM;
SELECT LEFT(orderDate, 4), MONTH(orderDate), DAYOFWEEK(orderDate)
INTO @YYYY, @mm, @dw
FROM tbl_orderinfo
WHERE orderNo = '202301008';
SELECT @YYYY, @mm, @dw;
-- 사용자 정의 변수 목록 확인하기 : information_schema DB의 user_variables에서 확인할 수 있습니다.
# 외부 데이터베이스의 테이블 조회하기
/*
(서버명).데이터베이스명.테이블명
◆ 서버명의 경우 내 로컬서버안에서 조회하는 경우는 생략
◆ 같은 서버라도 DB권한을 어디까지 갖고있냐에 따라 조회할 수 있는 DB가 다름
현재 나는 루트 권한으로 갖고있어서 내 로컬서버안에 있는 모든 DB는 다 조회 가능
*/
SELECT * FROM information_schema.user_variables;
-- BEGIN ~ END문: 프로그램 코드블록
/*
[사용법]
[begin_label : ] BEGIN [NOT ATOMIC]
내용;
내용;
END [end_label]
◆ NOT ATOMIC은 저장프로시저 또는 저장함수가 아닌 프로그램 블록을 작성할 떄 사용합니다.
해당 문법은 MariaDB에서만 해당하는 문법입니다.
◆ 선생님 TIP : BEGIN ~ END 문 실행 시 HeidiSQL의 경우 실행옵션이 일괄 보내기로 설정 안되어있을 시 한줄씩 실행해서 오류남.
LABEL을 사용할 수 있는데, 선생님은 안쓰는 주의..! 아래 모든 000 ~ END 000문 에서도 쓸 수 있으나 일단 안쓸예정
*/
# BEGIN ~ END 사용해보기
BEGIN NOT atomic
SET @A = 100, @B = 200;
SET @C = @A + @B;
SELECT @A, @B, @C;
END;
-- 제어문
-- IF ~ END IF 구문 : 조건에 맞는 쿼리를 실행할 수 있습니다.
/*
IF 조건문1
THEN 조건문1 이 참일 경우 실행할 쿼리
ELSEIF 조건문2
THEN 조건문1이 거짓이고 조건문2가 참일 경우 실행할 쿼리
ELSE
위 조건문들 다 거짓일 경우 실행할 쿼리
END IF;
*/
# IF함께 써보기
BEGIN NOT ATOMIC
SET @VAR1 = 100;
IF @VAR1 = 100 THEN
SELECT CONCAT('변수 @VAR1의 값은', @VAR1);
ELSE
SELECT '@VAR1은 100 이 아닙니다.';
END IF;
END
DECLARE @pram;
# IF 사용해보기2
BEGIN NOT ATOMIC
SET @NO = 70;
IF @NO > 80 AND @NO <= 100 THEN
SELECT '상' AS '등급';
ELSEIF @NO > 60 AND @NO <= 80 THEN
SELECT '중' AS '등급';
ELSEIF @NO > 0 AND @NO <= 60 THEN
SELECT '하' AS '등급';
ELSE
SELECT '범위 밖의 숫자 입니다.' AS '등급';
END IF;
END;
-- CASE ~ END 구문 : 조건에 맞는 쿼리를 실행할 수 있습니다.
/*
[사용법 ①] : 값과 비교할 값이 정확히 일치해야함 (Java의 SWITCH문과 유사)
CASE 값/컬럼
WHEN 비교할 값1 THEN 실행할 쿼리1
WHEN 비교할 값2 THEN 실행할 쿼리2
WHEN 비교할 값3 THEN 실행할 쿼리3
WHEN 비교할 값4 THEN 실행할 쿼리4
ELSE 다 만족하지 못할 때 실행할 쿼리
END;
[사용법 ②] : 해당하는 조건에 대한 THEN절 실행 (Java의 IF문과 유사)
CASE
WHEN 조건문1 THEN 실행할 쿼리1
WHEN 조건문2 THEN 실행할 쿼리2
WHEN 조건문3 THEN 실행할 쿼리3
WHEN 조건문4 THEN 실행할 쿼리4
ELSE 다 만족하지 못할 때 실행할 쿼리
END;
◆ CASE ~ END문만 다른 000 ~ END 000문과 달리 END 부분이 END CASE;가 아니라 그냥 END; 이니 주의합시다.
*/
# 사용법1 사용 예시
BEGIN NOT ATOMIC
SET @grade = 'A';
SET @score = CASE @grade
WHEN 'A' THEN 90
WHEN 'B' THEN 80
WHEN 'C' THEN 70
WHEN 'D' THEN 60
ELSE 59
END;
END;
SELECT CONCAT(@grade,'는 ',@score,'점 이상');
# 사용법2 사용 예시
BEGIN NOT ATOMIC
SET @POINT = 65;
SET @grade = CASE
WHEN @POINT >= 90 THEN 'A'
WHEN @POINT >= 80 THEN 'B'
WHEN @POINT >= 70 THEN 'C'
WHEN @POINT >= 60 THEN 'D'
ELSE 'F'
END;
END;
SELECT @grade;
-- 반복문
-- WHILE ~ END WHILE 문
/*
[사용법]
WHILE 조건식 DO
실행할쿼리;
END WHILE;
*/
BEGIN NOT ATOMIC
SET @total = 0;
SET @i = 0;
WHILE @i <= 100 DO
SET @total = @total + @i;
SET @i = @i + 1;
END WHILE;
SELECT @total AS '합계';
END;
-- 실습1
/*
1. 1~10까지 숫자의 곱을 출력하는 쿼리를 작성하시오.
2. 1~100까지 홀수의 합을 구하는 쿼리를 작성하시오.
*/
# 1번
BEGIN NOT ATOMIC
SET @total1 = 1;
SET @i1 = 1;
WHILE @i1 <= 10 DO
SET @total1 = @total1 * @i1;
SET @i1 = @i1 + 1;
END WHILE;
END;
# 2번
BEGIN NOT ATOMIC
SET @total2 = 0;
SET @i2 = 1;
WHILE @i2 <= 100 DO
SET @total2 = @total2 + @i2;
SET @i2 = @i2 + 2;
END WHILE;
END;
SELECT @total1, @total2;
-- FOR ~ END FOR문
/*
[사용법]
FOR 첨자(증감변수) IN [REVERSE] 초기값 .. 목적값(최종값) DO
실행할 쿼리;
END FOR;
◆ FOR ~ END FOR에서 사용하는 첨자(증감변수)의 경우
1) @를 사용하지 않음 ★★★★★ (왕 중요 지금 쓸때마다 까먹음)
2) 해당 FOR ~ END FOR 문 내에서만 사용할 수 있는 지역 변수 역할 입니다.
◆ 초기값에서 설정한 목적값까지 자동으로 1씩 추가합니다.
만약 +1씩 하고 싶지 않다면 실행할 쿼리 내에 증감 연산을 추가해줘서 사용할 수 있습니다.
◆ MySQL에서는 지원하지 않습니다.
*/
BEGIN NOT ATOMIC
SET @total3 = 0;
FOR i IN 1..100 DO
SET @total3 = @total3 + i;
END FOR;
SELECT @total3 AS '합계';
END;
# 증감 수 변경 예제
BEGIN NOT ATOMIC
SET @total3 = 0;
FOR i IN 1..100 DO
SET @total3 = @total3 + i;
SET i = i + 1;
END FOR;
SELECT @total3 AS '합계';
END;
-- 실습2
/*
1. 1~20까지 곱을 구하는 쿼리를 작성하시오.
1-1) FOR문
1-2) WHILE문
2. 1~100까지 짝수 합을 구하는 쿼리를 작성하시오.
2-1) FOR문
2-2) WHILE문
*/
# 1-1.
BEGIN NOT ATOMIC
SET @total4 = 1;
FOR i IN 1..20 DO
SET @total4 = @total4 * i;
END FOR;
END;
# 1-2
BEGIN NOT ATOMIC
SET @total5 = 1;
SET @i5 = 1;
WHILE @i5 <= 20 DO
SET @total5 = @total5 * @i5;
SET @i5 = @i5 + 1;
END WHILE;
END;
SELECT @total4, @total5;
# 2-1
BEGIN NOT ATOMIC
SET @total6 = 0;
FOR i IN 2..100 DO
SET @total6 = @total6 + i;
SET i = i + 1;
END FOR;
END;
#2-2
BEGIN NOT ATOMIC
SET @total7 = 0;
SET @i7 = 2;
WHILE @i7 <= 100 DO
SET @total7 = @total7 + @i7;
SET @i7 = @i7 + 2;
END WHILE;
END;
SELECT @total6, @total7;
-- 실습3
/*
1. 임시테이블 : TMP_member
컬럼 :
idx INT auto_increment
memberId VARCHAR(20) NOT NULL
name VARCHAR(20) NOT NULL
2.
회원아이디1 ~ 100
이름1~100의 형식으로 데이터 넣기
*/
# 지현
# 임시 테이블 만들기
CREATE TEMPORARY TABLE TMP_member (
idx INT AUTO_INCREMENT, # 프라이머리키 주는거 idx INT AUTO_INCREMENT PRIMARY KEY 이렇게 한번에 줘도 됨! (짱 신기)
memberId VARCHAR(20) NOT NULL,
NAME VARCHAR(20) ,
PRIMARY KEY (idx) USING BTREE
);
# 잘만들어졌나 조회
SELECT * FROM TMP_member;
# INSERT 하기 전 TRANSACTION 준비
SET AUTOCOMMIT = 0;
START TRANSACTION;
ROLLBACK;
COMMIT;
# 반복문 돌리기
BEGIN NOT ATOMIC
FOR i IN 1..100 DO
SET @memberId = CONCAT('memberId',i);
SET @memberNm = CONCAT('name',i);
INSERT INTO TMP_member(memberId, NAME)
VALUES (@memberId, @memberNm);
END FOR;
END;
-- LABEL문 : 특정위치로 이동하여 LEABE / ITERATE 하는 문법요소
/*
[사용법]
레이블명 : BEGIN
IF 조건절 THEN
ITERATE 레이블명;
ELSEIF 조건절 THEN
LEAVE 레이블명;
ELSE
실행문;
END 레이블명;
◆ 위 예시는 예시일뿐 000 ~ END 000 관련된거에서는 다 쓸 수 있습니다.
◆ ITERATE : 함께 작성한 레이블명 부분으로 돌아가 반복을 계속 진행합니다. (Java의 continue와 유사)
◆ LEAVE : 함께 작성한 레이블명 부분의 반복문을 종료합니다. (Java의 break와 유사)
◆ 선생님 TIP : LABEL 쓸 일 만들지 말고 반복문 조건안에서 해결하라! 실습은 안하겠다! 그래도 알고는 있어야한다!
*/
-- LOOP ~ END LOOP
/*
[사용법]
LOOP
실행할 쿼리;
END LOOP;
◆ LOOP ~ END LOOP의 경우 진입 시 입력한 실행할 쿼리를 무한 반복합니다.
즉 기본적인 구조에서는 탈출 방법이 없다!
◆ 따라서 LOOP ~ END LOOP에서는 반드시 위 LABEL/LEAVE/ITERATE 문을 이용해서 탈출 지점을 만들어줘야하합니다.
*/
-- REPEAT ~ UNTIL ~ END REPEAT
/*
[사용법]
REPEAT
실행할 쿼리;
UNTIL 종료 조건
END REPEAT;
◆ 위 LOOP ~ END LOOP의 한계(탈출지점 없는거)를 극본한 문법구조로,
일단 한번 실행 후 UNTIL 조건까지만 실행하고 종료됩니다.
◆ UNTIL 의 경우 실행조건이 아닌 종료할 조건임을 명심해야하며, 해당 UNTIL 부분에서는 ;(세미콜론) 사용시 에러가 발생하니 주의해야합니다.
*/
-- 실습 : REPEAT ~ END REPEAT을 이용해서 1~100까지 합을 구하는 쿼리를 작성하시오.
BEGIN NOT ATOMIC
SET @i8 = 1;
SET @total8 = 0;
REPEAT
SET @total8 = @total8 + @i8;
SET @i8 = @i8 + 1;
UNTIL @i8 > 100
END REPEAT;
END;
SELECT @total8 AS '결과';
-- 절차적 SQL에서 오류처리!
/*
[사용법]
DECLARE 처리유형 HANDLER
FOR 조건값1,,,
[BEGIN]
처리할 쿼리;
[END]
◆ 처리유형
CONTINUE : 처리할 쿼리를 실행하고, 현재 프로그램의 실행을 계속 진행합니다.
EXIT : 처리할 쿼리를 실행하고 현재 프로그램의 실행을 종료합니다.
◆ 조건값의 경우 ,(콤마)로 여러가지 작성할 수 있고 OR(또는)으로 해석됩니다.
SQLSTATE sqlstate_value
: sqlstate_value은 SQL문의 실행 상태를 의미하는 5자리 문자 리터럴로 표현하는 코드입니다.
ex) SQLSTATE'23000'
mariadb_error_code
: MariaDB에서 정의하는 숫자로 표현되는 오류 코드 입니다!
ex ) 1064
SQLWARNING
: '01'로 시작하는 SQLSTATE의 단축형 입니다.
NOT FOUND
: '02'로 시작하는 SQLSTATE의 단축형 입니다. 더이상 갖고올 데이터가 없을 경우 해당 에러가 발생합니다.
SQLEXCEPTION
: '01', '02', '03'로 시작하지 않는 SQLSTATE의 단축형입니다.
=> 무슨말인지 사실 제대로 이해 못함 따로 알아봐야할 것 같음.
◆ 조건값의 경우 여러개를 넣을 수 있으며, Java 예외처리처럼 작은 에러부터 작성해야합니다.
[오류 정보 확인하기]
GET [CURRENT] DIAGNOSTICS
CONDITION condition_no [condition_property,,,];
◆ 정보 받을 방법에는 다양한 방법이 있지만, CONDITION 절을 사용하는 방법으로 예시를 봐보자.
GET DIAGNOSTICS CONDITION 1 @SQLSTATE = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;
=> CONDITION1문에서 에러 발생 시 에러정보를 각각 뒤에 작성한 변수에 담습니다! 해당 정보로 뒤에 에러 관련 정보를 표시해주거나 하는식으로 활용할 수 있습니다.
*/
-- DELIMITER
/*
[DELIMITER]
BEGIN ~ END;의 코드 블록을 작성할 때 내부에 쿼리문을 작성하다보면 어디가 해당 코드 블록의 END;인지 불분명해질 수 있습니다.
그 떄 코드 블록 시작과 끝을 명확히 해주기 위해 DELIMITER 문을 사용할 수 있습니다.
DELIMITER $$
BEGIN
내용;
END
$$
◆ DELIMITER 부분은 프로시저할 때 더 자세히 수업 예정!
◆ DBMS별 기본 쿼리 종료 문자 (현재 ;)를 특정 다른 문자로 일시적으로 지정해주는 용도입니다.
DELIMITER $$ => 종료 문자를 $$로 바꿔줌
*/
DELIMITER $$
BEGIN NOT ATOMIC
# BLOCK1
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT '중복키 오류';
END;
# BLOCK2
INSERT INTO tbl_member(memberId, NAME, pwd)
VALUES ('test3', '테스트3', '1234');
SELECT '성공';
END;
$$
DELIMITER ;
#CONTINUE 일 경우 에러 발생 시 실행 구문도 발생하고, 그 다음 에러발생한 부분도 다 실행함 다만, 중복값의 경우 DB에서 제약이 걸려있기 떄문에 실제로 값이 들어가지는 않음.
BEGIN NOT ATOMIC
# BLOCK1
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT '중복키 오류';
END;
# BLOCK2
INSERT INTO tbl_member(memberId, NAME, pwd)
VALUES ('test3', '테스트3', '1234');
SELECT '성공';
END;
# 오류정보 갖고와서 써보기
BEGIN NOT ATOMIC
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @SQLSTATE = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;
SELECT @SQLSTATE, @errno, @err_msg;
END;
INSERT INTO tbl_member(memberId, NAME, pwd)
VALUES ('test5', '테스트5', '1234');
SELECT '성공';
END;
-- 동적 SQL문
-- PREPARE/EXECUTE 문 :
/*
[사용법]
PREPARE 프리페어이름 FROM "개발자가 작성한 쿼리문"; => 프리페어명 안에 쿼리구문 담음
EXECUTE 프리페어이름; => 담겨진 프리페어 사용
◆ PREPARE절로 쿼리를 저장해서, EXECUTE절로 저장한 쿼리를 실행하는 개념입니다.
◆ PREPARE절 내 FROM 뒤 "개발자가 작성한 쿼리문" 부분 안에서 따옴표가 필요할 경우 아래 처럼 사용하여 따옴표 중복을 방지할 수 있습니다.
- "OUTER 'INNER' OUTER"
- 'OUTER "INNER" OUTER'
- "OUTER ""INNER"" OUTER"
- 'OUTER ''INNER'' OUTER'
◆ PREPARE로 등록된 쿼리의 경우 컴파일을 미리 해놓기 때문에 쿼리를 직접 작성해 실행하는 것보다 속도가 빠릅니다.
[해제법]
DROP PREPARE 프리페어이름;
DEALLOCATE PREPARE 프리페어이름;
◆ 둘다 사용 가능함.
◆ Java에서 리소스 해제하는 것처럼 PREPARE쓰고 다 쓰면 없애주는게 좋음.
*/
BEGIN NOT ATOMIC
PREPARE p_query FROM "SELECT memberId, name, pwd FROM tbl_member WHERE memberId = 'test5';";
EXECUTE p_query;
END;
# USING으로 동적으로 사용해보기 ①
BEGIN NOT ATOMIC
PREPARE p_query FROM "SELECT memberId, name, pwd FROM tbl_member WHERE memberId = ?;";
EXECUTE p_query USING 'test5';
END;
# USING으로 동적으로 사용해보기 ②
BEGIN NOT ATOMIC
PREPARE p_query FROM "SELECT memberId, name, pwd FROM tbl_member LIMIT ?, ?;";
EXECUTE p_query USING 1,5;
END;
# USING으로 동적으로 사용해보기 ③
BEGIN NOT ATOMIC
SET @page_no = 2;
SET @page_size = 2;
PREPARE p_query FROM "SELECT memberId, name, pwd FROM tbl_member LIMIT ?, ?;";
EXECUTE p_query USING @page_no, @page_size;
END;
DROP PREPARE p_query;Review
- 오류처리 부분 관련해서 오류 조건 부분 좀 더 학습이 필요할 것 같습니다.
- INSERT/DELETE/UPDATE 쿼리 사용 시 데이터 복구가 어려울 수 있기 때문에 TRANSATION 사용을 습관화 하여야합니다. => 연습할 때도 가능하며 사용하여 습관화할 수 있도록 할 예정
TO DO
- Java 최종 정리(~2/25)
- Maria DB 정리 수업진도 따라잡기
블로그 이전 했습니다. 아래 블로그 아이콘(🏠) 눌러서 놀러오세요