Range function
- From: Yechiel Adar <adar666@xxxxxxxxxxxx>
- Date: Fri, 17 Mar 2006 11:59:05 +0200
I got several requests for the function so I decided to post it.
I do not remember what ts_ranges does, probably a copy for tests.
This was written years ago so there must be room for improvements.
It does mostly comparison so compiling it will bring great benefit.
The function gets 2 parameters:
1) ranges list. from1_to1,from2_to2,.....
2) Value to check.
For example: select Range('0-100,101-200,201-300',balance) from account;
will divide the records to negative balance, one of the ranges or more
then 300.
I am keeping the last 2 range lists in memory to avoid parsing of the
range list each time.
As you can see you can use and copy it, but please retain the copyright.
If you make any enhancements please tell me so I can enhance my copy.
USE IT ON YOUR OWN RISK.
CREATE OR REPLACE PACKAGE "RANGE" AS
--
-- Developed by: Yechiel Adar, Mehish Computer services.
--
-- E-mail: _adary_@xxxxxxxxxxxx <mailto:adary@xxxxxxxxxxxx>
adar666@inter,net.il <mailto:adar666@inter,net.il>
-- All Rights Reserved.
-- You can use, give away and modify this package freely,
-- just retain the credits.
-- Send any modifications and changes back to me so I can enhance my copy.
--
TYPE R_vec_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
current_ptr NUMBER DEFAULT 0;
R_low R_vec_type;
R_high R_vec_type;
no_of_ranges NUMBER DEFAULT 0;
SAVE_PARAM VARCHAR2(300) DEFAULT NULL;
R_low1 R_vec_type;
R_high1 R_vec_type;
no_of_ranges1 NUMBER DEFAULT 0;
SAVE_PARAM1 VARCHAR2(300) DEFAULT NULL;
R_low2 R_vec_type;
R_high2 R_vec_type;
no_of_ranges2 NUMBER DEFAULT 0;
SAVE_PARAM2 VARCHAR2(300) DEFAULT NULL;
FUNCTION RANGES(RANGE_LIST IN VARCHAR2 , FORMULA_V IN NUMBER) RETURN
VARCHAR2 PARALLEL_ENABLE;
FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2 , FORMULA_V IN NUMBER)
RETURN VARCHAR2 PARALLEL_ENABLE;
PRAGMA RESTRICT_REFERENCES (RANGES, WNDS, RNDS);
END RANGE;
/
CREATE OR REPLACE PACKAGE BODY RANGE IS
-- PRAGMA SERIALLY_REUSABLE;
FUNCTION RANGES(RANGE_LIST IN VARCHAR2 ,FORMULA_V IN NUMBER) RETURN
VARCHAR2
IS
list_p VARCHAR2(300);
obligo_p NUMBER;
len NUMBER;
s1 VARCHAR2(300);
BEGIN
IF SAVE_PARAM = RANGE_LIST THEN
CURRENT_PTR := current_ptr;
ELSIF SAVE_PARAM1 = RANGE_LIST THEN
SAVE_PARAM := SAVE_PARAM1;
FOR I IN 1..NO_OF_RANGES1 LOOP
R_LOW(I) := R_LOW1(I);
R_HIGH(I) := R_HIGH1(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES1;
ELSIF SAVE_PARAM2 = RANGE_LIST THEN
-- dbms_output.put_line('2 ' || current_ptR);
SAVE_PARAM := SAVE_PARAM2;
FOR I IN 1..NO_OF_RANGES2 LOOP
R_LOW(I) := R_LOW2(I);
R_HIGH(I) := R_HIGH2(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES2;
ELSE
-- dbms_output.put_line('1 ' || CUrrent_ptR);
SAVE_PARAM := range_list;
list_p := range_list;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW(I) := NULL;
R_HIGH(I) := NULL;
END LOOP;
NO_OF_RANGES := 0;
FOR i IN 1..LENGTH(list_p) LOOP
len := INSTR(list_p,',');
IF len IS NULL THEN
EXIT;
END IF;
no_of_ranges := no_of_ranges + 1;
s1 := SUBSTR(list_p,1,len);
list_p := SUBSTR(list_p,len+1);
len := INSTR(s1,'_');
R_low(i) := SUBSTR(s1,1,len-1);
R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);
END LOOP;
IF SAVE_PARAM1 = NULL OR CURRENT_PTR = 2 THEN
SAVE_PARAM1 := SAVE_PARAM;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW1(I) := R_LOW(I);
R_HIGH1(I) := R_HIGH(I);
END LOOP;
NO_OF_RANGES1:= NO_OF_RANGES;
CURRENT_PTR := 1;
ELSE
SAVE_PARAM2 := SAVE_PARAM;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW2(I) := R_LOW(I);
R_HIGH2(I) := R_HIGH(I);
END LOOP;
NO_OF_RANGES2 := NO_OF_RANGES;
CURRENT_PTR := 2;
END IF;
END IF;
-- dbms_output.put_line(current_ptr || save_param);
obligo_p:=FORMULA_V;
IF obligo_p < R_low(1) THEN
RETURN ' 0 ) <' ||R_low(1);
END IF;
FOR j IN 1..no_of_ranges LOOP
IF obligo_p BETWEEN R_LOW(j) AND R_high(j) THEN
IF j < 10 THEN
RETURN ' '||j||' ) '||R_low(j)||'-'||R_high(j);
ELSE
RETURN j||' ) '||R_low(j)||'-'||R_high(j);
END IF;
END IF;
END LOOP;
IF obligo_p > R_high(no_of_ranges) THEN
IF no_of_ranges+1 < 9 THEN
RETURN ' '||no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
ELSE
RETURN no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
END IF;
END IF;
RETURN obligo_p || ' - NOT IN RANGE !!';
END RANGES;
FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2 ,FORMULA_V IN NUMBER) RETURN
VARCHAR2
IS
list_p VARCHAR2(300);
obligo_p NUMBER;
len NUMBER;
s1 VARCHAR2(300);
BEGIN
IF SAVE_PARAM = RANGE_LIST THEN
CURRENT_PTR := current_ptr;
ELSIF SAVE_PARAM1 = RANGE_LIST THEN
SAVE_PARAM := SAVE_PARAM1;
FOR I IN 1..NO_OF_RANGES1 LOOP
R_LOW(I) := R_LOW1(I);
R_HIGH(I) := R_HIGH1(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES1;
ELSIF SAVE_PARAM2 = RANGE_LIST THEN
-- dbms_output.put_line('2 ' || current_ptR);
SAVE_PARAM := SAVE_PARAM2;
FOR I IN 1..NO_OF_RANGES2 LOOP
R_LOW(I) := R_LOW2(I);
R_HIGH(I) := R_HIGH2(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES2;
ELSE
-- dbms_output.put_line('1 ' || CUrrent_ptR);
SAVE_PARAM := range_list;
list_p := range_list;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW(I) := NULL;
R_HIGH(I) := NULL;
END LOOP;
NO_OF_RANGES := 0;
FOR i IN 1..LENGTH(list_p) LOOP
len := INSTR(list_p,',');
IF len IS NULL THEN
EXIT;
END IF;
no_of_ranges := no_of_ranges + 1;
s1 := SUBSTR(list_p,1,len);
list_p := SUBSTR(list_p,len+1);
len := INSTR(s1,'_');
R_low(i) := SUBSTR(s1,1,len-1);
R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);
END LOOP;
IF SAVE_PARAM1 = NULL OR CURRENT_PTR = 2 THEN
SAVE_PARAM1 := SAVE_PARAM;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW1(I) := R_LOW(I);
R_HIGH1(I) := R_HIGH(I);
END LOOP;
NO_OF_RANGES1:= NO_OF_RANGES;
CURRENT_PTR := 1;
ELSE
SAVE_PARAM2 := SAVE_PARAM;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW2(I) := R_LOW(I);
R_HIGH2(I) := R_HIGH(I);
END LOOP;
NO_OF_RANGES2 := NO_OF_RANGES;
CURRENT_PTR := 2;
END IF;
END IF;
-- dbms_output.put_line(current_ptr || save_param);
obligo_p:=FORMULA_V;
IF obligo_p < R_low(1) THEN
RETURN ' 0 ) <' ||R_low(1);
END IF;
FOR j IN 1..no_of_ranges LOOP
IF obligo_p BETWEEN R_LOW(j) AND R_high(j) THEN
IF j < 10 THEN
RETURN ' '||j||' ) '||R_low(j)||'-'||R_high(j);
ELSE
RETURN j||' ) '||R_low(j)||'-'||R_high(j);
END IF;
END IF;
END LOOP;
IF obligo_p > R_high(no_of_ranges) THEN
IF no_of_ranges+1 < 9 THEN
RETURN ' '||no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
ELSE
RETURN no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
END IF;
END IF;
RETURN obligo_p || ' - NOT IN RANGE !!';
END ts_RANGES;
END RANGE ;
/
Adar Yechiel
Rechovot, Israel
--
//www.freelists.org/webpage/oracle-l
Other related posts:
