存储过程实验

‘壹’ oracle存储过程失效重启后恢复正常

根据oracle数据库的特点和提供的工具，主要方法有以下几种方法：

利用逻辑备份使用import工具丢失数据的表

利用物理备份来通过还原数据文件并进行不完全恢复

利用dbms_logmnr包从redo log文件中恢复

利用flashback特性恢复数据

前提

为了方便使用方法的介绍，上述恢复方法都将基于以下场景进行：系统管理员在前一天晚上11点用export对数据库做了全库逻辑备份，然后对所有数据文件进行了热备份。第二天上午10点，系统管理员在修改表TFUNDASSET的数据时，由于修改语句的条件写错了，导致一批记录(几千条)的ztm字段被修改成了错误的值，而且已经提交。这个表是资产表，相对而言数据变化不频繁。

一、利用逻辑备份使用import工具恢复丢失的数据

export/import是oracle提供的用于对数据库进行逻辑备份的工具。该工具适用于备份那些数据量不大、业务量不多的数据库系统。因为如果在前一天晚上11点用export做了逻辑备份，那么当今天上午10点数据库意外崩溃时，从备份起到数据库崩溃的这段时间里的数据修改操作(包括DDL和DML)都会丢失。如果丢失数据内的表上的数据是相对比较稳定，也就是说该表上基本没有DML操作，例如标准代码表、分区表里的历史数据，那么采用import来导入该表可以比较完整的恢复数据。如果该表是经常变化的业务表，那么这些丢失的数据只能根据业务情况从纸质记录恢复，或者其他途径恢复。

▲示例如下：这个表是一个资产表。相对来说，今天系统运行中修改的数据较少，丢失的数据量可以承受或者可以从别的途径恢复。那就可以用import来恢复。

方法一：

1、把这个表的数据备份到另一个表：

.png

2、删除该表的记录:

.png

3、执行下面的命令：

.png

这个命令中在关键字tables中指定需要导入的表名字，ignore=y表示忽略表已经存在的错误。

4、导入结束后，检查表中的记录，并用适当的方法恢复当天的修改。

方法二：

1、 把需要恢复的表导入到另一个用户下面：

.png

2、检查数据以后，把原表记录删除：

.png

3、然后从另一用户表中插入回去：

.png

4、 数据量比较大时可以采用如下方法：

.png

二、利用物理备份来通过还原数据文件并进行不完全恢复

如果数据库运行在归档模式下，那么可以通过使用以前的数据文件备份进行还原，然后利用归档日志进行前滚，直到回滚到错误操作的时间点前，然后重置日志文件打开数据库。

可以通过下列方法确认是否是运行在归档模式：

.png

如果是如上所示，那么就是运行在归档模式了。

▲假定在前一天晚上11点做了全库物理备份，那么可以考虑如下恢复：

1、关闭数据库：

由于数据库的不完全恢复必须在一个关闭的数据库上实施，利用一个旧的数据库的备份还原，然后用日志根据需要逐步前滚，而不能还原一个新的备份，再回退到某个时间点。

通知各客户端数据库将关闭，然后发出：

.png

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

2、确定错误操作的时间：

可以根据操作员的估计来确定不完全恢复需要前滚停止的时间，也可以利用LogMiner来分析日志文件(这个工具将在后面介绍)，找出错误操作的准确时间。

3、还原数据文件：

先对当前的数据库文件进行备份，然后再用以前的最近一次备份覆盖现有数据文件。注意：不覆盖现有的控制文件。

4、基于时间点恢复，启动数据库到装配状态：

.png

这样数据库就恢复到了2015年10月20日的9点58分零秒。

然后再利用业务资料补充这段时间内的数据。

三、利用dbms_logmnr包从log文件中恢复

这个包是由Oracle提供，与dbms_logmnr_d包配合使用可以方便地分析联机日志文件和归档日志文件，从这些日志文件中提取出所有对数据库的更改操作。

在使用这个包之前，需要先做一些设置和修改：

1、打开initorcl.ora，修改初始化参数utl_file_dir，设置dbms_logmnr_d包将要使用的数据字典文件的放置目录。

.png

然后重启数据库使参数生效。

2、以sys用户连接到数据库执行dbmslmd.sql脚本重建dbms_logmnr_d这个包。

应用Logminer分析重做日志文件的操作主要有以下步骤：

● 使用dbms_logmnr_d里的存储过程build创建一个外部数据字典文件；

● 使用dbms_logmnr里的存储过程add_logfile添加要分析的日志文件；

● 使用dbms_logmnr里的存储过程start_logmnr启动分析；

● 查询与dbms_logmnr相关的几个视图来获取日志文件内容；

● 使用dbms_logmnr里的存储过程end_logmnr结束分析。

▲下面详细讲述使用的过程

1、使用dbms_logmnr_d里的存储过程build创建一个外部数据字典文件：

.png

2、使用dbms_logmnr里的存储过程add_logfile添加要分析的日志文件到待分析文件列表：

.png

如果没有运行在归档模式，那么由于重做日志文件的循环使用可能导致日志文件被覆盖而无法获取到所要寻找的恢复条目。如果运行在归档模式，则可以通过查看$ORACLE_HOMEadminorclbmp目录下的alert_orcl.log里日志文件归档的时间和错误操作的时间来确定加入哪些归档日志文件到待分析的文件列表中去。

.png

注意：执行以上过程时logfilename参数需要写日志文件的全路径，否则会报错。重复以上操作直到把所有需要分析的文件都加到列表中去。这样就可以启动进行分析。

3、使用dbms_logmnr里的存储过程start_logmnr启动分析；

.png

这样就可以通过下面的查询来获取日志文件的内容了。

4、查询与dbms_logmnr相关的几个视图来获取日志文件内容；

.png

这样就可以找出要恢复所需的语句。注意：v$logmnr_contents只对执行dbms_logmnr.start_logmnr的会话有效，如果通过其他会话或者使用dbms_logmnr.end_logmnr终止了分析，都将不能访问v$logmnr_contents的数据。如果要使其他会话也能获取到这些数据，可以通过另外建表来实现，如：

create table undo_sql as select * from v$logmnr_contents。

再对undo_sql进行授权，其他用户就可以访问v$logmnr_contents的数据了。

5、使用dbms_logmnr里的存储过程end_logmnr结束分析。

使用完成以后用下面的命令来结束分析活动：exec dbms_logmnr.end_logmnr;

这样就释放了分配给logminer的资源(内存和数据结构)。

从上面的过程可知，如果是更新的数据量比较大，而日志文件比较小，就可能会导致日志文件的切换。如果没有及时去挖掘日志文件(没有运行在归档模式)，那么可能会由于日志文件的循环使用而导致数据不可恢复。如果运行在归档模式，也可能由于需要分析的日志文件比较多而时间较长。

四、利用flashback新特性恢复数据

Oracle9i 开始提供了闪回查询(Flashback Query)功能，对于误删除或者误更新并且已经commit了的情况提供了简便快捷的恢复方法；而在Oracle 提供闪回查询之前，碰到这种情况只能通过备份来进行基于时间点的恢复或者使用logmnr挖掘日志来恢复，无疑这比闪回查询要麻烦而且费时。

使用这个Flashback Query特性的前提条件：

1. 数据库必须处于Automatic Undo Management 状态。

.png

2. 最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数(单位为秒)指定

.png

可以通过ALTER SYSTEM SET UNDO_RETENTION = ;来动态修改参数值。

▲如何使用Flashback Query来恢复数据呢？

1. 通过SQL

.png

使用SELECT 语句的AS OF 来进行闪回查询，语法如下：

使用AS OF 关键字来对表，视图或者物化视图进行Flashback Query，如果指定了SCN，那么expr 部分必须是一个数字，如果指定了TIMESTAMP，那么expr 必须是一个timestamp类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。

下面我们使用scott 方案来作一个实验。

.png

如果想在update 的子查询部分使用AS OF，那么该查询只能返回一条记录，否则将会报错。

可以通过添加一个临时表作为中转，然后再作更新，如下：

.png

2．通过DBMS_FLASHBACK包来恢复

DBMS_FLASHBACK 包提供了以下几个函数：

