Polytech_SQL/4_TABLE_1.SQL at master · park-cheonho/Polytech_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
DECLARE
	TYPE T_EMP_LIST IS TABLE OF VARCHAR2(20)
       		 INDEX BY BINARY_INTEGER;

	TBL_EMP_LIST 	T_EMP_LIST;
    	V_TMP   VARCHAR2(20);
    	V_INDEX NUMBER(10);
BEGIN
    TBL_EMP_LIST(1) := 'SCOTT';
    TBL_EMP_LIST(1000) := 'MILLER';
    TBL_EMP_LIST(-2134) := 'ALLEN';

    TBL_EMP_LIST(0) :='XMAN';

    V_TMP := TBL_EMP_LIST(1000);


    DBMS_OUTPUT.PUT_LINE('DATA OF KEY 1000 IS '||TBL_EMP_LIST(1000));
    DBMS_OUTPUT.PUT_LINE('DATA OF KEY -2134 IS '||TBL_EMP_LIST(-2134));
    DBMS_OUTPUT.PUT_LINE('DATA OF KEY 1 IS '||TBL_EMP_LIST(1));


    IF NOT TBL_EMP_LIST.EXISTS(888) THEN
        DBMS_OUTPUT.PUT_LINE('DATA OF KEY 888 IS NOT EXIST');
    END IF;


    V_INDEX := TBL_EMP_LIST.FIRST; --EX)PRIOR,FIRST,LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE('LOOP' || TO_CHAR(V_INDEX) || '==>' || TBL_EMP_LIST(V_INDEX));
        V_INDEX := TBL_EMP_LIST.NEXT(V_INDEX); -- 다음번 유효한 INDEX RETURN
        EXIT WHEN V_INDEX IS NULL;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('DATA OF KEY 999 IS '||TBL_EMP_LIST(999));
    DBMS_OUTPUT.PUT_LINE('DATA OF KEY 0 IS '||TBL_EMP_LIST(0));

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ERROR CODE=>'|| TO_CHAR(SQLCODE));
        DBMS_OUTPUT.PUT_LINE('ERROR MSG=>'|| SQLERRM);
END;
/
DECLARE
    TYPE T_EMP_LIST IS TABLE OF VARCHAR2(20)
        INDEX BY BINARY_INTEGER;

    TBL_EMP_LIST    T_EMP_LIST;
    V_TMP           VARCHAR2(20);
    V_INDEX         NUMBER(10);

BEGIN
    TBL_EMP_LIST(1) := 'SCOTT';
    TBL_EMP_LIST(1000) := 'MILLER';
    TBL_EMP_LIST(-2134) := 'ALLEN';

    TBL_EMP_LIST(0) := 'XMAN';
    V_TMP := TBL_EMP_LIST(1000);

    DBMS_OUTPUT.PUT_LINE('DATA OF KEY 1000 IS '||TBL_EMP_LIST(1000));
    DBMS_OUTPUT.PUT_LINE('DATA OF KEY -2134 IS '||TBL_EMP_LIST(-2134));
    DBMS_OUTPUT.PUT_LINE('DATA OF KEY 1 IS '||TBL_EMP_LIST(1));

    IF NOT TBL_EMP_LIST.EXISTS(888) THEN
        DBMS_OUTPUT.PUT_LINE('DATA OF KEY 888 IS NOT EXIST');
    END IF;

    V_INDEX := TBL_EMP_LIST.FIRST;
    LOOP
        DBMS_OUTPUT.PUT_LINE('LOOP' || TO_CHAR(V_INDEX) ||'==>' || TBL_EMP_LIST(V_INDEX));
        V_INDEX := TBL_EMP_LIST.NEXT(V_INDEX);
        EXIT WHEN V_INDEX IS NULL;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('DATA OF 999 IS ' || TBL_EMP_LIST(999));
    DBMS_OUTPUT.PUT_LINE('DATA OF KEY 0 IS' || TBL_EMP_LIST(0));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ERROR CODE =>' || TO_CHAR(SQLCODE));
        DBMS_OUTPUT.PUT_LINE('ERROR MSG=>' || TO_CHAR(SQLERRM));
END;
/