数据库系统概念教学课件3procedure 文档资料

1、Functions and Procedural,Constructs,函数和过程结构,陈良育,Outline,?,复习：单句,SQL,总结,?,函数和过程结构定义、优点和使用场景。,?,结构化程序语言回顾,?,SQL,过程结构,?,变量定义，输入输出参数,?,赋值语句,?,选择语句,?,循环语句,?,游标,?,表变量,?,异常处理,?,运行和调试,?,实际案例学习,?,总结,单句,SQL,语句,总结,?,(7) SELECT (8) DISTINCT (10) ,?,(1) FROM ,?,(3) JOIN ,?,(2) ON ,?,(4) WHERE ,?,(5) GROUP BY ,?,

2、(6) HAVING ,?,(9) ORDER BY ,?,Update t_stu set stu_name = ,张三, age = 20 where stu_id = 2,?,Delete from t_stu where stu_id = 2 and stu_name = ,张三,?,Insert into t_stu(stu_id, stu_name) values(2, ,张三,),?,Create table t_stu (stu_id int , stu_name varchar(100),?,Drop table t_stu,所学过的,SQL,关键字,?,Create, in

3、sert, select, update, delete, drop, table,?,Int, char, varchar, datetime,?,Null, is not null, is null,?,From, where, and, or, not, distinct, like (%), as,?, =, , !=, between and,?,Group by, having,?,Count, sum, min, max, avg,?,order by, asc, desc,?,Join, left outer join, right outer join, full join,

4、?,in, exists, union, union all, with, case when then else,?,View, primary key, foreign key, constraint, check,?,Round, ascii, char, left, len, lower, ltrim,replace,reverse, right,rtrim,space,stuff,substring,upper, convert, datepart, getdate,?,All, any, some, minus, except, intersect (,能不用尽量不用,),过程结构

5、,?,过程结构：即按照第二、三代编程语言的,结构来编写多句的,SQL,程序。,?,SQL2019,规范：,Function, Procedure,method 3,种数据库对象,?,实现以上,3,种对象，可以使用数据库内部语,言,sql,或者外部语言,(c+,java,c#, and etc).,?,function, routine, subroutine, method,procedure,区别,?,Function, Procedure.,重点。,存储过程优点,/,缺点,?,预编译，存储过程预先编译好放在数据库内，减少编译所耗费的时间,.,?,缓存，编译好的存储过程会进入缓存，所以对经常

6、执行的存储过程，,除了第一次执行外，其他次执行的速度会有明显提高,.,?,减少网络传输，特别对于处理一些数据的存储过程，不必像直接用,sql,语句实现那样多次传送数据到客户端,.,?,性能更快，利用数据库中向量化操作和众多具有较高性能的系统函数,.,?,更好的封装，用户不需要了解内部具体的表和数据等信息,.,?,更好的安全性，防止,sql,注入。,?,缺点：,?,增加程序员学习成本，需要多学一门语言。,?,开发调试工具不如普通程序语言，函数库,API,不如普通程序语言丰富。,?,增加数据库的工作负载。,?,可移植性较差。如果换数据库，那么需要重新编写存储过程或者函数。,?,存储过程是天使,or

7、,魔鬼？,Tradeoff,多层系统。,系统,Trade-off,普通程序语言,(1),?,赋值,x=1; x:=1; x=1,?,选择,if(1=x) x=2; else x=3; ,普通程序语言,(2),?,循环语句：,while, do.while,?,For, foreach,普通程序语言,(3),?,函数,function,?,函数调用,/,递归,学习程序语言的步骤,?,变量定义，数字，字符串。,?,1+1,，数字字符串相互转化,?,If.else, while, for, case/switch,语句。,?,数组,?,函数定义和调用,?,系统函数库,?,异常处理,?,看代码，抄代码

8、，改代码。,10%,?,高级特性。,?,一个语言的学习，只有在项目实践后，才能够真正掌握。,SQL Server,存储过程,(1),?,创建,Create procedure pr_t2 as select a, b from t2,?,执行,Execute pr_t2,或者,exec pr_t2,?,删除,Drop procedure pr_t2,?,修改,alter procedure pr_t2 as .,?,Create procedure pr_t2 as select a, b from t2,?,Go -,表示执行上一句话,?,-,注释,?,/* */,注释,SQL Server

9、,存储过程,(2),?,带参数的存储过程,?,Create procedure pr_t2,?,stu_id int, -,形式入参,?,stu_name varchar(10),?,As,?,Begin,?,SET NOCOUNT ON; -,屏蔽显示多少行受影响的信息,?,Select stu_id, stu_name from t_stu where stu_id = stu_id and,stu_name like stu_name,?,End,?,执行,?,Execute pr_t2 stu_id=1, stu_name=%tom%,?,Execute pr_t2 1, %tom%,

10、SQL Server,存储过程,(3),?,CREATE PROCEDURE pr_stu,?,age int,?,stu_id int OUTPUT,?,stu_id_str varchar OUT,?,AS,?,set stu_id = (SELECT MAX(stu_id) from t_stu where age ,age);,?,select stu_id_str = convert(varchar, stu_id);,?,GO,?,执行,?,declare a int, b varchar,?,exec pr_stu age=30, stu_id=a output, stu_id_

11、str = b output,?,select a,b,SQL Server,存储过程,(4),?,声明,变量定义,全局变量,（,hungarian,命名法）,?,Declare a int (,如果有多个变量，用逗号隔开，最后一个不要加逗号,),?,基本赋值语句,?,Set a=1,或者,select a=1,?,Select a=count(1), b = sum(a) from t1,?,Select a=stu_id from t_stu;,?,如果只有一条记录,那么,就把这条记录的,stu_id,赋给,a,?,如果有多条记录,那么返回最后一条记录的,stu_id,给,a.,问题是,w

12、ho is,the last one?,SQL Server,存储过程,(5),?,如果查询不返回任何纪录呢？,?,Declare a int,?,Select a=1,?,Select a=stu_id from t_stu where 1=2,?,Select a,?,变量的值没有被改变,SQL Server,存储过程,(6),?,Update t_stu set a = age = age +1 where stu_id =2,?,全局变量,?,identity,?,返回最近一个,identiti,值, create table t_stu(stu_id int identity(1,1

13、),stu_name varchar(100), age int);,?,Daclare stu_id int,?,Insert into t_stu(stu_name, age) values(Tom, 20),?,Select stu_id=identity,?,error 0,成功,非零,错误号,?,rowcount sql,语句所影响的行数。,SQL Server,存储过程,(7),?,选择语句,?,IF cost = compareprice,?,BEGIN,?,.,?,END,?,ELSE,?,begin,?,.,?,end,?,语句块,begin . End,，,可以嵌套，注意缩

14、进,?,While a 1,?,Begin,?,?,End,SQL Server,存储过程,(8),?,事务,Transaction,?,一般对,DML,有效，即,insert, update, delete. Select,不改变任何数据,?,SQL Server,单句,sql,默认自动提交,?,显式事务,?,Begin transaction,?,Update.,?,Insert.,?,Insert.,?,Commit transaction,?,Begin transaction,?,begin transaction,?,commit transaction,?,Commit tran

15、saction,SQL Server,存储过程,(9),?,当一个存储过程调用另外一个存储过程，通常会发生事务嵌套调用。,?,在嵌套的情况下，只有当最外层的,commit,，对数据库所有的修改才是,永恒的。,?,SQL Server,为每个客户端连接保存一个已打开的事务，记录在,trancount,中，每次打开一个，,trancount,加一，每次,commit,，减一，,直到最后,trancount=1,的时候,commit,保存所有的,?,Begin transaction transaction=1,?,Begin transaction transaction=2,?,Begin tr

16、ansaction transaction=3,?,commit transaction transaction=2,?,commit transaction transaction=1,?,Commit transaction transaction=0,SQL Server,存储过程,(12),?,Declare cur_stu cursor,?,Set cur_stu=cursor for select stu_id, stu_name, age from t_stu,?,Open cur_stu,?,Fetch next from cur_stu into stu_id, stu_na

17、me, age,?,While(fetch_status =0) - 0,成功,非零,失败,?,Begin,应用处理,fetch next from cur_stu into stu_id, stu_name, age,?,End,?,Close cur_stu,?,Deallocate cur_stu,?,游标很好写，因为符合我们第三代语言,(c,c+,c#,java),的风格,?,但是游标实际性能很慢，因为不符合第四代描述型,SQL,向量式风格,SQL Server,存储过程,(10),?,但是,rollback,回滚就不一样，,一个,rollback,会取消所有已经打开的事务,?,Beg

18、in transaction -trancount=1,?,begin transaction - trancount=2,?,rollback transaction trancount=0,?,rollback transaction -,本句报错,?,解决办法,?,if trancount 0,?,rollback transaction,?,保存点：回滚部分事务的机制,?,Create procedure pr_test val int output as,?,Begin transaction,?,save transaction s1;,?,insert into .,?,roll

19、back transation s1;,?,Commit transaction,SQL Server,存储过程,(11),?,游标,cursor,可以理解为一个二维的结果集,?,1. Declare Cursor,创建基于,select,语句的游标,?,declare cursor_name cursor for select .,?,declare cursor_name cursor,?,set cursor_name=cursor for select ,?,2. Open,语句打开游标,?,3. Fetch,语句获取游标的当前记录，存储到局部变量中,?,4.,应用处理,?,5. Cl

20、ose,语句关闭游标,?,6. Deallocate,语句释放游标,SQL Server,表变量,(13),?,表变量：类似于临时表，但是比临时表性能更好，更轻量。,?,Declare mytable table(id int primary key, name varchar(20),?,Insert into mytable values(1, jack),?,Insert into mytable values(2,tom),?,Select * from mytable,?,Go,?,表变量只能存活在定义它们的批处理脚本，存储过程、函数和触发器，,?,当定义环境执行结束后，临时表就自动

21、删除。,?,不同的连接相互之间不能看到临时表的数据。,?,表变量和正常表进行连接操作时候，需要用伪名。,?,Select a.* from mytable a, t_stu b where a.id = b.stu_id,SQL Server,异常处理,(14),?,错误代号，查询文档,?,基于,error,的错误处理,?,如果执行一句,sql,语句有错误，那么,error,就会被置上错误的代码。,?,If error 0,?,Begin,?,print error.,?,rollback transaction,?,End,?,error,有一个重要的缺陷是在执行每句,sql,语句后，都会更

22、改,error,这样,导致后面的语句所产生的状态覆盖了前面的,?,如果在,sqlserver 2000,以前，可以参考论文,Error Handling in T-SQL:,From Casual to Religious,有简便的处理方法，其中包含,goto,的使用。,SQL Server,异常处理,(15),?,Try-Catch,语句,?,begin try,?,insert.,?,update.,?,insert.,?,End try,?,Begin catch,?,.,?,End catch,?,在,catch,模块中，可以使用以下几个函数,?,Error_message(),返回给

23、调用应用程序的错误信息,?,Error_number(),错误号,?,Error_severity(),错误等级,?,Error_state(),状态,?,Error_procedure(),错误的存储过程名字,?,Error_line(),错误的过程行号,SQL Server,存储过程,-,调试,(16),?,SQL Server 2000,内自带的调试工具,?,SQL Server 2019,把调试存储过程的功能放到,visual studio 2019,里面。,?,可参看附件描述的,visual studio,调试方法,?,如果没有现成工具，那么可以在存储过程里面采用,print,中间变量的具,体值来判断程序是否正确,?,但是需要特别注意：产品正式发布的时候，需要把这些,print,语句屏蔽,了。否则会影响返回的结果。,?,SQL Server 2019,存储过程调试，,cns/ryanding/archive/2019/12/06/1896762.html,?,SQL Server 2019,，,mssqltips/sqlservertip/2410/sql-server-2019-,tsql-debugging-enhance