21.09.03

보통 테이블에서 데이터가 많은경우 내부적으로 ,명시로 정렬을 해서 구현 -> 인덱스

   - 트랜잭션 시작 : 첫번째 DML구문이 실행될 때 시작됨
   - 트랜잭션 종료 : 1) COMMIT(DML저장)/ROLLBACK(DML실행취소) 명령이 실행될 때 종료
                   2) DDL구문이 실행될 때 종료 -> AUTO COMMIT
                   3) SQL*PLUS OR DBSERVER가 비정상적으로 종료될때 -> AUTO ROLLBACK
                   
     		SQL> INSERT INTO;   ---> START
     		SQL> UPDATE..;
		SQL> COMMIT;  ---> END / COMMIT
 		---------------------------> 첫번째 트랜잭션
		SQL> UPDATE ~~;    ---> START
 		SQL> DELETE ~~~;
		SQL> CREATE OR REPLACE VIEW~~;  --> END / + DDL구문에 의해서 Auto Commit
		---------------------------> 두번째 트랜잭션              

COMMIT(저장) : 변경된 데이터(INSERT, DELETE, UPDATE)를 저장하고 트랜잭션을 종료하는 명령
ROLLBACK(취소) : 변경 작업(INSERT, DELETE, UPDATE)을 취소하고 트랜잭션을 종료하는 명령
	        기본적으로 데이터 상태를 트랜잭션의 시작 시점으로 되돌린다.
		SAVEPOINT savepoint_name : 트랜잭션의 특정 시점을 기록하는 명령
		ROLLBACK TO savepoint_name : 지정한 특정 시점으로 데이터 상태를 되돌릴 수 있음

1) 다른 사용자는 현재 작업중인 DML의 결과를 볼 수 없다.
2) 작업 중인 행에 대해 다른 변경 작업을 할 수 없다.

		COMMIT : 변경 내용이 데이터 베이스 (파일)에 저장 및 반영
			 모든 사용자는 변경된 동일한 결과를 볼 수 있다.
			 동일한 행에 대해 다른 변경 작업 가능
			 지금까지 설정된 모든 SAVEPOINT는 사라짐

		ROLLBACK : 데이터 이전 상태로 복구
			   동일한 행에 대해 다른 변경 작업 가능
			   지금까지 설정한 모든 SAVEPOINT는 사라짐

Q1) 트랜잭션과 데이터 상태를 확인해보자.

	ALTER TABLE EMPLOYEE
	DISABLE CONSTRAINTS FK_MGRID;

	SAVEPOINT S0;
	
	INSERT INTO DEPARTMENT VALUES('40','기획전략팀','A1');

	SAVEPOINT S1;
	
   	UPDATE EMPLOYEE SET DEPT_ID='40'
   	WHERE DEPT_ID IS NULL;

	SAVEPOINT S2;

	DELETE FROM EMPLOYEE;

----> ROLLBACK
   
	ROLLBACK TO S2;

	SELECT COUNT(*) FROM EMPLOYEE; 
	SELECT COUNT(*) FROM EMPLOYEE
	WHERE DEPT_ID = '40';

	ROLLBACK TO S1;
	SELECT COUNT(*) FROM DEPARTMENT
	WHERE DEPT_ID = '40';

	40번 테이블도 확인?

	ROLLBACK TO S0;
	
                       

 * 컬럼 삭제
    ALTER TABLE DEPARTMENT
    DROP COLUMN MGR_ID02;

