Oracle批量插入和高效的查询分页

一、批量插入数据

INSERT ALL
 into MS_USER(mobile_phone,user_name) values('12345678902','Kevin2')
 into MS_USER(mobile_phone,user_name) values('12345678903','Kevin3')
 into MS_USER(mobile_phone,user_name) values('12345678904','Kevin4')
SELECT * FROM dual;

二、高效的查询分页

1. 无ORDER BY排序：

SELECT tt.*
  FROM (SELECT ROWNUM AS rowNo,t.*
          FROM MS_USER t
         WHERE user_name LIKE '%Kevin%'
           AND ROWNUM <= 2) tt
 WHERE tt.rowNo >= 1;

2. 有ORDER BY排序（随着查询范围的扩大，速度会越来越慢）：

SELECT ttt.*
  FROM (SELECT ROWNUM AS rowNo,tt.*
          FROM (SELECT t.* FROM ms_user t 
               WHERE user_name LIKE '%Kevin%' 
               ORDER BY mobile_phone DESC) tt
         WHERE ROWNUM <= 2) ttt
 WHERE ttt.rowNo >= 1;

三、参考的文章

1.Oracle insert all语句介绍 (http://blog.csdn.net/indexman/article/details/28496901)

2.ORACLE分页查询SQL语法——最高效的分页(http://blog.sina.com.cn/s/blog_8604ca230100vro9.html)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!