Oracle 10g实现存储过程异步调用
Oracle 10g实现存储过程异步调用,将要运行的SQL文本,必须是有效的PL/SQL语句或一段代码. 例如: 运行存储过程P,可以传递字符串P
DBMS_JOB是什么?
DBMS_JOB是Oracle数据库提供的专家程序包的一个.
主要用来在后台运行程序,是数据库中一个极好的工具. 可用于自动调整调度例程任务,例如分析数据表,执行一些归档操作,清理草稿表等等.
使用语法说明.使用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操作.
示例设原有存储过程为:pr_movedetail(业务存储过程), 执行大数据量操作, 操作耗时.
利用DBMS_Job包功能, 将存储过程的执行集中提交给Oracle Job来处理, 客户端无需等待.
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;
//调用实际使用的Pr_movedetail例程, 然后更新记录,记录实际提交时间.
End;
最后在客户端直接调用fast_movedetail即可.

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]
