CREATE OR REPLACE PROCEDURE GetAlbums(IsPublic In Integer)
AS
begin
SELECT Albums.AlbumID,Albums.Caption,Albums.IsPublic,Count(Photos.PhotoID) AS NumberOfPhotos
FROM Albums LEFT JOIN Photos
ON Albums.AlbumID = Photos.AlbumID
WHERE
(Albums.IsPublic = 1 OR Albums.IsPublic = 1);
end;
AS
begin
SELECT Albums.AlbumID,Albums.Caption,Albums.IsPublic,Count(Photos.PhotoID) AS NumberOfPhotos
FROM Albums LEFT JOIN Photos
ON Albums.AlbumID = Photos.AlbumID
WHERE
(Albums.IsPublic = 1 OR Albums.IsPublic = 1);
end;
如果想返回记录集的话,可以用游标来实现
select ...
into ...
from ...
where ...
AS
begin
SELECT Albums.AlbumID,Albums.Caption,Albums.IsPublic,Count(Photos.PhotoID) AS NumberOfPhotos
FROM Albums LEFT JOIN Photos
ON Albums.AlbumID = Photos.AlbumID
WHERE
(Albums.IsPublic = IsPublic 這里是個變量 OR Albums.IsPublic = 1);
end;
对于plsql的程序块来说,是不能直接select来做查询你的,你需要用select ... into的语句,或者是open cursor的语句来取结果集。
FROM Albums LEFT JOIN Photos
ON Albums.AlbumID = Photos.AlbumID
WHERE
(Albums.IsPublic = 1 OR Albums.IsPublic = 1); 有countr()函数应该加个group by Albums.AlbumID,Albums.Caption,Albums.IsPublic
而且存储过程不能这样执行select语句的,楼上的都已经说明了