StoredProcedure — 存储过程

1.声明变量

DECLARE @F001 SMALLINT ,           @F002 INTEGER ,           @F003 VARCHAR ( 20 ), @F004 CHAR ( 20 ),           @F002 MONEY 2.赋值语句

set @F001 = space ( 40 )

3.条件判断(IF…ELSE)

If condition Begin [ statements   ] END ELSE 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.动态定义游标

SET @strSQL = ‘ DECLARE   name_cursor   CURSOR   FOR   ‘ + @inSQL EXEC    ( @strSQL )

7.遍历游标

FETCH NEXT FROM name_cursor into @F001 , @F002 WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM name_cursor into @F001 , @F002

END

说明：FETCH_STATUS检索到数据返回0，失败返回-1，可判断是否滚动未到结尾。

8.获得游标行数

SET @RECCNT = @@ROWCOUNT

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 )

11.创建临时表存储外部数据表 说明:临时过程用 # 和 ## 命名，可以由任何用户创建。创建过程后，局部过程的所有者是唯一可以使用该过程的用户。

CREATE TABLE #DMPARHED (FMCD    int , FMNAM   varchar ( 50 ), MGYO1   smallint , constraint DMPARHED_P primary key (FMCD)) SET @aSQL = ” SET @aSQL = @aSQL + ‘ INSERT INTO #DMPARHED ‘ SET @aSQL = @aSQL + ‘ SELECT FMCD,FMNAM,MGYO1 FROM ‘ SET @aSQL = @aSQL + ‘ OPENQUERY(Lk_MDB_NEO32, ” SELECT FMCD,FMNAM,MGYO1 FROM DMPARHED ‘ SET @aSQL = @aSQL + ‘ WHERE SYSNO = 1 ” ) ‘ execute ( @aSQL )

创建临时表的另类方法：

select a.name,a.password from with as temp1 select * from emp ( select * from temp1 union select * from temp1) a where a.name = ‘ hao ‘ ;

12.存储过程的调用及返回值 （1）存储过程的声明

CREATE PROCEDURE name_produce @F001 VARCHAR ( 20 ), @F002 SMALLINT OUTPUT

（2）VB.NET调用存储过程

Private SqlCmd As New OleDb.OleDbCommand

SqlCmd.CommandText = “ prNK3020SC03 “ SqlCmd.CommandType = CommandType.StoredProcedure

Dim parampre1 As OleDb.OleDbParameter = SqlCmd.Parameters.Add( _ New OleDb.OleDbParameter( “ @F001 “ , OleDb.OleDbType.VarChar, 20 , _           ParameterDirection.Input)) Dim parampre2 As OleDb.OleDbParameter = SqlCmd.Parameters.Add( _ New OleDb.OleDbParameter( “ @F002 “ , OleDb.OleDbType.SmallInt)) parampre2.Direction = ParameterDirection.Output

SqlCmd.Parameters( “ @F001 “ ).Value = aF001 SqlCmd.Parameters( “ @F002 “ ).Value = aF002 SqlCmd.ExecuteNonQuery() aF002 = SqlCmd.Parameters( “ @F002 “ ).Value.ToString()

（3）存储过程调用存储过程

DECLARE @C001 VARCHAR ( 20 ), @C002 SMALLINT EXEC name_produce @C001 , @C002 output

CREATE PROCEDURE dbo.getUserName @UserID int , @UserName varchar ( 40 ) output as set nocount on begin if @UserID is null return select @UserName = username from dbo. [ userinfo ] where userid = @UserID return end

13.Update语句常见错误总结 — √ Update name_table set         F001 = @F181 ,         F002 = @F182 Where         F003 = @F003 — × Update name_table         F001 = @F181 ,         F002 = @F182 Where         F003 = @F003 — × Update name_table set         F001 = @F181 ,         F002 = @F182 , Where         F003 = @F003 — × Update name_table set         ,F001 = @F181         ,F002 = @F182 Where         F003 = @F003

14.Insert语句常见语法错误总结 — √ INSERT INTO name_table( ,KEY_FIELD，BUSYOCD ) Values ( @F001 , @F002 ) — × INSERT INTO name_table( F001,F002 ) Values ( , @F001 , @F002 ) — × INSERT INTO name_table( F001,F002 ) Values ( @F001 , @F002 , ) — × INSERT    name_table( F001,F002 ) Values ( @F001 , @F002 )

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/164508.html原文链接：https://javaforall.c