用start with.... connect by 语法我不记得了,查下帮助哪...大概是count(*) from tb_busname start with id=8602 connect by prior_stop = prior id..
to smartbigcat: 我试验了你的方法,但是查询返回的是表中全部记录的行数,我若想查询8602到8604的行数就行不通了。
create procedure name_pro(bg_id in varchar2,ed_id in varchar2,count out number) as cursor t_sor(v_bgid varchar2,v_edid varchar2) is select id from tb_busname where id=v_bgid; begin count:=0; for v_sor in t_sor(bg_id,ed_id) loop count:=count+1; if v_sor.id=ed_id then exit; end if; end loop; end; /
还是大猫的方法: SQL> create table tb_busname 2 ( id varchar2(10) , 3 name varchar2(10) , 4 stop varchar2(10) , 5 prior_stop varchar2(10) , 6 next_stop varchar2(10));Table created. SQL> insert into tb_busname values ('8601', '86', 'stop1', '0','8602') ;1 row created.SQL> insert into tb_busname values ('8602', '86', 'stop2', '8601', '8603') ;1 row created.SQL> insert into tb_busname values ('8603', '86' , 'stop3', '8602', '8632') ;1 row created.SQL> insert into tb_busname values ('8604', '86', 'stop4', '8632', '8605') ;1 row created.SQL> insert into tb_busname values ('8605' , '86' , 'stop5' , '8604' , '1') ;1 row created.SQL> insert into tb_busname values ('8632' , '86' , 'stop6' , '8603' , '8604') ;1 row created.SQL> commit;Commit complete.SQL> select count(*) from tb_busname start with id='8602' connect by prior_stop != '8604' and p rior next_stop = id ; COUNT(*) ---------- 4
以下是我编写的存储函数,i_begin_id和i_end_id为已经确定的存储起始站点和终点站点的id号的变量 tb_count1为新建的一个用于统计站数的表,包含三列,结构为: bus_id number(5), prior_stop number(5), next_stop number(5) --编写用于计算乘车经过站数的存储函数 CREATE OR REPLACE FUNCTION func_checksamecount( i_begin_id IN tb_addressname.bus_id%TYPE, i_end_id IN tb_addressname.bus_id%TYPE) RETURN NUMBER AS v_count number(5) :=0; i_input_id tb_begin_temp.bus_id%type := i_begin_id; i_next_s tb_begin_temp.next_stop%type :=0; i_bus_name tb_begin_temp.bus_name%type; init_id tb_begin_temp.bus_id%type :=0; init_prior_stop tb_begin_temp.prior_stop%type :=0; init_next_stop tb_begin_temp.next_stop%type := 0;CURSOR cur_initcount IS select bus_id,prior_stop,next_stop from tb_busname where bus_id = i_input_id;BEGIN delete from tb_count1;OPEN cur_initcount; loop fetch cur_initcount into init_id,init_prior_stop,init_next_stop; exit when cur_initcount%notfound; insert into tb_count1 values(init_id,init_prior_stop,init_next_stop); if i_input_id = i_end_id then exit; else i_input_id := init_next_stop; end if; end loop; close cur_initcount;select count(*) into v_count from tb_count1;RETURN v_count; END func_checksamecount; 我编写了一个pl/sql块用于测试它, declare a number(5); begin a := func_checksamecount(8602,8604); DBMS_OUTPUT.PUT_LINE(TO_CHAR(a)); end; 但是结果为:1 而正确结果为:3 请大家帮忙改正。
create procedure name_pro(bg_id in varchar2,ed_id in varchar2,count out number) as cursor t_sor is select id,next_stop from tb_busname; i number:=0; begin count:=0; for v_sor in t_sor loop if v_sor.id=bg_id then i:=1; end if; count:=count+i; if v_sor.next_stop=ed_id then count:=count+1; exit;--退出循环 end if; end loop; end; / 修改以后,看以可以用,试试吧,不行再告诉我.
我和楼主较上了. 我认为只要SQL能执行的,为什么要用那么长的PL/SQL呢? 下面是8601-8605的例子,楼主可以改成动态SQL执行嘛.SQL> select count(*) from tb_busname start with id='8601' connect by id != '8605' and prior next_stop = id ; COUNT(*) ---------- 5
to guogexp:谢谢你这么热心的回复,但是这种树形查询如果遇到顺序颠倒地情况就行不通了。
to beckhambobo: 我有个问题,v_sor是什么?是一个游标变量吗? 另外,当我要查询8604到8602时,程序就不能返回正确的行数了,怎么办?
这是我写的另一个解决办法,但返回值是0,请帮忙改正,谢谢各位。 --编写在不用换车模块中用于计算乘车经过站数的存储函数 CREATE OR REPLACE FUNCTION func_checksamecount( i_begin_id IN tb_addressname.bus_id%TYPE, i_end_id IN tb_addressname.bus_id%TYPE) RETURN NUMBER AS v_count number(5) :=0; v_countn number(5) :=0; v_countp number(5) :=0; i_inputn_id tb_begin_temp.bus_id%type :=i_begin_id; i_inputp_id tb_begin_temp.bus_id%type :=i_begin_id; i_next_s tb_begin_temp.next_stop%type :=0; i_bus_name tb_begin_temp.bus_name%type; initp_id tb_begin_temp.bus_id%type :=0; initp_prior_stop tb_begin_temp.prior_stop%type :=i_begin_id; initp_next_stop tb_begin_temp.next_stop%type :=0; initn_id tb_begin_temp.bus_id%type :=0; initn_prior_stop tb_begin_temp.prior_stop%type :=0; initn_next_stop tb_begin_temp.next_stop%type :=i_begin_id; checkp number(1):=0; checkn number(1):=0; checkdone number(1):=0;
BEGIN delete from tb_count1;while checkdone=0 loop checkp:=0; checkn:=0;if initn_next_stop = 1 and i_inputn_id !=i_end_id then checkn:=4; end if; while checkn = 0 loop i_inputn_id := initn_next_stop;select next_stop into initn_next_stop from tb_busname where bus_id = i_inputn_id; v_countn := v_countn+1; if initn_next_stop = i_end_id then checkn:=2; checkp:=1; checkdone:=1; end if; checkn := 3; checkp := 0; end loop; if initp_prior_stop = 0 and i_inputp_id != i_end_id then checkp:=4; end if; while checkp = 0 loop i_inputp_id := initp_prior_stop;select prior_stop into initp_prior_stop from tb_busname where bus_id = i_inputp_id; v_countp := v_countp+1;
if initp_prior_stop = i_end_id then checkn:=1; checkp:=2; checkdone:=1; end if; checkp:=3; checkn:=0; end loop; exit when checkdone = 1; end loop; if checkn = 2 then v_count:=v_countn; elsif checkp = 2 then v_count:=v_countp; else v_count:=0; end if;RETURN v_count; END func_checksamecount;
:)create or replace function getStopsCount( xfrom in pls_integer, xto in pls_integer, xforward boolean :=true)
return number is cursor cnext(xid pls_integer) is select next_stop from tb_busname where id=xid for update nowait;
cursor cprior(xid pls_integer) is select prior_stop from tb_busname where id=xid for update nowait;
vcount pls_integer :=0; vcur pls_integer :=xfrom; vnxt pls_integer ; begin if xfrom=xto then return vcount; end if; loop vcount:=vcount+1; if xforward then open cnext(vcur); fetch cnext into vnxt; if cnext%notfound then vcount:=0; end if; close cnext; else open cprior(vcur); fetch cprior into vnxt; if cprior%notfound then vcount:=0; end if; close cprior; end if; vcur :=vnxt;
exit when vcount=0 or vnxt=xto; end loop; return vcount; exception when others then return 0; end;
to rainbow0911: 我在输入从8601到8604时函数返回的行数正确: declare a number(5); begin a := func_checksamecount(8601,8604); DBMS_OUTPUT.PUT_LINE(TO_CHAR(a)); end; 但当输入8604到8601时,返回的行数为0。请帮忙改正,谢谢!
但是这种树形查询如果遇到顺序颠倒地情况就行不通了。 什么意思? 我觉得start with ...connect by 完全可以解决你的问题啊....顺序和倒序计算都可以!!!用Cursor做是很容易,但是效率就真的不知道了..Oracle既然提供了一些方便的机制,为什么不用?
to hhdn: 注意第3个参数xforward,注意灵活应用,给你一个完整的例子写法 declare n pls_integer; begin n:=getStopsCount(8604,8601,true); if n>0 then dbms_output.put_line(n); return; else n:=getStopsCount(8604,8601,false); end if; dbms_output.put_line(n); return; end;
pl/sql大家应该易于理解! 方法2(connect by也是可以的,但要看更具体的要求) select level from tb_busname where next_stop=8604 start with id=8601 connect by id=prior next_stop;
select level,tb_busname.* from tb_busname where id=8601 start with next_stop=8604 connect by id=prior prior_stop;
tb_busname
---------------------------------
id name prior_stop next_stop
8601 86 0 8602
8602 86 8601 8603
8603 86 8602 8632
8604 86 8632 8605
8605 86 8604 1
8632 86 8603 8604
请注意,要根据next_stop列来确定正确id所在地行,才能统计行数,
如果按照你的方法则得到行数为5,但正确行数却为6。
where next_stop between 8602 and 8605
select count(*) into v_count from tb_busname
where (next_stop between 8602) and 8605 or (id between 8602 and 8605)
id为每一行的唯一的标识,它唯一标识了一个车次名和一个站名,name为车次名,prior_stop为此站的前一个站点名,next_stop为此站的下一个站点名
tb_busname
---------------------------------
id name stop prior_stop next_stop
8601 86 stop1 0 8602
8602 86 stop2 8601 8603
8603 86 stop3 8602 8632
8604 86 stop4 8632 8605
8605 86 stop5 8604 1
8632 86 stop6 8603 8604
其实这是一个双向链表的存储结构,我需要查询id为8602到8605的站点的个数,由于8632为后来插入的新站点,所以应该根据next_stop所指向的id的行来计算,正确的行数应该为5.即8602-->8603-->8632-->8604-->8605;请编写一个函数帮我完成将得到的行数送变量v_count,然后返回.感激不尽呀!!!
语法我不记得了,查下帮助哪...大概是count(*) from tb_busname start with id=8602 connect by prior_stop = prior id..
我试验了你的方法,但是查询返回的是表中全部记录的行数,我若想查询8602到8604的行数就行不通了。
as
cursor t_sor(v_bgid varchar2,v_edid varchar2) is
select id from tb_busname where id=v_bgid;
begin
count:=0;
for v_sor in t_sor(bg_id,ed_id) loop
count:=count+1;
if v_sor.id=ed_id then
exit;
end if;
end loop;
end;
/
还是大猫的方法:
SQL> create table tb_busname
2 ( id varchar2(10) ,
3 name varchar2(10) ,
4 stop varchar2(10) ,
5 prior_stop varchar2(10) ,
6 next_stop varchar2(10));Table created.
SQL> insert into tb_busname values ('8601', '86', 'stop1', '0','8602') ;1 row created.SQL> insert into tb_busname values ('8602', '86', 'stop2', '8601', '8603') ;1 row created.SQL> insert into tb_busname values ('8603', '86' , 'stop3', '8602', '8632') ;1 row created.SQL> insert into tb_busname values ('8604', '86', 'stop4', '8632', '8605') ;1 row created.SQL> insert into tb_busname values ('8605' , '86' , 'stop5' , '8604' , '1') ;1 row created.SQL> insert into tb_busname values ('8632' , '86' , 'stop6' , '8603' , '8604')
;1 row created.SQL> commit;Commit complete.SQL> select count(*) from tb_busname start with id='8602' connect by prior_stop != '8604' and p
rior next_stop = id ; COUNT(*)
----------
4
有如下的一个表:(双向链表)
tb_busname
---------------------------------
id name prior_stop next_stop
8601 86 0 8602
8602 86 8601 8603
8603 86 8602 8632
8604 86 8632 8605
8605 86 8604 1
8632 86 8603 8604
现要编写过程来统计id为8602到8604的行数(换言之,就是要统计表中任意两个id号之间的行值,请注意:提取id的顺序也可能发生改变,在这里我假设为8602到8604,但是当提取的id是8604到8602,程序也能返回正确的行数),并将行数放入变量v_count中,请帮忙,谢谢!
tb_count1为新建的一个用于统计站数的表,包含三列,结构为:
bus_id number(5),
prior_stop number(5),
next_stop number(5)
--编写用于计算乘车经过站数的存储函数
CREATE OR REPLACE FUNCTION func_checksamecount(
i_begin_id IN tb_addressname.bus_id%TYPE,
i_end_id IN tb_addressname.bus_id%TYPE)
RETURN NUMBER
AS
v_count number(5) :=0;
i_input_id tb_begin_temp.bus_id%type := i_begin_id;
i_next_s tb_begin_temp.next_stop%type :=0;
i_bus_name tb_begin_temp.bus_name%type;
init_id tb_begin_temp.bus_id%type :=0;
init_prior_stop tb_begin_temp.prior_stop%type :=0;
init_next_stop tb_begin_temp.next_stop%type := 0;CURSOR cur_initcount
IS
select bus_id,prior_stop,next_stop
from tb_busname
where bus_id = i_input_id;BEGIN
delete from tb_count1;OPEN cur_initcount;
loop
fetch cur_initcount into init_id,init_prior_stop,init_next_stop;
exit when cur_initcount%notfound;
insert into tb_count1 values(init_id,init_prior_stop,init_next_stop);
if i_input_id = i_end_id then
exit;
else
i_input_id := init_next_stop;
end if;
end loop;
close cur_initcount;select count(*) into v_count from tb_count1;RETURN v_count;
END func_checksamecount;
我编写了一个pl/sql块用于测试它,
declare
a number(5);
begin
a := func_checksamecount(8602,8604);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(a));
end;
但是结果为:1
而正确结果为:3
请大家帮忙改正。
as
cursor t_sor is
select id,next_stop from tb_busname;
i number:=0;
begin
count:=0;
for v_sor in t_sor loop
if v_sor.id=bg_id then
i:=1;
end if;
count:=count+i;
if v_sor.next_stop=ed_id then
count:=count+1;
exit;--退出循环
end if;
end loop;
end;
/
修改以后,看以可以用,试试吧,不行再告诉我.
我认为只要SQL能执行的,为什么要用那么长的PL/SQL呢?
下面是8601-8605的例子,楼主可以改成动态SQL执行嘛.SQL> select count(*) from tb_busname start with id='8601' connect by id != '8605' and prior next_stop = id ; COUNT(*)
----------
5
我有个问题,v_sor是什么?是一个游标变量吗?
另外,当我要查询8604到8602时,程序就不能返回正确的行数了,怎么办?
--编写在不用换车模块中用于计算乘车经过站数的存储函数
CREATE OR REPLACE FUNCTION func_checksamecount(
i_begin_id IN tb_addressname.bus_id%TYPE,
i_end_id IN tb_addressname.bus_id%TYPE)
RETURN NUMBER
AS
v_count number(5) :=0;
v_countn number(5) :=0;
v_countp number(5) :=0;
i_inputn_id tb_begin_temp.bus_id%type :=i_begin_id;
i_inputp_id tb_begin_temp.bus_id%type :=i_begin_id;
i_next_s tb_begin_temp.next_stop%type :=0;
i_bus_name tb_begin_temp.bus_name%type;
initp_id tb_begin_temp.bus_id%type :=0;
initp_prior_stop tb_begin_temp.prior_stop%type :=i_begin_id;
initp_next_stop tb_begin_temp.next_stop%type :=0;
initn_id tb_begin_temp.bus_id%type :=0;
initn_prior_stop tb_begin_temp.prior_stop%type :=0;
initn_next_stop tb_begin_temp.next_stop%type :=i_begin_id;
checkp number(1):=0;
checkn number(1):=0;
checkdone number(1):=0;
BEGIN
delete from tb_count1;while checkdone=0 loop
checkp:=0;
checkn:=0;if initn_next_stop = 1 and i_inputn_id !=i_end_id then
checkn:=4;
end if;
while checkn = 0 loop
i_inputn_id := initn_next_stop;select next_stop into initn_next_stop from tb_busname where bus_id = i_inputn_id;
v_countn := v_countn+1; if initn_next_stop = i_end_id then
checkn:=2;
checkp:=1;
checkdone:=1;
end if; checkn := 3;
checkp := 0;
end loop;
if initp_prior_stop = 0 and i_inputp_id != i_end_id then
checkp:=4;
end if;
while checkp = 0 loop
i_inputp_id := initp_prior_stop;select prior_stop into initp_prior_stop from tb_busname where bus_id = i_inputp_id;
v_countp := v_countp+1;
if initp_prior_stop = i_end_id then
checkn:=1;
checkp:=2;
checkdone:=1;
end if; checkp:=3;
checkn:=0;
end loop;
exit when checkdone = 1;
end loop;
if checkn = 2 then
v_count:=v_countn;
elsif checkp = 2 then
v_count:=v_countp;
else
v_count:=0;
end if;RETURN v_count;
END func_checksamecount;
xfrom in pls_integer, xto in pls_integer,
xforward boolean :=true)
return number
is
cursor cnext(xid pls_integer) is select next_stop from tb_busname
where id=xid for update nowait;
cursor cprior(xid pls_integer) is select prior_stop from tb_busname
where id=xid for update nowait;
vcount pls_integer :=0;
vcur pls_integer :=xfrom;
vnxt pls_integer ;
begin
if xfrom=xto then
return vcount;
end if;
loop
vcount:=vcount+1;
if xforward then
open cnext(vcur);
fetch cnext into vnxt;
if cnext%notfound then
vcount:=0;
end if;
close cnext;
else
open cprior(vcur);
fetch cprior into vnxt;
if cprior%notfound then
vcount:=0;
end if;
close cprior;
end if;
vcur :=vnxt;
exit when vcount=0 or vnxt=xto;
end loop;
return vcount;
exception when others then return 0;
end;
我在输入从8601到8604时函数返回的行数正确:
declare
a number(5);
begin
a := func_checksamecount(8601,8604);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(a));
end;
但当输入8604到8601时,返回的行数为0。请帮忙改正,谢谢!
什么意思?
我觉得start with ...connect by 完全可以解决你的问题啊....顺序和倒序计算都可以!!!用Cursor做是很容易,但是效率就真的不知道了..Oracle既然提供了一些方便的机制,为什么不用?
我做的是一个统计公交车起始站到终点站所经过的站数,例如有如下一个公交车的线路:
BUS_ID PRIOR_STOP NEXT_STOP
------- ---------- ----------
8601 0 8602
8602 8601 8603
8603 8602 8604
8604 8603 8605
8605 8604 8606
8606 8605 8607
8607 8606 8608
8608 8607 8609
8609 8608 8610
8610 8609 8611
8611 8610 8612
8612 8611 8613
8613 8612 8614
8614 8613 8615
8615 8614 8616
8616 8615 8617
8617 8616 8618
8618 8617 8619
8619 8618 8620
8620 8619 8621
8621 8620 8622
8622 8621 8623
8623 8622 8624
8624 8623 8625
8625 8624 8626
8626 8625 8627
8627 8626 8628
8628 8627 8629
8629 8628 1
用户输入任意两个公交车的id即bus_id,程序就能统计出这两个id之间的经过站数即行数。
但情况是用户输入的id可能是顺序也可能是倒序,另外,start with...connect by只能查询任意非叶子结点到叶子结点的长度,不能查询非叶子节点到非叶子节点的长度呀。
举例来说:我要查询id号从8601到8607的行数,返回值为6,当我输入id号从8607到8601时,返回值还能是6,即达到程序的目的。
注意第3个参数xforward,注意灵活应用,给你一个完整的例子写法
declare
n pls_integer;
begin
n:=getStopsCount(8604,8601,true);
if n>0 then
dbms_output.put_line(n);
return;
else
n:=getStopsCount(8604,8601,false);
end if;
dbms_output.put_line(n);
return;
end;
方法2(connect by也是可以的,但要看更具体的要求)
select level from tb_busname
where next_stop=8604
start with id=8601
connect by id=prior next_stop;
select level,tb_busname.* from tb_busname
where id=8601
start with next_stop=8604
connect by id=prior prior_stop;