这是本人第一次处理Oracle的存储过程以及计划任务（job）的事情；期间遇到了很多问题在此将这次试用job调用自己的编写的存储过程的过程中遇到的问题做一总结；
任务：实时采集系统会每5分钟给中心系统传送一个流量数据，但是没有给出累计流量数据；
1、编写一个存储过程WATERQCAL用于计算流量增量以及得出最后的累计流量；
2、用一个任务计划每隔5分钟，统一进行执行一次WATERQCAL存储过程；
创建存储过程代码如下：
CREATE OR REPLACE
PROCEDURE WaterQCal AS--如果用引号将存储过程名引起来后就会保存成WaterQCal存储过程；如果不引起来的话最终保存的是WATERQCAL；
oldACC_W NUMBER;
oldACC_W_PQ NUMBER;
newACC_W NUMBER;
BEGIN
DECLARE
--查询出一个集合
CURSOR mpcd_number IS
SELECT DISTINCT MP_CD from WR_MP_Q_R WHERE ACC_W is NULL;
--定义行游标变量
rcd_number mpcd_number % ROWTYPE;
--循环开始
BEGIN
--开启游标
open mpcd_number;
loop
--开始游标循环操作
fetch mpcd_number
into rcd_number;
exit when mpcd_number%notfound;
--开始查询某一个个测站数据
DECLARE
--查询出一个某测站的所有未计算浏览的集合
CURSOR sinSTB IS
select * from WR_MP_Q_R WHERE ACC_W is null and MP_CD=rcd_number.MP_CD order by TM ASC;
--定义行游标变量
sinSt sinSTB % ROWTYPE;
BEGIN
--开启单站游标
open sinSTB;
loop
fetch sinSTB
into sinSt;
exit when sinSTB%notfound;
BEGIN
--开始计算流量
--查询出最有一条计算过流量的记录
BEGIN
select ACC_W into oldACC_W from WR_MP_Q_R WHERE ACC_W is not null and MP_CD=sinSt.MP_CD and rownum=1 order by TM DESC;
exception--处理异常没有数据时；该如何处理；因为有异常处理此处的select语句以及异常都必须单独用begin end括起来；
when no_data_found then
oldACC_W:=0;
END;
--计算流量增量
oldACC_W_PQ:=ABS(sinSt.MP_Q)/12;
--计算当前累计流量
newACC_W:=ABS(oldACC_W)+ABS(oldACC_W_PQ);
--更新当前累计流量
update WR_MP_Q_R set ACC_W=newACC_W where MP_CD=sinSt.MP_CD and TM=sinSt.TM;
END;
END LOOP;
close sinSTB;
--关闭游标
END;
END loop;
close mpcd_number;
END;
END;
创建Oracle计划任务的代码如下：
declare jobNum NUMBER;
begin
sys.dbms_job.submit(job => jobNum,
what => 'SWATER.WaterQCal;',
next_date => trunc(sysdate)+1+8/1440,
interval => 'trunc(sysdate,''mi'')+1+8/1440');
commit;
end;
/
通过plsql工具创建：
在编制存储过程时应该注意的是
1、每一个语句结束时都必须采用；结束。否则会报sql语句不完整；
2、CREATE OR REPLACE PROCEDURE PROCEDURE_NAME AS --该语句部分PROCEDURE_NAME不能够用引号引起来；如果引号引起来的话，名字中的小写字母之类的还会原样保留；但是Oracle默认是需要将存储过程的名字转换成大写的；并且后面创建Job是如果输入之前带小写字母的名字时又会转换成大写的时候就会报找不到存储过程；
3、for循环来操作游标，不用open 游标;以及 close游标 操作否则会报错；
4、针对每一个包含异常处理语句的sql都必须防止在begin以及end之中；否则可能会出现部分语句不执行，直接跳转到下一个end处；
5、循环处理
1）、For 循环
For ... in ... LOOP
--执行语句
end LOOP;
(1)循环遍历游标
create or replace procedure test() as
Cursor cursor is select name from student; name varchar(20);
begin
for name in cursor LOOP
begin
dbms_output.putline(name);
end;
end LOOP;
end test;
(2)循环遍历数组
create or replace procedure test(varArray in myPackage.TestArray) as
--(输入参数varArray 是自定义的数组类型，定义方式见标题6)
i number;
begin
i := 1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张
--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历
for i in 1..varArray.count LOOP
dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));
end LOOP;
end test;
2)、While 循环
while 条件语句 LOOP
begin
end;
end LOOP;
E.g
create or replace procedure test(i in number) as
begin
while i < 10 LOOP
begin
i:= i + 1;
end;
end LOOP;
end test;
3).游标的使用
Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：
(1)Cursor型游标(不能用于参数传递)
create or replace procedure test() is
cusor_1 Cursor is select std_name from student where ...; --Cursor的使用方式1 cursor_2 Cursor;
begin
select class_name into cursor_2 from class where ...; --Cursor的使用方式2
可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor的遍历
end test;
(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR; name varhcar(20);
begin
OPEN cursor FOR select name from student where ... --SYS_REFCURSOR只能通过OPEN方法来打开和赋值
LOOP
fetch cursor into name --SYS_REFCURSOR只能通过fetch into来打开和遍历 exit when cursor%NOTFOUND; --SYS_REFCURSOR中可使用三个状态属性： ---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息) ---%ROWCOUNT(然后当前游标所指向的行位置)
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;
编辑JOB过程中该注意事项
1、what => 'SWATER.WaterQCal;'的WaterQCal的存储过程名必须以;结尾；否则会报如下错误：
[Err] ORA-06550: 第 1 行, 第 110 列:
PLS-00103: 出现符号 "END"在需要下列之一时：
:= . ( @ % ;
符号 ";" 被替换为 "END" 后继续。
ORA-06512: 在 "SYS.DBMS_JOB", line 82
ORA-06512: 在 "SYS.DBMS_JOB", line 140
ORA-06512: 在 line 3
2、如果前面创建存储过程中将存储过程名用引号引起来后，因为存储郭晨被保存成了WaterQCal；而what中指定的存储过程SWATER.WaterQCal会被转换SWATER.WATERQCAL；所以会报如下错误：
[Err] ORA-06550: 第 1 行, 第 93 列:
PLS-00201: 必须声明标识符 'SWATER.WATERQCAL'
ORA-06550: 第 1 行, 第 93 列:
PL/SQL: Statement ignored
ORA-06512: 在 "SYS.DBMS_JOB", line 82
ORA-06512: 在 "SYS.DBMS_JOB", line 140
ORA-06512: 在 line 3
3、时间间隔设置必须是在指定的时间必须是当前时间的后面的值（即如果由interval计算出来的next time如果在当前时间之前，会怎么处理？就没法处理，会报错），否则会报如下错误
ORA-23420:间隔必须以将来的一个时间作评估
ORA-06512:在 "SYS.DBMS_JOB",line 57
ORA-06512:在 "SYS.DBMS_JOB",line 134
ORA-06512:在 line 3
4、如果 sys.dbms_job.submit(job => :job,用的是:job作为参数，并且是通过查询语句执行的话会报如下错误：
[Err] ORA-01008: 并非所有变量都已绑定
解决办法如本示例：declare job NUMBER;--定义变量；并采用sys.dbms_job.submit(job => job方式去掉冒号；
5、参数说明
1)、job
参数job是一个整数，用来唯一地标示一个任务。该参数既可由用户指定也可由系统自动赋予，这完全取决于提交任务时选用了那一个任务提交过程。DBMS_JOB.SUBMIT过程通过获得序列SYS.JOBSEQ的下一个值来自动赋予一个任务号。该任务号是作为一个OUT参数返回的，所以调用者随后可以识别出提交的任务。而DBMS_JOB.ISUBMIT过程则由调用者给任务指定一个识别号，这时候，任务号的唯一性就完全取决于调用者了。
除了删除或者重新提交任务，一般来说任务号是不能改变的。即使当数据库被导出或者被导入这样极端的情况，任务号也将被保留下来。所以在执行含有任务的数据的导入/导出操作时很可能会发生任务号冲突的现象。
2)、what
what参数是一个可以转化为合法PL/SQL调用的字符串，该调用将被任务队列自动执行。在what参数中，如果使用文字字符串，则该字符串必须用单引号括起来。 what参数也可以使用包含我们所需要字符串值的VARCHAR2变量。实际的PL/SQL调用必须用分号隔开。在PL/SQL调用中如果要嵌入文字字符串，则必须使用两个单引号。
what参数的长度在Oracle7.3中限制在2000个字节以内，在Oracle 8.0以后，扩大到了4000个字节，这对于一般的应用已完全足够。该参数的值一般情况下都是对一个PL/SQL存储过程的调用。在实际应用中，尽管可以使用大匿名Pl/SQL块，但建议大家最好不要这样使用。还有一个实际经验就是最好将存储过程调用封装在一个匿名块中，这样可以避免一些比较莫名错误的产生。我来举一个例子，一般情况下，what参数可以这样引用：
what =>’my_procedure(parameter1);’
但是比较安全的引用，应该这样写：
what =>’begin my_procedure(parameter1); end;’
任何时候，我们只要通过更改what参数就可以达到更改任务定义的目的。但是有一点需要注意，通过改变what参数来改变任务定义时，用户当前的会话设置也被记录下来并成为任务运行环境的一部分。如果当前会话设置和最初提交任务时的会话设置不同，就有可能改变任务的运行行为。意识到这个潜在的副作用是非常重要的，无论何时只要应用到任何DBMS_JOB过程中的what参数时就一定要确保会话设置的正确。
3)、next_date
Next_date参数是用来调度任务队列中该任务下一次运行的时间。这个参数对于DBMS_JOB.SUBMIT和DBMS_JOB.BROKEN这两个过程确省为系统当前时间，也就是说任务将立即运行。
当将一个任务的next_date参数赋值为null时，则该任务下一次运行的时间将被指定为4000年1月1日，也就是说该任务将永远不再运行。在大多数情况下，这可能是我们不愿意看到的情形。但是，换一个角度来考虑，如果想在任务队列中保留该任务而又不想让其运行，将next_date设置为null却是一个非常简单的办法。
Next_date也可以设置为过去的一个时间。这里要注意，系统任务的执行顺序是根据它们下一次的执行时间来确定的，于是将next_date参数设置回去就可以达到将该任务排在任务队列前面的目的。这在任务队列进程不能跟上将要执行的任务并且一个特定的任务需要尽快执行时是非常有用的。
4)、Interval
Internal参数是一个表示Oracle合法日期表达式的字符串。这个日期字符串的值在每次任务被执行时算出，算出的日期表达式有两种可能，要么是未来的一个时间要么就是null。这里要强调一点：很多开发者都没有意识到next_date是在一个任务开始时算出的，而不是在任务成功完成时算出的。
当任务成功完成时，系统通过更新任务队列目录表将前面算出的next_date值置为下一次任务要运行的时间。当由interval表达式算出next_date是null时，任务自动从任务队列中移出，不会再继续执行。因此，如果传递一个null值给interval参数，则该任务仅仅执行一次。
通过给interval参数赋各种不同的值，可以设计出复杂运行时间计划的任务。附录的“任务间隔和日期算法”将对interval表达式进行详细讨论，并给出一个实际有用interval表达式的例子。
6、时间间隔指定函数：TRUNC函数返回以指定元素格式截去一部分的日期值；语法格式TRUNC（date,[fmt]）date是时间；fmt格式化字符串；默认截取出日期部分值。
1）:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)
2）:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/ (24)
3）:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
4）:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5）:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
6）:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
7）:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24
示例时间间隔：
trunc(sysdate) + 1/1440为当天的 0时01分.而当前时间往往大于这个当天值.所以你需要设置为interval => 'trunc(sysdate)+1+1/1440');意思是,明天的凌晨 0时01分.
这样,在每次oracle执行完当前job后,就可以将next_date设置为明天,而不是今天0时01分,因为今天的这个时间已经过去了;
interval => 'trunc(sysdate)+1/1440');就是今天的0时01分；你只有在0:00的时间执行才可以通过；否则就报错误了。
7、通过plsql工具创建计划任务时，fmt参数必须是用两个单引号括起来，而不能用双引号；
TRUNC(sysdate,''mi'') +1+8/1440
额外补充：
创建job语句如下：
variable jobno number;
begin
sys.dbms_job.submit(job => :jobno,
what => 'change_date;',
next_date => to_date('18-11-2013', 'dd-mm-yyyy'),
interval => 'sysdate+1/1440');--每天1440分钟，即一分钟运行change_date过程一次
commit; --必须有commit，如果没有是查不到该job的！！！
end;
/
这两种创建job的区别仅仅是jobno的定义方式不同：
第一种是declare出的变量
第二种是variable出的变量
在网上一搜发现两者作用域不同：
variable相当于一个sql*plus环境的全局变量,declare里定义的是pl/sql中的局部变量。
扫一扫
2316
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
