Oracle编程常见错误和分析

1.        资源正忙错误

在pl/sql developer开一个sql window,执行如下sql不提交
Create Table aa(a Int);
Insert Into aa Values(234);

再开一个sqlwindow执行
Drop Table aa

就会报ora-00054错误。

Oracle锁的知识：
此时Oracle已经对返回的结果集上加了排它的行级锁，所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放，产生的外在现象就是其他的操作将发生阻塞，这个这个操作commit或rollback.

同样这个查询的事务将会对该表加表级锁，不允许对该表的任何ddl操作，否则将会报出ora-00054错误：:resource busy and acquire with nowait specified.
因为DDL语句需要表上的排他锁,而这与DML语句已在表上放置了共享锁相冲突,所以试图在表中插入一个列的这条DDL语句会失败.需要注意的是:在类似情况下,DML语句会等待并不断进行尝试,直至获得其所需的锁(换句话说就是挂起);而DDL语句则会由于错误立即终止.

ejb BllRequireNewTrans在ejb-jar.xml被配置为需要一个新事务，所以调用此ejb方法的事务和BllRequireNewTrans不在一个事务中,不注意就会引发上面的错误。
<container-transaction>
<method>
                    <ejb-name>BllRequireNewTrans</ejb-name>
                    <method-intf>Remote</method-intf>
                    <method-name>*</method-name>
          </method>
          <trans-attribute>RequiresNew</trans-attribute>
</container-transaction>
</assembly-descriptor>

2.        字段被全部置空的错误
Create Table bb(a Int, b Int, c Int)

Insert Into bb Values(1,2,3)
Insert Into bb Values(3,2,3)
Insert Into bb Values(4,5,6)

Create Table cc(a Int, b Int, c Int);
Insert Into cc Values(1,20,30)

Update Bb Set (a, b, c) = (Select a, b, c From cc Where cc.a = bb.a + 1)
Where Exists(Select a, b, c From cc Where cc.a = bb.a)

Update Bb Set (a, b, c) = (Select a, b, c From cc Where cc.a = bb.a)

     Where Exists(Select a, b, c From cc Where cc.a = bb.a)

 

    Update Bb Set (a, b, c) = (Select a, b, c From cc Where cc.a = bb.a + 1)

    Where Exists(Select a, b, c From cc Where cc.a = bb.a)

where 符合条件的记录字段都被置空，因为where条件成立，而子查询找不到记录
2个子查询的where子句应该保证是一样的，用一个字段去更新另外一个字段一定要特别小心

 

3.        Merge的错误：ora-39026无法在源表中获取一组稳定的行
Merge Into Cc
Using Bb
On (Cc.a = Bb.a)
When Matched Then
   Update Set Cc.b = Bb.b, Cc.c = Bb.b
When Not Matched Then
   Insert (a, b, c) Values (Bb.a, Bb.b, Bb.c);

bb表的字段a有重复记录。On子句应该唯一关键字匹配

 

4.        ORA-04091 表XXX发生了变化,触发器/函数不能读它

对于某一个表函数读取不能和变更(update)同时发生
Create Or Replace Function ma_aaa
Return Int
Is
  l_max Int;
Begin
  Select nvl(Max(a), 1) Into l_max From aaa;
  Return l_max;
End;

Create Table aaa(a Int)

Insert Into aaa Values(100)

Update aaa Set a=max_aaa Where a=100

 

        

不能读正在变化的表里的数据，否则结果是不确定的

 

 

6.        Oracle的NULL和大小写问题
null和任何值比较都为空
空串是null, 如Select '' + 'asdf' From dual; 结果为null
oracle字符比较是大小写敏感的
数据字典如all_tables, all_tab_columns里的字符都是大写