TIL 정리_117(Oracle 문법2)

https://ranprog.tistory.com/138

Oracle 문법의 내용을 보충 및 추가 

 

---

Global Temporary Table(임시테이블 생성)

실제 데이터는 저장하지 않는다 작업을 마치면 자동으로 지운다

 

ON COMMIT delete ROWS;

커밋을 하면 데이터가 삭제된다

 

-> 같은 계정이어도 다른 창으로(세션) 검색하면 내용이 보이지 않는다

 

 

읽기 전용 테이블로 변경하기

alter table 테이블명 read only;

 

Join 두 개의 테이블을 엮는다

Oracle join 문법

select a col1, b col1 from table1 a, table2 b where a.col2 = b.col2;

 

ANSI join (범용 문법)

select a.col1, b.col2 from table1 a INNER JOIN table2 b ON a.col2 = b.col2;

 

Cartesian Produce(카티션 곱)

조인 대상 테이블들의 조건이 누락되었을 경우 발생. 해당 조인에 참여하는 모든 대상 행을 다 출력한다

데이터를 복제하고자 하는 경우 일부러 사용하기도 한다

 

 

EQUL join(등가 join)

emp 테이블과 dept 테이블을 조회하여 출력

 

Oracle

select e.empno, e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;

 

ANSI

select e.empno, e.ename, d.dname from emp e join dept d ON e.deptno = d.deptno;

 

**잘못 입력한 경우 r 혹은 /을 치면 수정한 내용이 반영되고 다시 실행된다

7번라인 오류라고 나오는 경우 7 (한칸 띄운 후 수정한 내용을 입력)

r 또는 >/을 입력 후 enter를 치면 수정한 내용이 반영되어 다시 실행된다

 

 

NON-Equi Join(비등가 join)

=를 사용하지 않고 사이에 있거나 크거나 작은것을 조회한다

 

Oracle

select s.name "STU_NAME", o.total "SCORE", h.grade "CREDIT" from student s, score o, hakjum h where s.studno = o.studno and o.total >= h.min_point and o.total <= h.max_point;

 

Ansi

select s.name "STU_NAME", o.total "SCORE", h.grade "CREDIT" from student s JOIN score o ON s.studno = o.studno and score o JOIN hakjum h ON o.total >= h.min_point and o.total <= h.max_point;

 

 

OUTTER Join

 

Oracle outer join

select s.name "STU_NAME", p.name "PROF_NAME" from student s, professor p where s.profno = p.profno(+);

 

Ansi

select s.name "STU_NAME", p.name "PROF_NAME" from student s LEFT OUTER JOIN professor p ON s.profno = p.profno;

>>oracle은 where에서 + 부호를 붙이고 ANSI는 from 절에서 JOIN으로 결정한다

기준이되는 컬럼에 따라 LEFT OUTER JOIN / RIGHT OUTER JOIN으로 나눠진다 

 

 

SELF Join

하나의 테이블에서 컬럼을 불러온다 -> 자기 자신과 Join

 

Oracle 

select e1.ename "ENAME", e2.ename "MGR_ENAME" from emp e1 , emp e2 where e1.mgr = e2.empno;

 

ANSI

select e1.ename "ENAME", e2.ename "MGR_ENAME" from emp e1 JOIN emp e2 ON e1.mgr = e2.empno;

 

---

제약조건 관리하기

제약조건 DISABLE(해제) 하기 -> DISABLE NOVALIDATE 사용

alter table 테이블명 DISABLE NOVALIDATE CONSTRAINT SYS_코드; 

-> NOVALIDATE의 경우 이미 들어간 데이터는 변경이 되지 않는다(관여x) 이후부터 제약이 적용된다

 

ENABLE VALIDATE는 이미 할당된 데이터에도 제약이 적용된다

-> 이미 잘못된 데이터가 존재할때 enable validate를 하는 경우 오류가 난다

 

 

INDEX 인덱스

order by는 정렬은 되지만 부하가 많다 -> 실행을 하고나서 정렬이 된다 (후 정렬) 

index를 사용한다 -> 데이터를 집어 넣을 때 미리 정렬을 해서 집어넣는다 (선 정렬)

 

인덱스 구조와 작동 원리(B-TREE 인덱스 기준) : 주소를 미리 만들어 놓는다

 

