oracle数据库.ppt
2023/3/16,oracle数据库,oracle数据库,oracle数据库,存储过程的优点,存储过程封装了商务逻辑，确保一致的数据访问和修改。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限改善性能。预编译的 Transact-SQL 语句，可以根据条件决定执行哪一部分减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包,oracle数据库,存储过程的初始处理,项存入 sysobjects和 syscomments 表,编译过的执行计划放入过程缓存,编译,优化,创建,执行（初次或重新编译）,解析,oracle数据库,存储过程的后续处理,若符合下列条件，则 SQL Server 使用在内存中的计划来执行随后的查询当前的环境和计划编译时的环境相同。服务器、数据库和连接的设置决定了环境存储过程引用的对象不需要名称解析。若被不同用户拥有的对象具有相同的名字，则需要名称解析。一个执行计划产生后，驻留在过程缓存中。仅当需要空间时，SQL Server 将老的、没用的计划移出缓存,oracle数据库,存储过程的后续处理（续）,oracle数据库,在存储过程中使用参数,使用输入参数使用输入参数执行存储过程使用输出参数返回值显式地重新编译存储过程,oracle数据库,使用输入参数,输入参数允许传递信息到存储过程内在 CREATE PROCEDURE 中指定 参数名 数据类型=默认值,USE NorthwindGOCREATE PROC dbo.OverdueOrders2Employee_ID int,Order_date datetime,ASSELECT CONVERT(char(8),OrderDate,1)OrderDate,OrderID,CustomerID,EmployeeIDFROM OrdersWHERE EmployeeID=Employee_ID and OrderDate=Order_dateGO,oracle数据库,使用输入参数执行存储过程,通过参数名传递值(顺序无所谓),通过位置传递参数（顺序保持一致）,EXEC OverdueOrders2 Employee_ID=1,Order_date=1996-7-17,EXEC OverdueOrders2 1,1996-7-17,EXEC OverdueOrders2 Order_date=1996-7-17,Employee_ID=1,oracle数据库,使用输出参数返回值,输出参数：以 OUTPUT 关键字指定的变量,CREATE PROC proc1A int,B int,RESULT int OUTPUTASSET RESULT=A*BGO,执行有输出参数的存储过程,DECLARE answer intEXEC proc1 4,7,answer OUTPUTSELECT answer as ANSWER,必须定义一个变量，以接受返回值,写上OUTPUT,才可以接收到返回值,oracle数据库,实例1：不带参数的存储过程的创建与执行,输入代码：Create proc hyproc1 asSelect*from 职工 where 工资 1800执行代码：Execute hyproc1在进行实验时请注意各数据库中的同名表和同名存储过程。,oracle数据库,实例2：带有输入参数的存储过程的创建与执行,代码：Create proc hyproc2mingz int,maxgz int asSelect*from 职工 where 工资 between mingz and maxgz执行：Execute hyproc2 1500,2000,oracle数据库,实例3：带有输入、输出参数的存储过程的创建与执行,Create proc hyproc3changkuhao varchar(50),maxgz int output,avggz real output AsBeginSelect*from 职工 where 仓库号=changkuhaoSelect maxgz=max(工资)from 职工 where 仓库号=changkuhaoselect avggz=avg(工资)from 职工 where 仓库号=changkuhaoend,oracle数据库,实例3：带有输入、输出参数的存储过程的创建与执行,Declare x1 int,x2 realExecute hyproc3 wh1,x1 output,x2 outputSelect x1 as wh1职工最大工资,x2 as wh1职工平均工资,oracle数据库,实例4：任意输入三个数，输出最大数,Create proc zfproc1x1 int,x2 int,x3 intAs beginDeclare max intIf x1x2Set max=x1ElseSet max=x2if x3maxset max=x3print 三个数中最大的数是：+cast(max as varchar(50)End,execute zfproc1 50,31,48,oracle数据库,实例5：阶乘之和(如:5!+4!+1),create proc zfproc2x intasbegindeclare i int,cj int,sum intselect i=1,cj=1,sum=0while i=xbeginset cj=cj*iset sum=sum+cjset i=i+1endprint cast(x as varchar(50)+阶乘之和是：+cast(sum as varchar(50)end,execute zfproc2 5,oracle数据库,实例6:登陆系统存储过程（P160）,create proc zfproc3hyuser varchar(50),hypwd varchar(50)asbegindeclare msg varchar(50)if hyuser=hystu1beginif hypwd=111set msg=用户名与密码正确，成功登录！elseset msg=密码不正确，请重新输入！endelse if hyuser=hystu2,oracle数据库,实例6:登陆系统存储过程,beginif hypwd=222set msg=用户名与密码正确，成功登录！elseset msg=密码不正确，请重新输入！endelse if hyuser=hystu3beginif hypwd=333set msg=用户名与密码成功登录！elseset msg=密码不正确，请重新输入！endelseset msg=用户名不正确，请重新输入！print msgend,oracle数据库,实例7：带有判断条件插入功能的存储过程,create proc zfproc4zghao varchar(30),ckhao varchar(30),sname varchar(50),sex varchar(10),gz intasbeginif exists(select*from 职工where 职工号=zghao)print 该职工已经存在，请重新输入职工号！else beginif exists(select*from 仓库where 仓库号=ckhao)begininsert into 职工(职工号,仓库号,姓名,性别,工资)values(zghao,ckhao,sname,sex,gz)print 成功的插入一条记录endelseprint 你输入的仓库号不合法，请重新输入仓库号！endEnd,oracle数据库,执行过程 实例7,Execute zfproc4 zg1,wh1,张平,女,1350Execute zfproc4 zg42,wh11,张平,女,1350Execute zfproc4 zg42,wh1,张平,女,1350,oracle数据库,实例8：带有判断条件删除功能的存储过程,create proc zfproc5zghao varchar(50)asbegin if exists(select*from 职工where 职工号=zghao)begin if exists(select*from 订购单where 职工号=zghao)begin delete from 订购单where 职工号=zghao print 该职工有定单，并成功删除！end else print 该职工没有定单！delete from 职工where 职工号=zghao print 删除该职工信息！end else print 该职工不存在，请重新输入职工号！End,oracle数据库,执行过程 实例8,execute zfproc5 zg52execute zfproc5 zg42execute zfproc5 zg1,2023/3/16,oracle数据库,演讲完毕，谢谢听讲!,再见，see you again,
