기술노트 블로그

* Youtube Link - https://www.youtube.com/watch?v=xkWzMYAqbqU&list=PL6i7rGeEmTvpLoDkB-kECcuD1zDt_gaPn&index=11

01. CUBE 의미

CUBE를 사용하면 결합 가능한 모든 집계를 계산한다.

예를 들어, GROUP BY CUBE(DEPTNO, JOB); 이면

부서별 합계, 직업별 합계, 부서별 직업별 합계, 전체합계가 조회되는 것이다.

02. CROSS JOIN 문제

CROSS JOIN은 ON 절을 사용할 수 없다.

사용하면 에러가 발생한다.

모든 경우의 수를 조회하는 조인이다.

03. ANSI JOIN

ORACLE의 OUTER JOIN을 ANSI JOIN으로 바꾼 결과이다.

정답은 (2)이다.

04. 키의 종류

정답은 (3)이다.

키는 식별자를 이야기 한다.

유일한 인스턴스를 구분할 수 있는 것이 무엇이냐

수퍼키는 유일하게 식별할 수 있는 모든 조합이다.

대신 최소성을 만족하지는 않는다.

후보키는 유일성 + 최소성을 만족한다.

후보키에는 예를 들어 CUST_ID, 주민등록번호 등이 있다.

그러면 CUST_ID를 기본키로 설정을 하면

주민등록번호 등은 대체키가 되는 것이다.

05. ROUND() 함수

반올림하는 함수이다.

ROUND(숫자, 반올림 소수 자리)는 반올림하는 함수이다.

06. 윈도우 함수

(3)은 문법에러이다.

07. 내부 식별자와 외부 식별자

정답은 (3)이다.

08. 서브쿼리 문제

상관 서브쿼리

비상관 서브쿼리

A.COL1이 1이면서 B.COL1이 1이고

동시에 A.COL2이 200이고 B.COL2이고

B.COL3 > 1400인 행을 조회한다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=aAYU18AGyDg&list=PL6i7rGeEmTvpLoDkB-kECcuD1zDt_gaPn&index=11

01. 파티션 문제

MANAGER_ID를 파티션으로 나누어서 연봉으로 오름차순을 하고 처음 값 부터 누적계산을 하여

SUM(SALARY)를 도출해낸다.

이로써 또 다른 의미있는 데이터를 만들어낸다.

AND CURRENT ROW와 같은 결과가 나온다.

RANGE BETWEEN 10 PRECEDING AND 150 FOLLOWING을 하면

예를 들어 SALARY가 2500인 열은 SALARY가 2490부터 2650까지의 범위를 만들어내고

이를 파티션으로 COUNT(*)을 한 결과가 컬럼 TTT에 나오는 것이다.

ROWS BETWEEN  1 PRECEDING AND 1 FOLLOWING의 경우 

예를 들어 SALARY가 6500인 경우

그 위의 행인 SALARY가 5800인 행과

바로 그 아래 행인 SALARY가 7900인 행 이렇게 3개의 행의 연봉의 평균값을 계산해서 

결과를 도출하는 것이다.

02. 인라인 뷰 및 UNION 

(1)이 정답이다.

(2)의 경우 문법적으로 오류가 난 경우이다.

group by salary로 하고 select 절에서 job_id를 조회를 하면

예를 들어 1500인 salary를 갖는 컬럼이 job_id를 2개 갖게 된다.

그래서 group by를 사용을 하면 사용할 수 있는 컬럼이 한정이 된다.

그래서 (2)은 정답이 아니다.

union all은 중복도 되지 않고 정렬도 되지 않는다.

union 기능이 sort unique였지만

오라클이 업그레이드 되면서 hash unique(중복제거+정렬 안할 수 있음)

으로 변경되었다.

03. CASE 문법의 종류와 CASE 문법의 NULL 연산

SIMPLE CASE 문법과 

SEARCHED CASE 문법 이렇게 2개의 문법이 있다.

(1)의 경우

SIMPLE CASE 문이다.

