第11章 存储过程汇总

1、11 .存储过程本章的主题什么是l存储过程l创建存储过程CREATE PROCEDURE命令的详细信息l存储过程的执行l重命名存储过程l修改存储过程l删除存储过程不要学习必杀技！ 学习如何使用SQL编写存储程序11.1 .什么是存储过程我相信接受前四章的洗礼，对Microsoft SQL Server的编程有基本的认识。第07章Transact-SQL编程第08章数据的查询、统计、统计和分析第09章数据记录的添加、修改、删除第十章视图本章就更高级的内容进行说明。存储过程包含几个Transact-SQL语句，并以特定名称存储在数据库中。 存储过程是数据库对象。 变量、条件执行和其他强大的编程功能

2、可以在存储过程中声明。l可以包含用于执行数据库操作的语句，并且可以调用其他存储过程。l输入参数可以被接受，并且多个数据值可以作为输出参数返回给调用程序。具体例子：试试看，明白吗？ 稍后详细介绍。createproceduregetcperson mname varchar (10 ) mcur _ salary money美联储更新VFP manSET cur_salary=cur_salary*1.2where name= mnameandscur _ salary= mcur _ salary11.1.1 .存储过程的优点l可以进行模块化的编程它的运行速度比l快，存储过程预先编译和优化，然

3、后存储在数据库中l有效地降低网络通信量比l安全，即使有人限制对表的操作，也必须要求对该表执行特定的操作。11.2 .创建存储过程创建过程命令。在查询分析器中执行命令。11.3 .详细说明创建程序命令create proc edu re procedure _ name ； 数字符号 parameter data_type varying =默认 输出， n WITH recompile，ENCRYPTION 复制的目的。AS sql_statement .n number型用于对同名过程进行分组的可选整数。 可以使用DROP PROCEDURE语句删除同一组中的过程。 例如，名为orders的

4、应用程序使用的过程可以命名为orderproc，1、orderproc； 二等。 DROP PROCEDURE orderproc语句删除整个组。VARYING公司指定支持的结果集作为输出参数(由存储过程动态构建，内容可以修改)。 仅适用于光标参数。11.3.1 .指定存储过程的名称l存储过程名称最多可包含128个字符l请勿以sp_开始，以免与系统存储过程混淆11.3.2 .指定存储过程的语句CREATE PROCEDURE命令不能与其他Transact-SQL命令位于同一批中。下面的代码是错误的USE NorthwindSQL -与下一个创建过程在同一批中创建程序myproc美联储SELEC

5、T * FROM飞狐工作室更改后：USE NorthwindSQLgo！ go！创建程序myproc美联储SELECT * FROM飞狐工作室评论：l最好各存储过程只负责一项作业建议您在创建l存储过程之前，使用SQL查询分析程序创建并测试要包含在存储过程中的程序代码，如果没有错误，则添加CREATE PROCEDURE命令来创建存储过程。l存储过程可以访问表、视图并调用其他存储过程。l存储过程的最大长度为128MB11.3.3 .输入参数将数据值传递给存储过程。 该数据值也称为输入参数。 parameter data_type varying =默认 输出l参数的名称必须以开头。l data_

6、type指定输入参数的数据类型。l default指定输入参数的默认值。l存储过程最多可以有2100个参数，包括输入和输出参数。示例1 :使用传递的参数，选择具有所选名称的员工数据。/*文件名： Demo111.sql */USE NorthwindSQLifexists (selectnamefromsysobjects )WHERE name=DemoProc1 AND type=P )DROP PROCEDURE DemoProc1go！ go！创建程序解调proc 1name varchar(10 )美联储SELECT身份证号码名字生日年龄=DATEDIFF(yy、出生年月日、GETD

7、ATE () )FROM飞狐工作室WHERE名称=namego！ go！执行存储过程EXECUTE DemoProc1 许建仁- -方法之一或者EXECUTE DemoProc1 name=许建仁- -方法的两个-参数名称必须与定义时的名称匹配-你喜欢哪种方法？任务：创建一个存储过程，根据传递的学生名称，显示学生的基本信息根据传递的学号，显示选择的课程根据传递的班级名称，显示该班的学生信息。 创建存储过程，根据接收到的课程信息，将课程信息添加到course表中，创建存储过程，根据传递的学生的学号和入学成绩，更新该学生的入学成绩，根据传递的学号，删除该学生的选择项信息示例2 :/*文件名： De

8、mo112.sql */USE NorthwindSQLifexists (selectnamefromsysobjects )WHERE name=DemoProc2 AND type=P )DROP PROCEDURE DemoProc2go！ go！创建程序解码proc 2 beginningdatedatetime=01/01/1900EndingDate datetime=12/31/2001美联储SELECT a .客户编号，a .公司名称a .地址、a .联系方式、a .电话b .订单编号，b .订单日期b .运费，b .受益人c .产品编号，c .单价c .数量，c .折扣从客