1 UNIQUE INDEX 문법 

CREATE UNIQUE INDEX 인덱스명 ON 테이블 이름(컬럼명 1 ASC|DESC, 컬럼명, ....);

 

2. NON UNIQUE INDEX : 중복이 허용된다 1의 문법에서 unique를 제외

CREATE index idx_dept2_area on dept2(area);

 

3. DESCENDING INDEX(내림차순)

CREATE INDEX idx_prof_pay ON professor(pay DESC); 

 

인덱스 생성

CREATE 인덱스 인덱스_테이블명_컬럼 on 테이블(컬럼);

 

 

View

뷰는 하나 이상의 테이블로부터 유도되어 만들어진 가상 테이블로 처리 과정중의 중간 내용이나 기본 테이블 중 일부 내용을 검색해 보여주거나 별도로 관리하고자 하는 임시 테이블이다

 

복합 뷰 (Complex View)

뷰를 만들 때 join을 이용하여 여러 개의 테이블을 조회한다

create or replace view v_emp as select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;

 

Inline View(인라인 뷰)

뷰를 만들지 않고 sql 문장의 from절에 view의 서브쿼리를 넣는다

select e.deptno, d.dname, e.sal from (select deptno, MAX(sal) sal from emp group by deptno) e, dept d where e.deptno = d.deptno;

 

Materialized View(MVIEW) 구체화 된 뷰 //Express 버전에서는 사용할 수 없는 기능이다

일반 뷰는 유저가 불러올 때마다 일일이 새로 불러왔지만 M뷰는 한 번 불러오면 계속 데이터를 읽을 수 있다

 

일반 view는 인덱스를 만들 수 없지만 MView는 데이터가 실제로 존재하기 때문에 만들 수 있다

create index idx_m_prof_pay on m_prof(pay);

 

 

Sequence

지정한 옵션에 따라 순서대로 번호를 만들어주는 객체이다

-> 시리얼 번호를 자동으로 만드는 기능이다

CREATE sequence sequence_name

 

 

Synonym 동의어

여러 사람이 데이터베이스를 공유 할 때 동의어를 만들어서 사용한다 시스템 계정에서 권한을 부여해야 사용 가능하다

create public synonym d2 for dept; //다른 사람들도 접근 가능

 

 

PL/SQL (오라클)

• Procedural language/sql이라는 의미이다 
• 문법은 begin으로 시작한다
• 오라클에서 제공하는 프로그래밍 언어로 데이터 베이스 업무를 처리하기에 최적화된 언어이다

• pl/sql안에 sql 문장이 존재한다
• 선언부, 실행부, 예외처리부로 구성된다
• declare(선언부) 모든 변수, 상수 선언
• Executable 실행부 제어문, 반복문, 함수정의 등의 로직을 기술한다
• Exception 예외처리 실행도중 에러 발생시 해결하는 문장들을 기술한다
• Anonymous PL/SQL Block과 Stored PL/SQL Block이 있다 (익명 블럭과 저장된 블럭)

 

**PL/SQL은 기본적으로 처리된 결과를 출력하지 않기 때문에 set serveroutput on; 을 활성화 해야한다

 

 

PL/SQL 커서

• 오라클 서버에서 sQL문을 실행할때마다 처리를 위한 메모리 공간을 사용한다
• -> 커서를 사용한다 
• 묵시적 커서 : 자동생성, 1행만 저장가능 사용자는 생성 유무를 알 수 없다
• 명시적 커서 : 직접 생성, 여러 개의 행을 처리하고자 할 떄 사용한다
• 커서이름%rowcount 
• 선언(CURSOR) - 오픈(OPEN) - 데이터추출(fetch) - 사용종료(close)를 해야한다

 

 

Trigger (PL/SQL)

참조 관계에 있는 두 테이블에서 하나의 테이블에 삽입, 삭제, 갱신 등의 연산으로 테이블의 내용이 바뀌었을 때
데이터의 일관성과 무결성 유지를 위해서 이와 관련된 테이블도 연쇄적으로 변경이 이루어질 수 있도록 한다

-> 특정 사건이 발생될때마다 묵시적으로 해당 PL/SQL 블럭이 실행된다