[Oracle] DCL / DDL / DML (Day12)

위 글은 해당 카테고리의 수업 강의 자료를 정리한 것입니다. 

 

 

 

 

1. DCL: 계정 관리

• 계정 관리
  
  • 사용자 생성: CREATE USER user IDENTIFIED BY passwd;
  • 비밀변호 변경: ALTER USER user IDENTIFIED BY passwd;
  • 사용자 삭제: DROP USER user [CASCADE];
• 주의
  • 일반적으로 DBA의 일
  • 사용자를 생성하려면 CREATE USER 권한 필요
  • 생성된 사용자가 Login하려면 CREATE SESSION 권한 필요
  • 일반적으로 CONNECT, RESOURCE의 ROLE을 부여하면 일반 사용자 역할을 할 수 있음
• 관리자 계정으로 접속: sqlplus system/manager (connect system/manager)
• 계정 생성: create user webdb identified by 1234;
• 접속 권한 부여: grant resource connect to webdb;
• 계정 비밀번호 변경: alter user webde identified by webdb;
• 계정 삭제: drop user webdb cascade;

오라클 12c 이상부터 계정 이름 앞에 c##을 붙여줘야 공통 사용자를 생성 가능 

create user c##계정이름 identified by 계정비번; // 계정 생성

grant connect, resource, dba to c##계정이름; // 계정 권한 부여

 

+매계정 생성 마다 하기 싫음

SQL> ALTER SESSION SET "_ORACLE_SCRIPT" = true; (세션이 변경되었습니다.)

 

 

 

2. DDL: 테이블 관리

• 테이블 생성

create table book(
    book_id number(5),
    title varchar2(50),
    author varchar2(10),
    pub_date date
);

• 테이블, 컬럼 명명 규칙
  • 문자로 시작
  • 30자 이내
  • A-Z, a-z, 0-9, _, $, #
  • 오라클 예약어는 사용할 수 없음

 

 

• 오라클 자료형

Data type	Description
CHAR(size)	고정길이 문자열 (최대 2000byte)
VARCHAR2(size)	가변길이 문자열 ()
NUMBER(p,s)	숫자 데이터 / p(전체 자리수), s(소수점 이하 자리수) 자리수 지정 없으면 NUMBER(38)
DATE	날짜+시간
NCHAR(size)	national character set에 따라 결정되는 size 만큼의 고정 길이 character data로 최대 200byte까지 가능, 디폴트는 1 character
NVARCHAR2(size)	national character set에 따라 결정되는 size 만큼의 가변길이character data로 최대 4000 byte까지 가능하며 반드시 길이를 정해 주어야 함
LONG	가변 길이 character data로 최대 2 giga byte까지 가능
RAW (size)	가변 길이 raw binary data로 최대 2000 까지 가능하며 반드시 길이를 주어야 함
LONG RAW	가변길이 raw binary data로 최대2 giga byte까지 가능
BLOB	Binary data로 4 giga byte까지 가능
CLOB	Single-byte character data로 4 giga byte까지 가능
NCLOB	national character set까지 포함한 모든 character data로 4 gigabyte까지 가능
BFILE	외부 파일로 저장된 binary data로 4 giga byte까지 가능
ROWID	Row의 물리적 주소를 나타내는 binary data로 extended rowid 는 10 byte, restricted rowid는 6 byte 길이
TIMESTAMP	Date값을 미세한 초 단위까지 저장. NLS_TIMESTAMP_FORMAT 형식으로 처리
INTERVAL YEAR TO MONTH	두 datetime 값의 차이에서 YEAR와 MONTH값 만을 저장
INTERVAL DAY TO SECOND	두 datetime 값의 차이를 DAY, HOUR, MINUTE, SECOND 까지 저장

 

• 컬럼 추가

ALTER TABLE book ADD (pubs VARCHAR2(50));

 

• 컬럼 수정

ALTER TABLE book MODIFY (title VARCHAR2(100));

 

