Home » SQL & PL/SQL » SQL & PL/SQL » Better PL/SQL
Better PL/SQL [message #231502] Tue, 17 April 2007 05:40 Go to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I wonder if someone could point me in the right direction. I'm trying to write a piece of code that will allow me to display the current sequences in use plus the range of numbers that exist between the sequences (so they can't be used). I'm on Oracle version 9.2.0.7.0.

I have a basic query like this

select sequence_name, min_value,  max_value, last_number
   from dba_sequences
   where sequence_name like 'POLICY_NO_%%_1'
   and sequence_name != 'POLICY_NO_QQ_1'
   order by min_value;


which gives me this (the line numbers are generated by PL/SQL Developer so please ignore)

1	POLICY_NO_HC_1	800001	899999	841154
2	POLICY_NO_CA_1	1300000	13899999	1407913
3	POLICY_NO_CS_1	16000000	16999999	16056098
4	POLICY_NO_MO_1	41300000	41999999	41756578
5	POLICY_NO_HS_1	102500000	102999999	102608113
6	POLICY_NO_HB_1	201500000	299999999	202236034
7	POLICY_NO_PA_1	300005000	399999999	300445595
8	POLICY_NO_SA_1	420000000	429999999	420017000
9	POLICY_NO_HV_1	501000000	501999999	501105858
10	POLICY_NO_HR_1	502000000	503999999	503223761
11	POLICY_NO_HH_1	504000000	579999999	518232823


It actually returns 25 rows but you get the idea.

I'm trying to write something that gets the maximum value of the first row and the minimum value of the second row and so on - then I can construct a query that looks up any numbers between that range that can't be reused.

I've made a start and come up with
declare

cursor c1 is
select sequence_name, min_value,  max_value, last_number
from dba_sequences
where sequence_name like 'POLICY_NO_%%_1'
and sequence_name != 'POLICY_NO_QQ_1'
order by min_value;

TYPE max_num IS TABLE OF number(10) INDEX BY BINARY_INTEGER;
   num_1 max_num;
TYPE min_num IS TABLE OF number(10) INDEX BY BINARY_INTEGER;
   num_2 min_num;

v_counter number(4);

begin

v_counter := 1;

for c_rec in c1
loop
   num_1(v_counter) := c_rec.max_value;
   v_counter := v_counter + 1; 
end loop;

v_counter := 0;

for c_rec in c1
loop
   if v_counter > 0 then
      num_2(v_counter) := c_rec.min_value;
   end if;
   v_counter := v_counter + 1;
end loop;

for i in 1 .. v_counter - 1
loop
   dbms_output.put_line(num_1(i) || ' ' || num_2(i));
end loop;

end;

------------------------------------------------------------
returns

899999 1300000
13899999 16000000
16999999 41300000
41999999 102500000
102999999 201500000
299999999 300005000
399999999 420000000
429999999 501000000
501999999 502000000
503999999 504000000


This is the correct data but the method seems somewhat cumbersome.
I then want to use this data in a query that does

select 'Not Available', min_policy_no, max(policy_no)
from policy_ent
where policy_no between 899999 and 1300000


I want to achieve

1	POLICY_NO_HC_1	800001	 899999	        841154
        Not Available   900001   950000
2	POLICY_NO_CA_1	1300000	 13899999	1407913
        Not Available   1500001  15500000
3	POLICY_NO_CS_1	16000000 16999999	16056098


by doing a UNION and an order by (I know there are a different number of columns at the moment but I'll come to that).

I can probably achieve this OK by continuing with my current method but I thought I'd find out if there is an alternative way I could do it. I'm a recent convert from 7.3 Embarassed so I'm still finding out about new functionality and would appreciate being pointed in the right direction. Thumbs Up
Re: Better PL/SQL [message #231536 is a reply to message #231502] Tue, 17 April 2007 07:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You sound quite capable, so I won't spoonfeed the solution to you - I suspect you will enjoy the voyage of discovery.

A whole new suite of functions were introduced in 8i called Analytic functions. They allow you to access details of neighboring rows in a SQL.

Specifically, the LAG function lets you look at the previous row, and LEAD() lets you look at the next row.

Try this:
select sequence_name, min_value,  max_value, last_number
, lead(min_value) OVER (ORDER BY min_value) AS next_min
   from dba_sequences
   where sequence_name like 'POLICY_NO_%%_1'
   and sequence_name != 'POLICY_NO_QQ_1'
   order by min_value;


LEAD() is included in the documentation.

Ross Leishman
Re: Better PL/SQL [message #231538 is a reply to message #231502] Tue, 17 April 2007 07:34 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
That's brilliant, thanks.

You're right - working it all out it so much better than being spoonfed - I just needed to know what to look at.

Off to do some coding Laughing

Previous Topic: SQL Tuning Made Easy in 10g
Next Topic: SQL Query
Goto Forum:
  


Current Time: Tue Dec 06 00:26:12 CST 2016

Total time taken to generate the page: 0.05042 seconds