WHEN NULL의 경우 '='의 연산을 하므로 NULL은 연산이 불가능하다. 그래서 함수를 사용해야 한다.

그래서 NULL인 컬럼은 else문인 0으로 출력된다.

NULL은 IS NULL 연산으로 연산할 수 있다.

DECODE() 함수는 NULL인 경우 -1. 아니면 있는 값 그대로를 배출하도록 만들어졌다.

예를들어 a.COL1이 NULL이면 -1 아니면 a.coll을 배출한다.

04. WHERE절 사용 방법

정답은 (4)이다.

(2)과 (4)의 다른 점은

정렬이 다르다는 것이다.

05. 데이터 입력 문제

숫자를 VARCHAR2(10) 데이터를 입력하려고 하면

형이 달라서 자기 형으로 바꿔본다. 그래서 TO_CHAR(002)를 한다.

그러면 '002'가 된다.

정답은 (4)이다.

숫자값을 DATE에 넣으려고 한다.

그러면 TO_DATE(20220420)을 하면 날짜 값으로 들어갈 수도 있다.

하지만 에러가 날 확률이 높다.

06. 여러 열 ORDER BY 문제

먼저 ORDER BY COL1 DESC를 한다.

그리고 COL2에서 같은 값들 중 COL3를 기준으로 정렬을 한다.

NULL은 제일 아래로 내려간다.

그래서 정답은

(3)이다.

07. 집계 함수

각 열을 집계해서 조회한다.

이렇게 다양한 컬럼들을 집계해서 조회할 수 있다.

정답은 (2)이다.

08. GROUP BY 활용

정답은 (3)이다.

각 계층별 결과를 정확히 알아야 한다.

GROUP BY DEPTNO, JOB: 이 경우는 전체를 GROUP BY한 결과가 나와선 안 된다.

GROUP BY GROUPING SETS(DEPTNO, JOB): JOB으로도 전체를 GROUP BY한 결과가 나와야 한다.

GROUP BY ROLLUP(DEPTNO, JOB): ROLLUP은 계층적으로 GROUP BY를 하여서 조회한다.

GROUP BY CUBE(DEPTNO, JOB): 모든 경우의 수를 GROUP BY하여 조회한다.

09. ORACLE을 ANSI 표준 SQL문으로 변환하는 문제

정답은 (4)이다.

10. 조인 문제

모든 경우의 수를 구하고

조인 조건에 맞는 결과만 조회한다.

카티션 조인이 된 순간 모든 경우의 수가 처리된 것을 볼 수 있다.

정답은 (1)번이다.

INNER JOIN은 키 값이 같은 결과만 조회한다.

LEFT OUTTER JOIN과 RIGHT OUTTER JOIN은 각각 키 값이 같지 않은 결과도 조회한다.

FULL OUTTER JOIN은 양 측의 테이블에서 키 값이 같지 않은 결과도 조회한다.

CROSS JOIN은 카티션 조인을 하여서 모든 경우의 수를 구한다. 예를 들어 4개 행과 3개 행을 곱해서 12개의 모든 경우의 수를 조인해서 조회한다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=rDBZ6q0QqBU&list=PL6i7rGeEmTvpLoDkB-kECcuD1zDt_gaPn&index=9

01. SQL 조회 순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 의 순서로 조회가 된다.

HAVING의 경우 GROUP BY를 통해서 집계가 완료된 상태를 토대로 추가적인 필터링을 하는 것이다.

그리고 SELECT를 통해서 해당하는 데이터가 출력이 된다.

ORDER BY를 통해서 출력하는 대상을 특정 컬럼을 기준으로 정렬을 한다.

그래서 순서를 잘 암기해야 한다.

02. 다중 컬럼 연산

예를 들어 컬럼 IN (1,2,3); 이면

컬럼 = 1 OR 컬럼 = 2 OR 컬럼 = 3이 됩니다.

다중 컬럼 연산의 경우

