SQL 트랜잭션 및 주요 내장 함수

⏰ 2024.11.01 (D+17)

1. 트랜잭션

🔖 중요]
- 일련의 작업 단위를 처리하기 위한 거를 트랜잭션 이라 한다.
- 일련의 작업에서 하나의 작업이라도 실패한다면 모든 작업을 취소 시킨다 (ROLLBACK 한다고 한다)
- 일련의 작업이 정상적으로 끝나면 COMMIT한다

💡 오라클의 트랜잭션 제어 명령어

COMMIT
모든 미 결정 데이타를 영구적으로 변경함으로써 현재 TRANSATION을 종료

ROLLBACK [TO SAVEPOINT name]
모든 미 결정 데이터 변경을 원래대로 돌림으로써 현재의 TRANSATION 종료

SAVEPOINT
TRANSATION내의 SAVEPOINT표시/ANSI 표준 SQL이 아님.

⚙️ COMMIT 및 ROLLBACK이 일어나는 경우

자동 COMMIT일 얼어나는 경우
1) DDL/DCL문장 완료시
2) SQL*PLUS 정상 종료시

자동 ROLLBACK이 일어나는 경우
SQL*PLUS 비정상 종료시 혹은 시스템 실패시

예시 📖]

SET SERVEROUTPUT ON

BEGIN
➖일련의 작업 3개를 하나의 업무로 묶은 것

➖수정 작업
UPDATE BBS SET TITLE='TITLE' WHERE NO=6;

➖삭제 작업
DELETE FROM BBS WHERE NO=5;

➖입력 작업:에러 발생
INSERT INTO BBS VALUES(SEQ_BBS.NEXTVAL,'TITLE','KIM',DEFAULT);
--INSERT INTO BBS VALUES(SEQ_BBS.NEXTVAL,'ABCDEFGHIJKLMNOP','KIM',DEFAULT);
/주석 실행결과:
0개 행 이(가) 업데이트되었습니다.
모든 일련의 작업이 취소되었어요
/
COMMIT;
DBMS_OUTPUT.PUT_LINE('모든 일련의 작업(수정→삭제→입력) 즉 트렌잭션이 성공했어요');

➖예외처리부
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;--작업 취소
DBMS_OUTPUT.PUT_LINE('모든 일련의 작업이 취소되었어요');

END;
/

2. 주요 내장 함수

⭐ NVL(컬럼명,NULL인 경우 대체할 값)

예시 📖] NULL인 값을 -1로 표시하도록 출력

SELECT
EMPNO,
ENAME,
COMM,
NVL(COMM,-1) NVL
FROM EMP;

⭐ NVL2(컬럼명,"대체값 1","대체값 2")

대체값 1은 NULL이 아닌 경우,대체값 2는 NULL인 경우의 대체 값이다

📖 예시 1. ]

SELECT
EMPNO,
ENAME,
COMM,
NVL2(NULL,'널이 아님',1004) NVL
FROM EMP;

📖 예시 2. ]

SELECT
EMPNO,
ENAME,
COMM,
NVL2(COMM,COMM+100,-1)
NVL
FROM EMP;

⭐ LOWER('문자열')

영문자를 소문자로 변환
SELECT LOWER('ORACLE') FROM DUAL +>oracle

SELECT
EMPNO,
ENAME,
LOWER(ENAME) 소문자
FROM EMP;

⭐ UPPER('문자열')

영문자를 대문자로 변환
SELECT UPPER('oracle') FROM DUAL +>ORACLE

SELECT
EMPNO,
ENAME,
UPPER(ename) 대문자
FROM EMP;

⭐ INITCAP('문자열')

첫 영문자를 대문자로 변환
SELECT INITCAP('oracle') FROM DUAL +>Oracle

SELECT
EMPNO,
ENAME,
INITCAP(ENAME),
JOB,
INITCAP(JOB)
FROM EMP;

⭐ CONCAT('문자열','문자열')

문자열 연결 또는 ||
SELECT CONCAT('ORACLE','JAVA') FROM DUAL +>ORACLEJAVA

⭐ LENGTH()

문자열 길이
SELECT LENGTH('오라클') FROM DUAL; +>3

SELECT
LENGTH('안녕')
FROM DUAL;

⭐ lengthb()

문자열 길이를 바이트로(한글이 3바이트 차지)
SELECT LENGTHB('오라클') FROM DUAL; +>9

SELECT
lengthb('HELLO')
FROM DUAL;

⭐ LPAD('문자열',전체 자리수,'채울 문자열')

좌측을 지정한 값으로 채운다
SELECT LPAD('HELLO',10,'X') FROM DUAL+>XXXXXHELLO
혹은
SELECT LPAD('9',2,'0') FROM DUAL +>09

