平时用来测试的异常处理
我们都是通过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
扫一扫
1701
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
