개발새발, 써놓기

IDENTITY COLUMN 이란 ANSI SQL서 지원하는 자동으로 숫자가 되는 컬럼이다.

오라클에서는 내부적으로 시퀀스를 사용한다.

실습용 테이블 생성

CREATE TABLE test(
    num NUMBER GENERATED AS IDENTITY PRIMARY KEY,
    subject VARCHAR2(1000) NOT NULL
);

생성시 GENERATED BY IDENTITY 를 넣으면 된다.

-- 데이터 추가

INSERT INTO test(subject) VALUES ('a');
INSERT INTO test(subject) VALUES ('b');
INSERT INTO test(subject) VALUES ('c');

SELECT * FROM test;

자동으로 num 에 숫자가 들어가 있는 것을 확인할 수 있다.

INSERT INTO test(num, subject) VALUES (10, 'x');

기본적으로 ALWAYS 옵션이므로 INSERT, UPDATE에서 값을 수정할 수 없다.

그러므로 num의 현재 값을 알기위해서

SELECT* FROM user_objects;

에서 ISEQ$$ 으로 시작하는 SEQUENCE를 찾으면된다.

SELECT ISEQ$$_75428.CURRVAL FROM dual;

BY DEFAULT ON NULL : 특정 값으로 IDENTITY 컬럼 값을 지정할 수 있다.

NULL 이면 IDENTITY 값으로 추가하는 테이블을 만들어보자.

CREATE TABLE test(
    num NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    subject VARCHAR2(1000) NOT NULL
);

INSERT INTO test(subject) VALUES ('a'); -- 1 a 추가
INSERT INTO test(num, subject) VALUES (9, 'b'); -- 9 b 추가
INSERT INTO test(num, subject) VALUES (null, 'c'); -- 2 c 추가
INSERT INTO test(subject) VALUES ('d'); -- 3 d 추가

-- BY DEFAULT 옵션 제거

ALTER TABLE test MODIFY (num GENERATED ALWAYS AS IDENTITY);

ALWAYS는 INSERT, UPDATE에서 IDENTITY 컬럼 수정이 불가능하다.

IDENTITY 컬럼은 테이블을 만들때만 지정할 수 있고, 테이블이 만들어진 후 일반컬럼을 IDENTITY 컬럼으로는 변경이 불가능하다. 단, IDENTITY 컬럼을 만든 후 속성을 변경할 수 있다.

BY DEFAULT 옵션을 제거했기 때문에

INSERT INTO test(num, subject) VALUES (99, 'y'); -- 에러

자동으로 생성되는 IDENTITY 컬럼에 값을 넣으면 오류이다.

-- 존재하는 테이블에 IDENTITY 컬럼 추가

CREATE TABLE test(
    qty NUMBER NOT NULL,
    subject VARCHAR2(1000) NOT NULL
);
ALTER TABLE test ADD( num NUMBER GENERATED AS IDENTITY PRIMARY KEY);

IDENTITY 컬럼을 존재하는 테이블에 추가하면된다.

보이지 않는 컬럼(INVISIBLE COLUMN)에 대해 알아보자.

테이블을 생성해서 알아보자.

CREATE TABLE test (
    num NUMBER PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    tel VARCHAR2(30) INVISIBLE
);


DESC test; -- 확인불가
SELECT * FROM col WHERE tname = 'TEST'; -- INVISIBLE 컬럼 보이지 않음.
SELECT * FROM cols WHERE table_name = 'TEST'; -- INVISIBLE 컬럼 보임.

테이블을 생성할 때 컬럼명 자료형 기술 후 INVISIBLE 을 넣으면 보이지 않는 컬럼이 생성된다.

이 컬럼의 존재는 SELECT * FROM cols WHERE table_name = 'TEST';를 통해 알 수 있다.

이렇게 숨겨진 컬럼에 데이터를 넣을 때는 컬럼명을 명시해야한다.

