Mutiple rows in singleton select 说明返回多条记录,:songnames,:singernames不确定
: lizhenjia(暴雪) 那该怎么办,是否要加一个for循环??
CREATE PROCEDURE LANSELECT ( LANID VARCHAR(3) ) AS begin select songname,singername from usersongs where songlanid=:lanid and songno in (select songfilename from songpath) end 这样选出所有符合条件的纪录不好吗? 或者使用游标
如果有多条记录返回可以将输出参数去除 把存储过程当作一个dataset来使用CREATE PROCEDURE LANSELECT ( LANID VARCHAR(3) ) AS begin select songname,singername from usersongs where songlanid=:lanid and songno in (select songfilename from songpath); exit; end
: xmlord(护戒使者) 编译通不过!
办法1: 定义一个数组 选出的数据into 到这个数组中 办法2: 用游标 declare cursor cursorname is select ...... form ... where ... open cursorname; Fetch cursorname into....
忘了告诉大家:我用的是interbase
如果是interbase;请使用一下类似的语句,具体我记不清了,有快三年没有用interbase了:),现在机器上没有装,你看一下interbase的文档CREATE PROCEDURE LANSELECT ( LANID VARCHAR(3) ) RETURNS ( SONGNAMES VARCHAR(80), SINGERNAMES VARCHAR(30) ) AS begin for select songname,singername from usersongs where songlanid=:lanid and songno in (select songfilename from songpath) into :songnames,:singernames do suspend ;end
多谢,我也改成了这样,可是进行查询时,出错说我的dataset是关闭的
嘻嘻,当然要使用游标了 CREATE PROCEDURE LANSELECT ( LANID VARCHAR(3) ) RETURNS ( SONGNAMES VARCHAR(80), SINGERNAMES VARCHAR(30) ) AS begin declare mycursor cursor for select songname,singername from usersongs where songlanid=:lanid and songno in (select songfilename from songpath) open mycursor fetch mycursor into :SONGNAMES,:SINGERNAMES
说明返回多条记录,:songnames,:singernames不确定
你先看看数据库里有没有重复的记录,另,把sql语句在查询分析器里执行一下,看看结果集。
还有不知道你用的是什么数据库,我是sqlserver。
你怎么写是不是只希望得到两个值?但是查到好象不只一条记录。
说明返回多条记录,:songnames,:singernames不确定
LANID VARCHAR(3)
)
AS
begin
select songname,singername from usersongs where songlanid=:lanid and songno in (select songfilename from songpath)
end
这样选出所有符合条件的纪录不好吗?
或者使用游标
把存储过程当作一个dataset来使用CREATE PROCEDURE LANSELECT (
LANID VARCHAR(3)
) AS
begin
select songname,singername from usersongs where songlanid=:lanid and songno in (select songfilename from songpath);
exit;
end
定义一个数组
选出的数据into 到这个数组中
办法2:
用游标
declare cursor cursorname is
select ......
form ...
where ...
open cursorname;
Fetch cursorname into....
LANID VARCHAR(3)
) RETURNS (
SONGNAMES VARCHAR(80),
SINGERNAMES VARCHAR(30)
) AS
begin
for select
songname,singername from usersongs where songlanid=:lanid and songno in (select songfilename from songpath)
into :songnames,:singernames
do
suspend ;end
CREATE PROCEDURE LANSELECT (
LANID VARCHAR(3)
) RETURNS (
SONGNAMES VARCHAR(80),
SINGERNAMES VARCHAR(30)
) AS
begin
declare mycursor cursor for select
songname,singername from usersongs where songlanid=:lanid and songno in (select songfilename from songpath)
open mycursor
fetch mycursor into :SONGNAMES,:SINGERNAMES