t-sql编程

ningleesherry

T-SQL语言学习（编程篇） 2008
T-SQL数据库编程基础：

    1。数据类型：
    2。变量：
    3。语句：
    4。函数：
    5。游标：
    6。存储过程：
    7。触发器：
    8。会话，事务，锁


======================================================
1。数据类型：


======================================================
2。变量
    a.全局变量
    b.局部变量

======================================================
3。语句

    #流程控制语句
        a.程序块语句：BEGIN...END
        b.判断语句:IF...ELSE
        c.检测语句:IF EXISTS
        D.循环语句:WHILE
        E.分支判断语句:CASE
        F.无条件退出语句:RETURN
        G.延期执行语句:WAITFOR
        H.跳转语句:GOTO
        I.输出语句:PRINT
        J.错误处理语句:RAISEERROR

    #功能性语句
        A.PRINT--输出信息语句
        B.PAISEERROR--错误处理语句
        C.SET--选项设置语句
======================================================
4。函数

    a.系统函数
    b.常用函数：聚合函数，数字函数，字符串函数，转换函数
    c.自定义函数

======================================================
5。游标

可以理解为一个在结果集中可以逐行移动的指针，它可以指向结果集中的任意位置，允许用户对指定位置的数据进行处理，但不能通过游标向结果集中插入数据。
在开发中，一般将游标作为逐行提取结果集数据的手段进行使用。
游标主要用在存储过程、触发器和 Transact-SQL 脚本中，它们使结果集的内容对其它 Transact-SQL 语句同样可用。

游标有严格的生命周期：
A.声明(declare),定义游标的名称，类型和属性.
b.调用(open),打开和填充数据。
c.读取(fetch),从结果集中读取单行的数据。
d.执行(update,delete),如果需要,可以使用update或者Delete语句对当前数据进行修改，并将修改反馈回游标来源的基表.
e.关闭(close),停止游标使用的查询，但并不删除游标定义，可以使用open语句再次打开游标。
f.释放(deallocated),删除游标，并释放其占用的所有资源。



例：写法：
cursor cursor_name is
select column_name1,column_name2 from table_name where column_namen='XXXX';
在程序使用时这样调用：
for v_var in cursor_name loop
begin
.......;
end loop;


节选自在线代码CursorLoop.sql
DECLARE
v_FirstName VARCHAR2(20);
v_LastName VARCHAR2(20);
-- Cursor declaration. This defines the SQL statement to
-- return the rows.
CURSOR c_Students IS
SELECT first_name, last_name
FROM students;
BEGIN
-- Begin cursor processing.
OPEN c_Students;
LOOP
-- Retrieve one row.
FETCH c_Students INTO v_FirstName, v_LastName;
-- Exit the loop after all rows have been retrieved.
EXIT WHEN c_Students%NOTFOUND;
/* Process data here */
END LOOP;
-- End processing.
CLOSE c_Students;
END;


======================================================
6。存储过程

存储过程的概念:

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，普通的存储过程是由命令语句执行的。

在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。


存储过程的主要优点:

以下有四点：

1、允许模块化程序设计。
只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。

2、允许更快执行。
如果某操作需要大量 Transact-SQL 代码或需重复执行，存储过程将比 Transact-SQL 批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。每次运行 Transact-SQL 语句时，都要从客户端重复发送，并且在 SQL Server 每次执行这些语句时，都要对其进行编译和优化。

3、减少网络流量。
一个需要数百行 Transact-SQL 代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。

4、可作为安全机制使用。
即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。

以上摘自sql server联机帮助，发现sql server的联机帮助真是不错，呵呵。



