[SQL]SQL -Day. 5

트랜잭션

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

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

COMMIT : 모든 미결정 데이타를 영구적으로 변경함으로써 현재 TRANSATION을 종료
ROLLBACK [TO SAVEPOINT name] : 모든 미결정 데이터 변경을 원래대로 돌림으로써 현재의 TRANSATION종료
SAVEPOINT : TRANSATION내의 SAVEPOINT표시/ANSI 표준 SQL이 아님.

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

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

▲ 수정,삭제,입력작업중 하나라도 에러가 발생하면 예외처리부로 들어가서 ROLLBACK 즉, 성공했던 작업들까지 취소해서 다시 원상복구 시키게 된다. 수정,삭제,입력 작업 모두 성공해야 COMMIT이 되는 것이다.

주요 내장 함수

: ORACLE에서의 주요내장함수들은 무조건 반환값이 존재한다.


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

- LOWER('문자열') : 영문자를 소문자로 변환
예) SELECT LOWER('ORACLE') FROM DUAL =>oracle

- UPPER('문자열') : 영문자를 대문자로 변환
예) SELECT UPPER('oracle') FROM DUAL =>ORACLE

- INITCAP('문자열') : 첫 영문자를 대문자로 변환
예) SELECT INITCAP('oracle') FROM DUAL =>Oracle

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

▲CONCAT()은 인자를 두개까지만 받을 수 있으므로 위 코드는 문법에 어긋난다.

▲위 두 코드는 동일한 내용의 코드이다. CONCAT()하나를 마치 하나의 인자처럼 사용해 여러개를 사용할 수 있다.






- LENGTH() : 문자열 길이
예)SELECT LENGTH('오라클') FROM DUAL; =>3
- LENGTHB() : 문자열 길이를 바이트로
예)SELECT LENGTHB('오라클') FROM DUAL; =>9

- LPAD('문자열',전체 자리수,'채울 문자열') : 좌측을 지정한 값으로 채운다
예) SELECT LPAD('HELLO',10,'X') FROM DUAL =>XXXXXHELLO
혹은
SELECT LPAD('9',2,'0') FROM DUAL =>09

- RPAD('문자열',전체 자리수,'채울 문자열') : 우측을 지정한 값으로 채운다
예) SELECT RPAD('HELLO',10,'X') FROM DUAL =>HELLOXXXXX
혹은
SELECT RPAD('9',2,'0') FROM DUAL =>90

- INSTR('문자열','찾을 문자열') : 찾은 문자열의 인덱스 반환.
인덱스는 1부터 시작 (없으면 0반환)
예) 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)이 훨씬 성능이 우수하다.

▲ 위 두 코드모두 이름에 k가 들어간 사람을 찾는 코드이지만 INSTR()을 사용하는 쪽이 처리 속도가 빠르다.

▲ 위 두 코드 역시 이름이 S로 시작하는 사람을 찾는 동일한 코드이다.

▲이름이 S로 끝나는 사람을 찾는 코드






- SUBSTR('문자열',시작인덱스,길이) : 문자열에서 시작인덱스부터 길이 만큼 가져옴, 인덱스는 1부터 시작

- 마치 JAVA의 subString 같은 함수
예) SELECT SUBSTR('123456789',3,3) FROM DUAL; =>345

- REPLACE('문자열','바꿀 문자열','바뀔 문자열') : 특정 문자열을 다른 문자열로 대체, 바뀔 문자열이 없으면 원본 출력
예)SELECT REPLACE('HELLO WORLD','HELLO','JAVA') FROM DUAL =>JAVA WORLD

- TO_CHAR() : 자바의 toString()과 동일, 괄호안의 내용을 문자열형식으로 바꾸는 함수
TO_CHAR(숫자 혹은 날짜)
TO_CHAR(숫자 혹은 날짜,숫자형식 포맷 문자열 혹은 날짜형식 포맷 문자열)

-숫자형을 문자형으로 변환

▲ 숫자끼리 연결하면 더하기가 아닌 문자열 이어붙이기 처럼 작동한다.

▲ 숫자끼리 연결하면 더하기가 아닌 문자열 이어붙이기 처럼 작동한다.

▲ oracle에서는 숫자를 작은 따옴표로 감싸도 숫자로 인식한다.



※ TO_CHAR(숫자,'숫자포맷문자열')
9는 값이 있으면 표시 ,없으면 공백으로 표시
0는 값이 있으면 표시, 없으면 0으로 표시
단,소수점은 9든 0이든 값이 없으면 모두 0으로 표시됨
또한 소수점은 실제값의 자리수가 많으면 반올림처리됨
단, 정수인 경우는 실제값의 자리수가 많으면 값이 #으로 표시됨

- 날짜형을 문자 형으로 변환
예) SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM DUAL
※ 'yyyy"월"mm"년"dd"일" : 한글로 년월일 표시시 포맷형식에 ""로 한글을 감싼다.
예) SELECT TO_CHAR(SYSDATE,'YYYY"년"MM"월"DD"일" HH"시"MI"분"SS"초"') 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초
AM	오전
PM	오후
DY	요일명만 출력(ex. 목)
DAY	요일명+요일까지 출력(ex. 목요일)

▲ 위 코드는 에러가 난다. '2022-03-31'은 DATE형이 아닌 문자열 형태이기 때문이다. 위 코드를 정상실행하기 위해서는 해당 날짜를 아래와 같이 DATE형으로 바꿔주어야 한다.

▲년,월,일,시,분,초 등을 같이 출력하고 싶으면 "년" 처럼 큰 따옴표로 감싸주어야 한다.

▲ TRIM은 양쪽끝의 공백만 제거한다. 문자열 내부 공백은 제거하지 않는다.

[수학관련]

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





