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

2019独角兽企业重金招聘Python工程师标准>>>

一、批量插入数据

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)

转载于:https://my.oschina.net/zhaokaiju/blog/826016