INSERT INTO test VALUES(1, 'a'); -- 가능
INSERT INTO test VALUES(2, 'b', '010'); -- 에러

INSERT INTO test (num, name, tel) VALUES(2, 'b', '010'); -- 가능

또, 테이블을 보기위해서도 컬럼명을 명시해야한다.

SELECT num, name, tel FROM test; -- INVISIBLE 컬럼 보임
SELECT * FROM test; -- INVISIBLE 컬럼 보이지 않음

-- VISIBLE/INVISIBLE 컬럼으로 변경

ALTER TABLE test MODIFY (tel VISIBLE);
ALTER TABLE test MODIFY (tel INVISIBLE);

-- INVISIBLE 컬럼에 NOT NULL 제약 조건 설정

ALTER TABLE test MODIFY (tel NOT NULL) ;

오라클 11g 방식

-- 급여 내림차순 정렬하여 5개 건너 뛰고 3개 출력 ( 즉 6~8번 출력)

SELECT * FROM (
    SELECT ROWNUM rnum, tb.* FROM(
        SELECT empNo, name, sal
        FROM emp
        ORDER BY sal DESC
    ) tb WHERE ROWNUM <= 8
)WHERE rnum >= 6;

-- 20개 건너뛰고 10개 출력

SELECT * FROM (
    SELECT ROWNUM rnum, tb.* FROM(
        SELECT empNo, name, sal
        FROM emp
        ORDER BY sal DESC
    ) tb WHERE ROWNUM <= 30
)WHERE rnum >= 21;

오라클 12c 이상

-- 급여 상위 10%

SELECT empNo, name, sal
FROM emp
ORDER BY sal DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

-- 남자 중 급여 내림차순 정렬하여 11번에서 16번까지 출력

SELECT empNo, name, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 1
ORDER BY sal DESC
OFFSET 10 ROWS FETCH FIRST 6 ROWS ONLY;

-- 여자 중 급여 내림차순 정렬하여 상위 10% 출력

SELECT empNo, name, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
ORDER BY sal DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

MY-SQL 페이징 처리

-- 20개 건너뛰고 10개 출력

SELECT empNo, name, sal
FROM emp
ORDER BY sal DESC
LIMIT 20, 10;

좀 더 알아보기 >>

CREATE TABLE board (
    num  NUMBER,
    name VARCHAR2(30) NOT NULL,
    subject VARCHAR2(500) NOT NULL,
    content VARCHAR2(4000) NOT NULL,
    reg_date DATE DEFAULT SYSDATE,
    hitCount NUMBER DEFAULT 0,
    CONSTRAINT board_num_pk PRIMARY KEY(num)
);

CREATE SEQUENCE board_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;

CREATE OR REPLACE PROCEDURE pInsertBoard
IS
    vName  VARCHAR2(30);
    vSubject  VARCHAR2(500);
    vContent VARCHAR2(4000);
BEGIN
    FOR  n  IN  1 .. 200000 LOOP
        vName := '자바' || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65)) || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65)) || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65)) || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65)) || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65));
        vSubject := '제목-' || (TRUNC(DBMS_RANDOM.VALUE * 1000000000)+1) || '-입니다.';
        vContent := '게시글 내용-' || (TRUNC(DBMS_RANDOM.VALUE * 100000000)+1) || '-입니다.';
        
        INSERT INTO board(num, name, subject, content, reg_date, hitCount) VALUES
            (board_seq.NEXTVAL, vName, vSubject, vContent, SYSDATE, 0);
    END LOOP;
    COMMIT;
END;
/

EXEC pInsertBoard;
EXEC pInsertBoard;
EXEC pInsertBoard;
EXEC pInsertBoard;
EXEC pInsertBoard;

SELECT num, name, subject, reg_date, hitCount
FROM board
ORDER BY num DESC
OFFSET 1000 ROWS FETCH FIRST 10 ROWS ONLY;

