[Oracle] 기초 2

저번에 사용한 MEMBER 테이블 이용

create table MEMBER(
MID NCHAR(20) NOT NULL,
MNAME NCHAR(10) NOT NULL,
MEMAIL VARCHAR2(100) NOT NULL,
MTEL INTEGER NOT NULL,
MPOST NUMBER(5) NOT NULL,
MADDR NVARCHAR2(200) NOT NULL,
MBIRTH DATE DEFAULT '0001-01-01',
MJOIN TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(MID),
UNIQUE (MEMAIL)
);

[COMMENT]

MySQL처럼 COMMENT '아이디' 한줄에 안쓰고 테이블 생성 후 따로 달아줘야함

🔸COMMENT 작성

COMMENT ON TABLE MEMBER IS '테이블코멘트';
COMMENT ON COLUMN 테이블명.컬럼명 IS '컬럼코멘트';

테이블 정보확인하는 곳에서 직접 넣을수도있음

🔸COMMENT 확인

MySQL 8.0 => InnoDB 테이블(데이터 딕셔너리)
USER_COL_COMMENTS : 데이터 딕셔너리 뷰

SELECT COLUMN_NAME, COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME = '테이블명';

COMMENT ON TABLE MEMBER IS '고객 정보를 저장하는 테이블';
COMMENT ON COLUMN MEMBER.MID IS '아이디';
COMMENT ON COLUMN MEMBER.MNAME IS '이름';

SELECT COLUMN_NAME, COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'MEMBER';

[컬럼 순서 변경]

ALTER TABLE MEMBER ADD MAGE NUMBER(3) NULL;
DESC MEMBER;    //MAGE가 가장 아래에 생성됨 
//위치를 옮기려면 
ALTER TABLE MEMBER MODIFY MJOIN INVISIBLE;  //MJOIN 안보이게 만듦 
DESC MEMBER;
ALTER TABLE MEMBER MODIFY MJOIN VISIBLE;  //MJOIN 보이게 만듦 
DESC MEMBER;
//MJOIN보다 한칸 올라감 

ALTER TABLE MEMBER MODIFY MJOIN INVISIBLE;
ALTER TABLE MEMBER MODIFY MAGE INVISIBLE;
ALTER TABLE MEMBER MODIFY MBIRTH INVISIBLE;
ALTER TABLE MEMBER MODIFY MADDR INVISIBLE;
ALTER TABLE MEMBER MODIFY MPOST INVISIBLE;
ALTER TABLE MEMBER MODIFY MTEL INVISIBLE;
DESC MEMBER;

//INVISIBLE 상태에서 값을 넣으면?
insert into MEMBER VALUES('Hong','홍길동','hong@nate.com');
//에러난다 보이지만 않을뿐 데이터 들어가야함 

//싹 INVISIBLE 해둔 상태에서 원하는 순서로 VISIBLE 하기 
ALTER TABLE MEMBER MODIFY MAGE VISIBLE;
ALTER TABLE MEMBER MODIFY MBIRTH VISIBLE;
ALTER TABLE MEMBER MODIFY MTEL VISIBLE;
ALTER TABLE MEMBER MODIFY MPOST VISIBLE;
ALTER TABLE MEMBER MODIFY MADDR VISIBLE;
ALTER TABLE MEMBER MODIFY MJOIN VISIBLE;
DESC MEMBER;

// MYSQL : after나 before 사용
ORACLE
1. 생성 후 다른 컬럼에 INVISIBLE -> VISIBLE 사용해서 올리기...
2. SQLDEVELOPER의 테이블 화면에서 COLUMN_ID 조정

[현재 테이블의 생성문 확인하기]

테이블 > 우클릭 > 편집 > DDL > 생성

그대로 긁어서 ERD 생성할때 사용가능!!

CREATE TABLE MEMBER 
(
  MID NCHAR(20) NOT NULL , 
  MNAME NCHAR(10) NOT NULL , 
  MEMAIL VARCHAR2(100 BYTE) NOT NULL , 
  MAGE NUMBER(3, 0) , 
  MBIRTH DATE DEFAULT '0001-01-01' , 
  MTEL NUMBER(*, 0) NOT NULL , 
  MPOST NUMBER(5, 0) NOT NULL , 
  MADDR NVARCHAR2(200) NOT NULL , 
  MJOIN TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP , 
PRIMARY KEY(MID),
UNIQUE (MEMAIL)
  );

[날짜 포멧]

🔸TIMESTAMP 값 삽입 : now()=> SYSTIMESTAMP

CURRENT_TIMESTAMP : insert시 컬럼명을 나열하지 않을 경우
🔸value로 처리할 필요없이 시스템 시간으로 자동 등록됨

insert into MEMBER VALUES('Hong','홍길동','hong@nate.com','28','2002-12-25',
'01012341234','05621','서울시 마포구',SYSTIMESTAMP);

