Skip to content

20_06_01_PLSQL.md

Latest commit

 

History

History
120 lines (87 loc) · 3.13 KB

20_06_01_PLSQL.md

File metadata and controls

120 lines (87 loc) · 3.13 KB

PL/SQL

BLOCK

NESTED_BLOCK_EXCEPT

PDF 3


BEGIN 
    INSERT INTO DEPT VALUES(66, 'OUTER_BLK_PART', 'Main_Blk');
    
    <<Nested_BLOCK_1>>
    BEGIN
            INSERT INTO DEPT VALUES(76, 'LOCAL_PART_1', 'NESTED_Blk1');        
            INSERT INTO DEPT VALUES(777, 'LOCAL_PART_1', 'NESTED_Blk1'); -- 런타임 에러 발생
            INSERT INTO DEPT VALUES(77, 'LOCAL_PART_1', 'NESTED_Blk1');
            INSERT INTO DEPT VALUES(78, 'LOCAL_PART_1', 'NESTED_Blk1');
            COMMIT;
    EXCEPTION
            WHEN OTHERS THEN
                NULL;
    END Nested_BLOCK_1;
    
    <<Nested_BLOCK_2>>
    BEGIN
            INSERT INTO DEPT VALUES(88, 'LOCAL_PART_1', 'NESTED_Blk1');
            COMMIT;
    END Nested_BLOCK2;
    
    INSERT INTO DEPT VALUES(99, 'OUTER_BLK_PART', 'Main_Blk');
END;
/
66,76,88,99 들어감


BEGIN 
    INSERT INTO DEPT VALUES(66, 'OUTER_BLK_PART', 'Main_Blk');
    
    <<Nested_BLOCK_1>>
    BEGIN
            INSERT INTO DEPT VALUES(76, 'LOCAL_PART_1', 'NESTED_Blk1');        
            INSERT INTO DEPT VALUES(777, 'LOCAL_PART_1', 'NESTED_Blk1'); -- 런타임 에러 발생
            INSERT INTO DEPT VALUES(77, 'LOCAL_PART_1', 'NESTED_Blk1');
            INSERT INTO DEPT VALUES(78, 'LOCAL_PART_1', 'NESTED_Blk1');
            COMMIT;
    EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
    END Nested_BLOCK_1;
    
    <<Nested_BLOCK_2>>
    BEGIN
            INSERT INTO DEPT VALUES(88, 'LOCAL_PART_1', 'NESTED_Blk1');
            COMMIT;
    END Nested_BLOCK2;
    
    INSERT INTO DEPT VALUES(99, 'OUTER_BLK_PART', 'Main_Blk');
END;
/
예외 처리 못해서 밖으로 던지다가 그래도 처리 못하면 ROLLBACK 그래서 아무것도 안들어감 


BEGIN 
    INSERT INTO DEPT VALUES(66, 'OUTER_BLK_PART', 'Main_Blk');
    
    <<Nested_BLOCK_1>>
    BEGIN
            INSERT INTO DEPT VALUES(76, 'LOCAL_PART_1', 'NESTED_Blk1');        
            INSERT INTO DEPT VALUES(777, 'LOCAL_PART_1', 'NESTED_Blk1'); -- 런타임 에러 발생
            INSERT INTO DEPT VALUES(77, 'LOCAL_PART_1', 'NESTED_Blk1');
            INSERT INTO DEPT VALUES(78, 'LOCAL_PART_1', 'NESTED_Blk1');
            COMMIT;
    EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
	WHEN OTHERS THEN
	    NULL;
    END Nested_BLOCK_1;
    
    <<Nested_BLOCK_2>>
    BEGIN
            INSERT INTO DEPT VALUES(88, 'LOCAL_PART_1', 'NESTED_Blk1');
            COMMIT;
    END Nested_BLOCK2;
    
    INSERT INTO DEPT VALUES(99, 'OUTER_BLK_PART', 'Main_Blk');
END;
/

66,76,88,99 들어감

스칼라 서브 쿼리

단하나의 값

컬럼자리에 오는 SELECT문

하나의 쿼리가 실행되는 시간이 늘어남!!!! -> 방지 HASH_TABLE(HASH 깊은 이해 필요) / ORACLE DBMS안에서 QUERY TRANSFORMAITION -> SCALAR SUBQUERY를 JOIN으로 바꾼다 임의로 암시적인 형변환을 던지면 DBMS가 쿼리를 바꿔벌임 WHERE EMPNO = '7369' -> DBMS가 옵티마이져로 자동적 형변환

SELECT DEPTNO, ENAME, JOB, SAL,(SELECT ROUND(AVG(SAL),1) FROM EMP SE WHERE SE.JOB = M.JOB) AS JOB_AVG_SAL

FROM EMP M

ORDER BY DEPTNO,JOB,JOB_AVG_SAL ASC;