SELECT num, name, subject, reg_date, hitCount FROM (
    SELECT ROWNUM rnum, tb.* FROM (
        SELECT num, name, subject, reg_date, hitCount
        FROM board
        ORDER BY num DESC
    ) tb WHERE ROWNUM <= 1010
) WHERE rnum >= 1001;

우선 정규식을 실습할 테이블이다.

num, name, email에 데이터 들어가 있다.

-- name이 한 또는 백으로 시작하는 레코드만 출력해라.

우리가 정규식을 배우기 전에 했던 방식

SELECT * FROM reg WHERE SUBSTR(name, 1, 1) IN ('한', '백');
SELECT * FROM reg WHERE INSTR(name, '한') = 1 OR INSTR(name, '백') = 1;

정규식을 이용해 출력하기

SELECT * FROM reg WHERE REGEXP_LIKE(name, '^[한백]');

-- name : 강산으로 끝나는 레코드

SELECT * FROM reg WHERE REGEXP_LIKE(name, '강산$');

-- com으로 끝나는 email

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'com$');
    -- 소문자만 가능
SELECT * FROM reg WHERE REGEXP_LIKE(email, 'com$', 'i');
    -- 대소문자 구분 안함

-- email : kim포함

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim');
SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim*');

-- email : kim3?3 가운데 한글자가 뭐든지 상관없음

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim3?3');

-- email : kim 다음에 0~3 사이의 문자가 2번 반복

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim[0-3]{2}');

-- email : kim 다음에 2~3 사이의 문자가 3~4번 반복

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim[2-3]{3,4}');

-- email : kim다음에 1이 아닌 레코드

SELECT* FROM reg WHERE REGEXP_LIKE(email, 'kim[^1]');

-- email : k로 시작하지 않는 사람들

SELECT* FROM reg WHERE REGEXP_LIKE(email, '^[^k]');

-- name : 이름이 한글인 사람만

SELECT* FROM reg WHERE REGEXP_LIKE(name, '^[가-힣]{2,}$');

-- email : 숫자가 있는 이메일을 출력

SELECT* FROM reg WHERE REGEXP_LIKE(email, '[0-9]');
SELECT* FROM reg WHERE REGEXP_LIKE(email, '[[:digit:]]');

-- email : 영문자가 존재하는 이메일 출력

SELECT* FROM reg WHERE REGEXP_LIKE(email, '[a-z|A-Z]');

REGEXP_LIKE(컬럼, '정규식'); 을 통해 컬럼에서 원하는 데이터만 출력해보았다.

REGEXP_REPLACE 는 문자열을 지정한 패턴으로 치환한다.

SELECT 'hong gil dong' FROM dual;
SELECT REGEXP_REPLACE('hong gil dong', '(.*) (.*) (.*)', '\2 \3 \1') FROM dual;
-- . 은 한글자 * 0자 이상
-- (.*) 모든 글자

-- email 에 숫자 없애기

SELECT email, REGEXP_REPLACE(email, '[[:digit:]]', '')FROM reg;

-- 특수 문자 제거

SELECT REGEXP_REPLACE('우리&*나라 12 대!@한', '[[:digit:]|[:punct:]]', '') FROM dual;

-- 숫자와 특수문자 제거

SELECT REGEXP_REPLACE('우리&*나라 12 대!@한', '[[:digit:]|[:punct:]]', '') FROM dual;

-- 지정된 위치부터 문자를 *로 치환

SELECT REGEXP_REPLACE ('00090931111111', '[0-9]', '*', 9) FROM dual;
    -- 7번째 위치부터 [0-9] 문자를 *로 치환

-- 영문자(한글도 포함)가 아닌 문자의 위치

SELECT name, REGEXP_INSTR(name, '[^[:alpha:]]') FROM reg;

-- 이름에 소문자가 아닌 문자가 존재하는 위치

SELECT name, REGEXP_INSTR(name, '[^[:lower:]]') FROM reg;

