[SQL] Oracle

오라클 데이터베이스의 개념과 구조

📚 데이터베이스 개념

유용한 데이터의 집합
데이터 베이스에 저장된 정보는 검색, 수정, 삭제에 용이

📚 데이터베이스 정의

• 통합된 데이터 (Integraged Data)
  똑같은 데이터가 원칙적으로 중복되어 있지 않다는 것
• 저장된 데이터 (Stored Data)
  컴퓨가 접근할 수 있는 기억장치에 저장된 데이터 (주로 하드디스크 저장)
• 운영 데이터 (Operational Data)
  존재 목적이 명확하고 유용성을 지닌 데이터
  단순한 데이터를 모아둔 개념이 아닌 같은 목적이 분명한 데이터여야 함
• 공용 데이터 (Shared Data)
  여러 사용자들이 서로 다른 목적으로 사용하는 공유 가능한 데이터

📚 데이터베이스 특징

• 실시간 접근성 (Real-time Accessability)
  다수의 사용자의 요구에 대해 처리 시간이 몇 초를 넘기지 말아야 함
• 지속적인 변화 (Continous Evolution)
  데이터베이스에 저장된 데이터는 최신 정보가 정확하게 저장되어 처리되어야 함
• 동시 공유 (Concurrent Sharing)
  동일한 데이터를 동시에 서로 다른 목적으로 사용할 수 있어야 함
• 내용에 대한 참조
  데이터베이스 내에 있는 데이터 레코드들은 주소나 위치에 의해 참조되는 것이 아닌
  가지고 있는 값에 따라 참조해야 함

📚 데이터베이스 관리 시스템(DBMS)

• 방대한 양의 데이터를 편리하게 정장하고 효율적으로 관리, 검색할 수 있는 환경 제공 시스템 소프트웨어
• 데이터를 공유해 정보의 체계적인 활용 가능
• 응용 프로그램과 데이터베이스의 중재자로, 모든 응용프로그램들이 데이터베이스를 공용할 수 있게 관리

📚 관계형 데이터베이스 관리 시스템(RDBMS)

• 가장 일반적인 형태의 DBMS
• 데이터정보를 테이블 형태로 저장
• 테이블은 2차원 형태의 표처럼 볼 수 있도록 행(ROW)와 열(COLRUMN)으로 구성
  ex ) Oracle, Sybase, Infomix, MySQL, Acess, SQL Server

• 장점
  작성과 이용이 비교적 쉽고 확장 용이
  처음 데이터베이스를 만든 후 관련되는 응용프로그램들을 변경하지 않고도, 새로운 데이터 항목을 데이터 베이스에 추가 가능

📚 데이터 딕셔너리(Data Dictionary : DD)

• 관계형 데이터베이스에서 객체를 정의하게 되면 그 객체가 가진 메타데이터의 정보가 저장되는 곳
• 사용자에 의해서 추가, 삭제, 수정되지 못하여 오로지 오라클 시스템에 의해서만 가능

📚 SQL(Structed Query Language)

• 사용자와 관계형 데이터베이스르 연결시켜주는 표준 검색 언어

---

오라클 데이터베이스의 저장 구조

📚 데이터 처리

📚 오라클 데이터베이스 구조

📚 오라클 서버(Oracle Server)

통합적인 정보관리를 위한 객체 관계형 데이터베이스 관리 시스템
ORACLE Intance(메모리+프로세스)와 데이터베이스(물리적공간데이터)로 구성

• ORACLE Intance(메모리+프로세스)

---

📚 데이터베이스 객체

데이터베이스 내에 존재하는 논리적인 저장 구조
오라클과 같은 DBMS가 데이터를 관리하려면 데이터를 어딘가에 저장해 관리해야 하는데,
이런 목적을 달성하기 위해 필요한 모든 논리적인 저장 구조가 데이터베이스 객체이다.

📚 데이터베이스 객체 종류

📖 테이블(Table)

