[ORACLE] 오라클 기초 상식 - 초보자 강하게 추천 > db

<계정>

> SYS/CHANGE_ON_INSTALL   //오라클 시스템의 최상위 계정,

                     설치시의 비밀번호 (INSTALL_ON_INSTALL)을 꼭 변경하도록 권고하고 있다.

> SCOTT/TIGER

> INTERNAL/ORACLE    //V7에서 지원, 호환성 때문에 8i까지만 존재, 9i에 없슴

> SYSTEM/MANAGER    //SYS의 권한을 위임, DBA급 계정

 

- 접속중 사용계정 전환

SQL> CONN 사용자계정/비밀번호

 

- 사용자 계정 확인

SQL> CONN SYS/CHANGE_ON_INSTALL   //오라클 최상위 계정으로 변경(CONN=CONNECT)

SQL> DESC DBA_USERS             //사용자 TABLE의 구조 확인

SQL> SELECT USERNAME, PASSWORD FROM DBA_USERS;

                                                //사용자 테이블의 사용자 이름과 비밀번호 확인

 

<접속하기>

SQL> SQLPLUS SCOTT/TIGER   //SCOTT은 사용자계정, TIGER은 PASSWORD이다.

- 9i 접속방법

SQL> CONN SYS/CHANGE_ON_INSTALL AS SYSDBA //8I의 CONN INTERNAL/ORACLE과동일

C:\> SQLPLUS "SYS/CHANGE_ON_INSTALL AS SYSDBA"  //DOS모드에서(쌍 따옴표 사용)

 

<재접속하기>

C:\> SQLPLUS INTERNAL/ORACLE   //휴지인스턴스에 접속

SQL> STRATUP                                //ORACLE 인스턴스

SQL> EXIT                                       //DOS모드로 전환

C:\> SQLPLUS SCOTT/TIGHE          // 접속

 

<주석처리>

SQL> -- 주석문                     // 한줄주석 : -- 이후에 주석문을 작성

SQL> /*                               //주석시작

DOC> 이건 주석입니다.

DOC> 여러줄을 주석처리함

SQL> */                                 //주석종료

 

<SQL명령 파일로 저장 및 실행하기>

SQL> SAVE A1                        //A1파일에 바로 이전에 실행한 명령을 저장한다.

SQL> HOST                            //DOS모드로 전환

C:\> DIR A1.*                        //파일을 리스트하여 확인한다.

C:\> NOTEPAD A1.SQL          //파일내용을 NOTEPAD 로 확인한다.

C:\> EXIT                             //SQL모드로 전환

SQL> @A1                             //저장된 A1의 SQL문을 실행한다.

- 한개의 SQL문만 저장한다.

- 지정된 경로에 저장가능한다.(C:\A2, DEFAULT경로는 C:\ 이다)

- 저장된 파일내의 종결문자는 / 이다.

 

<SQL명령 편집 실행하기 또는 SCRIPT 생성>

SQL> ED                              //EDIT로 바로전에 실행한 한개의 명령만을 메모장으로 보여준다.

                                           //수정후 저장한 다음 닫는다.

SQL> /                                //수정된 SQL문을 실행한다.

 

SQL> ED AA                        //AA의 SCRIPT파일을 생성할 수 있다.

        AA.SQL 파일을 편집/저장한다.  여러개의 SQL문을 작성가능하다.

SQL> @AA                          //저장된 AA의 SQL문을 실행한다.

 

<Oracle 접속후 실행된 모든 내용을 파일로 저장하기>

SQL> SPOOL A3                  //A3파일을 생성한다.(C:\),

                                         //이후부터 실행된 모든내용 A3.LST 파일에 저장된다.

SQL> SPOOL OFF               //현재까지의 내용을 저장한다.(실행하지 않으면 저장안됨)

SQL> HOST                        //DOS모드로 전환

C:\> DIR *.LST                   // //파일을 리스트하여 확인한다.

C:\> NOTEPAD A3.LST          //파일내용을 NOTEPAD 로 확인한다.

