꿈꾸는 개발자, DBA 커뮤니티 구루비

오라클 데이터베이스 12c PL/SQL의 새로운 기능

로우 패턴매칭

데이터의 특정 패턴이 존재할 경우, 이를 검색해 줌
일/월/분기별 등 특정 기간 동안의 데이터 추이에서 특정 패턴 검색에 유리함(예 : 주식시황, 기간별 매출추이 등)

참고URL : https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1


DROP TABLE sales_history PURGE;


CREATE TABLE sales_history (
  id            NUMBER,
  product       VARCHAR2(20),
  tstamp        TIMESTAMP,
  units_sold    NUMBER,
  CONSTRAINT sales_history_pk PRIMARY KEY (id)
);

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

INSERT INTO sales_history VALUES ( 1, 'TWINKIES', '01-OCT-2014', 17);
INSERT INTO sales_history VALUES ( 2, 'TWINKIES', '02-OCT-2014', 19);
INSERT INTO sales_history VALUES ( 3, 'TWINKIES', '03-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 4, 'TWINKIES', '04-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 5, 'TWINKIES', '05-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 6, 'TWINKIES', '06-OCT-2014', 10);
INSERT INTO sales_history VALUES ( 7, 'TWINKIES', '07-OCT-2014', 14);
INSERT INTO sales_history VALUES ( 8, 'TWINKIES', '08-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 9, 'TWINKIES', '09-OCT-2014', 15);
INSERT INTO sales_history VALUES (10, 'TWINKIES', '10-OCT-2014', 17);
INSERT INTO sales_history VALUES (11, 'TWINKIES', '11-OCT-2014', 23);
INSERT INTO sales_history VALUES (12, 'TWINKIES', '12-OCT-2014', 30);
INSERT INTO sales_history VALUES (13, 'TWINKIES', '13-OCT-2014', 31);
INSERT INTO sales_history VALUES (14, 'TWINKIES', '14-OCT-2014', 29);
INSERT INTO sales_history VALUES (15, 'TWINKIES', '15-OCT-2014', 25);
INSERT INTO sales_history VALUES (16, 'TWINKIES', '16-OCT-2014', 21);
INSERT INTO sales_history VALUES (17, 'TWINKIES', '17-OCT-2014', 35);
INSERT INTO sales_history VALUES (18, 'TWINKIES', '18-OCT-2014', 46);
INSERT INTO sales_history VALUES (19, 'TWINKIES', '19-OCT-2014', 45);
INSERT INTO sales_history VALUES (20, 'TWINKIES', '20-OCT-2014', 30);
COMMIT;

-- 한국인 입장으로 다시 표기
ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD';


SET PAGESIZE 50
COLUMN product    FORMAT A10
COLUMN tstamp     FORMAT A11
COLUMN graph      FORMAT A50

-- 데이터를 출력한다.
SELECT id,
       product,
       tstamp,
       units_sold,
       RPAD('#', units_sold, '#') AS graph
FROM   sales_history
ORDER BY id;


        ID PRODUCT    TSTAMP      UNITS_SOLD GRAPH
---------- ---------- ----------- ---------- --------------------------------------------------
         1 TWINKIES   2014-10-01          17 #################
         2 TWINKIES   2014-10-02          19 ###################
         3 TWINKIES   2014-10-03          23 #######################
         4 TWINKIES   2014-10-04          23 #######################
         5 TWINKIES   2014-10-05          16 ################
         6 TWINKIES   2014-10-06          10 ##########
         7 TWINKIES   2014-10-07          14 ##############
         8 TWINKIES   2014-10-08          16 ################
         9 TWINKIES   2014-10-09          15 ###############
        10 TWINKIES   2014-10-10          17 #################
        11 TWINKIES   2014-10-11          23 #######################
        12 TWINKIES   2014-10-12          30 ##############################
        13 TWINKIES   2014-10-13          31 ###############################
        14 TWINKIES   2014-10-14          29 #############################
        15 TWINKIES   2014-10-15          25 #########################
        16 TWINKIES   2014-10-16          21 #####################
        17 TWINKIES   2014-10-17          35 ###################################
        18 TWINKIES   2014-10-18          46 ##############################################
        19 TWINKIES   2014-10-19          45 #############################################
        20 TWINKIES   2014-10-20          30 ##############################

20 rows selected.


COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product                            -- 데이터의 그룹화 과정, 여기서는 product 별로 패턴을 찾기로 함
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,          -- 패턴매칭으로 가져올 컬럼명을 새로 지정함
                   LAST(UP.tstamp) AS peak_tstamp,
                   LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno                 -- 패턴을 만족한 개수
         ONE ROW PER MATCH                               -- 패턴을 만족하는 ROW 1개만 보여줌(중복제거 효과),  ALL ROWS : 패턴을 만족하는 과정의 모든 ROW를 보여줌
         AFTER MATCH SKIP TO LAST DOWN                   -- 현재 패턴을 만족 하는 마지막 DOWN 시작점부터 다시 패턴을 찾기 시작함
         PATTERN (STRT UP+ FLAT* DOWN+)                  -- 패턴을 정의 : UP이 1개 이상, FLAT은 0개 이상, DOWN은 1개 이상인 경우
         DEFINE                                          -- 패턴의 변수를 정의 : STRT의 경우 하단에 정의하지 않았으므로 모든 row에 매칭된다.
           UP AS UP.units_sold > PREV(UP.units_sold),       -- UP   : 이전 units_sold 보다 큰 경우
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold), -- FLAT : 이전 units_sold와 같은 경우
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)  -- DOWN : 이전 units_sold와 작은 경우
       ) MR
ORDER BY MR.product, MR.start_tstamp;

PRODUCT    START_TSTAM PEAK_TSTAMP END_TSTAMP         MNO
---------- ----------- ----------- ----------- ----------
TWINKIES   01-OCT-2014 03-OCT-2014 06-OCT-2014          1
TWINKIES   06-OCT-2014 08-OCT-2014 09-OCT-2014          2
TWINKIES   09-OCT-2014 13-OCT-2014 16-OCT-2014          3
TWINKIES   16-OCT-2014 18-OCT-2014 20-OCT-2014          4

4 rows selected.


