以下是公司机试题需要实现的其中的一个功能
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*
存储过程:Proc_f_invoice_mzsfjs_fph(int,varchar)
参数描述:INT 操作员编号,varchar 发票类型:‘%’所有发票,‘1%’作废发票
存储过程功能:得出指定操作员当前所有未结算的发票号码
*/
ALTER PROCEDURE [dbo].[Proc_f_invoice_mzsfjs_fph](@operater int,@if_zf varchar(2),@type VARCHAR(2),@invoice_kind varchar(2),@@f_no varchar(500) output)
--定义游标处理发票问题
AS
BEGIN
DECLARE @ll_invoice_no_old int
DECLARE @ll_invoice_no_now int
DECLARE @ls_invoice_no varchar(500)
SET @ll_invoice_no_old = 0
SET @ll_invoice_no_now = 0
SET @ls_invoice_no = ''
IF @type = '1'
BEGIN
DECLARE get_invoice_no CURSOR FOR
SELECT invoice_no
FROM f_invoice
WHERE f_invoice.p_hz_date IS NULL AND f_invoice.operater = @operater and f_invoice.if_zf like @if_zf and
f_invoice.invoice_kind = @invoice_kind
Order by invoice_no
END
IF @type = '2'
BEGIN
DECLARE get_invoice_no CURSOR FOR
select distinct recipe_no
from f_invoice,da_recipe_cf
where f_invoice.invoice_no =da_recipe_cf.invoice_no and
f_invoice.p_hz_date IS NULL and
f_invoice.operater = @operater and f_invoice.if_zf like @if_zf and
f_invoice.invoice_kind = @invoice_kind
order by recipe_no
END
OPEN get_invoice_no
FETCH NEXT FROM get_invoice_no INTO @ll_invoice_no_now
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ll_invoice_no_old = 0
SET @ls_invoice_no = CAST( @ll_invoice_no_now AS varchar(10))
ELSE
BEGIN
IF @ll_invoice_no_old + 1 = @ll_invoice_no_now
BEGIN
IF SUBSTRING(@ls_invoice_no,DATALENGTH(@ls_invoice_no),1) <> '-'
SET @ls_invoice_no = @ls_invoice_no + '-'
END
ELSE
BEGIN
IF SUBSTRING(@ls_invoice_no,DATALENGTH(@ls_invoice_no),1) = '-'
SET @ls_invoice_no = @ls_invoice_no + CAST(@ll_invoice_no_old AS VARCHAR(10)) + ',' + CAST( @ll_invoice_no_now AS varchar(10))
ELSE
SET @ls_invoice_no = @ls_invoice_no + ',' + CAST( @ll_invoice_no_now AS varchar(10))
END
END
SET @ll_invoice_no_old = @ll_invoice_no_now
FETCH NEXT FROM get_invoice_no INTO @ll_invoice_no_now
END
IF SUBSTRING(@ls_invoice_no,DATALENGTH(@ls_invoice_no),1) = '-'
SET @ls_invoice_no = @ls_invoice_no + CAST(@ll_invoice_no_old AS VARCHAR(10))
CLOSE get_invoice_no
DEALLOCATE get_invoice_no
set @@f_no = @ls_invoice_no
END