개발새발, 써놓기

시퀀스 (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 뷰테이블명;

-- 테이블에 컬럼 추가

ALTER TABLE 테이블명 ADD (컬럼명 자료형(크기));

-- 테이블 컬럼폭 또는 크기 변경

ALTER TABLE 테이블명 MODIFY (컬럼명 자료형(크기));

-- 테이블 컬럼명 변경

 ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 새로운컬럼명

-- 테이블 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명

-- ORACLE 11g 에서 페이징 처리 방법(3페이지, 20개 건너뛰고 10개 가져오기)

SELECT * FROM (
    SELECT ROWNUM rnum, tb.* FROM (
        SELECT num, name, subject, hitCount, reg_date
        FROM bbs
        [ WHERE 조건 ]
        ORDER BY num DESC
    ) tb WHERE ROWNUM <= 30
) WHERE rnum >=21;

-- ORACLE 12c 이상에서 페이징 처리 방법(3페이지, 20개 건너뛰고 10개 가져오기)

SELECT num, name, subject, hitCount, reg_date
FROM bbs
[ WHERE 조건 ]
ORDER BY num DESC
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

    VARCHAR2(길이) : 가변 길이 문자 데이터를 저장. 최대 길이는 4000자. 반드시 길이를 명시
    NCHAR, NVARCHAR2 : 다양한 언어의 문자 값(국가별 언어 집합 : NLS)을 저장하고 조회 가능
    NUMBER : 숫자 - NUMBER(P, S)
        P(1~38)는 정밀도로 전체 자리수를 나타내며 기본 값이 38이고 S(-84~127)는 소수점 이하의 자릿수이다.
    DATE : 날짜, 『년/월/일 시:분:초』
    TIMESTAMP : 날짜, 『년/월/일 시:분:초.밀리초』
    CLOB : 대용량의 문자 저장

-- 예제

-- 비회원 판매 현황 : cNum, cName, bCode, bName, sDate, bPrice, qty

단, 고객중 책을 한권도 구매하지 않은 고객도 출력

book(bCode, bPrice)

dsale(sNum, bCode, qty)

sale(sNum, cNum)

cus(cNum, cName)

SELECT c.cNum, cName, d.bCode, bName, sDate, bPrice, qty
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
RIGHT OUTER JOIN cus c ON c.cNum = s.cNum;

답 >>

SELECT c.cNum, cName, NVL(SUM(bPrice * qty), 0) 합
FROM cus c
LEFT OUTER JOIN sale s ON c.cNum = s.cNum
LEFT OUTER JOIN dsale d ON s.sNum = d.sNum
LEFT OUTER JOIN book b ON d.bCode = b.bCode
GROUP BY c.cNum, cName
ORDER BY c.cNum;
     
SELECT c.cNum, cName, NVL(SUM(bPrice * qty), 0) 합
FROM book b
JOIN dsale d ON b.bCode =d.bCode
JOIN sale s ON d.sNum = s.sNum
RIGHT OUTER JOIN cus c ON s.cNum = c.cNum
GROUP BY c.cNum, cName
ORDER BY c.cNum;

-- 년도별 고객 누적 판매금액이 가장 많은 값 출력

-- 판매년도, cNum, cName, bPrice* qty 합

-- 년도 오름차순으로

SELECT c.cNum, cName, SUM(bPrice * qty) 합
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON c.cNum = s.cNum
GROUP BY s.cNum, cName;

이 쿼리로 실행해봐도 자꾸 오류가 발생해서 

비교해보았더니 GROUP BY할때 s.cNum으로 GROUP 화 한거면 SELECT 에도 c.cNum이 아니라 s.cNum으로 써줘야 한다.

SELECT TO_CHAR(sdate, 'YYYY') 년도, s.cNum, cName, SUM(bPrice * qty) 합, 
    RANK() OVER(PARTITION BY TO_CHAR(sdate, 'YYYY') ORDER BY SUM(bPrice * qty) DESC) 순위
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON c.cNum = s.cNum
GROUP BY s.cNum, cName, TO_CHAR(sdate, 'YYYY');

여기서 년도별로 1등만 출력하면 된다.

WITH sb AS (
SELECT TO_CHAR(sdate, 'YYYY') 년도, s.cNum, cName, SUM(bPrice * qty) 합, 
    RANK() OVER(PARTITION BY TO_CHAR(sdate, 'YYYY') ORDER BY SUM(bPrice * qty) DESC) 순위
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON c.cNum = s.cNum
GROUP BY s.cNum, cName, TO_CHAR(sdate, 'YYYY')
)
SELECT 년도, cNum, cName, 합 FROM sb
WHERE 순위 = 1;

-- 년도의 월별 서적의 판매 수량의 합 구하기 : 년도 오름차순, 책코드 오름차순

-- 년도 책코드 책이름 1월 2월 3월 ... 12월

SELECT TO_CHAR(sDate, 'YYYY')년도, TO_CHAR(sDate, 'MM') 월, b.bCode 책코드, bName 책이름, qty 판매수량
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
GROUP BY b.bCode, bName, TO_CHAR(sDate, 'YYYY'), TO_CHAR(sDate, 'MM'), qty
ORDER BY 년도, 책코드;

일단 년도, 월 별로 몇개가 판매되었는지 구해보았다.

답 확인 >>

SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, bName, qty
FROM  book b
JOIN  dsale d ON  b.bCode = d.bCode
JOIN  sale s  ON  d.sNum = s.sNum;

SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, bName, 
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '01', qty)),0) M01,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '02', qty)),0) M02,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '03', qty)),0) M03,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '04', qty)),0) M04,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '05', qty)),0) M05,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '06', qty)),0) M06,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '07', qty)),0) M07,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '08', qty)),0) M08,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '09', qty)),0) M09,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '10', qty)),0) M10,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '11', qty)),0) M11,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '12', qty)),0) M12
FROM  book b
JOIN  dsale d ON  b.bCode = d.bCode
JOIN  sale s  ON  d.sNum = s.sNum
GROUP BY TO_CHAR(sDate, 'YYYY'), b.bCode, bName
ORDER BY 년도, bCode;