-- ONE ROW PER MATCH를 ALL ROWS PER MATCH로 변경
COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN final_peak_tstamp  FORMAT A11
COLUMN final_end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   LAST(UP.tstamp) AS peak_tstamp,
                   LAST(DOWN.tstamp) AS end_tstamp,
                   FINAL LAST(UP.tstamp) AS final_peak_tstamp,   -- Final : 가장 마지막 패턴 매칭값이 나옴
                   FINAL LAST(DOWN.tstamp) AS final_end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls         -- 분류자(패턴 정의시 사용한 변수)
         ALL ROWS PER MATCH                    -- 패턴을 만족하는 모든 ROW가 나타남
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

PRODUCT    TSTAMP               START_TSTAM PEAK_TSTAMP END_TSTAMP  FINAL_PEAK_ FINAL_END_T        MNO CLS           ID UNITS_SOLD
---------- -------------------- ----------- ----------- ----------- ----------- ----------- ---------- ----- ---------- ----------
TWINKIES   2014-10-01           2014-10-01                          2014-10-03  2014-10-06           1 STRT           1         17
TWINKIES   2014-10-02           2014-10-01  2014-10-02              2014-10-03  2014-10-06           1 UP             2         19
TWINKIES   2014-10-03           2014-10-01  2014-10-03              2014-10-03  2014-10-06           1 UP             3         23
TWINKIES   2014-10-04           2014-10-01  2014-10-03              2014-10-03  2014-10-06           1 FLAT           4         23
TWINKIES   2014-10-05           2014-10-01  2014-10-03  2014-10-05  2014-10-03  2014-10-06           1 DOWN           5         16
TWINKIES   2014-10-06           2014-10-01  2014-10-03  2014-10-06  2014-10-03  2014-10-06           1 DOWN           6         10
TWINKIES   2014-10-07           2014-10-07                          2014-10-08  2014-10-09           2 STRT           7         14
TWINKIES   2014-10-08           2014-10-07  2014-10-08              2014-10-08  2014-10-09           2 UP             8         16
TWINKIES   2014-10-09           2014-10-07  2014-10-08  2014-10-09  2014-10-08  2014-10-09           2 DOWN           9         15
TWINKIES   2014-10-10           2014-10-10                          2014-10-13  2014-10-16           3 STRT          10         17
TWINKIES   2014-10-11           2014-10-10  2014-10-11              2014-10-13  2014-10-16           3 UP            11         23
TWINKIES   2014-10-12           2014-10-10  2014-10-12              2014-10-13  2014-10-16           3 UP            12         30
TWINKIES   2014-10-13           2014-10-10  2014-10-13              2014-10-13  2014-10-16           3 UP            13         31
TWINKIES   2014-10-14           2014-10-10  2014-10-13  2014-10-14  2014-10-13  2014-10-16           3 DOWN          14         29
TWINKIES   2014-10-15           2014-10-10  2014-10-13  2014-10-15  2014-10-13  2014-10-16           3 DOWN          15         25
TWINKIES   2014-10-16           2014-10-10  2014-10-13  2014-10-16  2014-10-13  2014-10-16           3 DOWN          16         21
TWINKIES   2014-10-17           2014-10-17                          2014-10-18  2014-10-20           4 STRT          17         35
TWINKIES   2014-10-18           2014-10-17  2014-10-18              2014-10-18  2014-10-20           4 UP            18         46
TWINKIES   2014-10-19           2014-10-17  2014-10-18  2014-10-19  2014-10-18  2014-10-20           4 DOWN          19         45
TWINKIES   2014-10-20           2014-10-17  2014-10-18  2014-10-20  2014-10-18  2014-10-20           4 DOWN          20         30



SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ DOWN{1} UP+)   -- 패턴 : UP 1회 이상, DOWN 1회, UP 1회 이상
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
       ) MR
ORDER BY MR.product, MR.tstamp;



PRODUCT    TSTAMP               START_TSTAM PEAK_TSTAMP        MNO CLS           ID UNITS_SOLD
---------- -------------------- ----------- ----------- ---------- ----- ---------- ----------
TWINKIES   2014-10-06           2014-10-06  2014-10-13           1 STRT           6         10
TWINKIES   2014-10-07           2014-10-06  2014-10-13           1 UP             7         14
TWINKIES   2014-10-08           2014-10-06  2014-10-13           1 UP             8         16
TWINKIES   2014-10-09           2014-10-06  2014-10-13           1 DOWN           9         15
TWINKIES   2014-10-10           2014-10-06  2014-10-13           1 UP            10         17
TWINKIES   2014-10-11           2014-10-06  2014-10-13           1 UP            11         23
TWINKIES   2014-10-12           2014-10-06  2014-10-13           1 UP            12         30
TWINKIES   2014-10-13           2014-10-06  2014-10-13           1 UP            13         31

8 rows selected.



-- 패턴 매칭을 시작점을 정의
-- AFTER MATCH SKIP TO NEXT ROW  : 패턴을 만족시키는 ROW 바로 뒤 ROW부터 다시 패턴매칭을 시작함
-- AFTER MATCH SKIP PAST LAST ROW : 현재 패턴을 만족시키는 마지막 ROW 부터 패턴매칭을 다시 시작함
-- AFTER MATCH SKIP TO FIRST pattern_variable : 첫번째 pattern_variable 부터 다시 패턴매칭을 시작함
-- AFTER MATCH SKIP TO LAST pattern_variable : 마지막 pattern_variable 부터 다시 패턴매칭을 시작함
-- AFTER MATCH SKIP TO pattern_variable  : AFTER MATCH SKIP TO LAST pattern_variable 와 동일 기능


SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   LAST(UP.tstamp) AS peak_tstamp,
                   LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno
         ONE ROW PER MATCH
         AFTER MATCH SKIP TO NEXT ROW             -- 패턴을 만족시키는 ROW 바로 뒤 ROW부터 다시 패턴매칭을 시작함
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
       ) MR
ORDER BY MR.product, MR.start_tstamp;

