数据库系统概论 An Introduction to Database System 第八章 数据库编程

1. 数据库系统概论 An Introduction to Database System 第八章 数据库编程

2. 代码注入

3. 注释 • T-SQL中注释两种方式： • /* 注释语句*/ • -- 注释语句 • 批处理 • 批处理是包含一个或多个 T-SQL 语句的组，从应用程序一次性地发送到SQL Server执行。 • Go指令 • 用信号通知SQL Server实用工具一批 T-SQL 语句的结束。 2. T-SQL 语法

4. 声明局部变量 • 使用Declare 语句声明局部变量 • 语法： • Declare @variable_name <datatype>[,…n] • 给局部变量赋值 • 使用Select语句 • Select @variablel_name = expression [From table_name [,…n] Where clause ] • 使用Set语句 • Set @variable_name = expression [,…n] • 查看变量的值 • Select @variable_name 局部变量

5. 例 use students declare @number varchar(10), @name varchar(10) set @number ='111‘ select @name=name from students where number=@number select @number as number,@name as name

6. @@error • 每条Transact-SQL语句执行后，将会对@@error赋值。0代表语句执行成功。1代表失败。 • @@rowcount • 每条T-SQL语句执行后，服务器把此次执行影响的列数返回给@@rowcount以判断是否正常执行。 • @@fetch_status • 如果最后一次提取的状态为成功状态，则为0。如果出错，则为-1。 全局变量

7. Begin …End语句块 If … Else条件判断结构 While循环 Case多重判断结构 Return语句 T-SQL控制语句

8. Begin …End可以将一组SQL语句封装成一个语句块（出于编程的需要）。 任何时候当控制流语句必须执行一个包含两条或两条以上 T-SQL 语句的语句块时，请使用 BEGIN 和 END 语句。 BEGIN 和 END 语句必须成对使用：任何一条语句均不能单独使用。 Begin …End语句块

9. 在执行 T-SQL 语句时强加条件。 • 如果条件满足（布尔表达式返回 TRUE 时），则执行 IF 关键字后的 T-SQL 语句；当不满足 IF 条件时（布尔表达式返回 FALSE），就执行ELSE关键字后的T-SQL语句 • 语法： • IF Boolean_expression • { sql_statement | statement_block } • [ELSE IF Boolean_expression] • { sql_statement | statement_block } • [ ELSE • { sql_statement | statement_block } ] IF … ELSE条件判断结构

10. 设置重复执行 SQL 语句或语句块的条件。 • 只要指定的条件为真，就重复执行WHILE后面的T-SQL语句。 • 可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。 • 语法： • WHILE Boolean_expression • { sql_statement | statement_block } • [ BREAK ] • { sql_statement | statement_block } • [ CONTINUE ] While循环

11. 计算条件列表并返回多个可能结果表达式之一。 • CASE 具有两种格式： • 简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END • CASE 搜索函数计算一组布尔表达式以确定结果。 CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Case多重判断结构

12. 从查询或过程中无条件退出。 • RETURN 即时且完全，可在任何时候用于从过程、批处理或语句块中退出，不执行位于 RETURN 之后的语句。 • 语法： • RETURN [ integer_expression ] • 除非特别指明，所有系统存储过程返回 0 值表示成功，返回非零值则表示失败。 Return语句

13. Transact-SQL游标主要用在存储过程、触发器和Transact-SQL 脚本中，它们使结果集的内容对其它Transact-SQL 语句同样可用。 • 使用游标有四种基本的步骤: • 声明游标 DECLARE CURSOR • 打开游标 OPEN • 提取数据 FETCH • 关闭游标 CLOSE • 释放游标 DEALLOCATE 游标

14. DECLARE cursor_name CURSOR • FOR select_statement • 例： DECLARE authors_cursor2 CURSOR FOR SELECT au_id, au_fname, au_lname FROM authors WHERE state = "UT" ORDER BY au_id 声明游标

15. OPEN cursor_name CLOST cursor_name DEALLOCATE 打开游标 关闭游标 释放游标

16. 当用OPEN语句打开了游标并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。当用OPEN语句打开了游标并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。 您必须用FETCH语句来取得数据。 一条FETCH语句一次可以将一条记录放入程序员指定的变量中。 事实上,FETCH语句是游标使用的核心。 提取数据

17. FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { cursor_name | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ] FETCH语法

18. use students declare test cursor for select number,name from students open test declare @line varchar(10),@n varchar(10) fetch next from test into @n,@line while(@@FETCH_STATUS=0) begin print @line+' '+@n fetch next from test into @n,@line end close test deallocate test 例：

19. 1. 存储过程概念 存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以便以后调用，这样可以提高代码的执行效率。 2、存储过程的特点 接收输入参数并以输出参数的形式将多个值返回至调用过程或批处理。 包含执行数据库操作（包括调用其它过程）的编程语句。 向调用过程或批处理返回状态值，以表明成功或失败以及失败原因。 3. SQL Server存储过程

20. 3. 优点 安全机制：只给用户访问存储过程的权限，而不授予用户访问表和视图的权限。 改良了执行性能：只在第一次执行时进行编译，以后执行无需重新编译，而一般SQL语句每执行一次就编译一次。 减少网络流量：存储过程存在于服务器上，调用时，只需传递执行存储过程的执行命令和返回结果。 模块化的程序设计：增强了代码的可重用性，提高了开发效率。

21. 4. 存储过程类型 用户定义的存储过程：用户定义的存储过程是用户根据需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。 系统存储过程：系统存储过程以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQL Server提供帮助，为用户查看数据库对象提供方便。比如用来查看数据库对象信息的系统存储过程sp_help。从物理意义上讲，系统存储过程存储在资源数据库中。从逻辑意义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。

22. 只能在当前数据库中创建存储过程。 数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。 存储过程是数据库对象，其名称必须遵守标识符命名规则。 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。 注意事项

23. 使用SQL语句创建不带参数的存储过程语法格式如下：使用SQL语句创建不带参数的存储过程语法格式如下： CREATE PROC [ EDURE ]procedure_name [;number] [ WITH { RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}] ASsql_statement [ ...n ] procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。 RECOMPILE ： SQL 不会缓存该过程的计划，该过程将在运行时重新编译。 ENCRYPTION ：存储过程加密。 number: 是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。 简单存储过程

24. 可以使用EXECUTE 命令或其名称执行它，其语法格式如下： [ EXEC [ UTE ] ] procedure_name [number ] 注：如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用存储过程的名字执行该存储过程。 执行存储过程

25. 例 use students go create proc up_students as select *from students Exec up_students

26. 修改存储过程的T-SQL语句为ALTER PROCEDURE，其语法格式为： ALTER PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type }[ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION}] [ FOR REPLICATION ] AS sql_statement [ ...n ] 修改简单存储过程

27. 使用DROP PROCEDURE语句删除存储过程 DROP PROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除，其语法格式如下： DROP PROCEDURE 存储过程名称[,…n] 例：删除存储过程。代码如下： USE shop GO DROP PROCEDURE up_GoodsTypes GO 删除存储过程

28. 使用SQL语句创建带参数的存储过程语法格式如下：使用SQL语句创建带参数的存储过程语法格式如下： CREATE PROC [ EDURE ] procedure_name [number ] [ { @parameter data_type } [ = default ] [ OUTPUT ]] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION} ] AS sql_statement [ ...n ] 含参数的存储过程

29. OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用 OUTPUT参数可将信息返回给调用过程。 parameter：存储过程中的输入和输出参数。 data_type：参数的数据类型。 Default：指参数的默认值，必须是常量或NULL。如果定义了默认值，不必指定该参数的值即可执行过程。

30. 在学生数据库中创建存储过程，该存储过程用于根据性别查询学生信息，包括一个输入参数，用于输入要查询的学生的性别在学生数据库中创建存储过程，该存储过程用于根据性别查询学生信息，包括一个输入参数，用于输入要查询的学生的性别 use students go create proc up_query @condition varchar(10) as select * from students where sex=@condition 输入参数的使用

