oracle序列异常,RAISE_APPLICATION_ERROR--oralce 异常处理篇 监控的存储过程 sequence

平时用来测试的异常处理

我们都是通过dbms_output.put_line来输出异常信息，但是在实际的应用中，需要把异常信息返回给调用的客户端。

其实 RAISE_APPLICATION_ERROR

是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言)

raise_application_error(异常类型,传递信息)

异常类型：number() 值域：-20000 到-20999

传递信息：varchar2(2000)

DBMS_STANDARD包的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。语法如下

RAISE_APPLICATION_ERROR(errorNumber,errorString)

errorNumber是数值在-20000到-20999之间，errorString为自定义的错误信息。

如：

update jobs set job_title = v_newJobTitle where job_id =

v_jobid;

if sql%notfound then

RAISE_APPLICATION_ERROR(-20167,'update failure!');

end if;

..........

当在sqlpus中测试时，一旦没有要更新的行，则抛出这样的异常：

ORA-20167: update failure!

oracle的异常分为编译时异常(错误)和运行时异常，前者不能被处理，后者可以被处理。

我们主要讨论运行时异常。

异常类型：

a、预定义异常

已命名的预定义异常有CURSOR_ALREADY_OPEN、INVALID_NUMBER、TOO_MANY_ROWS等

b、用户定义异常

c、已命名异常(已命名异常不能单独归为一类异常，但它有点特别，所以我将它单独罗列说明)

如果希望处理一个异常(被when子串处理)，那么异常必须有一个名字，如TOO_MANY_ROWS；

数据库错误有数千个，但是只有不到25个是内建的已命名异常(这些异常都声明在standard包中)；

要处理那些未命名的异常时，你可以将一个名字和一个错误号联系在一起，达到这个目的的语句是pragma

exception_init语句；

抛出异常：

a、通过pl/sql运行时引擎

当数据库或pl/sql在运行时发生错误时，一个异常被pl/sql运行时引擎自动抛出

b、使用raise语句

异常也可以通过raise语句抛出：raise exception_name;

c、调用raise_application_error存储过程

处理异常&异常传播：

a、一旦程序进入异常部分就不能再回到同一块的执行部分；当异常被处理后，控制返回到外层执行部分的下一条语句；

b、如果有when others子串，则必须放置在最后面作为缺省处理器处理没有显式处理的异常；

c、执行部分抛出的异常将首先传递到同一块的异常部分，如果在同一块的异常部分没有处理这个异常的处理器，那么异常将会传播到上一层的异常部分中，一直到最外层；

d、异常被处理后如果你仍然希望它继续传播，可以在异常处理的最后执行不带参数的raise语句(raise语句将重新抛出出现的异常，允许它继续传播)；

--这是一个上面部分知识点的示例(伪代码)说明

declare

...

user_define_exception exception; --用户定义异常

invalid_column_name exception;

--补充说明：如果我们在程序块中使用了无效列名，会有括号中的错误提示(ORA-00904:invalid column

name)

--下面我们将这个异常代码号与我们自定义的异常进行关联，即为异常命名

pragma exception_init(invalid_column_name,-904);

begin

...

--raise user_define_exception; --可以显式引发异常

exception

when TOO_MANY_ROWS then --预定义异常处理

...;

when user_define_exception then --用户定义异常处理

...;

when invalid_column_name then --PRAGMA EXCEPTION_INIT异常处理

...;

raise; --继续传播该异常

end;

sqlcode和sqlerrm：

a、另外一种处理数据库错误的方法是使用内建函数sqlcode和sqlerrm；

b、sqlcode将返回现行数据库错误号，这些错误号中除了no_data_found是+100外其他都是负数；

c、sqlerrm返回文本描述的错误信息；

d、为了获得用户自定义异常返回的sqlerrm和sqlcode，你需要使用raise_application_error函数给自定义异常标注错误号

给自定义错误标注号码：

a、raise_application_error内建函数用于抛出一个异常并给异常赋予一个错误号以及错误信息；

b、自定义异常的缺省错误号是+1,缺省信息是user_defined_exception。来自未处理的异常的一般信息对于识别导致错误的原因没有帮助，

c、raise_application_error函数能够在pl/sql程序块的执行部分和异常部分调用，显式抛出带特殊错误号的命名异常；

d、使用语法：raise_application_error(error_no,error_message[,{true||

false}]);

e、错误号的范围是-20,001到-20,999；错误信息是文本字符串，最多为2048字节；true和false表示是添加(true)进错误堆(error

stack)还是覆盖(overwrite)错误堆(false)。缺省情况下是false。