• 컬럼 삭제

ALTER TABLE book DROP (author);

 

• 테이블명 수정:

 

• 테이블 삭제:

 

 

• 제약조건
  • NOT NULL: NULL 값 입력 불가
  • UNIQUE: 중복 값 입력 불가 (NULL값은 허용)
  • PRIMARY KEY
    • NOT NULL + UNIQUE
    • 즉, 데이터들끼리 유일성을 보장하는 칼럼에만 설정
    • 테이블당 1개만 설정 가능 (여러 개를 묶어서 설정 가능)
  • FOREIGN KEY (외래키)
    • 일반적으로 REFERENCE 테이블의 PK를 참조
    • REFERENCE 테이블에 없는 값은 삽입 불가
    • REFERENCE 테이블의 레코드 삭제시 동작
  • CHECK: 설정된 값만 입력 가능

 

예제: author 테이블 만들기

CREATE TABLE author(
author_id NUMBER(10),
author_name author_desc VARCHAR2(100) NOT NULL, 
PRIMARY KEY(author_id)VARCHAR2(500),
);

예제: book 테이블 만들기

CREATE TABLE book (
book_id NUMBER(10),
title VARCHAR2(100) NOT NULL,
pubs VARCHAR2(100),
pub_date DATE,
author_id NUMBER(10),
PRIMARY KEY(book_id),
CONSTRAINT c_book_fk FOREIGN KEY (author_id) REFERENCES author(author_id)
);

 

 

 

3. DML: INSERT / UPDATE / DELET

 

3.1. DML (INSERT)

• 묵시적 방법
  • 컬럼 이름, 순서를 지정하지 않음
  • 테이블 생성시 정의한 순서에 따라 값을 지정

INSERT INTO author
VALUES (1, '박경리', '토지 작가 ' );

• 명시적 방법
  • 컬럼 이름 명시적으로 사용
  • 지정되지 않은 컬럼 NULL 자동 입력

INSERT INTO author( author_id, author_name ) VALUES (2, '이문열' );

 

3.2. DML (UPDATE)

• 조건을 만족하는 레코드를 변경
  • 컬럼 이름, 순서를 지정하지 않음
  • 테이블 생성시 정의한 순서에 따라 값 지정

UPDATE author
SET author_name = ‘기안84’,
author_desc = ‘웹툰작가’ WHERE author_id = 1 ;

 

• WHERE 절이 생략되면 모든 레코드에 적용 (주의)

UPDATE author
SET author_name = ‘강풀’,
author_desc = ‘인기작가’ ;

 

 

3.3. DML (DELETE)

• 조건을 만족하는 레코드를 삭제

DELETE FROM author
WHERE author_id = 1 ;

 

• 조건이 없으면 모든 데이터 삭제 (주의)

DELETE FROM author ;

 

 

 

4. SEQUENCE / SYSDATE

 

4.1. SEQUENCE (시퀀스)

• 연속적인 일렬번호 생성 ➡️ 주로 PK에 사용

 

• 시퀀스 생성

CREATE SEQUENCE seq_author_id
INCREMENT BY 1
START WITH 1 ;

 

• 시퀀스 사용

INSERT INTO author
VALUES (seq_author_id.nextval, '박경리', '토지 작가 ' );

INSERT INTO author
VALUES (seq_author_id.nextval, '이문열', '삼국지 작가');

 

• 시퀀스 객체 리스트 조회

SELECT * FROM USER_SEQUENCES;

 

• 현재 시퀀스 조회

SELECT seq_author_id.currval FROM dual;

 

• 다음 시퀀스 조회

SELECT seq_author_id.nextval FROM dual;

 

• 시퀀스 삭제

DROP SEQUENCE seq_author_id;

 

 

 

4.2. SYSDATE

• 현재 시간이 입력됨

INSERT INTO board
VALUES (1, ‘게시판제목', '본문내용‘, SYSDATE );