• 테이블(Table)이란?
  Schema Objects의 하나로 관계형 데이터베이스를 구성하는 기본 데이터 구조
  행과 열의 구조를 가지며 테이블을 이용하여 데이터를 입력, 수정, 삭제, 추출 등을 한다.

💡 Schema Object란?
Schema는 Schema Objects의 모임이며 오라클에서 사용자를 스키마라고 부른다.
스키마 오브젝트에는 TABLES, INDEXES, VIEWS, SEQUENCES, SYNONYM, CLUSTERS, DATABASE LINK, PROCEDURES, FUNCTIONS, PACKAGE등이 해당된다.

• 테이블(Table)생성
  테이블 생성은 테이블에 대한 구조(칼럼 및 데이터타입, 제약조건)를 정의하고 저장공간을 할당하는 과정
  DDL(Data Definition Language)명령중 하나인 Create Table 명령어를 사용

CREATE TABLE 테이블명(
	컬럼명1 테이터_타입 [ 칼럼에 값이 안들어 오는 경우 사용되는 칼럼의 기본값 ][칼럼의 제약조건(NOT NULL등)],
	컬럼명2 테이터_타입 [ 칼럼에 값이 안들어 오는 경우 사용되는 칼럼의 기본값 ][constrinats],
...
);

ex )

create table addrbook1 (
      name varchar2(10) not null,
      address varchar2(50),
      age number default 0
    );

• 테이블(Table) 구조 변경
  DDL(Data Definition Language)명령인 ALTER TABLE 명령을 사용하며 주로 컬럼타입의 변경, 길이의 변경, 칼럼 추가, 칼럼 삭제 등과 같은 일을 한다.

• 컬럼 추가
  sql ALTER TABLE table_name ADD (column_name data_type [DEFAULT expr]…
• 컬럼 변경
  sql ALTER TABLE table_name MODIFY (column_name data_type [DEFAULT expr]…)
• 컬럼 삭제
  sql ALTER TABLE table_name DROP COLUMN column_name
• 테이블 이름변경 및 삭제, 주석추가

RENAME old_table_name TO new_table_name
DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS]

📖 뷰 (View)

• 뷰 (View)란?
  하나 이상의 테이블을 연결해 마치 테이블인 것 처럼 사용하는 객체
  하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체입니다.
  실제 데이터는 뷰를 구성하는 테이블에 담겨 있지만 마치 테이블처럼 사용할 수 있습니다.
  또한 테이블 뿐만 아니라 다른 뷰를 참조해 새로운 뷰를 만들어 사용할 수 있습니다.

• 뷰 (View) 사용 목적
  여러테이블의 JOIN과 GROUP BY 같은 같은 복잡한 쿼리를 view로 저장시켜놓으면 다음부터는 저장한 view의 정보만 가져오면 되므로 쉽게 좀 더 편리하게 사용할 수 있습니다.
  뷰는 데이터 보안에서도 유리하다.
  뷰를 보면 컬럼과 데이터만 공개되므로 원천 테이블은 알 수 없습니다.

• 뷰 (View) 생성 및 수정

--문법
CREATE OR REPLACE VIEW [스키마.][뷰 NAME] AS
SELECT문;

ex )

--예제
CREATE OR REPLACE VIEW v1
AS
    SELECT empno
         , ename
         , job
         , hiredate
      FROM emp
;

뷰 생성 및 수정은 CREATE OR REPLACE VIEW
CREATE는 생성, REPLACE는 수정인데 CREATE OR REPLACE라고 명시해줬을경우,
타겟 뷰이름이 없을경우 CREATE를 하고 있을 경우 REPLACE를 함

• 뷰 (View) 삭제

--문법
DROP VIEW [스키마.][뷰 NAME]

ex )

--예제
DROP VIEW V1;

뷰는 다른 테이블을 참조만 하고 있을 뿐이므로
뷰를 삭제하더라도 실제 뷰가 참조하고있는 테이블에 있는 데이터는 삭제되지 않음.

• 뷰 (View) 구조 확인