31. 按位置传递：在调用存储过程时，直接给出参数值。如果多于一个参数，给出的参数值要与定义的参数的顺序一致。按位置传递：在调用存储过程时，直接给出参数值。如果多于一个参数，给出的参数值要与定义的参数的顺序一致。 使用参数名称传递：在调用存储过程时，按“参数名=参数值”的形式给出参数值。采用此方式，参数如果多于一个时，给出的参数顺序可以与定义的参数顺序不一致。 如：执行存储过程up_query ，查看 “通信产品”的班级名称，代码如下： EXEC up_query ‘男’ EXEC up_query @condition=‘男’ 执行带参存储过程

32. use students go create proc up_query @condition varchar(10)＝‘男’ as select * from students where sex=@condition 参数所默认值

33. use students go create proc up_output_name @number varchar(10), @name varchar(10) output as select name from students where number=@number 执行 use students declare @name varchar(10) exec up_output_name '111', @name output select @name 输出参数的使用

34. 只能返回整数 use students go create proc up_return @number varchar(10) as begin select name from students where number=@number return @@ROWCOUNT end 执行 declare @lines int exec @lines=up_return '111' 使用return语句返回值

35. Dim conn As New ADODB.Connection Dim commd As New ADODB.Command Dim rs As New ADODB.Recordset Dim conStr As String conStr = "provider=SQLOLEDB;Server=HITLIC\SQLEXPRESS;Database=students;User ID=sa;Password=123456;" conn.Open conStr commd.ActiveConnection = conn commd.CommandType = adCmdStoredProc commd.CommandText = "up_students" Set rs = commd.Execute Dim i As Integer i = 1 Do While Not rs.EOF Sheet1.Cells(i, 1) = rs.Fields(1) i = i + 1 rs.MoveNext Loop rs.Close conn.Close VBA调用存储过程访问数据集

36. Dim conn As New ADODB.Connection Dim commd As New ADODB.Command Dim rs As New ADODB.Recordset Dim conStr As String conStr = "provider=SQLOLEDB;Server=HITLIC\SQLEXPRESS;Database=students;User ID=sa;Password=123456;" conn.Open conStr commd.ActiveConnection = conn commd.CommandType = adCmdStoredProc commd.CommandText = "up_getone" commd.Parameters(1) = "111" Set rs = commd.Execute If Not rs.EOF Then Sheet1.Cells(1, 1) = rs.Fields(1) End If rs.Close conn.Close VBA调用带参存储过程（1）——返回记录集

37. Dim conn As New ADODB.Connection Dim commd As New ADODB.Command Dim conStr As String conStr = "provider=SQLOLEDB;Server=HITLIC\SQLEXPRESS;Database=students;User ID=sa;Password=123456;" conn.Open conStr commd.ActiveConnection = conn commd.CommandType = adCmdStoredProc commd.CommandText = "up_getone1" commd.Parameters(1) = "111" 或 commd.Parameters("@number") = "111" commd.Execute Dim xx As String xx = commd.Parameters(2) 或 xx = commd.Parameters("@name") MsgBox xx conn.Close VBA调用带参存储过程（1）——返回参数

38. Dim conn As New ADODB.Connection Dim commd As New ADODB.Command Dim conStr As String conStr = "provider=SQLOLEDB;Server=HITLIC\SQLEXPRESS;Database=students;User ID=sa;Password=123456;" conn.Open conStr commd.ActiveConnection = conn commd.CommandType = adCmdStoredProc commd.CommandText = "up_getone1" Dim pin, pout Set pin = commd.CreateParameter("in", adChar, adParamInput, 10, "111") Set pout = commd.CreateParameter("out", adChar, adParamOutput, 10) commd.Parameters.Append pin commd.Parameters.Append pout commd.Execute MsgBox commd.Parameters("out") conn.Close VBA调用带参存储过程（2）

39. Dim conn As New ADODB.Connection Dim commd As New ADODB.Command Dim conStr As String conStr = "provider=SQLOLEDB;Server=HITLIC\SQLEXPRESS;Database=students;User ID=sa;Password=123456;" conn.Open conStr commd.ActiveConnection = conn commd.CommandType = adCmdStoredProc commd.CommandText = "up_getone2" commd.Parameters(0).Direction = adParamReturnValue commd.Execute MsgBox commd.Parameters(0) conn.Close VBA调用存储过程——返回return值