Range function

From: Yechiel Adar <adar666_at_inter.net.il>
Date: Fri, 17 Mar 2006 11:59:05 +0200
Message-ID: <441A8869.8000209@inter.net.il>

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__at_mehish.co.il <mailto:adary_at_mehish.co.il> adar666_at_inter,net.il <mailto:adar666_at_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);

         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

END RANGE ;
/

Adar Yechiel
Rechovot, Israel

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 17 2006 - 03:59:05 CST