- DECODE() : 첫번째 매개변수의 값에 따라 결과를 표시하는 함수, 자바의 SWITCH함수같은 것

[형식]
DECODE(표현식, 값1, 결과값1, 값2, 결과값2, ..값N, 결과값N, 기본값)

표현식이 값1일때 결과값1이 반환됨 값2일때는 결과값2
표현식이 아무값도 해당되지 않을때 기본값 반환
DECODE()는 값1 = 결과값1 즉 = 연산만 가능

▲ 입력받은 숫자를 3으로 나누었을 때 나머지가 0이면 '나머지가0'반환, 나머지가 1이면 '나머지가1'반환, 나머지가 2이면 '나머지가 2'를 반환한다.

- CASE WHEN

1] 다양한 조건을 줄때 CASE WHEN 조건1 THEN 결과값1 WHEN 조건2 THEN 결과값2 .................. WHEN 조건N THEN 결과값N ELSE 그외값 END [컬럼별칭] 2] DECODE()함수처럼 쓰려면 -범위가 아닌 특정 값으로 분기할때는 (자바의 SWITCH문) CASE와 WHEN 사이에 표현식 생략 불가 CASE 표현식 WHEN 조건1 THEN 결과값1 WHEN 조건2 THEN 결과값2 .................. WHEN 조건N THEN 결과값N ELSE 그외값 END [컬럼별칭]

▲ 위 두 코드는 동일한 내용의 코드이다. CASE WHEN절 사용시 CASE와 WHEN 사이에 표현식을 사용하면 DECODE()함수와 동일한 기능을 하게 된다.

UPPER(JOB)이 MANAGER이면 '관리자', CLERD'이면 '점원', 나머지 직책이면 '기타' 를 반환하며 JOB2로 별칭하였다.

▲ CASE와 WHEN사이를 비움으로써 IF절을 사용한 듯한 효과가 생긴다.

[CURSOR]
- SEELCT 문장에 의해 여러행이 RETURN되는 경우 각 행에 접근하기 위한 것

CURSOR 선언] CURSOR 커서명 IS SELECT문장 -------DECLARE부에서 한다 (INTO절이 없는 SELECT문) OPEN CURSOR] -질의를 수행하라는 의미 즉 OPEN시에 CURSOR 선언시의 SELECT문장이 실행되어 결과 셋(ResultSet)을 얻게 된다 CURSOR는 그 결과셋의 첫번째 행에 바로 위에 위치하게 된다. OPEN 커서명; => 위의 SELECT문장을 실행시키는 것 FETCH ~ INTO ~] -결과셋(RESULT SET)에서 하나의 행을 읽어 들이는 작업 , 결과 셋에서 인출(FETCH)후에 CURSOR는 다음 행으로 이동 FETCH 커서명 INTO {varaiable1[,variable2,....]}; =>커서움직인다. CURSOR 닫기] -결과셋의 자원을 반납.SELECT 문장이 다 처리된후 CURSOR를 CLOSE CLOSE 커서명; CURSOR의 속성] 커서명%FOUND : 가장 최근의 인출(FETCH)이 행을 RETURN하면 TRUE아니면 FALSE; 커서명%ROWCOUNT : 지금까지 RETURN된 행의 수

▲OPEN mycursor를 통해 커서를 오픈하면 select절이 실행된다. 즉, 조건에 맞는 컬럼들로 테이블을 만들게 되며 이후 FETCH mycursor INTO 첫번째 레코드를 가져오게 되고 이를 각각의 변수에 순서대로 저장하게 된다. 이를통해 mycursor%FOUND가 true가 되어 while문 안으로 들어가게 된다. 그럼 우리가 FETCH mycursor INTO에서 각각의 변수에 저장되어 있던 값들을 출력하게 되며 while문 마지막행에서 다시한번 FETCH INTO하므로써 그 다음 레코드를 가져오고 mycursor%FOUND가 다시 true가 된다. 만약 모든 데이터가 FETCH INTO되어 더 이상 가져올 것이 없을 경우에는 mycursor%FOUND가 false가 되므로 while문이 실행되지 않는다.






[SUBPROGRAM]

- PL/SQL에는 (스토어드)프로시저 와 함수라는 두가지 유형의 SUBPROGRAM이 있다
- SELECT구문 뿐만 아니라 기타 DML문등을 이용하거나 혹은 프로그래밍적인 요소등을 사용하여 처리하기 복잡한 여러가지 데이터베이스 작업등을 처리 할 수 있도록 만들어진 데이터베이스 객체이다


1) Function]
- 사용자가 PL/SQL구문을 사용하여 직접 오라클 에서 제공하는 내장함수와 같은 기능을 정의 한 것
- 함수는 IN 파라미터만 사용할 수 있으며, 반드시 반환될 값의 데이터 타입을 RETURN문에 선언해야 하며 단일값만 반환 된다.

-함수 구문] CREATE [OR REPLACE] FUNCTION 함수명 --함수는 Alter가 없고 REPLACE를 이용해 수정한다. [( 매개변수1 IN 자료형, --IN 생략 가능 매개변수2 IN 자료형 --자료형 정의시 자리수 지정 안함 )] --매개변수구문은 필수는 아니다. RETURN 자료형-- 자리수 지정 불가능, ;쓰면 안돼 IS [변수 선언] - 자리수 지정 가능, 변수초기화도 가능 BEGIN 함수 내용 RETURN(값); END; /

-FUNCTION 호출] 방법1) 예) SELECT 함수명(매개변수1,매개변수2) FROM DUAL 방법2) VAR HAP CHAR(2); EXCUTE :HAP := 함수명(매개변수1, 매개변수2); PRINT HAP;

함수 예제-1]

함수 예제-2]

함수 예제-3]