INSERT INTO MEMBER(MID, MNAME, MEMAIL, MAGE, MBIRTH, MTEL, MPOST, MADDR) 
VALUES ('lee', '이순신', 'lee@naver.com', 38, DATE '1998-01-05', '01012341004', '05620', '서울시 마포구');
INSERT INTO MEMBER (MID, MNAME, MEMAIL, MAGE, MBIRTH, MTEL, MPOST, MADDR) VALUES ('park', '박혁거세', 'park@daum.net', 30, TO_DATE('1994-03-01', 'YYYY-MM-DD'), '0109988776', '54321', '대전광역시 유성구');

select * from MEMBER;
//날짜 포멧이 적용안된 상태로 보임

//[날짜 포멧 설정] 

//현재 서버를 가동하고 잇는 상황에서만 적용 (종료 후 재시작시 원상태로 돌아감)
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'HH24:MI:SS';
select * from MEMBER;
//로그아웃하고 로그인시 다시 초기화됨... 왜? 세션이니까
//근데 실무에서는 이렇게 써도 됨! 왜? 서버가 24시간 돌아가니까 끌 일이 없으니 유지됨 

//사용후 종료하는 내 PC에서 유지하려면? 시스템을 세팅함

//시스템 날짜 세팅시 주의점 => 다른 데이터베이스도 영향이 감 
//오라클 껐다 켜면 적용됨 
//시스템 세팅은 권장하지 않음 위험함 / 실무에서 사용 X / 혼자 쓸 때 사용
//해당 SYSTEM 전반적인 날짜 기준을 변경해 버리는 명령어 
ALTER SYSTEM SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_TIMESTAMP_FORMAT = 'HH24:MI:SS' SCOPE=SPFILE;

select * from v$nls_parameters;

🔸SESSION 사용 권장

🔸SYSTEM 바꾸는 경우 SEED까지 변하기때문에 실무에 절대 사용 X

🔸이거로 설정해도 됨

상단 도구틀 > 도구 > 환경설정 > 데이터베이스 > NLS

접속해제 후 재접속

select sysdate from dual;

하면 적용된 모습 볼 수 있음

[오라클 종료, 시작]

일반적 : cmd 사용
window 개인PC일때 야매 : 제어판 > 관리도구 > 서비스 > OracleServiceXE 찾아서 우클릭 중지하고 다시 시작

[숫자자료형 전화번호 0채우기]

전화번호 입력시 0이 빠지는 경우
int, INTEGER, NUMBER => 0 누락 => NVARCHAR2, NCHAR 등 문자열로 변경
어떤 경우는 그냥 출력할때 0을 붙이는 경우도 존재

SELECT LPAD(TO_CHAR(1234),6,'0') FROM dual;

SELECT LPAD(MTEL,11,'0')|| LPAD(MTEL,10,'0') from member;

//전화번호 자릿수 : 10~11
SELECT
CASE
WHEN LENGTH(MTEL) = 10 THEN LPAD(MTEL,11,'0')
WHEN LENGTH(MTEL) = 9 THEN LPAD(MTEL,10,'0')
END MTEL
FROM MEMBER;

select sysdate from dual;

drop table member;

[ORACLE의 인덱스 컬럼]

MYSQL : auto_increment 존재
ORACLE은 없음 => 시퀀스 사용 (MSSQL도 시퀀스 사용)

[시퀀스 생성법 ]

명령어 or 아래 사진처럼 만들기

🔸GUI 이용

GUI 로 만들어서 위 사진의 DDL을 클릭하면 쿼리문 나옴! => 외울 필요가 없음

🔸명령어로 생성

START WITH : 시작값
INCREMENT BY : 번호 생성 간격 (실무에선 사용안함 => +1자동)

MINVALUE : 최소값을 지정 (기본 NOMINVALUE => 1)
MAXVALUE : 종료값
NOMAXVALUE : 종료값 무한 (10^27정도까지)
NOCYCLE : 최대치값이 되었을 경우 더 이상 +1의 증가값 사용하지 않음
CYCLE : 최대치값이 되었을 경우 다시 1부터 시작 (사용하는 경우가 없음)
NOCACHE : insert를 받아야 번호를 지정해줌
CACHE : 공유 메모리에 미리 생성할 번호의 개수를 지정 (기본값 20개까지)

//1부터 시작해서 1씩 증가하여 300까지 생성되는 시퀀스 (범위 벗어나면 값 안들어감)
CREATE SEQUENCE NO START WITH 1 INCREMENT BY 1 MAXVALUE 300 NOCYCLE NOCACHE;

[ 인덱스 사용]

🔸시퀀스를 사용한 인덱스

