KITRI 공공데이터 기반 JAVA Programming과정 5일차

INSERT INTO PRODUCT(PROD_NO, PROD_NAME, PROD_PRICE) 

VALUES('C001', '아반테', '1000')

INSERT INTO PRODUCT(PROD_NO, PROD_NAME, PROD_PRICE) 

VALUES('C002', '소나타', '2000')

INSERT INTO PRODUCT(PROD_NO, PROD_NAME, PROD_PRICE) 

VALUES('C003', '그랜저', '1000')

INSERT INTO PRODUCT(PROD_NO, PROD_NAME, PROD_PRICE) 

VALUES('C004', '에쿠스', '4000')

INSERT INTO order_line(order_info_no, order_line_no, order_prod_no, quantity)

values('2', '1', 'C001', 1)

INSERT INTO order_line(order_info_no, order_line_no, order_prod_no, quantity)

values('2', '2', 'C003', 2)

INSERT INTO order_line(order_info_no, order_line_no, order_prod_no, quantity)

values('2', (SELECT max(order_line_no) + 1

           FROM order_line

           WHERE order_info_no = 2), 'C004', 3)

--1번 주문서의 상세내역 추가

INSERT INTO order_line(order_info_no, order_line_no, order_prod_no, quantity)

values(1, (SELECT NVL(max(order_line_no) + 1, 0)

           FROM order_line

           WHERE order_info_no = 1), 'C004', 1)

--시퀀스객체 생성

CREATE SEQUENCE order_seq

START WITH 10

INCREMENT BY 2

MAXVALUE 20

MINVALUE 5

CYCLE

CACHE 3

--일련번호 발급

SELECT order_seq.NEXTVAL  FROM dual  --10

SELECT order_seq.NEXTVAL  FROM dual  --12

SELECT order_seq.NEXTVAL  FROM dual  --14

SELECT order_seq.NEXTVAL  FROM dual  --16

SELECT order_seq.NEXTVAL  FROM dual  --18

SELECT order_seq.NEXTVAL  FROM dual  --20 최대값도달

SELECT order_seq.NEXTVAL  FROM dual  --5 최소값으로 이동

--현재일련번호 확인 : 1회이상 NEXTVAL사용후에만 CURRVAL사용가능

SELECT order_seq.CURRVAL  FROM dual

--시퀀스의 NEXTVAL과 CURRVAL은 subquery에서 사용불가

DROP SEQUENCE order_seq

CREATE SEQUENCE order_seq  --default로 만듬. 1부터 시작해서 1씩 증가.

DELETE FROM order_line

DELETE FROM order_info

INSERT INTO order_info(order_no, order_id)

VALUES(order_seq.NEXTVAL, 'test')

SELECT *

FROM order_info

INSERT INTO order_line(order_info_no, order_line_no, order_prod_no, quantity)

VALUES(order_seq.CURRVAL, (SELECT NVL(MAX(order_line_no), 0) + 1

                           FROM order_line

                           WHERE order_info_no = 1),

       'C004',

       1

)

CREATE TABLE t1(

a number,

b varchar2(10)

)

CREATE SEQUENCE seq1

CREATE OR REPLACE VIEW view1

AS

SELECT * FROM t1

DESC USER_TAB_PRIVS_RECD

--SELECT * FROM USER_TAB_PRIVS_RECD

--SELECT * FROM USER_TAB_PRIVS_MADE