- 존재하는 이름으로 SPOOL하여 파일 생성시 기존내용은 사라짐(덮어쓰기)

- Oracle을 종료하기 전에 SPOOL OFF를 실행하여야 저장됨

 

 

<DATABASE NULL의 의미>

- 0 이 아니다.

- 빈 SPACE가 아니다.

- 할당,연산 ,비교에 직접 참여할 수 없다.

- ? , ∞ 의 알수없음 의미

- 해당사항 없음 의미

* NULL IS DATA

* 숫자에 NULL을 연산하면 NULL이 된다.

 

 

<SINGLE QUOTE, DOUBLE QUOTE>

- SINGLE QUOTE(') 는 문자열

- DOUBLE QUOTE(")는 ALIAS(별칭에 사용)

   SQL> SELECT ENAME EN FROM EMP;

   SQL> SELECT ENAME "EN" FROM EMP;

   SQL> SELECT ENAME AS EN FROM EMP;

   SQL> SELECT ENAME AS "EN" FROM EMP;

* ""를 사용하면 별칭의 문자열 사이에 빈공간을 사용할수 있다.

 

<투버티칼(||)>

SQL> SELECT ENAME || '==' || JOB FROM EMP;

SQL> SELECT ENAME || '==' || JOB "ABC" FROM EMP;

- (ENAME || '==' || JOB) 는 하나의 컬럼명이 된다.

- "ABC"는 (ENAME || '==' || JOB)의 별칭이 된다.

 

 

<ESCAPE 사용>

SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE '%\%%' ESCAPE '\';

- ESCAPE '\'에 의해 \다음에 오는 문자는 일반문자로 인식한다.

 

<FUNCTION : SINGLE ROW FUNCTION,  MULTIPLE ROW FUNCTION>

- SINGLE ROW FUNCTION(SRF) 과 MULTIPLE ROW FUNCTION(MRF)이 있다.

1. SINGLE ROW FUNCTION  *****************************

    SINGLE ROW FUNCTION 는 반환값이 하나인 것을 말한다.

 

*************** 문자열

SQL> SELECT UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME) FORM EMP;

      - UPPER(ENAME)  : ENAME의 문자를 모두 대문자로 출력한다.

      - LOWER(ENAME)  : ENAME의 문자를 모두 소문자로 출력한다.

      - INITCAP(ENAME)  : ENAME의 첫문자만  대문자로 출력한다.

 

SQL> SELECT SUBSTR(ENAME,1,3), INSTR(ENAME,'A'), LENGTH(ENAME), CONCAT(ENAME,JOB), LPAD(SAL,10,'*'), RPAD(SAL,10,'*') FROM EMP;

      - SUBSTR(ENAME,1,3)  : ENAME의 문자열에서 첫글자부터 세글자를 출력한다.

      - INSTR(ENAME,'A')  : ENAME의 문자열에서 A의 위치를 숫자로 반환한다.

      - LENGTH(ENAME)  : ENAME의 길이를 숫자로 반환한다.

      - CONCAT(ENAME,JOB)  : ENAME 컬럼과 JOB 컬럼의 내용을 합쳐서 출력한다.

                        (인자는 두개만가능, 그이상은 ||을 사용)

      - LPAD(SAL,10,'*')  : SAL컬럼의 내용을 출력하고,

                                    10자리까지의 남는 공백을 * 문자로 왼쪽에 채워서 출력한다.

      - RPAD(SAL,10,'*')  :  SAL컬럼의 내용을 출력하고,

                                    10자리까지의 남는 공백을 * 문자로 오른쪽에 채워서 출력한다.

※ 예제 

SQL> SELECT LPAD(' ',SAL/100+1,'*') FROM EMP;

 

*************** 숫자

SQL> SELECT ROUND(45.129, 2), TRUNC(45.129,2) FROM EMP; (FROM DUAL)

      - ROUND(45.129, 2)  : 소수 두째자리에서 반올림한다.(45.13)

      - TRUNC(45.129, 2)  : 소수 두째자리까지만 출력한다..(45.12)

※ FROM EMP는 ROW를 만날때 마다 실행한다. FROM DUAL 은 한번만 실행한다.

 

SQL> SELECT 42+26/2*3 FROM DUAL;

SQL> SELECT MOD(SAL,COMM) FROM EMP;

     - MOD(SAL,COMM)  : SAL값을 COMM 으로 나눈 나머지 값을 반환한다.

 

*************** 날자

SQL> SELECT SYSDATE FROM DUAL;     //SYSDATE 시스템날자를 출력한다.(05/11/16)

SQL> SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE), ADD_MONTHS(HIREDATE, 6), NEXT_DAY(HIREDATE, '금'), LAST_DAY(HIREDATE) FROM EMP;

     - MONTHS_BETWEEN(SYSDATE, HIREDATE)

           : SYSDATE과 HIREDATE 사이의 월의 개수를 FLOAT형으로 반환한다.

     - ADD_MONTHS(HIREDATE, 6) : HIREDATE에 6개월을 더한 날자를 반환한다.

     - NEXT_DAY(HIREDATE, '금') : HIREDATE에서 가장 가까운 금요일의 날자를 반환한다.

     - LAST_DAY(HIREDATE) : HIREDATE의 포함된 달의 마지막 일자를 반환한다.