PRODUCT    START_TSTAM PEAK_TSTAMP END_TSTAMP         MNO
---------- ----------- ----------- ----------- ----------
TWINKIES   2014-10-01  2014-10-03  2014-10-06           1
TWINKIES   2014-10-02  2014-10-03  2014-10-06           2
TWINKIES   2014-10-06  2014-10-08  2014-10-09           3
TWINKIES   2014-10-07  2014-10-08  2014-10-09           4
TWINKIES   2014-10-09  2014-10-13  2014-10-16           5
TWINKIES   2014-10-10  2014-10-13  2014-10-16           6
TWINKIES   2014-10-11  2014-10-13  2014-10-16           7
TWINKIES   2014-10-12  2014-10-13  2014-10-16           8
TWINKIES   2014-10-16  2014-10-18  2014-10-20           9
TWINKIES   2014-10-17  2014-10-18  2014-10-20          10

10 rows selected.


-- 새로운 형태의 데이터 생성
CREATE TABLE sales_audit (
  id            NUMBER,
  product       VARCHAR2(20),
  tstamp        TIMESTAMP,
  CONSTRAINT sales_audit_pk PRIMARY KEY (id)
);

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';

INSERT INTO sales_audit VALUES ( 1, 'TWINKIES', '01-OCT-2014 12:00:01');
INSERT INTO sales_audit VALUES ( 2, 'TWINKIES', '01-OCT-2014 12:00:02');
INSERT INTO sales_audit VALUES ( 3, 'DINGDONGS', '01-OCT-2014 12:00:03');
INSERT INTO sales_audit VALUES ( 4, 'HOHOS', '01-OCT-2014 12:00:04');
INSERT INTO sales_audit VALUES ( 5, 'HOHOS', '01-OCT-2014 12:00:05');
INSERT INTO sales_audit VALUES ( 6, 'TWINKIES', '01-OCT-2014 12:00:06');
INSERT INTO sales_audit VALUES ( 7, 'TWINKIES', '01-OCT-2014 12:00:07');
INSERT INTO sales_audit VALUES ( 8, 'DINGDONGS', '01-OCT-2014 12:00:08');
INSERT INTO sales_audit VALUES ( 9, 'DINGDONGS', '01-OCT-2014 12:00:09');
INSERT INTO sales_audit VALUES (10, 'HOHOS', '01-OCT-2014 12:00:10');
INSERT INTO sales_audit VALUES (11, 'HOHOS', '01-OCT-2014 12:00:11');
INSERT INTO sales_audit VALUES (12, 'TWINKIES', '01-OCT-2014 12:00:12');
INSERT INTO sales_audit VALUES (13, 'TWINKIES', '01-OCT-2014 12:00:13');
INSERT INTO sales_audit VALUES (14, 'DINGDONGS', '01-OCT-2014 12:00:14');
INSERT INTO sales_audit VALUES (15, 'DINGDONGS', '01-OCT-2014 12:00:15');
INSERT INTO sales_audit VALUES (16, 'HOHOS', '01-OCT-2014 12:00:16');
INSERT INTO sales_audit VALUES (17, 'TWINKIES', '01-OCT-2014 12:00:17');
INSERT INTO sales_audit VALUES (18, 'TWINKIES', '01-OCT-2014 12:00:18');
INSERT INTO sales_audit VALUES (19, 'TWINKIES', '01-OCT-2014 12:00:19');
INSERT INTO sales_audit VALUES (20, 'TWINKIES', '01-OCT-2014 12:00:20');
COMMIT;


COLUMN tstamp       FORMAT A20
COLUMN start_tstamp FORMAT A20
COLUMN end_tstamp   FORMAT A20
COLUMN cls          FORMAT A10

SELECT *
FROM   sales_audit MATCH_RECOGNIZE (
         --PARTITION BY product   -- 생략 가능함
         ORDER BY tstamp          -- 생략 가능함
         MEASURES  FIRST(TWINKIES.tstamp) AS start_tstamp,
                   FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST TWINKIES
         PATTERN(TWINKIES{2,} DINGDONGS{2} HOHOS{1} TWINKIES{3,})    -- TWINKIES가 2번 이상, DINGDONGS가 2번, HOHOS가 1번,  TWINKIES가 3번 이상
         DEFINE
           TWINKIES AS TWINKIES.product='TWINKIES',
           DINGDONGS AS DINGDONGS.product='DINGDONGS',
           HOHOS AS HOHOS.product='HOHOS'
       ) MR
ORDER BY MR.mno, MR.tstamp;


TSTAMP               START_TSTAMP         END_TSTAMP                  MNO CLS                ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
2014-10-01 12:00:12  2014-10-01 12:00:12  2014-10-01 12:00:20           1 TWINKIES           12 TWINKIES
2014-10-01 12:00:13  2014-10-01 12:00:12  2014-10-01 12:00:20           1 TWINKIES           13 TWINKIES
2014-10-01 12:00:14  2014-10-01 12:00:12  2014-10-01 12:00:20           1 DINGDONGS          14 DINGDONGS
2014-10-01 12:00:15  2014-10-01 12:00:12  2014-10-01 12:00:20           1 DINGDONGS          15 DINGDONGS
2014-10-01 12:00:16  2014-10-01 12:00:12  2014-10-01 12:00:20           1 HOHOS              16 HOHOS
2014-10-01 12:00:17  2014-10-01 12:00:12  2014-10-01 12:00:20           1 TWINKIES           17 TWINKIES
2014-10-01 12:00:18  2014-10-01 12:00:12  2014-10-01 12:00:20           1 TWINKIES           18 TWINKIES
2014-10-01 12:00:19  2014-10-01 12:00:12  2014-10-01 12:00:20           1 TWINKIES           19 TWINKIES
2014-10-01 12:00:20  2014-10-01 12:00:12  2014-10-01 12:00:20           1 TWINKIES           20 TWINKIES



SELECT *
FROM   sales_audit MATCH_RECOGNIZE (
         --PARTITION BY product
         ORDER BY tstamp
         MEASURES  FIRST(TWINKIES.tstamp) AS start_tstamp,
                   FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST TWINKIES
         PATTERN(TWINKIES+ (DINGDONGS | HOHOS){3} TWINKIES+)  -- TWINKIES 1번 이상, DINGDONGS 또는 HOHOS가 3회, TWINKIES 가 1회 이상
         DEFINE
           TWINKIES AS TWINKIES.product='TWINKIES',
           DINGDONGS AS DINGDONGS.product='DINGDONGS',
           HOHOS AS HOHOS.product='HOHOS'
       ) MR
