数据库编程和案例分析

1、数据库编程和案例分析 数据库设计的基本步骤6个阶段： 需求分析阶段 概念结构设计 逻辑结构设计 物理结构设计 数据库实施 数据库的运行和维护复习： 案例1P242例5 将计算机系全体学生年龄 置NULL值。 UPDATE Student SET Sage=NULL WHERE Sdept= CS；EXEC SQL UPDATE Student SET Sage=NULL WHERE Sdept= CS；案例分析试比较说明两个语句有什么区别？ 第2个语句有EXEC SQL 。新知识的引入SQL语言提供了两种不同的 使用方式： 交互式 嵌入式 优点：操作统一、面向集合、功能丰富、使用简单。缺点：缺

2、少流程控制，难以实现应用业务中的逻辑控制。 原因（标准SQL）： SQL编程技术可以有效克服SQL语言实现应用方面的不足，提高应用系统和RDBMS间的互操作性。 应用系统中使用SQL编程来访问和管理数据库中数据的方式： 嵌入式SQL（Embeded SQL，ESQL） PL/SQL(Procedural Language/SQL) ODBC编程(Open Data Base Connectivity) JDBC编程(Java Data Base Connectivity) OLEDB(Object Linking and Embedding DB)8.1 嵌入式SQL 嵌入式SQL是将SQL语

3、句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C+、Java，称为宿主语言，简称主语言。 为了区分SQL语句与主语言语句， 所有SQL语句必须加前缀EXEC SQL，以(;)结束: EXEC SQL ;1. 处理过程预编译方法 含ESQL语句的主语言程序ESQL语句转换为函数调用RDBMS的预处理程序主语言编译程序目标语言程序P238 图2. 与主语言之间的通信 将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句 。SQL语句（描述性的面向集合的语句） 高级语言语句（过程性的面向记录的语句） 负责操纵数据库 负责控制程序流程 它们之间应该如何通信？3. 通信方式及内容 S

4、QL通信区 （ SQLCA ）SQL Communication Area 是一个数据结构 SQL语句执行后，RDBMS把SQL语句的执行状态信息（当前工作状态 、运行环境 ）送到SQLCA中。应用程序（主语言）从SQLCA中取出这些状态信息，据此控制程序流程 。定义SQLCA EXEC SQL INCLUDE SQLCA 使用SQLCA SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE ；应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理；如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否

5、则表示出错。 主变量（Host Variable）嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据在SQL语句中使用的主语言程序变量简称为主变量向SQL语句提供参数 。将SQL语句查询数据库的结果交主语言进一步处理。主变量的类型 输入主变量 由应用程序对其赋值，SQL语句引用。输出主变量 由SQL语句对其赋值或设置状态信息，返回给应用程序。 一个主变量有可能既是输入主变量又是输出主变量。指示变量：一个主变量可以附带一个指示变量（Indicator Variable）。是一个整型变量，用来“指示”所指主变量的值或条件。输入主变量：可以检测是否为空值。输出主变量：可以检测是否为空值，值是

6、否被截断。 在SQL语句中使用主变量和指示变量的方法：1)说明主变量和指示变量 BEGIN DECLARE SECTION . . (说明主变量和指示变量) . END DECLARE SECTION 2) 使用主变量 说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现。为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志。3)使用指示变量 指示变量前也必须加冒号标志必须紧跟在所指主变量之后 在SQL语句之外(主语言语句中)使用主变量和指示变量的方法 直接引用，不必加冒号 游标 使用游标 的原因：SQL语言与主语言具有不同数据处理方式

7、。 SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录；主语言是面向记录的，一组主变量一次只能存放一条记录 ；仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求；嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式。游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字 。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。4. 建立数据库连接 EXEC SQL CONNECT TO target AS connection-name USER user-name; target是要连接的数据库服务器；它

8、可以是一个常见的服务器标识串，如:。或者是包含服务器标识的SQL串常量，也可以是DEFAULT。connect-name是可选的连接名；连接必须是一个有效的标识符，主要用来识别一个程序内同时建立的多个连接。在整个程序内只有一个连接时可以不指定连接名。 修改当前连接 ： 如果程序运行过程中建立了多个连接，执行的所有数据库单元的工作都在该操作提交时所选择的当前连接上。程序运行过程中可以修改当前连接，对应的嵌入式SQL语句为:EXEC SQL SET CONNECTION connection-name|DEFAULT EXEC SQL DISCONNECT connection; 关闭数据库连接：

9、当某个连接上的所有数据库操作完成后，应用程序应该主动释放所占用的连接资源。关闭数据库连接的ESQL的语句为：其中connection-name是EXEC SQL CONNECT所建立的数据库连接。程序实例：依次检查某个系的学生记录，交互式更新某些学生的年龄。EXEC SQL BEGIN DECLARE SECTION/*主变量说明开始*/char deptname20;char HSno9;char HSname20;char HSsex2;char HSage;int NEWAGE;EXEC SQL END DECLARE SECTION; /*主变量说明结束*/Long SQLCODE;E

10、XEC SQL INCLUDE sqlca; /*定义SQL通信区*/int main(void) int count=0;char yn;printf(“please input the department name(CS/MA/IS):”);scanf(“%s”,&deptname);/*为主变量deptname赋值EXEC SQL CONNECT TO TESTlocalhost:54321 USER”SYSTEM”/”MANAGER”;/*连接数据库*/EXEC SQL DECLARE SX CURSOR FOR/*定义游标SX*/SELECT Sno,Sname,Ssex,Sage

11、/*SX对应的语句*/FROM StudentWHERE SDept=:deptname;EXEC SQL OPEN SX;/*打开游标SX，指向查询结果的第一行*/for(;)/*用循环结构逐条处理结果集中的记录*/EXEC SQL FETCH SX INTO:HSno,:HSname,:HSsex,:HSage;/*推进游标，将当前的数据放入主变量*/if(sqlca.sqlcode!=0) break;/*sqlcode!=0,表示操作不成功*/*利用SQLCA中的状态信息决定何时退出循环*/if(count+=0)printf(“n%-10s%-20s%-10s%-10sn”,”Sno

12、”,”Sname”,”Ssex”,”Sage”);printf(“n%-10s%-20s%-10s%-10sn”,HSno,HSname,HSsex,HSage);/*打印查询结果*/printf(“UPDATE AGE(Y/N)?”); /*询问用户是否要更新该学生的年龄*/do scanf(“%d”,&yn);while (yn!=N&yn!=n&yn!=y&yn!=Y) if(yn=y|yn=Y) printf(“INPUT NEW AGE:”); scanf(“%d”,&NEWAGE); /*用户输入新的年龄到主变量中*/ EXEC SQL UPDATE Student /*嵌入式SQ

13、L更新语句*/ SET Sage=:NEWAGE WHERE CURRENT OF SX;/*对当前游标指向的学生年龄进行更新*/if/forEXEC SQL CLOSE SX;/*关闭游标SX不再和查询结果对应*/EXEC SQL COMMIT WORK;/*提交更新*/EXEC SQL DISCONNECT TEST/*断开数据库连接*/5. 不使用游标的SQL语句有的ESQL SQL语句不需要使用游标。它们是：说明性语句、数据定义语句、数据控制语句、查询结果为单记录的SELECT语句、非CURRENT形式的增删语句。 一、查询结果为单记录的SQL语句这类语句不需要使用游标，因为查询结构只

14、有一个，只需要用INTO子句指定存放查询结果的主变量。 例2根据学生号码查询学生信息。假设已经把要查询的学生的学号付给了主变量givernsnoEXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept INTO:Hsno,:Hname,:Hsex,:Hage,:HdeptFROM StudentWHERE Sno=:givensno;使用单记录的SELECT 语句需要注意以下几点：（1）INTO子句、WHERE子句和HAVING短语的条件表达式中均可以使用主变量。（2）查询结果为空值的处理。 查询返回的记录中，可能某些列为空值NULL。为了表示空值，在INTO子句的主