SQL> SELECT SYSDATE -1 FROM DUAL;    //어제일자

SQL> SELECT SYSDATE +1 FROM DUAL;    //내일일자

SQL> SELECT SYSDATE +3/24 FROM DUAL;    //오늘일자(의미없네.. 1보다 작으면 오늘)

SQL> SELECT TO_CHAR(SYSDATE, 'CC YYYY/MM/DD:HH24:MI:SS') FROM DUAL;

     -  CC : 세기

     -  YYYY : 년도

     -  MM : 월

     -  DD : 일

     -  HH24 : 시

     -  MI : 분

     -  SS : 초

 

※ 예제 

입사일로부터 6개월이 지난 달의 입사일 다음 금요일의 날자를 구하라.

SQL> SELECT NEXT_DAY(ADD_MONTHS(HIREDATE,6),'금') FROM EMP;

 

2. DOUBLE ROW FUNCTION *****************************

 

<NVL 사용>

- NULL 은 NVL FUNCTION을 이용하여 다른 값으로 대치될 수 있다.

- 사용가능 DADATYPE은 날자, 문자, 숫자형이다.

- NVL(COMM, 0)    //COMM이 NULL 인 경우 0을 반환한다.

                          (COMM, 0은 동일한 데이터타입 이여야한다.)

- NVL(HIREDATE, '05/01/01')  //HIREDATE이  NULL 인 경우 05/01/01를 반환한다.

- NVL(TITLE, 'NO TITLE YET') //TITLE이 NULL 인 경우 NO TITLE YET를 반환한다.

※ 예제 

SQL> SELECT ENAME, SAL*12+COMM FROM EMP;

          //COMM이 NULL인 경우 출력되지 않는다.

SQL> SELECT ENAME, SAL*12+NVL(COMM, 0) FROM EMP;

         //COMM이 NULL인 경우에도 출력된다.

SQL> SELECT NVL(TO_CHAR(MGR,'9999'),'CEO') FROM EMP;

        //MGR이 NULL인 경우 CEO를 출력한다.

 

<DECODE 사용>

※ 예제 

SQL> SELECT DECODE(MGR,NULL,'CEO',MGR) "MGR" FROM EMP;

        //MGR이 NULL이면 CEO를 반환하고 아니면 MGR을 반환한다.

SQL> SELECT COUNT(*) TOTAL,
         SUM(DECODE(TO_CHAR(HIREDATE,'YYYY'),'1980',1,0) ) "1980",
         SUM(DECODE(TO_CHAR(HIREDATE,'YYYY'),'1981',1,0) ) "1981",
         SUM(DECODE(TO_CHAR(HIREDATE,'YYYY'),'1982',1,0) ) "1982",
         SUM(DECODE(TO_CHAR(HIREDATE,'YYYY'),'1987',1,0) ) "1987"
         FROM EMP; 

 

 