9、户a INNER JOIN订单主控器bINNER JOIN订单明细行cON b .订单编号=c .订单编号ON a .客户编号=b .客户编号WHEREb .订单日期between beginningdateand ending datego！ go！执行存储过程executedemoproc 207/01/1996、07/31/1996或者executedemoproc2 beginning date=07/01/1996 ending date=07/31/1996-参数名称必须与定义时的名称匹配另一种调用方法：EXECUTE DemoProc2-:使用参数的默认值任务：创建存储过程，并定

10、义两个日期参数(参数的默认值显示出生于此日期带的狐狸工作室的个人信息)。任务：创建存储过程，并根据传递的产品类别名称(参数默认为饮料)显示该类别下的产品信息。例3 :/*文件名： Demo113.sql */USE NorthwindSQLifexists (selectnamefromsysobjects )WHERE name=DemoProc3 AND type=P )DROP PROCEDURE DemoProc3go！ go！创建程序解调proc 3id varchar(10)=A-M%，name varchar(10)=允许%address varchar(10)=%市%美联储SE

11、LECT * FROM飞狐工作室WHERE身份证号码LIKE id AND名字LIKE name AND家庭地址LIKE addressgo！ go！执行存储过程EXECUTE DemoProc3 ALM%、_建_、%天津市%执行演示proc 3任务：创建存储过程，并根据传递的产品名称(参数的默认值为%汁)显示产品信息。11.3.4 .输出参数示例1 :演示将传递的参数相乘并返回结果。/*文件名： Demo114.sql */USE NorthwindSQLifexists (selectnamefromsysobjects )WHERE name=DemoProc4 AND type=P )

12、DROP PROCEDURE DemoProc4go！ go！创建程序dbo.demo proc 4mult1 intmult2 intresult int OUTPUT美联储SET result=mult1 * mult2go！ go！执行存储过程/*文件名： Demo115.sql */DECLARE answer intexecdemoproc 4，12，33 answer outputSELECT运算的结果为：answer示例2 :计算指定部门的平均工资、最大工资和最低工资。/*文件名： Demo116.sql */USE NorthwindSQLifexists (selectnam

13、efromsysobjects )WHERE name=DemoProc5 AND type=P )DROP PROCEDURE DemoProc5go！ go！创建程序dbo.demo proc 5department varchar(10 )average money OUTPUTmaximum money OUTPUTminimummoney输出美联储SELECT average=AVG (当前工资)maximum=MAX (当前工资)minimum=MIN (当前工资)FROM飞狐工作室WHERE部门=departmentgo！ go！执行存储过程/*文件名： Demo117.sql

14、*/USE NorthwindSQLDECLARE department varchar(10 )declare average decimal (19，4 )declare maximum decimal (19，4 )declare minimum decimal (19，4 )SELECT department=制造部EXEC DemoProc5 departmentaverage OUTPUTmaximum OUTPUTminimum OUTPUTSELECT平均工资： CONVERT(nvarchar，average )最高工资： CONVERT(nvarchar，maximum )最低工资： CONVERT(nvarchar，minimum )任务：创建存储过程，定义输出参数，创建显示学生的平均入学成绩的存储过程，根据传递的课程名称，定义输出参数，显示选择该课程的学生的最高得分。11.4 .嵌套存储过程嵌套存储过程-在存储过程中调用另一个存储过程。嵌套最多可达32