ORDER BY MR.mno, MR.tstamp;


TSTAMP               START_TSTAMP         END_TSTAMP                  MNO CLS                ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
2014-10-01 12:00:01  2014-10-01 12:00:01  2014-10-01 12:00:07           1 TWINKIES            1 TWINKIES
2014-10-01 12:00:02  2014-10-01 12:00:01  2014-10-01 12:00:07           1 TWINKIES            2 TWINKIES
2014-10-01 12:00:03  2014-10-01 12:00:01  2014-10-01 12:00:07           1 DINGDONGS           3 DINGDONGS
2014-10-01 12:00:04  2014-10-01 12:00:01  2014-10-01 12:00:07           1 HOHOS               4 HOHOS
2014-10-01 12:00:05  2014-10-01 12:00:01  2014-10-01 12:00:07           1 HOHOS               5 HOHOS
2014-10-01 12:00:06  2014-10-01 12:00:01  2014-10-01 12:00:07           1 TWINKIES            6 TWINKIES
2014-10-01 12:00:07  2014-10-01 12:00:01  2014-10-01 12:00:07           1 TWINKIES            7 TWINKIES
2014-10-01 12:00:12  2014-10-01 12:00:12  2014-10-01 12:00:20           2 TWINKIES           12 TWINKIES
2014-10-01 12:00:13  2014-10-01 12:00:12  2014-10-01 12:00:20           2 TWINKIES           13 TWINKIES
2014-10-01 12:00:14  2014-10-01 12:00:12  2014-10-01 12:00:20           2 DINGDONGS          14 DINGDONGS
2014-10-01 12:00:15  2014-10-01 12:00:12  2014-10-01 12:00:20           2 DINGDONGS          15 DINGDONGS
2014-10-01 12:00:16  2014-10-01 12:00:12  2014-10-01 12:00:20           2 HOHOS              16 HOHOS
2014-10-01 12:00:17  2014-10-01 12:00:12  2014-10-01 12:00:20           2 TWINKIES           17 TWINKIES
2014-10-01 12:00:18  2014-10-01 12:00:12  2014-10-01 12:00:20           2 TWINKIES           18 TWINKIES
2014-10-01 12:00:19  2014-10-01 12:00:12  2014-10-01 12:00:20           2 TWINKIES           19 TWINKIES
2014-10-01 12:00:20  2014-10-01 12:00:12  2014-10-01 12:00:20           2 TWINKIES           20 TWINKIES

16 rows selected.

Oracle Native Left Outer Join 문법개선

12c 이전에는 한 번에 한 테이블에만 outer 조인이 가능했으나 12c부터는 2개 이상의 복수개의 테이블로 outer 조인이 가능하다.


-- 11g에서 실행할 경우 에러가 발생
SELECT COUNT(*)
  FROM SCOTT.EMP A, SCOTT.DEPT B, SCOTT.BONUS C
 WHERE B.DEPTNO = A.DEPTNO(+)
   AND C.ENAME = A.ENAME(+);

ERROR AT LINE 3:
ORA-01417: a tabe may be outer joined to at most one other table


-- 12c에서는 오류 없이 쿼리가 실행된다.
SELECT COUNT(*)
  FROM SCOTT.EMP A, SCOTT.DEPT B, SCOTT.BONUS C
 WHERE B.DEPTNO = A.DEPTNO(+)
   AND C.ENAME = A.ENAME(+);

  COUNT(*)
----------
         0


실행계획 : 
------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |     1 |    40 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE          |         |     1 |    40 |            |          |
|*  2 |   HASH JOIN OUTER        |         |     1 |    40 |     7   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN  |         |     1 |    20 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL    | BONUS   |     1 |     7 |     2   (0)| 00:00:01 |
|   5 |     BUFFER SORT          |         |     4 |    52 |     2   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| PK_DEPT |     4 |    52 |     2   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | EMP     |    14 |   280 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Top-n 쿼리의 새로운 기능 (row limiting)



select count(*) from emp;

  COUNT(*)
----------
        14

select empno, hiredate from emp order by hiredate asc;

     EMPNO HIREDATE
---------- -------------------
      7369 1980-12-17 00:00:00
      7499 1981-02-20 00:00:00
      7521 1981-02-22 00:00:00
      7566 1981-04-02 00:00:00
      7698 1981-05-01 00:00:00
      7782 1981-06-09 00:00:00
      7844 1981-09-08 00:00:00
      7654 1981-09-28 00:00:00
      7839 1981-11-17 00:00:00
      7900 1981-12-03 00:00:00
      7902 1981-12-03 00:00:00
      7934 1982-01-23 00:00:00
      7788 1987-04-19 00:00:00
      7876 1987-05-23 00:00:00

14 rows selected.



-- 8번째 row를 건너뛰고 조회됨
select empno, hiredate from emp order by hiredate offset 8 rows;

     EMPNO HIREDATE
---------- -------------------
      7839 1981-11-17 00:00:00
      7900 1981-12-03 00:00:00
      7902 1981-12-03 00:00:00
      7934 1982-01-23 00:00:00
      7788 1987-04-19 00:00:00
      7876 1987-05-23 00:00:00

6 rows selected.

-- 10개의 row를 가져옴
select empno, hiredate from emp
order by hiredate fetch first 10 rows only;

     EMPNO HIREDATE
---------- -------------------
      7369 1980-12-17 00:00:00
      7499 1981-02-20 00:00:00
      7521 1981-02-22 00:00:00
      7566 1981-04-02 00:00:00
      7698 1981-05-01 00:00:00
      7782 1981-06-09 00:00:00
      7844 1981-09-08 00:00:00
      7654 1981-09-28 00:00:00
      7839 1981-11-17 00:00:00
      7900 1981-12-03 00:00:00

10 rows selected.



-- 25%만큼의 데이터만 가져옴
select empno, hiredate from emp
order by hiredate fetch first 25 percent rows only;

     EMPNO HIREDATE