<형 변환>

- TO_NUMBER : CHARACTER를 NUMBER로 변환

- TO_CHAR : NUMBER나 DATE를 CHARACTER로 변환

- TO_DATE : CHARACTER를 DATE로 변환

* 사용형식 TO_CHAR('변환전', '변환형식')

※ 예제 

SQL> SELECT SYSDATE-TO_DATE('1980/01/01', 'YYYY/MM/DD') FROM DUAL;

SQL> SELECT TO_CHAR(SYSDATE, 'CC YYYY/MM/DD:HH24:MI:SS') FROM DUAL;

SQL> SELECT ENAME, TO_CHAR(SAL, '$99,999') FROM EMP;

         //콤마(,)는 자릿수 구분, 마침표(.)는 소수점, 원화(\)는 $대신 영문자 L 를 쓴다.

 

 

<정렬: ORDER BY>

SQL> SELECT EMPNO, ENAME "EN", SAL FROM EMP ORDER BY EMPNO; //컬럼명

         SELECT EMPNO, ENAME "EN", SAL FROM EMP ORDER BY EN;      //ALIAS(별칭)

         SELECT EMPNO, ENAME "EN", SAL FROM EMP ORDER BY 3;   //POSITION(컬럼번호)

- 정렬은 컬럼명, 별칭, 컬럼번호 로 정렬할 수 있다.

 

<그룹화: GROUP BY>

GROUP BY 절에 기술되는 항목은 반드시 SELECT절에 존재하는 컬럼이어야 하고,

그룹으로 묶인 다음에 컬럼에는 SUM이나 COUNT 와 같은 그룹함수가 반드시 따라와야 한다.

그렇지 않으면 SQL문장에 에러가 발생한다.

 

<그룹함수 : MULTIPLE ROW FUNCTION>

 

- COUNT(*) : NULL 값을 포함한 모든 행의 개수를 반환

- COUNT(컬럼명) : NULL아닌 레코드의 개수반환

- MIN(컬럼명) : 최소값

- MAX(컬럼명) : 최대값

- SUM(컬럼명) : 합계

- AVG(컬럼명) : 평균

 

<SUBQUERY>

- 주의사항

1. ()반드시 괄호안에

2. SINGLE ROW SUBQUERY는

   반드시 싱글연산자:SINGLE ROW ORERATOR(=,<>,>,>=,<=)를 사용한다.(SRS -> SRO)

2. MULTIPLE ROW SUBQUERY는

   반드시 싱글연산자:MULTIPLE ROW ORERATOR(IN, >ALL, >ANY)를 사용한다.(MRS -> MRO)

3. SUBQUERY 안에서는 ORDER BY 사용안함

   (단,8I R2부터 FROM절 사용, SUBQUERY ORDER BY 사용가능)

※

SRS : SINGLE ROW SUBQUERY - 반환값이 한개의 ROW

MRS : MULTIPLE ROW SUBQUERY - 반환값이 다수개의 ROW

SRO : SINGLE ROW ORERATOR (=,<>,>,>=,<=)

MRO : MULTIPLE ROW ORERATOR (IN, >ALL, >ANY)

 

PAIR : 반환값을 한쌍으로 반환하여 비교한다. -  IN( (800,7788),(1600,7369))

NONPAIR : 반환값을  개별로 반환하여 비교한다.  - IN (100, 200, 300 )

 

※ 예제

- JONES 사원보다 급여가 많은 사원명, 급여 출력하라.

SQL> SELECT ENAME, SAL
         FROM EMP
         WHERE SAL >= (SELECT SAL
                                 FROM EMP
                                 WHERE ENAME='JONES');

-

SQL> SELECT ENAME, SAL

         FROM EMP

         WHERE (SELECT SAL

                      FROM EMP

                      WHERE='JONES') <= SAL;

 

