所有的MIS系统都存在一个同样的需求,就是对于特定的数据,在一次批量操作过程中,如果数据已经存在,则对存在的数据按照现有情况进行更新,如果不存在,则需要加入数据库。这时,我们就可以考虑采用 Oracle 的 MERGE 函数,其具体用法如下:
sql 代码
- MERGE INTO [your table-name] [rename your table here]
- USING
- (
- [write your query here]
- )[rename your query-sql and using just like a table]
- ON
- ([conditional expression here] AND [...]...)
- WHEN
- MATHED
- THEN
- [here you can execute some update sql or something else ]
- WHEN
- NOT MATHED
- THEN
- [execute something else here ! ]
下面我再进行详细的说明:
上述代码格式中的加粗字体表示为 Oracle 关键字,[]以及其中的文字均是说明,在实际使用中不应有 [ words ] 出现。要注意()[圆括号]也是程序的组成部分。
为了能够使问题与实际问题更加贴切,不妨假设我们现在要给计算机系某个班的学生批量录入学生成绩。但是,录入时,如果学生的成绩已经存在时,老师只想对成绩进行修改,而如果成绩不存在则直接添加到库中。我们就老师的这些需求来构造一个执行语句。
sql 代码
- DEFINE TABLE :
- SCORE : using for save the students' score informations
- STUDENTS : the base information of students
- DEFINE COLUMNS :
- STUNO : the students' ID in the University
- STUNAME : students' name
- COURSENAME : course name
- COURSESCORE : the study-results of the reference course
- CLASSNAME : where the students study in
- STUGRADE : the students grade
- TERMNAME : the term which the reference course studied
- NOW BEAGIN TO WRITE DOWN THE STATEMENT HERE BLOW THIS LINE !
-
- MERGE INTO SCORE S
- USING
- (
- SELECT A.*,B.*,? MYSCORE FROM SCORE A,STUDENT B
- WHERE
- A.CLASSNO=? AND A.GRADE=?
- AND A.TERMNAME=? AND A.COURSENAME=?
- A.STUNO=B.STUNO(+)
- )X
- ON
- (S.STUNO=X.STUNO)
- WHEN
- MATHED
- THEN
- UPDATE SET COURSESCORE=X.MYSCORE
- WHEN
- NOT MATHED
- THEN
- INSERT
- (
- STUNO,STUNAME,COURSENAME,COURSESCORE,
- CLASSNAME,STUGRADE,TERMNAME
- )
- VALUES
- (
- X.STUNO,X.STUNAME,X.COURSENAME,X.MYSCORE,
- X.CLASSNAME,X.STUGRADE,X.TERMNAME
- );
注意到 MERGE 语句在最后的“;”(分号),这仅仅代表 MERGE 为一条完整的 SQL 语句。同时,要说明一下 USING 语句下方的 SQL 语句。这个语句仅仅是为了给后面语句的执行做准备性的工作,因此,如果你需要的数据仅仅是通过参数传入的那些值的话你就不需要再利用传入进来的参数在重新从库中查询。在 Oracle 的系统表中,有张 Dual 表,这样,你便可以使用 “select [your arguments] from dual ”的方式来构建这里的 SQL 语句,其中 [your arguments] 是你得到的一系列的参数,由于Dual表是系统表,因此可以大幅提升SQL的执行效率。
这时,如果你需要在你的 Java 程序中使用上述方法执行相应操作,则仅需要将其放入一个 for 循环中即可。由于是批量更新数据,因此,如果你不想对中间出现异常的数据进行提交,导致数据的不完整,则可以考虑使用 Java 的事务回滚机制。具体示例代码如下:
java 代码
- public yourMethod(statement,...){
- try{
- Connection conn=...;
- PreparedStatement ps=...;
- Resultset rs=...;
- conn.setAutoCommit(false);
- for(int i=0;i<...;i++){
-
- ......
- ps.addBatch();
- }
- ps.executeBatch();
- conn.commit();
- }catch(Exception e){
- try{
- conn.rollback();
- }catch(Exception el){}
- }
- }
这时,你会发现,在代码中直接使用 Merge 时,代码会变的非常复杂,首先是 SQL 的拼接变得非常复杂,接下来便是程序写完后的查错。因此,自然而然就会想到使用存储过程。接下来,我们来看看如何使用存储过程实现 Merge 调用过程。
Oracle 存储过程定义格式如下:
sql 代码
-
- CREATE OR REPLACE PROCEDURE PRO_YOUR_PROCEDURE (
- ELEMENT_01 IN ELEMENT_TYPE,
- ....... .... ..... ....
- ELEMENT_0S OUT ELEMENT_TYPE,
- .... ... ... ....
- )
- AS
- ARGUMENT_01 ARGUMENT_TYPE(ARGUMENT_RANGE);
- ...................
- BEGIN
- MERGE INTO YOUR_TABLE_NAEM [RENAEM_YOUR_TABLE_HERE]
-
- END;
- EXCEPTION
- WHEN
- OTHERS
- THEN
- RAISE_APPLICATION_ERROR(-20003,[YOUR EXCEPITON MESSAGE HERE !]);
-
- END;
- COMMIT;
- END PRO_YOUR_PROCEDURE;
其中,[RAISE_APPLICATION_ERROR(-20003,[YOUR EXCEPITON MESSAGE HERE !]);]中的“-20003”是 Oracle 提供的用于用户进行错误自定义的扩充代码。其值可以随便定义,但是也有范围: -20000 到-20999的负整数。
接下来就是如何来在 Java 程序中调用你的存储过程。Oracle为了方便开发人员调用其存储过程,开发了一个 [ OracleCallableStatement ] 位于 oracle.jdbc 包内。
核心代码如下:
java 代码
- OracleCallableStatement cal = null;
- cal=(OracleCallableStatement)conn.getMetaData().getConnection().prepareCall("call PRO_......");
- ........
- .............
- .......
- for(………………){
- …………
- cal.setDouble(i,ARGUMENTS);
- …………
- cal.executeUpdate();
- }
从中可以看出,OracleCallableStatement对象事实上承担了PreparedStatement对象的使命,当然如果需要的话,能够支持事务回滚是个不错的主意,关于这个问题,可以参考上面直接使用 Merge 函数的 Java 代码。
分享到:
相关推荐
oracle Merge 函数
前言 MERGE语句是Oracle9i...本博客介绍一下Oracle merge合并函数,业务场景:新增数据的时候要先查询数据库是否已经有改数据,有数据就更新数据,没数据才新增数据,这是很常见的业务场景,如果是用Oracle数据库的话
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之递归...
oracle数据匹配merge into的实例详解 前言: 很久之前,估计在2010年左右在使用Oralce,当时有个需求就是需要对两个表的数据进行匹配,这两个表的数据结构一致,一个是正式表,一个是临时表,这两表数据量还算是...
4、MERGE INFO 合并资料 5、层次查询(HIERARICAL RETRIVEL) 6、DECODE函数和行列互换 7、CASE表达式 8、ROWNUM-TOP-N分析 9、相关子查询和非相关子查询 10、增强GROUP BY 11、分析函数(ANALYTICAL FUNCTIONS) 12...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
本书主要覆盖Oracle开发必备的重要知识点:数据库编程规范、Oracle开发常用工具及使用、MERGE方法、神秘的NULL和讨厌的CHAR、扩展GROUP BY、Oracle自动类型转换、Oracle分析函数、Oracle层次查询、11g R2新特性之...
她是Oracle ACE,也是OakTable(Oracle社区中著名的“Oracle科学家”的非正式组织)的成员,经常在技术会议上演讲。她的著作还包括 Expert Oracle Practices和Beginning Oracle SQL,博客主页是karenmorton....
1.9 MERGE语句 22 1.10 小结 24 第2章 SQL执行 25 2.1 Oracle架构基础 25 2.2 SGA-共享池 27 2.3 库高速缓存 28 2.4 完全相同的语句 29 2.5 SGA-缓冲区缓存 32 2.6 查询转换 35 2.7 视图合并 36 2.8 子查询解嵌套 ...