Oracle 정리

[Oracle] - Container 기준
CDB - Oracle의 큰 운영체제 Database => Database를 구성
Seed(씨앗)라는 Database를 이용해서 PDB를 활용함

[관리자]
system 사용자 => sysoper (DB-startup, close, backup, recover, mount)
sys 사용자  => sysdba (오라클의 모든 권한을 갖는 관리자)

show pdbs;   => 가상의 Database 리스트를 확인하는 명령어
select name from v$pdbs; => show pdbs 동일하게 리스트를 확인

show con_name; => 컨테이너 네임을 출력하는 명령어

※ cmd로 접속시 sys는 sysdba 또는 sysoper로 접속을 해야하며, 해당 설정은 as로 구분하여 접속합니다.

[기본사용자] => pdbs를 생성시
- HR (인적자원 관리 사용자)
- PM (상품정보 관리 사용자)
- SH (판매정보 관리 사용자)
- BI (비지니스 모델 관리 사용자)

[Table 생성 및 Data를 입력시 - space공간이 필요함]
1. Tablespace - Table을 생성할 때 사용하는 공간
2. Tablelogspace - Table 로그 기록을 남기는 공간
3. 사용자를 추가
   - SYSKM : 암호화 관련 규정사항
   - SYSOPER : Database를 정지, 강제 백업, 추가 사용자 등록
   - SYSRAC : 사용자 그룹형태를 구성하는 기능, 일반 사용자에게 관리자 권한 줄 수 있음
4. 가상의 Database를 접속하는 방식
[sqlplus로 접속시]
sqlplus 사용자ID/패스워드@localhost:1521/xepdb1
show con_name;   => XEPDB1 정상접속

[외부 SQL 접속]
sqlplus 사용자ID/패스워드@IP:1521/xepdb1

[Database 자료형]
char = 1byte => 고정길이
[varchar  vs  varchar2] => 자료형 변환 => varchar => varchar2 변경(X)
숫자, 알파벳, 한글(varchar, varchar2 여유길이를 적용해야함)
varchar => 가변길이
varchar2 => Oracle전용 자료형
char(4) => 영어, 숫자 4자까지 
nchar(4) => 한글도 4자까지 입력가능, 영어, 숫자 4자 (유니코드)
nvarchar2 => 유니코드로 단어의 갯수만큼 입력이 가능함

[숫자 자료형]
int or Integer 둘다 동일한 자료형 (byte를 적용하지 않음)
long(2Gbyte)
[number => 정수랑 소수 모두 가능]
number => 크기를 정할 수 있음(Max : 38자리 숫자) 
정수 : number(8) => 단, 문자형태의 강제로 소수점이 입력될 경우 반올림이 이루어짐
소수 : number(8,2) => 소수점으로 표현 즉, 소수점 2자리 포함 8자
clob => long update 버젼 (4Gbyte)
nclob => 유니코드 clob보다 더 큰 데이터 타입가능

[Date 자료형]
timestamp => Oracle 시간
timestamp with time zone =>  서버가 위치한 대륙/국가에 대한 시간
timestamp with local time zone => 클라이언트가 위치한 시간대 정보를 포함

단어크기를 알고 싶을 경우
select lengthb('단어') from dual;  => 6

해당 서버 시간으로 입력시 : sysdate, systimestamp
select * from v$NLS_PARAMETERS;   => 22/09/24 15:36:22.000002

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS';

[배열 자료형] - 배열에 입력된 값만 저장 시키는 자료형
mysql : enum(X), set(x) => oracle : check 컬럼 자료형
(check 사용법 예시)
컬럼명 nchar(40) constraint 체크가상명 check(컬럼명 IN('취업','성적','직장','대인'))

[테이블명 변경 방법]
rename 기존테이블명 to 변경할 테이블명;

[테이블 리스트 확인 방법]
select table_name from user_tables;

[휴지통 기능] - drop된 테이블을 복구할 수 있는 기능이 있음
select * from user_recyclebin;   => 휴지통에 있는 테이블 목록 리스트를 확인하는 사항

flashback table 복구할 테이블명 to before drop; => drop으로 삭제한 테이블을 복구(데이터가 있을 경우 데이터도 함께 복구)

purge recyclebin;   => 휴지통에 있는 테이블을 모두 비우는 명령어

[컬럼 이름 변경]
alter table 테이블명 rename column 기존 컬럼명 to 변경할 컬럼명;

[컬럼 추가]
alter table 테이블명 add column 추가컬럼명 자료형 not null or null;
※ 단, 이미지 테이블에 데이터가 있을 경우는 not  null로만은 컬럼을 추가할 수 없음 default를 입력시에는 컬럼을 추가 할 수 있습니다.

[컬럼 순서변경]
alter table 테이블명 modify 컬럼명 invisible; => 컬럼 비활성화
alter table 테이블명 modify 컬럼명 visible;  => 컬럼 활성화

[AUTO_INCREMENT] - ORACLE에는 없는 속성 (자동증가값)
Oracle : SEQUENCE를 이용하여 자동 증가값(고유값)을 핸들링 할 수 있음

[시퀀스를 제작하는 방법] - 시퀀스 이름은 중복되지 않음
create SEQUENCE 시퀀스이름 start with 1 INCREMENT BY 1 MAXVALUE 99999999;

