创新谈-王军

创新性应用： 1.  ORACLE的存储过程的异步调用 利用ORACLE-DBMS_JOB程序包来异步调用存储过程,处理一些长时间运行的操作. 比如:用户每隔半年进行一次盘点(需得出这半年-物品的期初,出入库及结存数量),数据量大,使盘点处理上速度很慢,用户不愿等待,但现况-答案是不能提高速度. 但是可以让用户感觉它运行很快, 当用户点击应用程序中的提交按钮时,不执行盘点处理过程,而提交一个JOB,再由JOB来调用盘点处理过程, 处理完成后把结果写入某一特定表.   2.       采集数据实时提交到数据库(毫秒级). 流水线数据采集. 由于这项工作的本身决定的特性, 经过采集器的采集的数据要实时提交到后台数据库. 而普遍使用标准传输协议及现有数据库并非是高实时性的,比如:Oracle 10g (秒级), 或多或少会有一定延迟, 这样一来会造成数据堵塞-数据掉失. 解决思路: 对传输协议对其定制,与硬件充分相互结合,使用硬件多级储存,分级负荷. 使采集器采集到的数据能及时有效的提交到数据库,实现毫秒级数据库存储.   行业借鉴经验： 暂无   应用难点技巧： ◆ORACLE-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: 日期函数字符串,用来计算作业下次运行的时间.                                                         N   利用DBMS_JOB包将主处理存储过程作为任务提交到任务队列中,让任务队列在执行存储过程。用户操作时，把要提交的任务给后台的任务队列，然后就返回提交到提交页面。为了防止提交过程中存储过程发生异常,也要让用户能看到此异常。在发生异常时，把异常信息写到一数据表中,用户再对表进行查看，以知每次的提交操作状况（也可通过USER_JOB,DBA_JOB,DBA_JOBS_RUNNING来查看任务状况）.   举例说明: 利用dbms_job.summit(,) Pr_TestJob—原目标执行存储过程. Create or Replace procedure pr_TestJob As Begin    For I in 0 .. 1000000    Loop       Insert ATable(ANo) values(I*I*I);    End loop; End;   Fast_TestJob—把Pr_TestJob提交给任务队列. Background_TestJob--执行Pr_TestJob.

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 order by bomno,cfqty desc     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 返回有序组中一行的偏移量，从而可用于按特定标准排序的行号.   **End**

文章请同时提交信箱：bestdba@ciw.com.cn  mulibox@yahoo.com.cn