--문법
desc [스키마.][뷰 NAME];

ex )

--예제
desc V1;

📖 인덱스

테이블에 있는 데이터를 빠르게 찾기 위한 객체
데이터가 위치한 장소의 정보를 가진 일종의 주소록
데이터의 주소(ROWID)를 가지고 있다.
인덱스는 한 개 이상의 컬럼으로 만들 수 있으며, 하나의 테이블에 여러 개의 인덱스를 생성할 수 있다.
인덱스가 많이 생성되어 있으면, SELECT는 빠를 수 있지만 데이터 변경(INSERT, UPDATE, DELETE)은 느려질 수 있으므로 주의

• 인덱스 생성

CREATE [UNIQUE] INDEX [스키마명.]인덱스명
      ON [스키마명.]테이블명 (컬럼1 [, 컬럼2, 컬럼3, ...])
-- 대괄호 생략 가능
--ex )
-- 단일 인덱스
CREATE INDEX emp_ix01 ON emp(hiredate)

-- 복합 인덱스 (2개 이상의 컬럼으로 구성, 최대 32개 컬럼까지 지정하여 생성 가능)
CREATE INDEX emp_ix02 ON emp(job, deptno)

-- 유니크 인덱스 생성
CREATE UNIQUE INDEX emp_uk ON emp(empno, mgr)
--UNIQUE 인덱스를 생성하면 인덱스로 지정된 컬럼은 해당 테이블에서 유일한 값

• 인덱스 삭제

DROP INDEX 인덱스명

📖 시노님

데이터베이스 객체에 대한 별칭을 부여한 객체
스키마명(소유자명)을 붙이지 않고 테이블을 사용하고 있다면 해당 테이블의 시노님을 사용하고 있는 것

• 시노님 생성

CREATE [OR REPLACE] 
SYNONYM '[스키마명].시노님명'
	FOR '스키마명.대상오브젝트명'
  
--① OR REPLACE : 시노님을 수정할 때 DROP 하지 않고 기존 생성 쿼리를 사용하여 수정할 수 있다. 
--② PUBLIC : PUBLIC를 생략하면 PRIVATE로 시노님이 생성된다.
--③ [스키마명].시노님명 : 자신의 계정에서 시노님을 생성할 경우 [스키마명]은 생략이 가능
-- 타 계정에서 시노님을 생성해 줄 경우 명시해야 한다.

--ex)
CREATE SYNONYM employees FOR hr.employees

💡 PUBLIC과 PRIVATE차이

• PUBLIC : PUBLIC 시노님으로 생성할 경우 대상 오브젝트의 권한을 가지고 있는 모든 스키마에 해당 시노님을 사용할 수 있다. 스키마마다 시노님을 생성해 줄 필요 없음.
• PRIVATE : 시노님을 생성한 스키마에서만 해당 시노님을 사용할 수 있다.

• 시노님 삭제

DROP [PUBLIC] SYNONYM 시노님명;

📖 시퀀스

일련번호를 생성해주는 객체
오라클에서는 컬럼의 값을 증가시키기 위해서는 MAX(컬럼) + 1 또는 시퀀스를 사용하여 일련번호를 부여한다.

• INCREMENT BY : 시퀀스 실행 시 증가시킬 값
• START WITH : 시퀀스의 시작값이다. (MINVALUE과 같거나 커야 한다)
• MINVALUE : 시퀀스가 시작되는 최솟값이다.
• MAXVALUE : 시퀀스가 끝나는 최댓값이다.
• NOCYCLE | CYCLE : NOCYCLE (반복안함), CYCLE(시퀀스의 최댓값에 도달 시 최솟값 1부터 다시시작)
• NOCACHE | CACHE : NOCACHE(사용안함), CACHE(캐시를 사용하여 미리 값을 할당해 놓아서 속도가 빠르며, 동시 사용자가 많을 경우 유리)
• NOORDER | ORDER : NOORDER(사용안함), ORDER(요청 순서로 값을 생성하여 발생 순서를 보장하지만 조금의 시스템 부하가 있음)

