[DB] 트랜젝션,시퀀스,ROWNUM,RANK

-트랜잭션

• INSERT, UPDATE, DELETE에서 반드시 하나의 단위로 처리해야할 작업단위(같이 묶어서 하겠다고 처음부터 계획)
• 편의성이 아니라 정보의 무결성과 안정성을 위해서 사용
  • 프로그램 끄면 가상 메모리에 남아있던건 날아감
  • 단점: 무겁다
• UPDATE가 2개의 명령어로 실행할 때 2개 모두 실행 또는 2개 모두 실행이 안되거나 해야하는 트랜잭션
• 자동 커밋:INSERT, UPDATE, DELETE가 바로 테이블에 반영되는 것
• 수동 커밋:INSERT, UPDATE, DELETE가 바로 반영되지 않고 COMMIT해야 실행
  • 수동 커밋상태에서 SQL PLUS 실행, 계정 접속하기 -> 커밋전 상태로 보임
• INSERT, UPDATE, DELETE 명령 트랜잭션 취소는 ROLLBACK
  • 시퀀스는 이미 증가, 롤백과 관련없음
  • 커밋후에도 롤백할 수 있음 = 데이터의 무결성을 유지시켜줌
• 디비버 사용자가 추가한 INSERT는 커밋을 해야 SQLPLUS 사용자에게 보입(반영)니다.
• SQL PLUS
  • 명령보기 : SHOW AUTOCOMMIT;
  • 명령 설정: SET AUTOCOMMIT OFF(ON);
  • 테이블의 제약조건 이름 확인하기 : SELECT * FROM USER_CONSTRAINTS;
• 디비버에서 AUTO COMMIT 해제하기 -> 데이터베이스 메뉴 ->트랜잭션모드
• 롤백: ROLLBACK; —- 처리완료: COMMIT;

-시퀀스

• 유일(UNIQUE)한 값을 생성해주는 오라클 객체
  • 데이베이스관점에는 무결성(정확성) 을 유지할 수 있는 방법.
• 시퀀스 생성시 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성
• 목적: 행을 식별하기위해 (mysql은 시퀀스 없고 auto increment 속성으로 사용)
  • 보통 PRIMARY KEY 값을 생성하기 위해 사용
• 메모리에 Cache되었을 때 SEQUENCE 값의 액세스 효율이 증가
• SEQUENCE는 테이블과는 독립적으로 저장되고 생성→ 하나의 SEQUENCE를 여러 테이블에서 사용 O
  • 실행을 위해서 시퀀스 생성, money 컬럼 추가

    CREATE SEQUENCE pbuy_seq START WITH 1500;    (시퀀스 시작 숫자 1500)
    ALTER TABLE p_buy ADD money number(7) CHECK (money >=10000); --MONEY= 수량*가격 총 구매금액 컬럼

• 웹애플리케이션(인터넷 환경) 개발할 때, JDBC 에서 사용자가 원하는 기능 요청 하나에 sql을 1개씩만 실행
• 프로시저를 이용하면 요청 한번에 대해 많은 SQL을 실행을 할수 있습니다.
• 프로시저에서 트랜잭션을 관리하는 예시: 최소구매금액 10000미만이면 트랜잭션을 롤백하는 예시

  CREATE OR REPLACE PROCEDURE proc_set_money(	  --IN,OUT 입력출력 여부
  	acustom_id IN P_BUY.CUSTOMID %TYPE, --  회원ID		-- 입력 매개변수 IN
  	apcode IN P_BUY.PCODE %TYPE, 	-- 상품코드
  	acnt IN P_BUY.QUANTITY %TYPE, 		-- 수량
  	isSuccess OUT varchar2 -- 출력 매개변수 OUT. 트랜잭션 처리 성공여부 저장. 
  )
  IS 
  	vseq NUMBER;	--변수선언
  	vprice NUMBER;
  BEGIN 
  	INSERT INTO p_buy(buy_IDX,CUSTOMID,PCODE,QUANTITY,BUY_DATE)
  		values (pbuy_seq.nextval, acustom_id,apcode,acnt,sysdate);	-- 매개변수값으로 INSERT
  		
  	SELECT pbuy_seq.currval 		-- 방금 INSERT한 현재 시퀀스 값 조회
  		INTO vseq
  		FROM dual;
  	dbms_output.put_line('방금 INSERT한 현재 시퀀스 값 조회'||VSEQ);	
  
  	SELECT price 
  		INTO vprice 
  		FROM tbl_product tp WHERE pcode=apcode;		-- 상품코드에 대한 가격 조회
  		dbms_output.put_line('방금 INSERT한 현재 상품 값 조회'||VPRICE);
  	UPDATE p_buy SET money = vprice * QUANTITY 
  		WHERE BUY_IDX = vseq;						-- 위 INSERT 한 데이터로 가격*수량 수식 구해서 money 컬럼값 수정
  		
  	dbms_output.put_line('실행 성공');		--값이 만원 이상일때만 성공
  	isSuccess :='success';			-- 프로시저에서 일반변수 대입문 기호 :=
  	commit ;
  	EXCEPTION 			-- EXCEPTION 추가하여 처리 -> 메시지 출력, rollback 
  		WHEN OTHERS THEN 
  		dbms_output.put_line('실행 실패'); --만원 미만 실패
  		rollback ;		-- 오류가 발생한 sql 앞의 insert,update,delete를 취소.
  		isSuccess :='fail';
  END;
  
  -- 실행 예시
  DECLARE 
  	vresult varchar2(20);
  BEGIN                
  proc_set_money('twice','SNACK99',1,VRESULT);  --메시지는 'fail' ,p_buy 테이블 insert 입력값 없어야 함
  
  proc_set_money('mina012','SNACK99',3,VRESULT); --메시지는 성공, P_BUY 테이블에 값이 INSERT 됨
  
  dbms_output.put_line('결과 : ' || vresult );
  END;

