6주차 , 튜플의 추가, 입력취소 , NULL, 변수이용 , 인용부호 , 특수값삽입 , 시퀀스값 , 갱신 , 트랜잭션관리

==============
TUPLE의 추가(37쪽)
==============

INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES (50,'DEVELOPMENT', 'DETROIT');

SELECT * FROM DEPT;

입력사항 취소하기
==============
ROLLBACK;
SELECT * FROM DEPT;

이제 다시 시도하여 입력을 완료해보자
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES (50,'DEVELOPMENT', 'DETROIT');

SELECT * FROM DEPT;

입력사항 확정하기
==============
COMMIT;

ROLLBACK;
SELECT * FROM DEPT;

-----> 이제 취소할 수가 없다!!!

입력 내용이 저장되는 단계를 확인해보자
먼저 SCOTT계정에 조회  권한을 주자

GRANT SELECT ON DEPT TO SCOTT;

INSERT INTO DEPT
VALUES (70,'PURCHASING', 'ATLANTA');
----> SCOTT계정에서 확인해보자

COMMIT;
----> SCOTT계정에서 확인해보자


** NULL값의 입력 **

명시적 방법
INSERT INTO DEPT(DEPTNO, DNAME)
VALUES (65, 'MIS');

암시적방법
INSERT INTO DEPT
VALUES (80,'FINANCE', NULL);


변수 이용하기

INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES (&DEPTID, &DEPARTNAME, &LOCATION);


입력시 인용부호를 안쓰려면...

INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES (&DEPTID, '&DEPARTNAME', '&LOCATION');


다른테이블의 내용 복사

CREATE TABLE MANAGER (
 ID                 NUMBER(4) PRIMARY KEY,
 NAME               VARCHAR2(10),
 SALARY             NUMBER(7,2),
 HIREDATE           DATE);

INSERT INTO MANAGER(ID, NAME, SALARY, HIREDATE)
            (SELECT EMPNO, ENAME, SAL, HIREDATE
     FROM EMP
     WHERE JOB='MANAGER');
특수값(시스템의 CLOCK값) 삽입

INSERT INTO EMP
VALUES (7196,'GREEN','SALESMAN',7782,SYSDATE,2000,NULL,10);

---

TAEHO>INSERT INTO EMP
  2  VALUES(7196,'GREEN','SALESMAN',7782,SYSDATE,2000,NULL,10);

---

=======================
SEQUENCE : 자동으로 유일번호 생성
=======================
CREATE SEQUENCE dept_deptno
   INCREMENT BY 1
   START WITH 91
   MAXVALUE 100
   NOCYCLE
   NOCACHE;

SELECT dept_deptno.NEXTVAL FROM DUAL;  
---> 한번 수행할 떄 마다  값을 증가시킴 *

SELECT dept_deptno.CURRVAL FROM DUAL;
---->  현재값만을 조회 (NEXTVAL이 선행되어야 함) 

---

TAEHO>CREATE SEQUENCE dept_deptno
  2  INCREMENT BY 1
  3  START WITH 91
  4  MAXVALUE 100
  5  NOCYCLE
  6  NOCACHE;

시퀀스가 생성되었습니다.

TAEHO>SELECT dept_deptno.CURRVAL FROM DUAL;
SELECT dept_deptno.CURRVAL FROM DUAL
       *
1행에 오류:
ORA-08002: 시퀀스 DEPT_DEPTNO.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다


TAEHO>SELECT dept_deptno.NEXTVAL FROM DUAL;

   NEXTVAL
----------
        91

TAEHO>SELECT dept_deptno.CURRVAL FROM DUAL;

   CURRVAL
----------
        91

---

INSERT INTO dept(deptno,dname,loc)
VALUES (dept_deptno.NEXTVAL, 'FINANCE', 'SAN FRANCISCO');
SELECT * FROM DEPT;

---

TAEHO>SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 DEVELOPMENT    DETROIT
        70 PURCHASING     ATLANTA
        65 MIS
        80 FINANCE        NULL
        92 FINANCE        SAN FRANCISCO
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

9 개의 행이 선택되었습니다.

---

시퀀스 값의 원상 복구
1. 시퀀스를 삭제 한 후 다시 생성
     DROP SEQUENCE dept_deptno;

2. 다음과 같은 방법도 있음

ALTER SEQUENCE dept_deptno INCREMENT BY -3;
SELECT dept_deptno.NEXTVAL FROM DUAL      /* INCREMENT BY -3를 적용함*/;
ALTER SEQUENCE dept_deptno INCREMENT BY 1;


========================================
갱신 (UPDATE, 38쪽)

UPDATE EMP
SET DEPTNO =20, SAL =500
WHERE EMPNO = 7782;
SELECT * FROM EMP;

---

TAEHO>UPDATE EMP
  2  SET DEPTNO=20 , SAL = 500
  3  WHERE EMPNO = 7782;

1 행이 갱신되었습니다.