SELECT LPAD(12345,2,'0')FROM DUAL;

⭐ RPAD('문자열',전체 자리수,'채울 문자열')

우측을 지정한 값으로 채운다
SELECT LPAD('HELLO',10,'X') FROM DUAL+>HELLOXXXXX
혹은
SELECT LPAD('9',2,'0') FROM DUAL +>09

SELECT RPAD('HELLO',7,'0')FROM DUAL;

⭐ INSTR('문자열','찾을 문자열')

찾은 문자열의 인덱스 반환.인덱스는 1부터 시작
SELECT INSTR('ABCDEFG','DE') FROM DUAL +>4
(1) SELECT FROM DEPT WHERE instr(LOWER(DNAME),'e') !=0 는
(2) SELECT FROM DEPT WHERE LOWER(DNAME) like '%e%'와 결과가 같으나 쿼리 속도면에서 (1)이 훨씬 성능이 우수하다.

SELECT INSTR('HELLO','HE') FROM DUAL;
SELECT INSTR('HELLO','L') FROM DUAL;
SELECT INSTR('HELLO','ㅣ') FROM DUAL; --대소문자를 확인하여 해당 값이 없는 경우 0으로 반환
SELECT INSTR('HELLO','X') FROM DUAL; --문자열이 포함이 안된 경우 0으로 반환

응용 예시 📖]
1)
SELECT
ENAME
FROM EMP
WHERE UPPER(ENAME) LIKE 'S%';

2)
SELECT
ENAME
FROM EMP
WHERE INSTR(UPPER(ENAME),'S')=1;

❗ 1)와 2)의 결과값이 동일하게 출력된다.

⭐ SUBSTR('문자열',시작인덱스,길이)

문자열에서 시작인덱스부터 길이 만큼 가져옴, 인덱스는 1부터 시작
SELECT SUBSTR('123456789',3,3) FROM DUAL;+>345

SELECT
SUBSTR('HELLP',4,2)
FROM EMP;

SELECT
ENAME,RPAD(SUBSTR(ENAME,1,1),LENGTH(ENAME),'*') 익명
FROM EMP;

⭐ REPLACE('문자열','바꿀 문자열','바뀔 문자열')

특정 문자열을 다른 문자열로 대체
SELECT REPLACE('HELLO WORLD','HELLO','JAVA') FROM DUAL +>JAVA WORLD

SELECT
ENAME,
JOB,
REPLACE(JOB,'SALEMAN'),

⭐ TO_CHAR()

TO_CHAR(숫자 혹은 날짜)
TO_CHAR(숫자 혹은 날짜,숫자형식 포맷 문자열 혹은 날짜형식 포맷 문자열)

SELECT TO_CHAR(1004)+100 FROM DUAL;

※ TO_CHAR(숫자,'숫자 포맷 문자열')주의점
➖ 9는 값이 있으면 표시 ,없으면 표시 안함
SELECT TO_CHAR(1004,'9999')FROM DUAL;
SELECT TO_CHAR(1004,'99999')FROM DUAL;

➖0는 값이 있으면 표시, 없으면 0으로 표시
SELECT TO_CHAR(9,'09')FROM DUAL;

➖또한 소수점은 실제값의 자리수가 많으면 나머지는 짤림.
SELECT TO_CHAR(1004,'9999,99')FROM DUAL;
SELECT TO_CHAR(1004,'$9,999')FROM DUAL;
SELECT TO_CHAR(3.144678,'99.99')FROM DUAL;

➖단, 정수인 경우는 실제값의 자리수가 많으면 값이 #으로 표시됨
SELECT TO_CHAR(3456.144678,'09.99')FROM DUAL;

⭐ TO_DATE()

문자형을 날짜형으로 변환
yy : 년도 2자리만
yyyy : 년도 4자리
mm : 01~12형태의 월
d : 요일 반환(일요일은 1,월요일 2)
dd : 1~31형태의 일 표시
ddd : 해당 년도의 1월 1일부터 현재 까지의 일수
HH : 1~12시 형태로 표시
HH12 : 1~12시 형태로 표시
HH24 : 0~23시
MI : 0~59분
SS : 0~59초

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD')FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YY-MM-DD D')FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YY-MM-DD DDD')FROM DUAL;
SELECT TO_CHAR(SYSDATE,'AM')FROM DUAL;
SELECT TO_CHAR(SYSDATE,'SS')FROM DUAL;

⭐ TO_NUMBER()

문자형을 숫자형으로 변환
SELECT TO_NUMBER('123') + TO_NUMBER('456') FROM DUAL +>579
혹은
SELECT '123' + '456' FROM DUAL

SELECT TO_NUMBER('123')+10 FROM DUAL;

⭐ TRIM()