ENABLE_AT_TIME：设置当前SESSION 的闪回查询时间

ENABLE_AT_SYSTEM_CHANGE_NUMBER：设置当前SESSION 的闪回查询SCN

GET_SYSTEM_CHANGE_NUMBER：取得当前数据库的SCN

DISABLE：关闭当前SESSION 的闪回查询

当将一个SESSION 设置为闪回查询模式之后，后续的查询都会基于那个时间点或者SCN 的数据库状态，如果SESSION 结束，那么即使没有明确指定DISABLE，闪回查询也会自动失效。

当SESSION 运行在闪回查询状态时，不允许进行任何DML 和DDL 操作。如果要用DML操作来进行数据恢复就必须使用PL/SQL 游标。

▲示例：

.png

通过上面的例子可以看出，只要这个修改的时间不早于sysdate- (UNDO_RETENTION指定的秒数),就可通过这种方式来恢复数据。

.png

对于问题中的批量数据，可以写个过程来完成获取到更改前的数据：

然后再用这个临时表里的数据来更新TFUNDASSET就可以了。

五、总结

比较以上几种恢复数据的方法的使用过程，我们可以看出：

● exp/imp只适合于数据变化不大的表的数据丢失的情况，即使用这种方法处理后也需要从业务办理资料中修正数据，否则导致数据丢失；

● 采用基于时间点的不完全恢复可以恢复丢失的数据，但是需要关关闭数据库，减少系统可用时间，而且也会丢失恢复时间点以后的数据；

● 使用LogMiner可以较好的恢复数据，但是要求数据库尽可能运行在归档模式，否则也可能导致数据丢失。好处是不用关闭系统，能够从日志文件中得到所有的数据。

● 使用Flashback最方便和简洁，可以直接得到修改前的数据，但是需要依赖系统设置，并且需要占用大量的回滚表空间。

因此选择什么样的方法来恢复数据，取决于你的系统环境和具体情况，不能生搬硬套。采用正确的方法才能最大程度的减少数据的丢失。

当然，最好是不需要用到这些恢复的办法。前提是，你必须做好以下的工作：

1、 为不同环境创建不同的数据库用户、不同密码(如果不能用户不同，一定要密码不同)；

2、 将owner和应用用户分开，并做适度授权；

3、 在做DML前，先用同样的条件做查询，看根据结果集是否符合预期。

‘贰’ 数据库实验-sql存储过程

CREATE PROCEDURE P1
@XH VARCHAR(10) AS
IF @XH NOT IN(SELECT 学号 FROM 选课) INSERT 选课 VALUES(@XH,'1',0)

ELSE IF NOT EXISTS(SELECT * FROM 选课 WHERE 学号=@XH AND 成绩>=60)
DELETE FROM 选课 WHERE 学号=@XH
ELSE SELECT * FROM 选课 WHERE 学号=@XH

‘叁’ 求SQL数据库实验报告

*****系实验（上机）报告

课程名称 数据库系统基础
实验名称 数据查询与存储过程
学号 33
学生姓名 嘻习喜戏
成绩

年 月 日

序号 5 实验名称 SQL数据查询
实验目的：
熟练掌握SQL SELECT 语句，能够运用该语句完成各种查询。