Q2) 동시성 LOCK : 다수의 사용자가 동시에 동일한 데이터에 접근해서 변경 시도 가능
	          무결성을 보장하기 위해 동시성을 제어하는 것이 필요함
                
       데이터 동시성 제어 기법에 대한 특징
	1) 서로 다른 사용자가 (서로 다른 트랜잭션)가 동시에 동일한 행을 변경 할 수 없도록 방지
	2) 다른 사용자 (다른 트랜잭션)가 COMMIT 되지 않은 변경 내용을 Over Write 할 수 없도록 방지
 	3) 트랜잭션이 실행되는 동안 자동으로 수행/유지/관리 됨

	세션1
	SELECT EMP_NAME, MARRIAGE
	FROM EMPLOYEE
	WHERE EMP_ID = '143';

	UPDATE EMPLOYEE
	SET MARRIAGE = 'N'
	WHERE EMP_ID = '143';

	세션2 
	SELECT EMP_NAME, MARRIAGE 
	FROM EMPLOYEE
	WHERE EMP_ID = '143';

	UPDATE EMPLOYEE
	SET MARRIAGE = 'N'
	WHERE EMP_ID = '143';
	---------------------------> 세션1에서 사용중이라 세션2에서 잠금상태 (LOCK)
                                     세션1 종료(ROLLBACK..)하면 세션2 UPDATE 실행됨

 set SERVEROUTPUT ON -> 해야 화면에 출력됨
  DECLARE 
        i int : 10;
        j int : 20;
        hap int: 0 ; -- 선언부
   BEGIN
        hap :=i+j;
        DBMS_OUTPUT.put_line(i || '+' || j || '=' || hap); -- 실행문
   END;

 IF 조건 THEN 명령
  ELSIF   THEN
  ELSE
  END IF;

Q3) 점수에 대한 학점을 A~F로 IF문으로 구현 해보자.
	
	DECLARE
	     score01 int := 85;
	     grade01 varchar2(2);
	BEGIN
	     IF score01 >= 90 THEN grade01 := 'A';
	     ELSIF score01 >= 80 THEN grade01 := 'B';
	     ELSIF score01 >= 70 THEN grade01 := 'C';
	     ELSIF score01 >= 60 THEN grade01 := 'D';
	     ELSE grade01 := 'F';
	     END IF;
	     DBMS_OUTPUT.PUT_LINE('SCORE ' || score01 || ',GRADE = ' || grade01);
	END;

Q4) '홍길동1' 3번 출력하자
	
	DECLARE
	     NAME VARCHAR2(10) := '홍길동';
             I INT := 0;
	BEGIN
	     FOR I IN 1..3 LOOP
	     DBMS_OUTPUT.PUT_LINE(NAME||I);
	     END LOOP;
	END;

Q5) 테이블을 생성 해서 반복적인 쿼리, 자료를 넣어보자.
	
	CREATE TABLE TEST02(
	NO NUMBER(3),
	IRUM VARCHAR2(10));

	DECLARE
	
	BEGIN
	     FOR I IN 1..3 LOOP
	     INSERT INTO TEST02 VALUES(I,SYSDATE);
	     END LOOP;
	END;

Q6) 구구단 싫지만 해보자 홀수 단만 출력 해보자
	
	DECLARE
	     TOTAL NUMBER := 0;
	BEGIN
	     FOR I IN 2..9 LOOP
	     	IF MOD(I,2) != 0  THEN DBMS_OUTPUT.PUT_LINE (I|| '단');
		
		FOR J IN 2..9 LOOP
		TOTAL := I*J;
		DBMS_OUTPUT.PUT_LINE (I || '*' || J ||'=' || TOTAL);
                END LOOP;

	   	END IF;
	     END LOOP;
	END;

Q7) EMP테이블에서 부서번호 10번인 사원의 평균 급여를 출력하는 익명의 블록을 작성하자
     ex) 000부서의 평균 급여는 00원입니다.
	
	SELECT AVG(SAL)
	FROM EMP
	WHERE DEPTNO = 10
	GROUP BY DEPTNO;

	DECLARE
		MY_AVG NUMBER(7,2) := 0;
		MY_DEPTNO NUMBER(7) := 10; -- MY_DEPTNO   EMP.DEPTNO%TYPE := 10;
	BEGIN	
		SELECT AVG(SAL) INTO MY_AVG
		FROM EMP
		WHERE DEPTNO = MY_DEPTNO
		GROUP BY DEPTNO;
		DBMS_OUTPUT.PUT_LINE (MY_DEPTNO || '부서의 평균 급여는 ['|| MY_AVG || ']원입니다');
	END;