- PAIR : 반환값을 한쌍으로 반환하여 비교한다. -  IN( (800,7788),(1600,7369))

SQL> SELECT EMPNO, ENAME, SAL, COMM

         FROM EMP

         WHERE (SAL, NVL(COMM,-1)) IN (SELECT SAL, NVL(COMM,-1)

                                                           FROM EMP

                                                           WHERE DEPTNO=30);

 

- NONPAIR : 반환값을  개별로 반환하여 비교한다.  - IN (100, 200, 300 )

SQL> SELECT EMPNO, ENAME, SAL

         FROM

         WHERE SAL IN ( SELECT SAL

                                  FROM EMP

                                  WHERE DEPTNO=30)

          AND NVL(COMM, -1) IN (SELECT NVL(COMM, -1)

                                               FROM EMP

                                               WHERE DEPTNO=30);

 

<테이블조인 : JOIN>

- 카르테시안 조인(Cartaesian Join), 내부조인(Inner Join), 외부조인(Outer Join)으로 구분

1. 카르테시안 조인

    →조인된 테이블 사이에 조건이 걸리지 않을 경우에 발생(100*1000=100,000)하며,

       테이블간의 모든 경우의 수에 대해서 로우가 생성되는 조인방식이다.

    ※ 예제

    - 두 개 테이블의 모든 경우의 수를 가져와라.

    SQL> SELECT DNAME, ENAME FROM DEPT, EMP;

 

2. 내부조인

    →조인되는 테이블간에 서로 연결되는 컬럼이 존재하여 이것에 의해 연결된 로우의 데이더가

       출력된다.

    ※ 예제

    - 부서와 사원 테이블에서 부서별로 존재하는 사원이름을 조회하라.

    SQL> SELECT A.DNAME, B.ENAME

             FROM DEPT A, EMP B

             WHERE A.DEPTNO = B.DEPTNO

             ORDER BY A.DNAME ASC;

 

3. 외부조인

    내부조인을 사용할 경우 항상 양쪽에 데이터가 연결되는 데이터값이 존재해야

    테이블에 데이터를 가져올 수 있다.

    예를 들어, A 테이블에는 데이터가 존재하지만, B 테이블에는 연결되는 컬럼값에 대해 데이터가

    존재할 수도 있고, 안 할 수도 있다. 이때 B 테이블의 연결 컬럼이 있든 없든 A 테이블의 값을 가

    져오면서 B와 연결된 값이 있을 경우 B 의 값을 가져오는 경우가 있다. 이때 B 테이블에 연결되

    는 컬럼값이 NULL인지를 OR조건으로 비교하는 문장이 추가되거나 UNION이나 UNION ALL을

    사용하여 처리한다.

 

    →조인되는 테이블에서 B 테이블에 연결되는 컬럼값이 존재하지 않더라도(즉 NULL 값)

       A 테이블의 데이터를 가져올 수 있는 조인 방법이다.

       오라클에서는 외부조인을 위해 '+' 기호를 이용한다.

    ※ 예제

    SQL> SELECT A.DEPTNO, A.DNAME, B.ENAME

             FROM DEPT A, EMP B

             WHERE A.DEPTNO = B.DEPTNO(+);

           

     - 외부조인은 참조하는 테이블이나 참조되는 테이블 어느 곳에 사용해도 된다.

     - 오라클의 경우 양쪽 모두 외부조인을 사용하면 에러가 발생한다.

     - OR, IN, SUBQUERY 와 함께 사용할 수 없다.

     - 조인이 여러개의 컬럼에 걸리거나 조건이 들오올 경우 모든 컬럼에 대해 '+'기호를 표현한다.

        하나라도 빠뜨리면 외부조인은 정상적으로 실행되지 않으며 에러는 발생하지 않는다.

     - 외부조인이 걸린 테이블이 두개 이상의 테이블과 조건이 걸리면 외부조인이 실행되지 않는다.

 

 4. 기타

    - NONEQUI JOIN

    ※ 예제

    SQL> SELECT E.ENAME, S.GRADE

             FROM EMP E, SALGRADE S

             WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

         // (E.SAL BETWEEN S.LOSAL AND S.HISAL)는 NONEQUI JOIN이다.

  

    - SELF JOIN

      SELF JOIN은 한 테이블내에서 컬럼과 컬럼을 비교 또는 연산을 위해 사용된다.

    ※ 예제

    사원과 그 사원의 직속상사 이름을 출력하라.

    SQL> SELECT E.ENAME, C.ENAME

             FROM E, EMP C

             WHERE E.MGR = C.EMPNO;

 

    - 조인 조건은 조인되는 테이블 개수가 N개면 N-1이 된다.

 

