06-第6章 T-SQL 基础和存储过程.ppt

第六章,T-SQL基础和存储过程,T-SQL基础和存储过程,第1讲T-SQL语言基础,T-SQL定义、功能、组成定义Transact-SQL（简写为T-SQL）是SQLServer对标准SQL功能的增强与扩充功能T-SQL可以完成数据库上的各种操作，而且可以编制复杂的例行程序组成,系统内置函数,语言基础,T-SQL,学习要点,变量显示和输出语句流程控制语句CASE表达式,标识符注释批处理运算符,语言基础,标识符定义标识符是指用户在SQLServer中定义的服务器、数据库、数据库对象、变量和列等对象名称分类常规标识符定界标识符,T-SQL,T-SQL语言基础,标识符,常规标识符命名原则标识符长度可以为1128个字符。标识符的首字符必须为Unicode2.0标准所定义的字母或_、符号。标识符第一个字符后面的字符可以为UnicodeStandard2.0所定义的字符、数字或、#、$、_符号。标识符内不能嵌入空格和特殊字符。标识符不能与SQLServer中的保留关键字同名,返回,SELECT*FROMinformation,此为常规标识符,标识符,定界标识符实质包含在双引号(“)或者方括号()内的标识符号示例,CREATEtable（column1CHAR（10）NOTNULL，column2INT）,表名Table与T-SQL保留字相同，用方括号来分隔,返回,语言基础,注释定义注释是程序代码中不执行的文本字符串。分类-用于单行注释/*/用于多行注释示例【例6-2】,T-SQL,T-SQL语言基础,注释,【例6-2】使用单行与多行注释,返回,USEstudentGOSELECT*FROMinformation-从表information中查询信息GO/*下面SQL语句查询学生信息*/SELECT*FROMinformationGO,语言基础,批处理说明多条语句作为一个批处理执行时，其语句之间用GO分隔示例【例6-3】批处理语句,T-SQL,T-SQL语言基础,USEstudentGOCREATEVIEWv_informationASSELECT*FROMinformationGOSELECT*FROMv_informationGO,语言基础,运算符算术运算符位运算符比较运算符逻辑运算符,T-SQL,T-SQL语言基础,赋值运算符字符串连接符单目运算符运算符优先级,运算符,算术运算符加（+）减（-）乘（*）除（/）取模（%）,返回,运算符,位运算符对整数或二进制数据进行按位逻辑运算例如与（&）或（|）异或（）求反（）等,返回,运算符,比较运算符用来比较两个表达式的值是否相同SQLServer支持的比较运算符包括：大于。=等于。=大于等于。不等于。!=不等于。!不大于。!、=、!=、！和！=80THEN良WHENgrade=70THEN中WHENgrade=60THEN及格ELSE不及格ENDFROMscoreWHEREsemester=200401,WAITFOR延时语句：2种用法用法一，格式如下：Waitfortime某个时间语句块例：waitfortime16:23:25print等到某个时间执行的语句！,用法二：Waitfordelay延迟的时间语句块例：waitfordelay00:00:10print等待10秒种后输出的语句！,T-SQL基础和存储过程,第2讲存储过程,存储过程,存储过程简介企业管理器创建存储过程T-SQL创建和执行存储过程修改和删除存储过程常用系统存储过程,学习要点,存储过程,简介定义：SQLServer提供的一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务分类系统提供的存储过程定义的存储过程扩展存储过程,存储过程,存储过程,企业管理器创建存储过程Step1:展开数据库右键选择“存储过程”快捷菜单,next,存储过程,企业管理器创建存储过程Step2:打开“新建存储过程”对话框书写存储过程定义,next,存储过程,企业管理器创建存储过程Step3:查看存储过程,存储过程,存储过程,T-SQL创建和执行存储过程基本语句创建：执行：示例存储过程：【例6-26】执行存储过程：【例6-27】综合【例6-28】,存储过程,CREATEPROCEDURE存储过程名参数1数据类型=默认值OUTPUT，参数n数据类型=默认值OUTPUTASSQL语句,EXECprocedure_nameValue_List,【例6-26】创建一存储过程，检索信息021班学生的记录,返回,USEstudentGOCREATEPROCEDUREpr_informationASSELECTs_name，s_addressFROMinformationWHEREs_class=信息021,【例6-27】执行一个名为pr_student的存储过程返回结果,返回,USEstudentGOpr_studnet,【例6-28】创建一存储过程，检索信息021班学生的记录调用AddCourse存储过程插入一条课程号为gdsx、课程名为高等数学、学分为3的记录,返回,USEstudentGOCREATEPROCAddCourse(c_noVARCHAR(12),c_nameVARCHAR(20),c_creditINT,c_typeVARCHAR(12)ASINSERTINTOcourseVALUES(c_no,c_name,c_credit,c_type),EXECAddCoursegdsx,1001高等数学(2),3,基础课,存储过程,修改和删除存储过程基本语句格式修改删除示例修改【例6-32】,存储过程,ALTERPROCEDURE存储过程名参数1数据类型=默认值OUTPUT，参数n数据类型=默认值OUTPUTASSQL语句,DROPPROCEDURE存储过程名,【例6-32】修改存储过程,返回,USEstudentGO-创建存储过程p_1CREATEprocp_1AsSELECT*FROMcourseGO-修改存储过程p_1ALTERPROCp_1ASSELECTs_name，s_addressFROMinformationWHEREs_class=信息021GOEXECp_1,返回结果,存储过程,常用系统存储过程sp_helpsp_helptextsp_dependssp_stored_proceduressp_rename,存储过程,常用存储过程,sp_help显示存储过程的参数及其数据类型基本语句格式,返回,sp_helpobjname=name,存储过程的名称,常用存储过程,sp_helptext显示存储过程的源代码基本语句格式,返回,sp_helptextobjname=name,常用存储过程,sp_depends显示和存储过程相关的数据库对象基本语句格式,返回,sp_depENDsobjname=object,为要查看依赖关系的存储过程的名称,常用存储过程,sp_stored_procedures返回当前数据库中的存储过程列表,返回,常用存储过程,sp_rename重命名存储过程,返回,sp_rename原存储过程名称，新存储过程名称,1.游标的概念2.声明游标3.打开游标4.数据处理5.关闭游标6.释放游标,游标,定义:步骤:类型:,游标（Cursor）是用户从查询记录集中逐条逐行地访问（移动读取、修改或删除）记录的数据处理机制。,声明、打开、使用(移动读取、删除、修改)、关闭、释放5步,(1)静态(Static)：只能看到open时的记录内容。(2)动态(Dynamic)：随时能看到当前的记录内容，包括别人所做的修改。(3)只进(ForwardOnly)：只能从前往后一条一条移动记录指针。(4)滚动(Scroll)：向前、向后，一条或多条移动记录指针。,游标的概念,declare游标名cursorlocal|globalforward_only|scrollstatic|keyset|dynamic|fast_forwardread_only|scroll_locks|optimisticforselect语句forupdateof列名,.n,声明游标,语法格式：OPEN游标名当游标被打开时，行指针会指在第一行之前打开游标后,如果error=0表示打开操作成功打开游标后,可用cursor_rows返回游标记录数-m游标被异步填充.-m是键集中当前的行数-1游标为动态。符合条件记录的行数不断变化0没有符合的记录、游标没打开、关闭或释放n游标已完全填充。n是在游标中的总行数,打开游标,-【例】用游标函数查询记录数use教学管理数据库declarec学生游标cursorkeysetforselect*from学生信息表openc学生游标iferror=0print学生总数：+convert(char(5),cursor_rows)closec学生游标deallocatec学生游标,数据处理(1)提取数据,语法格式：fetchnext|prior|first|last|absoluten|relativenfromglobal游标名into变量名,.n功能：在查询记录集中移动指针并提取一行数据。First第一行；Next下一行；Prior上一行；Last最后一行；absolutenn0,第一行开始正数的第n行；n0,从当前行开始正数的第n行；n0,从当前行开始倒数的第n行。,数据处理(1)提取数据,注意：查询结果用into子句写入局部变量，须先声明局部变量的类型和宽度，且与select语句中相应列的顺序、类型和宽度一致。第一次执行fetchnext，则将获取游标中的第一行数据。打开游标后第一次执行fetchprior，则得不到任何数据。用fetch_status返回执行FETCH操作之后，当前游标指针的状态。状态值如下：0表示行已成功地读取。-1表示读取操作已超出了结果集。-2表示行在表中不存在。,-【例】：使用游标从课程信息表中逐行提取记录。use教学管理数据库select*from课程信息表declarec课程cursorforselect*from课程信息表openc课程fetchnextfromc课程whilefetch_status=0fetchnextfromc课程closec课程deallocatec课程,语法格式：update表或视图set列名=表达式,wherecurrentof游标名功能：修改当前行指定字段的值,数据处理(2)修改数据,-【例】使用游标更新课程临时表中第2行课程名称为:网络技术,授课教师为:刘德华。select*into课程临时表from课程信息表select*from课程临时表declarec课程游标cursorforselect*from课程临时表openc课程游标fetchc课程游标fetchc课程游标update课程临时表set课程名称=网络技术,授课教师=刘德华wherecurrentofc课程游标closec课程游标deallocatec课程游标select*from课程临时表droptable课程临时表,数据处理(3)删除数据,语句格式：deletefrom表或视图wherecurrentof游标名功能：删除游标名指定的当前行数据。,-【例】使用游标删除课程临时表中第2行的数据。use教学管理数据库select*into课程临时表from课程信息表select*from课程临时表declarec课程游标cursorforselect*from课程临时表openc课程游标fetchc课程游标fetchc课程游标deletefrom课程临时表wherecurrentofc课程游标closec课程游标deallocatec课程游标select*from课程临时表droptable课程临时表,关闭游标,打开游标时锁定与其关联的查询结果集。关闭完游标后释放与游标关联的查询结果集。语法格式：CLOSEGLOBAL游标名,释放游标，即释放其占用系统资源。语法格式：DEALLOCATEGLOBAL游标名,释放游标,关闭/释放游标,定义:步骤: