기술노트 블로그

* 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이 가능하다.

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

감사합니다.

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

들어가면서

7장에서는 PL/SQL 고급을 다루어볼 예정이다.

7.1.1 Oracle에서 지원하는 데이터 형식의 종류

숫자 데이텨 형식

1바이트 -> 8비트 -> 00000000

BINARY_FLOAT 4Bytes (32bit 부동 소수점)

BINARY_DOUBLE 8Bytes (64bit 부동 소수점)

NUMBER(p,[s])  5~21Bytes (전체 자리수(p)와 소수점 이하 자리수(s)을 가진 숫자형.)

문자 데이터 형식

CHAR는 영문자

NCHAR는 유니코드, 한글이 들어가는 경우 사용하면 됨.

CHAR로 한글이 들어가도 되지만 두배로 잡아야 하기 때문에 유니코드를 사용하는 NCHAR가 편하다.

VARCHAR2(n) 가변길이형, 4000글자까지 표현 가능

NVARCHAR2(n) 유니코드 가변길이 문자형.

CLOB굉장히 큰 글자를 넣을 때 사용. 최대 128TB

NCLOB굉장히 큰 글자를 넣을 때 사용. 한글 소설이다 그러면 NCLOB이다. 최대 128TB. 거의 무제한에 가깝게 넣을 수 있다.

주로 VARCHAR2를 많이 사용을 하는데 고정 길이는 CHAR형을 사용한다.

INSERT/UPDATE 시 CHAR형이 더 좋은 성능을 발휘한다.

N자가 붙은 것은 한글이다.

이진 데이터 형식

BLOB은 대용량의 이진 데이터를 저장할 수 있다.

그림, 동영상, 음악도 저장할 수 있다. 거의 무제한에 가깝게 저장이 된다.

BFILE은 이진 데이터를 파일 형태로 저장함. 운영체제에 외부 파일형태로 저장됨. 

날짜와 시간 데이터 형식

• SELECT SYSDATE FROM DUAL; -- 현재 날짜
• SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MM:SS') "현재 날짜" FROM DUAL;
• SELECT TO_DATE('20201231235959', 'YYYYMMDDHH24MISS') "날짜 형식" FROM DUAL;
  
  

기타 데이터 형식

• RAWID 10바이트 
• XMLType N/A XML 데이터를 저장하기 위한 데이터 형식.
• URIType N/A URL 형식의 데이터를 저장하기 위한 데이터 형식.

ROWID를 통해서 내부적으로 저장되어 있는 물리적인 주소도 알 수 있다.

LOB(Large Object: 대량의 데이터)를 저장하기 위해서 CLOB, BLOB 데이터 형식을 지원한다.

차후 실습을 통해서 확인해보겠다.

유니코드 데이터

변수의 사용

SET SERVEROUTPUT ON; 명령어를 통해서 SERVEROUTPUT을 ON한다.

DBMS_OUTPUT.PUT_LINE(myVar1); 명령어를 통해서 서버에 output한다.

데이터 형식과 형변환

굉장히 중요한 함수이다.

CAST 함수를 통해서 형변환이 가능하다.

예를 들어 이와 같이 AVG() 함수를 이용해서 평균값을 구한 경우 다음과 같은 결과값을 얻을 수 있다.

CAST() 함수를 이용해서 NUMBER(3) 형식으로 소수점 데이터를 형변환하였다.

NUMBER는 정수형 타입이다.

그래서 2.7666666666666666666666의 값이

3으로 형변환되었다.

CAST(expression AS DATE)

/,$,%,@ 등은 날짜의 형식을 지정할 수 있다.

CAST(expression AS CHAR) 및 ||

|| 기호를 통해서 문자열로 잇는다.

CAST(expression AS CHAR) 명령어를 통해서 숫자를 문자로 바꾼다.

TO_CHAR(expression, '형식')

숫자를 달러, 원 등의 통화로 바꿀 수 있다.

그리고 SYSDATE(현재날짜)를 'YYYY/MM/DD HH:MM:SS' 형식으로 변경할 수 있다.

그러면 2024/11/04 05:11:39 형식으로 바꿀 수 있다.

옵션을 주어서 문자를 다른 형태로 바꿀 수 있다.

그 문자가 숫자일 수도 있다. 16진수와 같은 것이다. 이렇게 다양한 형태로 바꿀 수 있다.

TO_CHAR() 및 TO_NUMBER()를 통한 16진수 <-> 10진수 변환

TO_CHAR() 명령어를 통해서 문자를 16진수로 변형할 수 있다.

16진수로 변형해서 문자로 바꿀 수 있다.

