TSQL

-QUERY : SELECT

-DDL : CREATE [객체 생성] / ALTER [객체구조 변경]/ DROP [객체구조 변경] / TRUNCATE [모든 자료 제거]

-DML : INSERT [자료추가] / UPDATE [자료변경] / DELETE [자료삭제] / MERGE [자료 병합]

트랜젝션 337

-> 위키 독스 그림을 통해 참조.

- 트랜젝션은 하나의 시퀀스와 같아서, 시작할때 COMMIT으로 시작하여, COMMIT으로 끝이 난다. 만약 마무리 작업에서 COMMIT을 완료하지 않았다면, 작업은 완료되지 않아, 해당 시퀀시는 실제로 데이터베이스에 반영되지 않는다.

데이터 무결성 제약 조건

CASCADE 옵션(PRIMARY KEfY, FOREIGN KEY 비활성화)

--제약조건 삭제[자식 참조키도 연속삭제]

ALTER TABLE category

DROP PRIMARY KEY CASCADE

COMMIT;

--제약조건 확인

SELECT * FROM user_constraints WHERE table_name IN

('CATEGORY', 'PRODUCT')

--제약조건 복원

ALTER TABLE category

ADD CONSTRAINT category_no_pk PRIMARY KEY(category_no)

[자식 FK]

ALTER TABLE product

ADD CONSTRAINT product_category_no_fk FOREIGN KEY(category_no)

REFERENCES category(category_no) ON DELETE CASCADE

//ON DELETE 명령어를 통해, 자식에서 컬럼이 삭제된다면, 부모에서도 컬럼이 삭제되게

설정할수 있다.

가상 테이블

VIEW

기본 테이블을 변경하여 자주 사용하게 될 경우, 이를 북마크처럼 가상의 테이블로 만들어 사용할수 있는데. 이를 VIEW라고 한다.

CREATE OR REPLACE VIEW

FORCE | NOFORCE

WITH CHECK OPTION

WITH READ ONLY

2) ROWNUM (TOP-N 구하기)

출력된 컬럼에 순번을 지정해줍니다.
가령 ORDER BY절을 이용하여, 급여나, 입사일을 오름차순 정렬한뒤, ROWNUM을 처리한다면, 상위 입사일 순위 10명, 급여순위 5명 등의 데이터를 손쉽게 구할수 있습니다.

2-1) 인라인 뷰 (FROM 절의 서브쿼리)

인라인 뷰는, FROM절에 서브쿼리가 들어갈 경우를 말합니다.
만약 상위 급여 TOP-N을 구한다면, 서브쿼리를 이용해 사원의 이름 사번 급여를 SELECT절과 , ORDER BY를 이용해 SALARY순으로 출력하는 인라인 뷰를 만들고, 메인 쿼리에 WHERE문에 ROWNUM을 이용하면, 인라인 뷰를 이용한 TOP-N을 구할수 있습니다.

--수당을 받지 않는 사원들을 급여순으로 내림차순하고, 5명까지 출력하시오.

로직 순서

1) 내림차순 2) ROWNUM과 비교

SELECT ROWNUM, employee_id, first_name, salary

FROM

(

SELECT employee_id, first_name, salary

FROM employees

WHERE commission_pct IS NULL

ORDER BY salary DESC

)

WHERE ROWNUM BETWEEN 1 AND 5

책정리: p265,270,277,279,290,301,303,311,318,323,327,329,334,337,343,350,368,409,

,416, 422,424,446,449~452,455,458,462,466

예제)

**주문 기본정보 테이블

DESC order_info

**주문 상세 정보(order_line)테이블

주문번호 order_no number

상세번호 line_no number(2)

상품번호(order_prod_no Varchar2(10)) 상품번호와 동일해야하므로

수량 (order_quantity number(3))

CREATE TABLE order_line

(

order_no number,

line_no number(2),

order_prod_no number(10),

order_quantity number(3)

)

ALTER TABLE order_line

MODIFY order_no CONSTRAINT order_no_pk PRIMARY KEY

MODIFY line_no CONSTRAINT order_no_pk PRIMARY KEY

ADD order_prod_no CONSTRAINT order_prod_no_fk

FOREIGN KEY(prod_no) REFERENCES product(prod_no)

ALTER TABLE order_line

ADD order_prod_no CONSTRAINT order_prod_no_fk

FOREIGN KEY(order_prod_no) REFERENCES product(prod_no)

