sqlserver存储过程和游标.docVIP

SQLSERVER存储过程基础 1.声明变量 DECLARE @F001 SMALLINT, (三元素，声明declare+变量名+类型) @F002 INTEGER, @F003 VARCHAR(20), @F004 CHAR(20), @@F002 MONEY 2.赋值语句 SET @F001 = space(40) @F002= 3 3.条件判断(IF...ELSE) IF condition BEGIN [ statements ] END ELSE BEGIN (ELSE IF condition2 BEGIN) [ elseifstatements ] END 4.多分支判断(case [when...then...else...] end） SET @F011 = CASE WHEN [testexpression1] THEN @F001 WHEN [testexpression2] THEN @F002 WHEN [testexpression3] THEN @F003 WHEN [testexpression4] THEN @F004 END 5.循环(while) WHILE condition BEGIN [ statements ] END 6.动态定义游标(*) DECLARE @strSQL nvarchar(30) DECLARE @inSQL nvarchar(50) -- select... SET @strSQL = DECLARE name_cursor CURSOR FOR + @inSQL EXEC (@strSQL) 7.遍历游标 FETCH NEXT FROM name_cursor into @F001,@F002 （要求与游标的返回值对应） WHILE @@FETCH_STATUS = 0 BEGIN （如@inSQL是select f001,f002 from tablename) FETCH NEXT FROM name_cursor into @F001,@F002 END (说明：FETCH_STATUS检索到数据返回0，失败返回-1，可判断是否滚动未到结尾。) 8.获得游标行数 SET @RECCNT = @@ROWCOUNT --内嵌参数@@FETCH_STATUS 同 9.事务处理 BEGIN distributed transaction WHILE @@TRANCOUNT 0 commit transaction 10.字符串连接 SET @m_sql = @m_sql + Where F001 = + @F001 + SET @m_sql = @m_sql + F002 = + CONVERT(varchar,@F002) convert会加引号？ 11.存储过程的调用及返回值 （1）存储过程的创建 (sql server数据库中create procedure) CREATE PROCEDURE material_solo_days_proc (@materialno varchar(20), @year_month datetime, @totalday int, @qty decimal(18,4)=0 output --总量,也会return ) AS BEGIN DECLARE @@count int SET @@count = 0 DECLARE @@dailyqty decimal(18,4) SET @@dailyqty = 0 SET @qty = 0 WHILE @@count @totalday BEGIN --计算日流水量 EXECUTE @@dailyqty = material_solo_oneday_proc @materialno,@year_month SET @qty = @qty + @@dailyqty SET @year_month = dateadd(dd,1,@year_month) SET @@count = @@count + 1 END RETURN @qty END （2）SQL-DELPHI调用存储过程(open/execproc) with material_account_proc d