SQL 存储过程(2)

数据类型

字符字符串  字符常量使用引号进行分隔。
char  存固定大小的字符串，最大8000个字符。不足将用空格填满。
varchar  最大8000字符。不足不用空格填满，按原样记录。占用2个额外字节。
text 存储大型数据，最大为2GB。

Unicode字符字符串  字符常量使用引号进行分隔，并用N为前缀。比如：values(N'CODSD')
nchar  存固定大小的字符串，最大4000个字符。不足将用空格填满。
nvarchar  最大4000字符。不足不用空格填满，按原样记录。
ntext  存储大型数据，最大为1GB。

日期和时间的数据类型
datetime 占8字节，精度3.33ms 日期范围1753年1月1日到9999年12月31日
smalldatetime 占4字节，精度1ms，日期范围1900年1月1日到2079年6月6日
日期和时间常量在SQL中用引号分隔符分隔。Set DateOfBirth='2/21/1983 10:10 AM'

整数
int
smallint
tinyint
bigint
bit逻辑真或逻辑假

近似数字（近似计算） 避免使用'='，而是使用'<','>'等符号。
real单精度，精度最多7位。
float双精度，精度最多15位。

精确数字（非近似计算，必须指定小数位数和精度）
decimal 精度最大为38位。Weight decimal(10,3) 小数位数为3位，小数之前最多7位。
numeric

未定数据类型sql_variant
可以存储除text,ntext,image,timestamp,sql_variant这些类型以外的任何数据类型。
但使用它们时必须限制为：
·键中数据总长度小于900个字节，可以在索引和唯一键中使用sql_variant列。
·不能具有identity属性。
·不能是计算列的一部分。
·在Like谓词中不受支持。
·不支持全文索引。
·不可使用+运算符进行串联运算。
·有些函数不支持sql_variant类型的参数比如AVG，IDENTITY，ISNUMBER，POWER，ROUND，SIGN，SUM，VAR，VARP，RADIANS

局部变量  必须以@开头
Declare @LastName varchar(50),
        @FirstName varchar(30)

全局变量 必须以@@开头
@@identity

SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 是相似的函数，因为它们都返回插入到标识列中的值。

IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。有关详细信

息，请参阅 IDENT_CURRENT (Transact-SQL)。

SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中

的值；@@IDENTITY 不受限于特定的作用域。

@@error 记录错误编码。

@@rowcount 记录该语句影响到的总的记录条数，用来确认选择操作的成功与否。

 

table 变量[表变量] 变量具有下列优点：

A. table 变量的行为类似于局部变量，有明确定义的作用域。这就是在其中声明该变量的函数、存储过程或批处理。

在其作用域内，table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的任何地

方。但是，table 不能用于以下语句中：

SELECT select_list INTO table_variable  
 
在定义 table 变量的函数、存储过程或批处理结束时，会自动清除此变量。

table 类型声明中的 CHECK 约束、DEFAULT 值和计算列不能调用用户定义函数。

在存储过程中使用 table 变量与使用临时表相比，减少了存储过程的重新编译量。

涉及 table 变量的事务只在 table 变量更新期间存在。因此减少了 table 变量对锁定和记录资源的需求。

不能显式创建 table 变量的索引，也不保留 table 变量的任何统计信息。在某些情况下，可以通过改用支持索引和统计信息的临时表来改善

性能。有关临时表的详细信息，请参阅CREATE TABLE (Transact-SQL)。

可以在批处理的 FROM 子句中按名称引用 table 变量，如下例所示：
 
SELECT Employee_ID, Department_ID FROM @MyTableVar
 
在 FROM 子句外，必须使用别名来引用 table 变量，如下例所示：
-----------------------------------------------------------
SELECT EmployeeID, DepartmentID
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID)
-----------------------------------------------------------
不支持在 table 变量之间进行赋值操作。另外，由于 table 变量作用域有限，并且不是持久数据库的一部分，因而不受事务回滚的影响。
 

 

T-SQL游标
步骤：
·使用Declare Cursor语句，根据Select语句创建游标。
·使用Open语句填充游标。
·使用Fetch语句更改游标中的当前记录，并将值存储在局部变量中。NEXT,PRIOR,FIRST,LAST
·对检索的信息进行某些处理。
·如有必要重复步骤3，4。
·使用Close语句关闭该游标，然后释放大部分的资源（内存，锁等）。
·使用Deallocate语句清除该游标。

下面的示例用@@FETCH_STATUS控制在一个WHILE循环中的游标活动

每执行一个FETCH操作之后，通常都要查看一下全局变量@@FETCH_STATUS中的状态值，以此判断FETCH操作是否成功。该变量有三种状态值：
 0   表示成功执行FETCH语句。
