23/01/09

✏️<DML과 트랜잭션 연습문제>

• 새로운 터미널 열어서 실행
  
• 본래의 터미널로 돌아와서 lab_08_01.sql 가져오기
  

✔ 트랜잭션 시작
3. insert (첫번째 행)

insert into my_employee
values (1, 'Patel', 'Ralph', 'rpatel', 895);

4. insert (두번째 행)

insert into my_employee
values (2, 'Dancs', 'Betty', 'bdancs', 860);

5. select (미리보기)

select *
from my_employee;

6. insert + 치환변수(&) (세번째 행)
   => load_emp.sql 스트립트 파일 생성(저장)

SQL> insert into my_employee
     values (&id, '&lname', '&fname', '&uid', &sal); 
SQL> save /home/oracle/load_emp.sql

7. load_emp.sql 스크립트 파일을 활용 insert (네번째 행)

SQL> @/home/oracle/load_emp.sql 

8. select (미리보기)

select *
from my_employee;

9. commit;
   ✔ 트랜잭션 종료

✔ 트랜잭션 시작
10. update

update my_employee
set last_name = 'Drexler'
where id = 3;

11. update

update my_employee
set salary = 1000
where salary < 900;

12. select (미리보기) 4개
13. delete

delete from my_employee
where first_name = 'Betty';

14. select (미리보기) 3개
15. commit;
    ✔ 트랜잭션 종료

✔ 트랜잭션 시작
16. load_emp.sql 활용 insert (다섯번째 행)

SQL> @/home/oracle/load_emp.sql

17. select (미리보기)
18. savepoint 생성 (저장점 생성)

savepoint test1;

19. delete (모든행 삭제)

delete from my_employee;

20. select (미리보기)
21. savepoint로 롤백

SQL> rollback to test1;

22. select (미리보기)
    
23. commit;
    ✔ 트랜잭션 종료

---

📌 10. 데이터 검색을 위한 Data Dictionary 사용

📂 Data Dictionary의 구성요소

1. Base tables

• DB의 모든 정보를 DBMS가 보기 쉬운 형태로 기록해 놓은 테이블

2. Data dictionary views

• Base tables을 사용자가 보기 쉬운 형태로 만들어 놓은 뷰
• 읽기전용(select만 가능)
• Data dictionary views의 유형
  1) user_xxx : 내가 소유한 object 정보 조회
  (실시간X)     ex) user_object, user_tables, user_views, user_users 등
  2) all_xxx : 내가 소유하거나 접근 가능한 object 정보 조회
  (실시간X)     ex) all_objects, all_tables 등
  3) dba_xxx : DB의 모든 정보 조회, DBA만 select 가능함.
  (실시간X)     ex) dba_tables, dba_users, dba_data_files 등
  4) v$xxx : DB의 모든 정보 조회, DBA만 select 가능함.
  (실시간O)     ex) v$table, v$session, v$datafile 등

---

<SQL 활용>

📗 데이터정의어(DDL) : Object(객체) 종류

✔ Table
◽ view
◽ Sequence
◽ Index
◽ Synonym

---

📌 1. 데이터정의어(DDL) - Table

• DDL(데이터정의어) : create table, alter table, drop table, truncate table
                         => 작업단위 : object(객체) = table
                         => autocommit 내포 함. (자동저장됨)

[참고] 데이터 모델링
요구사항 수집 및 분석 -> 개념모델링(ERD)
-> 논리모델링(구체화된 ERD, 테이블차트) -> 물리모델링(create table ---;)

📁 테이블 생성(create table)

create table dept
(deptno number(2),
 dname varchar2(14),
 loc varchar2(13),
 create_date date default sysdate);

• 필수 : 테이블명, 컬럼명, 데이터타입, 컬럼사이즈
• 옵션 : default값, 제약조건
• 데이터타입 : 숫자 - 정수형 : number(p)
                          - 실수형 : number(p,s)
                             ex. a number(10,2) = 99999999.99
                             ex. b number(2,2) = 0.99
                   문자 - 고정형 : char(n) = 성능 good
                          - 가변형 : varchar2(n) = 공간효율 good
                   날짜 - 년/월/일 : date
                          - 년/월/일/시/분/초 : timestamp

