大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺
1.声明变量
DECLARE
@F001
SMALLINT
,
@F002
INTEGER
,
@F003
VARCHAR
(
20
),
@F004
CHAR
(
20
),
@F002
MONEY
2.赋值语句
@F001
=
space
(
40
)
3.条件判断(IF…ELSE)
condition
Begin
[
statements
]
END
ELSE
BEGIN
[
elseifstatements
]
END
4.多分支判断(case…when…then…else…end）
@F011
=
CASE
WHEN
[
testexpression1
]
THEN
@F001
WHEN
[
testexpression2
]
THEN
@F002
WHEN
[
testexpression3
]
THEN
@F003
WHEN
[
testexpression4
]
THEN
@F004
END
5.循环(while)
condition
Begin
[
statements
]
End
6.动态定义游标
@strSQL
=
‘
DECLARE name_cursor CURSOR FOR
‘
+
@inSQL
EXEC
(
@strSQL
)
7.遍历游标
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.获得游标行数
@RECCNT
=
@@ROWCOUNT
9.事务处理
distributed
transaction
WHILE
@@TRANCOUNT
>
0
commit
transaction
10.字符串连接
@m_sql
=
@m_sql
+
‘
Where F001 =
”’
+
@F001
+
””
SET
@m_sql
=
@m_sql
+
‘
F002 =
‘
+
CONVERT
(
varchar
,
@F002
)
11.创建临时表存储外部数据表
说明:临时过程用 # 和 ## 命名，可以由任何用户创建。创建过程后，局部过程的所有者是唯一可以使用该过程的用户。
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
)
创建临时表的另类方法：
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）存储过程的声明
PROCEDURE
name_produce
@F001
VARCHAR
(
20
),
@F002
SMALLINT
OUTPUT
（2）VB.NET调用存储过程
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）存储过程调用存储过程
@C001
VARCHAR
(
20
),
@C002
SMALLINT
EXEC
name_produce
@C001
,
@C002
output
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.net/164508.html原文链接：https://javaforall.net