-1   表示FETCH语句失败，例如移动行指针使其超出了结果集。
-2   表示被提取的行不存在。

由于@@FETCH_STATU是全局变量，在一个连接上的所有游标都可能影响该变量的值。因此，在执行一条FETCH语句后，必须在对另一游标执行另

一FETCH 语句之前测试该变量的值才能作出正确的判断。

-----------------------------------------------------------
/* 使用游标读取数据的操作如下。*/

DECLARE E1cursor cursor       /* 声明游标，默认为FORWARD_ONLY游标 */

FOR SELECT * FROM c_example

OPEN E1cursor                 /* 打开游标 */

FETCH NEXT from E1cursor      /* 读取第1行数据*/

WHILE @@FETCH_STATUS = 0      /* 用WHILE循环控制游标活动 ，@@fetch_status表示当前游标的状态*/

BEGIN

   FETCH NEXT from E1cursor    /* 在循环体内将读取其余行数据 */

END

CLOSE E1cursor                /* 关闭游标 */

DEALLOCATE E1cursor           /* 删除游标 */
-----------------------------------------------------------

语法
-----------------------------------------------------------
Declare @curTables Cursor
Set @curTables=Cursor FOR
 select name
  from sysobjects
  where xType='U'
-----------------------------------------------------------
Fetch Next From @curtables
Into @chvTable

While(@@fetch_status=0)
begin
 ......
end
-----------------------------------------------------------

函数的使用
函数是用来对0个或多个输入参数进行求值并返回数据给调用者的元素。
SELECT Sin(45) --显示45度正弦值
GetDate()  --调系统时间
-----------------------------------------------------------
Select @chvMake=Make,   --局部变量
       @Model=Model,    --局部变量
       @dtsCurrentDate=GetDate()  --将函数值赋给局部变量
from EquipmentID=@intEqId  
-----------------------------------------------------------
Create Procecure PRP
 @intValue01 int,
 @intValue02 int
As
 Insert Lease(Lea,Sta,EndDate,LeaF)
 Values(@intValue01,GetDate(),DateAdd(Year,3,GetDate()),@intValue02)   --DateAdd(...)截止日期为当前日期3年后
return @@Error
-----------------------------------------------------------

INNER JOIN 组合两个表中的记录，只要在公共字段之中有相符的值。

用 INNER JOIN语法联接多个表建记录集 多表联接建立记录集是十分有用的，因为某些情况下，我们需要把数字数据类型显示为相应的文本名

称，这就遇到了多表联接建立记录集的问题。

INNER JOIN ……ON的语法格式：

    FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON 表1.字段

号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号
   
    连接两个数据表的用法：
    FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort
    语法格式可以概括为：
    FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
   
    连接三个数据表的用法：
    FROM (Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON

Member.MemberLevel=MemberLevel.MemberLevel
    语法格式可以概括为：
    FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

系统函数

·Case 条件表达式

select @insDays=
CASE @chv1
  When 'monthly' then 30
  When 'bi-weekly' then 40
END
return

获取数据信息函数
·IsDate() 是否是合法日期的函数，真值为1。
·IsNumeric() 确定是否可以将一个字符值或表达式转换为某一数字数据类型。
·DateLength()返回存储或显示一个表达式所用的字节数。
·Binary_CheckSum()对指定表达式或表列集的二进制校验和进行计算，用于检测在记录中所做的修改。

处理空值的函数
·NullIf(expression,expression)如果函数中两个表达式的值相等，那么NullIf()将返回空值。如果两个表达式的值不相等，该函数将返回第

一个表达式的值。
-------------------------------------------------------
select AVG(Rent)                --计算平均值的时候仅包括非空的值。
       AVG(NullIf(Rent,0))   --计算平均值的时候包括空值。
from Inventory
-------------------------------------------------------

·IsNull(check_expression,replacement_value)对check_expression进行检测，如果值为空，函数将返回replacement_value。如果

check_expression值非空，该函数将返回check_expression。
-------------------------------------------------------
select AVG(Rent)
       AVG(ISNULL(Rent,0))
from Inventory
-------------------------------------------------------

·Coalesce(expression[,...n])函数经常用于合并被拆分到若干列中的各个值。返回结果是第一个非空表达式。(盲点不是很明白)

转换函数：显示的将某一种数据类型的信息转换成另一种特定的数据类型信息。
·Cast(expression AS data_type)    不允许指定返回结果的格式
Cast(@@error as varchar)

·Convert(data_type[(length)],expression[,style]) 允许指定返回结果的格式。style意指将日期和时间或数字表达式转换为字符串时所用

的格式化样式。
Convert(varchar,GetDate(),104)

style数字在转换时间时的含义如下:
------------------------------------------------------------------------------------------------------------
Style(2位表示年份)               |   Style(4位表示年份)             |    输入输出格式                             
------------------------------------------------------------------------------------------------------------
0                                |   100                            |    mon dd yyyy hh:miAM(或PM)             
------------------------------------------------------------------------------------------------------------
1                                |   101     美国                   |    mm/dd/yy                                      
------------------------------------------------------------------------------------------------------------
2                                |   102     ANSI                   |    yy-mm-dd                                       
------------------------------------------------------------------------------------------------------------
3                                |   103     英法                   |    dd/mm/yy                                      
------------------------------------------------------------------------------------------------------------
4                                |   104     德国                   |    dd.mm.yy                                       
------------------------------------------------------------------------------------------------------------
5                                |   105     意大利                 |    dd-mm-yy                                       
------------------------------------------------------------------------------------------------------------
6                                |   106                            |    dd mon yy                                       
------------------------------------------------------------------------------------------------------------
7                                |   107                            |    mon dd,yy                                       
------------------------------------------------------------------------------------------------------------
8                                |   108                            |    hh:mm:ss                                        
------------------------------------------------------------------------------------------------------------
9                                |   109                            |    mon dd yyyy hh:mi:ss:mmmmAM(或PM)
------------------------------------------------------------------------------------------------------------
10                               |   110     美国                   |    mm-dd-yy                                        
------------------------------------------------------------------------------------------------------------
11                               |   111     日本                   |    yy/mm/dd                                       
------------------------------------------------------------------------------------------------------------
12                               |   112     ISO                    |    yymmdd                                          
------------------------------------------------------------------------------------------------------------
13                               |   113     欧洲默认值             |    dd mon yyyy hh:mi:ss:mmm(24小时制) 
------------------------------------------------------------------------------------------------------------
14                               |   114                            |    hh:mi:ss:mmm(24小时制)                   
------------------------------------------------------------------------------------------------------------
20                               |   120     ODBC 规范              |    yyyy-mm-dd hh:mi:ss(24小时制)        
------------------------------------------------------------------------------------------------------------
21                               |   121                            |    yyyy-mm-dd hh:mi:ss:mmm(24小时制)
 
将货币值转换为字符信息时的含义如下：
0（默认值） 小数点坐侧每三位数字之间不用逗号分隔，小数点右边取两位 如1234.56
1     小数点坐侧每三位数字之间用逗号分隔，小数点右边取两位 如1,234.56
2           小数点坐侧每三位数字之间不用逗号分隔，小数点右边取四位 如1234.5678

将浮点数或实型数转换为字符信息：
0（默认值） 最大为6位数
1     始终为8位
2     始终为16位

当前会话信息：函数返回和当前会话有关的信息

App_Name() 返回打开会话的应用程序名称。
Host_Id() 返回工作站表识号。
Host_Name() 返回工作站名称。
Permissions() 返回位图的值，表明选定列，数据库对象或当前数据库权限。
Current_User 返回数据库用户名。
Session_User 返回当前会话的数据库用户名。
System_User 返回当前会话的服务器登陆名。
User_Name() 返回数据库用户名。

比如使用System_User函数表识往系统中添加订单的用户。
if @chvUserName=null
  Set @chvUserName=system_User

标识值处理函数
Ident_Seed("expression") 返回种子值
Ident_Incr("expression") 返回表的增量值

@@identity 返回当前会话的标识值，无论是否同一作用域（同一个存储过程，触发器或批处理就是同一作用域）
Ident_Current("expression") 返回任何作用域，在指定表中设置的最后一个标识值
Scope_Identity() 返回当前会话同一作用域的标识值。参见第9.5节

日期和时间函数
GetDate()
DAY(date)
MONTH(date)
YEAR(date)
DatePart(datepart,date)  /datepart为year,quarter,month,dayofyear,day,week,weekday,hour,minute,second,millisecond
DateName(datepart,date)  /datepart为year,quarter,month,dayofyear,day,week,weekday,hour,minute,second,millisecond

比如：
Select GetDate() As 'Date',
       DateName(month,GetDate()) As 'Month Name',
       DatePart(month,GetDate()) As 'Month',
       DatePart(year,GetDate()) As 'Year'

Date               Month Name      Month     Year
-----------------------------------------------------
2003-02-20 00:45   February        2         2003

字符串函数
Len(string_expression)返回字符串长度
DateLength()系统函数，返回字符串占用的字节数
Left(character_expression,integer_expression) 返回从字符串左边起指定的字符个数
Right(character_expression,integer_expression) 返回从字符串右边起指定的字符个数
SubString(expression,start,length)从start个字符开始，长度为length的字符串