CLEAR;
SET feedback off
SET define off
set serveroutput on
SPOOL CreateExample.log
DROP TABLE detailtable_disp_total CASCADE CONSTRAINTS;
DROP TABLE detailtable_disp_partial CASCADE CONSTRAINTS;
DROP TABLE detailtable_consecutive CASCADE CONSTRAINTS;
DROP TABLE mastertable CASCADE CONSTRAINTS;
DROP TABLE detailtable_perm CASCADE CONSTRAINTS;
DROP TABLE detailtable_perm_raw CASCADE CONSTRAINTS;
DROP TABLE mastertable_perm CASCADE CONSTRAINTS;
DROP TABLE mastertable_perm_raw CASCADE CONSTRAINTS;
--------------------------------------------------------------------------------
PROMPT Creating table MASTERTABLE
CREATE TABLE mastertable
(
itemgroup RAW(16) NOT NULL
);
ALTER TABLE mastertable
ADD CONSTRAINT pk_mastertable PRIMARY KEY (itemgroup);
--------------------------------------------------------------------------------
PROMPT Creating table DETAILTABLE_CONSECUTIVE
CREATE TABLE detailtable_consecutive
(
RANK NUMBER(38) NOT NULL,
rankminusone NUMBER(38),
item RAW(16) NOT NULL,
itemgroup RAW(16)
);
ALTER TABLE detailtable_consecutive
ADD CONSTRAINT pk_detailtable_consecutive PRIMARY KEY (item);
ALTER TABLE detailtable_consecutive
ADD CONSTRAINT ak_detailtable_consecutive UNIQUE (RANK,itemgroup)
DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE detailtable_consecutive
ADD CONSTRAINT fk_det_cons_ref_master FOREIGN KEY (itemgroup)
REFERENCES mastertable (itemgroup);
ALTER TABLE detailtable_consecutive
ADD CONSTRAINT fk_cons_ref_cons FOREIGN KEY (rankminusone,itemgroup)
REFERENCES detailtable_consecutive (RANK,itemgroup)
DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE detailtable_consecutive
ADD CONSTRAINT chk_detailtable_consecutive CHECK ((RANK =1 AND rankminusone IS NULL)OR RANK>1 AND rankminusone=RANK-1) DEFERRABLE INITIALLY DEFERRED;
--------------------------------------------------------------------------------
PROMPT Creating table DETAILTABLE_DISP_PARTIAL
CREATE TABLE detailtable_disp_partial
(
RANK NUMBER(38) NOT NULL,
item RAW(16) NOT NULL,
itemgroup RAW(16)
);
ALTER TABLE detailtable_disp_partial
ADD CONSTRAINT pk_detailtable_disp_partial PRIMARY KEY (item);
ALTER TABLE detailtable_disp_partial
ADD CONSTRAINT fk_det_dsp_part_ref_master FOREIGN KEY (itemgroup)
REFERENCES mastertable (itemgroup);
ALTER TABLE detailtable_disp_partial
ADD CONSTRAINT chk_detailtable_disp_partial CHECK (RANK > 0);
--------------------------------------------------------------------------------
PROMPT Creating table DETAILTABLE_DISP_TOTAL
CREATE TABLE detailtable_disp_total
(
RANK NUMBER(38) NOT NULL,
item RAW(16) NOT NULL,
itemgroup RAW(16)
);
ALTER TABLE detailtable_disp_total
ADD CONSTRAINT pk_detailtable_disp_total PRIMARY KEY (item);
ALTER TABLE detailtable_disp_total
ADD CONSTRAINT ak_detailtable_disp_total UNIQUE (RANK,itemgroup) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE detailtable_disp_total
ADD CONSTRAINT fk_disp_tot_ref_master FOREIGN KEY (itemgroup)
REFERENCES mastertable (itemgroup);
ALTER TABLE detailtable_disp_total
ADD CONSTRAINT chk_detailtable_disp_total CHECK (RANK > 0);
--------------------------------------------------------------------------------
PROMPT Creating table MASTERTABLE_PERM
CREATE TABLE mastertable_perm
(
itemgroup RAW(16) NOT NULL,
grouppermutation NUMBER(38)
);
ALTER TABLE mastertable_perm
ADD CONSTRAINT pk_mastertable_perm PRIMARY KEY (itemgroup);
--------------------------------------------------------------------------------
PROMPT Creating table DETAILTABLE_PERM
CREATE TABLE detailtable_perm
(
item RAW(16) NOT NULL,
itemgroup RAW(16) NOT NULL
);
ALTER TABLE detailtable_perm
ADD CONSTRAINT pk_detailtable_perm PRIMARY KEY (item);
ALTER TABLE detailtable_perm
ADD CONSTRAINT ak_detailtable_perm UNIQUE (item,itemgroup);
ALTER TABLE detailtable_perm
ADD CONSTRAINT fk_detperm_ref_masterper FOREIGN KEY (itemgroup)
REFERENCES mastertable_perm (itemgroup);
--------------------------------------------------------------------------------
PROMPT Creating table MASTERTABLE_PERM_RAW
CREATE TABLE mastertable_perm_raw
(
itemgroup RAW(16) NOT NULL,
grouppermutation RAW(2000)
);
ALTER TABLE mastertable_perm_raw
ADD CONSTRAINT pk_mastertable_perm_raw PRIMARY KEY (itemgroup);
--------------------------------------------------------------------------------
PROMPT Creating table DETAILTABLE_PERM_RAW
CREATE TABLE detailtable_perm_raw
(
item RAW(16) NOT NULL,
itemgroup RAW(16) NOT NULL
);
ALTER TABLE detailtable_perm_raw
ADD CONSTRAINT pk_detailtable_perm_raw PRIMARY KEY (item);
ALTER TABLE detailtable_perm_raw
ADD CONSTRAINT ak_detailtable_perm_raw UNIQUE (item,itemgroup);
ALTER TABLE detailtable_perm_raw
ADD CONSTRAINT fk_detperm_ref_masterper_raw FOREIGN KEY (itemgroup)
REFERENCES mastertable_perm_raw (itemgroup);
PROMPT Creating packages and spec
PROMPT Please note that maxint is defined in package spec common
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
prompt Creating package COMMON
CREATE OR REPLACE PACKAGE COMMON
IS
maxint CONSTANT NUMBER (38, 0) := 99999999999999999999999999999999999999;
--10^39-1
END;
/
prompt Creating package CONSECUTIVE
CREATE OR REPLACE PACKAGE CONSECUTIVE
IS
END;
/
prompt Creating package PARTIALORDER
CREATE OR REPLACE PACKAGE PARTIALORDER
IS
PROCEDURE redistranksglobally (itemgroupguid IN RAW);
PROCEDURE additemtogroup (
itemguid IN RAW,
itemgroupguid IN RAW,
predecessorrank IN INTEGER,
allowsamerank IN BOOLEAN
);
END;
/
prompt Creating type RAWTABLE
create or replace type RawTable as varray(32000) of raw(16)
/
prompt Creating package PERMUTATIONS
CREATE OR REPLACE PACKAGE PERMUTATIONS
IS
--------------------------------------------------------------------------------
FUNCTION arrangecategoryitems (initems IN rawtable)
RETURN NUMBER;
--------------------------------------------------------------------------------
FUNCTION reversearrangecategoryitems (
permutation IN NUMBER,
items IN rawtable
)
RETURN rawtable;
--------------------------------------------------------------------------------
PROCEDURE arrangecategoryitems (initems IN rawtable, RESULT OUT NUMBER);
--------------------------------------------------------------------------------
PROCEDURE reversearrangecategoryitems (
permutation IN NUMBER,
items IN OUT NOCOPY rawtable
);
--------------------------------------------------------------------------------
PROCEDURE addnewitemingroup (
itemgroupguid IN RAW,
newitemguid IN RAW,
itempredecessor IN RAW
);
--------------------------------------------------------------------------------
PROCEDURE updatepermutationforgroup (
itemgroupguid IN RAW,
itemguids IN rawtable
);
--------------------------------------------------------------------------------
FUNCTION retriveitemsorderedforgroup (itemgroupguid IN RAW)
RETURN rawtable;
--------------------------------------------------------------------------------
END;
/
prompt Creating package PERMUTATIONSWITHRAW
CREATE OR REPLACE PACKAGE PERMUTATIONSWITHRAW
IS
--------------------------------------------------------------------------------
FUNCTION arrangecategoryitems (initems IN rawtable)
RETURN RAW;
--------------------------------------------------------------------------------
PROCEDURE arrangecategoryitems (initems IN rawtable, RESULT OUT RAW);
--------------------------------------------------------------------------------
FUNCTION reversearrangecategoryitems (permutation IN RAW, items IN rawtable)
RETURN rawtable;
--------------------------------------------------------------------------------
PROCEDURE reversearrangecategoryitems (
permutation IN RAW,
items IN OUT NOCOPY rawtable
);
--------------------------------------------------------------------------------
PROCEDURE addnewitemingroup (
itemgroupguid IN RAW,
newitemguid IN RAW,
itempredecessor IN RAW
);
--------------------------------------------------------------------------------
PROCEDURE updatepermutationforgroup (
itemgroupguid IN RAW,
itemguids IN rawtable
);
--------------------------------------------------------------------------------
FUNCTION retriveitemsorderedforgroup (itemgroupguid IN RAW)
RETURN rawtable;
--------------------------------------------------------------------------------
END;
/
prompt Creating package TOTALORDER
CREATE OR REPLACE PACKAGE TOTALORDER
IS
PROCEDURE additemtogroup (
itemguid IN RAW,
itemgroupguid IN RAW,
predecessorrank IN NUMBER
);
PROCEDURE redistranksglobally (itemgroupguid IN RAW);
END;
/
prompt Creating package body COMMON
CREATE OR REPLACE PACKAGE BODY COMMON
IS
END;
/
prompt Creating package body CONSECUTIVE
CREATE OR REPLACE PACKAGE BODY CONSECUTIVE
IS
--------------------------------------------------------------------------------
PROCEDURE insertnewrank (
itemgroupguid IN RAW,
newrank IN NUMBER,
newitem IN RAW
)
IS
BEGIN
UPDATE detailtable_consecutive
SET detailtable_consecutive.RANK = RANK + 1,
detailtable_consecutive.rankminusone = RANK
WHERE detailtable_consecutive.RANK >= newrank
AND detailtable_consecutive.itemgroup = itemgroupguid;
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (newrank, DECODE (newrank, 1, NULL, newrank - 1), newitem,
itemgroupguid
);
END;
--------------------------------------------------------------------------------
PROCEDURE removerank (itemgroupguid IN RAW, deleterank IN NUMBER)
IS
BEGIN
DELETE detailtable_consecutive
WHERE detailtable_consecutive.itemgroup = itemgroupguid
AND detailtable_consecutive.RANK = deleterank;
UPDATE detailtable_consecutive
SET detailtable_consecutive.RANK = RANK - 1,
detailtable_consecutive.rankminusone =
DECODE (rankminusone,
1, NULL,
rankminusone - 1
)
WHERE detailtable_consecutive.RANK > deleterank
AND detailtable_consecutive.itemgroup = itemgroupguid;
END;
--------------------------------------------------------------------------------
END;
/
prompt Creating package body PARTIALORDER
CREATE OR REPLACE PACKAGE BODY PARTIALORDER
IS
--------------------------------------------------------------------------------
CURSOR ranks (groupid RAW)
IS
SELECT RANK, item
FROM detailtable_disp_partial t
WHERE t.itemgroup = groupid
ORDER BY RANK ASC
FOR UPDATE OF RANK;
--------------------------------------------------------------------------------
PROCEDURE redistranksglobally (itemgroupguid IN RAW)
IS
index_ INTEGER;
previousrank_ NUMBER;
previosindex_ INTEGER;
isfirst_ BOOLEAN;
newrank NUMBER (38, 0);
n INTEGER;
BEGIN
SELECT COUNT (1)
INTO n
FROM detailtable_disp_partial
WHERE detailtable_disp_partial.itemgroup = itemgroupguid;
isfirst_ := TRUE;
FOR r IN ranks (itemgroupguid)
LOOP
IF isfirst_
THEN
index_ := 1;
previousrank_ := r.RANK;
previosindex_ := 1;
isfirst_ := FALSE;
END IF;
IF (index_ != previosindex_ AND r.RANK = previousrank_)
THEN
newrank := ROUND (common.maxint / (n + 1)) * previosindex_;
ELSE
newrank := ROUND (common.maxint / (n + 1)) * index_;
previosindex_ := index_;
previousrank_ := r.RANK;
END IF;
UPDATE detailtable_disp_partial
SET detailtable_disp_partial.RANK = NVL (newrank, r.RANK)
WHERE CURRENT OF ranks;
index_ := index_ + 1;
END LOOP;
END;
--------------------------------------------------------------------------------
PROCEDURE additemtogroup (
itemguid IN RAW,
itemgroupguid IN RAW,
predecessorrank IN INTEGER,
allowsamerank IN BOOLEAN
)
IS
aux INTEGER;
hirank INTEGER;
lowrank INTEGER;
curentrank INTEGER;
preditem RAW (16);
BEGIN
BEGIN
SELECT MIN (RANK)
INTO hirank
FROM detailtable_disp_partial
WHERE detailtable_disp_partial.RANK > predecessorrank;
IF hirank IS NULL
THEN
hirank := common.maxint;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
hirank := common.maxint;
END;
curentrank := ROUND (predecessorrank / 2 + hirank / 2);
IF curentrank = predecessorrank
OR hirank = curentrank AND NOT allowsamerank
THEN
BEGIN
SELECT detailtable_disp_partial.item
INTO preditem
FROM detailtable_disp_partial
WHERE detailtable_disp_partial.itemgroup = itemgroupguid
AND detailtable_disp_partial.RANK = predecessorrank;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
preditem := NULL;
END;
redistranksglobally (itemgroupguid => itemgroupguid);
IF preditem IS NULL
THEN
lowrank := 1;
ELSE
SELECT detailtable_disp_partial.RANK
INTO lowrank
FROM detailtable_disp_partial
WHERE detailtable_disp_partial.itemgroup = itemgroupguid
AND detailtable_disp_partial.item = preditem;
END IF;
BEGIN
SELECT MIN (RANK)
INTO hirank
FROM detailtable_disp_partial
WHERE detailtable_disp_partial.RANK > lowrank;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
hirank := common.maxint;
END;
curentrank := ROUND (lowrank / 2 + hirank / 2);
INSERT INTO detailtable_disp_partial
(RANK, item, itemgroup
)
VALUES (curentrank, itemguid, itemgroupguid
);
ELSE
INSERT INTO detailtable_disp_partial
(RANK, item, itemgroup
)
VALUES (curentrank, itemguid, itemgroupguid
);
END IF;
END;
--------------------------------------------------------------------------------
END;
/
prompt Creating package body PERMUTATIONS
CREATE OR REPLACE PACKAGE BODY PERMUTATIONS
IS
--------------------------------------------------------------------------------
PROCEDURE arrangecategoryitems (initems IN rawtable, RESULT OUT NUMBER)
IS
BEGIN
RESULT := arrangecategoryitems (initems => initems);
END;
--------------------------------------------------------------------------------
FUNCTION arrangecategoryitems (initems IN rawtable)
RETURN NUMBER
IS
i INTEGER;
s INTEGER;
aux RAW (16);
r INTEGER;
t INTEGER;
f DOUBLE PRECISION;
items rawtable;
BEGIN
items := initems;
i := 0;
s := 1;
t := items.COUNT;
r := t;
f := 0;
LOOP
s := 1;
i := 0;
LOOP
i := i + 1;
EXIT WHEN i > r;
IF items (i) > items (s)
THEN
s := i;
END IF;
END LOOP;
aux := items (s);
items (s) := items (r);
items (r) := aux;
f := r * f + s - 1;
r := r - 1;
EXIT WHEN r <= 1;
NULL;
END LOOP;
RETURN f;
END;
--------------------------------------------------------------------------------
PROCEDURE reversearrangecategoryitems (
permutation IN NUMBER,
items IN OUT NOCOPY rawtable
)
IS
BEGIN
items := reversearrangecategoryitems (permutation, items);
END;
--------------------------------------------------------------------------------
FUNCTION reversearrangecategoryitems (
permutation IN NUMBER,
items IN rawtable
)
RETURN rawtable
IS
RESULT rawtable;
i NUMBER (38, 0);
s NUMBER (38, 0);
aux RAW (16);
r NUMBER (38, 0);
t NUMBER (38, 0);
f NUMBER (38, 0);
BEGIN
RESULT := items;
i := 0;
s := 1;
t := items.COUNT;
f := permutation;
r := 1;
LOOP
r := r + 1;
EXIT WHEN r > t;
s := MOD (f, r);
f := FLOOR (f / r);
aux := RESULT (s + 1);
RESULT (s + 1) := RESULT (r);
RESULT (r) := aux;
END LOOP;
LOOP
i := i + 1;
EXIT WHEN i > RESULT.COUNT;
END LOOP;
RETURN RESULT;
END;
--------------------------------------------------------------------------------
PROCEDURE updatepermutationforgroup (
itemgroupguid IN RAW,
itemguids IN rawtable
)
IS
newpermutation NUMBER (38, 0);
BEGIN
newpermutation := arrangecategoryitems (initems => itemguids);
UPDATE mastertable_perm
SET mastertable_perm.grouppermutation = newpermutation
WHERE mastertable_perm.itemgroup = itemgroupguid;
END;
--------------------------------------------------------------------------------
PROCEDURE addnewitemingroup (
itemgroupguid IN RAW,
newitemguid IN RAW,
itempredecessor IN RAW
)
IS
CURSOR detailcursor (itemid RAW)
IS
SELECT detailtable_perm.item
FROM detailtable_perm
WHERE detailtable_perm.itemgroup = itemid
ORDER BY detailtable_perm.item ASC;
initialtable rawtable;
curentindex NUMBER (38, 0);
initialpermutation NUMBER (38, 0);
i NUMBER (38, 0);
BEGIN
initialtable := NULL;
curentindex := 0;
IF newitemguid IS NULL
THEN
raise_application_error (-20455, 'No newitemguid was specified');
END IF;
FOR c IN detailcursor (itemgroupguid)
LOOP
IF initialtable IS NULL
THEN
curentindex := 1;
initialtable := rawtable (c.item);
ELSE
initialtable.EXTEND;
curentindex := curentindex + 1;
initialtable (curentindex) := c.item;
END IF;
END LOOP;
SELECT mastertable_perm.grouppermutation
INTO initialpermutation
FROM mastertable_perm
WHERE mastertable_perm.itemgroup = itemgroupguid;
permutations.reversearrangecategoryitems
(permutation => initialpermutation,
items => initialtable
);
i := initialtable.COUNT ();
initialtable.EXTEND ();
LOOP
IF initialtable (i) = itempredecessor
THEN
initialtable (i + 1) := newitemguid;
i := 0;
ELSE
initialtable (i + 1) := initialtable (i);
END IF;
i := i - 1;
IF itempredecessor IS NULL AND i = 0
THEN
initialtable (1) := newitemguid;
END IF;
EXIT WHEN i <= 0;
END LOOP;
INSERT INTO detailtable_perm
(item, itemgroup
)
VALUES (newitemguid, itemgroupguid
);
updatepermutationforgroup (itemgroupguid => itemgroupguid,
itemguids => initialtable
);
END;
--------------------------------------------------------------------------------
FUNCTION retriveitemsorderedforgroup (itemgroupguid IN RAW)
RETURN rawtable
IS
CURSOR detailcursor (itemid RAW)
IS
SELECT detailtable_perm.item
FROM detailtable_perm
WHERE detailtable_perm.itemgroup = itemid
ORDER BY detailtable_perm.item ASC;
initialtable rawtable;
curentindex NUMBER (38, 0);
initialpermutation NUMBER (38, 0);
i NUMBER (38, 0);
BEGIN
initialtable := NULL;
curentindex := 0;
FOR c IN detailcursor (itemgroupguid)
LOOP
IF initialtable IS NULL
THEN
curentindex := 1;
initialtable := rawtable (c.item);
ELSE
initialtable.EXTEND;
curentindex := curentindex + 1;
initialtable (curentindex) := c.item;
END IF;
END LOOP;
SELECT mastertable_perm.grouppermutation
INTO initialpermutation
FROM mastertable_perm
WHERE mastertable_perm.itemgroup = itemgroupguid;
permutations.reversearrangecategoryitems
(permutation => initialpermutation,
items => initialtable
);
RETURN initialtable;
END;
--------------------------------------------------------------------------------
END;
/
prompt Creating package body PERMUTATIONSWITHRAW
CREATE OR REPLACE PACKAGE BODY PERMUTATIONSWITHRAW
IS
--------------------------------------------------------------------------------
PROCEDURE arrangecategoryitems (initems IN rawtable, RESULT OUT RAW)
IS
BEGIN
RESULT := arrangecategoryitems (initems => initems);
END;
--------------------------------------------------------------------------------
FUNCTION arrangecategoryitems (initems IN rawtable)
RETURN RAW
IS
items rawtable;
i INTEGER;
s INTEGER;
aux RAW (2000);
r INTEGER;
t INTEGER;
RESULT RAW (2000);
x PLS_INTEGER;
bitposition PLS_INTEGER;
bitsize PLS_INTEGER;
samesizecount PLS_INTEGER;
samesizeindex PLS_INTEGER;
xasraw RAW (20);
curent PLS_INTEGER;
auxitem RAW (16);
BEGIN
RESULT := UTL_RAW.copies ('00', 2000);
bitposition := 0;
bitsize := 1;
samesizecount := 1;
samesizeindex := 1;
items := initems;
t := items.COUNT;
r := t;
LOOP
s := 1;
i := 0;
LOOP
i := i + 1;
EXIT WHEN i > r;
IF items (i) > items (s)
THEN
s := i;
END IF;
END LOOP;
auxitem := items (s);
items (s) := items (r);
items (r) := auxitem;
x := (s - 1) * POWER (2, (24 - bitsize - (bitposition MOD 8)));
xasraw := UTL_RAW.SUBSTR (UTL_RAW.cast_from_binary_integer (x), 2, 3);
curent := FLOOR (bitposition / 8);
IF curent > 0
THEN
aux := UTL_RAW.copies ('00', curent);
ELSE
aux := NULL;
END IF;
aux := UTL_RAW.CONCAT (aux, xasraw);
RESULT := UTL_RAW.bit_or (RESULT, aux);
bitposition := bitposition + bitsize;
samesizeindex := samesizeindex + 1;
IF (samesizeindex > samesizecount)
THEN
BEGIN
samesizeindex := 1;
samesizecount := samesizecount * 2;
bitsize := bitsize + 1;
END;
END IF;
r := r - 1;
EXIT WHEN r <= 1;
NULL;
END LOOP;
RETURN RESULT;
END;
--------------------------------------------------------------------------------
PROCEDURE reversearrangecategoryitems (
permutation IN RAW,
items IN OUT NOCOPY rawtable
)
IS
BEGIN
items := reversearrangecategoryitems (permutation, items);
END;
--------------------------------------------------------------------------------
FUNCTION reversearrangecategoryitems (permutation IN RAW, items IN rawtable)
RETURN rawtable
IS
RESULT rawtable;
i INTEGER;
s INTEGER;
aux RAW (16);
r INTEGER;
t INTEGER;
x PLS_INTEGER;
bitposition PLS_INTEGER;
bitsize PLS_INTEGER;
samesizecount PLS_INTEGER;
samesizeindex PLS_INTEGER;
xasraw RAW (20);
curent PLS_INTEGER;
MASK RAW (3);
TYPE auxvarray IS VARRAY (32000) OF INTEGER;
cj auxvarray;
BEGIN
bitposition := 0;
bitsize := 1;
samesizecount := 1;
samesizeindex := 1;
cj := NULL;
RESULT := items;
r := 0;
LOOP
r := r + 1;
EXIT WHEN r > items.COUNT - 1;
curent := FLOOR (bitposition / 8);
xasraw := UTL_RAW.SUBSTR (permutation, curent + 1, 3);
MASK :=
CASE bitposition MOD 8
WHEN 0
THEN 'FFFFFF'
WHEN 1
THEN '7FFFFF'
WHEN 2
THEN '3FFFFF'
WHEN 3
THEN '1FFFFF'
WHEN 4
THEN '0FFFFF'
WHEN 5
THEN '07FFFF'
WHEN 6
THEN '03FFFF'
WHEN 7
THEN '01FFFF'
END;
xasraw := UTL_RAW.bit_and (xasraw, MASK);
x := UTL_RAW.cast_to_binary_integer (xasraw);
s := FLOOR (x / POWER (2, (24 - bitsize - (bitposition MOD 8))));
IF cj IS NULL
THEN
cj := auxvarray (s + 1);
ELSE
cj.EXTEND ();
cj (cj.COUNT) := s + 1;
END IF;
bitposition := bitposition + bitsize;
samesizeindex := samesizeindex + 1;
IF (samesizeindex > samesizecount)
THEN
BEGIN
samesizeindex := 1;
samesizecount := samesizecount * 2;
bitsize := bitsize + 1;
END;
END IF;
END LOOP;
r := cj.COUNT () + 1;
LOOP
r := r - 1;
EXIT WHEN r <= 0;
aux := items (cj (r));
RESULT (cj (r)) := RESULT (RESULT.COUNT - r + 1);
RESULT (RESULT.COUNT - r + 1) := aux;
END LOOP;
RETURN RESULT;
END;
--------------------------------------------------------------------------------
PROCEDURE updatepermutationforgroup (
itemgroupguid IN RAW,
itemguids IN rawtable
)
IS
newpermutation RAW (2000);
BEGIN
newpermutation := arrangecategoryitems (initems => itemguids);
UPDATE mastertable_perm_raw
SET mastertable_perm_raw.grouppermutation = newpermutation
WHERE mastertable_perm_raw.itemgroup = itemgroupguid;
END;
--------------------------------------------------------------------------------
PROCEDURE addnewitemingroup (
itemgroupguid IN RAW,
newitemguid IN RAW,
itempredecessor IN RAW
)
IS
CURSOR detailcursor (itemid RAW)
IS
SELECT detailtable_perm_raw.item
FROM detailtable_perm_raw
WHERE detailtable_perm_raw.itemgroup = itemid
ORDER BY detailtable_perm_raw.item ASC;
initialtable rawtable;
curentindex NUMBER (38, 0);
initialpermutation RAW (2000);
i NUMBER (38, 0);
BEGIN
initialtable := NULL;
curentindex := 0;
IF newitemguid IS NULL
THEN
raise_application_error (-20455, 'No newitemguid was specified');
END IF;
FOR c IN detailcursor (itemgroupguid)
LOOP
IF initialtable IS NULL
THEN
curentindex := 1;
initialtable := rawtable (c.item);
ELSE
initialtable.EXTEND;
curentindex := curentindex + 1;
initialtable (curentindex) := c.item;
END IF;
END LOOP;
SELECT mastertable_perm_raw.grouppermutation
INTO initialpermutation
FROM mastertable_perm_raw
WHERE mastertable_perm_raw.itemgroup = itemgroupguid;
permutationswithraw.reversearrangecategoryitems
(permutation => initialpermutation,
items => initialtable
);
i := initialtable.COUNT ();
initialtable.EXTEND ();
LOOP
IF initialtable (i) = itempredecessor
THEN
initialtable (i + 1) := newitemguid;
i := 0;
ELSE
initialtable (i + 1) := initialtable (i);
END IF;
i := i - 1;
IF itempredecessor IS NULL AND i = 0
THEN
initialtable (1) := newitemguid;
END IF;
EXIT WHEN i <= 0;
END LOOP;
INSERT INTO detailtable_perm_raw
(item, itemgroup
)
VALUES (newitemguid, itemgroupguid
);
updatepermutationforgroup (itemgroupguid => itemgroupguid,
itemguids => initialtable
);
END;
--------------------------------------------------------------------------------
FUNCTION retriveitemsorderedforgroup (itemgroupguid IN RAW)
RETURN rawtable
IS
CURSOR detailcursor (itemid RAW)
IS
SELECT detailtable_perm_raw.item
FROM detailtable_perm_raw
WHERE detailtable_perm_raw.itemgroup = itemid
ORDER BY detailtable_perm_raw.item ASC;
initialtable rawtable;
curentindex NUMBER (38, 0);
initialpermutation RAW (2000);
i NUMBER (38, 0);
BEGIN
initialtable := NULL;
curentindex := 0;
FOR c IN detailcursor (itemgroupguid)
LOOP
IF initialtable IS NULL
THEN
curentindex := 1;
initialtable := rawtable (c.item);
ELSE
initialtable.EXTEND;
curentindex := curentindex + 1;
initialtable (curentindex) := c.item;
END IF;
END LOOP;
SELECT mastertable_perm_raw.grouppermutation
INTO initialpermutation
FROM mastertable_perm_raw
WHERE mastertable_perm_raw.itemgroup = itemgroupguid;
permutationswithraw.reversearrangecategoryitems
(permutation => initialpermutation,
items => initialtable
);
RETURN initialtable;
END;
--------------------------------------------------------------------------------
END;
/
prompt Creating package body TOTALORDER
CREATE OR REPLACE PACKAGE BODY TOTALORDER
IS
CURSOR ranks (groupid RAW)
IS
SELECT RANK, item, ROWNUM currentindex
FROM detailtable_disp_total t
WHERE t.itemgroup = groupid
ORDER BY t.RANK ASC
FOR UPDATE OF t.RANK;
--------------------------------------------------------------------------------
PROCEDURE redistranksglobally (itemgroupguid IN RAW)
IS
n NUMBER (38, 0);
f NUMBER (38, 0);
ceils NUMBER (38, 0);
newrank NUMBER (38, 0);
BEGIN
SELECT COUNT (1)
INTO n
FROM detailtable_disp_total
WHERE detailtable_disp_total.itemgroup = itemgroupguid;
IF n > 0
THEN
f := FLOOR (common.maxint / (n + 1));
ceils := common.maxint - f * (n + 1);
newrank := 0;
FOR r IN ranks (itemgroupguid)
LOOP
newrank := newrank + f;
IF ceils > 0
THEN
newrank := newrank + 1;
ceils := ceils - 1;
END IF;
UPDATE detailtable_disp_total
SET detailtable_disp_total.RANK = newrank
WHERE CURRENT OF ranks;
END LOOP;
END IF;
END;
--------------------------------------------------------------------------------
PROCEDURE additemtogroup (
itemguid IN RAW,
itemgroupguid IN RAW,
predecessorrank IN NUMBER
)
IS
aux NUMBER (38, 0);
hirank NUMBER (38, 0);
lowrank NUMBER (38, 0);
currentrank NUMBER (38, 0);
preditem RAW (16);
localpredrank NUMBER (38, 0);
BEGIN
SELECT COUNT (1)
INTO aux
FROM detailtable_disp_total
WHERE detailtable_disp_total.itemgroup = itemgroupguid;
IF aux = 0
THEN
currentrank := ROUND (common.maxint / 2);
ELSE
BEGIN
SELECT MAX (RANK)
INTO localpredrank
FROM detailtable_disp_total
WHERE detailtable_disp_total.itemgroup = itemgroupguid
AND RANK <= predecessorrank;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
localpredrank := NULL;
END;
-- 1 <= localPredRank <= maxint or localPredRank is null
IF localpredrank IS NULL
THEN
SELECT MIN (RANK)
INTO localpredrank
FROM detailtable_disp_total
WHERE detailtable_disp_total.itemgroup = itemgroupguid;
-- localPredRank is not null any longer
IF localpredrank = 1
THEN
currentrank := NULL;
ELSE
currentrank := ROUND (localpredrank / 2);
END IF;
ELSE
-- there exists 1 <= localPredRank <= maxint
BEGIN
SELECT MIN (RANK)
INTO hirank
FROM detailtable_disp_total
WHERE detailtable_disp_total.itemgroup = itemgroupguid
AND detailtable_disp_total.RANK > localpredrank;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
hirank := NULL;
END;
-- hirank is null or hirank > localpredrank
IF hirank IS NULL
THEN
-- localPredRank is already the greatest rank
IF localpredrank = common.maxint
THEN
currentrank := NULL;
ELSE
currentrank :=
localpredrank
+ ROUND ((common.maxint - localpredrank) / 2);
END IF;
ELSE
-- we need to insert between predrank and hirank
IF hirank - localpredrank = 1
THEN
currentrank := NULL;
ELSE
currentrank :=
localpredrank + ROUND ((hirank - localpredrank) / 2);
END IF;
END IF;
END IF;
END IF;
IF currentrank IS NULL
THEN
SELECT detailtable_disp_total.item
INTO preditem
FROM detailtable_disp_total
WHERE detailtable_disp_total.itemgroup = itemgroupguid
AND detailtable_disp_total.RANK = localpredrank;
redistranksglobally (itemgroupguid => itemgroupguid);
SELECT detailtable_disp_total.RANK
INTO lowrank
FROM detailtable_disp_total
WHERE detailtable_disp_total.itemgroup = itemgroupguid
AND detailtable_disp_total.item = preditem;
SELECT detailtable_disp_total.RANK
INTO lowrank
FROM detailtable_disp_total
WHERE detailtable_disp_total.itemgroup = itemgroupguid
AND detailtable_disp_total.item = preditem;
BEGIN
SELECT MIN (RANK)
INTO hirank
FROM detailtable_disp_total
WHERE detailtable_disp_total.RANK > lowrank;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
hirank := common.maxint;
END;
currentrank := lowrank + ROUND ((hirank - lowrank) / 2);
IF currentrank = lowrank OR currentrank = hirank
THEN
raise_application_error (-20101, 'too many items');
END IF;
END IF;
INSERT INTO detailtable_disp_total
(RANK, item, itemgroup
)
VALUES (currentrank, itemguid, itemgroupguid
);
END;
--------------------------------------------------------------------------------
END;
/
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
PROMPT Loading test data;
PROMPT Loading MasterTable
INSERT INTO mastertable
VALUES ('2D3D12F50BB64845A582CC65FB7D0228');
INSERT INTO detailtable_disp_partial
(RANK, item,
itemgroup
)
VALUES (3, '1D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_partial
(RANK, item,
itemgroup
)
VALUES (5, '2D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_partial
(RANK, item,
itemgroup
)
VALUES (7, '3D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_partial
(RANK, item,
itemgroup
)
VALUES (7, '4D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_partial
(RANK, item,
itemgroup
)
VALUES (7, '5D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_partial
(RANK, item,
itemgroup
)
VALUES (10, '6D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_partial
(RANK, item,
itemgroup
)
VALUES (11, '7D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_partial
(RANK, item,
itemgroup
)
VALUES (14, '8D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
---
INSERT INTO detailtable_disp_total
(RANK, item,
itemgroup
)
VALUES (3, '1D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_total
(RANK, item,
itemgroup
)
VALUES (5, '2D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_total
(RANK, item,
itemgroup
)
VALUES (7, '3D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_total
(RANK, item,
itemgroup
)
VALUES (8, '4D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_total
(RANK, item,
itemgroup
)
VALUES (9, '5D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_total
(RANK, item,
itemgroup
)
VALUES (10, '6D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_total
(RANK, item,
itemgroup
)
VALUES (11, '7D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_disp_total
(RANK, item,
itemgroup
)
VALUES (14, '8D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
---
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (1, NULL, '1D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (2, 1, '2D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (3, 2, '3D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (4, 3, '4D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (5, 4, '5D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (6, 5, '6D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (7, 6, '7D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO detailtable_consecutive
(RANK, rankminusone, item,
itemgroup
)
VALUES (8, 7, '8D3D12F50BB64845A582CC65FB7D0228',
'2D3D12F50BB64845A582CC65FB7D0228'
);
COMMIT ;
PROMPT Loading Master Tables for permutations;
INSERT INTO mastertable_perm
(itemgroup
)
VALUES ('3D3D12F50BB64845A582CC65FB7D0228'
);
INSERT INTO mastertable_perm_raw
(itemgroup
)
VALUES ('4D3D12F50BB64845A582CC65FB7D0228'
);
COMMIT ;
PROMPT Loading Detail Tables for permutations and testing.. ;
DECLARE
a rawtable;
perm1 rawtable;
perm2 rawtable;
i INTEGER;
BEGIN
-- Please note the order (F,E,D,C,B,A,9,8)
a :=
rawtable ('FD3D12F50BB64845A582CC65FB7D0228',
'ED3D12F50BB64845A582CC65FB7D0228',
'DD3D12F50BB64845A582CC65FB7D0228',
'CD3D12F50BB64845A582CC65FB7D0228',
'BD3D12F50BB64845A582CC65FB7D0228',
'AD3D12F50BB64845A582CC65FB7D0228',
'9D3D12F50BB64845A582CC65FB7D0228',
'8D3D12F50BB64845A582CC65FB7D0228'
);
FORALL i IN 1 .. a.COUNT
INSERT INTO detailtable_perm
(itemgroup, item
)
VALUES ('3D3D12F50BB64845A582CC65FB7D0228', a (i)
);
permutations.updatepermutationforgroup
(itemgroupguid => '3D3D12F50BB64845A582CC65FB7D0228',
itemguids => a
);
-- Add another value
permutations.addnewitemingroup
(itemgroupguid => '3D3D12F50BB64845A582CC65FB7D0228',
newitemguid => '7D3D12F50BB64845A582CC65FB7D0228',
itempredecessor => '8D3D12F50BB64845A582CC65FB7D0228'
);
FORALL i IN 1 .. a.COUNT
INSERT INTO detailtable_perm_raw
(itemgroup, item
)
VALUES ('4D3D12F50BB64845A582CC65FB7D0228', a (i)
);
permutationswithraw.updatepermutationforgroup
(itemgroupguid => '4D3D12F50BB64845A582CC65FB7D0228',
itemguids => a
);
-- Add another value
permutationswithraw.addnewitemingroup
(itemgroupguid => '4D3D12F50BB64845A582CC65FB7D0228',
newitemguid => '7D3D12F50BB64845A582CC65FB7D0228',
itempredecessor => '8D3D12F50BB64845A582CC65FB7D0228'
);
a.EXTEND ();
a (9) := '7D3D12F50BB64845A582CC65FB7D0228';
-- Test the order..
perm1 :=
permutations.retriveitemsorderedforgroup
(itemgroupguid => '3D3D12F50BB64845A582CC65FB7D0228');
perm2 :=
permutationswithraw.retriveitemsorderedforgroup
(itemgroupguid => '4D3D12F50BB64845A582CC65FB7D0228');
DBMS_OUTPUT.put_line ('Size of initial table :' || a.COUNT);
DBMS_OUTPUT.put_line ('Size of perm1 table :' || perm1.COUNT);
DBMS_OUTPUT.put_line ('Size of perm2 table :' || perm2.COUNT);
DBMS_OUTPUT.put_line
('LINE Initial table Perm1 Table Perm2 Table '
);
i := 1;
WHILE i <= a.COUNT
LOOP
DBMS_OUTPUT.put_line ( i
|| ' '
|| a (i)
|| ' '
|| perm1 (i)
|| ' '
|| perm2 (i)
);
i := i + 1;
END LOOP;
END;
/
COMMIT ;
SPOOL off;
PROMPT Loading Done..
Submit an article or tip