15、变量后面跟有指示变量，当查询得出的某个数据项为空值时，系统会自动将相应主变量后面的指示变量置为负值，而不再向该主变量赋值。所以当指示变量值为负值时，不管主变量为何值，均认为主变量值为NULL(3)如果查询结果实际并不是单条记录，而是多条记录，则程序出错，RDBMS会在SQLCA中返回错误信息。例3 查询某个学生选修某门课程的成绩。假设已经把将要查询的学生的学号付给了主变量givensno,将课程号赋给了主变量givensnoEXEC SQL Sno,Cno,GradeINTO :Hsno,:Hcno,:Hgrade:GradeidFROM SCWHERE Sno=:givensno AND C

16、no=:givencno如果Gradeid0，则不论Hgrade为何值，均认为该学生成绩为空值。二、非Current形式的增、删、改语句有些增删改语句不需要使用游标，不是CURRENT形式的。在UPDATE的SET子句和WHERE子句中可以使用主变量，SET子句还可以使用指示变量。例4，例5，例6，例7例4 修改某个学生选修1号课程的成绩EXEC SQL UPDATE SC SET Grade=:newgrade /*修改的成绩已经赋给主变量*/ WHERE Sno=:givensno; /*学号已经赋给主变量givensno*/例5 将计算机系全体学生年龄置为NULL值Sageid=-1;E

17、XEC SQL UPDATE Student SET Sage=:Raise:Sageid WHERE Sdept=CS将指示变量Sageid赋一个负值后，无论主变量Raise为何值，RDBMS都会将CS系所有学生的年龄置为空值。它等价于EXEC SQL UPDATE Student SET Sage=:Null WHERE Sdept=CS例6 某个学生退学了，现在要将有关他的所有选课记录删除掉。假设该学生的姓名已经赋给主变量stdnameEXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdn

18、ame);例7 某个学生新选修了某门课程，将有关记录插入到SC表中。假设插入的学号已经赋给主变量stdno,课程号已经赋给主变量couno。gradeid=-1EXEC SQL INSERTINTO SC(Sno,Cno,Grade)VALUES(:stdno,:couno,:gr:gradeid);由于该学生刚选修课程，成绩应为空，所以要把指示变量赋为负值。6. 使用游标的SQL语句必须使用游标的SQL语句有：查询结果为多条记录的SELECT语句；CURRENT形式的UPDATE和DELETE语句。一、查询结果为多记录的SQL语句一般情况下，SELECT语句查询结果是多条记录，因此需要用游标

19、机制，将多条记录一次一条的送主程序处理，从而把对集合的操作转换为对单个记录的处理。使用游标的步骤：（1）说明游标。用DECLARE语句为一条SELECT语句定义游标。EXEC SQL DECLARE CURSOR FOR；说明：游标仅仅是一条说明性语句，这是RDMS并不执行SELECT语句。(2)打开游标。用OPEN语句将定义的游标打开。EXEC SQL OPEN ；说明：打开游标实际上是执行相应的SELECT语句，把查询结果取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中的第一条记录。（3）推进游标指针并取当前记录。EXEC SQL FETCHINTO , .;说明：其中主变量必须与

20、SELECT语句中的目标列表达式具有一一对应关系。用FETCH语句把游标指针向前推进一条记录，同时将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。通过循环执行FETCH语句逐条取出结果集中进行处理。（4）关闭游标。用CLOSE语句关闭游标，释放结果集占用的缓冲区及其他资源。 EXEC SQL CLOSE；说明：游标被关闭后，就不再和原来的查询结果集相联系。但被关闭的游标可以再次被打开，与新的查询结果想联系。二、Current形式的删、改语句 UPDATE语句和DELETE语句都是集合操作，如果只想修改或删除其中的记录，则需要用带游标的SELECT语句查出所有满足条件的记录，从中进一步找