TAEHO>SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7196 GREEN      SALESMAN        7782 22/10/04       2000                    10
      7369 SMITH      CLERK           7902 80/12/17        800                    20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/09/09        500                    20
      7788 SCOTT      ANALYST         7566 82/12/09       3000                    20
      7839 KING       PRESIDENT            81/11/17       5000                    10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7876 ADAMS      CLERK           7788 83/01/12       1100                    20
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    10
      7934 MILLER     CLERK           7782 82/01/23       1300                    10
      7950 LEE_HK     MANAGER         7839 85/10/09       2450       2000         10

16 개의 행이 선택되었습니다.

TAEHO>

---

만약 다음과 같이 수행하면???
UPDATE EMP
SET DEPTNO =60, SAL =500
WHERE EMPNO = 7782;

-----> 참조무결성 위배

---

TAEHO>UPDATE EMP
  2  SET DEPTNO = 60 , SAL = 500
  3  WHERE EMPNO = 7782
  4  ;
UPDATE EMP
*
1행에 오류:
ORA-02291: 무결성 제약조건(TAEHO.SYS_C0012129)이 위배되었습니다- 부모 키가 없습니다


TAEHO>

---

주의!! 조건이 생략되면 모든행을 갱신

UPDATE EMP
SET DEPTNO =20, SAL =500;

서브퀴리에 의한 갱신

---

TAEHO>UPDATE EMP
  2  SET DEPTNO = 20 , SAL = 500
  3  ;

16 행이 갱신되었습니다.

TAEHO>

---

UPDATE EMP
SET (JOB,DEPTNO) = (SELECT JOB,DEPTNO
    FROM EMP
    WHERE EMPNO =7499)
WHERE EMPNO = 7698;

ROLLBACK;
========================================

행의 삭제  ( 38쪽)

DELETE FROM DEPT
WHERE DEPTNO = 50;


DELETE FROM DEPT
WHERE DEPTNO = 30;
------> 참조무결성 위배의 예

ROLLBACK;


========================================
트랜잭션 관리

시스템의 변동사항을 모은 것을 트랜잭션이라고 함.
INSERT/DELETE/UPDATE는 시스템에 변동을 가져온다.

변동에 대한 처리 방법 --->트랜잭션 관리


1) 시작 : 실행가능한 SQL문장(INSERT/DELETE/UPDATE)이 처음 실행될때
2) 종료 : 다음의 이벤트 중 어느하나
-COMMIT/ROLLBACK (명시적종료)
-DDL/DCL의 실행 (자동커밋)
-사용자 종료(커밋)
-시스템 파손(롤백)

COMMIT;

SAVEPOINT : 일정시점에 MARKER를 표시한다
예
SAVEPOINT T1;
...
ROLLBACK TO T1;

예제
UPDATE EMP SET SAL =1 WHERE EMPNO = 7369;
SAVEPOINT T1;

UPDATE EMP SET SAL =2 WHERE EMPNO = 7369;
SAVEPOINT T2;

UPDATE EMP SET SAL =3 WHERE EMPNO = 7369;
SAVEPOINT T3;
 
UPDATE EMP SET SAL =4 WHERE EMPNO = 7369;
SAVEPOINT T4;

UPDATE EMP SET SAL =5 WHERE EMPNO = 7369;

ROLLBACK TO T3;
SELECT * FROM EMP;

ROLLBACK TO T1;
SELECT * FROM EMP;

ROLLBACK;
SELECT * FROM EMP;



연습문제
=====================================================================
1. 다음과 같이 테이블 EMP_SUMMARY를 생성하시오.

        EMPNO    NUMBER(4),
        ENAME    VARCHAR2(10),
        JOB      VARCHAR2(9),
        INCOME   NUMBER(7, 2),
        DNAME    VARCHAR2(14))

   단, PRIMARY KEY는 EMPNO로 지정할 것!

-----> 다음을 수행
CREATE TABLE EMP_SUMMARY
       (EMPNO NUMBER(4) PRIMARY KEY,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        INCOME NUMBER(7, 2),
        DNAME VARCHAR2(14));

---

TAEHO>CREATE TABLE EMP_SUMMARY(
  2  EMPNO NUMBER(4) PRIMARY KEY,
  3  ENAME VARCHAR2(10),
  4  JOB VARCHAR2(9),
  5  INCOME NUMBER(7,2),
  6  DNAME VARCHAR2(14));

테이블이 생성되었습니다.

---

자! 이제
   테이블 EMP, DEPT로부터 INCOME과 부서이름을 구하여 EMP_SUMMARY에 튜플을 삽입하시오
   단, 연봉은 SAL*12 + COMM 이며 COMM이 NULL이면 0으로 처리

---

TAEHO>INSERT INTO EMP_SUMMARY(
  2  SELECT e.EMPNO, e.ENAME,e.JOB,e.SAL*12 + NVL(e.COMM,0), d.DNAME
  3  FROM EMP e, DEPT d
  4  WHERE e.DEPTNO=d.DEPTNO);

16 개의 행이 만들어졌습니다.