• JDBC:JAVA BATABASE CONNECTION . 자바와 DB(오라클, MYSQL..)를 연결하는 프로토콜
  • 코드

    1. 시퀀스 생성
    CREATE SEQUENCE test_seq;
    
    2. 시퀀스 사용을 위한 함수: nextval(다음 시퀀스 값), currval(현재 시퀀스 값)
    SELECT test_seq.nextval FROM dual;
    
    현재 시퀀스 값은 nextval 을 처음 한번 꼭 실행한 후에(값이 만들어진 후) 실행가능
    SELECT test_seq.currval FROM dual;
    
    3. 시퀀스 시작값을 바꾸기
    CREATE SEQUENCE TEST_SEQ2 START WITH 2001;
    
    4. 증가값 바꾸기
    CREATE SEQUENCE TEST_SEQ3 INCREMENT BY 5;
    SELECT TEST_SEQ3.NEXTVAL FROM DUAL ; -- 실행시킬때마다 5씩 올라감

    • 시퀀스를 컬럼에 넣는방법

      1. 시퀀스, 컬럼 만들기 2. 컬럼 PK로 만들기 3. INSERT VALUES에 컬럼명과 시퀀스.NEXTVAL넣기
      
      INSERT INTO TBL_EXBUY (ID, PRODUCTCODE, BUY, BUYDATE,BUYIDX)
      VALUES('hongGD','DOWON123a', 1, TO_DATE('2021-10-21 11:13:25','YYYY-MM-DD HH24:MI:SS'),EXBUY_PK_SEQ.NEXTVAL);

-ROWNUM

• ROWNUM 컬럼: 오라클이 SELECT 결과에 부여하는 행번호, 오라클이 내부적으로 관리를 위한 컬럼

  -1)상품 테이블의 상품 가격을 내림차순으로 정렬하여 조회
  SELECT *
  FROM TBL_PRODUCT tp 
  ORDER BY PRICE DESC ;
  
  -2) ROWNUM 컬럼을 출력, 정렬 이전의 ROWNUM(원하는 결과가 아님)   --ROWNUM은 SELECT된 결과에 붙이는 것..
  SELECT ROWNUM, TP.*
  FROM TBL_PRODUCT TP
  ORDER BY PRICE DESC;
  
  -3)서브 쿼리로 변경 --원하는 ROWNUM출력을 위해서는 서브쿼리로 만들어야함
  SELECT ROWNUM, TP.*,TPCODE.*
  FROM(
  	SELECT *
  	FROM TBL_PRODUCT 
  	ORDER BY PRICE DESC
  	)TP
  
  -4) 결과에 ROWNUM 조건을 적용해봅시다
  SELECT ROWNUM, TP.*
  FROM(
  	SELECT *
  	FROM TBL_PRODUCT 
  	ORDER BY PRICE DESC
  	)TP
  --WHERE ROWNUM=1    --ROWNUM이 1이 포함되는 경우만 가져올 수 있음
  --WHERE ROWNUM=3 --안됨
  
  -5) 중간 일부를 가져오기 위해서 BETWEEN ~ AND  연산.. 4번을 대상으로 서브 쿼리를 또 써야함
  -예시 : 게시판의 글목록에 대해 페이지 번호 적용합니다.
  SELECT *
  FROM (
  	SELECT ROWNUM R, TP.* --괄호 속 ROWNUM에 대한 별칭 필요 (ROWNUM은 컬럼 키워드이기에), 밖에서 사용하기 위해..
  	FROM(
  		SELECT *
  		FROM TBL_PRODUCT 
  		ORDER BY PRICE DESC
  		)TP
  	)
  -WHERE R=3;
  WHERE R BETWEEN 2 AND 4;
  
  -6)오라클 12C 버전부터는 FETCH 명령이 있음  ->4번을 대신하여 사용가능(ROWNUM<3)과 같은 결과
  SELECT *
  FROM TBL_PRODUCT tp 
  ORDER BY PRICE DESC 
  FETCH FIRST 2 ROWS ONLY; --TOP2 가져오기
  
  -여러개 동시에 서브쿼리로 만들기 가능
  SELECT ROWNUM, TP.*,TPCODE.*
  FROM(
  	SELECT *
  	FROM TBL_PRODUCT 
  	ORDER BY PRICE DESC
  	)TP,
  	(
  	SELECT *
  	FROM TBL_PRODUCT 
  	ORDER BY PCODE DESC
  	)TPCODE;