WHERE (COL1, COL2) IN ('x', 'y'), ('KK', 'BB')); 이면

COL1 = 'x' 이고 COL2 = 'y' 이거나 COL1 = 'KK' 이고 COL2 = 'BB'인 경우이다.

각각 AND 그리고 OR 별로 처리가 된다.

여기서 NOT 연산은 TRUE -> FALSE로 그리고 FALSE -> TRUE로 변환하는 것이다.

그러면 해당 컬럼들을 제외한 값을 조회하는 결과가 되므로

같은 결과가 나온다.

이외 2~4번까지를 보면

가로 안에 OR 또는 = 연산자가 들어가는 것을 볼 수 있다.

이러한 방식으로도 연산이 가능하다.

다중 연산을 풀어서 설명한 그림이다.

정답은 (3)이다.

AND와 OR에는 우선순위가 있다.

AND -> OR 순서로 처리가 된다. 마치 수학에서 곱하기와 더하기와 같다.

03. WITH GRANT OPTION 과 권한 회수

예를 들어

REVOKE 권한 FROM B CASCADE 라고 작성을 하시면

WITH GRANT OPTOIN으로 권한을 주었던 그 대상도 연쇄적으로 뺏어버린다.

CASCADE 를 작성을 안 하면 B만 뺏기고 C는 그대로 권한을 갖고 있는 것이다.

CASCADE를 사용을 한 다는 가정하에 정답은 (4)이다.

그러나 CASCADE를 사용을 안 한다면 정답은 (3)이다.

04. CASCADE CONSTRAINT

만일 DROP TABLE [테이블명] CASCADE CONSTRAINTS; 를 하면

테이블과 그에 연관된 제약조건들이 전부 다 삭제가 된다.

예를 들어 STUDENT 테이블을 삭제를 하면 STUDENT_TEL과 STUDENT_ADDR과의 관계가 제거됩니다.

즉 STUDENT_TEL과 STUDENT_ADDR의 FK였던 데이터들은 그대로 남아 있지만 FK였던 제약조건은 의미가 없어지므로 제거가 됩니다.

그 반대로 CASCADE CONSTRAINTS를 붙이지 않는다면 STUDENT_TEL, STUDENT_ADDR은 제약조건에 의해서 제거가 되지 않습니다.

* CASCADE는 SQL-SERVER에서는 지원하지 않는다. SQL-SERVER는 MS사이다. ORACLE사가 아니다.

정답은 (4)이다.

05. DDL/DML/DCL/TCL

정답은 (2) DML에 대한 설명이다.

DCL은 데이터 컨트롤 언어이다.

06. NVL() 함수 사용

GROUP BY가 없으므로 전체를 대상으로 집계를 한다.

NVL() 함수를 사용하므로 만일 CNT 열이 NULL이라면 그 대체할 값을 출력해주는 함수이다.

NVL(CNT, 0) 이면 CNT열의 값이 NULL이면 0으로 대체된다.

그리고 숫자와 NULL을 연산을 하면 NULL은 제외하고 연산이 된다.

정답은 (3)이다.

07. GROUP BY 연산 문제

COUNT(*)의 경우 NULL과 관계없이 개수를 파악한다.

FROM 부터 시작을 해서 GROUP BY를 CLASS열 기준으로 하였다.

그리고 distince NAME으로 NAME열을 기준으로 중복을 제거 하였다.

그리고 count() 함수를 사용한 결과를 얻었으므로 

정답은 (2)이다.

COUNT(1)의 경우

COUNT(*)과 같은 행의 수를 조회를 한다.

성능도 비슷하다. COUNT(*)을 사용할 것을 권장한다.

COUNT(컬럼)의 경우 만일 컬럼이 NULL이면 조회가 결국 되지 않고 개수가 파악된다.

이러한 결과가 조회된다.

08. WINDOW 함수 문제 (순위 문제)

OVER가 나온다면 WINDOW 함수라는 것을 캐치하시고

RANK()의 경우 중복된 순위 다음에는 그 다음 순위를 부여한다.