创建存储过程：
在SQL Server中，可以使用三种方法创建存储过程
①向导
②企业管理器
③使用T-SQL语句
1. 使用创建存储过程向导创建存储过程
在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导”，则出现欢迎使用创建存储过程向导对话框。根据图提示可完成创建存储过程。
2. 使用SQL Server 企业管理器创建存储过程
（１）在SQL Server企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程…”选项，如图7-8所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程…”选项，如图7-9所示。均会出现创建存储过程对话框，如图7-10所示。
（２）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限…”按钮，如图7-11所示。
3. 使用T-SQL语句中的CREATE PROCEDURE命令创建存储过程
创建存储过程前，应该考虑下列几个事项：
①不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。
②创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。
③存储过程是数据库对象，其名称必须遵守标识符规则。
④只能在当前数据库中创建存储过程。
⑤一个存储过程的最大尺寸为128M。
使用CREATE PROCEDURE语法形式如下：
CREATE PROC[EDURE] procedure_name
[{@parameter data_type}[=default]]
    [,...n]
    [WITH
    {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
AS
sql_statement [ ...n ]
用CREATE PROCEDURE参数的意义如下：
procedure_name：用于指定要创建的存储过程的名称。
@parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。
data_type：用于指定参数的数据类型。
Default：用于指定参数的默认值。
RECOMPILE：表明 SQL Server 不会保存该存储过程的执行计划。
ENCRYPTION ：表示 SQL Server 加密了 syscomments 表，该表的text字段是包含 CREATE PROCEDURE 语句的存储过程文本。
AS：用于指定该存储过程要执行的操作。
sql_statement：是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。


创建存储过程时，需要确定存储过程的三个组成部分：
①输入参数及输出参数。
②被执行的针对数据库的操作语句，包括调用其它存储过程的语句。
③返回给调用者的状态值，以指明调用是成功还是失败。


执行存储过程：
直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：
[EXEC]{procedure_name [@parameter=]{value}
[ WITH RECOMPILE ]


查看和修改存储过程：

1.查看存储过程
存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。
（1）使用企业管理器查看用户创建的存储过程
    在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。
（2）使用系统存储过程来查看用户创建的存储过程
可供使用的系统存储过程及其语法形式如下：
sp_help：用于显示存储过程的参数及其数据类型
sp_help [[@objname=] name]
参数name为要查看的存储过程的名称。
sp_helptext：用于显示存储过程的源代码
sp_helptext [[@objname=] name]
参数name为要查看的存储过程的名称。
sp_depends：用于显示和存储过程相关的数据库对象
sp_depends [@objname=]’object’
参数object为要查看依赖关系的存储过程的名称。
sp_stored_procedures：用于返回当前数据库中的存储过程列表

2. 修改存储过程
使用ALTER PROCEDURE语句可以更改先前通过执行 CREATE PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：
ALTERPROC[EDURE]procedure_name[;number]
  [{@parameterdata_type}
  [VARYING][=default][OUTPUT]][,...n]
[WITH
    {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
AS  sql_statement [...n]

重命名和删除存储过程：
1. 重命名存储过程
修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：
sp_rename  原存储过程名称，新存储过程名称
另外，通过企业管理器也可以修改存储过程的名称。
2. 删除存储过程
删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：
drop procedure {procedure} [,…n]
当然，利用企业管理器也可以很方便地删除存储过程。

例子:
在数据库（名为CPXS）中，包含如下表的信息：
产品表：产品编号（C  3），产品名称（C 20），价格（I），库存量（I）
销售商表：客户编号（C 3），客户名称（C 20），地区（C 20），负责人（C 10），电话（C 20）
产品销售表：销售日期（D），产品编号（C 3），客户编号（C 3），数量（I），销售额（I）
（其中，C代表varchar，后面数字表示字段大小；I代表int；D代表datetime）

在该数据库中，需要经常对指定日期的某种产品的销售总额进行统计，为了执行方便，现在需要在服务器端创建一个完成该功能的存储过程。请你运用SQL Server 2000的有关知识，设计一个相应的存储过程，过程名为cpxs_info，参数为指定的日期和产品名称。
 Use CPXS   
  go
    create procedure cpxs_info      @d datetime,@cname varchar(3)
    AS
       Select a.销售日期,sum(销售额)
       From  产品销售表 a join 产品表 on a.产品编号=b.产品编号
       Where a.销售日期=@d       and b.产品名称=@cname
    go
执行：EXEC  cpxs_info  ‘2004-1-5’,’电视机’

======================================================
7。触发器

触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。
触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而被直接调用。
触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制执行其业务规则。
触发器可以用于 SQL Server 约束、默认值和规则的完整性检查。
用来实现当对某表进行UPDATE、 INSERT、 DELETE 操作时，自动触发的处理方法。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。

触发器主要优点如下：
触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。
触发器可以通过数据库中的相关表进行层叠更改。
触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。


创建触发器：
创建触发器应该考虑以下几个问题：
①CREATE TRIGGER 语句必须是批处理中的第一个语句。
②创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。
③触发器为数据库对象，其名称必须遵循标识符的命名规则。
④虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
⑤虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。
⑥在含有用 DELETE 或 UPDATE 操作定义的外键的表中，不能定义 INSTEAD OF 和 INSTEAD OF UPDATE 触发器。
⑦虽然 TRUNCATE TABLE 语句类似于没有 WHERE 子句（用于删除行）的 DELETE 语句，但它并不会引发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。
⑧WRITETEXT 语句不会引发 INSERT 或 UPDATE 触发器。
⑨当创建一个触发器时必须指定：⑴名称；⑵在其上定义触发器的表；⑶触发器将何时激发；⑷激活触发器的数据修改语句。

1. 使用企业管理器创建触发器
在企业管理器中，展开指定的服务器和数据库项，然后展开要在其上创建触发器的表所在的数据库，用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在该对话框中，在名称文本框中选择新建，然后在文本框中输入触发器文本。单击“检查语法”按钮，则可以检查语法是否正确。单击“应用”按钮，则在名称下拉列表中会有新创建的触发器名称。最后，单击“确定”按钮，即可关闭该对话框，成功创建触发器。
2. 使用CREATE TRIGGER命令创建触发器
其语法形式如下：
CREATE TRIGGER trigger_name
ON{table|view}
[WITHENCRYPTION]
{
    { { FOR | AFTER | INSTEAD OF } { [DELETE][,][ INSERT ] [ , ] [ UPDATE ] }
    [WITHAPPEND]
    [NOTFORREPLICATION]
    AS
    [{IFUPDATE(column)
       [{AND|OR}UPDATE(column)]
        [...n]
    |IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)
        {comparison_operator}column_bitmask[...n]
    }]
    sql_statement[...n]
    }
}
例子:
创建了一个触发器，在 titles 表上创建一个插入、更新类型的触发器，其程序清单如下：
USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER reminder
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS sql_statements

查看、修改和删除触发器
1. 查看触发器
（１）使用企业管理器查看触发器信息
（２）使用系统存储过程查看触发器
sp_help、sp_helptext和sp_depends具体用途和语法形式如下。
 sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。
sp_help  ‘触发器名称’
 sp_helptext：用于查看触发器的正文信息
sp_helptext  ‘触发器名称’??????
 sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。
sp_depends  ’触发器名称’
sp_depends  ‘表名’
2. 修改触发器
（１）使用企业管理器修改触发器正文
    在企业管理器中，展开指定的服务器和数据库，选择指定的数据库和表，用右键单击要修改的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要修改的触发器的名称，然后在文本框中修改触发器的SQL语句，单击“检查语法”按钮，可以检查语法是否正确。
（２）使用sp_rename命令修改触发器的名称
sp_rename命令的语法形式如下：
sp_rename oldname,newname
（３）使用alter trigger命令修改触发器正文
alter trigger命令的语法形式如下：
ALTERTRIGGERtrigger_name
ON(table|view)
[WITHENCRYPTION]
{
    { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
    [NOTFORREPLICATION]
    AS
    sql_statement[...n]}
    | {(FOR|AFTER|INSTEADOF){[INSERT][,]UPDATE}}
    [NOTFORREPLICATION]
    AS
    {IFUPDATE(column)
    [{AND|OR}UPDATE(column)]
    [...n]
    | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
    {comparison_operator}column_bitmask[...n]}
    sql_statement[...n]
    }
}
3. 删除触发器
（１）使用系统命令DROP TRIGGER删除指定的触发器，其语法形式如下：
DROP TRIGGER { trigger } [ ,...n ]
（２）删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。
（３）在企业管理器中，用右键单击要删除的触发器所在的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要删除的触发器，单击“删除”按钮，即可删除该触发器。


======================================================
8。会话，事务和锁

    事务是一组DML语句的逻辑集合。


：Commit，Savepoint，Rollback

======================================================