数据库系统原理教案(2)

go

执行存储过程：

exec stu_info ?20060701?;

5、删除存储过程

删除存储过程的语句格式是： DROP PROCEDURE 过程名(); 例如，

DROP PROCEDURE stu_info (); 6、SQL Server中常用的系统存储过程

(1) sp_addlogin

创建新的 SQL Server 登录，该登录允许用户使用 SQL Server 身份验证连接到 SQL Server 实例。

sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt= ] 'encryption_option' ]

例如，为用户 Victoria 创建 SQL Server 登录，密码为 B1r12-36，并且不指定默认数据库。

EXEC sp_addlogin 'Victoria', 'B1r12-36';

例如，为用户 Albert 创建 SQL Server 登录，密码为 B5432-3M6，默认数据库为StudentDB

EXEC sp_addlogin 'Albert', '''B5432-3M6', StudentDB'; (2) sp_droplogin

删除 Microsoft SQL Server 登录，禁止以该登录名访问 SQL Server 实例。

sp_droplogin [ @loginame= ] 'login' sp_droplogin 调用 DROP LOGIN。

例如，从 SQL Server 实例中删除 Victoria 登录。

sp_droplogin ?Victoria?； (3) sp_adduser

向当前数据库中添加新的用户：

sp_adduser [ @loginame = ] 'login'

[ , [ @name_in_db = ] 'user' ] [ , [ @grpname = ] 'role' ]

例如，使用现有的 SQL Server 登录名 Vidur，将数据库用户 Vidur 添加到当前数据库中的现有 Recruiting 角色。

EXEC sp_adduser ?Vidur?, ?Vidur?, ?Recruiting? ；

例如，将用户 Arvind 添加到 SQL Server 登录名 Arvind 的当前数据库。该用户属于默认的 public 角色。

EXEC sp_adduser ?Arvind? ； (4) sp_dropuser

从当前数据库中删除数据库用户。

sp_dropuser [ sp_dropuser ] 'user'

? sp_dropuser 执行 sp_revokedbaccess 以从当前数据库中删除用户。 ? 使用 sp_helpuser 将显示一个可从当前数据库中删除的用户名的列表。 例如，从当前数据库中删除用户 Albert。

EXEC sp_dropuser 'Albert';

6

(5) sp_addrole

在当前数据库中创建新的数据库角色。

sp_addrole [ @rolename = ] 'role?

[ , [ @ownername = ] 'owner' ]

例如，向当前数据库中添加名为 Managers 的新角色。 EXEC sp_addrole ?Managers? ； (6) sp_droprole

从当前数据库中删除数据库角色：

sp_droprole [ @rolename= ] 'role'

? 使用 sp_droprole 只能删除数据库角色。

? 不能删除带有现有成员的数据库角色。必须删除数据库角色的所有成员，然后才能

删除该数据库角色。若要从角色中删除用户，请使用 sp_droprolemember。 ? 不能删除固定角色及 public 角色。 例如，删除应用程序角色 Sales。

EXEC sp_droprole 'Sales'; (7) sp_grantdbaccess

将数据库用户添加到当前数据库的语句格式：

sp_grantdbaccess [ @loginame = ] 'login'

[ , [ @name_in_db = ] 'name_in_db' [ OUTPUT ] ]

例如，将 Windows 登录名 Edmonds\\LolanSo 的数据库用户添加到当前数据库。新用户名为 Lolan。

EXEC sp_grantdbaccess Lolan FOR LOGIN [Edmonds\\LolanSo]; (8) sp_revokedbaccess

从当前数据库中删除数据库用户的语句格式：

sp_revokedbaccess [ @name_in_db = ] 'name'

例如，从当前数据库中删除映射到 Edmonds\\LolanSo 的数据库用户。

EXEC sp_revokedbaccess 'Edmonds\\LolanSo'; (9) sp_rename

在当前数据库中更改用户创建对象的名称，此对象可以是表、索引、列、别名、数据类型。其语句格式：

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ]

例如，将 SalesTerritory 表重命名为 SalesTerr。

EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';

例如，将 SalesTerritory 表中的 TerritoryID 列重命名为 TerrID。

EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN'; (10)sp_renamedb

更改数据库的名称的格式：

sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'

例如，创建 Accounting 数据库，然后将该数据库的名称更改为 Financial。然后，查询 sys.databases 目录视图以确认数据库的新名称。

CREATE DATABASE Accounting;

EXEC sp_renamedb 'Accounting', 'Financial';

SELECT name, database_id FROM sys.databases WHERE name = 'Financial';

7

5．4 触发器

数据库触发器是存放在数据库中的代码，由应用所产生的事件触发。触发器程序运行于数据库服务器上，由于不存在客户端与数据库服务器端的数据传输，因而有较好的执行性能。当用户对有数据库触发器的表执行某种操作时，就会触发对应的触发器工作，完成规定的任务。

触发器实际上就是具有特殊功能的能够自动执行的存储过程。SQL的触发器有3种类型：插入（INSERT）、更新（UPDATE）、删除（DELETE）。

触发器定义规定了触发器的特征和被调用时采取的行动。这些动作被规定在一个或多个SQL语句中（称作被触发SQL语句），可以包括如：更新表、删除数据、调用过程或执行在SQL语句中实现的更多任务。任何对这些语句的限制通常也就是SQL实现方式的限制。

就触发器的执行环境而言，是SQL的执行环境之一。这个执行环境创建在计算机内存中、在语句执行过程中保存语句进程的空间。

每当调用触发器时，就创建了触发器的执行环境。如果调用多个触发器，就会分别为每个触发器创建执行环境。但是，在任何时候，一个会话只有惟一的一个执行环境是活动的。

一个触发器执行环境包含了触发器正确执行所必需的信息，这些信息包括有关触发器本身的细节和触发器所定义的表，即目标表。此外，执行环境还包括一个或两个迁移表，迁移表是虚表，它保存对目标表插入、更新、删除的数据信息。如果更新数据，则创建两个迁移表，一个用于旧数据，一个用于新数据。如果插入数据，则创建一个迁移表来保存新数据。如果删除数据，则创建一个迁移表来保存旧数据。迁移表和触发器环境的信息是实现触发动作的SQL语句执行的依据。

触发器的功能主要表现在：

? 审核修改：可以检测和拒绝数据库中不允许的特定更新操作。

? 级联操作：可以检测通过对参照完整性的定义完成更新、删除操作中的级联处理。 ? 强制互联：可以强制执行比参照完整性更复杂的、通过定义的数据互联关系。 ? 日志管理：可以存储对数据库的增、删、改操作内容，建立数据库日志。 一、触发器的创建

创建触发器的一般语句格式是：

CREATE TRIGGER <触发器名> BEFORE | AFTER INSERT | DELETE | UPDATE [ OF <列名表> ]

ON <表名> [ REFERENCING {OLD [AS] old| NEW [AS] new}] [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN（触发条件）] <触发动作体> 说明：

① BEFORE 与 AFTER 确定触发器是在目标表中数据修改语句前（选择BEFORE）调用还是修改语句后（选择AFTER）调用。

② 触发事件：插入（INSERT）、删除（DELETE）、更新（UPDATE），对于更新，还可以通过<列名表>选择触发器应用到哪些列。

③ ON <表名>是定义触发器的表（目标表）。触发事件会导致记录数据的改变，REFERENCING子句是对引用数据的来源与数据迁移的描述，一般用NEW代表新值状态对应的记录，OLD代表旧值状态对应的记录，其中AS可以省略。注意：对INSERT操作来说，不存在“旧”值对应记录；对DELETE操作来说，不存在“新”值对应记录。

④ 触发器按照触发动作的间隔尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT），行级触发器表示每次插入、删除、更新一行就调用触

8

发器；而语句级触发器表示每个数据修改语句执行后调用一次，而不论影响到多少行。有的DBMS省缺此子句，表示定义的是语句级触发器。

例如，假设在2.1节SPJ数据库的零件表P上创建一个AFTER UPDATE触发器，若表中有1000条记录，执行如下SQL语句：

UPDATE P SET COLOR=?RED?; 如果该触发器为语句级触发器，那么执行完该语句后触发动作只发生一次，如果是行级触发器，则触发动作将执行1000次。

⑤ WHEN指明触发动作的条件，即触发事件发生后，应满足什么条件才执行该动作。 ⑥ 触发动作体确定触发器所应完成的对相关数据库表的操作，这些操作就是在定义触发器时要求实现的功能。触发动作体既可以是一个PL/SQL程序块，也可以是对已创建存储过程的调用。

MS SQL Server创建触发器的语句格式：

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {

{ { FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [, DELETE ] [, UPDATE ] }

[ WITH APPEND ] [ NOT FOR REPLICATION ] AS

sql_statement [ ...n ] } }

SQL Server支持两种类型的触发器：

? AFTER触发器：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行

后才激发，只可以建立在表上。

? INSTEAD OF触发器：代替了相应的触发事件而被执行，既可以建立在表上也可以

建立在视图上。当为表或视图定义了针对某一操作(INSERT、DELETE、UPDATE)的INSTEAD OF 类型触发器且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发器SQL 语句本身。INSTEAD-OF触发器在数据库引擎中可以取代数据操作语句而执行。

例如，在SPJ数据库的SPJ表上创建一个AFTER触发器，完成的功能是：在SPJ表上创建一个插入、更新类型的触发器SPJCheck，当在SPJ表的QTY字段中插入或修改供应数量后，触发该触发器，检查供应数量是否在100-1000之间。

Use SPJ Go

IF EXISTS(SELECT name FROM sysobjects WHERE name='SPJCheck' and type ='TR')

DROP TRIGGER SPJCheck Go

CREATE TRIGGER SPJCheck ON SPJ FOR INSERT, UPDATE AS IF UPDATE (QTY) PRINT 'AFTER触发器开始执行……' BEGIN

Declare @QTYValue real

SELECT @QTYValue=( SELECT QTY FROM INSERTED)

9

If @ QTYValue >1000 or @ QTYValue <100 Print '输入供应数量错误！' END Go

用下面的语句测试创建的触发器： Use SPJ Go

Print '在SPJ中插入记录时触发器的执行结果：' Insert into SPJ values ('S4', 'P1',?J1?,50) Insert into SPJ values ('S5', 'P2',?J3?,1350) Go

Print '在SPJ中修改记录时触发器的执行结果：'

Update SPJ set QTY =1150 where SNO='S2' and PNO='P2' and JNO=?J1? Update SPJ set QTY =60 where SNO='S2' and PNO='P3' and JNO=?J2? Go

又如，INSTEAD OF触发器的实例：INSTEAD OF 触发器主要优点是使不可被修改的视图能够支持修改，在视图中添加INSTEAD-OF触发器后，则可创建更新视图。

--创建表

CREATE TABLE Products

( ProductID SMALLINT IDENTITY(1,1) PRIMARY KEY, Description VARCHAR(75), Price MONEY NOT NULL ) GO

CREATE TABLE Purchases

( PurchaseID SMALLINT IDENTITY(1,1) PRIMARY KEY, ProductID SMALLINT REFERENCES Products(ProductID), PurchasePrice MONEY NOT NULL,

PurchaseDate SMALLDATETIME DEFAULT(GETDATE()) ) GO

--插入记录

INSERT INTO Products(Description, Price) VALUES('Camera',325) INSERT INTO Products(Description, Price) VALUES('Projector',1500) INSERT INTO Products(Description, Price) VALUES('XBox',400) GO

INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(1, 500) INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(5, 325) INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(1, 525) GO --创建视图

CREATE VIEW vw_ProductPurchases AS

10

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库数据库系统原理教案(2)在线全文阅读。