DENSE_RANK()의 경우 중복된 순위 다음에는 바로 다음 순위를 부여한다.

ROW_NUMBER()의 경우 그대로 1부터 차례대로 순위를 부여한다.

정답은 (2)이다.

09. 윈도우 함수 (집계 문제)

먼저 테스트할 데이터는 HR.EMPLOYEES의 SALARY, JOB_ID 그리고 EMPLOYEE_ID 이다.

OVER() 함수는 SUM(SALARY)의 기준을 정하는 함수이다. 집계 함수 바로 뒤에 붙여서 사용된다.

OVER() 함수에 PARTITION BY를 사용을 하면 JOB_ID를 파티션으로 연봉의 합계를 구한다.

ORDER BY를 통해서 EMPLOYEE_ID를 기준으로 오름차순한다.

RANGE BETWEEN은 행을 어디까지 처리할 것이냐는 의미이다.

UNBOUNDED PRECEDING AND UNBOUNDED FOLLWING 은 구분 없는 이전 그리고 구분 없는 이후까지 행을 처리하겠다는 의미이다.

하지만 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 

각 행별로 누적값을 구하게 된다.

정답은 오류가 발생하는 (3)이다. 이미 UNBOUNDED PRECEDING이 범위에 나왔기 때문이다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=0RlRnjds4Co&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=21

8.1.3 임시 테이블

메모리상에 생성되기 때문에 속도가 빠르다.

• 기본형식

CREATE GLOBAL TEMPORARY TABLE 테이블 이름

(열 정의...)

[ON COMMIT DELETE ROWS 또는 ON COMMIT PRESERVE ROWS]

• 내용

ON COMMIT DELETE ROWS : 커밋하면 테이블이 삭제된다. (디폴트 옵션)

ON COMMIT PRESERVE ROWS: 세션이 종료되면(DB연결이 종료되면) 테이블은 삭제된다.

tempTBL 임시 테이블을 생성 완료하였다.

tempTBL이 로컬-tableDB 커넥션(Session)의 tableDB 사용자(스키마) 에 생성된 것을 볼 수 있다.

COMMIT을 통해서 데이터를 확정하니

임시 테이블의 데이터가 날아가버렸다.

디폴트 옵션이 ON COMMIT DELETE ROWS; 이기 때문이다.

ON COMMIT PRESERVE ROWS

ON COMMIT PRESERVE ROWS로 생성된 임시 테이블은

다른 세션에서도 접속이 가능하다.

하지만 세션이 종료되면 임시 테이블의 데이터는 삭제된다.

로컬-tableDB와 로컬-tableDB~2 는 다른 세션이다.

하지만 각각의 세션에서 임시 테이블 tempTBL2는 조회가 된다.

하지만

다음과 같이 임시 테이블은 남아 있지만

데이터는 삭제된 것을 볼 수 있다.

테이블 삭제

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=40b8p9okBa4&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=20

8.1.2 제약 조건

무결성을 지키는 제약 조건

ID는 중복이 안 된다.

학번, 사번 등이 이에 해당한다. 또는 일련번호, UUID등도 속한다.

데이터베이스를 만드는 사람이 정한다. 정해진 약속은 없다.

하지만 이름이 기본 키라면 같은 이름의 사람은 회원가입이 불가능할 것이다.

테이블 중 기본 키가 없는 경우도 있다. 대부분의 테이블은 기본 키를 갖는다. 기본 키가 있는 것을 권장한다.

테이블을 설정할 때 기본 키를 설정할 수 있다.

제약 조건 확인

USER_CONSTRAINTS 테이블을 통해서

OWNER = 'TABLEDB'

TABLE_NAME = 'USERTBL'

CONSTRAINT_TYPE = 'P'

조건절을 통해서 

PK를 확인할 수 있다.

P는 기본키, R은 외래키, C는 NOTNULL 또는 CHECK이다.

CASCADE CONSTRAINTS

CASCADE CONSTRAINTS를 통해서 

