1.声明变量
DECLARE @F001 SMALLINT ,
@F002 INTEGER ,
@F003 VARCHAR ( 20 ),
@F004 CHAR ( 20 ),
@F002 MONEY
2.赋值语句
3.条件判断(IF...ELSE)
4.多分支判断(case...when...then...else...end)
5.循环(while)
6.动态定义游标
7.遍历游标
8.获得游标行数
9.事务处理
10.字符串连接
11.创建临时表存储外部数据表
说明:临时过程用 # 和 ## 命名,可以由任何用户创建。创建过程后,局部过程的所有者是唯一可以使用该过程的用户。
创建临时表的另类方法:
12.存储过程的调用及返回值
(1)存储过程的声明
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
)
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
[ 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
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
[ statements ]
End
6.动态定义游标
SET
@strSQL
=
'
DECLARE name_cursor CURSOR FOR
'
+
@inSQL
EXEC ( @strSQL )
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,可判断是否滚动未到结尾。
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM name_cursor into @F001 , @F002
END
8.获得游标行数
SET
@RECCNT
=
@@ROWCOUNT
9.事务处理
BEGIN
distributed
transaction
WHILE @@TRANCOUNT > 0
commit transaction
WHILE @@TRANCOUNT > 0
commit transaction
10.字符串连接
SET
@m_sql
=
@m_sql
+
'
Where F001 =
'''
+
@F001
+
''''
SET @m_sql = @m_sql + ' F002 = ' + CONVERT ( varchar , @F002 )
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 )
(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 ' ;
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调用存储过程
@F001 VARCHAR ( 20 ),
@F002 SMALLINT OUTPUT
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)存储过程调用存储过程
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()
DECLARE
@C001
VARCHAR
(
20
),
@C002 SMALLINT
EXEC name_produce @C001 , @C002 output
@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
@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
)