Bohemian life

* implicit savepoints

 * Automatic commit

- DDL

- DCL

- 정상 종료

  * Automatic rollback;

- 비정상 종료

 drop table t1 purge;

  create table t1 (col1 number);

  insert into t1 values (1000);

  insert into t1 values (3000);

  insert into t1 values (2500);

  savepoint s1;

  insert into t1 values (2000);

  insert into t1 values (4200);

  savepoint s2;

  insert into t1 values (1800);

  insert into t1 values (2200);

  rollback to s1;

  insert into t1 values (3800);

  insert into t1 values (2900);

  commit;

- Read Consistency (읽기 일관성)

    1) Readers do NOT wait for writers

    2) Writers do NOT wait for readers

        [Session1]                                                 [Session2]

  -- Read consistency

  create table t_book -- DDL

  (no number,

   name varchar2(10));

  desc t_book

  insert into t_book values (1000, 'Jack1'); -- DML

  insert into t_book values (2000, 'Java');

  select * from t_book;

  NO NAME

----- ----------

1000 Jack1

2000 Java

desc t_book

select * from t_book;

no rows selected

  commit;

select * from t_book;

  NO NAME

----- ----------

1000 Jack1

2000 Java

  -- Lock

  update t_book

  set name = 'Luke'

  where no = 1000;

update t_book

set name = 'Lake'

where no = 1000;

|wait!

  rollback;

1 row updated.

rollback;

  -- Deadlock

  update t_book

  set name = 'Luke'

  where no = 1000;

update t_book

set name = 'James'

where no = 2000;

  update t_book

  set name = 'James'

  where no = 2000; 

  |wait!

update t_book

set name = 'Lake'

where no = 1000;

|wait!

  ORA-00060: deadlock detected 

  while waiting for resource

  commit;

1 row updated.

commit;

- an atomic unit of logical business rule

- ALL or NOTHING

1) one or more DMLs

2) one DDL

3) one DCL

  * Tx의 시작

- 첫번째 DML 문장의 실행

  * Tx의 종료

1) TCL: C, R

2) DDL: C, A, D, T, R, C ==> automatic commit

3) DCL: G, R ==> automatic commit

4) 환경 종료: SQL*Plus|iSQL*Plus 종료

  - 정상 종료:  exit, quit ==> automatic commit

  - 비정상 종료: 강제 종료, 닫힘 버튼, 시스템 다운 ==> automatic rollback

        1) DDL, No Rollback Information

2) Does not fire delete trigger --> faster than DELETE statement

3) can NOT trucate a table if it is referenced by children table (FK constraint)

서브쿼리를 이용해서 다른쪽값을이용해서 삭제

DELETE FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');

%Public% 이들어가는 department_name 의 행들의 department_id 삭제

가공이나 묶을땐 열단위
추가삭제변경은 전부 행단위
업데이트 범위를찾아서 열값을찾아 행을넣음

  drop table t1 purge;

  create table t1 as select * from emp;

  update t1

  set sal = sal + 1000, comm = nvl(comm, 0) + 100

  where deptno=30;

 - 서브쿼리를 이용한 update

  update t1

  set (sal, comm) = (sal + 100, nvl(comm, 0) + 10)

  where deptno = 10;

set (sal, comm) = (sal + 100, nvl(comm, 0) + 10)

                   *

ERROR at line 2:

ORA-01767: UPDATE ... SET expression must be a subquery

↓ ↓ ↓

  update t1

  set (sal, comm) = (select sal, comm from emp where ename = 'ALLEN')

  where deptno = 10;

  * implicit query --> http://goo.gl/QPFrI

: is a component of a DML statement that retrieves data without using a subquery.

        drop table c_emp purge;

create table c_emp

as

select * from emp where 1=2;

select * from c_emp;

insert into c_emp

 select *

 from emp;

select * from c_emp;

↓ ↓ ↓

drop table c_emp purge;

select * from c_emp;

create table c_emp

as 

select * from emp;

   cf.) 특정 컬럼만 복사하고 컬럼 이름 변경하여 테이블 생성

drop table p_emp purge;

create table p_emp

as select empno as emp_no, ename, sal as salary, deptno

  from emp;

desc p_emp

select * from p_emp;

 SQL> ed in_test

insert into t1 (col1, col2, col3)

 values (&c1, '&c2', '&c3');

select * from t1;

SQL> @in_test

※검색 변수 값은 알아서....

 1) NOT NULL 제약조건임에도 값을 생략하는 경우 

drop table t2 purge;

create table t2 (no number(3), name varchar2(20) not null);

desc t2

insert into t2 values (10, 'JAMES');

insert into t2 values (20, null);

insert into t2 values (20, null)

                           *

ERROR at line 1:

ORA-01400: cannot insert NULL into ("JAVA30"."T2"."NAME")

insert into t2 (no) values (20);

insert into t2 (no) values (20)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("JAVA30"."T2"."NAME")

  2) UNIQUE 제약조건인데 중복되는 값을 입력하는 경우

  3) FOREIGN KEY 제약조건 위배

  4) CHECK 제약조건 위배

  5) 데이터 타입 일치하지 않을 때

  6) 컬럼에 들어가는 데이터가 너무 클 때

alter session set nls_date_format = 'YYYY-MM-DD';

drop table t1 purge;

create table t1 (

 col1 number(4),

 col2 varchar2(10),

 col3 date);

desc t1

insert into t1

 values (1111, 'Hello', sysdate);

insert into t1

 values (2222, 'Oracler'); --> 에러: ORA-00947: 값의 수가 충분하지 않습니다

    - 명시적으로 null 값 입력

insert into t1

 values (2222, 'Oracler', to_date(null));

insert into t1

 values (3333, '2012-01-04', 'Good Boy!'); --> 에러: ORA-01841: 년은 영이 아닌 -4713 과 +4713 사이의 값으로 지정해야 합니다.

    - 암시적 형 변환

insert into t1

 values (3333, to_char(null), '2012-01-01');

    - 함수 사용 가능, 컬럼 명시

insert into t1 (col1, col2, col3)

 values (4444, 'Today', sysdate);

insert into t1 (col1, col2)

 values (5555, 'Choongang', '1963-07-07'); --> 에러: ORA-00913: 값의 수가 너무 많습니다

    - 암시적으로 null 값 입력

insert into t1 (col1, col2)

 values (5555, 'Choongang');

insert into t1 (col1, col3)

 values (6666, '2001-04-13');

insert into t1 (col2, col3, col1)

 values ('Valentine', '2012-02-14', 7777);

insert into t1 (c1, col2, col3)

 values (8888, 'Possible?', to_date(null)); --> 에러: ORA-00904: "C1": 부적합한 식별자

insert into t1

 values (8888, 'Possible?', to_date('10, 3월 2012', 'dd, MON YYYY'));

  COLUMN a_dummy NOPRINT                --------------------> a_dummy 열을 출력하지말아라.

  SELECT 'sing' AS "My dream", 3 a_dummy

  FROM dual

  UNION

  SELECT 'I''d like to teach', 1 a_dummy

  FROM dual

  UNION 

  SELECT 'the world to', 2 a_dummy

  FROM dual

  ORDER BY a_dummy;

SELECT employee_id, job_id, salary

  FROM   employees

  UNION

  SELECT employee_id, job_id, 0

  FROM   job_history;