INCREMENT BY : 숫자를 증가 시키는 형태
START WITH : 최초 자동증가값의 시작값
MAXVALUE : 자동증가값의 최대값 
NOMAXVALUE : 최대값이 없음 
CYCLE : MAXVALUE 되었을 경우 다시 1 부터 시작 여부를 사용
CACHE : 미리 생성할 번호 갯수를 메모리 저장 최대 20번까지 

데이터 입력 다음과 같이 입력을 함
insert into 테이블명 values (시퀀스이름.nextval,.....);  

※ 단, 테이블끼리 시퀀스를 공유시 다시 1 부터 시작하지 않습니다. (각각의 시퀀스 제작하여 사용하는 것을 권장) 또한 시퀀스를 사용하는 테이블 중 해당 시퀀스를 삭제시 오류 발생함.

[시퀀스 없이 사용하는 형태]
create table 생성시 컬럼에 다음과 같이 셋팅

컬럼명 number(4) GENERATED ALWAYS as IDENTITY (START WITH 1 INCREMENT BY 1) 생성
insert 시 예시 ) insert into 테이블명 (컬럼명-자동증가값 컬럼을 뺀 나머지 컬럼) values ('','','','');

[Table에 대한 상세 내역을 확인하는 방법] - 테이블명 대문자로 설정

all_constraints : 사용자 범위 및 설정 정보가 담겨 있는 테이블 (Key 정보)
all_cons_columns : 컬럼 이름 및 컬럼의 사용된 자료형, null, not null

SELECT * from all_constraints cons, all_cons_columns cols 
where cols.table_name = '테이블명' 
and cons.constraint_name=cols.constraint_name;

C : KEY 셋팅이 되지 않은 컬럼
P : Primary key로 셋팅한 컬럼
U : Unique 로 셋팅한 컬럼
R : Foreign Key로 셋팅한 컬럼

※ Foreign Key 셋팅시 부모 테이블에 대한 설계 기준
1. Primary key 한개만 있을 경우 (가능)
2. Unique 각각 분리하여 생성 하였을 경우 (가능)
3. Unique 복합으로 생성 하였을 경우, Foreign Key 복합으로 생성(가능)

//after : 입력 후, before : 입력전
TRIGGER 사용형태 : insert, update, delete 

create or replace TRIGGER 트리거이름
after INSERT on 부모테이블
FOR EACH ROW
BEGIN
INSERT INTO 자식테이블 (컬럼) values (:old or :new);
END;

:old 기존에 테이블에 값이 있을 경우 백업 테이블에 삽입할 때 사용하는 형태
:new 테이블에 신규 데이터가 입력 되었을 경우 사용하는 형태

SQL DEVELOPER에서 미러링으로 데이터를 입력 후
입력사항이 필요없을 경우 rollback;  => 실제 데이터에 반영하지 않음

commit; => 실제 테이블로 데이터를 반영 합니다.

[시퀀스 증가값 확인]
select LAST_NUMBER from user_sequences where SEQUENCE_NAME='시퀀스 이름';

해당 문법을 입력 후 다시 원래의 숫자로 나올 경우는 CYCEL 옵션이 빠진 상황
alter SEQUENCE CNO INCREMENT BY -숫자;
alter SEQUENCE CNO INCREMENT BY 1;

drop 을 이용하여 시퀀스를 삭제 후 재 생성 하셔야 정상으로 LAST_NUMBER 적용이 됩니다.

[limit을 사용하는 방법] 단, Oracle limit 명령어가 없습니다. BETWEEN을 이용하여 설정
고유  row 를 확인하는 방법

ROWNUM    :  단순 SQL ROW의 번호를 정하는 명령어 (order by 적용안됨)
ROW_NUMBER()  :  Oracle에 전용 ROW 함수를 설정하는 명령어 (order by 적용)

select * from (select ROWNUM NUM, 테이블명.* from 테이블명) where NUM BETWEEN 시작값 AND 종료값;


[ROW_NUMBER() 사용하는 방식]
select * from
(select ROW_NUMBER() OVER (order by 컬럼명 desc) num, 
테이블명.* from 테이블명 
order by 컬럼명 desc)
where num BETWEEN 11 and 20;

예시) 한 page당 데이터 10개씩 출력시 : BETWEEN 1 and 10;   그리고 BETWEEN 11 and 20;


[View - 테이블 생성법] - join이라는 단어를 사용하지 않고,  "," 를 이용하여 출력하는 방식
create view 뷰테이블명 as 
select 컬럼명, 컬럼명, from 테이블명 가상이름, 테이블명 가상이름 where 컬럼명 = 컬럼명;

※ JOIN 이라는 단어를 사용하기 위해서는 on 을 이용하여 사용 해야하며, where은 사용하지 못함
예시) 
select a.id, a.name, b.email from mb1 a join mb2 b on a.idx=b.idx where a.name='홍길동';
select a.id, a.name, b.email from mb1 a join mb2 b on a.idx=b.idx;
select a.id, a.name, b.email from mb1 a, mb2 b where a.idx=b.idx;

[Mybatis 검색 like 사용 부분]
select * from customer where c_name like '%'||#{c_name}||'%' order by c_idx desc