• RANK 함수

  예제: 고객- 상품별 최대 구매 금액을 구하고 해당 상품코드 조회하기
   	ㄴ고객ID로 1차 그룹화, 고객 내에서 상품코드로 2차 그룹화
  
  -1) GROUP BY
  SELECT TB.CUSTOMID , TB.PCODE ,SUM(TP.PRICE*TB.QUANTITY) "총합"    --가격*수량에 대한 그룹화 함수 SUM 실행
  FROM TBL_BUY TB, TBL_PRODUCT TP                                    --진라면 갯수 합해짐
  WHERE TB.PCODE = TP.PCODE
  GROUP BY TB.CUSTOMID, TB.PCODE ;
  
  -2) 1번 결과 정렬
  SELECT TB.CUSTOMID , TB.PCODE ,SUM(TP.PRICE*TB.QUANTITY) MONEY 
  FROM TBL_BUY TB, TBL_PRODUCT TP
  WHERE TB.PCODE = TP.PCODE
  GROUP BY TB.CUSTOMID, TB.PCODE 
  ORDER BY TB.CUSTOMID, MONEY DESC 
  
  -같은 수준의 SELECT에서는 AS로 호출 불가능
  -3)1번에 RANK 함수 적용
  SELECT TB.CUSTOMID , TB.PCODE ,SUM(TP.PRICE*TB.QUANTITY) MONEY, 
  RANK() OVER (PARTITION BY TB.CUSTOMID ORDER BY SUM(TP.PRICE*TB.QUANTITY)DESC) "RANK"
  --PARTITION BY ->GROUP BY랑 비슷함.. 적힌 것끼리 묶음
  --OVER()안에는 어떤 컬럼으로 정렬해서 순위를 매기는지에 대한 내용
  --RANK() OVER (ORDER BY SUM(TP.PRICE*TB.QUANTITY)DESC) "RANK"  --그룹화된  MONEY 순위
  FROM TBL_BUY TB, TBL_PRODUCT TP
  WHERE TB.PCODE = TP.PCODE
  GROUP BY TB.CUSTOMID, TB.PCODE ;
  
  -4) 3번을 서브쿼리로 하여 RANK=1인 조건을 적용해보기
  WITH CUSTOMSALE
  AS
  (SELECT TB.CUSTOMID , TB.PCODE ,SUM(TP.PRICE*TB.QUANTITY) MONEY, 
  RANK() OVER (PARTITION BY TB.CUSTOMID ORDER BY SUM(TP.PRICE*TB.QUANTITY)DESC) "RANK"
  FROM TBL_BUY TB, TBL_PRODUCT TP
  WHERE TB.PCODE = TP.PCODE
  GROUP BY TB.CUSTOMID, TB.PCODE 
  )
  SELECT CUSTOMSALE.CUSTOMID,CUSTOMSALE.PCODE,CUSTOMSALE.MONEY,CUSTOMSALE.RANK
  FROM CUSTOMSALE
  WHERE RANK =1;

-RANK

• 데이터베이스 스키마
  • 데이터베이스에서 자료의 구조, 자료의 표현 방법, 자료 간의 관계를 '형식 언어로 정의한 구조'
    • 사용자가 계정을 받아서 여러가지 방법으로 데이터(자료)를 저장하고 관리하는 공간
    • 오라클 등 DBMS는 스키마에 여러 객체를 생성해서 사용