양쪽 공백 제거

SELECT TO_CHAR(1044,'L9,999')FROM DUAL;
SELECT TRIM(TO_CHAR(1044,'L9,999'))FROM DUAL;--TRIM으로 공백제거

응용 예시 📖]
SELECT
ENAME,
SAL,
TRIM(TO_CHAR(SAL,'99,999.99')),
HIREDATE,
TO_CHAR(HIREDATE,'YYYY-MM-DD')
FROM EMP;

⭐ 수학 관련

ROUND() : 반올림
FLOOR() : 소수자리 버림
CEIL() : 올림
MOD() : 나머지
SELECT MOD(5,2) FROM DUAL+>1
POWER(2,3) : 지수곱
SQRT(10) : 제곱근

수학 관련 예시 📖]
ROUND() : 반올림
SELECT FLOOR(3.4) FROM DUAL;

POWER(2,3) : 지수곱
SELECT POWER(2,3) FROM DUAL;

⭐ DECODE()

첫번째 매개변수의 값에 따라 결과를 표시하는 함수 (switch와 같다)
DECODE(표현식,값1,결과값1,값2,결과값2,..값N,결과값N,기본값)
표현식이 값1일때 결과값1이 반환됨 값2일때는 결과값2
표현식이 아무값도 해당되지 않을때 기본값 반환

DECODE() == switch
SELECT DECODE(MOD(10,3),0,'나머지가 0',1,'나머지 1',2,'나머지 2','몰라') FROM DUAL;
SELECT DECODE(MOD(12.5,3),0,'나머지가 0',1,'나머지 1',2,'나머지 2','몰라') FROM DUAL;
SELECT MOD(12.5,3) FROM DUAL;//0.5
SELECT 12.5/4 FROM DUAL;

응용 예시 📖]
SELECT
JOB,
DECODE(JOB,'CLERK','점원','SALESMAN','영업사원','몰라') 한글명
FROM EMP;

※CASE WHEN 절은 다양한 조건식 가능 📖]

SELECT
ENAME,
JOB,
CASE JOB WHEN 'CLERK' THEN '점원'
WHEN 'SALESMAN' THEN '영업사원'
ELSE '몰라'
END 한글명,
SAL,
CASE WHEN SAL >= 3000 THEN '고액 연봉자'
WHEN SAL >= 2000 THEN '중간 연봉자'
ELSE '저액 연봉자'
END 연봉수준
FROM EMP;

📙문제 35]
LAST NAME의 ‘ar’이 포함된 직원(LIKE연산자 말고 함수 이용)의 정보를 출력하는데 phone number는 앞에서 3자리만 보여주고 나머지는 ‘X’로 채우고 입사일는 YYYY-MM-DD 형식으로 출력하여라.

SELECT
LAST_NAME,
FIRST_NAME || LAST_NAME 이름,
RPAD(SUBSTR(PHONE_NUMBER,1,3),LENGTH(PHONE_NUMBER),'X') 전화번호,
TO_CHAR(HIRE_DATE,'YYYY-MM-DD') 입사일
FROM EMPLOYEES
WHERE INSTR(UPPER(LAST_NAME),'AR') !=0;

📙문제 36]
LAST_NAME이 t(소문자-LIKE연산자 불가(함수이용))로 끝나는 직원의 정보를 출력하되 출력할 컬럼은 LAST_NAME, EMAIL(EMAIL은 첫자만 보여주고 나머지는 *처리)
SALARY는 (10000이상이면 고액 연봉,5000이상이면 중간,그외는 보통)의 정보를 출력하고 컬럼명은 등급으로 별칭하고 SALARY는 앞에 $를 붙이고 3자리마다 ,(콤마)붙여서 출력하고 HIRE_DATE는 2013년10월12일 형식으로 출력하여라.

[출력예시]
LAST_NAME | EMAIL | 등급 | SALARY | HIRE_DATE Robert | R | 고액연봉 | $10,000 | 2013년 12월 12일

SELECT
LAST_NAME 이름,
RPAD(SUBSTR(EMAIL,1,1),LENGTH(EMAIL),'*') 이메일,
TO_CHAR(SALARY,'$999,999') SALARY,
CASE WHEN SALARY >= 10000 THEN '고액연봉'
WHEN SALARY >= 5000 THEN '중간연봉'
ELSE '보통연봉'
END 등급,
TO_CHAR(HIRE_DATE,'YYYY"년 "MM"월 "DD"일"') HIRE_DATE
FROM EMPLOYEES
--WHERE INSTR(substr(LAST_NAME, LENGTH(LAST_NAME), 1), 't') != 0;
WHERE SUBSTR(UPPER(LAST_NAME), LENGTH(LAST_NAME),1) = 'T';