Q8) EMP테이블에서 20번 부서의 개수, 평균 급여를 구하라
    ex) 000부서의 평균 급여는 00이고, 사원의 수는 00 명입니다.

	DECLARE
		MY_AVG NUMBER(7,2) :=0;
		MY_DEPTNO NUMBER(7) := 20;
		MY_COUNT NUMBER (7,2) := 0;
	BEGIN
		SELECT AVG(SAL),COUNT(*)INTO MY_AVG,MY_COUNT
		FROM EMP
		WHERE DEPTNO = MY_DEPTNO 
		GROUP BY DEPTNO;
		DBMS_OUTPUT.PUT_LINE (MY_DEPTNO || '부서의 평균 급여는 ['|| MY_AVG || ']이고, 사원의 수는 '|| MY_COUNT || '명입니다.');
	END;

Q9) ACCEPT : 사용자의 입력 값을 읽어서 변수에 저장하는 키워드
	
	ex) ACCEPT inputVal prompt '사번이름 : '
  
    사번에 해당하는 사원의 이름을 출력해보자
	ACCEPT inputVal prompt '사번입력 : '
	
	DECLARE
	    NAME1 EMP.ENAME%TYPE;
	BEGIN 
	    SELECT ENAME INTO NAME1 FROM EMP
	    WHERE EMPNO = &inputVal;
	    DBMS_OUTPUT.PUT_LINE ('현재의 결과 : ' || NAME1);
	END;

Q10) 사번을 입력받아 봉급을 100 증가시키자

	CREATE OR REPLACE PROCEDURE UPDATE_SAL (V_empno IN NUMBER) // ()안은 외부로부터 입력받을 값
	AS 
	BEGIN
		UPDATE EMP
		SET SAL = SAL + 100
		WHERE EMPNO = V_empno;
		COMMIT;
	END UPDATE_SAL;

	EXEC UPDATE_SAL(7369);

Q11) 사원의 번호를 입력받아 사원의 번호, 사원의 이름, 사원의 월급을 출력하자.

    CREATE OR REPLACE PROCEDURE EXAM01(RES_EMPNO IN EMP.EMPNO%TYPE)
    AS 
        V_EMPNO EMP.EMPNO%TYPE;
        V_ENAME EMP.ENAME%TYPE;
        V_SAL EMP.SAL%TYPE;
        
    BEGIN
        DBMS_OUTPUT.ENABLE;
        SELECT EMPNO,ENAME,SAL INTO V_EMPNO,V_ENAME,V_SAL
        FROM EMP
        WHERE EMPNO = RES_EMPNO;
        
        DBMS_OUTPUT.PUT_LINE('사원의 번호 : ' || V_EMPNO);
        DBMS_OUTPUT.PUT_LINE('사원의 이름 : ' || V_ENAME);
        DBMS_OUTPUT.PUT_LINE('사원의 봉급 : ' || V_SAL);
	DBMS_OUTPUT.PUT_LINE('사원의 연봉 : ' || GET_MYAVG(RES_EMPNO));
    END EXAM01;

Q12) 함수를 만들어서 연동해보자
	
	CREATE FUNCTION GET_MYAVG(V_ID IN EMP.EMPNO%TYPE)
	RETURN NUMBER
	AS  // AS를 써도 되고 IS를 써도 됨 / 오라클 툴 기본은 AS
		V_SAL EMP.SAL%TYPE := 0;	
		V_TOT NUMBER := 0;
		V_COMM EMP.COMM%TYPE;

	BEGIN
		SELECT SAL, COMM INTO V_SAL,V_COMM
		FROM EMP
		WHERE EMPNO = V_ID;
		V_TOT := V_SAL *12 + NVL(V_COMM,0);
		RETURN V_TOT;
	END;
	/

 - 프로시저 안에 함수 호출
	CREATE OR REPLACE PROCEDURE EXAM02 AS 
	BEGIN
  	DBMS_OUTPUT.PUT_LINE(GET_MYAVG(7902));	
	END EXAM02;