<집합연산 : SET OPERATOR>

1. UNION : 두 개의 테이블에서 조회하여 중복을 제외한 모든 ROW 출력

                  (합집합A∪B = (A+B)-(A∩B))

    - 두 테이블에 동일한 수의 컬럼이 존재해야 한다. 컬럼의 이름은 달라도 무관하다.

    - 두 테이블에서 조회하는 테이터의 타입이 서로 일치해야 한다.

2. UNION ALL : 두 개의 테이블에서 조회하여 중복을 포함한 모든 ROW 출력(A+B)

3. INTERSECT : 두 개의 테이블에 모두 존재하는 데이터에 대해 한 ROW만 출력(교집합A∩B)

4. MINUS : 첫번째 테이블에만 존재하는 데이터에 대해 한 로우만 출력(A-B)

※ 예제

SQL> SELECT EMPNO, ENAME, SAL, JOB FROM EMP

         UNIION

         SELECT EMPNO, ENAME, SAL, '---' FROM EMP_HIS;

       //'---' 는 없는 컬럼을 대신한다.

 

 

<치환변수>

- SQL*PLUS에서만 지원한다.

- 변수명 앞에 &을 사용한다.

- logout 까지 사용하려면 &&을 사용한다.

- 변수자리에 맞는 내용의 값을 입력한다.

- 재 login후 사용하려면 script을 생성한다.

 

- 치환변수 값을 확인 하려면 SQL> DEFINE <엔터>

- 치환변수 값을 수정 하려면 SQL> DEFINE 변수명="VALUE"<엔터>

- 치환변수 삭제 하려면 SQL> UNDEFINE 변수명 <엔터>

- 치환변수 값을 프롬프트로 받고 지정 하려면

         SQL> ACCEPT 변수명 PROMPT  '사원번호 컬럼명 입력 : '

※ &&, ACCEPT, DEFINE은 logout하면 모두 사라진다.(sql*plus에서만 사용가능)

 

SQL> SELECT * FROM  &tab1;  

        tab1의 값을 입력하시오: 테이블명 <엔터>

 

SQL> SELECT &col1 FROM &tab1 ORDER BY &col1;

        col1의 값을 입력하시오: 컬럼명 <엔터>

        tab1의 값을 입력하시오: 테이블명 <엔터>

        col1의 값을 입력하시오: 컬럼명 <엔터>

       //치환변수를 만날때 마다 물어본다.

 

SQL> SELECT &&col1 FROM &tab1 ORDER BY &col1;

        col1의 값을 입력하시오: 컬럼명 <엔터>

        tab1의 값을 입력하시오: 테이블명 <엔터>

       //&&col1 변수는 다음에 오는 동일한 변수에 바로 적용된다.

       //두번째 col1의 값을 다시 물어 보지 않는다.(환경변수에 등록됨,define 확인)       

 

 

<환경변수>

- 모든 환경변수 설정확인 SQL> SHOW ALL <엔터>

- 개별 환경변수 설정확인 SQL> SHOW 키워드 <엔터>

- 환경변수 값 변경 SQL> SET 키워드 수정값 <엔터>

※ 환경변수는 logout 하면 소멸된다.

- 재사용방법

1. SCRIPT로 저장하여 사용

2. DIRECTORY 경로: ORA81/SQLPLUS/ADMIN/ glogin.sql 파일을 수정(모든 사용자에게반영)

