oracle数据库

计算机科学与技术专业定义存储过程存储过程是存储在服务器上的Transact-SQL语句的命名集合是封装重复性任务的方法支持用户声明变量、条件执行以及其他强有力的编程特性SQLServer中的存储过程与其他编程语言中的过程类似，它可以包含执行数据库操作（包括调用其他过程）的编程语句接受输入参数向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）以输出参数的形式将多个值返回至调用过程或批处理计算机科学与技术专业存储过程的优点存储过程封装了商务逻辑，确保一致的数据访问和修改。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限改善性能。预编译的Transact-SQL语句，可以根据条件决定执行哪一部分减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包计算机科学与技术专业存储过程的初始处理项存入sysobjects和syscomments表编译过的执行计划放入过程缓存编译优化创建执行（初次或重新编译）解析计算机科学与技术专业存储过程的后续处理若符合下列条件，则SQLServer使用在内存中的计划来执行随后的查询当前的环境和计划编译时的环境相同。服务器、数据库和连接的设置决定了环境存储过程引用的对象不需要名称解析。若被不同用户拥有的对象具有相同的名字，则需要名称解析。一个执行计划产生后，驻留在过程缓存中。仅当需要空间时，SQLServer将老的、没用的计划移出缓存计算机科学与技术专业存储过程的后续处理（续）检索到的执行计划未用过的计划过时被清除执行计划执行上下文SELECT*FROMdbo.memberWHEREmember_no=?连接18082连接2连接3241003计算机科学与技术专业在存储过程中使用参数使用输入参数使用输入参数执行存储过程使用输出参数返回值显式地重新编译存储过程计算机科学与技术专业使用输入参数输入参数允许传递信息到存储过程内在CREATEPROCEDURE中指定@参数名数据类型[=默认值]USENorthwindGOCREATEPROCdbo.OverdueOrders2@Employee_IDint,@Order_datedatetimeASSELECTCONVERT(char(8),OrderDate,1)OrderDate,OrderID,CustomerID,EmployeeIDFROMOrdersWHEREEmployeeID=@Employee_IDandOrderDate=@Order_dateGO计算机科学与技术专业使用输入参数执行存储过程通过参数名传递值(顺序无所谓)通过位置传递参数（顺序保持一致）EXECOverdueOrders2@Employee_ID=1,@Order_date='1996-7-17'EXECOverdueOrders21,'1996-7-17'EXECOverdueOrders2@Order_date='1996-7-17',@Employee_ID=1计算机科学与技术专业使用输出参数返回值输出参数：以OUTPUT关键字指定的变量CREATEPROCproc1@Aint,@Bint,@RESULTintOUTPUTASSET@RESULT=@A*@BGO执行有输出参数的存储过程DECLARE@answerintEXECproc14,7,@answerOUTPUTSELECT@answerasANSWER必须定义一个变量，以接受返回值写上OUTPUT,才可以接收到返回值计算机科学与技术专业实例1：不带参数的存储过程的创建与执行输入代码：Createprochyproc1asSelect*from职工where工资1800执行代码：Executehyproc1在进行实验时请注意各数据库中的同名表和同名存储过程。广西大学信息网络中心卢豫开计算机科学与技术专业实例2：带有输入参数的存储过程的创建与执行代码：Createprochyproc2@mingzint,@maxgzintasSelect*from职工where工资between@mingzand@maxgz执行：Executehyproc21500,2000广西大学信息网络中心卢豫开计算机科学与技术专业实例3：带有输入、输出参数的存储过程的创建与执行Createprochyproc3@changkuhaovarchar(50),@maxgzintoutput,@avggzrealoutputAsBeginSelect*from职工where仓库号=@changkuhaoSelect@maxgz=max(工资)from职工where仓库号=@changkuhaoselect@avggz=avg(工资)from职工where仓库号=@changkuhaoend广西大学信息网络中心卢豫开计算机科学与技术专业实例3：带有输入、输出参数的存储过程的创建与执行Declare@x1int,@x2realExecutehyproc3'wh1',@x1output,@x2outputSelect@x1aswh1职工最大工资,@x2aswh1职工平均工资计算机科学与技术专业实例4：任意输入三个数，输出最大数Createproczfproc1@x1int,@x2int,@x3intAsbeginDeclare@maxintIf@x1@x2Set@max=@x1ElseSet@max=@x2if@x3@maxset@max=@x3print'三个数中最大的数是：'+cast(@maxasvarchar(50))Endexecutezfproc150,31,48计算机科学与技术专业实例5：阶乘之和(如:5!+4!+…+1)createproczfproc2@xintasbegindeclare@iint,@cjint,@sumintselect@i=1,@cj=1,@sum=0while@i=@xbeginset@cj=@cj*@iset@sum=@sum+@cjset@i=@i+1endprintcast(@xasvarchar(50))+'阶乘之和是：'+cast(@sumasvarchar(50))endexecutezfproc25计算机科学与技术专业实例6:登陆系统存储过程（P160）createproczfproc3@hyuservarchar(50),@hypwdvarchar(50)asbegindeclare@msgvarchar(50)if@hyuser='hystu1'beginif@hypwd='111'set@msg='用户名与密码正确，成功登录！'elseset@msg='密码不正确，请重新输入！'endelseif@hyuser='hystu2‘广西大学信息网络中心卢豫开计算机科学与技术专业实例6:登陆系统存储过程beginif@hypwd='222'set@msg='用户名与密码正确，成功登录！'elseset@msg='密码不正确，请重新输入！'endelseif@hyuser='hystu3'beginif@hypwd='333'set@msg='用户名与密码成功登录！'elseset@msg='密码不正确，请重新输入！'endelseset@msg='用户名不正确，请重新输入！'print@msgend计算机科学与技术专业实例7：带有判断条件插入功能的存储过程createproczfproc4@zghaovarchar(30),@ckhaovarchar(30),@snamevarchar(50),@sexvarchar(10),@gzintasbeginifexists(select*from职工where职工号=@zghao)print'该职工已经存在，请重新输入职工号！'elsebeginifexists(select*from仓库where仓库号=@ckhao)begininsertinto职工(职工号,仓库号,姓名,性别,工资)values(@zghao,@ckhao,@sname,@sex,@gz)print'成功的插入一条记录'endelseprint'你输入的仓库号不合法，请重新输入仓库号！'endEnd广西大学信息网络中心卢豫开计算机科学与技术专业执行过程实例7Executezfproc4'zg1','wh1','张平','女',1350Executezfproc4'zg42','wh11','张平','女',1350Executezfproc4'zg42','wh1','张平','女',1350计算机科学与技术专业实例8：带有判断条件删除功能的存储过程createproczfproc5@zghaovarchar(50)asbeginifexists(select*from职工where职工号=@zghao)beginifexists(select*from订购单where职工号=@zghao)begindeletefrom订购单where职工号=@zghaoprint'该职工有定单，并成功删除！'endelseprint'该职工没有定单！'deletefrom职工where职工号=@zghaoprint'删除该职工信息！'endelseprint'该职工不存在，请重新输入职工号！'End计算机科学与技术专业执行过程实例8executezfproc5'zg52'executezfproc5'zg42'executezfproc5'zg1'计算机科学与技术专业实例9：带有判断条件更新新功能的存储过程Createproczfproc9@zghaovarchar(50),@newzghaovarchar(50)AsBeginIfexists(select*from职工where职工号=@zghao)beginIfexists(select*from订购单where职工号=@zghao)BeginUpdate订购单set职工号=@newzghaowhere职工号=@zghaoPrint'该职工有定单！并成功修改了定单中的职工号！'EndElseprint'该职工没有定单！'Update职工set职工号=@newzghaowhere职工号=@zghaoprint'更新该职工信息！'EndElsePrint'该职工不存在，请重新输入职工号'end