---------- -------------------
      7369 1980-12-17 00:00:00
      7499 1981-02-20 00:00:00
      7521 1981-02-22 00:00:00
      7566 1981-04-02 00:00:00


select empno, hiredate from emp
order by hiredate fetch next 25 percent rows only;  -- first / next는 서로 동일한 기능을 하였다.

     EMPNO HIREDATE
---------- -------------------
      7369 1980-12-17 00:00:00
      7499 1981-02-20 00:00:00
      7521 1981-02-22 00:00:00
      7566 1981-04-02 00:00:00

-- 4개의 row를 건너뛰고, 5번째 row부터 25% 가져옴
select empno, hiredate from emp
order by hiredate offset 4 rows fetch next 25 percent rows only;


     EMPNO HIREDATE
---------- -------------------
      7698 1981-05-01 00:00:00
      7782 1981-06-09 00:00:00
      7844 1981-09-08 00:00:00
      7654 1981-09-28 00:00:00


-- with ties절은 마지막 row가 서로 동률일 경우 동률인 row를 모두 가져옴
SELECT employee_id, last_name, salary
  FROM employees
  ORDER BY salary
  FETCH FIRST 5 PERCENT ROWS ONLY;


EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        132 Olson                           2100
        128 Markle                          2200
        136 Philtanker                      2200
        127 Landry                          2400
        135 Gee                             2400
        119 Colmenares                      2500

6 rows selected.


SELECT employee_id, last_name, salary
  FROM employees
 ORDER BY salary
 FETCH FIRST 5 PERCENT ROWS WITH TIES;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        132 Olson                           2100
        128 Markle                          2200
        136 Philtanker                      2200
        127 Landry                          2400
        135 Gee                             2400
        119 Colmenares                      2500
        131 Marlow                          2500
        140 Patel                           2500
        144 Vargas                          2500
        182 Sullivan                        2500
        191 Perkins                         2500

11 rows selected.

union, union all 분기문장 동시실행


create table big_table
as
select object_id, object_name
   from (select object_id, object_name from dba_objects where rownum <=1000) a
      , (select level from dual connect by level <= 1000) b
;

create index big_table_idx1 on big_table(object_id asc);
analyze table big_table compute statistics;

select count(*) cnt from big_table where object_id = 885;

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     3 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| BIG_TABLE_IDX1 |  1000 |  3000 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------

select count(*) cnt from big_table where object_id = 885
union all
select count(*) cnt from big_table where object_id = 237
union all
select count(*) cnt from big_table where object_id = 420
union all
select count(*) cnt from big_table where object_id = 557
union all
select count(*) cnt from big_table where object_id = 880
;

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     2 |     6 |    10   (0)| 00:00:01 |
|   1 |  UNION-ALL         |                |       |       |            |          |
|   2 |   SORT AGGREGATE   |                |     1 |     3 |            |          |
|*  3 |    INDEX RANGE SCAN| BIG_TABLE_IDX1 |  1000 |  3000 |     5   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE   |                |     1 |     3 |            |          |
|*  5 |    INDEX RANGE SCAN| BIG_TABLE_IDX1 |  1000 |  3000 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

오라클 데이터베이스 12c DDL의 새로운 기능

VARCHAR2, NVARCHAR2 RAW 데이터 타입의 최대 길이 증가

VARCHAR2, NVARCHAR2, RAW 데이터 타입이 32,767 BYTE까지 저장가능
확장 데이터 타입의 활성화


select name, value from v$parameter where name = 'max_string_size';

NAME             VALUE
---------------- ----------------
max_string_size  STANDARD


CREATE TABLE EXTEND_TABLE (DATA_VARCHAR VARCHAR2(4001), DATA_NVARCHAR2 NVARCHAR2(2001), DATA_RAW RAW(2001));
CREATE TABLE EXTEND_TABLE (DATA_VARCHAR VARCHAR2(4001), DATA_NVARCHAR2 NVARCHAR2(2001), DATA_RAW RAW(2001))
                                                 *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
@?/rdbms/admin/utl32k.sql    -- recyclebin에 테이블이 있을 경우 에러발생, recyclebin을 비우고 작업하자
SHUTDOWN IMMEDIATE;
STARTUP;


select name, value from v$parameter where name = 'max_string_size';

NAME             VALUE
---------------- ----------------
max_string_size  EXTENDED


CREATE TABLE EXTEND_TABLE (DATA_VARCHAR VARCHAR2(4001), DATA_NVARCHAR2 NVARCHAR2(2001), DATA_RAW RAW(2001));

Table created.

desc extend_table;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 DATA_VARCHAR                                                               VARCHAR2(4001)
 DATA_NVARCHAR2                                                             NVARCHAR2(2001)
 DATA_RAW                                                                   RAW(2001)



CREATE TABLE t1 (
  id             NUMBER,
  varchar2_data  VARCHAR2(32767),
  nvarchar2_data NVARCHAR2(16383),
  raw_data       RAW(32767)
);

Table created.

INSERT INTO t1
SELECT 1,
       RPAD('X', 32767, 'X') AS varchar2_data,
       RPAD('X', 16383, 'X') AS nvarchar2_data,
       UTL_RAW.cast_to_raw(RPAD('X', 32767, 'X')) as raw_data
FROM   dual;

SELECT id,
       LENGTH(varchar2_data),
       LENGTH(nvarchar2_data),
       LENGTH(raw_data)
FROM   t1;

        ID LENGTH(VARCHAR2_DATA) LENGTH(NVARCHAR2_DATA) LENGTH(RAW_DATA)
---------- --------------------- ---------------------- ----------------
         1                 32767                  16383            32767

1 row selected.

-- 인덱스가 LOB 타입으로 자동으로 생성됨
SELECT index_name, index_type
  FROM user_indexes
 WHERE table_name = 'T1';


INDEX_NAME                               INDEX_TYPE
---------------------------------------- ---------------------------
SYS_IL0000093481C00004$$                 LOB
SYS_IL0000093481C00003$$                 LOB
SYS_IL0000093481C00002$$                 LOB

