sql 연습

--부모 테이블의 기본키가 자식테이블의 외래키로 전이됨
--자식테이블의 외래키가 부모테이블의 기본키를 참조함

--insert
--부모테이블 -> 자식테이블(외래키데이터는 부모테이블의 기본키데이터가 있어야함)
INSERT INTO BOOK_INFO(BOOK_ID, NAME, UNIT_PRICE, AUTHOR, DESCRIPTION, PUBLISHER
, CATEGORY, UNITS_IN_STOCK, TOTAL_PAGES, RELEASE_DATE, CONDITION)
VALUES('ISBN1234','이순신의여행',10000,'이순신','내용','삼성출판사'
,'소설',1000,100,'2023-07-12','신규도서');


INSERT INTO ATTACH(SEQ, BOOK_ID, FILENAME)
VALUES(
(SELECT NVL(MAX(SEQ),0) + 1 FROM ATTACH),'ISBN1234','이순신.jpg'
);

SELECT NVL(MAX(SEQ),0) FROM ATTACH;
--delete 순서


--SUBSTR(문자열, 시작번호, 글자수)
SELECT NVL(MAX(BOOK_ID),'ISBN1234')
      ,SUBSTR(NVL(MAX(BOOK_ID),'ISBN1234'),1,4)
      || TRIM(SUBSTR(NVL(MAX(BOOK_ID),'ISBN1234'),5)+1)
FROM BOOK_INFO;


--카티전 프로덕트
--LEFT OUTER JOIN
--오라클 사투리
SELECT A.BOOK_ID, A.NAME, A.UNIT_PRICE, A.AUTHOR, A.DESCRIPTION
     , A.PUBLISHER, A.CATEGORY, A.UNITS_IN_STOCK, A.TOTAL_PAGES
     , A.RELEASE_DATE, A.CONDITION
     ,B.SEQ, B.FILENAME
FROM BOOK_INFO A, ATTACH B
WHERE A.BOOK_ID = B.BOOK_ID(+);

--표준어 ANSI 표준
SELECT A.BOOK_ID, A.NAME, A.UNIT_PRICE, A.AUTHOR
     , SUBSTR(A.DESCRIPTION,1,7) || '..</P>' DESCRIPTION
     , A.PUBLISHER, A.CATEGORY, A.UNITS_IN_STOCK, A.TOTAL_PAGES
     , A.RELEASE_DATE, A.CONDITION
     ,B.SEQ, B.BOOK_ID, B.FILENAME
FROM BOOK_INFO A LEFT OUTER JOIN ATTACH B
ON (A.BOOK_ID = B.BOOK_ID)
ORDER BY A.BOOK_ID DESC;


--자식테이블 -> 부모테이블

create or replace FUNCTION FN_GETCAMEL(COLUMN_NAME IN VARCHAR2)
RETURN VARCHAR2
IS
RSLT VARCHAR2(30);
BEGIN
--카멜표기로 변환(SITE_NUM -> siteNum)
SELECT LOWER(SUBSTR(REPLACE(INITCAP(COLUMN_NAME),'_'),1,1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME),'_'),2) INTO RSLT
FROM DUAL;
--리턴
RETURN RSLT;
END;
/

--구글 카멜변환(https://heavenly-appear.tistory.com/270)
SELECT COLUMN_NAME
, DATA_TYPE
, CASE WHEN DATA_TYPE='NUMBER' THEN 'private int ' || FN_GETCAMEL(COLUMN_NAME) || ';'
WHEN DATA_TYPE IN('VARCHAR2','CHAR') THEN 'private String ' || FN_GETCAMEL(COLUMN_NAME) || ';'
WHEN DATA_TYPE='DATE' THEN 'private Date ' || FN_GETCAMEL(COLUMN_NAME) || ';'
ELSE 'private String ' || FN_GETCAMEL(COLUMN_NAME) || ';'
END AS CAMEL_CASE
, '<result property="'||FN_GETCAMEL(COLUMN_NAME)||'" column="'||COLUMN_NAME||'" />' RESULTMAP
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'BOOK_INFO'; 


select * from book_info;


--데이터 다중 생성
declare
begin
    for I in 1301..1457 loop
    INSERT INTO BOOK_INFO(BOOK_ID, NAME, UNIT_PRICE, AUTHOR, DESCRIPTION,
                PUBLISHER, CATEGORY, TOTAL_PAGES)
    VALUES('ISBN'||I,'제목'||I,12000,'개똥이','내용'||I,
            '개똥이출판사','소설',1000);
    end loop;
    commit;
end;

/
WITH U AS (
    SELECT ROW_NUMBER() OVER(ORDER BY T.BOOK_ID DESC) RNUM
        , T.*
    FROM
    (
        SELECT A.BOOK_ID, A.NAME
             , A.UNIT_PRICE, A.AUTHOR
             , SUBSTR(A.DESCRIPTION,1,7) || '..</P>' DESCRIPTION
             , A.PUBLISHER, A.CATEGORY, A.UNITS_IN_STOCK, A.TOTAL_PAGES
             , A.RELEASE_DATE, A.CONDITION
             , B.SEQ, B.BOOK_ID AS ATTACH_BOOK_ID, B.FILENAME
        FROM BOOK_INFO A
        LEFT OUTER JOIN ATTACH B 
        ON (A.BOOK_ID = B.BOOK_ID)
        WHERE 1=1
        AND (
            CATEGORY     LIKE '%' || '개똥이' || '%' OR
            NAME         LIKE '%' || '개똥이' || '%' OR
            DESCRIPTION  LIKE '%' || '개똥이' || '%' OR
            AUTHOR       LIKE '%' || '개똥이' || '%' OR
            PUBLISHER    LIKE '%' || '개똥이' || '%'
        )
    ) T
)
SELECT U.*
FROM U
WHERE U.RNUM BETWEEN 1 AND 10;
/

--SCALAR서브쿼리
SELECT A.SEQ
    ,A.BOOK_ID
    ,(SELECT B.NAME FROM BOOK_INFO B WHERE B.BOOK_ID = A.BOOK_ID) NAME
    ,A.FILENAME
FROM (
    --INLINE VIEW
    SELECT * FROM ATTACH 
    WHERE SEQ = 1
) A;

--ROWNUM : 행번호, ROWID : 행의 고유 아이디
WITH T AS(
    SELECT ROW_NUMBER() OVER(ORDER BY SEQ DESC) RNUM
          ,SEQ, BOOK_ID, FILENAME
    FROM ATTACH
)
SELECT * FROM T;


SELECT NAME FROM BOOK_INFO WHERE BOOK_ID = 'ISBN1234';

--집계함수 5총사 : (SUM,AVG,MAX, MIN, COUNT)
SELECT COUNT(*) FROM BOOK_INFO;

--LEFT OUTER JOINE
select A.BOOK_ID, NAME, UNIT_PRICE, AUTHOR, DESCRIPTION
     , PUBLISHER, CATEGORY, UNITS_IN_STOCK, TOTAL_PAGES, RELEASE_DATE, CONDITION
     , B.SEQ,B.BOOK_ID,B.FILENAME
from book_info A , ATTACH B
where A.book_id=B.BOOK_ID(+)
AND   A.BOOK_ID = 'ISBN1452';
--ANSI 표준
select A.BOOK_ID, NAME, UNIT_PRICE, AUTHOR, DESCRIPTION
     , PUBLISHER, CATEGORY, UNITS_IN_STOCK, TOTAL_PAGES, RELEASE_DATE, CONDITION
     , B.SEQ,B.BOOK_ID,B.FILENAME
from book_info A LEFT OUTER JOIN ATTACH B
ON(A.book_id=B.BOOK_ID)
WHERE A.BOOK_ID = 'ISBN1452';

SELECT SEQ, BOOK_ID, FILENAME FROM ATTACH;

SELECT SEQ, BOOK_ID, FILENAME FROM ATTACH;

--고등어, 갈치, 꽁치
--등푸른생성 주세요
DELETE FROM BOOK_INFO
WHERE BOOK_ID='ISBN1527';
--업데이트 세대여
UPDATE BOOK_INFO
SET    NAME=,UNIT_PRICE=,AUTHOR,
       PUBLISHER=,RELEASE_DATE=,
       UNITS_IN_STOCK=, CATEGORY,
       DESCRIPTION=, CONDITION=
WHERE  BOOK_ID=;

UPDATE ATTACH
SET    FILENAME =
WHERE  SEQ = ;

--CORRRELATED SUBQUERY(상관관계 서브쿼리)
SELECT A.SEQ
      ,A.BOOK_ID
      ,A.FILENAME
FROM   ATTACH A
WHERE  A.SEQ = (
        SELECT MIN(B.SEQ)
        FROM ATTACH B
        WHERE B.BOOK_ID = A.BOOK_ID
    );
    
    

UPDATE ATTACH A
SET    A.FILENAME = '개똥이'
WHERE  A.SEQ = (
        SELECT MIN(B.SEQ)
        FROM ATTACH B
        WHERE B.BOOK_ID = A.BOOK_ID --*********************
    )
AND     A.BOOK_ID = 'ISBN1234';


DELETE FROM BOOK_INFO
WHERE BOOK_ID = 'ISBN1301';

--참조무결성
/*
제한(RESTRICT) : 부모의 기본 데이터를 참조하고 있는 자식 테이블의 외래키가 있을 경우
                 해당 부모의 기본키 데이터를 삭제할 수 없음
연쇄(CASCADE)  : 부모를 삭제하면 해당 기본키 데이터를 참조하고 있는 자식의 데이터도
                 함께 삭제됨
널값으로 처리(NULLIFY) : 부모를 삭제하면 해당 기본키 데이터를 참조하고 있는 자식의 외래키
                        데이터만 NULL로 변경
*/

SELECT * FROM ATTACH WHERE BOOK_ID = 'ISBN1455';
SELECT * FROM ATTACH WHERE BOOK_ID = 'ISBN1234';


MERGE INTO ATTACH A
USING DUAL ON(A.BOOK_ID = 'ISBN1455')
WHEN MATCHED THEN   --조건에 해당하는 데이터가 있음
    UPDATE 
    SET    A.FILENAME = '개똥이2'
    WHERE  A.SEQ = (
            SELECT MIN(B.SEQ)
            FROM ATTACH B
            WHERE B.BOOK_ID = A.BOOK_ID
        )
WHEN NOT MATCHED THEN   --조건에 해당하는 데이터가 없음
    INSERT (SEQ, BOOK_ID, FILENAME)
    VALUES((SELECT NVL(MAX(SEQ),0) + 1 FROM ATTACH),'ISBN1455','개똥이')
;

 

 

 

 

 

ITEM 테이블

--CLOB : Character Large OBject(4GB)
CREATE TABLE ITEM(
    ITEM_ID NUMBER,
    ITEM_NAME VARCHAR2(60),
    PRICE NUMBER,
    DESCRIPTION CLOB,
    PICTURE_URL VARCHAR2(600),
    CONSTRAINT PK_ITEM PRIMARY KEY(ITEM_ID)
);

CREATE TABLE ITEM2(
    ITEM_ID NUMBER,
    ITEM_NAME VARCHAR2(60),
    PRICE NUMBER,
    DESCRIPTION CLOB,
    PICTURE_URL VARCHAR2(600),
    PICTURE_URL2 VARCHAR2(600),
    CONSTRAINT PK_ITEM2 PRIMARY KEY(ITEM_ID)
);

CREATE TABLE ITEM3(
    ITEM_ID NUMBER,
    ITEM_NAME VARCHAR2(60),
    PRICE NUMBER,
    DESCRIPTION CLOB,
    CONSTRAINT PK_ITEM3 PRIMARY KEY(ITEM_ID)
);

CREATE TABLE ITEM_ATTACH(
    FULLNAME VARCHAR2(600) NOT NULL,
    ITEM_ID NUMBER,
    REGDATE DATE,
    CONSTRAINT PK_ITEM_ATTACH PRIMARY KEY(FULLNAME),
    CONSTRAINT FK_ITEM_ATTACH FOREIGN KEY(ITEM_ID) REFERENCES ITEM3(ITEM_ID)
);

-- 2023.08.10
INSERT INTO ITEM(ITEM_ID, ITEM_NAME, PRICE, DESCRIPTION, PICTURE_URL)
VALUES((SELECT NVL(MAX(ITEM_ID),0)+1 FROM ITEM),'태블릿',12000,'설명글','개똥이.jpg');

SELECT NVL(MAX(ITEM_ID),0)+1 FROM ITEM;

commit;

INSERT INTO ITEM3(ITEM_ID, ITEM_NAME, PRICE, DESCRIPTION)
VALUES((SELECT NVL(MAX(ITEM_ID),0)+1 FROM ITEM3),'태블릿',12000,'설명글');

ROLLBACK;

SELECT NVL(MAX(ITEM_ID),0)+1 FROM ITEM3;

--다중 insert
INSERT ALL
INTO ITEM_ATTACH(FULLNAME, ITEM_ID, REGDATE) VALUES('개똥이.jpg',1,sysdate)
INTO ITEM_ATTACH(FULLNAME, ITEM_ID, REGDATE) VALUES('개똥이2.jpg',1,sysdate)
INTO ITEM_ATTACH(FULLNAME, ITEM_ID, REGDATE) VALUES('개똥이3.jpg',1,sysdate)
SELECT * FROM DUAL;

COMMIT;


-- 2023.08.11


--트랜잭션 : 데이터베이스를 변경(I/U/D)하기 위해 수행되어야 할 논리적 단위
--          여러개의 SQL로 구성됨
-- ACID(원일고지)
/*
원자성(Atomicity) : 흑백논리. All or Nothing
일관성(Consistency) : 성공 시 일관성(무결성) 유지
고립성(Isolation) : 외부 간섭 없음. 화장실 원리.
지속성(Durability) : 성공시 영속적 보관
*/
INSERT INTO ATTACH(SEQ, BOOK_ID, FILENAME)
VALUES(100,'ISBN1234','개똥이.jpg');

rollback;