외래키 제약조건이 있는 테이블도 같이 데이터가 삭제된다.

PK의 이름을 지어주기

userID CHAR(8) NOT NULL CONSTRAINT PK_userTBL_userID PRIMARY KEY

이다.

이렇게 기존과는 달리

SYS_C0011269 -> PK_USERTBL_USERID

로 PK의 이름이 지어진 것을 볼 수 있다.

PK의 이름을 직접 지어주는 것을 권장한다.

동일하게 생성이 되었다.

테이블 생성 시 PK를 지정하지 않고

테이블을 변경해서 PK 및 PK이름을 정해주는 방식이다.

제품 코드는 겹친다.

제품 코드 및 제품 일련 번호를 합쳐서 PK를 구성을 하면

각 행을 구분지을 수 있다.

테이블을 PK없이 만들고

테이블을 변경함으로써

prodCode, prodID를 결합한다.

PRIMARY KEY (prodCode, prodID) 이렇게 만든다.

PRODTBL 테이블을 우클릭하고 편집을 보면

PK가 두 컬럼이 지정되어 있다.

이러한 경우는 각각이 PK가 아니라

두개 합쳐서 PK인 경우다.

외래 키(Foreign Key) 제약 조건

대게 일대다의 관계로 연관을 지어주는 것이다.

데이터의 무결성을 보장해주는 것 중 하나이다.

두개의 테이블이 필요하고 한 개의 테이블이 다른 한 개의 테이블을 의존한다.

회원 테이블의 아이디를 갖고 있는 테이블을 '기준 테이블'이라고 한다.

구매 테이블은 회원 테이블을 참조한다고 해서 '참조 테이블'이라고 부른다.

PK는 반드시 유일해야 한다. 중복이 있으면 절대 안 된다.

REFERENCES 를 통해서 기준 테이블의 PK를 지정한다.

CONSTRAINT FK_userTBL_buyTBL REFERENCES userTBL(userID)

로써 FK의 이름을 지정해줄 수 있다.

,CONSTRAINT FK_userTBL_buyTBL FOREIGN KEY(userID) REFERENCES userTBL(userID)

를 통해서 제약조건을 맨 아래에 설정함으로써 FK를 설정할 수 있다.

테이블을 지정하고

테이블 변경을 통해서 FK를 지정할 수 있다.

ON DELETE CASCADE

회원을 탈퇴하면 구매 테이블의 예를 들어 KBS의 구매 목록은 붕 뜨게 된다.

원래는 제약조건에 의해서 회원 탈퇴가 안 된다.

ON DELETE CASCADE를 통해서 

회원이 탈퇴하면 자동으로 구매 테이블 내역도 삭제할 수 있다.

업무적으로 설정하면 된다.

UNIQUE 제약 조건

이메일이나 주민등록번호는 중복이 안 된다.

PK로 지정할만 하지만

추가로 하기 위해서

UNIQUE키로 지정하는 경우가 많다.

PK는 테이블에 하나 밖에 지정을 못 한다.

이메일은 중복이 안 된다.

UNIQUE 키를 적어주시면 설정이 된다.

또는 맨 마지막에 추가로 추가할 수 있다.

UNIQUE 이름 지정

, CONSTRAINT AK_email UNIQUE (email) 를 통해서

UNIQUE 키 및 이름을 지정할 수 있다.

테이블을 만들고

테이블 변경 시 

ADD CONSTRAINT AK_EMAIL UNIQUE (EMAIL);

를 통해서 제약조건을 추가하였다.

CHECK 제약 조건

키는 -값이 안 된다.

CHECK 조건을 통해서 해당하는 것만 

입력된다.

테이블 변경 시

ADD CONSTRAINT CK_height

CHECK (height >= 0)

제약조건을 추가함으로써

키가 0보다 큰 경우만 입력되도록 제약조건을 한다.

ADD CONSTRAINT CK_mobile1

CHECK (mobile1 IN ('010','011','016','017','018','019'));