블록사이즈가 8K인 경우 B*Tree 인덱스의 최대 키 길이는 6,398바이트이며, 이 길이를 초과하는 데이터를 insert하거나, update 시 실패할 수 있다.
substr 함수를 이용한 Function-based Index를 생성하거나 standard_hash 함수를 통해 컬럼 사이즈를 축소하면 해결 가능하다.


-- 크기초과로 인덱스 생성 불가능
create index t1_idx01 on t1(varchar2_data asc);
create index t1_idx01 on t1(varchar2_data asc)
                         *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

Identity 컬럼

Sequence generator로부터 정수값의 증가와 감소를 지정할 수 있음
규칙
- 테이블당 1개만 생성가능
- number 타입 컬럼만 가능
- default 지정 불가능
- Not Null, Not Deferrable 제약조건 암시적으로 정의됨
- 암호화 가능
- CTAS 명령을 실행해서 만든 테이블 컬럼은 Identity 컬럼 속성은 상속받지 않음


create table identify_table (s_key number generated as identity primary key, data varchar2(30));
insert into identify_table (data) values ('test');
commit;

select * from identify_table;

     S_KEY DATA
---------- ------------------------------
         1 test


insert into identify_table (data) values ('test222');
update identify_table set data='11111' where s_key=1;
commit;

select * from identify_table;

     S_KEY DATA
---------- ------------------------------
         1 11111
         2 test222

insert into identify_table values (3, '3333');
insert into identify_table values (3, '3333')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

-- 컬럼을 재정의하면 데이터를 직접 입력할 수 있다.
alter table identify_table modify ( s_key number generated by default as identity);
 
insert into identify_table values (5, '55555');
commit;

select * from identify_table;

     S_KEY DATA
---------- ------------------------------
         1 11111
         2 test222
         5 55555


-- 현재 시퀀스보다 큰 수를 수동으로 입력하고
-- 이후 자동으로 시퀀스로 채번하게 되면 UNIQUE 오류가 발생할 수 있다.
 select * from identify_table;

     S_KEY DATA
---------- ------------------------------
         1 11111
         2 test222
         5 55555
        21 3333
        22 4444
        23 test55555

insert into identify_table (data) values ('666666');
insert into identify_table (data) values ('77777');
insert into identify_table (data) values ('77777')
*
ERROR at line 1:
ORA-00001: unique constraint (JIGI.SYS_C0010318) violated


-- 시퀀스가 자동으로 생성됨
select sequence_name, cache_size, last_number from user_sequences;

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
ISEQ$$_93500                           20          41

-- 테이블 생성 스크립트를 뽑으면 아래와 같음
CREATE TABLE JIGI.IDENTIFY_TABLE
(
  S_KEY  NUMBER                                 DEFAULT "JIGI"."ISEQ$$_93500".nextval NOT NULL,
  DATA   VARCHAR2(30 BYTE)
);

Cascade Truncate 문

부모 테이블을 Truncate해서 자식 테이블까지 연쇄적으로 Truncate 되는 문장을 지원함


create table parent(id number primary key);
create table child(cid number primary key, pid number);
alter table child add constraint fk_parent_child foreign key(pid) references parent(id) on delete cascade;

insert into parent values(1);
insert into parent values(2);
insert into child values(1,1);
insert into child values(2,1);
insert into child values(3,2);
commit;

select * from parent;

        ID
----------
         1
         2

select * from child;

       CID        PID
---------- ----------
         1          1
         2          1
         3          2


truncate table parent cascade;

select * from parent;
no rows selected

select * from child;
no rows selected

Invisible 컬럼

아래의 경우 컬럼명이 보이지 않음
- Select * from table 과 같은 와일드카드 문자를 사용한 문장
- SQL*PLUS에서 DESC 명령
- PL/SQL %ROWTYPE 선언
- INSERT INTO SELECT * 문장


create table invisible (id number, invisible_col number invisible);
desc invisible;

 Name                             Null?    Type
 -------------------------------- -------- --------------------------------------------
 ID                                        NUMBER


insert into invisible values (1, 1);
insert into invisible values (1, 1)
            *
ERROR at line 1:
ORA-00913: too many values


insert into invisible (id, invisible_col) values (1, 1);

select * from invisible;

        ID
----------
         1

select id, invisible_col from invisible;

        ID INVISIBLE_COL
---------- -------------
         1             1

참고사항 : 토드 11에서도 안보인다.


col owner format a30
col table_name format a30
col column_name for a20

select owner, table_name, column_name, hidden_column
from dba_tab_cols
where table_name = 'INVISIBLE';

OWNER                          TABLE_NAME                     COLUMN_NAME          HID
------------------------------ ------------------------------ -------------------- ---
JIGI                           INVISIBLE                      INVISIBLE_COL        YES
JIGI                           INVISIBLE                      ID                   NO


alter table invisible modify(invisible_col visible);

desc invisible;
 Name                                    Null?    Type
 --------------------------------------- -------- --------------------------------------------
 ID                                               NUMBER
 INVISIBLE_COL                                    NUMBER

참고 : inivisible로 변경된 컬럼은 가장 마지막으로 위치가 바뀐다. 테이블의 컬럼을 순서를 바꾸고 싶은 경우 위치하고 싶은 컬럼 뒤 모든 컬럼을 invisible -> visible 함으로서 컬럼의 순서를 조정할 수 있다

오라클 시퀀스를 기반으로 한 디폴트 값

identity 컬럼과는 다르게 여러개의 컬럼에 선언가능
시퀀스가 미리 생성되어 있어야 한다.
시퀀스가 삭제된 상태에서 insert 작업시 에러가 발생한다.
테이블 소유자와 디폴트 값을 할당하는 컬럼의 유저는 시퀀스의 select 권한이 필요하다.
시퀀스가 적용된 컬럼에 수동으로 값을 지정하여 입력할 수 있다.


create sequence test_seq start with 1;
create table seq_table (id number default test_seq.nextval, test varchar2(10));

insert into seq_table values(null, '1');
insert into seq_table values(default, '2');
insert into seq_table (test) values('3');
insert into seq_table values(100, '4');

commit;

select * from seq_table;

        ID TEST
---------- ----------
           1
         1 2
         2 3
       100 4


insert into seq_table (test) values('5');
commit;

select * from seq_table;

        ID TEST
