使用oracle 的 PL/Sql 定时执行一个存储过程
<h1>
<span class="link_title"><a href="/xxj_jing/article/details/6207910">
使用oracle 的 PL/Sql 定时执行一个存储过程
</a></span>
</h1>
<div class="article_manage clearfix">
<div class="article_l">
<span class="link_categories">
标签：
<a href="http://www.csdn.net/tag/%e5%ad%98%e5%82%a8" target="_blank" onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_tag']);">存储</a><a href="http://www.csdn.net/tag/oracle" target="_blank" onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_tag']);">oracle</a><a href="http://www.csdn.net/tag/jobs" target="_blank" onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_tag']);">jobs</a><a href="http://www.csdn.net/tag/user" target="_blank" onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_tag']);">user</a><a href="http://www.csdn.net/tag/%e6%b5%8b%e8%af%95" target="_blank" onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_tag']);">测试</a><a href="http://www.csdn.net/tag/delete" target="_blank" onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_tag']);">delete</a>
</span>
</div>
<div class="article_r">
<span class="link_postdate">2011-02-25 16:17</span>
<span class="link_view" title="阅读次数">9109人阅读</span>
<span class="link_comments" title="评论次数"> <a href="#comments" onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_pinglun'])">评论</a>(4)</span>
<span class="link_collect tracking-ad" data-mod="popu_171"> <a href="javascript:void(0);" onclick="javascript:collectArticle('%e4%bd%bf%e7%94%a8oracle+%e7%9a%84+PL%2fSql+%e5%ae%9a%e6%97%b6%e6%89%a7%e8%a1%8c%e4%b8%80%e4%b8%aa%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b','6207910');return false;" title="收藏">收藏</a></span>
<span class="link_report"> <a href="#report" onclick="javascript:report(6207910,2);return false;" title="举报">举报</a></span>
</div>
</div>
<div class="embody" style="display:none" id="embody">
<span class="embody_t">本文章已收录于：</span>
<div class="embody_c" id="lib" value="{"err":0,"msg":"ok","data":[]}"></div>
</div>
<style type="text/css">
.embody{
padding:10px 10px 10px;
margin:0 -20px;
border-bottom:solid 1px #ededed;
}
.embody_b{
margin:0 ;
padding:10px 0;
}
.embody .embody_t,.embody .embody_c{
display: inline-block;
margin-right:10px;
}
.embody_t{
font-size: 12px;
color:#999;
}
.embody_c{
font-size: 12px;
}
.embody_c img,.embody_c em{
display: inline-block;
vertical-align: middle;
}
.embody_c img{
width:30px;
height:30px;
}
.embody_c em{
margin: 0 20px 0 10px;
color:#333;
font-style: normal;
}
</style>
<script type="text/javascript">
$(function () {
try
{
var lib = eval("("+$("#lib").attr("value")+")");
var html = "";
if (lib.err == 0) {
$.each(lib.data, function (i) {
var obj = lib.data[i];
//html += '<img src="' + obj.logo + '"/>' + obj.name + " ";
html += ' <a href="' + obj.url + '" target="_blank">';
html += ' <img src="' + obj.logo + '">';
html += ' <em><b>' + obj.name + '</b></em>';
html += ' </a>';
});
if (html != "") {
setTimeout(function () {
$("#lib").html(html);
$("#embody").show();
}, 100);
}
}
} catch (err)
{ }
});
</script>
<div class="category clearfix">
<div class="category_l">
<img src="http://static.blog.csdn.net/images/category_icon.jpg">
<span>分类：</span>
</div>
<div class="category_r">
<label onclick="GetCategoryArticles('481450','xxj_jing','top','6207910');">
<span onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_fenlei']);">数据库资料<em>（5）</em></span>
<img class="arrow-down" src="http://static.blog.csdn.net/images/arrow_triangle _down.jpg" style="display:inline;">
<img class="arrow-up" src="http://static.blog.csdn.net/images/arrow_triangle_up.jpg" style="display:none;">
<div class="subItem">
<div class="subItem_t"><a href="http://blog.csdn.net/xxj_jing/article/category/481450" target="_blank">作者同类文章</a><i class="J_close">X</i></div>
<ul class="subItem_l" id="top_481450">
</ul>
</div>
</label>
<label onclick="GetCategoryArticles('481448','xxj_jing','top','6207910');">
<span onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_fenlei']);">技术资料<em>（56）</em></span>
<img class="arrow-down" src="http://static.blog.csdn.net/images/arrow_triangle _down.jpg" style="display:inline;">
<img class="arrow-up" src="http://static.blog.csdn.net/images/arrow_triangle_up.jpg" style="display:none;">
<div class="subItem">
<div class="subItem_t"><a href="http://blog.csdn.net/xxj_jing/article/category/481448" target="_blank">作者同类文章</a><i class="J_close">X</i></div>
<ul class="subItem_l" id="top_481448">
</ul>
</div>
</label>
</div>
</div>
<script type="text/javascript" src="http://static.blog.csdn.net/scripts/category.js"></script>
<div class="bog_copyright">
<p class="copyright_p">版权声明：本文为博主原创文章，未经博主允许不得转载。</p>
</div>
/*
使用Oracle 的 PL/Sql 定时执行一个存储过程
测试目的：在表 tab 中间隔一分钟插入一条数据
实现方案：
1.通过 oracle 中 dbms_job 完成存储过程的定时调用
2.在存储过程中完成相应的逻辑操作
脚本说名：
以下代码为 dbms_job 操作的基本写法，
其中不包含各个系统函数参数说明，
如需要了解“系统函数参数说明”请另行在网上查找
注：
1.其中的停止运行后继续执行的方法还有待测试
2.开始运行一个计划时，计划内的存储过程将立刻执行一次
*/
--1.创建一个测试表
create table tab(dtime date);
Select * From tab;
--2.创建一个存储过程
create or replace procedure p_test as
begin
insert into tab values(sysdate);
end;
--3.创建一个执行计划：每天1440分钟，即一分钟运行存储过程一次
Declare
i Integer;
Begin
dbms_job.submit(i,'p_test;',Sysdate,'sysdate+1/1440');
end;
--4.查看已经创建的所有执行计划
Select * From user_jobs;
--5.运行执行计划
Declare
job_num Integer;
Begin
-- 查找计划号
Select t.JOB Into job_num From User_Jobs t ;
-- 运行制定的执行计划
dbms_job.run(job_num);
end;
--6.查看计划的运行结果
select
t.dtime
--to_char(t.dtime,'yyyy-mm-dd hh24:mi:ss')
from tab t
Order By t.dtime;
-- 查出测试表内容
Delete tab t;
--7.修改执行计划（修改执行的间隔时间）
/*
sysdate+1 表示每天执行一次
sysdate+1/24 表示每小时执行一次
sysdate+1/(24*60) 表示每分钟执行一次
sysdate+1/(24*60*60) 表示每秒执行一次
*/
Declare
job_num Integer;
Begin
-- 查找计划号
Select t.JOB Into job_num From User_Jobs t ;
-- 修改为：每天执行一次
dbms_job.interval(job_num, 'sysdate+1/(24*60)');
end;
--8.停止一个执行计划
/*
Sysdate+(5) 加五天,
Sysdate+(5/24) 加五时,
Sysdate+(5/24/60) 加五分,
Sysdate+(5/24/60/60) 加五秒
*/
Declare
job_num Integer;
Begin
-- 查找计划号
Select t.JOB Into job_num From User_Jobs t ;
-- 停止计划，不在执行
--dbms_job.broken(job_num,True);
-- 停止计划，并在两分钟后继续执行
dbms_job.broken(job_num,True,Sysdate+(2/24/60));
end;
--9.删除执行计划Declare
Declare
job_num Integer;
Begin
-- 查找计划号
Select t.JOB Into job_num From User_Jobs t ;
dbms_job.remove(job_num);
end;
<div id="digg" articleid="6207910">
<dl id="btnDigg" class="digg digg_disable" onclick="btndigga();">
<dt>顶</dt>
<dd>1</dd>
</dl>
<dl id="btnBury" class="digg digg_disable" onclick="btnburya();">
<dt>踩</dt>
<dd>0</dd>
</dl>
</div>
<div class="tracking-ad" data-mod="popu_222"><a href="javascript:void(0);"> </a> </div>
<div class="tracking-ad" data-mod="popu_223"> <a href="javascript:void(0);"> </a></div>
<script type="text/javascript">
function btndigga() {
$(".tracking-ad[data-mod='popu_222'] a").click();
}
function btnburya() {
$(".tracking-ad[data-mod='popu_223'] a").click();
}
</script>
<div style="clear:both; height:10px;"></div>
<div class="similar_article" style="">
<h4>我的同类文章</h4>
<div class="similar_c" style="margin:20px 0px 0px 0px">
<div class="similar_c_t">
<label class="similar_cur">
<span style="cursor:pointer" onclick="GetCategoryArticles('481450','xxj_jing','foot','6207910');">数据库资料<em>（5）</em></span>
</label>
<label class="">
<span style="cursor:pointer" onclick="GetCategoryArticles('481448','xxj_jing','foot','6207910');">技术资料<em>（56）</em></span>
</label>
</div>
<div class="similar_wrap tracking-ad" data-mod="popu_141" style="max-height:195px;">
<a href="http://blog.csdn.net" style="display:none">http://blog.csdn.net</a>
<ul class="similar_list fl"><li><em>•</em><a href="http://blog.csdn.net/xxj_jing/article/details/51729023" id="foot_aritcle_51729023undefined24620912596583366" target="_blank" title="LiteDB v.2.0.0-rc 使用介绍">LiteDB v.2.0.0-rc 使用介绍</a><span>2016-06-21</span><label><i>阅读</i><b>443</b></label></li> <li><em>•</em><a href="http://blog.csdn.net/xxj_jing/article/details/7353827" id="foot_aritcle_7353827undefined9441469646990299" target="_blank" title="解决 用户'sa'登录失败。错误:18456 问题">解决 用户'sa'登录失败。错误:18456 问题</a><span>2012-03-14</span><label><i>阅读</i><b>891</b></label></li> <li><em>•</em><a href="http://blog.csdn.net/xxj_jing/article/details/6414240" id="foot_aritcle_6414240undefined5528015608433634" target="_blank" title="EntityFramework 4.0 中添加对存储过程的调用">EntityFramework 4.0 中添加对存储过程的调用</a><span>2011-05-12</span><label><i>阅读</i><b>2426</b></label></li> </ul>
<ul class="similar_list fr"><li><em>•</em><a href="http://blog.csdn.net/xxj_jing/article/details/28611215" id="foot_aritcle_28611215undefined016637336928397417" target="_blank" title="微软企业库5.0 支持 MySql">微软企业库5.0 支持 MySql</a><span>2014-06-05</span><label><i>阅读</i><b>2721</b></label></li> <li><em>•</em><a href="http://blog.csdn.net/xxj_jing/article/details/6443012" id="foot_aritcle_6443012undefined8050204403698444" target="_blank" title="Vs2010 打包项目使用（打包）">Vs2010 打包项目使用（打包）</a><span>2011-05-24</span><label><i>阅读</i><b>3912</b></label></li> </ul>
</div>
</div>
</div>
<script type="text/javascript">
$(function () {
GetCategoryArticles('481450', 'xxj_jing','foot','6207910');
});
</script>
<div>
<ins data-revive-zoneid="205" data-revive-id="8c38e720de1c90a6f6ff52f3f89c4d57" id="revive-0-1"></ins>
</div>
<dt><span>猜你在找</span></dt>
<div id="adCollege" style="width: 42%;float: left;">
<script src="http://csdnimg.cn/jobreco/job_reco.js" type="text/javascript"></script>
<script type="text/javascript">
csdn.position.showEdu({
sourceType: "blog",
searchType: "detail",
searchKey: "6207910",
username: "",
recordcount: "5",
containerId: "adCollege" //容器DIV的id。
});
setEduLoc();
function setEduLoc() {
var edus = $("#adCollege div dd a");
if (edus.length == 0) {
setTimeout(function () {
setEduLoc();
}, 500);
}
else {
var eduLoc = "?ref=blog&loc=0";
$.each(edus, function (index,item) {
var href = $(this).attr("href") + eduLoc;
$(this).attr("href", href);
});
}
}
</script>
<div class="tracking-ad" data-mod="popu_84"><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px; white-space: nowrap;"><a href="http://edu.csdn.net/course/detail/2513?ref=blog&loc=0" title="顾荣：开源大数据存储系统Alluxio（原Tachyon）的原理分析与案例简介" strategy="v4:content" target="_blank">顾荣：开源大数据存储系统Alluxio（原Tachyon）的原理分析与案例简介</a></dd><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px; white-space: nowrap;"><a href="http://edu.csdn.net/course/detail/1487?ref=blog&loc=0" title="Ceph—分布式存储系统的另一个选择" strategy="v4:content" target="_blank">Ceph—分布式存储系统的另一个选择</a></dd><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px; white-space: nowrap;"><a href="http://edu.csdn.net/course/detail/3004?ref=blog&loc=0" title="Android之数据存储" strategy="v4:content" target="_blank">Android之数据存储</a></dd><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px; white-space: nowrap;"><a href="http://edu.csdn.net/course/detail/3676?ref=blog&loc=0" title="Android核心技术——Android数据存储" strategy="v4:content" target="_blank">Android核心技术——Android数据存储</a></dd><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px; white-space: nowrap;"><a href="http://edu.csdn.net/course/detail/4299?ref=blog&loc=0" title="Android中的数据存储" strategy="v4:content" target="_blank">Android中的数据存储</a></dd></div></div>
<div id="res" data-mod="popu_36" class="tracking-ad" style="width: 42%; float: left; margin-right: 30px; display: block;"><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px;"><a href="http://blog.csdn.net/laoyang5219/article/details/1716306" title="Oracle PLSQL 存储过程 入门之案例实践" strategy="SearchAlgorithm">Oracle PLSQL 存储过程 入门之案例实践</a></dd><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px;"><a href="http://blog.csdn.net/gzhan1603/article/details/49302837" title="Oracle plsql编程 25--调用返回结果集的存储过程" strategy="SearchAlgorithm">Oracle plsql编程 25--调用返回结果集的存储过程</a></dd><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px;"><a href="http://blog.csdn.net/viscent_huang/article/details/49904495" title="置顶 Oracle PLSQL中编写空操作的存储过程" strategy="SearchAlgorithm">置顶 Oracle PLSQL中编写空操作的存储过程</a></dd><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px;"><a href="http://blog.csdn.net/caomiao2006/article/details/45798595" title="PLSQL如何调试Oracle存储过程" strategy="SearchAlgorithm">PLSQL如何调试Oracle存储过程</a></dd><dd style="background:url(http://static.blog.csdn.net/skin/default/images/blog-dot-red3.gif) no-repeat 0 10px;"><a href="http://blog.csdn.net/u011553653/article/details/51822631" title="PLSQL如何调试Oracle存储过程" strategy="SearchAlgorithm">PLSQL如何调试Oracle存储过程</a></dd></div>
<div id="ad_cen">
<!-- 广告位开始 -->
<ins data-revive-zoneid="72" data-revive-id="8c38e720de1c90a6f6ff52f3f89c4d57" id="revive-0-3"></ins>
<!-- 广告位结束 -->
<div id="ad_bot">
</div>
<a id="quick-reply" class="btn btn-top q-reply" title="快速回复" style="display:none;">
<img src="http://static.blog.csdn.net/images/blog-icon-reply.png" alt="快速回复">
</a>
<a id="d-top-a" class="btn btn-top backtop" style="display: none;" title="返回顶部" onclick="_gaq.push(['_trackEvent','function', 'onclick', 'blog_articles_huidaodingbu'])">
<img src="http://static.blog.csdn.net/images/top.png" alt="TOP">
</a>
<h5>
<a href="http://www.csdn.net/tag/" target="_blank">核心技术类目</a></h5>
<div class="classify">
全部主题
Hadoop
AWS
移动游戏
Java
Android
iOS
Swift
智能硬件
Docker
OpenStack
VPN
Spark
ERP
IE10
Eclipse
CRM
JavaScript
数据库
Ubuntu
NFC
WAP
jQuery
BI
HTML5
Spring
Apache
.NET
API
HTML
SDK
IIS
Fedora
XML
LBS
Unity
Splashtop
UML
components
Windows Mobile
Rails
QEMU
KDE
Cassandra
CloudStack
FTC
coremail
OPhone
CouchBase
云计算
iOS6
Rackspace
Web App
SpringSide
Maemo
Compuware
大数据
aptech
Perl
Tornado
Ruby
Hibernate
ThinkPHP
HBase
Pure
Solr
Angular
Cloud Foundry
Redis
Scala
Django
Bootstrap
<div class="clear">
</div>
</div>
</div>
DROP TABLE tab;