※ 예제

SQL> SHOW USER               //현재사용자 확인

SQL> SET NULL "널이네"     //변수값수정(DEFAULT : SET NULL "")

SQL> SET LINESIZE 100

SQL> SET PAGESIZE 20       //해딩과 해딩사이의 ROW 수

SQL> SET SQLPREFIX "|"    //(DEFAULT : #),SQL문 작성중간에 다른 SQL문 실행

SQL> SET SQLPROMPT "A.SQL>"  //프롬프트 변경

SQL> SET TIMING ON  //처리되는 시간 출력(ON/OFF)

 

<TCL(TRANSACTION CONTROL LANGUAGE) : 트랜잭션 컨트롤>

 

- 트랙잭션이란 데이터베이스에 행해지는 작업의 논리적인 단위(Logical Unit Of Work)이다.

                     성격은 ALL 또는 NOTHING 이다.

 

- 하나의 트랙잭션의

   →시작 : 실행가능한 첫번째 SQL문이 실행되면 시작된다.

   →종료 : 1. COMMIT 또는 ROLLBACK 실행

               2. DDL 또는 DCL 문 실행(자동 커밋)

               3. 사용자 종료(정상 종료시 자동 커밋)

               4. 시스템 고장(비정상 종료시 자동 롤백)

 

- 트랙잭션 컨트롤 문장은 COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

     COMMIT :  데이터베이스에 DML작업내용을 반영한다.

     ROLLBACK :  데이터베이스에 DML작업내용을 취소한다.

     SAVEPOINT :  긴 트랜잭션 중간 중간에 롤백을 대비하여 기록한다.

     SET TRANSACTION :  트랜잭션의 종류를 읽기 전용으로 할 것인지

                                     읽기/쓰기로 할 것인지 지정한다.

 

- ROLLBACK Segment (다른 말로 Undo Segment)

  오라클은 update 실행시 본래의 값을 R/S에 복사해 놓고 SET으로 지정한 값을 변경한다.

  이후에 롤백을 하면 R/S에 복사해 놓은 값을 원래의 장소에 복원한다.

 

- AUTO COMMIT 과 AUTO ROLLBACK

   →AUTO COMMIT : DDL, DCL 문 실행시, 정상 종료시(EXIT)

   →AUTO ROLLBACK : 비정상 종료시

 

- 두 세션간의 관계

  A와 B 두 세션이 동시에 같은 자원에 대한 UPDATE시 나중에 접근한 세션은 LOCK에 걸린다.

  (무한대기 상태가 된다.) 먼저 접근한 세션이 커밋 할지  롤백할지 모르는 상태.

  :먼저 접근한 세션이 COMMIT 또는 ROLLBACK하면 나중에 들어온 세션의 SQL문이 실행된다.

  :A가 업데이트 후에 A가 SELECT 검색시 반영된 값이 보이나 B는 반영되지 않은 값이 보인다.

 

  DEADLOCK은 두 세션간에 상대의 자원을 할당 받으려고 양쪽 모두 대기중인 상태로

  이때는 오라클에서 자동으로 어느 한쪽의 마지막 명령만 취소하고 ERROR 메시지를 출력해 준다.

  해결방법은 A와 B가 업무의 시차를 두거나 업무이관을 통해 해결한다.

 

※ LOCK은 TRANSACTION 간에 파괴적인 행위를 막기위한 오라클 보호 매커니즘이다.

    :무한대기, 오라클 AUTO 실행, 최저LEVEL, ROW(동일한 로우 갱신시), DML LOCK의 종류

   

※ 예제

SQL> COMMIT;     //현재까지의 작업을 DB에 반영

SQL> ROLLBACK;  //현재까지의 DML작업을 취소

 

SQL> SAVEPOINT A1;         //트랙잭션 중간에 A1라는 표시자를 남긴다.

SQL> ROLLBACK TO A1;     //A1까지 롤백한다.

 

 

<TABLE 확인>

