《SQL Server数据库服务器端编程》由会员分享，可在线阅读，更多相关《SQL Server数据库服务器端编程（28页珍藏版）》请在金锄头文库上搜索。
1、第10章 数据库服务器端编程10.1 存储过程10.2 触发器10.3 游标编程学习：重庆IT论坛 10.1 存储过程为改善系统性能，数据库管理系统允许用户将完成特定功能的SQL 语句序列组织成 一个存储过程（Stored Procedure）存储在数据库中，并通过指定存储过程名及参数调用 相应的存储过程。 使用存储过程有如下优点： 存储过程进行了预编译，并且在服务器端运行，执行速度快，客户端通过调用存 储过程，可使应用程序和数据库服务器间的通信量小，降低网络负载。 存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，在以后的操作中 ，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提
2、高了系统性能。 使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据 库信息访问的权限，从而确保数据库的安全。 可将一些初始化的任务定义为存储过程，当系统启动时自动执行，而不必在系统 启动后再进行手工操作，大大方便了用户的使用。 1存储过程的创建 语句格式： CREATE PROCEDURE 过程名 （， ） AS /*描述存储过程的操作*/ 其中，参数定义如下： IN | OUT | INOUT 重庆IT论坛 10.1 存储过程存储过程的定义由三部分组成：过程名、参数和过程体。存储过程名必须满足标识 符的定义，在数据库内必须唯一；存储过程可以有零个或多个参数，存储过程通过这
3、些 参数与外部交换数据，输入（IN）参数，可将数据传递给存储过程，输出（OUT）参数 ，可从存储过程将数据传递到外部；INOUT参数表示该参数既可用做输入参数也可用做 输出参数；过程体由SQL语句序列构成。 可用DBMS（或第三方软件）提供的数据库管理工具定义存储过程。 【例10.1】 定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩。 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
4、_KC.课程号=KC.课程号重庆IT论坛 10.1 存储过程2存储过程的执行 存储过程的执行基于如下几种情况： 在数据库应用程序中通过嵌入式SQL语句 调用，不同的开发工具提供的调用形式不一样； 通过DBMS（或第三方软件）提供 的数据库管理工具调用，调用的语法格式取决于具体的管理工具。下面的例子是基于 SQL Server 2005的查询分析器调用存储过程，后面的例子中介绍了开发数据库应用程 序时，如何通过嵌入式SQL语句调用存储过程。 【例10.2】 在SQL Server 2005查询分析器中执行例10.1中的存储过程。 EXECUTE student_grade GO 注：SQL Se
5、rver 2005采用EXECUTE 语句，可缩写为EXEC。 3存储过程的删除 语句格式： DROP PROCEDURE 数据库名. 【例10.3】 删除例10.1中创建的存储过程student_grade。 DROP PROCEDURE student_grade重庆IT论坛 10.1 存储过程4综合应用 【例10.4】 设计一个无参存储过程，从XSCJ数据库的3个表中查询选课学生的学 号、姓名、课程名、成绩、学分。 USE XSCJ /*检查是否已存在同名的存储过程，若有，删除。*/ IF EXISTS （SELECT name FROM sysobjects WHERE name =
6、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重庆IT论坛 10.1 存储过程在SQL Server 2005查询分析器中，student_info 存储过程可以通过以下方法执行： EXECUTE student_info 或 EXEC
7、 student_info 如果该过程是批处理中的第一条语句，则可使用： student_info /*直接输入过程名*/ 【例10.5】 从XSCJ数据库的3个表中查询某人指定课程的成绩和学分，该存储过 程要求实参为精确匹配的值。 USE XSCJ IF EXISTS （SELECT name FROM sysobjects WHERE name = student_info1 AND type = P）DROP PROCEDURE student_info1 GO CREATE PROCEDURE student_info1name char （8），cname char（16） AS S
8、ELECT a.学号，姓名，课程名，成绩，学分FROM XS a INNER JOIN XS_KC bON a.学号 = b.学号 INNER JOIN KC tON b.课程号= t.课程号 WHERE a.姓名=name and t.课程名=cname GO重庆IT论坛 10.1 存储过程在SQL Server 2005查询分析器中，student_info1 存储过程有多种调用方式，下面列出 了一部分。 EXECUTE student_info1 王林，计算机基础 或 EXECUTE student_info1 name=王林，cname=计算机基础 或 EXECUTE student_
9、info1 cname=计算机基础，name=王林 或 EXEC student_info1 王林，计算机基础 或 EXEC student_info1 cname=计算机基础，name=王林 重庆IT论坛 10.1 存储过程【例10.6】 从3个表的联接中查询指定学生的学号、姓名、所选课程名称及该课程 的成绩。该存储过程在参数中使用带通配符的参数进行模式匹配，如果没有提供实参， 则使用预设的默认值。 USE XSCJ IF EXISTS （SELECT name FROM sysobjects WHERE name = st_info AND type = P）DROP PROCEDURE
10、st_info GO CREATE PROCEDURE st_infoname varchar（30）= 刘% AS SELECT a.学号，a.姓名，c.课程名，b.成绩 FROM XS a INNER JOIN XS_KC bON a.学号 =b.学号 INNER JOIN KC cON c.课程号= b.课程号 WHERE 姓名 LIKE name GO 如下为st_info 存储过程在SQL Server 2005查询分析器的调用： EXECUTE st_info /*参数使用默认值*/重庆IT论坛 10.1 存储过程或 EXECUTE st_info 王% /*传递给name 的实参
11、为王%*/ 或 EXECUTE st_info 王张% 【例10.7】 计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出 参数。 USE XSCJ GO IF EXISTS（SELECT name FROM sysobjectsWHERE 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_
12、KC，KC WHERE 姓名=name AND XS.学号= XS_KC.学号 GROUP BY XS.学号 GO重庆IT论坛 10.1 存储过程注：在创建存储过程时，如果形参定义为OUTPUT参数，调用时对应的实参变量也 必须定义，形参名与实参变量名不一定要相同，但数据类型和参数位置必须匹配。 如下语句是对上述存储过程的调用： USE XSCJ DECLARE t_credit char（20），total int EXECUTE totalcredit 王林，total OUTPUT SELECT 王林，total GO 【例10.8】 创建3个存储过程，分别对XSCJ数据库的KC表实现增
13、、删、改。 （1）在KC表中插入一条记录 USE XSCJ CREATE PROCEDURE kc_insert （kch char（3），kcxz char（8），kcm char（16），xq tinyint，xs tinyint，xf tinyint） AS INSERT INTO kc VALUES（kch，kcxz，kcm，xq，xs，xf）重庆IT论坛 10.1 存储过程（2）对KC表中的一条记录进行修改 USE XSCJ CREATE PROCEDURE kc_update （kch char（3），kcxz char（8），kcm char（16），xq tinyint，xs
14、tinyint， xf tinyint，flag int output） AS BEGINSET flag=1IF exists（SELECT * FROM kc WHERE 课程号=kch）UPDATE kcSET 课程性质=kcxz，课程名=kcm，学期=xq，学时=xs， 学分=xfWHERE 课程号=kch ELSE SET flag=0 END重庆IT论坛 10.1 存储过程（3）删除KC表中的一条记录 USE XSCJ CREATE PROCEDURE kc_delete （kch char（3），flag int output） AS BEGIN SET flag=1 IF ex
15、ists（SELECT * from kc where 课程号=kch）DELETE kc where 课程号=kch ELSE SET flag=0END重庆IT论坛 10.1 存储过程【例10.9】 创建一个系统存储过程，显示表名以XS开头的所有表及其对应的索引 。如果没有指定参数，该过程将返回表名以KC开头的所有表及对应的索引，然后调用 它。 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