를 통해서 국번 제약조건을 걸었다.

예를 들어 삐삐의 경우

012, 013의 국번의 경우

현재의 제약조건으로 하려고 하면

제약조건이 안 걸린다.

왜냐하면 012, 013이 있기 때문이다.

그런데 ENABLE NOVALIDATE 조건을 걸면

기존의 것은 그대로 인정하고

제약조건을 걸겠다는 의미이다.

DEFAULT 정의

테이블에 지정할 수도 있고

ALTER TABLE userTBL MODIFY birthYear DEFAULT -1;

MODIFY를 통해서 테이블의 열을 수정한다.

DEFAULT 명령문, NULL 명령문 등을 통해서

INSERT문으로 데이터 입력 시 DEFAULT 값을 자동으로 입력하거나, NULL을 입력할 수 있다.

' ' 띄어쓰기 한칸이 입력된 열은 NULL이 아니라 ' '공백이 들어간 것을 볼 수 있다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=5ReqI3V1LxY&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=19

8.1 테이블

SQL Developer를 통해서 GUI로 그림의 테이블들을 생성해보겠다.

새로운 사용자 tableDB 를 만들었다.

connect, resource, dba 권한을 부여하였다.

새로운 접속을 만들었다.

그리고 테이블을 두 건 만들었다.

BUYTBL의 경우

고급을 선택하고

제약조건을 통해서

USERTBL과 일대다 관계를 형성해준다.

+를 선택하고

새 외래 키 제약 조건을 선택하여

다음과 같이 USERTBL의 PK를 외래키로 추가해준다.

시퀀스를 우클릭하고

새 시퀀스를 생성한다.

이름이 IDSEQ 시퀀스를 생성하였다.

다음으로 시작 1

증분 1

1로 시작해서 1씩 증가하는 시퀀스를 생성 완료하였다.

테이블의 열 화면에서

편집 버튼을 선택하고

BUYTBL의 IDNUM을 선택하고

ID 열 탭에서

IDSEQ 시퀀스를 선택한 다음

확인을 선택한다.

PK 제약조건에 의해서 회원 테이블에 없는 회원의 구매는 구매 테이블에 들어갈 수 없다.

SQL 로 테이블 생성

tableDB 생성

tableDB 사용자 생성

테이블 생성 (1)

테이블 생성(2): NOT NULL, NULL 추가

테이블 생성(3): PRIMARY KEY 추가

테이블 생성(4): FOREIGN KEY 제약조건 추가

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=kNf-UnP0gAM&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=18

7.3 PL/SQL 프로그래밍

DECLARE 명령문으로 변수를 선언하고

BEGIN과 END 사이에 프로그래밍한다.

7.3.1 IF...ELSE...

SET SERVEROUTPUT ON;

- SERVEROUTPUT 변수 ON으로 설정

DBMS_OUTPUT.PUT_LINE('100입니다.');

- 화면에 출력한다.

IF var1 = 100 THEN

  DBMS_OUTPUT.PUT_LINE('100입니다.');

ELSE

  DBMS_OUTPUT.PUT_LINE('100이 아닙니다.');

END IF;

- IF문을 통해서 프로그래밍을 한다.

SELECT hire_date INTO hireDate;

- 조회된 값 hire_date 를 hireDate에 입력한다.

CURRENT_DATE()

- 세션에 저장된 현재 날짜를 조회한다.

CASE 

    WHEN pNumber >= 90 THEN

        credit := 'A';

    ...

    ELSE

        pNumber := 'F';

END CASE;

- CASE와 END CASE 사이에 CASE 문이 들어간다.

- WHEN THEN을 통해서 조건식과 조건에 부합하면 실행할 코드가 들어간다.

NULLS LAST 명령문을 통해서 

총구매액이 null인 값들을 맨 아래에서 조회된다.

원래는 null인 값들은 맨 위에서부터 조회된다.

CASE문을 통해서 

총 구매액을 구간으로 회원등급을 나누어서 회원들을 조회하였다.