ORACLE

DATE和TIMESTAMP数据类型的比较

DATE数据类型

这个数据类型我们实在是太熟悉了，当我们需要表示日期和时间的话都会想到date类型。它可以存储月，年，日，世纪，时，分和秒。它典型地用来表示什么时候事情已经发生或将要发生。DATE数据类型的问题在于它表示两个事件发生时间间隔的度量粒度是秒。这个问题将在文章稍后讨论timestamp的时候被解决。可以使用TO_CHAR函数把DATE数据进行传统地包装，达到表示成多种格式的目的。

SQL> SELECT

TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM

date_table;

Date

---------------------------

06/20/2003 16:55:14

06/26/2003 11:16:36

我见到的大多数人陷入的麻烦就是计算两个时间间的间隔年数、月数、天数、小时数和秒数。你需要明白的是，当你进行两个日期的相减运算的时候，得到的是天数。你需要乘上每天的秒数(1天=86400秒)，然后，你可以再次计算得到你想要的间隔数。下面就是我的解决方法，可以精确计算出两个时间的间隔。我明白这个例子可以更简短些，但是我是为了显示所有的数字来强调计算方式。

1　　　　 SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,

2　　　　 TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,

3　　　　 trunc(86400*(date2-date1))-

4　　　　 60*(trunc((86400*(date2-date1))/60)) seconds,

5　　　　 trunc((86400*(date2-date1))/60)-

6　　　　 60*(trunc(((86400*(date2-date1))/60)/60)) minutes,

7　　　　 trunc(((86400*(date2-date1))/60)/60)-

8　　　　 24*(trunc((((86400*(date2-date1))/60)/60)/24))

hours,

9　　　　 trunc((((86400*(date2-date1))/60)/60)/24) days,

10　　　　 trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks

11*　　　　FROM date_table

DATE1

DATE2　　　　　　　　SECONDS　　MINUTES　　　HOURS　　　DAYS　　　WEEKS

----------------- ----------------- ---------- ----------

---------- ---------- ----------

06202003:16:55:14 07082003:11:22:57　　　　 43　　　　 27

18　　　　17　　　　　2

06262003:11:16:36 07082003:11:22:57

21　　　　　6　　　　　0　　　　12　　　　　1

TIMESTAMP 数据类型

DATE数据类型的主要问题是它粒度不能足够区别出两个事件哪个先发生。ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型，它包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息。如果你想把DATE类型转换成TIMESTAMP类型，就使用CAST函数。

SQL> SELECT CAST(date1 AS

TIMESTAMP) "Date" FROM t;

Date

-----------------------------------------------------

20-JUN-03 04.55.14.000000 PM

26-JUN-03 11.16.36.000000 AM

正如你看到的，在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候，没有小数秒的信息，缺省为0。而且显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候，可以直接写INSERT

SELECT语句，oracle会自动为你做转换的。

1　SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM

date_table

Date

-------------------

06/20/2003 16:55:14

06/26/2003 11:16:36

TIMESTAMP数据的格式化显示和DATE

数据一样。注意，to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下，使用TIMESTAMP数据类型要比DATE数据类型更确切。

如果你想显示TIMESTAMP的小数秒信息，参考下面：

1　SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM

date_table

Date

-----------------------

06/20/2003 16:55:14:000

06/26/2003 11:16:36:000

在上例中，我只现实了小数点后3位的内容。

计算timestamp间的数据差别要比老的date数据类型更容易。当你直接相减的话，看看会发生什么。结果将更容易理解，第一行的17天，18小时，27分钟和43秒。

1　SELECT time1,

2　　　　 time2,

3　　　　 substr((time2-time1),instr((time2-time1),' ')+7,2)

seconds,

4　　　　 substr((time2-time1),instr((time2-time1),' ')+4,2)

minutes,

5　　　　 substr((time2-time1),instr((time2-time1),' ')+1,2)

hours,

