SQL主要内容(二)

一、SQL编程
1、局部变量
   在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。
   局部变量必须以@ 开头，而且必须先用DECLARE 命令说明后才可使用。
   使用SELECT 或SET 命令来设定变量的值
   说明形式如下：
   DECLARE @变量名 变量类型 [ @变量名 变量类型]
    SELECT @局部变量 = 变量值
    SET @局部变量= 变量值
   如：
  declare @name char 30 @wage money
   select @name = e_name @wage = e_wage from employee where emp_id = '10010001'

2、BEGIN…END
  BEGIN
   <命令行或程序块>
   END
   BEGIN…END 用来设定一个程序块,将在BEGIN…END 内的所有程序视为一个单元执行。BEGIN…END 经常在条件语句如IF…ELSE 中使用。
  
3、IF…ELSE…
IF <条件表达式>
    <命令行或程序块>
   [ELSE IF [条件表达式]
    <命令行或程序块>]
   如：
   declare @x int @y int @z int
    select @x = 1 @y = 2 @z=3
    if @x > @y
     print 'x > y' --打印字符串'x > y'
    else if @y > @z
     print 'y > z'
    else print 'z > y'

4、CASE
  CASE <运算式>
   WHEN <运算式> THEN <运算式>
   WHEN <运算式> THEN <运算式>
   [ELSE <运算式>]
   END
   如：
  update employee
   set e_wage =
   case
   when job_level = ’1’ then e_wage*1.08
   when job_level = ’2’ then e_wage*1.07
   when job_level = ’3’ then e_wage*1.06
   else e_wage*1.05
   end
  
5、WHILE…CONTINUE…BREAK
  WHILE <条件表达式>
   BEGIN
    <命令行或程序块>
    [BREAK]
    [CONTINUE]
    [命令行或程序块]
   END
   如：
   declare @x int @y int @c int
   select @x = 1 @y=1
   while @x < 3
   begin
    print @x --打印变量x 的值
    while @y < 3
    begin
     select @c = 100*@x + @y
     print @c --打印变量c 的值
     select @y = @y + 1
    end
    select @x = @x + 1
    select @y = 1
   end

6、WAITFOR
   WAITFOR 命令用来暂时停止程序执行,直到所设定的等待时间已过或所设定的时间已到才继续往下执行。DELAY 用来设定等待的时间最多可达24 小时
   其中时间必须为DATETIME 类型的数据
  WAITFOR {DELAY <‘时间’> | TIME <‘时间’>| ERROREXIT | PROCESSEXIT | MIRROREXIT}
   TIME     用来设定等待结束的时间点
    ERROREXIT    直到处理非正常中断
    PROCESSEXIT    直到处理正常或非正常中断
    MIRROREXIT    直到镜像设备失败
   如：
    等待1 小时2 分零3 秒后才执行SELECT 语句
    waitfor delay ’01:02:03’
     select * from employee
    等到晚上11 点零8 分后才执行SELECT 语句
    waitfor time ’23:08:00’
     select * from employee