CACHE를 사용할 경우 CACHE 또는 CACHE "값"으로 지정할 수가 있다.
값을 지정하지 않으면 기본값은 20이다. 지정한 값만큼 시퀀스를 미리 생성해 놓는다.

• 시퀀스 사용

SELECT emp_seq.NEXTVAL
  FROM dual

시퀀스명.NEXTVAL을 사용하여 일련번호를 생성
시퀀스를 실행할 때마다 값이 증가하니 주의해야 한다. (증가된 값을 다시 내릴 수 없다)

• 현재 시퀀스 순번 조회

SELECT emp_seq.NEXTVAL
  FROM dual

시퀀스명.CURRVAL을 사용하여 현재 시퀀스 순번을 가져올 수 있다.
CURRVAL은 여러번 실행해도 순번은 증가하지 않고, 현재 순번만 가져온다

• 시퀀스 수정

ALTER SEQUENCE 시퀀스명 INCREMENT BY 2
--시퀀스 증가값을 1 → 2로 변경
ALTER SEQUENCE 시퀀스명 MAXVALUE 99999
시퀀스 최댓값을 9999 → 99999 변경

• 시퀀스 삭제

DROP SEQUENCE 시퀀스명

📖 함수

특정 연산을 하고 값을 반환하는 객체

• 함수 생성 및 수정
  CREATE OR REPLACE 구문을 사용하면 해당 함수명이 있다면 수정, 없다면 생성

CREATE OR REPLACE FUNCTION 함수이름(파라미터1이름 타입, 파라미터2이름 타입...)
RETURN 반환할타입
IS
	변수 선언
BEGIN
	실행문 작성
	RETURN 반환할값
END;

-- ex )
--두 개의 숫자를 받아 합의 두배를 반환하는 함수
CREATE OR REPLACE FUNCTION SUM_TWICE(n1 NUMBER, n2 NUMBER)
RETURN NUMBER
IS
	twice_sum  NUMBER := 0;
BEGIN
	twice_sum := (n1 + n2)*2;
	RETURN twice_sum;
END;

• 함수 실행

SELECT SUM_TWICE(12,13) 
FROM DUAL;

• 함수 조회

-- 모든 함수 조회
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';

• 함수 삭제

DROP FUNCTION 함수명;

📖 프로시저

함수와 비슷하지만 값을 반환하지 않는 객체
자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출,사용하여 작업 효율을 늘릴 수 있다.

• 프로시저 생성 및 수정
  CREATE OR REPLACE 구문을 사용하면 해당 프로시저명이 있다면 수정, 없다면 생성되게 됩니다.

CREATE OR REPLACE PROCEDURE 프로시져이름 (파라미터1,파라미터2...);
	IS
	변수
BEGIN
	쿼리문
END 프로시져 이름;

-- ex )
CREATE OR REPLACE PROCEDURE GET_TIER(in_name IN VARCHAR2,out_tier OUT VARCHAR2)
IS
BEGIN
    SELECT TIER INTO out_tier FROM SUMMONER_TB WHERE NAME = in_name;
EXCEPTION
  --소환사를 찾을 수 없을 때
  WEHN NO_DATA_FOUND THEN
    out_tier:='NO_SUMMONER_FOUND';
END GET_TIER;

파라미터 값은 in,out,inout으로 총 세가지 종류로 작성할 수 있다.
in : 전달될 데이터 | out : 결과로 나갈 데이터 | inout : in과 out 모두 가능한 데이터

• 프로시저 조회

DECLARE
	출력될 변수 선언
	실행할 프로시저
	출력문(Optional)
END
-- ex ) faker의 티어를 출력하는 프로시저
DECLARE
out_tier VARCHAR2(10);
BEGIN get_tier('faker',out_tier);
DBMS_OUTPUT.PUT_LINE(out_tier);
END;

• 프로시저 삭제