-- 이름에 문자가 아닌 것이 있는 위치

SELECT name, REGEXP_INSTR(name, '[^[:alpha:]]') FROM reg;

:alpha: 는 한글도 검색한다.

-- email에서 @ 뒤부분을 제외한 ID부분 출력

SELECT email, REGEXP_SUBSTR(email, '[^@]+') FROM reg;

-- email : 0~9 범위의 수가 몇개 존재하는지

SELECT email, REGEXP_COUNT(email, '[0-9]') FROM reg;

시노님 (SYNONYM) ?

사용자가 다른 사용자의 테이블 등의 객체를 참조하는 방법으로 사용자.테이블명으로 접근한다.

-- 현재 사용자가 가지고 있는 권한 확인

SELECT * FROM USER_SYS_PRIVS;

SKY 계정에서 HR계정의 employees 테이블의 내용 확인을 하고 싶다.

그러면

1. SKY계정이 시노님을 작성할 수 있도록 권한을 가지고 있는지 확인한다.

2. 권한이 없으면 관리자에서 권한을 부여한다.

관리자 계정으로 들어가서 GRANT CREATE SYNONYM TO SKY; 로 주면 될 것이다.

-- SKY계정에서 hr.employees를 employees 라는 시노님으로 작성

CREATE SYNONYM employees FOR hr.employees;

-- 시노님 확인

SELECT * FROM syn;

SYNONYM_NAME, TABLE_OWNER, TABLE_NAME 등 원래 소유자와 소유자의 테이블명, 내가 만든 시노님의 테이블명 등을 확인할 수 있다.

-- 시노님 삭제

DROP SYNONYM employees;

시퀀스 (SEQUENCE) ?

유일한 정수값을 연속적으로 생성하는 객체이다.

시퀀스 번호는 트랜잭션 커밋 또는 롤백과 상관없이 증가한다.

-- 시퀀스 만들기

-- 1부터 1씩 증가하는 시퀀스 작성

CREATE SEQUENCE test_seq1
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;

-- 시퀀스 목록 확인

SELECT * FROM user_sequences;
SELECT * FROM seq;

※ 시퀀스를 만들 때 seq로 시퀀스 명을 부여하면 안된다. seq로 시퀀스 이름을 부여하면 목록을 확인할 때 seq로 시퀀스 확인이 불가하다.

-- 다음 시퀀스 값 가져오기

SELECT test_seq1.NEXTVAL FROM dual;

-- 현재 시퀀스 값 확인

SELECT test_seq1.CURRVAL FROM dual;

※ 동일한 SELECT, INSERT 문에서는 NEXTVAL을 여러번 해도 동일한 값이 나옴에 주의하자.

SELECT test_seq1.NEXTVAL, test_seq1.CURRVAL, test_seq1.NEXTVAL FROM dual;

-- 시퀀스 삭제

DROP SEQUENCE 시퀀스이름;
DROP SEQUENCE test_seq1;

SELECT * FROM seq; -- 확인

-- 1부터 증가하는 시퀀스(기본 캐시 20개)를 만들어보자

CREATE SEQUENCE test_seq1;
SELECT * FROM seq;

CREATE SEQUENCE 시퀀스명; 으로 시퀀스를 생성하면 1부터 증가하는 시퀀스(기본캐시20개)를 자동으로 만들어준다.

기본 캐시가 20개라는 의미는 미리 20개의 시퀀스를 만들어 놓는다는 의미로, 만약 현재 시퀀스가 3인 상태에서 오라클 서버가 재실행되면 다음 시퀀스의 값은 21이 된다.

예제를 통해 시퀀스 만드는 방법을 좀 더 알아보자 >>

더보기

-- 100~200까지 100부터 2씩 증가하는 시퀀스(캐시5개, 200 넘어가면 오류)

CREATE SEQUENCE test_seq2
INCREMENT BY 2
START WITH 100
MINVALUE 100
MAXVALUE 200
CACHE 5;