二、存储过程
存储过程是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行它。
创建存储过程
  CREATE PROC [ EDURE ] procedure_name [ ; number ]
   [ { @parameter data_type }
   [ VARYING ] [ = default ] [ OUTPUT ]
   ] [ ,...n ]
   [ WITH
   { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
   AS
   sql_statement [ ...n ]
如：
if exists select name from sysobjects where name=’author_infor’ and type=’p’
   drop procedure author_infor
   go
   create procedure author_infor
   as
    select au_lname, au_fname, title, pub_name
    from authors a inner join titleauthor ta
    on a.au_id=ta.au_id inner join titles t
    on t.title_id=ta.title_id inner join publishers p
    on t.pub_id=p.pub_id
   go

修改存储过程的名字使用系统存储过程sp_rename
  sp_rename 原存储过程名, 新存储过程名
删除存储过程
DROP PROCEDURE {procedure}} [,…n]
修改存储过程
  ALTER PROC[EDURE] procedure_name [;number]
   [ {@parameter data_type } [OUTPUT]] [,...n]
   [WITH
   {RECOMPILE | ENCRYPTION}]
   AS
   sql_statement [...n]

三、触发器
触发器是一种特殊的存储过程。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，另外还有强化约束和级联运行的功能。
inserted,deleted临时表
   这两个表是由系统管理的，存储在内存中，不是存储在数据库中，因此不允许用户直接对其修改，是只读的。
   这两个表的结构总是与被该触发器作用的表有相同的表结构。
   这两个表是动态驻留在内存中的，当触发器工作完成，这两个表也被删除。
   这两个表主要保存因用户操作而被影响到的原数据值或新数据值。
inserted表的的触发器插入功能
   对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入操作，那么对向该表插入的所有行来说，都有一个相应的副本存放到插入表中，即插入表就是用来存储向原表插入的内容。
deleted表的触发器删除功能
   对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除操作，则将所有的删除行存放至删除表中，这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表中得以恢复。
inserted,deleted临时表的触发器更新功能
   更新操作包括两个部分，在deleted中存放了旧值，然后在inserted中存放新值。
   插入操作，在inserted中存放新值。
   删除操作，在deleted中存放旧值
触发器的种类
   AFTER 触发器
   INSTEAD OF 触发器
创建触发器
  CREATE TRIGGER trigger_name
   ON { table | view }
   [ WITH ENCRYPTION ]
   {{ { FOR | AFTER | INSTEAD OF }
   { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
   [ WITH APPEND ][ NOT FOR REPLICATION ]
   AS
   sql_statement [ ...n ]
   }
触发器举例
  create trigger trg_di_titles
   on titles
   for delete,update
   as sql_statements
   return
  
INSTEAD OF 触发器的主要优点是使不可被修改的视图能够支持修改。

四、索引
为了提高检索数据的能力，数据库引入了索引机制。
一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引，页面索引就存放在索引页面上。通常索引页面相对于数据页面来说小得多，当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针再直接通过指针从数据页面中读取数据。
在SQL Server 的数据库中按存储结构的不同将索引分为两类
   簇索引   ClusteredIndex
   非簇索引 Nonclustered Index
簇索引
   簇索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，
   即簇索引与数据是混为一体的，因此用簇索引查找数据很快。
   但由于簇索引将表的所有数据完全重新排列了，它所需要的空间也就特别大。
   表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个簇索引。
非簇索引
   非簇索引具有与表的数据完全分离的结构。
创建索引
  CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
   INDEX index_name ON {table | view } (column [ ASC | DESC ] [,...n])
如：为表products 创建一个簇索引
   create unique clustered index pk_p_id
    on products(p_id)
删除索引：drop index

五、视图
视图是从一个或多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。
所以视图不是真实存在的基础表，而是一张虚表视图，所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。通过视图看到的数据只是存放在基本表中的数据
创建视图
  CREATE [ < owner > ] VIEW view_name [ ( column [ ,...n ] ) ] [with encryption]
   AS
   select_statement
   如：
  create view emprange (emp_id, fname, lname, pubid, job_id, rows)
   with encryption
   as
    select emp_id, fname, lname, pub_id , job_id, @@rowcount
    from employee
    where job_id between 11 and 12
删除视图
    DROP VIEW 视图名称
使用视图所产生的问题
  问题一：
   首先创建一个视图
   create view castate (au_id, au_lname, au_fname, phone, address, city, state, contract)
   as
    select au_id, au_lname, au_fname, phone, address, city, state, contract
    from authors
    where state = ’ca’
   执行以下语句向视图中插入数据记录
   insert into castate
   value (‘234-34-4512’, ‘John’, ‘Smith’, ‘415 548-7723’,’ 18 Broadway Av.’,’ Gary’, ‘IN’,1)
   虽然仍可以成功执行，但只可以在表而不是视图中查到该条数据。
  
  问题二：
   视图引用了多个表
   create view au_title (author_au_id, au_lname, au_fname, contract, title_au_id, title_id, au_ord ,royaltyper)
   as
   select a.au_id, a.au_lname, a.au_fname, a.contract, t.au_id, t.title_id, t.au_ord , t.royaltyper
   from authors a, titleauthor t
   where a.au_id = t.au_id
   通过视图对数据进行更新与删除时，需要注意到两个问题
   执行UPDATE DELETE 时，所删除与更新的数据必须包含在视图结果集中。
   如果视图引用多个表时，无法用DELETE 命令删除数据，若使用UPDATE 则应与INSERT 操作一样被更新的列必须属于同一个表。

六、游标
关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式。
如果要从某一结果集中逐一地读取一条记录，那么如何解决这种问题呢？游标为我们提供了一种极为优秀的解决方案。
游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制
声明游标：
   每一个游标必须有四个组成部分，这四个关键部分必须符合下面的顺序：
1.DECLARE 游标
   2.OPEN 游标
   3.从一个游标中FETCH 信息
   4.CLOSE 或DEALLOCATE 游标
语法格式如下
  DECLARE cursor_name [SCROLL] CURSOR
   FOR select_statement
   [FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]
   SCROLL
   表明所有的提取操作，如FIRST LAST PRIOR NEXT RELATIVE ABSOLUTE都可用，如果不使用该保留字那么只能进行NEXT 提取操作
标准游标
declare cur_authors cursor
   for
   select au_id, au_lname, au_fname, phone, address, city, state, contract
   from authors
只读游标
  declare cur_authors cursor
   for
   select au_lname, au_fname, phone, address, city, state
   from authors
   for read only
更新游标
  declare cur_authors cursor
   for
   select au_lname, au_fname
   from authors
   for update
打开游标
OPEN cursorname
读取游标中的数据
  FETCH
   [ [ NEXT | PRIOR | FIRST | LAST
   | ABSOLUTE {n | @nvar}
   | RELATIVE {n | @nvar} ]
   FROM ]
   {cursor_name}
   [INTO @variable_name[,...n] ]

@@FETCH_STATUS 全局变量返回上次执行FETCH 命令的状态
   0    FETCH 命令被成功执行
   -1    FETCH 命令失败或者行数据超    过游标数据结果集的范围
   -2    所读取的数据已经不存在
  
关闭游标语法格式：
  CLOSE cursorname
释放游标语法格式：
DEALLOCATE

举例
  declare titleauthor_cur cursor scroll for
   select * from titleauthor
   open titleauthor
   go
/*用游标变量引用已声明过的游标*/
   declare @cur_ta1 cursor
   set @cur_ta1 = titleauthor_cur
   /*现在释放对游标的引用*/
   deallocate @cur_ta1
/*游标titleauthor_cur 仍旧存在*/.
   fetch next from titleauthor_cur
   go
   /*再引用游标*/
   declare @cur_ta2 cursor
   set @cur_ta2 = titleauthor_cur
/*释放titleauthor_cur 游标*/.
   deallocate titleauthor_cur
/*由于游标被@cur_ta2 引用所以仍旧存在*/
   fetch next from @cur_ta2
/*当最后一个游标变量超出游标作用域时游标将被释放*/
   go
   declare @cur_ta cursor
   set @cur_ta = cursor local scroll for
   select * from titles
  /*由于没有其它变量对其进行引用所以游标被释放*/
   deallocate @cur_ta
   go