ALTER TABLE order_info

ADD order_no CONSTRAINT order_no_fk

FOREIGN KEY(order_no) REFERENCES order_line(order_no)

===판서 답

CREATE TABLE order_line

(

order_no number,

line_no number(2),

order_prod_no varchar2(10),

order_quantity number(3),

CONSTRAINT order_line_pk PRIMARY KEY(order_no, line_no),

CONSTRAINT order_line_order_no_fk FOREIGN KEY(order_no)

REFERENCES order_info(order_no),

CONSTRAINT order_line_prod_no_fk FOREIGN KEY(order_prod_no)

REFERENCES product(prod_no)

)

고객 정보 추가

INSERT INTO customer(id, password, name) VALUES ('test1', 't1', '테스트1')

주문 기본정보

INSERT INTO order_info(order_no, order_id) VALUES (3, 'test1')

주문 상세정보

INSERT INTO order_line(order_no, line_no, order_prod_no, order_quantity) VALUES (1, 4, 'C001', 3)

INSERT INTO order_line(order_no, line_no, order_prod_no, order_quantity) VALUES (3, 2, 'J001', 5)

--주문일자, 주문번호, 주문자ID, 주문자 이름, 주문상세 번호, 주문상품번호, 상품명, 주문수량을 출력하시오.

단, 최근 주문일자부터 출력한다.

SELECT oif.order_dt, oif.order_no, c.name, oif.order_id, oln.line_no, oln.order_prod_no, pd.prod_name, oln.order_quantity

FROM order_info oif JOIN order_line oln

ON oif.order_no = oln.order_no

JOIN customer c

ON oif.order_id = c.id

JOIN product pd

ON oln.order_prod_no = pd.prod_no

ORDER BY oif.order_dt desc

--2016년 4월의 주문내역중 카테고리별 카테고리명과 주문수량을 출력하시오.

SELECT category_no, sum(oln.order_quantity)

FROM product pd JOIN order_line oln

ON pd.prod_no = oln.order_prod_no, order_info oif

WHERE TO_CHAR(oif.order_dt, 'YYYY/DD') = ('2016/04')

GROUP BY category_no

select * from product

select * from order_line

select * from order_info

pd.prod_no = oln.order_prod_no

오후

==============================

SELECT * FROM CATEGORY

INSERT INTO CATEGORY VALUES ('B', '테스트')

DELETE FROM category WHERE category_no = 'B'

COMMIT;

ROLLBACK;

INSERT INTO product(prod_no, prod_name, category_no) VALUES ('B001', '테스트상품', 'B')

DELETE FROM category WHERE category_no = 'B'

DELETE FROM product WHERE category_no = 'B'

-- ORA-02292: 무결성 제약조건(HR.CATEGORY_NO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다

COMMIT;

--제약조건 삭제[자식 참조키도 연속삭제]

ALTER TABLE category

DROP PRIMARY KEY CASCADE

COMMIT;

--제약조건 확인

SELECT * FROM user_constraints WHERE table_name IN

('CATEGORY', 'PRODUCT')

--제약조건 복원

ALTER TABLE category

ADD CONSTRAINT category_no_pk PRIMARY KEY(category_no)

[자식 FK]

ALTER TABLE product

ADD CONSTRAINT product_category_no_fk FOREIGN KEY(category_no)

REFERENCES category(category_no) ON DELETE CASCADE

//ON DELETE 명령어를 통해, 자식에서 컬럼이 삭제된다면, 부모에서도 컬럼이 삭제되게

설정할수 있다.

VIEW : 가상테이블 - 실제 자료는 테이블에 있고 뷰에는 SELECT 구문만 저장됨

- 가상테이블은 SELECT * FROM 뷰 가능

SELECT employee_id, department_id, first_name, salary

FROM employees

WHERE department_id = 50

CREATE VIEW emp50

SELECT employee_id, department_id, first_name, salary

FROM employees

WHERE department_id = 50

// 생성이 가능하나 수정은 할수 없다.

SELECT *

FROM emp50

CREATE OR REPLACE VIEW emp50

SELECT employee_id, department_id, first_name, salary

FROM employees

WHERE department_id = 50

// 위와 달리 CREATE OR REPLACE VIEW를 사용해 수정이 가능하다.

--뷰삭제

DROP VIEW emp50

-- 수당을 받지 않는 사원들을 급여순으로 내림차순

SELECT ROWNUM, empolyee_id, first_name, salary

FROM empolyees

WHERE commission_pct IS NULL

ORDER BY salary DESC

ROWNUM은 ORDER BY보다 먼저 처리가 되기때문에,

ORDER BY를 이용한뒤 ROWNUM을 처리하면, 현재 예제에서 salary가 내림차순이 아닌

임의의 컬럼 순서에서 처리가 된다.

이때는(ROWNUM)을 사용할때는 ORDER BY를 사용하지않고, 서브쿼리를 사용하여야 원하는 데이터값을 구할수있다.

FROM -> WHERE -> GROUP BY-> HAVING -> (ROWNUM처리) -> SELECT -> ORDER BY

--수당을 받지 않는 사원들을 급여순으로 내림차순하고, 5명까지 출력하시오.

로직 순서

1) 내림차순