SEQUENCE는 여러개의 테이블에서 공유 가능
만약 테이블에 자동 증가값(고유값)을 사용시 새로운 시퀀스를 생성하여 적용하는것이 올바름
쿼리문 오류 및 코드 문법 오류가 발생시에도 시퀀스 무조건 +1

insert시 시퀀스명.NEXTVAL 사용하여 자동증가값 적용

사수가 하나 만들어 놓고 부사수한테 시퀀스 여기저기 같이쓰자~ 하면 뭐 어쩔수없음 (미친사수)

테스트 다 하고 시퀀스 초기화하고싶을때
GUI에서 시퀀스 누르고 시작값 1로 다시 만들기

CREATE TABLE MEMBER (
MIDX NUMBER(3) NOT NULL,
MID NCHAR(20) NOT NULL , 
MNAME NCHAR(10) NOT NULL , 
MEMAIL VARCHAR2(100 BYTE) NOT NULL , 
MAGE NUMBER(3) , 
MBIRTH DATE DEFAULT '0001-01-01' , 
MTEL NVARCHAR2(11) NOT NULL , 
MPOST NVARCHAR2(5) NOT NULL , 
MADDR NVARCHAR2(200) NOT NULL , 
MJOIN TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP , 
PRIMARY KEY(MIDX),
UNIQUE (MID),
UNIQUE (MEMAIL)
);

//기본테이블 생성 후 INSERT시 시퀀스명.NEXTVAL 사용 

DESC MEMBER;

INSERT INTO MEMBER VALUES(NO.NEXTVAL, 'Hong','홍길동','hong@nate.com','28','2002-12-25',
'01012341234','05621','서울시 마포구',SYSDATE);

INSERT INTO MEMBER VALUES(NO.NEXTVAL, 'lee', '이순신', 'lee@naver.com', 38, DATE '1998-01-05', '01012341004', '05620', '서울시 마포구',SYSDATE);

SELECT * FROM MEMBER;

//시퀀스는 다른 테이블에서도 사용 가능! 
//대신 테이블별로 번호 따로따로 순서대로 안되고 테이블별로 번호가 중간중간 비게 됨 
//시퀀스 따로 사용하기 => MEMBER_NO 이런식으로 잘 알아볼수있게 만들어서 사용
create table pay(
pidx int not null,
pmoney int not null,
primary key(pidx)
);
insert into pay values (NO.NEXTVAL,'5000');
SELECT * FROM pay;

🔸 시퀀스 삭제

DROP SEQUENCE NO2;

🔸시퀀스를 안쓰는 경우의 인덱스 사용

GENERATED ALWAYS as IDENTITY (START WITH 1 INCREMENT BY 1)
요거 12c부터 사용가능 (실무에서 아직 g쓰는곳도 많아서 못쓸수도있음)
최대 단점 : 오라클은 참 나쁘다 이렇게 할수있으면 쉽게 쓸 수 있게 해줘야지
INSERT INTO MEMBER VALUES 쓸때 컬럼명 다 넣어줘야됨

insert시 values를 바로 적용 불가능 => 인덱스컬럼을 제외한 컬럼명을 모두 넣어줘야함

CREATE TABLE MEMBER (
MIDX NUMBER(3) GENERATED ALWAYS as IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
MID NCHAR(20) NOT NULL , 
MNAME NCHAR(10) NOT NULL , 
MEMAIL VARCHAR2(100 BYTE) NOT NULL , 
MAGE NUMBER(3) , 
MBIRTH DATE DEFAULT '0001-01-01' , 
MTEL NVARCHAR2(11) NOT NULL , 
MPOST NVARCHAR2(5) NOT NULL , 
MADDR NVARCHAR2(200) NOT NULL , 
MJOIN TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP , 
PRIMARY KEY(MIDX),
UNIQUE (MID),
UNIQUE (MEMAIL)
);

INSERT INTO MEMBER (MID, MNAME, MEMAIL, MAGE, MBIRTH, MTEL, MPOST, MADDR)
VALUES('Hong','홍길동','hong@nate.com','28','2002-12-25','01012341234','05621','서울시 마포구');

INSERT INTO MEMBER (MID, MNAME, MEMAIL, MAGE, MBIRTH, MTEL, MPOST, MADDR) 
VALUES('lee', '이순신', 'lee@naver.com', 38, DATE '1998-01-05', '01012341004', '05620', '서울시 마포구');

select * from MEMBER;

이거사용시 시퀀스에 ISEQ$$__73949 이런식으로 스스로 마음대로 시퀀스가 생성돼서 사수한테 말해야함

그리고 넘 많이쓰면 이런 지맘대로생성된이름의시퀀스 많아져서

사실 그냥 시퀀스 만들어서 시퀀스 이름 명시적으로 만들어서 사용하는것이 바람직함

저작자표시 비영리 변경금지 (새창열림)