[오라클]인덱스, 시퀀스, PL/SQL-프로시저의 개념, 스크립트

 

프로젝트 진행시 DB모델링이 제일 먼저 진행되야함 (TABLE생성(컬럼명, 테이블명, 제약조건 다 정해야됨))

---

INDEX

 

1. INDEX 생성 방법

 

2. INDEX 리스트 확인

이 리스트에 PK도 존재하는 것을 볼수있음. PK생성시 동일한 이름의 PK와 INDEX가 동시에 생성됨.

즉, PRIMARY KEY 를 만들게되면 INDEX도 같이 만들어진다.

인덱스의 종류는 CLUSTERED INDEX와 NON-CLUSTERED INDEX 두가지

오라클은 NON-CLUSTERED 형태의 인덱스이므로 정렬이 되지 않음.

MS-SQL은 CLUSTERED 인덱스므로 정렬되어 저장됨

 

＃ INDEX에 해당하는 컬럼 조회

3. NON-CLUSTERED INDEX

① 테이블 생성

② 데이터 삽입

③ 테이블 조회 : 정렬되지 않은 데이터 조회됨

 

---

 

일렬번호

 

1. SEQUENCE와 ROWNUM 의 차이점

① SEQUENCE(일렬번호) : 실제로 DB안에 집어넣는 데이터. 절대 중복값을 집어넣지 않음. 고유값을 가지게 함.

하지만 순수하게 일렬번호를 갖게하진 않음 1,101~... 이순서로도 입력 가능

생성방법은 간단. MAX+1..?

 

② ROWNUM : DB에 저장되어 있지 않음

 SELECT 하는 순간에만 ROWNUM 을 생성하는 것

 

2. 시퀀스

CREATE SEQUENCE DIV_DNO

START WITH 1      < 1부터 시작하겠다.

INCREMENT BY 1 < 1씩 증가

MAXVALUE 100 | NOMAXVALUE    < 최대값 설정

CYCLE | NOCYCLE                          < 싸이클 설정

CACHE | NOCACHE                         < 캐시 설정

 

- CYCLE : 1,2,3,4~100이 된 후 다시 1,2,3,4~

- NOCYCLE : 1,2,3,4~100이 된후 101번째 데이터입력시 데이터를 입력할 수 없다고 뜸

- 메모리에 미리 올려놓는 변수의 개수를 선언하는 것이 CACHE의 역할

 

 

3. 시퀀스 생성

 

4.시퀀스 조회

 

5. 시퀀스의 번호 조회. 조회하는 순간 1번 사용한 것으로 인식.

NEXTVAL : SEQUENCE의 다음번호 (SEQUENCE이름.NEXTVAL)

CURRVAL : SEQUENCE의 현재번호 (SEQUENCE이름.CURRVAL)

 

6. 시퀀스 수정

START WITH를 작성해주지 않아도 가능.

동일한 일렬번호를 다른 테이블 두개에 할당가능. 하지만 GAP 이 생긴다.

PERSONNEL 테이블, DIVISION 테이블 동일한 SEQUNECE를 줄수있음. 고유값을 가지게 되지만 시퀀스간의 GAP 발생

 

7. MS-SQL에서

레코드 추가시 1부터 시작해서 1씩 자동증가

레코드 추가시 1부터 시작해서 1씩 자동증가. 중복값 허용안함

 

---

 

PL/SQL (Lee사용자 계정으로 진행)

1. PL/SQL(Procedural Language/SQL)은 최근의 프로그래밍 언어의 특성을 수용한, SQL의 확장이라 할 수 있다. SQL의 데이터 조작(DML)과 질의문(QUERY)을 블록 구조에 절차적 단위(IF, LOOP, FOR등)로 된 코드를 포함할 수 있으며 절차적 프로그래밍을 가능하게 한 강력한 TRANSACTION 처리 언어이다.

 

2. 선언문, BEGIN, EXCEPTION으로 나뉨.

 

3. 트래픽 증가시 트랜잭션 처리속도가 느려질 수 밖에 없음.

 

PL/SQL은 여러 SQL문장을 BLOCK으로 묶고 한번에 BLOCK전부를 서버로 전송하기 때문에 통신량을 줄일 수 있다.

 

 

4.구조

① DECLARE

- variables, cursor, user_defined, exception

② BEGIN

- SQL,PL/SQL statements;            

▲여기서 SQL은 (쿼리문, DML,TCL:  SELECT, INSERT, UPDATE, DELETE, COMMIT, ROLLBACK, MERGE) 가능

③ EXCEPTION

- actions to perform when errors occur

④ END;

 

5. 블록의 유형

Anonymous - 관리자들이 많이 사용하는 형태. 스크립트문.

Procedure, Function -  일반 개발자가 많이 사용하는 형태.

 

6.변수선언

데이터의 타입을 가져올 수 있음. 변수명이 중요하지 않음. 명시적으로 특정 테이블의 컬럼 타입을 가져오므로 직관적으로 볼 수 있기 때문. 하나의 레코드값을 가질 저장공간을 만든 것.

A,B,C는 변수명, 사원TABLE의 사원명컬럼의 데이터타입을 가져온 것

 

---

익명의 프로시저 생성

실제로는 DBMS_OUTPUT.PUT_LINE(); 부분은 작성하지 않음.

WHY? 자바에서 코딩할 때 작업을 하기 때문에. 하지만 DB에서 확인하고 싶어서 기재한 것

SHOW ERROR;   CMD 창에서 실행 뒤 몇번째 라인에서 에러 발생했는지 확인가능

SET SERVEROUTPUT ON;  CMD창에서 PL/SQL 결과를 띄우겠는지 여부를 설정. (DEFAULT 값은 OFF )

 

---

무명의 프로시저 생성2

 

익명의 프로시저를 생성하고 SQL파일로 저장한 뒤 (SAVE 파일명.SQL),

해당 SQL파일을 불러올 경우(@파일명) 결과를 바로 볼 수 있음. 관리자가 많이 사용하는 이유.

 

---

익명의 프로시져 - 반복문 사용

---

저장PROCEDURE (기본적으로 가장 많이 사용하는 프로시저)

 

DB : 쿼리를 번역 -> 컴파일 -> 실행 하는 절차를 거침.

프로시저를 이용 :  번역 -> 컴파일 -> 컴파일한 내용을 저장해둠 -> 실행.

여러번 해당 프로시저를 반복하게 된다면 컴파일한 내용을 저장해뒀던 것을 호출하므로 속도가 빨라짐.

 

---

함수 생성

 

함수 실행

함수만 실행하고 싶다면, 함수는 반환값을 담을 변수가 필요함

변수(TAX) 선언 후 실행(EXEC) -> PRINT

 

---

프로시져는 원래 반환값이 없지만 함수처럼 반환값을 만들어 줄 수 있음.

 

---

프로시저 생성 - 전화번호 입력받아서 형식 바꾸기

 

---

프로시저 생성 - 주민등록번호 형식 변환

 

---

SCRIPT (sql파일로 생성해서 cmd 창에서 실행)

 

---

문제풀이

1.고객정보를 입력받아 해당 고객이 지금까지 주문한 총 횟수 구하기

----결과 : A001번 고객님의 총 주문횟수는 3번 입니다.

 

1-1. 스크립트

 

---

1-2.프로시져

 

---

2.직책을 입력받아 그직책의 급여의 총액,평균 월급,인원수를 검색하시오.

결과

과장의 급여총액: XXXXXX

과장의 평균 월급: XXXXXX

과장의 인원수 : XX명