2) ROWNUM과 비교

SELECT ROWNUM, employee_id, first_name, salary

FROM

(

SELECT employee_id, first_name, salary

FROM employees

WHERE commission_pct IS NULL

ORDER BY salary DESC

)

WHERE ROWNUM BETWEEN 1 AND 5

--수당을 받지 않는 사원들을 급여순으로 내림차순, 11~20명까지 출력하시오.

SELECT r, employee_id, first_name, salary

FROM

(

SELECT ROWNUM r, a.*

FROM

(

SELECT employee_id, first_name, salary

FROM employees

WHERE commission_pct IS NULL

ORDER BY salary DESC

) a

)

WHERE r BETWEEN 11 AND 20

--------------------------------------

시퀀스 : 자동번호 발생용 객체

CREATE SEQUENCE order_seq

START WITH 5

INCREMENT BY 2

MAXVALUE 10

MINVALUE 3

CYCLE

CACHE 4

SELECT order_seq.NEXtVAL FROM dual

SELECT order_seq.CURRVAL FROM dual

[시퀀스 제거]

SELECT * FROM order_info

DROP SEQUENCE order_seq

CREATE SEQUENCE order_seq

START WITH 5

INSERT INTO order_info(order_no, order_id)

VALUES (order_seq.NEXTVAL, 'test')

SELECT * FROM order_info

desc order_line

-- 6번 주문의 상세정보 추가

INSERT INTO order_line

(order_no, line_no, order_prod_no, order_quantity)

VALUES (6,

(

SELECT NVL(MAX(line_no), 0)+1

FROM order_line

WHERE order_no = 6

), 'C001', 4)

INSERT INTO order_line

(order_no, line_no, order_prod_no, order_quantity)

VALUES (6,

(

SELECT NVL(MAX(line_no), 0)+1

FROM order_line

WHERE order_no = 6

), 'F001', 9)

// 주문이 추가되면 LINE_NO가 순차적으로 1씩 증가하며 주문내역의 상세정보가 추가된다.

'디버깅 > Oracle' 카테고리의 다른 글

160406 (0)	2016.04.09
160405 (0)	2016.04.09
160401 (0)	2016.04.09
160331 (0)	2016.04.09
160330 (0)	2016.04.09

Liante의 이야기

060404

트랜젝션 337

데이터 무결성 제약 조건

CASCADE 옵션(PRIMARY KEfY, FOREIGN KEY 비활성화)

가상 테이블

VIEW

CREATE OR REPLACE VIEW

FORCE | NOFORCE

WITH CHECK OPTION

WITH READ ONLY

2) ROWNUM (TOP-N 구하기)

2-1) 인라인 뷰 (FROM 절의 서브쿼리)

인라인 뷰는, FROM절에 서브쿼리가 들어갈 경우를 말합니다.

예제)

'디버깅 > Oracle' 카테고리의 다른 글

티스토리툴바

060404

트랜젝션 337

데이터 무결성 제약 조건

CASCADE 옵션(PRIMARY KEfY, FOREIGN KEY 비활성화)

가상 테이블

VIEW

CREATE OR REPLACE VIEW

FORCE | NOFORCE

WITH CHECK OPTION

WITH READ ONLY

2) ROWNUM (TOP-N 구하기)

2-1) 인라인 뷰 (FROM 절의 서브쿼리)

인라인 뷰는, FROM절에 서브쿼리가 들어갈 경우를 말합니다.

예제)

'디버깅 > Oracle' 카테고리의 다른 글

'디버깅/Oracle' Related Articles

티스토리툴바