---------- ----------
           1
         1 2
         2 3
       100 4
         3 5

NULL 값이 입력되었을 때의 디폴트 값


create table sales_information ( 
  store_id number
, sales_date date default on null to_date('20991231', 'YYYYMMDD') not null
, sale_number number
, product_id number
, quantity number default on null 0 not null
, constraint pk_sales_information primary key (product_id, sales_date, sale_number)
);


insert into sales_information values (1, null, 1, 12345, null);
select * from sales_information;

  STORE_ID SALES_DATE          SALE_NUMBER PRODUCT_ID   QUANTITY
---------- ------------------- ----------- ---------- ----------
         1 2099-12-31 00:00:00           1      12345          0


select table_name, column_name, default_on_null from user_tab_columns where table_name ='SALES_INFORMATION';

TABLE_NAME                     COLUMN_NAME          DEF
------------------------------ -------------------- ---
SALES_INFORMATION              STORE_ID             NO
SALES_INFORMATION              SALES_DATE           YES
SALES_INFORMATION              SALE_NUMBER          NO
SALES_INFORMATION              PRODUCT_ID           NO
SALES_INFORMATION              QUANTITY             YES

시퀀스 관련 새로운 기능

세션단위로 생성되는 시퀀스


conn scott/tiger
create global temporary table gtt (id number, seq_number number);
grant all on gtt to jigi;
create sequence seq_session start with 1 session;
grant all on seq_session to jigi;

insert into gtt values ( 1, seq_session.nextval);
insert into gtt values ( 2, seq_session.nextval);
select * from scott.gtt;

        ID SEQ_NUMBER
---------- ----------
         1          1
         2          2

commit;

select * from scott.gtt;
no rows selected

insert into gtt values ( 1, seq_session.nextval);
insert into gtt values ( 2, seq_session.nextval);
select * from scott.gtt;

	ID SEQ_NUMBER
---------- ----------
         1          3
         2          4

commit;

conn jigi/oracle

select * from scott.gtt;

no rows selected

insert into scott.gtt values ( 1, scott.seq_session.nextval);
insert into scott.gtt values ( 2, scott.seq_session.nextval);

 select * from scott.gtt;

        ID SEQ_NUMBER
---------- ----------
         1          1
         2          2

commit;


select * from scott.gtt;

no rows selected

insert into scott.gtt values ( 1, scott.seq_session.nextval);
insert into scott.gtt values ( 2, scott.seq_session.nextval);

select * from scott.gtt;

        ID SEQ_NUMBER
---------- ----------
         1          3
         2          4

동일한 컬럼에 대한 다른 종류의 멀티 인덱스

동일 컬럼에 서로 다른 인덱스 타입으로 2개 이상의 인덱스를 만들 수 있다.
단, 1개의 인덱스만 visible로 허용된다. 나머지 인덱스는 invisible로 생성가능


create table emp_tab as select * from scott.emp;
create index emp_tab_ix01 on emp_tab(hiredate, deptno);

-- 동일컬럼에 비트맵 인덱스를 바로 생성하려고 하면 에러가 난다.
create bitmap index emp_tab_ix02 on emp_tab(hiredate, deptno);
create bitmap index emp_tab_ix02 on emp_tab(hiredate, deptno)
                                            *
ERROR at line 1:
ORA-01408: such column list already indexed

-- 두번째 인덱스를 생성하기 위해 처음 인덱스를 invisible로 변경하면 인덱스가 생성된다.
alter index emp_tab_ix01 invisible;

Index altered.

create bitmap index emp_tab_ix02 on emp_tab(hiredate, deptno);

Index created.

이 기능은 가용성을 유지하는 목적으로 유용하다. 기존과 다른 새로운 인덱스를 새롭게 만들어야 하는 경우, invisible 속성으로 새로운 유형의 인덱스를 만들고 기존 인덱스를 삭제한 후 새롭게 만든 인덱스를 visible 하면 된다.

with 쿼리의 새로운 기능

with 절 내에서 PL/SQL 기능을 사용할 수 있다.
단, PL/SQL 내에서 이러한 형태의 with 절은 사용할 수 없다.


create table test_table (id number);
insert into test_table values (1);
insert into test_table values (2);
commit;

select * from test_table;

        ID
----------
         1
         2

with 
function plus_one(p_id number) 
return number 
is
begin
	return p_id + 1;
end;
select plus_one(id) from test_table;
/

PLUS_ONE(ID)
------------
           2
           3

오라클 데이터베이스 12c PL/SQL의 새로운 기능

뷰에서 Bequeath 절 사용

BEQUEATH CURRENT_USER : 해당 뷰를 호출한(사용하는) 계정의 권한으로 뷰를 실행
BEQUEATH DEFINER : 해당 뷰를 생성한 계정의 권한으로 뷰를 실행

PL/SQL 서브프로그램이 허가된 리스트


create or replace function helper (v_input number)
return number
accessible by (api) is    -- 해당 FUNCTION은 API라는 PL/SQL에서만 호출가능
begin
    return v_input + 1;
end;


JIGI@2016-11-22 11:32:45> select helper(1) from dual;
select helper(1) from dual
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object HELPER



create or replace function api (v_input number)
return number
is 
    v_return number;
begin
    v_return := helper(v_input);
    return v_return;
end;



JIGI@2016-11-22 11:33:08> select api(1) from dual;

    API(1)
----------
         2

JIGI@2016-11-22 11:35:54>

PL/SQL에서 SQL 인터페이스를 통한 PL/SQL 지정 데이터 타입 사용

11g까지는 PL/SQL 전용 데이터타입(boolean, 연관배열, record 타입 등)을 바인딩 할 수 없었으나, 12c에서 사용가능해짐
일부 제한사항이 존재함
- PL/SQL 전용데이터 타입이 미리 정의되어 있거나 PACKAGE SPEC에 선언되어 있어야 함
- 연관배열을 사용시 배열은 PLS_INTEGER 데이터 타입에 인덱스화가 되어 있어야함
- PL/SQL 함수 사용 시 PL/SQL 전용 데이터 타입은 SQL로 리턴되지 못함
- BOOLEAN 값은 STATICS SQL 쿼리에서 호출되는 PL/SQL 함수의 인수가 되지 못함
- SQL 문장 내에서는 PACKAGE SPEC에 선언되는 RETURN 타입을 가진 함수는 사용 할 수 없음


