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 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201
|
DROP PACKAGE my_package
/
DROP TYPE my_array_type
/
DROP TABLE test_laly
/
CREATE TABLE test_laly(id number, grp1 number, grp2 number);
INSERT INTO test_laly values(1,100,1001);
INSERT INTO test_laly values(2,200,1002);
INSERT INTO test_laly values(3,200,1001);
INSERT INTO test_laly values(4,300,1002);
INSERT INTO test_laly values(5,400,1003);
INSERT INTO test_laly values(6,400,1004);
INSERT INTO test_laly values(7,500,1004);
COMMIT;
CREATE OR REPLACE TYPE my_type AS
OBJECT
(id INTEGER
,grp1 INTEGER
,grp2 INTEGER)
/
CREATE TYPE my_array_type AS TABLE OF my_type
/
CREATE OR REPLACE PACKAGE my_package AS
/* $Header: $ */
/*======================================================================+
| |
+======================================================================*/
TYPE t_array_integer IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
g_cached_id_breakup INTEGER;
g_cached_array my_array_type;
/*======================================================================+
| |
+======================================================================*/
FUNCTION my_function
(p_id_breakup INTEGER
,p_grp1 INTEGER
,p_grp2 INTEGER)
RETURN INTEGER;
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package AS
/* $Header: $ */
/*======================================================================+
| |
+======================================================================*/
/*======================================================================+
| |
+======================================================================*/
PROCEDURE get_all
(p_id1 INTEGER
,p_id2 INTEGER
,o_array_id OUT t_array_integer
,o_array_grp1 OUT t_array_integer
,o_array_grp2 OUT t_array_integer)
IS
BEGIN
SELECT id
,grp1
,grp2
BULK COLLECT
INTO o_array_id
,o_array_grp1
,o_array_grp2
FROM test_laly
WHERE grp1 = p_id1
OR grp2 = p_id2
MINUS
SELECT id
,grp1
,grp2
FROM TABLE(CAST(g_cached_array AS my_array_type))
;
END get_all;
/*======================================================================+
| |
+======================================================================*/
PROCEDURE rebuild_cache
(p_id_breakup INTEGER)
IS
i INTEGER;
l_new_array_id t_array_integer;
l_new_array_grp1 t_array_integer;
l_new_array_grp2 t_array_integer;
BEGIN
g_cached_array := my_array_type(NULL);
g_cached_array(1):= my_type(NULL,NULL,NULL);
SELECT id
,grp1
,grp2
INTO g_cached_array(1).id
,g_cached_array(1).grp1
,g_cached_array(1).grp2
FROM test_laly
WHERE id = p_id_breakup
;
i := 0;
WHILE 1=1 LOOP
i := i + 1;
IF i > g_cached_array.COUNT THEN
EXIT;
END IF;
get_all
(p_id1 => g_cached_array(i).grp1
,p_id2 => g_cached_array(i).grp2
,o_array_id => l_new_array_id
,o_array_grp1 => l_new_array_grp1
,o_array_grp2 => l_new_array_grp2);
FOR j IN 1..l_new_array_id.COUNT LOOP
g_cached_array.EXTEND(1);
g_cached_array(g_cached_array.COUNT) := my_type(NULL,NULL,NULL);
g_cached_array(g_cached_array.COUNT).id := l_new_array_id(j);
g_cached_array(g_cached_array.COUNT).grp1 := l_new_array_grp1(j);
g_cached_array(g_cached_array.COUNT).grp2 := l_new_array_grp2(j);
END LOOP;
END LOOP;
END rebuild_cache;
/*======================================================================+
| |
+======================================================================*/
FUNCTION my_function
(p_id_breakup INTEGER
,p_grp1 INTEGER
,p_grp2 INTEGER)
RETURN INTEGER
IS
l_found BOOLEAN;
BEGIN
IF p_id_breakup <> g_cached_id_breakup
OR g_cached_id_breakup IS NULL THEN
rebuild_cache
(p_id_breakup => p_id_breakup);
g_cached_id_breakup := p_id_breakup;
END IF;
l_found := FALSE;
FOR i IN 1..g_cached_array.COUNT LOOP
IF g_cached_array(i).grp1 = p_grp1
OR g_cached_array(i).grp2 = p_grp2 THEN
l_found := TRUE;
EXIT;
END IF;
END LOOP;
IF l_found THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END my_function ;
END my_package;
/
SELECT *
FROM test_laly a
WHERE my_package.my_function(1,a.grp1,a.grp2) = 1
/
SELECT *
FROM test_laly a
WHERE my_package.my_function(5,a.grp1,a.grp2) = 1
/
SELECT *
FROM test_laly a
WHERE my_package.my_function(2,a.grp1,a.grp2) = 1
/ |
Partager