《数据库系统概念教学课件》3procedu.ppt

Functions and Procedural Constructs函数和过程结构陈良育,Outline,复习：单句SQL 总结函数和过程结构定义、优点和使用场景。结构化程序语言回顾SQL过程结构变量定义，输入输出参数赋值语句选择语句循环语句游标表变量异常处理运行和调试实际案例学习总结,单句SQL语句 总结,(7)SELECT(8)DISTINCT(10)(1)FROM(3)JOIN(2)ON(4)WHERE(5)GROUP BY(6)HAVING(9)ORDER BY Update t_stu set stu_name=张三,age=20 where stu_id=2Delete 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,insert,select,update,delete,drop,tableInt,char,varchar,datetimeNull,is not null,is nullFrom,where,and,or,not,distinct,like(%),as,=,!=,between andGroup by,havingCount,sum,min,max,avgorder by,asc,descJoin,left outer join,right outer join,full joinin,exists,union,union all,with,case when then elseView,primary key,foreign key,constraint,checkRound,ascii,char,left,len,lower,ltrim,replace,reverse,right,rtrim,space,stuff,substring,upper,convert,datepart,getdateAll,any,some,minus,except,intersect(能不用尽量不用),过程结构,过程结构：即按照第二、三代编程语言的结构来编写多句的SQL程序。SQL1999规范：Function,Procedure,method 3种数据库对象实现以上3种对象，可以使用数据库内部语言sql 或者外部语言(c+,java,c#,and etc).function,routine,subroutine,method,procedure 区别Function,Procedure.重点。,存储过程优点/缺点,预编译，存储过程预先编译好放在数据库内，减少编译所耗费的时间.缓存，编译好的存储过程会进入缓存，所以对经常执行的存储过程，除了第一次执行外，其他次执行的速度会有明显提高.减少网络传输，特别对于处理一些数据的存储过程，不必像直接用sql语句实现那样多次传送数据到客户端.性能更快，利用数据库中向量化操作和众多具有较高性能的系统函数.更好的封装，用户不需要了解内部具体的表和数据等信息.更好的安全性，防止sql注入。缺点：增加程序员学习成本，需要多学一门语言。开发调试工具不如普通程序语言，函数库API不如普通程序语言丰富。增加数据库的工作负载。可移植性较差。如果换数据库，那么需要重新编写存储过程或者函数。存储过程是天使or魔鬼？Tradeoff,多层系统。,系统 Trade-off,普通程序语言(1),赋值 x=1;x:=1;x=1选择 if(1=x)x=2;else x=3;If,else,then,elsif,elseif,else if,begin,end&,|,!,and,or,not.,普通程序语言(2),循环语句：while,do.whileFor,foreach,普通程序语言(3),函数 function函数调用/递归,学习程序语言的步骤,变量定义，数字，字符串。1+1，数字字符串相互转化If.else,while,for,case/switch语句。数组函数定义和调用系统函数库异常处理看代码，抄代码，改代码。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 t2Go-表示执行上一句话-注释/*/注释,SQL Server 存储过程(2),带参数的存储过程Create procedure pr_t2stu_id int,-形式入参stu_name varchar(10)AsBegin 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%,SQL Server 存储过程(3),CREATE PROCEDURE pr_stu age int,stu_id int OUTPUT,stu_id_str varchar OUTAS 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 varcharexec pr_stu age=30,stu_id=a output,stu_id_str=b outputselect a,b,SQL Server 存储过程(4),声明 变量定义 全局变量（hungarian命名法）Declare a int(如果有多个变量，用逗号隔开，最后一个不要加逗号)基本赋值语句Set a=1 或者 select a=1 Select a=count(1),b=sum(a)from t1Select a=stu_id from t_stu;如果只有一条记录 那么 就把这条记录的stu_id赋给a如果有多条记录 那么返回最后一条记录的stu_id给a.问题是who is the last one?,SQL Server 存储过程(5),如果查询不返回任何纪录呢？Declare a intSelect a=1Select a=stu_id from t_stu where 1=2Select 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),stu_name varchar(100),age int);Daclare stu_id intInsert into t_stu(stu_name,age)values(Tom,20)Select stu_id=identityerror 0 成功 非零 错误号rowcount sql语句所影响的行数。,SQL Server 存储过程(7),选择语句IF cost 1Begin End,SQL Server 存储过程(8),事务 Transaction一般对DML有效，即insert,update,delete.Select不改变任何数据SQL Server 单句sql 默认自动提交显式事务Begin transactionUpdate.Insert.Insert.Commit transactionBegin transaction begin transaction commit transactionCommit transaction,SQL Server 存储过程(9),当一个存储过程调用另外一个存储过程，通常会发生事务嵌套调用。在嵌套的情况下，只有当最外层的commit，对数据库所有的修改才是永恒的。SQL Server为每个客户端连接保存一个已打开的事务，记录在trancount中，每次打开一个，trancount加一，每次commit，减一，直到最后trancount=1的时候commit保存所有的Begin transaction transaction=1 Begin transaction transaction=2 Begin transaction transaction=3 commit transaction transaction=2 commit transaction transaction=1Commit transaction transaction=0,SQL Server 存储过程(12),Declare cur_stu cursorSet cur_stu=cursor for select stu_id,stu_name,age from t_stuOpen cur_stuFetch next from cur_stu into stu_id,stu_name,ageWhile(fetch_status=0)-0 成功 非零 失败Begin 应用处理 fetch next from cur_stu into stu_id,stu_name,ageEndClose cur_stuDeallocate cur_stu游标很好写，因为符合我们第三代语言(c,c+,c#,java)的风格但是游标实际性能很慢，因为不符合第四代描述型SQL 向量式风格,SQL Server 存储过程(10),但是rollback 回滚就不一样，一个rollback会取消所有已经打开的事务Begin 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 asBegin transaction save transaction s1;insert into.rollback 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.Close语句关闭游标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 mytableGo表变量只能存活在定义它们的批处理脚本，存储过程、函数和触发器，当定义环境执行结束后，临时表就自动删除。不同的连接相互之间不能看到临时表的数据。表变量和正常表进行连接操作时候，需要用伪名。Select a.*from mytable a,t_stu b where a.id=b.stu_id,SQL Server 异常处理(14),错误代号，查询文档基于error的错误处理如果执行一句sql语句有错误，那么error就会被置上错误的代码。If error 0Begin print error.rollback transactionEnderror有一个重要的缺陷是在执行每句sql语句后，都会更改error,这样导致后面的语句所产生的状态覆盖了前面的如果在sqlserver 2000以前，可以参考论文Error Handling in T-SQL:From Casual to Religious,有简便的处理方法，其中包含goto的使用。,SQL Server 异常处理(15),Try-Catch语句 begin try insert.update.insert.End tryBegin catch.End catch在catch模块中，可以使用以下几个函数Error_message()返回给调用应用程序的错误信息Error_number()错误号Error_severity()错误等级Error_state()状态Error_procedure()错误的存储过程名字Error_line()错误的过程行号,SQL Server 存储过程-调试(16),SQL Server 2000内自带的调试工具SQL Server 2005把调试存储过程的功能放到visual studio 2005里面。可参看附件描述的visual studio调试方法如果没有现成工具，那么可以在存储过程里面采用print 中间变量的具体值来判断程序是否正确但是需要特别注意：产品正式发布的时候，需要把这些print语句屏蔽了。否则会影响返回的结果。SQL Server 2008,存储过程调试，SQL Server 2012，https:/,外部存储过程,SQL:1999 permits the use of functions and procedures written in other languages such as C or C+or Java 外部存储过程，即采用通用程序语言写成函数，并编译成数据库内部存储过程。Declaring external language procedures and functionscreate procedure account_count_proc(in customer_name varchar(20),out count integer)language Cexternal name/usr/avi/bin/account_count_proccreate function account_count(customer_name varchar(20)returns integerlanguage Cexternal name/usr/avi/bin/author_count,案例（1）,图书馆借书给定一个学生号，问是否可以借书。这个学生号是有效的这个学生在借的书不能超过5册这个学生不能有超期的。,案例（2）,案例（3）,总结,存储过程变量定义，输入输出参数赋值语句选择语句循环语句游标表变量异常处理运行和调试作业英文书P119/中文书Page77,3.21，3.22，3.23，分别使用单句sql和存储过程实现。,谢 谢！Q&A,