CREATE OR REPLACE PROCEDURE ANSWER_ME (P_R BOOLEAN)
IS
BEGIN
    IF P_R THEN
        DBMS_OUTPUT.PUT_LINE('ANSWER IS TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('ANSWER IS FALSE');
    END IF;
END;


SET SERVEROUTPUT ON
DECLARE
    V_SQL VARCHAR2(1000);
    V_BOOLEAN BOOLEAN := TRUE;
BEGIN
    V_SQL := 'BEGIN ANSWER_ME(:X); END;';
    EXECUTE IMMEDIATE V_SQL USING V_BOOLEAN;
END;


ANSWER IS TRUE
PL/SQL procedure successfully completed.

PL/SQL 프로그램 단위에서 호출자 권한의 보안 권한 변경

PL/SQL 프로그램에서 프로시저를 생성 시 호출자 권한을 정의하려는 경우, PL/SQL 프로그램 유닛의 소유자가 INHERIT ANY PRIVILEGE나 INHERIT PRIVILEGE 권한을 가져야 함.
12c에서는 호환성을 위해 기본적으로 grant inherit any privileges to public가 모든 계정에 적용되어 있다.


grant inherit privileges on user scott to public;
grant inherit privileges on user to tiger;
grant inherit any privileges to tiger;

위 3가지 방법으로 권한을 부여가능함

PL/SQL 패키지와 독립 서브프로그램에 롤 부여


-- 테이블 생성
CREATE TABLE need_privs (id NUMBER);

INSERT INTO need_privs VALUES (1);

COMMIT;

-- function 생성
CREATE OR REPLACE FUNCTION get_new_value(v_input IN NUMBER)
    RETURN NUMBER
--    AUTHID definer           -- 해당 function을 만든 사용자 권한으로 실행한다는 의미
    AUTHID current_user    -- 해당 function을 호출자(실행자) 권한으로 실행한다는 의미
AS
    v_number   NUMBER := 0;
BEGIN
    SELECT id + v_input INTO v_number FROM scott.need_privs;

    RETURN v_number;
END;


SCOTT@2016-11-22 14:51:11> select get_new_value(1) from dual;

GET_NEW_VALUE(1)
----------------
               2


-- 실행 권한 부여
SCOTT@2016-11-22 15:01:35> grant execute on get_new_value to hr;

Grant succeeded.

SCOTT@2016-11-22 15:01:45> conn hr/hr
Connected.

-- 권한이 없어서 오류발생
HR@2016-11-22 15:01:48> select scott.get_new_value(1) from dual;
select scott.get_new_value(1) from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.GET_NEW_VALUE", line 7


-- Role을 만들어본다.
HR@2016-11-22 15:02:04> conn scott/tiger
Connected.
SCOTT@2016-11-22 15:05:56> create role run_get_new_value;
create role run_get_new_value
*
ERROR at line 1:
ORA-01031: insufficient privileges


SCOTT@2016-11-22 15:06:11> conn /as sysdba
Connected.
SYS@2016-11-22 15:06:42> grant create role to scott;

Grant succeeded.

SYS@2016-11-22 15:07:14> conn scott/tiger
Connected.
SCOTT@2016-11-22 15:07:19> create role run_get_new_value;

Role created.

SCOTT@2016-11-22 15:07:32> grant select on need_privs to run_get_new_value;

Grant succeeded.

-- 12c에 새로생긴 기능
SCOTT@2016-11-22 15:07:57> grant run_get_new_value to function get_new_value;

Grant succeeded.


SCOTT@2016-11-22 15:08:25> conn hr/hr
Connected.

-- 결과가 잘 나옴
HR@2016-11-22 15:10:20> select scott.get_new_value(1) from dual;

SCOTT.GET_NEW_VALUE(1)
----------------------
                     2

HR@2016-11-22 15:10:27>

PL/SQL의 기타 새로운 기능

function에 result_cache 절 지원 : Result Cache가 가능해짐
Pluggable 데이터베이스 트리거 지원
- before unplug : 데이터베이스가 언플러그 되기 전에 실행됨
- after clone : PDB가 복제되고 나서 실행됨, 이후 트리거는 제거됨.
디버깅을 위한 PL/SQL 지시어 추가


-- 디버깅을 위한 새로운 파라미터 제공
CREATE OR REPLACE procedure test_procedure
is
begin
   dbms_output.put_line('$$plsql_unit_owner : ' || $$plsql_unit_owner);
   dbms_output.put_line('$$plsql_unit_type : ' || $$plsql_unit_type);
end test_procedure;
/

HR@2016-11-22 15:39:00> exec test_procedure;
$$plsql_unit_owner : HR
$$plsql_unit_type : PROCEDURE

06. SQL, PLSQL의 새로운 기능 (by jiginet) [2016.12.02]

오라클 데이터베이스 12c PL/SQL의 새로운 기능

로우 패턴매칭

Oracle Native Left Outer Join 문법개선

Top-n 쿼리의 새로운 기능 (row limiting)

union, union all 분기문장 동시실행

오라클 데이터베이스 12c DDL의 새로운 기능

VARCHAR2, NVARCHAR2 RAW 데이터 타입의 최대 길이 증가

Identity 컬럼

Cascade Truncate 문

Invisible 컬럼

오라클 시퀀스를 기반으로 한 디폴트 값

NULL 값이 입력되었을 때의 디폴트 값

시퀀스 관련 새로운 기능

동일한 컬럼에 대한 다른 종류의 멀티 인덱스

with 쿼리의 새로운 기능

오라클 데이터베이스 12c PL/SQL의 새로운 기능

뷰에서 Bequeath 절 사용

PL/SQL 서브프로그램이 허가된 리스트

PL/SQL에서 SQL 인터페이스를 통한 PL/SQL 지정 데이터 타입 사용

PL/SQL 프로그램 단위에서 호출자 권한의 보안 권한 변경

PL/SQL 패키지와 독립 서브프로그램에 롤 부여

PL/SQL의 기타 새로운 기능