oracle数据库连接,用ibatis执行SQL的时候报无法从源表中获取一组稳定的数据的异常是怎么回事?
解决方案 »
- oracle连接无故断掉3次 昨晚到今天下午 process数和session数都没问题
- oracle 一对一同时插入问题
- sql问题
- SQL行列转换
- cnoug网站好久不能打开了
- ORA-01756 sql语句连结问题求助 急
- 一个存储老是有问题,请大家帮忙解决一下
- 求助:关于oracle数据排序的问题.
- 有关显示列的问题
- 请问高手如何实现对oracle数据中long字段类型数据的查询???
- 存储过程中发生CASE_NOT_FOUND 异常,日志中会有记录吗?在哪个日志文件中看?
- 在版本高于9206的时候,有没有其它方法,可以代替ORA_SQL_TXT函数,用来实现update ,delete ,insert触发器的日志?
select id
from tableA a,
tableB b
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="startDate">
a.act_time >= #startDate#
and a.act_time <= #endDate#
and a.id = b.id
</isNotNull>
</dynamic>
</select>错误例2:将整个sql语句用<![CDATA[ ]]>标记来避免冲突,在一般情况下都是可行的,但是由于该sql配置中有动态语句(where部分),将导致系统无法识别动态判断部分,导致整个sql语句非法。<select id="find" parameterClass="java.util.Map" resultClass="java.lang.Long">
<![CDATA[
select id
from tableA a,
tableB b
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="startDate">
a.act_time >= #startDate#
and a.act_time <= #endDate#
and a.id = b.id
</isNotNull>
</dynamic>
]]>
</select>正确做法:缩小范围,只对有字符冲突部分进行合法性调整。<select id="find" parameterClass="java.util.Map" resultClass="java.lang.Long">
select id
from tableA a,
tableB b
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="startDate">
a.act_time >= #startDate#
<![CDATA[ and a.act_time <= #endDate# ]]>
and a.id = b.id
</isNotNull>
</dynamic>
</select>
oracle9i的mergeMERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。Oracle 9i/10g merge 用法oracle9i的mergeMERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。语法如下:MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS aliasON (join condition)WHEN MATCHED THENUPDATE SET
col1 = col_val1,col2 = col2_valWHEN NOT MATCHED THENINSERT (column_list) VALUES (column_values);下面看个具体的例子: http://blog.itpub.net/post/468/14844CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;表已创建。CREATE TABLE T1 AS SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE FROM DBA_TABLES;表已创建。MERGE INTO T1 USING T ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)WHEN MATCHED THENUPDATE SET T1.ID = T.IDWHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);6165 行已合并。SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
MINUSSELECT * FROM T1;未选定行
MERGE语法其实很简单,下面稍微修改一下例子。SQL> DROP TABLE T;表已丢弃。SQL> DROP TABLE T1;表已丢弃。SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;表已创建。SQL> CREATE TABLE T1 AS SELECT ROWNUM ID, OWNER, TABLE_NAME FROM DBA_TABLES;表已创建。SQL> MERGE INTO T1 USING T
2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
MERGE INTO T1 USING T*
ERROR 位于第 1 行:
ORA-30926: 无法在源表中获得一组稳定的行这个错误是使用MERGE最常见的错误,造成这个错误的原因是由于通过连接条件得到的T的记录不唯一。最简单的解决方法类似:SQL> MERGE INTO T1
2 USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
3 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
4 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
5 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);5775 行已合并。另外,MERGE语句的UPDATE不能修改用于连接的列,否则会报错,详细信息可以参考:===============================================================ref: http://tomszrp.itpub.net/post/11835/263865 在Oracle 10g之前,merge语句支持匹配更新和不匹配插入2种简单的用法,在10g中Oracle对merge语句做了增强,增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。 参考Oracle 的SQL Reference,大家可以看到Merge Statement的语法如下:MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] { table | view | subquery } [t_alias] ON ( condition ) WHEN MATCHED THEN merge_update_clause WHEN NOT MATCHED THEN merge_insert_clause;下面我在windows xp 下10.2.0.1版本上做一个测试看看SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - ProductionSQL> 一、创建测试用的表SQL> create table subs(msid number(9),2 ms_type char(1),3 areacode number(3)4 );表已创建。SQL> create table acct(msid number(9),2 bill_month number(6),3 areacode number(3),4 fee number(8,2) default 0.00);表已创建。SQL> SQL> insert into subs values(905310001,0,531);已创建 1 行。SQL> insert into subs values(905320001,1,532);已创建 1 行。SQL> insert into subs values(905330001,2,533);已创建 1 行。SQL> commit;提交完成。SQL> 二、下面先演示一下merge的基本功能1) matched 和not matched clauses 同时使用merge into acct a using subs b on (a.msid=b.msid)when MATCHED thenupdate set a.areacode=b.areacodewhen NOT MATCHED theninsert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);2) 只有not matched clause,也就是只插入不更新merge into acct a using subs b on (a.msid=b.msid) when NOT MATCHED theninsert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);3) 只有matched clause, 也就是只更新不插入merge into acct a using subs b on (a.msid=b.msid)when MATCHED thenupdate set a.areacode=b.areacodeConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as studySQL> select * from subs;MSID MS_TYPE AREACODE---------- ------- --------905310001 0 531905320001 1 532905330001 2 533SQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------SQL> SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when MATCHED then4 update set a.areacode=b.areacode5 when NOT MATCHED then6 insert(msid,bill_month,areacode) 7 values(b.msid,'200702',b.areacode);DoneSQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------905320001 200702 532 0.00905330001 200702 533 0.00905310001 200702 531 0.00SQL> insert into subs values(905340001,3,534);1 row insertedSQL> select * from subs;MSID MS_TYPE AREACODE---------- ------- --------905340001 3 534905310001 0 531905320001 1 532905330001 2 533SQL> SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when NOT MATCHED then4 insert(msid,bill_month,areacode) 5 values(b.msid,'200702',b.areacode);DoneSQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------905320001 200702 532 0.00905330001 200702 533 0.00905310001 200702 531 0.00905340001 200702 534 0.00SQL> update subs set areacode=999;4 rows updatedSQL> select * from subs;MSID MS_TYPE AREACODE---------- ------- --------905340001 3 999905310001 0 999905320001 1 999905330001 2 999SQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------905320001 200702 532 0.00905330001 200702 533 0.00905310001 200702 531 0.00905340001 200702 534 0.00SQL> SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when MATCHED then4 update set a.areacode=b.areacode;DoneSQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------905320001 200702 999 0.009053300