-- 년도별 소계
SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, bName, 
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '01', qty)),0) M01,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '02', qty)),0) M02,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '03', qty)),0) M03,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '04', qty)),0) M04,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '05', qty)),0) M05,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '06', qty)),0) M06,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '07', qty)),0) M07,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '08', qty)),0) M08,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '09', qty)),0) M09,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '10', qty)),0) M10,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '11', qty)),0) M11,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '12', qty)),0) M12
FROM  book b
JOIN  dsale d ON  b.bCode = d.bCode
JOIN  sale s  ON  d.sNum = s.sNum
GROUP BY TO_CHAR(sDate, 'YYYY'), ROLLUP((b.bCode, bName))
ORDER BY 년도, bCode;

-- 년도별 소계, 총계
SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, bName, 
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '01', qty)),0) M01,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '02', qty)),0) M02,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '03', qty)),0) M03,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '04', qty)),0) M04,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '05', qty)),0) M05,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '06', qty)),0) M06,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '07', qty)),0) M07,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '08', qty)),0) M08,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '09', qty)),0) M09,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '10', qty)),0) M10,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '11', qty)),0) M11,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '12', qty)),0) M12
FROM  book b
JOIN  dsale d ON  b.bCode = d.bCode
JOIN  sale s  ON  d.sNum = s.sNum
GROUP BY ROLLUP(TO_CHAR(sDate, 'YYYY'), (b.bCode, bName))
ORDER BY 년도, bCode;

예제를 통해 알아보자.

예제를 위한 테이블 생성 및 자료 삽입

CREATE TABLE grade1 
(
    grade VARCHAR2(10) PRIMARY KEY,
    score NUMBER(3)
);
INSERT INTO grade1 ( grade, score ) VALUES ('A', 90);
INSERT INTO grade1 ( grade, score ) VALUES ('B', 80);
INSERT INTO grade1 ( grade, score ) VALUES ('C', 70);
INSERT INTO grade1 ( grade, score ) VALUES ('D', 60);
INSERT INTO grade1 ( grade, score ) VALUES ('F', 0);
COMMIT;

CREATE TABLE score1
(
    hak VARCHAR2(10) PRIMARY KEY,
    score NUMBER(3)
);

INSERT INTO score1 ( hak, score ) VALUES('1', 75);
INSERT INTO score1 ( hak, score ) VALUES('2', 50);
INSERT INTO score1 ( hak, score ) VALUES('3', 90);
INSERT INTO score1 ( hak, score ) VALUES('4', 80);
INSERT INTO score1 ( hak, score ) VALUES('5', 65);
COMMIT;

위의 테이블형태로 Oracle에서 출력하고 싶다.

먼저

위 형태를 출력해보자.

SELECT hak, score, 
(SELECT MAX(score) FROM grade1 WHERE score <= score1.score) gscore
FROM score1;

SELECT hak, s1.score, grade FROM (
    SELECT hak, score, 
        (SELECT MAX(score) FROM grade1 WHERE score <= score1.score) gscore
    FROM score1
) s1
JOIN grade1 s2 ON s1.gscore = s2.score
ORDER BY hak;