1 / 22

存储过程

存储过程. 在 SQL Server 中,可以定义子程序存放在数据库中,这样的子程序称为存储过程,存储过程是数据库对象之一。使用存储过程的特点如下: ( 1 )存储过程在服务器端运行,执行速度快。 ( 2 )存储过程执行一次后,其执行规划主驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。 ( 3 )确保数据库的安全。使用存储过程可以完成所有的数据库操作。 ( 4 )降低网络负载,提高效率。 ( 5 )可以接受用户参数,亦可返回参数。. 存储过程和触发器. 存储过程的类型 系统存储过程 本地存储过程

yale
Télécharger la présentation

存储过程

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 存储过程 • 在SQL Server中,可以定义子程序存放在数据库中,这样的子程序称为存储过程,存储过程是数据库对象之一。使用存储过程的特点如下: • (1)存储过程在服务器端运行,执行速度快。 • (2)存储过程执行一次后,其执行规划主驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。 • (3)确保数据库的安全。使用存储过程可以完成所有的数据库操作。 • (4)降低网络负载,提高效率。 • (5)可以接受用户参数,亦可返回参数。 7 存储过程和触发器

  2. 存储过程和触发器 • 存储过程的类型 • 系统存储过程 • 本地存储过程 • 临时存储过程 • 远程存储过程 • 扩展存储过程 • 用户存储过程的创建与执行 • 通过SQL命令创建和执行存储过程 定义如下存储过程 USE XSCJ Go CREATE PROCEDURE student_grade AS SELECT XS.学号,XS.姓名,KC.课程名, XS_KC.成绩 FROM XS,XS_KC,KC WHERE XS.学号=XS_KC.学号AND XS_KC.课程号=KC.课程号 Go 7 存储过程和触发器

  3. 用户存储过程的创建与执行 • 举例 • 设计简单的存储过程 【例】从XSCJ数据库的三个表中查询,返回学生学号、姓名、课程名、成绩、学分。该存储过程不使用任何参数。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'student_info' AND type = 'P') DROP PROCEDURE student_info GO /*创建存储过程*/ CREATE PROCEDURE student_info AS SELECT a.学号, 姓名, 课程名, 成绩, 学分 FROM XS a INNER JOIN XS_KC b ON a.学号 = b.学号 INNER JOIN KC t ON b.课程号= t.课程号 GO 7 存储过程和触发器

  4. 用户存储过程的创建与执行 • 使用带参数的存储过程 • 【例】从XSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'student_info1' AND type = 'P') DROP PROCEDURE student_info1 GO CREATE PROCEDURE student_info1 @name char (8),@cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, 学分 FROM XS a INNER JOIN XS_KC b ON a.学号 = b.学号 INNER JOIN KC t ON b.课程号= t.课程号 WHERE a.姓名=@name and t.课程名=@cname GO 7 存储过程和触发器

  5. 用户存储过程的创建与执行 • 使用带有通配符参数的存储过程 【例】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配,如果没有提供参数,则使用预设的默认值。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'st_info' AND type = 'P') DROP PROCEDURE st_info GO CREATE PROCEDURE st_info @name varchar(30) = '刘%' AS SELECT a.学号,a.姓名,c.课程名,b.成绩 FROM XS a INNER JOIN XS_KC b ON a.学号 =b.学号 INNER JOIN KC c ON c.课程号= b.课程号 WHERE 姓名 LIKE @name GO 7 存储过程和触发器

  6. 用户存储过程的创建与执行 • 使用带OUTPUT参数的存储过程 【例】用于计算指定学生的总学分,存储过程中使用了一个输入参数和一个输出参数。 USE XSCJ GO IF EXISTS(SELECT name FROM sysobjects WHERE name = 'totalcredit' AND type = 'P') DROP PROCEDURE totalcredit GO USE XSCJ GO CREATE PROCEDURE totalcredit @name varchar(40), @total int OUTPUT AS SELECT @total= SUM(学分) FROM XS,XS_KC,KC WHERE 姓名=@name AND XS.学号= XS_KC.学号 GROUP BY XS.学号 GO 7 存储过程和触发器

  7. 用户存储过程的创建与执行 【例】创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从syscomments 表中获取关于该过程的信息。 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'encrypt_this' AND type = 'P') DROP PROCEDURE encrypt_this GO USE XSCJ GO CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM XS GO • 创建用户定义的系统存储过程 【例】创建一个过程,显示表名以 xs 开头的所有表及其对应的索引。如果没有指定参数,该过程将返回表名以 kc 开头的所有表及对应的索引 。 7 存储过程和触发器

  8. 用户存储过程的创建与执行 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_showtable' AND type = 'P') DROP PROCEDURE sp_showtable GO USE master GO CREATE PROCEDURE sp_showtable @TABLE varchar(30) = 'kc%' AS SELECT tab.name AS TABLE_NAME, inx.name AS INDEX_NAME, indid AS INDEX_ID FROM sysindexes inx INNER JOIN sysobjects tab ON tab.id = inx.id WHERE tab.name LIKE @TABLE GO USE XSCJ EXEC sp_showtable 'xs%' GO 7 存储过程和触发器

  9. 存储过程 • create procedure pc_name1 • @customerid varchar(50),@phone varchar(25) • as • if @customerid is null • begin • print 'you must input customerid' • return • end • if not exists(select * from customers where customerid=@customerid) • begin • print ‘the user’+@customerid+‘is not exists’ • return • end • update customers • set phone=@phone • where customerid=@customerid • select 'the phone number for'+@customerid+'has been updated to '+@phone 7 存储过程和触发器

  10. 用户存储过程的编辑修改 【例】对存储过程student_info1进行修改。 USE XSCJ GO ALTER PROCEDURE student_info1 @name char(8),@cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, 学分 FROM XS a INNER join XS_KC b ON a.学号 = b.学号 INNER JOIN KC t ON b.课程号= t.课程号 WHERE a.姓名=@name and t.课程名=@cname GO 【例】创建名为 select_students 的存储过程,默认情况下,该过程可查询所有学生信息,当该过程需更改为能检索计算机专业的学生信息时,用 ALTER PROCEDURE 重新定义该存储过程。 USE XSCJ GO IF EXISTS(SELECT name FROM sysobjects WHERE name = ' select_students ' AND type = 'P') 7 存储过程和触发器

  11. 用户存储过程的编辑修改 DROP PROCEDURE select_students /*若该存储过程已存在,则删除*/ GO USE XSCJ GO CREATE PROCEDURE select_students /*创建存储过程*/ AS SELECT * FROM XS ORDER BY 学号 GO -- 修改存储过程select_students ALTER PROCEDURE select_students WITH ENCRYPTION AS SELECT * FROM XS WHERE 专业名= '计算机' ORDER BY 学号 GO 7 存储过程和触发器

  12. 用户存储过程的删除 • 语法格式: DROP PROCEDURE { procedure } [ ,...n ] 【例】删除 XSCJ数据库中的student_info1 存储过程。 USE XSCJ GO DROP PROCEDURE student_info1 7 存储过程和触发器

  13. 触发器 • 触发器是一种特殊的存储过程,它被分配给某个特定的表,触发器都是自动调用的。当一特定的表数据被插入、更新或删除时,数据库需要执行一定的动作,触发器是确保数据完整性和一致性的基本有效的方法。 • 特点:不接受用户参数,也不返回用户参数;事件驱动,不能被调用;存储在表上; • 四种触发器: • 1、insert • 2、delete • 3、update • 4、instead of • 触发器的工作: • 执行触发器时,系统创建了两个特殊的逻辑表:inserted和deleted表,当向表中插入数据时,insert触发器触发执行,新的记录插入到触发器表和inserted表中。 当触发一个delete触发器时,被删除的表记录存放到deleted表中。修改一条记录等于插入一条新记录,同时删除旧记录,表中原始记录放在deleted中,修改过的记录插入到inserted表中。 7 存储过程和触发器

  14. 利用SQL命令创建触发器 • 添加一个用户自定义信息: • exec sp_addmessage 50010,16,'id not found',@lang='English',@replace='replace' 【例】对于XSCJ数据库,如果在XS表中添加或更改数据,则将向客户端显示一条信息. USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO CREATE TRIGGER reminder ON XS FOR INSERT, UPDATE AS RAISERROR (50010, 16, 10) GO 【例】在数据库XSCJ中创建一触发器,当向XS_KC表插入一记录时,检查该记录的学号在XS表是否存在,检查课程号在KC表中是否存在,若有一项为否,则不允许插入。 USE XSCJ 7 存储过程和触发器

  15. 利用SQL命令创建触发器 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'check_trig' AND type = 'TR') DROP TRIGGER check_trig GO CREATE TRIGGER check_trig ON XS_KC FOR INSERT AS if exists (SELECT * FROM inserted a WHERE a.学号 NOT IN (SELECT b.学号 FROM XS b) OR a.课程号 NOT IN (SELECT c.课程号 FROM KC c)) BEGIN RAISERROR ('违背数据的一致性.', 16, 1) ROLLBACK TRANSACTION END 7 存储过程和触发器

  16. 触发器 • 当修改XS表中的信息时,自动修改xs_kc表中的信息。 • create trigger pod_update on xs for update • as • begin • update xs_kc set 学号=(select 学号 from inserted) where 学号=(select 学号 from deleted) • end • go 7 存储过程和触发器

  17. 利用SQL命令创建触发器 • INSTEAD OF触发器的设计 【例】在XSCJ数据库中创建表、视图和触发器,以说明INSTEAD OF INSERT触发器的使用。 USE XSCJ CREATE TABLE books 7 存储过程和触发器

  18. 利用SQL命令创建触发器 ( BookKey int IDENTITY(1,1), BookName nvarchar(10) NOT NULL, Color nvarchar(10) NOT NULL, ComputedCol AS (BookName +Color), Pages int ) GO CREATE VIEW View2 AS SELECT BookKey, BookName ,Color, ComputedCol, Pages FROM books GO CREATE TRIGGER InsteadTrig on View2 INSTEAD OF INSERT AS BEGIN INSERT INTO books SELECT BookName ,Color, Pages FROM inserted END GO 7 存储过程和触发器

  19. 触发器的修改 【例】修改XSCJ数据库中在XS表上定义的触发器reminder。 USE XSCJ ALTER TRIGGER reminder ON XS FOR UPDATE AS RAISERROR (“执行的操作是修改”, 16, 10) GO • 通过企业管理器修改触发器 进入企业管理器,修改触发器的步骤与创建的步骤相同,进入界面后在“名称”对应的下拉表中选择要修改的触发器名即可进入触发器修改状态。 7 存储过程和触发器

  20. 触发器的删除 • 利用SQL命令删除触发器 • 语法格式: DROP TRIGGER { trigger } [ ,...n ] 【例】 删除触发器reminder。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO • 通过企业管理器删除触发器 7 存储过程和触发器

  21. 修改删除禁用触发器 一、修改触发器: Alter trigger pod_insert on pizzaorders for insert As //修改的内容 二、删除触发器: Drop trigger pod_insert[触发器名称] 三、禁用/启用触发器: Alter table customers disable trigger pod_insert --禁用触发器 alter table customers enable trigger pod_insert --启用触发器 7 存储过程和触发器

  22. 作业触发器 • 有两个表:产品表(P)和订单表(O) • P:pid,pname,price,quantity • O:Oid,pid,quantity • 在orders表中建立一个INSERT的触发器,每插入一条记录,激活触发器,去修改products表中相应的quanlity(将库存量减去即将销售量) 7 存储过程和触发器

More Related