총 구매액이 NULL인 유령고객들은 맨 아래에 조회되도록 하였다.

WHILE 문

WHILE (조건문)

- 조건이 TRUE인 동안 반복문이 실행된다.

LOOP

END LOOP;

- LOOP 문 안에 코드들이 반복되어서 실행된다.

FOR 문

FOR iNum IN 1 .. 100

- 1에서 100까지 계속 반복된다.

- 1을 iNum에 넣고 돌고, 2를 iNum에 넣고 돌고를 100까지 계속 반복한다. 

7의 배수는 건너 뛰고 합을 구한다.

MOD는 나누기의 나머지를 구하는 함수이다.

IF MOD(iNum, 7) = 0

iNum을 7로 나눈 값의 나머지를 구하는 함수이다.

CONTINUE는 다음 반복으로 넘어간다.

EXIT은 반복문을 탈출한다.

GOTO my_goto_location;

- <<my_goto_location>> 으로 이동한다. EXIT과 똑같은 역할을 하고 있다.

DBMS_LOCK.SLEEP(5);

DB가 5초간 멈춘후 실행된다.

7.3.6 예외 처리

DECLARE

    -- 테이블 열의 데이터 타입과 동일하게 변수 타입을 설정

v_userName userTBL.userName%TYPE;

- userTBL 테이블의 userName 컬럼의 타입을 v_userName의 타입으로 지정한다.

EXCEPTION

    WHEN NO_DATA_FOUND THEN

          DBMS_OUTOUT.PUT_LINE('김씨 고객이 없습니다.');

    WHEN TOO_MANY_ROWS THEN

          DBMS.OUTPUT.PUT_LINE('김씨 고객이 너무 많네요.');

- EXCEPTION 처리를 한다.

사용자 정의 예외 처리

userException EXCEPTION;

PRAGMA EXCEPTION_INIT(userException, -1422);

- EXCEPTION을 정의하였다. -1422는 TOO_MANY_ROWS 예외이다.

하지만 이러한 경우는 예외를 만들어서 자주 사용하지는 않는다.

1) IF SQL%NOTFOUND THEN

- SQL이 하나도 조회되지 않았을 때이다. 이러한 경우 zeroDelete 예외를 발생시키는 로직이다.

2) RAISE zeroDelete;

zeroDelete 예외를 발생시키는 것이다.

그리고

3) EXCEPTION WHEN THEN 구문

을 통해서 zeroDelete 예외를 처리한다.

RAISE_APPLICATION_ERROR(-20001, '데이터 없음 오류 발생!!');

- 마치 오라클 예외인 것 처럼 처리할 수 있다.

동적 SQL문 처리

EXECUTE IMMEDIATE v_sql INTO v_height;

- v_sql의 변수 값인 문자열로된 sql문을 실행한다. 동적 SQL문을 위해서 사용한다.

연/월/일을 찾아와서

연/월/일을 테이블명으로 생성하고 싶다.

그러면 동적 SQL문을 실행해야 하는 것이다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=y_fKHQHon7k&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=16

7.2 조인(Join)

조인: 두개의 테이블을 서로 연관해서 조회하는 것

조인을 위해서는 두 개의 테이블이 필요하다.

일대다의 관계가 가장 일반적이다.

PK는 1 밖에 없다.

FK는 여러개일 수도 있다.

조인의 종류

7.2.1 INNER JOIN(내부 조인)

INNER JOIN을 통해서 두 테이블이 서로 연관이 된다.

ON하고 이렇게 조인될 조건을 적어주는 것이다. <조인될 조건>

그리고 연관된 테이블에서 WHERE 조건을 쓸 수 있다. [검색 조건]

위 그림은 INNER조인의 형식이다.

사용자 테이블과 구매 테이블

이 연관된 두 개의 테이블을 조인해서 결과를 낸 상태이다.

userID의 열이 어떤 테이블의 userID인지 알 수가 없다.

두 테이블의 연관된 테이블의 컬럼을 지정해서 조회시

