oracle数据库.ppt

《oracle数据库.ppt》由会员分享，可在线阅读，更多相关《oracle数据库.ppt（22页珍藏版）》请在优知文库上搜索。

1、2023-3-16oracle数据库oracle数据库数据库oracle数据库存储过程的优点存储过程的优点v存储过程封装了商务逻辑，确保一致的数据访问和修改。若规则或。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用可以直接使用v屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据。用户不需要访问底层的数据库和数据库内的对象库内的对象v提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限在存储过程引用的所有对象上都有权限

2、v改善性能。预编译的。预编译的 Transact-SQL 语句，可以根据条件决定执语句，可以根据条件决定执行哪一部分行哪一部分v减少网络通信量。客户端用一条语句调用存储过程，就可以完成可。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求的请求/回答包回答包oracle数据库存储过程的初始处理存储过程的初始处理项存入项存入 sysobjects和和 syscomments 表表 编译过的执行计划编译过的执行计划放入过程缓存放入过程缓存编译编译优化优化解析解析oracle数据库存储过

3、程的后续处理存储过程的后续处理v 若符合下列条件，则若符合下列条件，则 SQL Server 使用在内存中的计划使用在内存中的计划来执行随后的查询来执行随后的查询 当前的环境和计划编译时的环境相同。服务器、数据库当前的环境和计划编译时的环境相同。服务器、数据库和连接的设置决定了环境和连接的设置决定了环境 存储过程引用的对象不需要名称解析。若被不同用户拥存储过程引用的对象不需要名称解析。若被不同用户拥有的对象具有相同的名字，则需要名称解析。有的对象具有相同的名字，则需要名称解析。v 一个执行计划产生后，驻留在过程缓存中。仅当需要空间一个执行计划产生后，驻留在过程缓存中。仅当需要空间时，时，SQL

4、 Server 将老的、没用的计划移出缓存将老的、没用的计划移出缓存oracle数据库存储过程的后续处理（续）存储过程的后续处理（续）检索到的执行计划检索到的执行计划未用过的计划过时被清除未用过的计划过时被清除执行计划执行计划执行上下文执行上下文SELECT *FROM dbo.memberWHEREmember_no = ?连接连接18082连接连接2连接连接3241003oracle数据库在存储过程中使用参数在存储过程中使用参数v 使用输入参数使用输入参数v 使用输入参数执行存储过程使用输入参数执行存储过程v 使用输出参数返回值使用输出参数返回值v 显式地重新编译存储过程显式地重新编译存储

5、过程oracle数据库使用输入参数使用输入参数v 输入参数允许传递信息到存储过程内输入参数允许传递信息到存储过程内 在在 CREATE PROCEDURE 中指定中指定 参数名参数名 数据类型数据类型 =默认值默认值 USE NorthwindGOCREATE PROC dbo.OverdueOrders2Employee_ID int ,Order_date datetimeASSELECT CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeIDFROM OrdersWHERE EmployeeID

6、= Employee_ID and OrderDate = Order_dateGO oracle数据库使用输入参数执行存储过程使用输入参数执行存储过程v 通过参数名传递值通过参数名传递值(顺序无所谓顺序无所谓)v 通过位置传递参数（顺序保持一致）通过位置传递参数（顺序保持一致）EXEC OverdueOrders2 Employee_ID = 1 , Order_date = 1996-7-17EXEC OverdueOrders2 1 , 1996-7-17EXEC OverdueOrders2 Order_date = 1996-7-17, Employee_ID = 1 oracle数

7、据库使用输出参数返回值使用输出参数返回值v 输出参数：以输出参数：以 OUTPUT 关键字指定的变量关键字指定的变量CREATE PROC proc1A int , B int , RESULT int OUTPUTASSET RESULT = A * BGO v 执行有输出参数的存储过程执行有输出参数的存储过程DECLARE answer intEXEC proc1 4, 7, answer OUTPUTSELECT answer as ANSWER 必须定义一个变量，必须定义一个变量，以接受返回值以接受返回值写上写上OUTPUT,才才可以接收到返回值可以接收到返回值oracle数据库实例实

8、例1 1：不带参数的存储过程的创建与执行：不带参数的存储过程的创建与执行v输入代码：Create proc hyproc1 asSelect * from 职工 where 工资 1800v执行代码：Execute hyproc1v在进行实验时请注意各数据库中的同名表和同名存储过程。oracle数据库实例实例2 2：带有输入参数的存储过程的创建与执行：带有输入参数的存储过程的创建与执行v 代码：Create proc hyproc2mingz int,maxgz int asSelect * from 职工 where 工资 between mingz and maxgzv 执行：Execut