实验内容：
用SQL SELECT 语句完成下列查询：
1． 查询客户表中的所有记录。
2． 从订购单表中查询客户号信息（哪些客户有订购单）。
3． 查询单价在20元以上（含）的产品信息。
4． 查询单价在20元以上（不含）的产品名称为牛奶的产品信息。
5． 查询单价在20元以上（不含）的产品名称为牛奶或德国奶酪的产品信息。
6． 查询有2003年7月订购单的客户名称、联系人、电话号码和订单号信息。
7． 查询有德国奶酪订货的客户的名称、联系人和电话号码信息。
8． 查询有德国奶酪订购需求的订单名细记录。
9． 查询所有订购数量（即订单名细中每个订购项目的数量）都在10个以上的订购单的信息。
10． 找出和德国奶酪同等价位的所有产品信息。
11． 查询单价范围在10元到30元范围内的产品信息（使用BETWEEN…AND）。
12． 从客户表中查询出客户名称中有“公司”二字的客户信息（使用LIKE运算符）。
13． 从客户表中查询出客户名称中没有“公司”二字的客户信息（使用NOT LIKE运算符）。
14． 按产品的单价升序列出全部产品信息。
15． 先按产品名称排序，再按单价排序列出全部产品信息。
16． 从产品表中查询共有几种产品。
17． 从订购名细表中查询德国奶酪的订购总数。
18． 计算德国奶酪所有订购的总金额。
19． 求所有订购单的平均金额，在查询结果中列出订购单的个数和平均金额。
20． 求每个订购单订购的项目数和总金额。
21． 求每个客户包含了德国奶酪订购的订单号及其最高金额和最低金额。
22． 求至少有两个订购项目的订购单的平均金额。
23． 找出尚未最后确定订购单（即订购日期为空值的记录）的有关客户信息（客户的名称、联系人和电话号码）和订单号。
24． 找出在2000年1月1日之后签订的订购单的客户信息（客户的名称、联系人和电话号码）、订单号和订购日期。
25． 列出每类产品（相同名称）具有最高单价的产品信息（产品号、名称、规格说明和单价，提示：使用内外层互相关嵌套查询）。
26． 确定哪些客户目前没有订购单（使用谓词NOT EXISTS）。
27． 查询目前有订购单的客户的信息（使用谓词EXISTS）。
28． 查询符合条件的产品信息，要求该产品的单价达到了任意一款产品名称为牛奶的单价的一半（使用ANY或SOME量词）。
29． 查询符合条件的产品信息，要求该产品的单价大于任何一款产品名称为牛奶的单价（使用ALL量词）。
30． 设计如下的连接操作，并分析各自的特点：
•广义笛卡儿积
•内连接
•外连接
•左连接
•右连接
•全连接

掌握存储过程的创建命令，按照题目要求创建存储过程，理解存储过程的作用。
（1） 建立存储过程。查询单价范围在x元到y元范围内的产品信息。
（2） 建立存储过程。查询在某年某月某日之后签订的订购单的客户信息（客户的名称、联系人和电话号码）、订单号和订购日期。
（3） 建立存储过程。将某产品的订购日期统一修改为一个指定日期。
（4） 建立存储过程。删除没有签订单的客户信息。

实验要求：
用SELECT语句完成本次实验，并提交上机报告。
（1） 掌握存储过程的创建命令，按照实验内容的要求创建存储过程，理解存储过程的作用。
（2） 用CREATE PROCEDURE和EXECUTE 语句完成本次实验，并提交上机报告。
实验准备(本实验预备知识和为完成本实验所做的准备)：
仔细阅读课本第五章关于SQL的数据查询功能的内容
实验过程(实验的操作过程、遇到的问题及其解决办法或未能解决的问题)：
用SQL SELECT 语句完成以上30题查询

实验总结(总结本次实验的收获、未解决的问题以及体会和建议等)：
熟练掌握SQL SELECT 语句，能够运用该语句完成各种查询

