Sql基础代码第六章存储过程.docx

1、Sql基础代码 第六章 存储过程Sql基础代码目录第一章 建库建表 21.1 数据库的应用 21.1.1 创建数据库 21.1.2 查询数据库 31.1.3 删除数据库 31.2 表的运用 41.2.1 创建表 41.2.2 删除表 51.3 约束 61.3.1 五种约束 61.3.2 约束的使用 6第二章 数据查询 82.1 建表和插入数据 82.1.1 建立学生信息表 82.1.2 插入信息 92.2 查询运用 112.2.1 数据查询 112.2.2 单表查询 122.2.3 连接查询 132.2.4 操作结果集查询 182.2.5 嵌套查询 182.3 数据库更新 19第三章 函数 1

2、93.1 五种函数的运用 193.1.1 字符串函数 203.1.2 数学函数 213.1.3 日期函数 223.1.4 系统函数 233.1.5 聚合函数 24第四章 变量 254.1 局部变量 264.2 全局变量 27第五章 循环语句 305.1 统计平均成绩 305.2 提分 315.3 根据成绩显示等级 35第六章 存储过程 366.1 存储过程的应用 366.1.1 系统存储过程 366.1.2 Sp_helptext的使用 406.1.3 创建存储过程 416.2 raiserror语句的应用 45第七章 exists子查询 467.1 exists子查询的应用 467.2 求银

3、行利息 497.3 变量查询 517.4 查询考试通过情况 537.5 循环提分 557.6 通过率 57第八章 事物 588.1 使用事物解决银行转账 59第九章 触发器 609.1 检测触发器 629.2 创建触发器 629.3 触发器delete运用 649.4 触发器update运用 659.5 触发器禁止修改运用 66第六章 存储过程6.1 存储过程的应用6.1.1 系统存储过程-列出服务器上的所有数据库-sp_databases-报告有关指定数据库或所有数据库的信息-sp_helpdb-更改数据库的名称-use mastersp_renamedb gongziguanli/*更改前

4、的名称*/,工资管理/*更改后的名称*/-返回当前环境下可查询的对象的列表-sp_tables-以下存储过程都以student数据库为例-use student-回某个表列的信息-sp_columns stuinfo-查看某个表的所有信息-sp_help stuinfo-查看某个表的约束-sp_helpconstraint stuinfo-查看某个表的索引-sp_helpindex stuinfo-列出当前环境中的所有存储过程-sp_stored_procedures-添加或修改登录转户的密码-sp_password 1234,2234-显示默认值、未加密的存储过程、用户定义的存储过程、触发器

5、或视图的实际文本-sp_helptextsp_helptext-创建数据库bankDB，要求保存在D:bankuse mastergoexec xp_cmdshell mkdir d:bank,no_outputif exists(select*from sysdatabases where name=bankDB)drop database bankDBgocreate database bankDBexec xp_cmdshelldir d:bank-查看文件6.1.2 Sp_helptext的使用-显示表的所有文本信息sp_helptextsysobjects-显示数据库的所有文本信息s

6、p_helptextsysdatabasessp_helptextsp_helptextsp_helptextsys.all_columns6.1.3 创建存储过程-用sp_executesql显示stuinfo表use studentdeclare sql nvarchar(1000)declare tablename nvarchar(20) set tablename=stuinfoset sql=select*from +tablename+ where 1=1print sqlexec sp_executesql sql-创建存储过程，查看本次考试平均分及未通过考试的学员名单crea

7、te proc proc_stuasdeclare wavg float-笔试平均分declare lavg float-机试平均分select wavg=avg(wriexam),lavg=avg(labexam)from stumarksprint本次笔试的平均分为：+convert(varchar(10),wavg)print本次机试的平均分为：+convert(varchar(10),lavg)if(wavg80 and lavg80)-判断平均分等级print本班成绩优秀elseprint本班成绩较差print没有通过考试的学员有：print=select*from stumarks

8、 where wriexam60 or labexam60-显示未通过的学员go-调用存储过程exec proc_stu-用存储过程更改及格线create proc proc_stu1 wriexam int,-在存储过程前加变量要加逗号,并且变量前不加declare labexam intasprint没有通过考试的学员有：print=select*from stumarks where wriexamwriexam or labexamlabexam-将笔试及格线和机试及格线设为70exec proc_stu1 wriexam=70,labexam=70-如果希望存储过程后，返回一个或多个

9、值，这是就需要使用输出(output)参数了-修改上列，返回未通过考试的学员人数create proc proc_stu2nopasscount int output,wriexam int=60,labexam int=60asprint笔试及格分数线为：+convert(varchar(10),wriexam)+机试及格分数线为:+convert(varchar(10),labexam)print不及格学员有:select nopasscount=count(*) from stumarks where wriexamwriexamor labexamlabexamselect sname

10、,stuinfo.sno,wriexam,labexamfrom stuinfoinner join stumarks on stuinfo.sno=stumarks.snowhere wriexamwriexamor labexam3)print不及格人数+convert(char(5),count)+分数线还应下调elseprint分数线合适,不用下调6.2 raiserror语句的应用-使用raiserror语句，如果有错误，统计中断退出create proc proc_stu3notpasssum int output,-输出参数wriexam int=60,labexam int=6

11、0asif(not wriexam between 0 and 100)or(not labexam between 0 and 100)beginraiserror(及格线错误,请指定1-100之间的分数,统计中断退出,16,1)-引发系统错误，指定错误的严重级别，调用级别为（默认），并影响error系统变量的值return-退出批处理，后续语句不再执行endgo-调用存储过程，测试raiserror语句 declare sum int,t intexec proc_stu3 sum output,604-笔试及格线误输入分set t=errorprint错误号:+convert(varchar(5),t)if t0return print-if sum=3print未通过人数:+convert(varchar(5),sum)+人，超过60%,及格分数线还应下调elseprint未通过人数:+convert(varchar(5),sum)+人，自控制在60%一下,及格分数线适中Go