Sybase 自定义函数,游标存储过程的使用 示例

Sybase 自定义函数（function）、游标（cursor）、存储过程（procedure）示例：

一. 函数（function）：Sybase数据库有的版本是不支持自定义函数的，本示例是在Sybase Central Adaptive Server Enterprise Plug-in Utility/15.5/EBF 17785

IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid AND https://www.sodocs.net/doc/ca2156485.html, = 'function_get_house_tenement' AND https://www.sodocs.net/doc/ca2156485.html, = 'dbo' AND o.type = 'SF')
BEGIN
setuser 'dbo'
drop function function_get_house_tenement

END
go

/*
** Sybase自定义函数：根据房产id，得到该房产对应的物业信息
** returns 物业名称：（拼接而成）
** 测试语句：select dbo.function_get_house_tenement ('2584')
** 0:Fetch语句成功
** 1:Fetch语句导致一错误
** 2:结果集没有更多的数据，当前位置位于结果集最后一行，而客户对该游标仍发出Fetch语句时。
*/
create function dbo.function_get_house_tenement(
@houseId varchar(32)
)
returns varchar(100)
as
begin

declare @tenementName varchar(100)
declare @temp varchar(100)
declare house_tenement_cursor cursor
for select tenement_type_name from tenement_type_dic where tenement_type_id in (select tenement_type_id from house_tenement_list where house_id=@houseId)
open house_tenement_cursor
while @@SQLSTATUS = 0
begin
fetch house_tenement_cursor into @temp
if @@SQLSTATUS=0
begin
select @tenementName = @tenementName + @temp+ ','
end
end
close house_tenement_cursor
DEALLOCATE CURSOR house_tenement_cursor

return @tenementName
end


二. 存储过程(procedure)

IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid AND https://www.sodocs.net/doc/ca2156485.html, = 'test' AND https://www.sodocs.net/doc/ca2156485.html, = 'dbo' AND o.type = 'P')
BEGIN
setuser 'dbo'
drop procedure test

END
go

/*
** Sybase存储过程：根据房产id，得到该房产对应的物业信息
**
** 测试语句：exec dbo.test '2584'
** 0:Fetch语句成功
** 1:Fetch语句导致一错误
** 2:结果集没有更多的数据，当前位置位于结果集最后一行，而客户对该游标仍发出Fetch语句时。
*/
create procedure dbo.test(
@houseId varchar(100)
)
as
begin
declare @tenementName varchar(100)
declare @temp varchar(100)
declare house_tenement_cursor cursor
for select tenement_type_name from tenement_type_dic where tenement_type_id in (select tenement_type_id from house_tenement_list where house_id=@houseId)
open house_tenement_cursor
while @@SQLSTATUS = 0
begin
fetch house_tenement_cursor into @temp
if @@SQLSTATUS=0
begin
select @tenementName = @tenementName + @temp+ ','
end
end
close house_tenement_cursor
DEALLOCATE CURSOR house_tenement_cursor
print @tenementName
end