21、出要修改或删除的记录。然后用CURRENT形式的UPDATE和DELETE语句修改或删除之。即UPDATE语句和DELETE语句中要用子句 WHERE CURRENT OF 来表示修改或删除的是最近一次取出的记录，即游标指针指向的记录。注意：当游标定义中的SELECT语句带有UNION或ORDER BY子句时，或者该SELECT 语句相当于定义了一个不可更新视图时，不能使用CURRENT形式的UPDAT语句和DELETE语句。7. 动态SQL 前面所讲的嵌入式SQL语句中使用的主变量、查询目标列、条件等都是固定的，属于静态SQL语句。静态嵌入式SQL语句能够满足一般要求，但是某些应用可能要到执

22、行时才能够确定要提交的SQL语句，查询的条件。就要使用动态SQL来解决此类问题。 动态SQL方法允许在程序运行过程中临时“组装”SQL语句。动态SQL支持动态组装SQL语句和动态参数两种形式，给开发者提供设计任意SQL语句的能力。一、使用SQL语句的主变量 程序主变量包含的内容是SQL语句的内容，而不是原来保存数据的输入或输出的变量，这样的变量称为SQL语句主变量。SQL语句主变量在程序执行期间可以设定不同的SQL语句，然后立即执行。例8创建基本表TESTEXEC SQL BEGIN DECLARE SECTION;const char *stmt =“CREATE TABLE test(a

23、int);”;/*SQL语句主变量*/EXEC SQL END DECLARE SECTION;EXEC SQL EXECUTE IMMEDIATE:stmt/*执行语句*/二、动态参数动态参数是SQL语句中的可变元素，使用参数符号（？）表示该位置的数据在运行时设定。和前面使用的主变量不同：动态参数的输入不是编译时完成绑定的，而是通过（prepare）语句准备主变量和执行（execute）时绑定数据或主变量完成。使用动态参数的步骤：1.声明SQL语句主变量。变量的SQL内容包含动态参数（？）2.准备SQL语句（PREPARE）PREPARE将分析含主变量的SQL语句内容，建立语句中包含的动态参

24、数的内部描述符，并用标识它们的整体。 EXEC SQL PREPARE FROM ；3.执行准备好的语句（EXECUTE）EXECUTE将SQL语句分析出的动态参数和主变量或数据常量绑定作为语句的输入或输出变量。EXEC SQL EXECUTEINTO USING例9向TEST中插入元组。EXEC SQL BEGIN DECLARE SECTION;const char *stmt=“INSERT INTO test VALUES(?);”;/*声明SQL主变量*/ EXEC SQL END DECLARE SECTIONEXEC SQL PREPARE mystmt FROM:stmt;.E

25、XEC SQL EXECUTE mystmt USING 100;EXEC SQL EXECUTE mystmt USING 200;本部分小结在嵌入式SQL中，SQL语句用来与数据库打交道，存取数据库中的数据。主语言语句用来控制程序流程以及对取出的数据做进一步加工处理。SQL与主语言具有不同的数据处理方式。SQL是面向集合的，而主语言是面向记录的。所以嵌入式SQL引入了游标的概念，用游标来协调这两种不同的处理方式。8.2 存储过程PL/SQL过程语言块结构常量变量的定义赋值语句控制结构基础知识：使用批处理使用脚本使用注释标识符 常量变量T-SQL系统内置函数T-SQL的运算符 T-SQL语言

26、 基础知识认知目标：了解批、脚本、注释的含义了解T-SQL语言标识符的命名规则掌握常量的书写格式和基本用途掌握运算符的使用方法能力目标:掌握变量的声明、赋值和显示方法(重点)掌握系统内置函数的使用方法(难点) 一、使用批处理批处理的含义批处理是一条或多条T-SQL语句的集合，从应用程序一次性发送到SQL Server服务器，并由服务器编译成一个可执行单元（执行计划） 。一个批处理以GO语句结束。在一个GO命令行中不能包含其他T-SQL语句，但可以使用注释文字。 一、使用批处理特点如果一个批中的某条语句包含了语法错误，则整个批都不能被编译和执行。如果一个批中的某条语句出现执行错误，这时可能有两个

