2015.1.15 using Oracle functions to return table results significant technical progress!

--SQL Call SELECT * FROM table (Get_airway_subpoint (x,x,x))

/////////////////////////////////////////////////////////////////////

Method One, with the cursor implementation, namely provided, there is no subsequent scenario two good, can not use the cursor, more simple Scenario 2

//////////////////////////////////////////////////////////////////////

Create or Replace function Subairway (pid1 in number,

Awid in number,

Pid2 in number) return TAB_PNT is

-- function function is to return Awid all waypoints between the two-point route .

Type mycursor is REF CURSOR; -- Defining cursor Types

Dr Row_pnt: = row_pnt (0, 0, NULL); --type Row Type each point record Create or Replace type Row_pnt is Object (SRT number (5), PID number (one), PNM varchar2 ())

DT tab_pnt:= TAB_PNT (); --type table type, return route all points Create or Replace type TAB_PNT as table of Row_pnt

DTT tab_pnt:= tab_pnt (); -- results table, all table records between two points

-- table types and row types must be initialized first, or compile well, but run with a hint of error

CUR_PNT MyCursor; -- Cursors

N1 number (5); -- number of two points in the direction of the route

N2 number (5);

TM number (5);

Begin

Open CUR_PNT for - cursors can be viewed as a table of results

Select ST,PID,PNM from (

Select F1.code_sort St,f2.airway_point1 pid,f3.txt_name | | Decode (F3.tab, ' DES ', ' ', F3.tab) PNM

From Rte_seg F1, segment F2, Airway_point F3

where f1.segment_id = f2.segment_id

and f2.airway_point1 = f3.significant_point_id

and f1.en_route_rte_id = Awid

Union

Select F1.code_sort + 1st, F2.airway_point2 pid,f3.txt_name | | Decode (F3.tab, ' DES ', ' ', F3.tab) PNM

From Rte_seg F1, segment F2, Airway_point F3

where f1.segment_id = f2.segment_id

and F2.airway_point2 = f3.significant_point_id

and F1.code_sort = (select Max (code_sort) from rte_seg where rte_seg.en_route_rte_id = f1.en_route_rte_id)

and f1.en_route_rte_id = Awid

ORDER BY St

);

If Cur_pnt%notfound then - returns an empty table if no data is in the cursor

RETURN DT;

END IF;

Loop

FETCH CUR_PNT

Into DR.SRT, Dr.pid, DR.PNM;

EXIT when Cur_pnt%notfound; -- the end of the cursor is reached, the row position cannot be placed last, or a row is repeated

Dt.extend (); --dt Add a new row

DT (Dt.count ()): = Dr; --dt last line =DR

END LOOP;

If pid1=0 or pid2=0 then -- if the input variable First point ID or end point ID is 0 return all routes to the direction

return DT;

End If;

N1:=-1; -- The initial value is used to determine when the input point is found on the route.

N2:=-1;

For V in 1..dt.count () loop -- from the first line to The last row of the DT table

If pid1 = DT (v). PID then N1:=dt (v). SRT; -- Find N1 n2 corresponding position

elsif pid2 = dt (v). PID then N2:=dt (v). SRT;

End If;

End Loop;

If N1=-1 or N2=-1 then -- at least one point does not match, returns an empty table

return DTT;

End If;

If N1<n2 then - point 1 sequence number less than point 2 ordinal, normal cycle

For V in N1. N2 Loop

If DT (v). SRT >= N1 and DT (v). Srt<=n2 then --dt (v). SRT accesses row structure, field structure from table structure

Dtt.extend ();

DTT (Dtt.count ()): = DT (v);

End If;

End Loop;

End If;

If N1>n2 then -- if n1>n2 description p1,p2 order reversed

TM:=N1;

While TM >= N2 LOOP

Begin

Dtt.extend ();

DTT (Dtt.count ()): = DT (tm);

Tm:= tm-1;

End

End LOOP;

End If;

return DTT;

End

////////////////////////////////////////////////////////////////

Scenario 2 No cursor key is for the myrow in (select ...) loop function The remainder is the same as scenario one

////////////////////////////////////////////////////////////////