DROP PROCEDURE 프로시저명;

📖 패키지

용도에 맞게 함수나 프로시저를 하나로 묶어놓은 객체
수학에 관한 함수들을 모아 놓은 MATH라는 패키지가 있고 그 안에 연산을 하는 함수 또는 프로시저 등이 있는 것이다.
패키지는 컴파일을 거쳐서 실행되며, 다른 패키지를 참조, 실행할 수 있습니다.

• 패키지 선언부
  CREATE OR replace PACKAGE 패키지이름

CREATE OR replace PACKAGE 패키지이름
AS
  FUNCTION 함수이름(
    파라미터1이름 타입,
    파라미터2이름 타입...)
  RETURN 타입;
  PROCEDURE 프로시저이름(
  	파라미터명1 입출력 타입,
    파라미터명1 입출력 타입...)
END 패키지이름;

-- ex ) TWONUMBER_MATH를 조회
CREATE OR replace PACKAGE twonumber_math
AS
  FUNCTION Twonumber_sum(
    n1 NUMBER,
    n2 NUMBER)
  RETURN NUMBER;
  FUNCTION Twonumber_minus(
    n1 NUMBER,
    n2 NUMBER)
  RETURN NUMBER;
END twonumber_math;

• 패키지 본문
  선언문에 쓴 함수들을 구체적으로 어떤 실행이 일어날 것인지를 작성

CREATE OR replace PACKAGE BODY
  패키지이름
AS
FUNCTION 함수이름( 파라미터명1 타입,
                      파라미터명2 타입...)
  RETURN 타입
IS
  변수 선언
BEGIN
  실행문
  RETURN 반환값;
END;
PROCEDURE 프로시저이름( 파라미터명1 입출력타입 타입,
                             파라미터명2 입출력타입 타입...)
IS
  변수 선언
BEGIN
  실행문
  RETURN 반환값;
END;
END 패키지이름;

-- ex )
CREATE OR replace PACKAGE BODY twonumber_math
AS
  -- 두 개 숫자를 합쳐주는 함수
  FUNCTION Twonumber_sum(n1 NUMBER,
                         n2 NUMBER)
  RETURN NUMBER
  IS
    twonum_sum NUMBER := 0;
  BEGIN
      twonum_sum := n1 + n2;

      RETURN twonum_sum;
  END;
  
  -- 두 개 숫자를 빼는 함수
  FUNCTION Twonumber_minus(n1 NUMBER,
                           n2 NUMBER)
  RETURN NUMBER
  IS
    twonum_minus NUMBER := 0;
  BEGIN
      twonum_minus := n1 - n2;

      RETURN twonum_minus;
  END;
 END;

파라미터 값은 in,out,inout으로 총 세가지 종류로 작성할 수 있다.
in : 전달될 데이터 | out : 결과로 나갈 데이터 | inout : in과 out 모두 가능한 데이터

• 패키지 실행
  ALL_PROCEDURES로 검색하면 함수와 프로시저가 모두 출력

SELECT 패키지이름.함수(파라미터값)
FROM 테이블이름;
--OR
BEGIN
	패키지이름.프로시저(파라미터);
END;

-- ex ) 두 숫자를 연산하는 패키지인 TWONUMBER_MATH의 함수들을 실행
SELECT TWONUMBER_MATH.TWONUMBER_SUM(3,5) 두숫자더하기
FROM DUAL;

SELECT TWONUMBER_MATH.TWONUMBER_MINUS(3,5) 두숫자빼기
FROM DUAL;

• 패키지 조회
  ALL_PROCEDURES로 검색하면 함수와 프로시저가 모두 출력

SELECT *
FROM ALL_PROCEDURES
WHERE OBJECT_NAME = '패키지이름';

-- ex ) TWONUMBER_MATH를 조회
SELECT *
FROM ALL_PROCEDURES
WHERE OBJECT_NAME = 'TWONUMBER_MATH';

• 패키지 삭제

DROP PACKAGE 패키지이름;

---