|
第七讲 数据库编程基础
一、批处理
SQL Server可以处理作为一批而提交的多个SQL语句，既可以是交互式的，也可以是一个文件。批处理SQL语句由批结束标志终止，该标志指示SQL Server从前面开始执行该批处理语句，对于独立的SQL实用程序isql而言，其批结束标志为单独占一行的“go”。
举例：选择表title及表authors的行数
select count(*) from titles
select count(*) from authors
go
二、流程控制语言
1．变量声明与赋值
全局变量由系统预定义，以符号@@打头。
局部变量声明使用Declare语句，这个变量必须以符号@开头，后跟一个标识符。
Declare @变量名 数据类型[，@变量名 数据类型，……]
变量赋值使用Select语句，未赋值的变量其值为Null。
举例：
Declare @msg char(50)
Select @msg=’How are you?’
Select @msg=emp_name from employee
Where emp_id=12345678
2．SQL语句块
Begin
Statement Block/*多个顺序执行的SQL 语句*/
End
3．条件语句
If 条件表达式
语句（块）
Else
语句（块）
举例：
if(select max(id) from sysobjects)<50
print ‘数据库里没有用户创建的对象‘
else
select name,type,id from sysobjects where id>50
4．循环语句
While 条件表达式
语句（块）
●两个特殊的循环控制语句：
Continue 执行下一次循环
Break 退出当前循环
举例：
While(select avg(price) from titles)>$20
Begin
Update titles set price=price/2
If(select avg(price) from titles)<$40
Break
Else
Continue
End
5．其它控制语句
◇Return语句——无条件结束当前过程，并可返回给调用者的一个状态值：Return[整数表达式]
◇Print语句
◇RaiseError语句
◇Waitfor语句
三、存储过程
存储过程是存储在服务器端的一类数据库对象，它实质上是一段用SQL语言编写的程序，它在服务器端预先经过编译，并确定出执行计划，因此与同样功能的批处理语句相比，它的执行速度较快。
基本语法：
Create Procedure[owner.]过程名
[@参数名 数据类型[=默认值][Output>
[，@参数名 数据类型[=默认值][Output>
[……]
AS
Begin
SQL语句（块）
End
存储过程是数据库对象，和表、索引是一个级别的；是SQL语句和控制流语言的集合，存储过程在首次运行时被编译，并驻留在过程高速缓存的内存中，所以存储过程的招待非常快。存储过程可以带参数，可以调用其他过程，返回状态值，返回参数值，并且可以在远程SQL Server执行。可以在远程SQL Server执行对数据库设计有特别重要的意义。SQL Server提供的存储过程称为系统过程。
存储过程大大增强了SQL的能力、效率和灵活性，经过编译的存储过程极大地改善SQL语句和批处理的性能。
存储过程有很多优点：
●存储过程在第一次执行时编译，并存储在过程高速缓存的内存中。编译时系统对其进行优化，以选择最佳的路径来访问数据集中的数据，这种优化考虑了数据集的实际数据结构。因此存储过程大大提高了系统的性能。
●存储过程可以跨服务器运行。这一点是通过触发器来实现的，当然，首先存储过程要能登录到该远程服务器。
●应用程序也能执行存储过程，从而实现服务器和客户之间的协同作业。
●存储过程减少了网络的交通。这是因为存储过程的文本存储在数据库里，调用存储过程时通过网络的只是存储过程的过程名。
●利用存储过程可以提供一个附加的安全层。
如（该例子取自pubs2数据库）：
Create proc titleid_proc(@title_id varchar(80))
As
Begin
Select @title_id=lower(@title_id)+”%”
Select title,title_id,price
Form titles
Where lower(title_id) like @title_id
Return @@rowcount
End
注意例子中的黑体部分，这实际上是一条赋值语句。该存储过程有返回值。
存储过程可以变得非常复杂。我们认为，创建存储过程还是要遵循“最简单就是最好”的原则。建议在创建存储过程时采用缩进风格，否则创建的存储过程三天之后连自己都看不懂。
需要对存储过程作些说明：
●Create procedure 语句不能和其他语句在同一个批命令里。
●Create procedure 语句不能包括下列语句：
use
Create View
Create default
Create rule
Create trigger
Create procedure
不能使用use语句好理解，存储过程是针对数据库的，不能在一个数据库里访问另外的数据库。如果在存储过程里访问另外的数据库，则数据库表的参照完整性难于得到保障。
从另外几条语句看，在存储过程里一般不能创建新的数据库对象。但可以创建表和索引，以及和表相关联的键，表是临时表，在存储过程结束后不能看见创建的临时表；否则的话每运行一次存储过程就创建一个表，结果可想而知。
存储过程里不能创建一个对象，删除它；然后又在同一存储过程里用相同的名字创建新的对象。实际上，SQL Server在存储过程运行时而不是在编译时创建对象的。
●如果存储过程调用另外的存储过程，则第二个存储过程可以调用在第一个存储过程里创建的对象。
●存储过程包含的最多参数为255个，对存储过程里的局部和全局变量没有限制。
最后讨论一下系统存储过程。系统存储过程以sp_开头，当然用户创建的存储过程也可以以sp_开头；系统过程保存在sybsystemprocs数据库里。系统过程的使用有权限，如果打入系统过程名但没有出现预期的结果，要么是命令名错，要么是使用者没有该过程的权限。一般可通过系统管理员或数据库所有者对系统过程的execute授权。
系统过程繁多，大致有几类：
a. 用户标志和授权。这一类的过程主要由于：增加、删除或报告在SQL Server上的登录，增加、删除或报告某数据库的用户、分组或别名等。这类过程有sp_addlogin,sp_adduser,sp_helpgroup,sp_dropuser等。
b. 远程过程的调用。这类过程用于：增加、删除或报告能存取本SQL Server的远程服务器；增加能从远程服务器上存取本SQL Server的用户名。这类过程有：sp_addremotelogin,sp_addserver,sp_dropserver等。
c. 数据定义和数据库对象。这类存储过程用于：连接和定义规则和缺省值，增加、删除或报告主码、外码和公共码；增加、删除或报告用户定义的数据类型。这类存储过程有：sp_bindfault, sp_bindrule, sp_help, sp_helpdb, sp_foreignkey, sp_helptext等。
d. 系统管理。这类存储过程用于：增加、删除或报告数据库及转储设备；报告锁；设置的数据库选择及用户正进行的进程；修改及报告配置变量；监控SQL Server的活动。这类过程有：sp_addumpdevice，sp_dropdevice, sp_helpdevice等。
四、触发器
触发器是一种用来保障参照完整性的特殊的存储过程，它维护不同表中数据间关系的有关规则。当对指定的表进行某种特定操作（如：Insert,Delete或Update）时，触发器产生作用。触发器可以调用存储过程。
创建触发器的语法：
Create Trigger[owner.]触发器名
On [owner.]表名
For {insert,update,delete}
As
Begin
SQL语句（块）
End
定义一个好的触发器对简化数据的管理，保证数据库的安全都有重要的影响。触发器是针对表一级的，这就意味着，只有表的所有者有权创建表的触发器。
举例：
插入一个新行，必须保证外键与主键相匹配，触发器应该首先检查被插入行与主键表的连接。
以下的触发器对inserted表和titles表的title_id进行比较，这里假设正在给外键输入数据，没有插入空值，若连接失败，事务被回退。insert,update,delete
Create trigger forinsertrigl
On salesdetail
For insert
As
If(select count(*)
From title,inserted
Where titles.title_id=inserted.title_id)!=@@rowcount
Begin
Rollback transaction
Print “No,some title_id does not exist in titles.”
End
Else
Print “Added! All the title_id is exist in titles.”
在本例中，@@rowcount代表添加到salesdetail表的行数，这也是添加到inserted表中的行数。通过连接表titles和表inserted来检测所有添加到salesdetail的title_id是否在titles中存在。若所连接的行数（count(*)）与@@rowcount不同，由有一个或多个插入不正确，整个事务被取消。
触发器的限制：
●一个表最多只能有三个触发器，insert,update,delete
●每个触发器只能用于一个表
●不能对视图、临时表创建触发器
●Truncate table能删除表，但不能触发触发器
●不能将触发器用于系统表
合理地使用触发器对性能的影响是正面的。在设计和使用触发器时，经常地用sp_depends命令了解对象所关联的触发器是有好处的，该命令能列出触发器影响的所有对象、表和视等。
在定义几类数据库对象的时候，对存储过程、索引和触发器要给予特别的注意，尤其存储过程，它设计的好坏对数据库性能的影响很大。
说明：Sybase触发器使用的两个测试表：Deleted表和Inserted表，它们都是临时表，其结构与触发器的基表结构相同，用来存放与修改相关的数据行。
五、游标
1．游标的概念
游标是指向查询结果集的一个指针，它是一个通过定义语句与一条Select语句相关联的一组SQL语句。游标包含两方面的内容：
●游标结果集：执行其中的Select语句所得到的结果集；
●游标位置：一个指向游标结果集内的某一条记录的指针
利用游标可以单独操纵结果集中的每一行。游标在定义以后存在两种状态：关闭和打开。当游标关闭时，其查询结果集不存在；只有当游标打开时，才能按行读取或修改结果集中的数据。
2．使用游标
一个应用程序可以使用两种类型的游标：前端（客户）游标和后端（服务器）游标，它们是两个不同的概念。
无论使用哪一种游标，都需要经过如下几个步骤：
●定义游标
●打开游标
●从游标中操作数据
●关闭游标
下面讲述的是后端（服务器）游标。
（1）定义游标
在使用游标之前必须声明它。声明指定定义游标结果集的查询。通过使用for update或for read only关键词将游标显式定义成可更新的或只读的。
Declare cursor语法为：
For select_statement
[for{read only|update[of colum_name_list]}]
举例：
declare pubs_crsr cursor
for select pub_name,city,state
from publishers
for update of city,state
（2）打开游标
open的语法为：
open 游标名
在声明游标后，必须打开它以便用fetch,update,delete读取、修改、删除行。在打开一个游标后，它将被放在游标结果集的首行前，必须用fetch语句访问该首行。
（3）从游标中读取数据
在声明并打开一个游标后，可用fetch命令从游标结果集中获取数据行。
Fetch的语法为：
Fetch 游标名[into 变量列表]
举例：fetch pub_crsr into @name,@city,@state
SQL Server在每次读取后返回一个状态值。可用@@sqlstatus访问该值，下表给出了可能的@@sqlstatus值及其意义。
值意 义
0Fetch语句成功
1Fetch语句导致一错误
2结果集没有更多的数据，当前位置位于结果集最后一行，而客户对该游标仍发出Fetch语句时。
若游标是可更新的，可用update和delete语句来更新和删除行。
删除游标当前行的语法为：
delete[from]表名
where current of 游标名
举例：delete from authors where current of authors_crsr
当游标删除一行后，SQL Server将游标置于被删除行的前一行上。
更新游标当前行的语法为：
update 表名
set column_name1={expression1|NULL|(select_statement)}
[,column_name2={expression2|NULL|(select_statement)}
[……]
where current of 游标名
举例：
update publishers
set city=”Pasadena”,state=”CA”
where current of pubs_crsr
（4）关闭游标
当结束一个游标结果集时，可用close关闭。该语法为：
close 游标名
关闭游标并不改变其定义，可用open再次打开。若想放弃游标，必须使用deallocate释放它，deallocater的语法为：
deallocater cursor 游标名
deallocater语句通知SQL Server释放Declare语句使用的共享内存，不再允许另一进程在其上执行Open操作。
六、事务——维持数据一致性和恢复
1．事务的定义
事务提供了一种将T-SQL语句分组的方法，从而使它们能够被当成一个单元来处理：组中所有语句或都执行，或都不执行。
事务是确保一个或多个SQL语句的集合不被当成单一工作单元处理的机制，SQL Server自动将所有数据修改命令，包括单步改变请求，作为事务处理，缺省时，每个insert、update、delete语句被当成一个事务处理。
2．事务的作用
事务使SQL Server能保证：
•一致性——同时发生的查询或改变请求彼此不冲突，用户不能对正处于改变过程的数据进行查看或操作。
•恢复——在系统失效时，数据库的恢复是完全的和自动的。
3．使用事务
a. 开始和确认事务
begin transaction和commit transaction可将任意数目的SQL语句封装起来，这两名的简单语法为：
begin transaction
commit transaction
b. 回退事务
在commit transaction命令提交前任何时候可取消或回退事务，该命令的简单语法为：
rollback transaction
4．检查事务状态
全局变量@@transtate记录了事务当前的状态。在执行一个语句后SQL Server通过记录所有事务变化来确定返回何种状态。
@@transtate可包含下列值：
值意 义
0事务进行中：一个显式或隐式事务有效；上一语句执行成功
1事务完成：事务完成并提交其变化
2语句异常中止：上一语句异常终止；对事务无影响
3事务异常中止：事务异常中止并回退所有变化
举例：在事务中，可在一个语句（如insert）后使用@@transtate确定该语句成功或失败对事务的影响。commit transaction
Begin transaction
Insert into publishers(pub_id)values(‘9999’)
(1 row affected)
select @@transtate
…………………
0
commit transaction
select @@transtate
…………………
0
(1 row affected)
[SyBase]sybase基础知识(5)
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
