【SQLSERVER】存储过程基础
发布时间：2020-12-12 15:51:37 所属栏目：MsSql教程 来源：网络整理
导读：1.声明变量 DECLARE ? @F001 ? SMALLINT ,??? ??? ????? @F002 ? INTEGER ,??? ??? ????? @F003 ? VARCHAR ( 20 ),??? ??? ????? @F004 ? CHAR ( 20 ),??? ??? ????? @F002 ? MONEY 2.赋值语句 set ? @F001 ? = ? space ( 40 ) 3.条件判断(IF...ELSE) If ?co
|
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,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 )
Values
(
@F001
,
@F002
,)
--
×
INSERT
??name_table( F001,
@F002
)
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |