EastNet:Dx项目之Oracle10g技术应用篇

前言
经过二年时间Dx项目的洗刷, 从一个不知道怎么做项目的小兵成长为一个大兵, 经历了学习,高兴,郁闷,离别等非常时期, 其中的滋味在接下来的日子里和大家分享一下, 今天提交的是Dx项目的Oracle10g技术应用篇, 接下来还会有Dx项目管理篇,团队篇等等, 提交的文章的速度和自己的闲忙程度有关, 最近比较忙, 望公司能理解并取消50元/一篇文章的政策? 会尽力多写, 也允许我将其发布在俺的Blog上, 在此也做特别说明: 以下思路均属自己一时看法, 如有异议欢迎交流.
在这个过程中, 也看到了不少, 也明白了不少, 和同事也聊了不少, 有人说你在Dx项目上学到了不少, 还有人说你不适合做项目, 更适合去做别的, 如果让我来说: “做好今天的事, 明天的明天再说” 还有人说, 我比较极端, 如果用我的话说”就是更趋向于专业,力求完美”. 就Dx项目而言-需要变化,就是我所说的一个”变”字,从项目一开始,对项目的很多方面我们(原班Dx项目成员)都提出了质疑, 而最终的决定是:不充许变更. 项目走到今天,也算是有了个盼头, 此时只能用这句话来概括: 只有变化才有可能. 当高岳凯看到这儿的时候, 遗憾当初的建议未必采纳和执行, 再到过来想想, 也不算是什么遗憾, 什么阶段会有什么阶段的政策, 当时的考量重点肯定和现在不一样.

Dx项目之Oracle10g技术应用篇
第一: DBMS_JOB程序包实现存储过程的异步调用-说明.
DBMS_JOB用来在后台运行程序,是数据库中一个极好的工具. 可用于自动调整调度例程任务,例如分析数据表,执行一些归档操作,清理草稿表等等. 使用DBMS_JOB主例程是SUBMIT例程, 对SUBMIT例程中的各变量含义说明:
JOB: 一个作业标识符,由系统来分配(OUT参数).
WHAT: 将要运行什么的SQL文本,必须是有效的PL/SQL语句或一段代码. 例如: 运行存储过程P，可以传递字符串P;(包括分号)给这个例程。无论在WHAT参数中提交什么,将被封装成如下PL/SQL块:
Declare
Job Binary_Integer := :Job;
Next_Date Date := :Mydate;
Broken Boolean := False;
Begin
What
:Mydate := Next_Date;
If Broken Then :B := 1;
Else :B := 0;
End If;
End;

NEXT_DATE: 日期函数字符串,用来计算作业下次运行的时间.
NO_PARSE: 确定WHAT参数在提交时是否进行有效性分析。
INSTANCE: 只得在松耦合聚簇的机器上,在并行服务器模式(一个Oracle可以运行的模式)下才有意义,这将指定在哪个实例上作业可以执行.
FORCE: 只有在并行服务器模式下才有意义. 如设置为True(默认值),可以使用任何实例数据库提交作业, 如设置为False, 相关实例不可用,提交请求将失败.
在DBMS_JOB程序包中也有其它入口点. SUBMIT是用来调度作业的一个，其他的允许操作已调度的作业,执行操作，如RUN,REMOVE和CHANGE操作.
以上是对SUBMIT例程各参数的一个简要说明,具体请参考相关资料。

应用说明(设原有例程为:pr_movedetail):
1.创建一张表,包含每个参数的字段,再加一个ID主码字段.
Create Table run_movedetail
(
ID number primary key,
Param1 varchar2(255),
Param1 varchar2(255),
RunDate Date
)
此表不仅作为将要提交的处理过程排队的地方,而且当提交时,也可作为已提交处理过程保存永久日志的地方.
2.创建fast_movedetail例程(创建作业ID及把各参数Insert到run_movedetail).
create or replace procedure fast_movedetail(p_Param1 varchar2,p_Param2 varchar2)
As
L_job number;
Begin
dbms_job.submit(L_job,’background_movedetail(JOB);’);
Insert into run_movedetail(ID,Param1,Parma2) values (L_job, p_Param1, p_Param2);
End;
该例程将提交一项作业background_movedetail,并且将它传递给JOB参数.
3.创建background_movedetail例程.
Create or replace procedure background_movedetail(p_job in number)
As
L_rec run_movedetail%rowtype;
Begin
Select * into L_rec from run_movedetail where id = p_job;
Pr_movedetail(L_rec.Param1,L_rec.Param2);
Update run_movedetail set RunDate = sysdate where id = p_job;
End;
--调用实际使用的Pr_movedetail例程, 然后更新记录,记录实际提交时间.
总之: DBMS_JOB是一个非常健壮的工具,其它用途请参考Oracle相关书籍.

第二.简单应用Oracle功能强大的分析函数.
在实际应用中,会有非常复杂的SQL查询,但性能都不是很理想,而Oracle提供的分析函数,即可以实现一些复杂的查询且性能也非常好. 下面给出几个示例对所用的分析函数做一说明(同时给出常规SQL实现). 当然Oracle提供的分析函数不至于以下几个,要对所有的分析函数一一说明,至少得上百页.这理就不哆嗦了,感兴趣的同志可查阅Oracle PL/SQL相关书籍.
应用说明: 对ikdbom各字段进行说明: bomno-BOM编码. Itemcode-部品编码. Cfqty-系数.
1. 统计各采购BOM使用系数最大的前3物品,如有使用系统一样,全部显示
使用分析函数实现:
select bomno,itemcode,cfqty
from(select bomno,itemcode,cfqty,groupno,dense_rank() over(partition by bomno
order by cfqty desc) cf from ikdbom)
where cf <= 3
order by bomno,cfqty

常规实现:
select bomno,itemcode,cfqty
from ikdbom t1
where t1.cfqty in (select cfqty
from (select distinct bomno,cfqty from ikdbom
order by bomno,cfqty desc) t2
where t2.bomno = t1.bomno and rownum <= 3)
order by bomno,cfqty desc

2. 统计各采购BOM使用系数最大的3条物品(最多为3条)
使用分析函数实现:
select *
from (select bomno,itemcode,cfqty,count(*) over (partition by bomno order by
cfqty desc range unbounded preceding) cnt
from ikdbom)
where cnt <= 3
order by bomno,cfqty

常规实现:
select bomno,itemcode,cfqty
from ikdbom t1
where (select count(*) from ikdbom t2
where t2.bomno = t1.bomno and t2.cfqty >= t1.cfqty) <= 3

3. 对各采购BOM物品使用系数从大到小进行排序,给出前3行记录
使用分析函数实现:
select *
from (select bomno,itemcode,cfqty,
row_number() over (partition by bomno order by cfqty desc) rn
from ikdbom) where rn <= 3

常规实现:
select bomno,itemcode,cfqty
from ikdbom t1
where (select count(*)
from ikdbom t2
where t2.bomno = t1.bomno and t2.cfqty >= t1.cfqty and (t2.cfqty > t1.cfqty
or t2.rowid > t1.rowid)) < 3
order by bomno,cfqty desc

通过以上几个示例,对应用分析也会有一定的认识,及其使用它给我们的查询带来的好处.

对以上使用的分析函数简单说明:
over(开窗函数)
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over(order by salary) 按照salary排序进行累计，order by是个默认的开窗函数
over(partition by deptno) 按照部门分区
over(order by salary range between 50 preceding and 150 following)
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over(order by salary rows between 50 preceding and 150 following)
每行对应的数据窗口是之前50行，之后150行
over(order by salary rows between unbounded preceding and unbounded following)
每行对应的数据窗口是从第一行到最后一行，等效:
over(order by salary range between unbounded preceding and unbounded following)

dense_rank
根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加.

row_number
返回有序组中一行的偏移量，从而可用于按特定标准排序的行号.

鸿雁在云(2007-3-30)

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/779764/viewspace-912467/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/779764/viewspace-912467/