StoredProcedure — 存储过程 - 全栈程序员必看

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元售后保障童叟无欺
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

)