附录（SQL语句）：
--1． 查询客户表中的所有记录。
select * from 客户
--2． 从订购单表中查询客户号信息（哪些客户有订购单）
select 客户号from 订单where 订单号!=null
--3． 查询单价在元以上（含）的产品信息。
select *from 产品where 单价> 20 or 单价=20
--4． 查询单价在元以上（不含）的产品名称为牛奶的产品信息。
select *from 产品where 单价>20 and 产品名称='牛奶'
－－． 查询单价在元以上（不含）的产品名称为牛奶或德国奶酪的产品信息
select *from 产品where 单价>20 and (产品名称='牛奶'or 产品名称='德国奶酪')
--6． 查询有年月订购单的客户名称、联系人、电话号码和订单号信息
select 客户名称,联系人, 电话,订单号from 客户,订单where (year(订购日期)=2003 and month (订购日期)=7)and (订单.客户号=客户.客户号)
--7． 查询有德国奶酪订货的客户的名称、联系人和电话号码信息。
select 客户名称,联系人, 电话from 客户
where
(客户号= (select 客户号from 订单where(订单号 =(select 订单号from 订单明细
where 产品号= ( select 产品号from 产品where 产品名称= ' 德国奶酪' )))))
--8． 查询有德国奶酪订购需求的订单名细记录。
select * from 订单明细where (数量!=null and 产品号=(select 产品号from 产品where 产品名称= '德国奶酪'))
--9． 查询所有订购数量（即订单名细中每个订购项目的数量）都在个以上的订购单的信息。
select * from 订单where (订单号in (select 订单号from 订单明细where (数量>10)))
--10． 找出和德国奶酪同等价位的所有产品信息。
select * from 产品where (
--11． 查询单价范围在元到元范围内的产品信息（使用BETWEEN…AND）。
select * from 产品where (单价between 10 and 30)
--12． 从客户表中查询出客户名称中有“公司”二字的客户信息（使用LIKE运算符）
select * from 客户where 客户名称like '%公司%'
--13． 从客户表中查询出客户名称中没有“公司”二字的客户信息（使用NOT LIKE运算符）。
select * from 客户where 客户名称not like '%公司%'
--14． 按产品的单价升序列出全部产品信息。
select *from 产品order by 单价
--15． 先按产品名称排序，再按单价排序列出全部产品信息。
select * from 产品order by 产品名称,单价
--16． 从产品表中查询共有几种产品。
select count ( distinct 产品名称) as 产品总数from 产品
--17． 从订购名细表中查询德国奶酪的订购总数
select sum (数量) as '订购奶酪数量'
from 订单明细
where 产品号in(select 产品号from 产品where 产品名称='德国奶酪')
--18． 计算德国奶酪所有订购的总金额
declare @a money
select @a=(select 单价from 产品where 产品名称='德国奶酪')
declare @b int
select @b=(select sum (数量) as '订购奶酪数量'
from 订单明细
where 产品号in(select 产品号from 产品where 产品名称='德国奶酪'))
declare @c int
select @c=@a*@b
select @c as 总金额
--19． 求所有订购单的平均金额，在查询结果中列出订购单的个数和平均金额。
select 订单均值= avg(单价*数量) ,订单个数=count ( 订单号)
from 订单明细,产品
where 产品.产品号=订单明细.产品号
--20． 求每个订购单订购的项目数和总金额。
select 订单号, count (产品.产品号) as 项目数,sum(数量*单价) as 总金额
from 产品,订单明细
where (产品.产品号=订单明细.产品号)
group by 订单号
--21．求每个客户包含了德国奶酪订购的订单号及其最高金额和最低金额
select 客户.客户号,产品.产品号,数量*单价as 总金额
from 客户,订单,订单明细,产品
where 客户.客户号=订单.客户号and 订单.订单号=订单明细.订单号and 订单明细.产品号=产品.产品号and
产品名称='德国奶酪'
order by 客户号
compute max(数量*单价),min (数量*单价) by 客户号
--22．求至少有两个订购项目的订购单的平均金额
select 订单号,avg(数量*单价),count(产品.产品号)
from 订单明细,产品
where 订单明细.产品号=产品.产品号
group by 订单号
having count(产品.产品号)>=2

--23．找出尚未最后确定订购单（即订购日期为空值的记录）的有关客户信息
-- （客户的名称、联系人和电话号码）和订单号
select 客户名称,联系人,电话,订单明细.订单号
from 客户, 订单明细,订单
where(客户.客户号= 订单.客户号) and 订购日期=null

--24．找出在年月日之后签订的订购单的客户信息
--（客户的名称、联系人和电话号码）、订单号和订购日期

select 客户名称,联系人,电话,订单号,订购日期
from 客户,订单
where 客户.客户号=订单.客户号
and year(订购日期)>1996 and month(订购日期)>4 and day(订购日期)>2

--25．列出每类产品（相同名称）具有最高单价的产品信息
--（产品号、名称、规格说明和单价，提示：使用内外层互相关嵌套查询）
select A.产品号, A.产品名称, A.规格说明, A.单价
from 产品A
where 单价= (SELECT MAX(单价)
FROM 产品B
WHERE A.规格说明= B.规格说明)
--26．确定哪些客户目前没有订购单（使用谓词NOT EXISTS）
select *
from 客户
where not exists (select* from 订单where 客户号=订单.客户号)
--27．查询目前有订购单的客户的信息（使用谓词EXISTS）
select *
from 客户
where exists (select* from 订单where 客户号=订单.客户号)

--28．查询符合条件的产品信息，要求该产品的单价达到了任
--意一款产品名称为牛奶的单价的一半（使用ANY或SOME量词）
select *
from 产品a
where(单价>any(select 单价/2 from 产品b where b.产品名称='牛奶'))
--29．查询符合条件的产品信息，要求该产品的单价大于任何
-- 一款产品名称为牛奶的单价（使用ALL量词）
select *
from 产品a
where(单价>all(select 单价from 产品b where b.产品名称='牛奶'))

--30．设计如下的连接操作，并分析各自的特点：
-- •广义笛卡儿积
SELECT *
FROM 客户CROSS JOIN 订购单
WHERE 客户.客户号= 订购单.客户号

-- •内连接
SELECT *
FROM 客户INNER JOIN 订购单
ON 客户.客户号= 订购单.客户号

-- •外连接
-- •左连接
SELECT *
FROM 客户LEFT JOIN 订购单
ON 客户.客户号= 订购单.客户号
-- •右连接
SELECT *
FROM 客户RIGHT JOIN 订购单
ON 客户.客户号= 订购单.客户号

-- •全连接
SELECT *
FROM 客户FULL JOIN 订购单
ON 客户.客户号= 订购单.客户号

说明：
1. 上机报告上传到211.68.36.251的数据库文件夹中的上传目录
2. 文件名的命名规则为：学号+姓名+实验+序号。如：9724101汪伟的第二次上机报告名为：9724101汪伟实验2
3. 封面由学生填写；
4. 正文的实验名称、实验目的、实验内容、实验要求已经由教师指定；
5. 实验准备由学生在实验或上机之前填写；
6. 实验过程由学生记录实验的过程，包括操作过程、遇到哪些问题以及如何解决等；
7. 实验总结由学生在实验后填写，总结本次实验的收获、未解决的问题以及体会和建议等；
8. 将相关的语句粘贴到附录中。

你自己改改吧。想要word原版的话再说一声。

‘肆’ 创建存储过程 实验内容 1.求共有多少个学生 sql语句

create proc p1 as
select count(*) 学生数 from 学生信息表

‘伍’ 使用SQLServer查询分析器创建存储过程和触发器

sql server 2000 第8章及第九章课后习题答案.

‘陆’ 使用Transact-SQL创建函数和存储过程的实验感想

函数和存储过程对SQL SERVER来说有很大的区别：
1.在SQL SERVER2K以前，没有自定义函数UDF,只有系统函数。
2.无论系统函数和UDF,在系统启动是就进行编译并加载，所以UDF的效率比SP要高，SP只是在调用时才加载（扩展的存储过程除外）。
3.有些函数getdate,exec..都在UDF里不能用！
4.函数必须有返回值，SP则不一定。
5.函数可以包括在FROM子句中，SP则不可以。
6.在SQL SERVE2K中SP可以DEBUG,UDF不可以。 用户定义函数
函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。Microsoft® SQL Server™ 2000 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。

可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION 语句修改、以及使用 DROP FUNCTION 语句除去用户定义函数。每个完全合法的用户定义函数名 (database_name.owner_name.function_name) 必须唯一。

必须被授予 CREATE FUNCTION 权限才能创建、修改或除去用户定义函数。不是所有者的用户在 Transact-SQL 语句中使用某个函数之前，必须先给此用户授予该函数的适当权限。若要创建或更改在 CHECK 约束、DEFAULT 子句或计算列定义中引用用户定义函数的表，还必须具有函数的 REFERENCES 权限。

在函数中，区别处理导致删除语句并且继续在诸如触发器或存储过程等模式中的下一语句的 Transact-SQL 错误。在函数中，上述错误会导致停止执行函数。接下来该操作导致停止唤醒调用该函数的语句。