📁 제약조건 : Primary key, Foreign key, Unique, Not Null, Check

[제약조건 선언 시기]

• table 생성 시 : create table ----;
• table 생성 후 : alter table ----;

[제약조건명 지정]

• 사용자(개발자) 지정 : 고유하고 의미있는 제약조건명 부여를 권장함
  ex) 테이블약자_컬럼약자_제약조건유형약자
      emp_eid_pk
      emp_lname_nn
      dept_locid_fk

• 사용자(개발자) 생략 : Oracle이 고유한 제약조건명 자동 부여함

[제약조건 선언 문법]

• 컬럼레벨의 문법
  

• 테이블레벨의 문법
  

➀ NOT NULL 제약조건

• 컬럼 레벨의 문법만 가능
• null값이 들어올 수 없는 제약조건
• 필수 정보인 컬럼에 not null 제약조건 사용함

 create table test1
 ( id number(10) constraint t1_id_nn not null, 
   name varchar2(30) constraint t1_name_nn not null,
   job varchar2(20), email varchar2(20),
   phone varchar2(20) constraint t1_ph_nn not null,
   start_date date );

➁ UNIQUE 제약조건

• 고유한 값만 들어올 수 있는 제약조건, 중복된 값은 들어올 수 없는 제약조건 (null은 여러개 들어와도 가능! )
• 주민번호, 전화번호, 메일주소, 학번, 사번 등의 컬럼에 활용됨

 create table test2
 ( id number(10) constraint t2_id_nn not null 
 				 constraint t2_id_uk unique, 
   name varchar2(30) constraint t2_name_nn not null, 
   job varchar2(20), 
   email varchar2(20), 
   phone varchar2(20) constraint t2_ph_nn not null 
 				      constraint t2_ph_uk unique, 
   start_date date, 
   constraint t2_email_uk unique(email) );

➂ PRIMARY KEY 제약조건

• 기본키 제약조건
• NOT NULL + UNIQUE의 성격을 가짐
• 테이블당 한번만 선언 가능함!
  

create table test3
( id number(10) constraint t3_id_pk primary key, 
  name varchar2(30) constraint t3_name_nn not null, 
  job varchar2(20), email varchar2(20), 
  phone varchar2(20) constraint t3_ph_nn not null
  					 constraint t3_ph_uk unique,
  start_date date, 
  constraint t3_email_uk unique(email) );

➃ FOREIGN KEY 제약조건

• 외래키 제약조건
• 정의 : 자기 자신 테이블이나 다른 테이블의 특정 컬럼(PK, UK)을 참조하는 제약조건
  

➄ CHECK 제약조건

• 광범위하게 활용되는 제약조건
• 컬럼이 만족해야하는 조건문을 자유롭게 지정하는 제약조건

[ex1] salary number(10), 
	  constraint OOO check (salary > 0), 
		(==) 
	  salary number(10) constraint OOO check (salary > 0),

[ex2] 주민번호 varchar2(13), 
	  constraint OOO check (length(주민번호) = 13), 
		(==) 
	  주민번호 varchar2(13) constraint OOO check (length(주민번호) = 13),

[ex3] 성별 varchar2(10), 
	  constraint OOO check (성별 in ('남', '여')), 
		(==) 
	  성별 varchar2(10) constraint OOO check (성별 in ('남', '여')),

📁 제약조건 정의하여 테이블 생성

create table employees1
( employee_id number(6) 
	constraint emp1_employee_id primary key,
  first_name varchar2(20),
  last_name varchar2(25)
  	constraint emp1_last_name_nn not null,
  email varchar(25)
  	constraint emp1_email_nn not null,
    constraint emp1_email_uk unique,
  phone_number varchar2(20),
  hire_date date
  	constraint emp1_hire_date_nn not null,
  job_id varchar2(10)
  	constraint emp1_job_nn not null,
  salary number(8,2)
  	constraint emp1_salary_ck check(salary>0),
  commission_pct number(2,2),
  manager_id number(6)
  	constraint emp1_manager_fk references
    employees1 (employee_id)
  department_id number(4)
  	constraint emp1_dept_fk references
    departments (department_id));