Today
- SQL 프로그래밍
-- 2024-02-22
-- 트랜잭션 : 쿼리의 개수와 상관없이 한꺼번에 수행되어야할 연산 모음
/*
<트랜잭션의 특징> => ACID
◆ 원자성(ATOMICITY) : 트랜잭션이 전부 실행되던가 전부 실행되지 않아야한다는 특성
◆ 일관성(CONSISTENCY) : 미리 정의된 규칙에 의해서만 데이터 조작이 가능해야한다는 특성
◆ 독립성(ISOLATION) : 하나의 트랙잭션을 실행하는 중 다른 트랜잭션의 동작이 끼어들지 못하도록 하는 특성
◆ 지속성(DURABILITY) : 트랜잭션 결과과 반영되면 그 결과는 영원히 적용되어야한다는 특성
<트랜잭션 기본 형식>
SET AUTOCOMMIT = 0; => AUTOCOMMIT 설정 비활성화
START TRANSACTION; => 트랜잭션 시작
실행구문;
COMMIT; => 결과 보고 확정할 때
ROLLBACK; => 결과 보고 시작시점으로 돌릴 떄 (한번 COMMIT한 후에는 다시 못돌림
◆ MariaDB, MySQL : 기본적으로 AUTOCOMMIT이 활성화되어있음 따라서 1) DB자체의 설정에서 비활성화 해주던가 2) 쓸 떄 AUTOCOMMIT을 0으로 돌려줘야함.
*/
# 실제 해보기
# 난 test3 데이터가 없었어서 호다다다닥 추가
INSERT INTO tbl_member(memberID, NAME, pwd, jumin, regDate)
VALUES ('test3', '테스트3', '1234', '123456-1234567', NOW());
# 트랜잭션 부분
BEGIN NOT ATOMIC
SET autocommit = 0;
START TRANSACTION;
SET @FLAG = 0;
UPDATE tbl_member
SET jumin = '345678-1234567', addr1='서울 금천구 독산1동'
, birthDay = '1999-12-31', jobCode = '06', mileage = 1500, memberState = 'Y'
WHERE memberId = 'test3';
SET @FLAG = ROW_COUNT();
if @FLAG > 0 then
COMMIT;
SELECT @FLAG, '성공' AS MSG;
ELSE
ROLLBACK;
SELECT @FLAG, '실패' AS MSG;
END IF;
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 @RESULT_MSG = '{
RESULT : "ERROR"
, RESULT_CODE : 500
, RESULT_MSG : {
SQL_STATE : '@SQLSTAT'
', ERROR_NO : '@errno'
', ERROR_MSG : '@err_msg'
}
};
RETURN @RESULT_MSG;
END;
END;
# 이런식으로 오류메시지 JSON객체로 전달해서 처리한다는 예시용! 실행 XXXX
# 실제 해보기2
# 난 test4 데이터가 없었어서 호다다다닥 추가
INSERT INTO tbl_member(memberID, NAME, pwd, jumin, regDate)
VALUES ('test4', '테스트4', '1234', '123456-1234567', NOW());
# 트랜잭션 부분
SELECT * FROM tbl_member WHERE memberId = 'test4';
BEGIN NOT ATOMIC
SET autocommit = 0;
START TRANSACTION;
SET @FLAG = 0;
UPDATE tbl_member
SET jumin = '345678-1234567', addr1='서울 금천구 독산1동'
, birthDay = '1999-12-31', jobCode = '06', mileage = 1500, memberState = 'Y'
WHERE memberId = 'test3';
ROLLBACK; # STRAT 지점으로 다시 돌아감
END;
SELECT * FROM tbl_member WHERE memberId = 'test4';
# SQL오류 발생 시 ROLLBACK 해보기
BEGIN NOT ATOMIC
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT '실패' AS '결과';
END;
SET autocommit = 0;
START TRANSACTION;
UPDATE tbl_member
SET memberId = 'test3',jumin = '345678-1234567', addr1='서울 금천구 독산1동'
, birthDay = '1999-12-31', jobCode = '06', mileage = 1500, memberState = 'Y'
WHERE memberId = 'test4';
COMMIT;
END;
# 트랜잭션 내 쿼리문 여러개 실행
# 난 test5 데이터가 없었어서 호다다다닥 추가
INSERT INTO tbl_member(memberID, NAME, pwd, jumin, regDate)
VALUES ('test5', '테스트5', '1234', '123456-1234567', NOW());
# 트랜잭션 부분
DELIMITER $$
BEGIN NOT ATOMIC
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE tbl_member SET memberState = 'N', mileage = 1000 WHERE memberId = 'test4';
UPDATE tbl_member SET memberID= 'gee1', memberState = 'Y', mileage = 2500 WHERE memberId = 'test5'; # 이부분이 실패하기 떄문에 해당 트랜잭션 전체가 실행되지 않음.
END;
$$
DELIMITER ;
# 트랜잭션 내 JOIN문 사용할 경우
# 내꺼에 외래키 없어서 호다닥 추가
ALTER TABLE tbl_orderdetail
ADD CONSTRAINT FK_tbl_orderDetail_goodsCode
FOREIGN KEY (goodsCode) REFERENCES tbl_goodsinfo(goodsCode)
ON UPDATE CASCADE ON DELETE CASCADE;
# 트랜잭션 부분 (인데 뭔가 잘못된 테스트 였던것)
DELIMITER $$
BEGIN NOT ATOMIC
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE tbl_orderinfo SET orderAmount = 10000 WHERE orderNo = '202301009';
INSERT INTO tbl_orderdetail (orderNo, goodsCode, unitCode, unitPrice, orderedCnt, amount)
VALUES('202301009', 'GDS002', '04', 100, 10, 1000);
COMMIT;
END;
$$
DELIMITER ;
DELIMITER $$
BEGIN NOT ATOMIC
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE tbl_orderinfo SET orderNo = '202301010' WHERE orderNo = '202301009';
INSERT INTO tbl_orderdetail (orderNo, goodsCode, unitCode, unitPrice, orderedCnt, amount)
VALUES('202301009', 'GDS002', '04', 100, 10, 1000);
COMMIT;
END;
$$
DELIMITER ;
# 재 테스트
# 데이터 정합성 맞추려고 tbl_orderdetail 데이터 삭제해야하는데 해당 테이블 백업본 생성
CREATE TABLE `tbl_orderdetail_backup` AS (SELECT * FROM tbl_orderdetail)
;
# tbl_orderdetail 데이터 삭제
DELETE FROM tbl_orderdetail WHERE orderNo IN ('202301001','202301002','202301003','202301006');
# tbl_orderdetail 외래키 다시 설정
ALTER TABLE tbl_orderdetail
ADD CONSTRAINT FK_tbl_orderDetail_orderNo
FOREIGN KEY (orderNo) REFERENCES tbl_orderinfo (orderNo)
ON UPDATE CASCADE ON DELETE RESTRICT;
# 정핮성 맞추고 다시 테스트
DELIMITER $$
BEGIN NOT ATOMIC
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE tbl_orderinfo SET orderNo = '202301010' WHERE orderNo = '202301009'; # tbl_orderinfo에서 orderNo가 바뀌면서 tbl_orderdetail에서도 해당 orderNo 같이 변경됨 (CASCADE 설정)
# DELETE FROM tbl_orderinfo WHERE orderNo = '202301010'; # tbl_orderinfo에서 삭제하려고하는데 tbl_orderdetail에서 해당값이 외래키로 사용중이어서 삭제 안됨 (RESTRICT 설정)
COMMIT;
END;
$$
DELIMITER ;
SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;
DELIMITER $$
BEGIN NOT ATOMIC
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
SET AUTOCOMMIT = 0;
START TRANSACTION;
DELETE FROM tbl_orderdetail WHERE orderNo = '202301010'; # 자식 테이블인 tbl_orderdetail 에서 먼저 삭제 후
DELETE FROM tbl_orderinfo WHERE orderNo = '202301010'; # 부모 테이블인 tbl_orderinfo 에서 삭제할 시 외래키 제약조건에 걸리지 않기 때문에 정상 삭제됨.
COMMIT;
END;
$$
DELIMITER ;
# 선생님 TIP : OUTFILE / INFILE로 데이터 백업/복구 정말 많이 하니 꼭 알아두기
/*
[백업 : 외부파일로 내려서 백업]
SELECT 컬럼명
INTO OUTFILE '파일경로'
FIELDS TERMINATED BY '구분자' ENCLOSED BY '구분자'
FROM 테이블명;
[복구 : 외부파일 내려놓은거 읽어서 테이블에 넣기]
LOAD LOCAL INFILE '파일경로'
INTO TABLE 테이블명 (컬럼명 - 모든 컬럼이 구조&순서 동일하다면 생략)
FIELDS TERMINATED BY '구분자' ENCLOSED BY '구분자'
*/
# 선생님꺼와 데이터 맞추기용
DELETE FROM tbl_orderinfo WHERE orderNo = '202301008';
-- 지역변수 사용
/*
[사용법]
DECLARE 지역변수명 타입 [DEFAULT 값];
◆ 지역변수는 해당 코드 블럭 내에서만 사용할 수 있는 변수입니다.
◆ 해당 지역변수는 코드 블럭 내에서만 선언할 수 있으며, 선언한 코드블럭 내에서만 사용 가능합니다.
◆ DECLARE 지역변수에서는 @를 사용하지 않습니다. (선언할 때도 사용할 때도)
*/
# 사용해보기
delimiter $$
BEGIN NOT ATOMIC
DECLARE i INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
loop1 : LOOP
if i > 100 TheN leave loop1;
END if;
SET total = total + i;
SET i = i + 1;
END LOOP loop1;
SELECT total AS '합계';
END;
$$
delimiter ;
-- 실습 : WHILE / FOR로 위와 같은 결과를 만드시오.
# WHILE
delimiter $$
BEGIN NOT ATOMIC
DECLARE i INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
WHILE i <= 100 DO
set total = total + i;
set i = i + 1;
END WHILE;
SELECT total AS '합계';
END;
$$
delimiter ;
# FOR
delimiter $$
BEGIN NOT ATOMIC
DECLARE i INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
FOR i IN 1..100 DO
set total = total + i;
END FOR;
SELECT total AS '합계';
END;
$$
delimiter ;
-- CURSOR : SELECT 결과문에 대해 행으로 접근할 수 있게 해주는 역할을 함. (행에 Java의 Array처럼 접근하는 개념)
/*
[사용법]
DECLARE 커서명 CURSOR FOR 셀렉트문; => 1. 커서등록
DECLARE 진행상태 HANDLER FOR 처리상태 => 2. 커서 핸들러 등록
처리할쿼리;
OPEN 커서명; => 3. 커서 오픈(사용한다고 하는것!)
FETCH 커서명 INTO 지역변수명; => 4. 결과 가져오기
추가작업;
CLOSE 커서명; => 5. 커서 닫기
◆ 1. 커서등록
: 사용할 커서를 등록하는 부분, 순회할 셀렉트 문도 함께 입력합니다.
2. 커서핸들러 등록
: 커서가 순차적으로 동작할 때 시작점을 BOF(Begin Of File)
끝점을 EOF(End Of File) 이라고 하며 커서가 EOF를 만날 떄 FETCH가 실행되면 NOT FOUND 상태가 됩니다.
주로 해당 상태에 대해 처리할 쿼리를 작성합니다.
3. 커서 오픈
: 선언한 커서 중 사용할 커서를 OPEN 하여 사용합니다.
4. 결과 가져오기
: 커서 사용하여 INTO 로 결과를 할당할 변수는 반드시 지역변수여야합니다!
결과를 가져와서 해당 결과로 작업할 부분을 아래에 적어줍니다.
5. 커서 닫기
: 커서를 다 썼다면 꼭 해당 코드 블록 종료 전에 CLOSE 해줘야합니다.
◆ DECLARE 오류 처리문과 구조가 유사함.
DECLARE 상태 HANDLER FOR SQL상태/코드
BEGIN
오류발생 시 실행할 코드;
END;
*/
# 임시테이블 미리 만들어놓기
CREATE TEMPORARY TABLE TMP_FOR_CURSOR (
idx INT AUTO_INCREMENT PRIMARY KEY,
memId VARCHAR(20),
memName VARCHAR(20)
);
delimiter $$
BEGIN NOT ATOMIC
# 변수선언
DECLARE check_flag CHAR(1) DEFAULT 'N';
DECLARE memId VARCHAR(20);
DECLARE memName VARCHAR(20);
# 커서작업
# 1. 커서등록
DECLARE cur1 CURSOR FOR
SELECT memberId, NAME FROM tbl_member ORDER BY regDate DESC;
# 2. 커서 핸들러 등록
DECLARE CONTINUE handler FOR NOT FOUND SET check_flag = 'Y';
# 3. 커서 오픈
OPEN cur1;
loopCur1 : loop
# 4. 1에서 SQL 로 가져온 결과를 순회하면서 ROW별 데이터를 처리
FETCH cur1 INTO memId, memName;
if (check_flag) = 'Y' then
leave loopCur1;
END if;
# SELECT memId, memName;
INSERT INTO TMP_FOR_CURSOR(memId, memName) VALUES (memId, memName);
END loop loopCur1;
CLOSE cur1;
SELECT * FROM TMP_FOR_CURSOR;
END;
$$
delimiter ;
# 만들어 놓은 임시 테이블 TMP_FOR_CURSOR 업애기
DROP TABLE TMP_FOR_CURSOR;
-- 다른버전 (지현 못따라가서 그냥 내맘대로 수정!)
delimiter $$
BEGIN NOT ATOMIC
# 변수선언
DECLARE check_flag CHAR(1) DEFAULT 'N';
DECLARE memId VARCHAR(20);
DECLARE memName VARCHAR(20);
DECLARE i CHAR(4) DEFAULT '(수정)';
# 커서작업
# 1. 커서등록
DECLARE cur1 CURSOR FOR
SELECT memberId, NAME FROM tbl_member ORDER BY regDate DESC;
# 2. 커서 핸들러 등록
DECLARE CONTINUE handler FOR NOT FOUND SET check_flag = 'Y';
# 3. 커서 오픈
OPEN cur1;
loopCur1 : loop
# 4. 1에서 SQL 로 가져온 결과를 순회하면서 ROW별 데이터를 처리
FETCH cur1 INTO memId, memName;
if (check_flag) = 'Y' then
leave loopCur1;
END if;
IF memId IN (SELECT A.memId FROM TMP_FOR_CURSOR AS A) then # 선생님을 이부분을 Cnt변수에 개수로 체크해서 담으셨으나 나는 있냐여부를 IF 조건절안에 넣음.
UPDATE TMP_FOR_CURSOR AS A SET A.memName = CONCAT(memName, ' ', i) WHERE A.memId = memId;
ELSE
INSERT INTO TMP_FOR_CURSOR(memId, memName) VALUES (memId, memName);
END if;
END loop loopCur1;
CLOSE cur1;
SELECT * FROM TMP_FOR_CURSOR;
END;
$$
delimiter ;
# 나중에 받은 선생님 코드!
DELIMITER $$
BEGIN NOT ATOMIC
#변수선언
DECLARE check_flag INT DEFAULT FALSE;
DECLARE tmemId VARCHAR(20);
DECLARE tmemName VARCHAR(20);
DECLARE i INT DEFAULT 1;
#커서작업
#1. 커서이름 선언/할당
DECLARE cur1 CURSOR FOR
SELECT `memberId`, `NAME` FROM `tbl_member` ORDER BY `regDate` DESC;
#2. 커서 핸들러 등록
DECLARE CONTINUE HANDLER FOR NOT FOUND SET check_flag = TRUE;
#3. 커서 오픈
OPEN cur1;
loopCur1:LOOP
#4. 1에서 SQL 로 가져온 결과를 순회하면서 row 별 데이터 처리
FETCH cur1 INTO tmemId, tmemName;
IF check_flag THEN
LEAVE loopCur1;
END IF;
SELECT @cnt:=COUNT(*) FROM TMP_FOR_CURSOR WHERE `memId` = tmemId;
IF (@cnt) > 0 THEN
UPDATE TMP_FOR_CURSOR SET `memName` = CONCAT(`memName`, i) WHERE `memId` = tmemId;
ELSE
INSERT INTO TMP_FOR_CURSOR(`memId`, `memName`) VALUES(tmemId, tmemName);
END IF;
SET i = i + 1;
END LOOP loopCur1;
#5. 종료시점에 커서close
CLOSE cur1;
END;
$$
SELECT * FROM TMP_FOR_CURSOR;
-- 저장 프로시저
/*
[사용법]
1. 저장 프로시저 생성법
DELIMITER $$
CREATE PROCEDURE 프로시저명 (파라미터,,,)
BEGIN
실행구문;
END;
##
DELIMITER ;
◆ 파라미터의 종류는 IN, OUT이 있습니다.
- IN : 해당 프로시저를 실행할때 내부에서 사용할 용도로 받는 파라미터 (기본적인 파라미터 느낌)
EX) IN 파라미터명 타입
- OUT : 해당 프로시저 실행을 통해 나온 결과등을 리턴해주는 파라미터 (리턴값과 유사)
EX) OUT 파리미터명 타입
◆ DELIMITER ~ $$는 사실 기본구조는 아니지만 습관적으로 저거까지 적어주는게 좋습니다.
◆ 보통은 프로시저명은 '사용자가 만든 프로시저(시스템 프로시저랑 구분하기 위해)'라는 의미에서 USP_업무명_기능명 이런식으로 만듭니다.
◆ 저장 프로시저를 생성할 때 캐시에 저장되어 나중에 호출해서 사용할 때 그냥 쿼리문 직접 작성하는 것보다 속도가 빠릅니다.
2. 저장 프로시저 호출법
CALL 프로시저명(인자,,,);
◆ OUT 파라미터를 갖는 프로시저의 경우 @변수 등을 이용해서 받아서 이용할 수 있습니다.
EX) CREATE PROCEDURE USP_TEST(
IN 파라미터명1 INT,
IN 파라미터명2 INT,
OUT 파라미터명3 VARCHAR(20)
) ~~~;
CALL USP_TEST(인자1, 인자2, @담을변수);
*/
# 저장 프로시저 만들어보기1
DELIMITER $$
CREATE PROCEDURE USP_ORDER_ORDERINFO_LIST()
BEGIN
SELECT
MB.memberId, MB.name
, OI.orderNo, OI.orderDate
, OD.orderedCnt, OD.amount
, GI.goodsCode, GI.goodsName, GI.state
FROM tbl_orderinfo AS OI
INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
INNER JOIN tbl_goodsinfo AS GI ON GI.goodsCode = OD.goodsCode
INNER JOIN tbl_member AS MB ON MB.memberId = OI.memberId
ORDER BY OI.orderDate DESC;
END;
$$
DELIMITER ;
# 저장 프로시저 만들어보기2 : 파라미터 있는거
DROP PROCEDURE if EXISTS USP_ORDER_ORDERINFO_LIST;
DELIMITER $$
CREATE PROCEDURE if NOT EXISTS USP_ORDER_ORDERINFO_LIST(
IN pOderNo CHAR(9) COLLATE 'utf8mb4_general_ci' # 지현 MariaDB character_set_system 설정이 utf8mb3 여서...강제로 변경해주는 부분...또륵
, IN pPageNo INT
, IN pPageSize INT
)
BEGIN
DECLARE PAGE_NO INT DEFAULT 1;
DECLARE PAGE_SIZE INT DEFAULT 1;
SET PAGE_NO = pPageNo * pPageSize;
SET PAGE_SIZE = pPageNO + pPageSize;
SELECT
MB.memberId, MB.name
, OI.orderNo, OI.orderDate
, OD.orderedCnt, OD.amount
, GI.goodsCode, GI.goodsName, GI.state
FROM tbl_orderinfo AS OI
INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
INNER JOIN tbl_goodsinfo AS GI ON GI.goodsCode = OD.goodsCode
INNER JOIN tbl_member AS MB ON MB.memberId = OI.memberId
WHERE OI.orderNo = pOderNo
ORDER BY OI.orderDate DESC
# LIMIT PAGE_NO, PAGE_SIZE
;
END;
$$
DELIMITER ;
# 저장 프로시저 호출해보기
CALL USP_ORDER_ORDERINFO_LIST('202301005', 1,1);
# 설정 바꾸려했던 노력들...memberId 다실패...
SELECT maria.collation_database;
SELECT collation_server;
show variables like 'c%';
SET character_set_system = 'utf8mb4';
SET collation_database = 'utf8mb4_general_ci';
COMMIT;
ALTER DATABASE maria
DEFAULT CHARACTER SET UTF8 COLLATE UTF8MB4_GENERAL_CI;
# 저장 프로시저 만들어보기3 : OUT 파라미터도 써보기
DROP PROCEDURE if EXISTS USP_MEMBER_NAME_BY_MEMBERID;
DELIMITER $$
CREATE PROCEDURE if NOT EXISTS USP_MEMBER_NAME_BY_MEMBERID(
IN pMemberId CHAR(9) COLLATE 'utf8mb4_general_ci' # 지현 MariaDB character_set_system 설정이 utf8mb3 여서...강제로 변경해주는 부분...또륵
, OUT oMemberId CHAR(9) COLLATE 'utf8mb4_general_ci' # 지현 MariaDB character_set_system 설정이 utf8mb3 여서...강제로 변경해주는 부분...또륵
)
BEGIN
SELECT NAME INTO oMemberId
FROM tbl_member
WHERE memberId = pMemberId
ORDER BY regDate desc
LIMIT 1
# LIMIT PAGE_NO, PAGE_SIZE
;
END;
$$
DELIMITER ;
# 실행해보기
BEGIN NOT ATOMIC
CALL USP_MEMBER_NAME_BY_MEMBERID('gee1', @OUT_MEMBER_NAME);
IF @OUT_MEMBER_NAME != '장지현' then
SELECT '회원정보가 올바르지 않습니다.' AS '결과';
ELSE SELECT @OUT_MEMBER_NAME AS '결과';
END if;
END;
-- 선생님 TIP
/*
<데이터베이스>
CREATE DATABASE 데이터베이스명 내용~;
ALTER DATABASE 데이터베이스명 내용~;
DROP DATABASE 데이터베이스명;
<테이블>
CREATE TABLE 테이블명 내용~;
ALTER TABLE 테이블명 내용~;
DROP TABLE 테이블명;
<프로시저>
CREATE PROCEDURE 프로시저명 내용~;
ALTER PROCEDURE 프로시저명 내용~;
DROP PROCEDURE 프로시저명;
◆ IF EXISTS 로 존재하면 만들거나, 수정하거나, 삭제하는 조건 추가 가능
CREATE PROCEDURE IF EXISTS 프로시저명 내용~~;
◆ OR REPLACE 로 없으면 생성, 있으면 덥어쓰기 할 수 있습니다.
CREATE OR REPLACE PROCEDURE 프로시저명 내용~~~; => 이 방법은 매우 위험함... 막 남의 테이블/프로시저 등 덮어써버리고....
◆ DDL 구문의 특징 '기능구문 + 대상 + 이름' 의 구조로 구성되어있습니다..
*/
-- 저장 프로시저 수정
/*
ALTER PROCEDURE 프로시저명
BEGIN
실행구문;
END;
◆ ???? 내용 필요...
*/
-- 저장 프로시저 정보 확인
# 사용자 변수조회
# 방법 ①
SELECT * FROM information_schema.user_variables; # 또는 직접 information_schema DB를 사용해서 조회
# 방법 ②
SHOW VARIABLES;
# 프로시저 정보 조회
# 방법 ①
SELECT * FROM information_schema.ROUTINES # 프로시저, 함수 등등 있는 테이블 조회 (시스템, 사용자 정의 다 있음)
WHERE ROUTINE_SCHEMA = 'maria' && ROUTINE_TYPE = 'PROCEDURE' # 사용처가 maria 이고 타입이 프로시저인 건만 조회
;
# 방법 ②
SHOW PROCEDURE STATUS # 프로시저 조회 (시스템, 사용자 정의 다 있음)
WHERE Db = 'maria' # 사용처가 maria 이고 타입인 것만 갖고오기
;
-- 실습..
SELECT * FROM tbl_member;
/*
1. 회원정보를 입력하고, 결과를 리턴받는 프로시저를 작성하시오.
- 테이블 : tbl_member
- 입력 컬럼 : memberId, name, pwd
- 리턴값 : oResult
*/
# 지현 답
DROP PROCEDURE if EXISTS USP_MEMBER_NAME;
DELIMITER $$
CREATE PROCEDURE USP_MEMBER_NAME(
IN pMemberId VARCHAR(20) COLLATE 'utf8mb4_general_ci'
, IN pName VARCHAR(20) COLLATE 'utf8mb4_general_ci'
, IN pPwd VARCHAR(300) COLLATE 'utf8mb4_general_ci'
, OUT oResult VARCHAR(20) COLLATE 'utf8mb4_general_ci'
, OUT oResultYN CHAR(1) COLLATE 'utf8mb4_general_ci'
)
BEGIN
DECLARE err INT default 0;
DECLARE continue HANDLER FOR SQLEXCEPTION SET err = 1;
SET AUTOCOMMIT = 0;
START TRANSACTION;
INSERT INTO tbl_member(memberId, NAME, pwd)
VALUES (pMemberId, pName, pPwd);
if err != 1 then
COMMIT;
SET oResultYN = 'Y';
SELECT NAME INTO oResult FROM tbl_member
WHERE
pMemberID = memberId && pName = NAME && pPwd = pwd
;
ELSE
ROLLBACK;
SET oResultYN = 'N';
SET oResult = '-';
END if;
END;
$$
DELIMITER ;
BEGIN NOT ATOMIC
CALL USP_MEMBER_NAME('gee8', '지현8', '1234', @OutName, @OutResult);
SELECT @OutName, (CASE @OutResult WHEN 'Y' THEN '성공' WHEN 'N' THEN '실패' END) AS '결과';
END;Review
- MariaDB character_set_system 설정이
utf8mb3으로 설정되어있어서, 관련 오류가 계속 발생하여 현재 해당하는 부분 수기로 'utf8mb4_general_ci' 설정해서 사용중
=> 삭제 후 재설치 말고...다른 방법이 없나 찾는중.......😰😵😱
=> 24/02/23 추가 : 해결-
파악한 문제 원인
: collation_database 가 'utf8mb4_unicode_ci'로 설정되어있어 해당 프로시저들이 전부 'utf8mb4_unicode_ci'로 설정되는 상태에서 인자로 던져진 값들은 'utf8mb4_general_ci'로 받게되어 충돌 발생 -
해결방법
# 현재 나의 캐릭터셋 확인하기 SHOW VARIABLES LIKE 'c%'; # 문제되는 캐릭터 셋 변경하기 SET collation_database = 'utf8mb4_general_ci'; COMMIT; # 추가로 문제되던 프로시저들 전부 DROP 후 재생성 # 해결완료!
-
- ALTER PROCEDURE 부분 제대로 진행하지 못함...ㅠ 혼자 알아볼 필요가 있음
- Java와 MariaDB 정리 필요한 부분 우선순위 정리가 필요할듯...
TO DO
- Java 최종 정리(~2/25) => 할수...있을까..?
- Maria DB 정리 수업진도 따라잡기
정리하고 있는 부분 : 232p / 진도 : 425p
블로그 이전 했습니다. 아래 블로그 아이콘(🏠) 눌러서 놀러오세요