6　　　　 trunc(to_number(substr((time2-time1),1,instr(time2-time1,'

'))))　 days,

7　　　　 trunc(to_number(substr((time2-time1),1,instr(time2-time1,'

')))/7) weeks

8*　 FROM date_table

TIME1　　　　　　　　　　　 TIME2　　　　　　　　　　　SECONDS MINUTES HOURS DAYS

WEEKS

-------------------------　 -------------------------- -------

------- ----- ---- -----

06/20/2003:16:55:14:000000　07/08/2003:11:22:57:000000　43

27　　　18　　17　　2

06/26/2003:11:16:36:000000　07/08/2003:11:22:57:000000　21

06　　　00　　12　　1

这就意味着不再需要关心一天有多少秒在麻烦的计算中。因此，得到天数、月数、天数、时数、分钟数和秒数就成为用substr函数摘取出数字的事情了。

系统日期和时间

为了得到系统时间，返回成date数据类型。你可以使用sysdate函数。

SQL> SELECT SYSDATE FROM DUAL;

为了得到系统时间，返回成timestamp数据类型。你可以使用systimpstamp函数。

SQL> SELECT SYSTIMESTAMP FROM DUAL;

你可以设置初始化参数FIXED_DATE指定sysdate函数返回一个固定值。这用在测试日期和时间敏感的代码。注意，这个参数对于systimestamp函数无效。

SQL> ALTER SYSTEM SET fixed_date =

'2003-01-01-10:00:00';

System altered.

SQL> select sysdate from dual;

SYSDATE

---------

01-JAN-03

SQL> select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------

09-JUL-03 11.05.02.519000 AM -06:00

当使用date和timestamp类型的时候，选择是很清楚的。你可以随意处置date和timestamp类型。当你试图转换到更强大的timestamp的时候，需要注意，它们既有类似的地方，更有不同的地方，而足以造成破坏。两者在简洁和间隔尺寸方面各有优势，请合理地选择。

2011.11.3

Oracle提供了sequence对象,由系统提供自增长的序列号,通常用于生成数据库数据记录的自增长主键或序号的地方.

下面介绍一下关于sequence 的生成,修改,删除等常用的操作:

1. 创建 Sequence 使用如下命令新建sequence(用户需要有CREATE

SEQUENCE 或者CREATE ANY SEQUENCE权限):

CREATE SEQUENCE test_sequence

INCREMENT BY 1 　 -- 每次加的个数据

START WITH 1 　　 -- 从1开始计数

NOMAXVALUE　　　 -- 不设置最大值

NOCYCLE　　　　　 -- 一直累加，不循环

CACHE 10 ; [注意]

如果设置了CACHE值，ORACLE将在内存里预先放置一些sequence，以使存取速度更快。cache里面的取完后，oracle自动再取一组到cache。

但是，使用cache可能会跳号， 当遇到数据库突然异常down掉(shutdown

abort),cache中的sequence就会丢失. 因此，推荐在create sequence的时候

使用 nocache 选项。

2. 使用 sequence:

sequence.CURRVAL -- 返回 sequence的当前值

sequence.NEXTVAL -- 增加sequence的值，然后返回 sequence 值

[注意] 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT

BY值，然后返回增加后的值。

CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。

一次NEXTVAL会增加一次 SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。

sequence 存储在数据字典中,存储于user_sequences表 LAST_NUMBER

为最终序列号，也就是sequence游标当前所在的位置。

//get sequence last_number SELECT LAST_NUMBER

FROM USER_SEQUENCES WHERE SEQUENCE_NAME=TEST_SEQNAME

// NEXTVAL 使游标指向下一位(增一或减一) SELECT SEQNAME.NEXTVAL FROM

USER_SEQUENCES 得到下一位游标的值

3. 修改 Sequence 用户必须拥有ALTER ANY SEQUENCE

权限才能修改sequence.

可以alter除start至以外的所有sequence参数. 如果想要改变start值，必须

drop sequence 再 re-create.

命令格式如下： ALTER SEQUENCE test_sequence

INCREMENT BY 10

MAXVALUE 10000

CYCLE 　　 -- 到10000后从头开始

NOCACHE ;

4. 删除 Sequence DROP SEQUENCE

order_seq;

三个用于日常监控开发库与对应测试库的存储过程

标签： 检查 监控 分类： Oracle 2007-01-19

13:25

--检查表,列是否一致

create or replace procedure check_tab_col(v_schema

varchar2)

as

isfindtab number;

isfindcol number;

begin

dbms_output.put_line('Sechema '||v_schema||' begin

searching...');

dbms_output.put_line(chr(10));

for i in

(select table_name from dba_tables where owner=v_schema) loop

select count(*) into isfindtab from dba_tables@dblink_testdbc

where owner=v_schema

and table_name=i.table_name;

if isfindtab=0 then

--没有找到表

dbms_output.put_line(rpad(i.table_name,30,' ')||' is lost in

testdbc');

else

--找到表,则继续搜索列

for j in (select column_name from dba_tab_columns where

owner=v_schema and table_name=i.table_name) loop

select count(*) into isfindcol from dba_tab_columns@dblink_testdbc

where owner=v_schema