TAEHO>SELECT * FROM EMP_SUMMARY;

     EMPNO ENAME      JOB           INCOME DNAME
---------- ---------- --------- ---------- --------------
      7196 GREEN      SALESMAN       24000 ACCOUNTING
      7782 CLARK      MANAGER        29400 ACCOUNTING
      7934 MILLER     CLERK          15600 ACCOUNTING
      7902 FORD       ANALYST        36000 ACCOUNTING
      7839 KING       PRESIDENT      60000 ACCOUNTING
      7950 LEE_HK     MANAGER        31400 ACCOUNTING
      7369 SMITH      CLERK           9600 RESEARCH
      7876 ADAMS      CLERK          13200 RESEARCH
      7566 JONES      MANAGER        35700 RESEARCH
      7788 SCOTT      ANALYST        36000 RESEARCH
      7499 ALLEN      SALESMAN       19500 SALES
      7521 WARD       SALESMAN       15500 SALES
      7654 MARTIN     SALESMAN       16400 SALES
      7900 JAMES      CLERK          11400 SALES
      7698 BLAKE      MANAGER        34200 SALES
      7844 TURNER     SALESMAN       18000 SALES

16 개의 행이 선택되었습니다.

TAEHO>SELECT * FROM EMP_SUMMARY ORDER BY EMPNO);
SELECT * FROM EMP_SUMMARY ORDER BY EMPNO)
                                        *
1행에 오류:
ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다


TAEHO>SELECT * FROM EMP_SUMMARY ORDER BY EMPNO;

     EMPNO ENAME      JOB           INCOME DNAME
---------- ---------- --------- ---------- --------------
      7196 GREEN      SALESMAN       24000 ACCOUNTING
      7369 SMITH      CLERK           9600 RESEARCH
      7499 ALLEN      SALESMAN       19500 SALES
      7521 WARD       SALESMAN       15500 SALES
      7566 JONES      MANAGER        35700 RESEARCH
      7654 MARTIN     SALESMAN       16400 SALES
      7698 BLAKE      MANAGER        34200 SALES
      7782 CLARK      MANAGER        29400 ACCOUNTING
      7788 SCOTT      ANALYST        36000 RESEARCH
      7839 KING       PRESIDENT      60000 ACCOUNTING
      7844 TURNER     SALESMAN       18000 SALES
      7876 ADAMS      CLERK          13200 RESEARCH
      7900 JAMES      CLERK          11400 SALES
      7902 FORD       ANALYST        36000 ACCOUNTING
      7934 MILLER     CLERK          15600 ACCOUNTING
      7950 LEE_HK     MANAGER        31400 ACCOUNTING

16 개의 행이 선택되었습니다.

---

2. SMITH의 연봉을 ALLEN의 연봉으로 수정하시오

---

TAEHO>UPDATE EMP_SUMMARY
  2  SET INCOME = ( SELECT INCOME FROM EMP_SUMMARY WHERE ENAME='ALLEN')
  3  WHERE ENAME = 'SMITH';

1 행이 갱신되었습니다.

---

3. SAL이 2000 이상인 직원을 EMP_SUMMARY에서 삭제하시오

---

TAEHO>DELETE FROM EMP_SUMMARY
  2  WHERE EMPNO IN (SELECT EMPNO FROM EMP WHERE SAL >= 2000);

8 행이 삭제되었습니다.

---

=======================================

4. 주문번호 자동발생(다음주까지 수행)

우선 주문 테이블을 다음과 같이 생성

CREATE TABLE 주문 
    (주문번호 CHAR(12));

아래처럼 주문번호를 자동으로 삽입이 되도록 INSERT문을 만드시오


SELECT * FROM 주문;

주문번호
------------
20201014001
20201014002
20201014003
20201014004
20201014005
20201014006
20201014007
20201014008
20201014009
20201014010
20201014011
11 개의 행이 선택되었습니다.

---

TAEHO>CREATE TABLE 주문
  2  (주문번호 CHAR(12));

테이블이 생성되었습니다.

TAEHO>CREATE SEQUENCE 주문번호발생기
  2  INCREMENT BY 1
  3  START WITH 1
  4  MAXVALUE 100
  5  NOCYCLE
  6  NOCACHE;

시퀀스가 생성되었습니다.

TAEHO>INSERT INTO 주문 VALUES (to_char(sysdate, 'YYYYMMDD')||(to_char(주문번호발생기,NEXTVAL,'FM000'));
INSERT INTO 주문 VALUES (to_char(sysdate, 'YYYYMMDD')||(to_char(주문번호발생기,NEXTVAL,'FM000'))
                                                                                               *
1행에 오류:
ORA-00917: 누락된 콤마


TAEHO>INSERT INTO 주문 VALUES (to_char(sysdate, 'YYYYMMDD') || to_char(주문번호발생기.NEXTVAL, 'FM000'));

1 개의 행이 만들어졌습니다.

TAEHO>SELECT * FROM 주문;

주문번호
------------
20221004002

---

====================================================================