存储过程
This presentation is the property of its rightful owner.
Sponsored Links
1 / 22

存储过程 PowerPoint PPT Presentation


  • 101 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

存储过程

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


4654564

存储过程

  • 在SQL Server中,可以定义子程序存放在数据库中,这样的子程序称为存储过程,存储过程是数据库对象之一。使用存储过程的特点如下:

  • (1)存储过程在服务器端运行,执行速度快。

  • (2)存储过程执行一次后,其执行规划主驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。

  • (3)确保数据库的安全。使用存储过程可以完成所有的数据库操作。

  • (4)降低网络负载,提高效率。

  • (5)可以接受用户参数,亦可返回参数。

7 存储过程和触发器


4654564

存储过程和触发器

  • 存储过程的类型

    • 系统存储过程

    • 本地存储过程

    • 临时存储过程

    • 远程存储过程

    • 扩展存储过程

  • 用户存储过程的创建与执行

    • 通过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 存储过程和触发器


4654564

用户存储过程的创建与执行

  • 举例

    • 设计简单的存储过程

      【例】从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 存储过程和触发器


4654564

用户存储过程的创建与执行

  • 使用带参数的存储过程

    • 【例】从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 [email protected] and [email protected]

      GO

7 存储过程和触发器


4654564

用户存储过程的创建与执行

  • 使用带有通配符参数的存储过程

    【例】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配,如果没有提供参数,则使用预设的默认值。

    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 存储过程和触发器


4654564

用户存储过程的创建与执行

  • 使用带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 [email protected] AND XS.学号= XS_KC.学号

    GROUP BY XS.学号

    GO

7 存储过程和触发器


4654564

用户存储过程的创建与执行

【例】创建加密过程,使用 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 存储过程和触发器


4654564

用户存储过程的创建与执行

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 存储过程和触发器


4654564

存储过程

  • 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 [email protected])

  • begin

  • print ‘the [email protected]+‘is not exists’

  • return

  • end

  • update customers

  • set [email protected]

  • where [email protected]

  • select 'the phone number [email protected]+'has been updated to [email protected]

7 存储过程和触发器


4654564

用户存储过程的编辑修改

【例】对存储过程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 [email protected] and [email protected]

GO

【例】创建名为 select_students 的存储过程,默认情况下,该过程可查询所有学生信息,当该过程需更改为能检索计算机专业的学生信息时,用 ALTER PROCEDURE 重新定义该存储过程。

USE XSCJ

GO

IF EXISTS(SELECT name FROM sysobjects WHERE name = ' select_students ' AND type = 'P')

7 存储过程和触发器


4654564

用户存储过程的编辑修改

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 存储过程和触发器


4654564

用户存储过程的删除

  • 语法格式:

    DROP PROCEDURE { procedure } [ ,...n ]

    【例】删除 XSCJ数据库中的student_info1 存储过程。

    USE XSCJ

    GO

    DROP PROCEDURE student_info1

7 存储过程和触发器


4654564

触发器

  • 触发器是一种特殊的存储过程,它被分配给某个特定的表,触发器都是自动调用的。当一特定的表数据被插入、更新或删除时,数据库需要执行一定的动作,触发器是确保数据完整性和一致性的基本有效的方法。

  • 特点:不接受用户参数,也不返回用户参数;事件驱动,不能被调用;存储在表上;

  • 四种触发器:

  • 1、insert

  • 2、delete

  • 3、update

  • 4、instead of

  • 触发器的工作:

  • 执行触发器时,系统创建了两个特殊的逻辑表:inserted和deleted表,当向表中插入数据时,insert触发器触发执行,新的记录插入到触发器表和inserted表中。 当触发一个delete触发器时,被删除的表记录存放到deleted表中。修改一条记录等于插入一条新记录,同时删除旧记录,表中原始记录放在deleted中,修改过的记录插入到inserted表中。

7 存储过程和触发器


4654564

利用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 存储过程和触发器


4654564

利用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 存储过程和触发器


4654564

触发器

  • 当修改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 存储过程和触发器


4654564

利用SQL命令创建触发器

  • INSTEAD OF触发器的设计

    【例】在XSCJ数据库中创建表、视图和触发器,以说明INSTEAD OF INSERT触发器的使用。

    USE XSCJ

    CREATE TABLE books

7 存储过程和触发器


4654564

利用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 存储过程和触发器


4654564

触发器的修改

【例】修改XSCJ数据库中在XS表上定义的触发器reminder。

USE XSCJ

ALTER TRIGGER reminder ON XS

FOR UPDATE

AS RAISERROR (“执行的操作是修改”, 16, 10)

GO

  • 通过企业管理器修改触发器

    进入企业管理器,修改触发器的步骤与创建的步骤相同,进入界面后在“名称”对应的下拉表中选择要修改的触发器名即可进入触发器修改状态。

7 存储过程和触发器


4654564

触发器的删除

  • 利用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 存储过程和触发器


4654564

修改删除禁用触发器

一、修改触发器:

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 存储过程和触发器


4654564

作业触发器

  • 有两个表:产品表(P)和订单表(O)

  • P:pid,pname,price,quantity

  • O:Oid,pid,quantity

  • 在orders表中建立一个INSERT的触发器,每插入一条记录,激活触发器,去修改products表中相应的quanlity(将库存量减去即将销售量)

7 存储过程和触发器


  • Login