'Oracle CURD Package Sample'에 해당되는 글 1건

  1. 2014.11.14 [펌] Oracle CURD Package Sample
02.Oracle/DataBase2014. 11. 14. 20:23

[펌] Oracle CURD Package Sample

반응형

출처 : http://blog.beany.co.kr/archives/485

예전에 테스트로 만들어 본 Oracle CURD Package Sample

  • Package 정의
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
CREATE OR REPLACE PACKAGE SP_CMM_DEVL_0170
AS
TYPE REF_TYPE IS REF CURSOR;
 
-- 다중 반환
PROCEDURE MULTI_RETURN (
                p_search_str IN  VARCHAR2
               ,p_return_1   OUT REF_TYPE
               ,p_return_2   OUT VARCHAR2
              );
 
-- 목록
PROCEDURE LST (
                p_search_str IN  VARCHAR2
               ,p_return     OUT REF_TYPE
              );
 
-- 입력
PROCEDURE INS (
                p_word_kor  IN VARCHAR2
               ,p_abbr_nm   IN VARCHAR2
               ,p_word_eng  IN VARCHAR2
               ,p_word_desc IN VARCHAR2
               ,p_reg_id    IN VARCHAR2
               ,p_reg_ip    IN VARCHAR2
               ,p_reg_dt    IN VARCHAR2
              );
 
-- 수정
PROCEDURE UPD (
                p_seq       IN NUMBER
               ,p_word_kor  IN VARCHAR2
               ,p_abbr_nm   IN VARCHAR2
               ,p_word_eng  IN VARCHAR2
               ,p_word_desc IN VARCHAR2
               ,p_upd_id    IN VARCHAR2
               ,p_upd_ip    IN VARCHAR2
               ,p_upd_dt    IN VARCHAR2
              );
 
-- 삭제
PROCEDURE DEL (
                p_seq IN NUMBER
              );
 
END SP_CMM_DEVL_0170;
/
  • Package Body 구현
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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
CREATE OR REPLACE PACKAGE BODY SP_CMM_DEVL_0170
IS
 
-- 다중 반환
PROCEDURE MULTI_RETURN (
                p_search_str IN  VARCHAR2
               ,p_return_1   OUT REF_TYPE
               ,p_return_2   OUT VARCHAR2
)
IS
    v_query   VARCHAR2(1000);
    v_message VARCHAR2(2000);
BEGIN
     
    v_query := 'SELECT  SEQ
                       ,WORD_KOR
                       ,ABBR_NM
                       ,WORD_ENG
                       ,WORD_DESC
                       ,REG_ID
                       ,REG_IP
                       ,REG_DT
                       ,UPD_ID
                       ,UPD_IP
                       ,UPD_DT
                FROM   SAMPLE_DIC
               WHERE  1 = 1';
 
    IF (LENGTH(p_search_str) > 0) THEN
        v_query := v_query || ' AND WORD_KOR LIKE ''' || p_search_str || '%''';
    END IF;
     
    v_query := v_query || ' ORDER BY SEQ';
     
    OPEN p_return_1 FOR v_query;
     
    v_message  := 'Procedure 반환값!!';
    p_return_2 := v_message;
END MULTI_RETURN;
 
-- 목록
PROCEDURE LST (
                p_search_str IN  VARCHAR2
               ,p_return     OUT REF_TYPE
)
IS
    v_query VARCHAR2(1000);
BEGIN
     
    v_query := 'SELECT  SEQ
                       ,WORD_KOR
                       ,ABBR_NM
                       ,WORD_ENG
                       ,WORD_DESC
                       ,REG_ID
                       ,REG_IP
                       ,REG_DT
                       ,UPD_ID
                       ,UPD_IP
                       ,UPD_DT
                FROM   SAMPLE_DIC
               WHERE  1 = 1';
 
    IF (LENGTH(p_search_str) > 0) THEN
        v_query := v_query || ' AND WORD_KOR LIKE ''' || p_search_str || '%''';
    END IF;
     
    v_query := v_query || ' ORDER BY SEQ';
     
    OPEN p_return FOR v_query;
END LST;
 
-- 입력
PROCEDURE INS (
                p_word_kor  IN VARCHAR2
               ,p_abbr_nm   IN VARCHAR2
               ,p_word_eng  IN VARCHAR2
               ,p_word_desc IN VARCHAR2
               ,p_reg_id    IN VARCHAR2
               ,p_reg_ip    IN VARCHAR2
               ,p_reg_dt    IN VARCHAR2
)
IS
BEGIN
    INSERT INTO SAMPLE_DIC (
                             SEQ
                            ,WORD_KOR
                            ,ABBR_NM
                            ,WORD_ENG
                            ,WORD_DESC
                            ,REG_ID
                            ,REG_IP
                            ,REG_DT
                           )
    VALUES (
             SAMPLE_DIC_SEQ.NEXTVAL
            ,p_word_kor
            ,p_abbr_nm
            ,p_word_eng
            ,p_word_desc
            ,p_reg_id
            ,p_reg_ip
            ,p_reg_dt
           );
END INS;
 
-- 수정
PROCEDURE UPD (
                p_seq       IN NUMBER
               ,p_word_kor  IN VARCHAR2
               ,p_abbr_nm   IN VARCHAR2
               ,p_word_eng  IN VARCHAR2
               ,p_word_desc IN VARCHAR2
               ,p_upd_id    IN VARCHAR2
               ,p_upd_ip    IN VARCHAR2
               ,p_upd_dt    IN VARCHAR2
)
IS
BEGIN
    UPDATE  SAMPLE_DIC
    SET     WORD_KOR  = p_word_kor
           ,ABBR_NM   = p_abbr_nm
           ,WORD_ENG  = p_word_eng
           ,WORD_DESC = p_word_desc
           ,UPD_ID    = p_upd_id
           ,UPD_IP    = p_upd_ip
           ,UPD_DT    = p_upd_dt
    WHERE  SEQ        = p_seq;
END UPD;
 
-- 삭제
PROCEDURE DEL (
                p_seq IN NUMBER
)
IS
BEGIN
    DELETE
    FROM SAMPLE_DIC
    WHERE SEQ = p_seq;
END DEL;
 
END SP_CMM_DEVL_0170;
/


Posted by 1010

티스토리툴바