9、e hyproc2 1500,2000oracle数据库实例实例3 3：带有输入、输出参数的存储过程的创建与执行：带有输入、输出参数的存储过程的创建与执行v Create proc hyproc3v changkuhao varchar(50),maxgz int output,avggz real output Asv Beginv Select * from 职工 where 仓库号=changkuhaov Select maxgz=max(工资) from 职工 where 仓库号=changkuhaov select avggz=avg(工资) from 职工 where 仓库号=ch

10、angkuhaov endoracle数据库实例实例3 3：带有输入、输出参数的存储过程的创建与执行：带有输入、输出参数的存储过程的创建与执行v Declare x1 int,x2 realv Execute hyproc3 wh1,x1 output,x2 outputv Select x1 as wh1职工最大工资,x2 as wh1职工平均工资oracle数据库实例实例4 4：任意输入三个数，输出最大数：任意输入三个数，输出最大数vCreate proc zfproc1vx1 int,x2 int,x3 intvAs beginvDeclare max intvIf x1x2vSet m

11、ax=x1vElsevSet max=x2vif x3maxvset max=x3vprint 三个数中最大的数是：+cast(max as varchar(50)vEndvexecute zfproc1 50,31,48oracle数据库实例实例5 5：阶乘之和：阶乘之和( (如如:5!+4!+1):5!+4!+1)vcreate proc zfproc2vx intvasvbeginvdeclare i int,cj int,sum intvselect i=1,cj=1,sum=0vwhile i=xvbeginvset cj=cj*ivset sum=sum+cjvset i=i+1v

12、endvprint cast(x as varchar(50)+阶乘之和是：+cast(sum as varchar(50)vendvexecute zfproc2 5oracle数据库实例实例6:6:登陆系统存储过程（登陆系统存储过程（P160P160）vcreate proc zfproc3vhyuser varchar(50),hypwd varchar(50)vasvbeginvdeclare msg varchar(50)vif hyuser=hystu1vbeginvif hypwd=111vset msg=用户名与密码正确，成功登录！velsevset msg=密码不正确，请重新

13、输入！vendvelse if hyuser=hystu2oracle数据库实例实例6:6:登陆系统存储过程登陆系统存储过程vbeginvif hypwd=222vset msg=用户名与密码正确，成功登录！velsevset msg=密码不正确，请重新输入！vendvelse if hyuser=hystu3vbeginvif hypwd=333vset msg=用户名与密码成功登录！velsevset msg=密码不正确，请重新输入！vendvelsevset msg=用户名不正确，请重新输入！vprint msgvendoracle数据库实例实例7 7：带有判断条件插入功能的存储过程：带

14、有判断条件插入功能的存储过程vcreate proc zfproc4vzghao varchar(30),ckhao varchar(30),sname varchar(50),sex varchar(10),gz intvasvbeginvif exists(select * from 职工where 职工号=zghao)vprint 该职工已经存在，请重新输入职工号！velse vbeginvif exists(select * from 仓库where 仓库号=ckhao)vbeginvinsert into 职工(职工号,仓库号,姓名,性别,工资)values(zghao,ckhao,

15、sname,sex,gz)vprint 成功的插入一条记录vendvelsevprint 你输入的仓库号不合法，请重新输入仓库号！vendvEndoracle数据库执行过程执行过程 实例实例7v Execute zfproc4 zg1,wh1,张平张平,女女,1350v Execute zfproc4 zg42,wh11,张平张平,女女,1350v Execute zfproc4 zg42,wh1,张平张平,女女,1350oracle数据库实例实例8 8：带有判断条件删除功能的存储过程：带有判断条件删除功能的存储过程vcreate proc zfproc5vzghao varchar(50)v

16、asvbeginv if exists(select * from 职工where 职工号=zghao)v beginv if exists(select * from 订购单where 职工号=zghao)v beginv delete from 订购单where 职工号=zghaov print 该职工有定单，并成功删除！v endv elsev print 该职工没有定单！v delete from 职工where 职工号=zghaov print 删除该职工信息！v endv elsev print 该职工不存在，请重新输入职工号！vEndoracle数据库执行过程执行过程 实例实例8v execute zfproc5 zg52v execute zfproc5 zg42v execute zfproc5 zg12023-3-16oracle数据库