27、结果：第一种情况，如：违反约束，仅终止当前语句，其前其后语句正常执行。第二种情况，如：引用不存在的对象，终止当前语句和其后语句，其前语句正常执行。一个批中出现的语法和执行错误，都不影响其前和其后的批。 二、使用脚本定义：存储在文件(.sql )中的一系列SQL语句，也即一系列按顺序提交的批 。脚本可以直接在查询分析器等工具中输入并执行，也可以保存在文件中，再由查询分析器打开执行。作用：将语句保存为脚本文件，可以使多台计算机执行相同的操作。例如：将在一台服务器的查询分析器上编写的SQL语句在另一台服务器上运行。三、使用注释注释的作用：说明代码的含义，增强代码的可读性；可以把程序中暂时不用的语句注

28、释掉，使它们暂时不被执行，需要时可恢复。类型：行内注释：用于注释单行。-（两个减号）块注释：用于注释多行。/*/四、标识符标识符的含义是指用户定义的变量名、函数名、存储过程名、库名、表名、索引名、视图名等各种名称。命名规则标识符的长度可以为1-128个字符标识符的第一个字符必须为字母、下划线、#以为首的标识符表示1个局部变量。以#为首的标识符表示1个临时数据库对象。对于表或存储过程，名称前包含一个#时表示局部临时对象，而两个#(“#”)则表示为全局临时对象。标识符内不允许有空格，也不允许为SQL中的关键字 五、常量常量的含义在运行过程中保持不变的量，是表示特定数据值的符号常量的书写格式取决于它

29、所表示的值的数据类型。课堂练习2：判断下列常量是什么类型常量？123 w34 2006-04-24整型常量二进制常量浮点型常量精确数字型常量货币常量字符串常量日期时间型常量数值型常量六、变量 1.类型局部变量：用户自己定义的变量，用于在语句之间传递数据局部变量名以符号开头使用时，必须先声明全局变量(了解)：是系统定义的变量，用于存储系统的特定信息用户不能建立全局变量，也不能修改其值全局变量名以符号开头六、变量 2.局部变量的声明语法格式declare 变量名 数据类型,n 说明数据类型可以是系统定义的或用户定义的类型实例2：分别定义一个整型变量和两个字符型变量。declare name var

30、char(10) declare age smallint , msg varchar(50)注意：局部变量未被赋值之前，其值为null。六、变量 3.局部变量的赋值语法格式：set 变量=表达式select 变量=表达式 ,n说明：select语句不能同时完成赋值和查询两种操作实例3：给age、name、msg赋值。select name=刘伟，age=21set msg=欢迎使用SQL Server 2005六、变量 4.局部变量的显示语法格式为：print 表达式select 表达式 ,n 实例4：显示age、name、msg的值。print nameprint ageselect na

31、me as 姓名, 年龄=age, msg 欢迎词必须是char、varchar；或可以隐式地转化成字符型的常量或变量表达式。注意与赋值的区别。六、变量 5.局部变量的作用域作用域的含义是指可以使用该变量的范围 。局部变量的作用域从声明它的地方开始，到声明它的批处理或存储过程的结尾。 六、变量 5.局部变量的作用域实例5：分析下列局部变量的作用域，执行语句后，会出现什么问题？declare name varchar(10) declare age smallint , msg varchar(50)go - -第1个批结束select name=刘伟，age=21set msg=欢迎使用SQL

32、 Server 2005 go - -第2个批结束select name as 姓名, 年龄=age, msg 欢迎词七、函数 1.类型字符串函数求串长len( )、转换为大写/小写字母upper() / lower()数学函数Sum( )、avg( )、max( )、min( )、count( )日期和时间函数求当前系统日期：Getdate( ) 数据类型转换函数转换为字符型数据：Convert( )、cast( )全局变量函数(了解) 七、函数 2.常用函数应用实例6：已知msg的值为“欢迎使用SQL Server 2005”，显示该字串的长度。课堂练习3：显示kc表中课程号为“107”的

33、课程名称的长度。declare changdu smallintselect changdu=len(课程名) from kc where 课程号=107select changdu as 课程名称的长度declare msg varchar(50) - -声明变量select msg=欢迎使用SQL Server 2005 - -给变量赋值select len ( msg ) as 字符串的长度 - -显示变量的值七、函数 2.常用函数应用实例7：统计xsqk表中的学生人数，要求显示信息为“学生表中共有 XX 名学生”。use xscj -将xscj库切换为当前库declare rs sma

34、llint -声明变量select rs=count(*) from xsqk -给变量赋值print -显示1个空行print 学生表中共有 + convert(varchar(3),rs) + 名学生将数值型rs的值转换为字符型的值，以便与其它字符进行串联运算。将字符串进行联接七、函数 2.常用函数应用实例8：已知变量x中的值为“ABCDefghi”，要求将其转换为大写字母并显示。declare x varchar(20) -声明变量select x=ABCDefghi -给变量赋值print x -显示原字符串print upper(x) -显示转换后的大写字符串课堂练习4：将x中的字符

35、串转换成小写字母输出。七、函数 2.常用函数应用实例9：显示系统当前的日期。select getdate( )print getdate( )实例10：求xsqk表中陈伟的年龄，并显示为“年龄是：XX岁。”。declare a varchar(30)select a = convert(varchar(4), datediff (yy, 出生日期 , getdate() ) ) from xsqk where 姓名=陈伟print 年龄是： + a + 岁。七、函数 2.常用函数应用课堂练习5：将系统当前的日期显示为“现在的时间是：XX”。print 现在的时间是：+ convert(varc

36、har(30), getdate( )print 现在的时间是：+ cast(getdate( ) as varchar(30)T-SQL语言流程控制语句基础知识：流程控制语句概述beginend语句ifelse、if exists语句while、break和continue语句waitfor语句return 语句一、流程控制语句概述T-SQL语言与其它高级语言一样，提供了可以控制程序执行流程的语句。使用这些流程控制语句可以更好地组织和控制程序的流程。类型语句块：beginend条件语句：ifelse、if exists循环语句：while、break、continue等待语句：waitfor

37、返回语句：return转移语句：goto（了解）二、beginend语句语句格式：begin 语句 | 语句块 end作用可以将多条T-SQL语句封装起来，构成一个独立的语句块。Begin和end必须成对出现。语句块的开始语句块的结束三、 ifelse、if exists语句语法格式：if 逻辑表达式 语句 | 语句块 else 语句 | 语句块 作用：按条件控制程序的执行。当条件满足时，则在执行条件之后的T-SQL 语句。否则，就执行else后的T-SQL语句（若ELSE部分存在）。if not exists(select语句) 语句 | 语句块 else 语句 | 语句块 三、 ifels

38、e语句实例1：查询学号为“020101”的平均分是否超过了85分，若超过，则输出“XX考出了高分”的信息；否则输出“考的一般”。use xscjdeclare xm varchar(10)if (select avg(成绩) from xs_kc where 学号= 020101 ) 85 begin select xm= 姓名 from xsqk where 学号= 020101 print xm + 考出了高分 end else print 考的一般从学生情况表中取姓名求指定学号的平均成绩三、 ifelse语句课堂练习1：测试xscj库的xsqk表中是否有“田丽”这个人，若有则显示其信息，

39、若无则显示“没有XX这个人！”。use xscjdeclare xm varchar(20)select xm = 田丽if exists (select * from xsqk where 姓名=xm ) select * from xsqk where 姓名=xm else print 没有“ + xm + ”这个人！测试“学生情况表”中是否有这个人四、while、break和continue语句语法格式：while 逻辑表达式 语句 | 语句组 break 语句 | 语句组 continue 说明：Break用于退出最内层的while循环。Continue用于重新开始一次while循环。

40、四、while、break和continue语句实例2：计算1到100中奇数的和，要求显示为“奇数和为：XX”。declare i int, sum intselect i = 1, sum = 0while i 100 begin set sum = sum + i set i = i +2 endprint 奇数和为：+ cast(i as varchar(5)四、while、break和continue语句课堂练习2：计算500以内能被3或7整除的数。declare i intselect i = 1while i 21if n=0 print 年龄大于21的学生人数有: + cast(

41、 n as varchar(5) )else print 没有年龄大于21的学生存储过程必备知识：使用批处理、脚本、注释标识符、常量、变量T-SQL系统内置函数流程控制语句beginend语句ifelse、if exists语句while、break和continue语句waitfor语句return语句存储过程和触发器 存储过程的使用认知目标：掌握存储过程的定义及优点了解常用的系统存储过程能力目标:掌握存储过程的创建和执行(重点)掌握存储过程中输入参数和输出参数的设置和使用方法。 (难点)一、存储过程的基本概念1.存储过程的含义及功能存储过程的含义是一组预先写好的能实现某种功能的T-SQL程

42、序，是存储在服务器上的预编译集合。存储过程是一种独立的数据库对象，在服务器上创建和运行。类似于编程语言中的过程或函数。一、存储过程的基本概念2.存储过程的优点模块化编程执行速度快效率高存储过程在执行1次后，其执行计划就保存在内存中，以后在执行时就不必再进行编译和优化。减少网络流量由于存储过程是存在服务器上的一组T-SQL，在客户端调用时，可以只用一条调用语句即可执行。提供一种安全机制可以限制用户访问存储过程中涉及到的表的权限，只授予执行存储过程的权限，用户只能通过存储过程来访问表，从而保证了数据的安全性。一、存储过程的基本概念3.存储过程的类型系统存储过程是指由SQL Server提供的存储过

43、程，可以作为命令执行。系统存储过程定义在系统数据库master中，其前缀是sp_。用户存储过程 是由用户在当前数据库中创建的存储过程。最好不要以sp开头。若用户定义的存储过程与系统存储过程同名，用户定义的存储过程永远不会执行。二、创建和执行存储过程1.简单存储过程创建格式：create procedure 存储过程名 as SQL语句 说明：create procedure不能与别的T-SQL语句位于同一个批中。相应的执行格式：exec 存储过程名二、创建和执行存储过程1.简单存储过程实例1：创建1个简单的存储过程p_cj1，查询所有学生的学号、姓名、课程号和成绩信息，并按成绩降序排列。use

44、 xscj go create procedure p_cj1 as select xsqk.学号,姓名,课程号,成绩 from xsqk, xs_kc where xsqk.学号=xs_kc.学号 order by 4 descgoexec p_cj1 -执行存储过程二、创建和执行存储过程2.使用输入参数创建格式：create procedure 存储过程名 形参 数据类型 =默认值 ，n as SQL语句 相应的执行格式：execute 存储过程名 实参= 值 ,n 输入参数的传递方式有两种：按位置传递：直接给出参数的值，实参与形参一一对应通过参数名传递：使用“参数名=参数值“的形式，参数

45、可以任意顺序给出。输入形参：接收从外部传递给存储过程的数据。输入实参：将数据传递给存储过程。二、创建和执行存储过程2.使用输入参数实例2：创建1个带有输入参数的存储过程p_cj2，查询指定课程号(作为输入参数)的学生成绩信息。create procedure p_cj2 kch char(3)=101 -有默认值的输入形参：接收外部传递的数据 as select * from xs_kc where 课程号= kchgoexec p_cj2 -（1）使用默认值执行存储过程exec p_cj2 102 -（2）按位置传递参数exec p_cj2 kch=103 -（3）通过参数名传递参数输入实参：将数据传递给存储过程。二、创建和执行存储过程2.使用输入参数课堂练习1：创建并执行带输入参数的存储过程p_xsqk，查询指定学号(作为输入参数)的学生姓名、课程号、成绩。 create procedure p_xsqk xh char(6) as select 姓名，课程号，成绩 from xsqk , xs_kc where xsqk.学号=xs_kc.学号 and xsqk.学号= xhgoexec p_xsqk 020102 -（1）按位置传递参数exec p_xsqk xh=020103 -（2）通过参数名传递参数注意：因输入参数没