만일 두 컬럼이 같은 컬럼 예를 들어 PK, FK와 같은 경우

컬럼의 테이블을 지정해주어야 한다.

테이블을 지정해서 컬럼을 조회한 조인 결과이다.

Alias를 통해서 코드를 줄일 수 있다.

INNER JOIN은 조인된 행만 조회한다.

결합이 되는 것만 조회가 된다.

편리한 부분이 있다.

구매한 회원만 조회를 할 것이다.

다대다 관계의 테이블 조인

그림을 보고 테이블을 설계한다.

학생_동아리 테이블의 PK는 일련번호이므로

CREATE SEQUENCE stdclubSEQ; 명령어를 통해서 학생_동아리 테이블의 시퀀스를 생성해준다.

이렇게 3개의 테이블을 조인을 해서 

다대다 관계의 테이블인 학생과 동아리를 조인한 결과를 볼 수 있다.

열들이 조금 많아지더라도 기본 구조는 가지고 가니까 잘 이해하시길 바랍니다.

외부 조인

LEFT OUTTER JOIN하면 왼쪽이 다 나온다.

RIGHT OUTTER JOIN하면 오른쪽이 다 나온다.

PRODNAME이 null인 행은 구매를 하지 않은 회원들을 의미한다.

RIGHT OUTTER JOIN을 하고 테이블의 위치를 서로 바꾸어도

같은 결과가 나온다.

OUTTER JOIN으로

다음과 같이 B.prodName IS NULL인 경우

회원은 회원이지만 구매를 한 적이 한 번도 없는

유령 회원을 조회할 수 있다.

3개의 테이블 외부 조인

INNER JOIN으로는 조회되지 않았던

동아리에 들지 않은 학생들이 모두 조회가 된다.

다음과 같이 성시경은 동아리에 들지 않았음을 알 수 있다.

RIGHT OUTTER JOIN을 하면 이번에는

동아리 테이블을 기준으로 RIGHT OUTTER JOIN을 한 결과이다.

성시경이라는 이름과 주소가 각 각 null로 나오는 것을 볼 수 있다.

하지만 아무도 가입하지 않은 수영 동아리도 조회되는 것을 볼 수 있다.

이 두개의 결과를 모두 보고 싶다면

UNION을 사용하면 된다.

아무런 동아리도 가입하지 않은 성시경과

아무도 가입하지 않은 동아리인 수영 동아리도 같이 조회가 되는 것을 볼 수 있다.

CLUBNAME IS NULL 인 경우가 전자이고

STDNAME IS NULL 인 경우가 후자이다.

상호 조인

조인이라기 보다도 대량의 데이터를 만들 때 자주 사용된다.

이렇게 120개의 행이 출력되었다.

무작위로 대용량의 샘플 데이터를 만들 때 종종 사용할 수 있다.

100만건의 데이터를 갖고 금방 1조건의 데이터를 만들 수 있다.

SELF JOIN(자체 조인)

예를 들어 부서를 통해서 부하직원과 직속상관이 서로 연관된다면

자기 자신의 컬럼들을 기준으로

자체 조인을 할 수가 있다.

이렇게 부하직원과 직속 상관 그리고 직속상관부서를 구할 수 있다.

실제로는 이러한 데이터를 조회한 결과이다.

이러한 데이터를 가공해서 만들었다.

자체 조인이 가능한 테이블이다.

UNION

UNION 을 통해서

테이블들의 결과를 결합해서 보여줄 수 있다.

중복된 결과를 갖고 있으면 조회가 불가능하다.

UNION ALL을 통해서 중복된 결과도 조회가 가능하다.

UNION을 하려면 컬럼의 갯수가 같고, 데이터 타입이 같거나 서로 호환이 되어야 한다.

이렇게 테이블을 합치는 것이 가능하다.

서브쿼리를 통해서 NOT IN이 가능하다.

핸드폰이 있는 회원의 이름과 전화번호를 조회하였다.

감사합니다.