1.局部变量
声明单个局部变量
declare @num int
声明多个局部变量
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
局部变量赋值
被赋值的局部变量必须是已经声明的。
a.简单赋值方法
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
b.使用select语句赋值
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
注意:如果查询返回了多个值时,那么只有最后一个值赋给了变量。
c.使用update语句赋值
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
注意:update无法象select语句一样魏数据提供一些常用的转换,所以在使用update进行赋值时,
最好严格匹配数据类型,否则会产生错误。
2.全局变量
下面列举几个我们在编程中常用的全局变量
a. @@CURSOR_ROWS
返回本次服务器连接中,打开游标取回的数据行的数目。如:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
b. @@ERROR
返回上一条语句返回的错误号,执行成功返回0,
一般在insert,update,delete语句之后使用(常结合事务处理)。
c. @@FETCH_STATUS
返回上一次使用游标FETCH操作所返回的状态值。返回值为0表示操作成功,
为-1表示操作失败或者已经超过了游标所能操作的数据行的范围,当到了最后一行数据后,
还要接着取下一列数据,返回-2,表示返回值已经丢失。
d. @@ROWSCOUNT
返回上一条SQL语句所影响到的数据行的数据。常用于检查操作是否达到了目的,
当执行的SQL语句不影响数据库数据时,该变量返回0
e. @@VERSION
返回版本号
3.结构语句
a.条件结构
if.... else ...如:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
b.循环结构
while 语句。如:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
在循环中常用的语句有break和continue,
break为跳出while,而continue为跳出当前循环,进入下一循环。
有时候也用到return和goto语句,下面我们将讲这两个语句。
c.case语句
case语句又叫条件分支语句。如:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
或者
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
注意:case语句中when匹配成功后,就到end,不会匹配下一个when,
所以如果有一条记录,userid = '1' 并且usertype = '2',
则返回uertype是‘admin'而不是’general user'
d.return语句
立即退出程序,return后面的语句将不执行。return 后常跟一个整形表达式作为返回值。
e.goto语句
跳转到跟在goto后面的标签位置。如
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
以后将相继推出触发器,存储过程,游标及性能优化