-- 10~20까지 3씩 증가하는 시퀀스(캐시3개 최소1, 최대값에 도달하면 처음부터 다시)

CREATE SEQUENCE test_seq3
INCREMENT BY 3
START WITH 10
MINVALUE 1
MAXVALUE 20
CYCLE
CACHE 3;

몇 번 실행해서 어떻게 진행되는지 확인해보자

SELECT test_seq1.NEXTVAL, test_seq2.NEXTVAL, test_seq3.NEXTVAL
FROM dual;

test_seq3의 경우 CYCLE이 설정되어있는데, CYCLE옵션이 있는 경우 명시적으로 CACHE를 지정하거나 NOCACHE로 설정해야한다.

MINVALUE가 1이기 때문에 20을 초과하면 1부터 다시 CYCLE을 만들며 시작된다.

-- 시퀀스 삭제

DROP SEQUENCE test_seq1;
DROP SEQUENCE test_seq2;
DROP SEQUENCE test_seq3;

실습을 위한 테이블을 만들어 보자.

CREATE TABLE test1 (
    id VARCHAR2(30) PRIMARY KEY, 
    name VARCHAR2(30) NOT NULL,
    city VARCHAR2(30) NOT NULL,
    memo VARCHAR2(100)
);

CREATE TABLE test2(
    num NUMBER PRIMARY KEY,
    id VARCHAR2(30)NOT NULL,
    score NUMBER(3) NOT NULL,
    FOREIGN KEY ( id ) REFERENCES test1 ( id )
    ON DELETE CASCADE
);

INSERT INTO test1(id, name, city, memo) VALUES ('a', '김자바', '서울', NULL);
INSERT INTO test1(id, name, city, memo) VALUES ('b', '오라클', '경기', NULL);
INSERT INTO test1(id, name, city, memo) VALUES ('c', '스프링', '인천', 'C/C++');
INSERT INTO test1(id, name, city, memo) VALUES ('d', '이기자', '서울', NULL);

INSERT INTO test2 (num, id, score) VALUES (1,'a',80);
INSERT INTO test2 (num, id, score) VALUES (2,'b',70);
INSERT INTO test2 (num, id, score) VALUES (3,'c',90);
INSERT INTO test2 (num, id, score) VALUES (4,'d',85);
COMMIT;

-- 테이블 test1과 test2를 조인해서 뷰를 만들어보자.

CREATE OR REPLACE VIEW testView1
AS
SELECT t1.id, num, name, memo, score
FROM test1 t1
JOIN test2 t2 ON t1.id = t2.id;

-- test1 테이블을 이용하여 작성

CREATE OR REPLACE VIEW testView2
AS
SELECT id, name, city
FROM test1;

-- 뷰가 만들었는지 확인

SELECT * FROM tab;

지금 만들어진 뷰 중에 testView1은 두 개의 테이블을 JOIN해서 만든 뷰이다.

-- 뷰에 데이터 추가

이 경우에, 뷰를 통한 데이터 추가는 에러가 발생한다.

INSERT INTO testView1(id, num, name, city, memo, score)
    VALUES ('e', 5, '너자바', '부산', null, 80);

단순 뷰(하나의 테이블로 만들어진 뷰)가 아닌 경우 데이터 추가가 불가능하다.

반면, 하나의 테이블로 이루어진 뷰는 데이터 추가가 가능하다.

INSERT INTO testView2(id, name, city) VALUES('e', '너자바', '부산');

단순뷰는 제약 조건을 위반하지 않으면 추가가 가능하다. 위 테이블의 경우 id, name, city가 모두 NOT NULL이므로 값이 다 입력되어야 하는 제약조건이 있다. INSERT할 때 제약조건을 만족했으므로 데이터의 추가가 잘 되었다.

INSERT INTO testView2(id, name) VALUES ('f', '안된다');

위 경우는 city가 NULL이 될 수 없기 때문에 에러가 발생한다.