Create or Replace function Subairway (pid1 in number,

Awid in number,

Pid2 in number,

Ishis in number default 0) return TAB_PNT is

function function is to return all the waypoints between the two points of the Awid route, using this function to obtain all the points between two points of a route, can not be sorted by SRT, otherwise you can always get the order of the original route sequence, instead of the point string by the point to the out point sequence.

Dr Row_pnt: = row_pnt (0, 0, NULL); --type row Type each point record create or replace type Row_pnt is Object (SRT number (5), PID number (one), PNM VARCHAR2 (50))

DT tab_pnt:= TAB_PNT (); --type table type, return route all points create or replace type TAB_PNT as table of ROW_PNT

DTT tab_pnt:= tab_pnt (); --Results table, all table records between two points

--The table type and row type must be initialized first, otherwise the compilation is good but the run will prompt the error

N1 number (5); --Number of two points in the direction of the route

N2 number (5);

TM number (5);

Begin

If Ishis=1 then--2016.9.7 originally wrote the temporary route actually can't find, rewrite

For Myrow in

(Select Sort St, airway_point_id pid, Airway_point_name PNM from his_airway_pnts

where Airway_id=awid

Order by sort

)

Loop

DR:=ROW_PNT (MYROW.ST,MYROW.PID,MYROW.PNM);

Dt.extend (); --dt Add a new row

DT (Dt.count ()): = Dr; --dt last line =dr

END LOOP;

Else

For Myrow in

(

Select ST,PID,PNM from (

Select F1.code_sort St,f2.airway_point1 pid,f3.txt_name | | Decode (F3.tab, ' DES ', ' ', F3.tab) PNM

From Rte_seg F1, segment F2, Airway_point F3

where f1.segment_id = f2.segment_id

and f2.airway_point1 = f3.significant_point_id

and f1.en_route_rte_id = Awid

Union

Select F1.code_sort + 1st, F2.airway_point2 pid,f3.txt_name | | Decode (F3.tab, ' DES ', ' ', F3.tab) PNM

From Rte_seg F1, segment F2, Airway_point F3

where f1.segment_id = f2.segment_id

and F2.airway_point2 = f3.significant_point_id

and F1.code_sort = (select Max (code_sort) from rte_seg where rte_seg.en_route_rte_id = f1.en_route_rte_id)

and f1.en_route_rte_id = Awid

ORDER BY St

)

)

Loop

DR:=ROW_PNT (MYROW.ST,MYROW.PID,MYROW.PNM);

Dt.extend (); --dt Add a new row

DT (Dt.count ()): = Dr; --dt last line =dr

END LOOP;

End If;

If pid1=0 or pid2=0 then--if the input variable first-point ID or end-point ID is 0, returns all route trends

return DT;

End If;

N1:=-1; --initial value, used to determine when to find the input point on the route

N2:=-1;

For V in 1..dt.count () loop--from the first line to the last row of the DT table

If pid1 = DT (v). PID then N1:=dt (v). SRT; --Find N1 n2 corresponding position

elsif pid2 = dt (v). PID then N2:=dt (v). SRT;

End If;

End Loop;

If N1=-1 or N2=-1 then--at least one point does not match, returns an empty table

return DTT;

End If;

If N1<n2 then-point 1 sequence number less than point 2 ordinal, normal cycle

For V in N1. N2 Loop

If DT (v). SRT >= N1 and DT (v). Srt<=n2 then--dt (v). SRT accesses row structure, field structure from table structure

Dtt.extend ();

DTT (Dtt.count ()): = DT (v);

End If;

End Loop;

End If;

If N1>n2 then--If N1>N2 description p1,p2 order reversed

TM:=N1;

While TM >= N2 LOOP

Begin

Dtt.extend ();

DTT (Dtt.count ()): = DT (tm);

DTT (Dtt.count ()). Srt:=n1-tm+1; --2016.9.7 in reverse order, to allow the sequence number to reflect the true small to large order, rewrite each point ordinal

Tm:= tm-1;

End

End LOOP;

End If;

return DTT;

End

2015.1.15 using Oracle functions to return table results significant technical progress!