그리고 TO_NUMBER()를 통해서 16진수의 문자인 숫자를 다시 10진수의 숫자로 바꿀 수 있다.

그리고 '0123'인 문자를 TO_NUMBER를 통한 숫자로 표현을 하면 다음과 같이

123 그리고 1234.456으로 표현될 수 있다.

이외 문자열을 잇는 방법

1) 는 + 연산을 통해서 문자인 숫자를 서로 더했다. 결과로 300이 출력되었다. 정수로 형변환이 되었다.

2) CONCAT() 함수를 통해서 문자를 연결했다. 결과는 100200이다. 문자로 출력되었다.

3) 100 || '200' 의 결과는 100200이다. || 연산은 문자로 암시적 형변환을 일으킨다.

4) >,>= 등의 비교 연산식에 있는 '500' 문자는 500이라는 숫자로 암시적 형변환이 일어나서 WHERE 조건절을 실행하였다. 결과는 price >= 500 인 구매 목록이다.

FROM DUAL 을 통해서 임시 테이블을 만든다.

그리고 SELECT문을 통해서 연산을 한 결과들을 임시적으로 출력한다.

감사합니다.

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

CTE (공통 테이블 표현)

WITH CTE_테이블이름(열이름)

AS

(

            <쿼리문>

)

SELECT 열이름 FROM CTE_테이블이름;

실습

WITH절을 통해서 abc 라는 cte, 임시 테이블을 만들 수 있다.

WITH절에서 정의한 함수와 가로 안의 SELECT문의 열이 1:1로 매치가 되어야 한다.

userID -> userID, SUM(price*amount) -> total

중복 CTE (공통 테이블 표현)

각 지역별 최고키 평균을 구하였다.

지역별 최고키를 구하고

cte를 만든 다음

 지열별 최고키의 평균을 구하였다.

재귀 cte를 통해서 다음과 같은 여러 cte테이블들을 사용해서

전체 평균값을 구해보았다.

SQL의 분류

DML문은 실수할 수도 있다. 그래서 ROLLBACK을 시키면 입력하고 변경한 것을 취소할 수 있다.

COMMIT을 시키면 입력하고 변경한 것을 확정을 지을 수 있다.

트랜잭션을 완전 적용할 수 있는 것은 COMMIT 그리고 취소시킬 수 있는 것은 ROLLBACK이다.

DDL문은 CREATE, DROP, ALTER이다. COMMIT이나 ROLLBACK이 필요 없다. 그래서 DDL문은 실행 즉시 Oracle에 적용된다.

DCL문은 GRANT/REVOKE/DENY 등이 이에 해당한다.

DML문

INSERT INTO 테이블(열1, 열2, ...) VALUES (값1, 값2, ...)

열을 입력하지 않으면 테이블에 정의된 모든 열들을 차례대로 값을 입력해주면 된다.

MERGE

멤버 테이블은 굉장히 중요한 테이블이다.

변경 테이블을 만들어 놓을 것이다.

1주일에 한번씩 변경 작업을 일괄적으로 할 것이다.

사유에 따라서 한꺼번에 적용되게끔 어떻게 하는지 한번 살펴보겠다.

changeTBL을 토대로 

memberTBL의 userID값과 같은 값들을 체크해서

주소를 변경하고

만약 변경 사유가 '회원탈퇴'라면

DELETE 한다.

만약 MATCH가 되지 않는다면

memberTBL에 새로 INSERT한다.

감사합니다.

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

SELECT...FROM...WHERE

서브쿼리를 이용한 테이블 복사는

열과 데이터는 그대로 복사가 된다.

그리고 NOT NULL 여부도 복사가 된다.

PK, FK는 복사가 안 된다.

GROUP BY 및 HAVING

실습

SUM(): 수량이 몇개인지 조회하고 싶다.

AS: 컬럼 별칭 지정

SUM() 연산의 순서: price * amount 컬럼을 갖은 테이블을 먼저 구하고 그 다음에 sum을 구하여서 그룹 테이블을 조회한다.

AVG(): 평균

CAST(AVG(amount) AS NUMBER(5,3)): 전체 5자리, 소수점 3자리 소수점을 구한다.

MAX(): 최대값

MIN(): 최소값

그룹함수를 ORDER BY 절에서도 사용할 수 있다.

그룹함수의 정렬이 필요하기 때문이다.

ROLLUP: 소계와 합계를 계산해낸다.

CUBE: 다차원을 계산해낸다.

그룹함수에 대해서는 이것이 오라클이다 강의 이후에서 가르칩니다.

감사합니다.