-- 뷰를 이용한 데이터 수정

하나의 테이블로 만들어졌던 뷰 testView2는 데이터 수정이 가능하다.

UPDATE testView2 SET name='가나다' WHERE id = 'a';

하지만 테이블 2개를 JOIN했던 testView1은 수정이 불가능하다.

-- WITH CHECK OPTION 

-- testView2 뷰를 city가 서울인 자료만 갖는 뷰로 수정해보자.

CREATE OR REPLACE VIEW testView2
AS
SELECT id, name, city
FROM test1
WHERE city = '서울';

-- testView2 뷰를 이용하여 id='a'의 city를 '제주'로 변경해보자.

UPDATE testView2 SET city = '제주' WHERE id = 'a';

SELECT * FROM testView2;
SELECT * FROM test1;

ID가 a인 사람의 city가 제주로 변경되었음을 확인할 수 있다.

-- testView2 뷰에 WITH CHECK OPTION을 부여해서 서울이 아닌 값으로 수정하지 못하도록 설정

CREATE OR REPLACE VIEW testView2
AS
SELECT id, name, city
FROM test1
WHERE city = '서울'
WITH CHECK OPTION;

UPDATE testView2 SET city = '제주' WHERE id = 'd';

WITH CHECK OPTION이 '서울'인 것을 바꾸지 못하게 설정해놨으므로 city를 바꿀 수 없다. 

UPDATE testView2 SET name = '제주도안가' WHERE id = 'd';

하지만 WITH CHECK OPTION이 걸려있지 않은 name항목은 수정이 가능했다.

뷰 삭제

DROP VIEW testView1;
DROP VIEW testView2;

뷰 ?

사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블 또는 뷰를 기반으로 만들어진 가상 테이블(논리 테이블)이다.

뷰는 데이터를 가지고 있지 않으며, SQL만 저장되어 있다.

뷰 생성 및 삭제

뷰를 생성하거나 삭제하기 위해서는 사용자는 시스템 권한 중의 하나인 CREATE VIEW 권한을 가져야 한다.

- 사용자의 시스템 권한(privilege) 확인

SELECT * FROM user_sys_privs;

- 사용자에게 CREATE VIEW 권한 설정 (system 또는 sys 계정에서 실시)

GRANT CREATE VIEW TO 사용자;

여태껏 판매현황을 출력할 때 마다 JOIN을 이용해서 계속 테이블을 했어야 했는데 이것을 VIEW 뷰로 만들면 뷰만 출력하면 된다. 

-- 뷰 만들기

CREATE VIEW panmai
AS 
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName, qty
FROM book b 
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum;

-- 뷰 SELECT

SELECT * FROM panmai;

이제 판매내역을 위해 일일히 다 JOIN할 필요 없이 뷰 테이블을 선택하면 된다.

-- 뷰 수정

CREATE OR REPLACE VIEW panmai
AS 
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName, qty, qty*bPrice amt
FROM book b 
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum;

수정 시에는 CREATE OR REPLACE VIEW를 붙여서 없으면 만들고 있으면 수정한다는 명령어를 붙이면 된다.

수정된 결과가 잘 나왔다.

-- 뷰 컬럼 확인

SELECT * FROM col WHERE tname = 'PANMAI';
DESC panmai;

일반 테이블의 컬럼을 확인할때와 마찬가지의 방식이다.

-- 뷰 소스 확인

뷰는 SQL만 저장되어있는 것이라고 위에 기술했는데, 그렇다면 그 SQL을 보기 위해서 어떻게 해야할까?

SELECT * FROM user_views;

이 쿼리를 통해 계정이 가지고 있는 뷰를 볼 수 있다. 거기서 TEXT 칼럼을 통해 확인할 수 있다.

뷰는 PURGE 없이 삭제해도 휴지통에 가지않고 바로 삭제된다.

DROP VIEW 뷰테이블명;