- TABLE은 사용자 TABLE과 관리정보 TABLE(DATA DICTIONARY/관리용)으로 구분된다.

 

- DICTIONARY TABLE의 내용을 출력

SQL> SELECT * FROM DICT;   //(851개 출력:8i)(DICT=DICTIONARY)

 

- 사용자 TABLE의 이름을 출력(SQL의 SHOW TABLES)

SQL> CONN SCOTT/TIGER      //SCOTT사용자로 변경

SQL> SELECT TABLE_NAME FROM USER_TABLES;  //SCOTT 사용자의 테이블 조회

SQL> DESC EMP;          //테이블의 구조확인

 

 

<접두사 4가지 의미>

1. USER_  : 연결된 사용자에 의해 생성된 정보

2. ALL_    : 내가 권한있는 것에 대한 정보

3. DBA_   : 관리정보

4. V$       : 동적성능

 

<TABLE명, COLUMN명, ALIAS명 규칙>

1. a~z, A~Z, 0~9, _ , $ 사용가능

2. 길이는 1~30자까지

3. 첫글자는 반드시 영문

4. 의미있게 생성

5. 예약어는 사용안됨

 

<TABLE 생성>

※ 예제

- 일반

SQL> CREATE TABLE EMPS(

         EMPNO    NUMBER(4),        //네자리의 정수

         ENAME    VARCHAR(20),      //크기는 4000까지 가능

         SAL         NUMBE(7,2),        //전체 7자리로 두자리는 소수자리로 사용

         HIRE        DATE));               //날자타입

 

- 다른 테이블의 결과를 받아서 생성

SQL> CREATE TABLE COPY_EMP

         AS

         SELECT * FROM EMP;

        //EMP 테이블의 모든 내용을 받아서 COPY_EMP 테이블을 생성한다.

 

- 테이블의 구조만을 받아서 생성

SQL> CREATE TABLE COPY_EMP1

         AS

         SELECT * FROM KEMP

         WHERE 0 = 1;  //0 과 1일 같은 경우가 없기 때문에 구조만 생성된다.

                              //또는 WHERE EMPNO IS NULL;

 

 

       

 

        

 

<무결성 : CONSTRAINT>

- 무결성을 유지하기 위한 제약조건 정의 방법은 COLUMN LEVEL 정의방식 과 TABLE LEVEL 정의방식이 있다.

- TABLE 생성시

-- 1. COLUMN LEVEL 정의방식 : 컬럼명 바로 뒤에 정의
--    컬럼명 DATATYPE 제약종류 

-- 2. TABLE LEVEL 정의방식 : 테이블의 뒤에 정의
--    CONSTRAINT 제약명 제약종류 (컬럼명)
--    제약명은 생략가능하다.

 

 

CREATE TABLE DEPT1(
DEPTNO    NUMBER(2) PRIMARY KEY,
DNAME     VARCHAR2(20) UNIQUE, 
LOC       VARCHAR2(20));

CREATE TABLE DEPT2(
DEPTNO    NUMBER(2),
DNAME     VARCHAR2(20),
LOC       VARCHAR2(20),
CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY (DEPTNO),
CONSTRAINT DEPT2_DEPTNO_UK UNIQUE (DNAME));

CREATE TABLE EMP1(
EMPNO NUMBER(4) CONSTRAINT EMP1_EMPNO_PK PRIMARY KEY,
ENAME  VARCHAR2(20) NOT NULL,
SAL NUMBER(7,2),
DEPTNO NUMBER(2),
CONSTRAINT EMP1_SAL_CK CHECK (SAL BETWEEN 500 AND 5000),
CONSTRAINT EMP1_DEPTNO_FK FOREIGN KEY (DEPTNO)
  REFERENCES DEPT1(DEPTNO) ON DELETE CASCADE);

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,
TABLE_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('EMP1','DEPT1');

 

COL CONSTRAINT_NAME  FORMAT A15;
COL TABLE_NAME FORMAT A15;
COL SEARCH_CONDITION FORMAT A15;