oracle4

分页查询
按雇员的id号升序取出 
oracle的分页一共有三种方式 

1.根据rowid来分 
  select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc; 
执行时间0.03秒 
2.按分析函数来分 
  select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980; 
执行时间1.01秒 
3.按rownum来分 
  select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980; 
执行时间0.1秒 

其中t_xiaoxi为表名称，cid为表的关键字段，取按cid降序排序后的第9981-9999条记录，t_xiaoxi表有70000多条记录。 
个人感觉1的效率最好，3次之，2最差。 

//测试通过的分页查询okokok 
select * from (select a1.*, rownum rn from(select ename,job from emp) a1 where rownum<=10)where rn>=5; 

下面最主要介绍第三种：按rownum来分 
1. rownum 分页 
   SELECT * FROM emp;    
 2. 显示rownum[oracle分配的] 
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; 
(SELECT * FROM emp) e中的(SELECT * FROM emp) 当成内嵌视图，取了一个别名叫e,
e.*表示把后面子查询的信息在重新取出来，
 ROWNUM  rn相当于Oracle分配的每行的ID号 
3.挑选出6—10条记录 
先查出1-10条记录 
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10; 
如果后面加上rownum>=6是不行的， 
4. 然后查出6-10条记录 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 
5. 几个查询变化 
a. 指定查询列，只需要修改最里层的子查询(SELECT * FROM emp) 
只查询雇员的编号和工资 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 
b. 排序查询，只需要修改最里层的子查询(SELECT * FROM emp)  
工资排序后查询6-10条数据 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6; 

用查询结果创建新表
这个命令是一种快捷的建表方式 
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp; 
创建好之后，desc mytable；和select * from mytable;看看结果如何？ 

合并查询 
合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus 
多用于数据量比较大的数据局库，运行速度快。 
1). union 
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。 
SELECT ename, sal, job FROM emp WHERE sal >2500 
UNION 
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 
2).union all 
该操作符与union相似，但是它不会取消重复行，而且不会排序。 
SELECT ename, sal, job FROM emp WHERE sal >2500 
UNION ALL 
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。 
3). intersect 
  使用该操作符用于取得两个结果集的交集。 
SELECT ename, sal, job FROM emp WHERE sal >2500 
INTERSECT 
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 
4). minus 
使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。 
SELECT ename, sal, job FROM emp WHERE sal >2500 
MINUS 
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 
（MINUS就是减法的意思） 

创建数据库有两种方法： 
1). 通过oracle提供的向导工具。√ 
   database Configuration Assistant  【数据库配置助手】 
2).我们可以用手工步骤直接创建。

七：java操作oracle

java连接oracle 
 介绍：前面我们一直在plsql中操作oracle，那么如何在java 程序中操作数据库呢？ 下面我们举例说明，写一个java，分页显示emp表的用户信息。 

Java代码 

1. package com.sp;   
3. import java.sql.Connection;   
4. import java.sql.DriverManager;   
5. import java.sql.ResultSet;   
6. import java.sql.Statement;   
8. //演示  如何使用 jdbc_odbc桥连接方式   
9. public class TestOracle {   
11. public static void main(String[] args) {   
12. try {   
14. // 1.加载驱动   
15. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   
17. // 2.得到连接   
18. Connection ct = DriverManager.getConnection(   
19. "jdbc.odbc:testConnectOracle", "scott",    
21. "tiger");   
23. // 从下面开始，和SQL Server一模一样   
24. Statement sm = ct.createStatement();   
25. ResultSet rs = sm.executeQuery("select * from emp");   
26. while (rs.next()) {   
27. //用户名   
28. System.out.println("用户名： "+rs.getString(2));   
29. //默认是从1开始编号的   
30. }   
31. } catch (Exception e) {   
32. e.printStackTrace();   
33. }   
34. }   
35. }  

package com.sp;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

//演示  如何使用 jdbc_odbc桥连接方式

public class TestOracle {

public static void main(String[] args) {

try {

// 1.加载驱动

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// 2.得到连接

Connection ct = DriverManager.getConnection(

"jdbc.odbc:testConnectOracle", "scott", 

"tiger");

// 从下面开始，和SQL Server一模一样

Statement sm = ct.createStatement();

ResultSet rs = sm.executeQuery("select * from emp");

while (rs.next()) {

//用户名

System.out.println("用户名： "+rs.getString(2));

//默认是从1开始编号的

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

在得到连接那里，要去配置数据源，点击控制面板-->系统和安全-->管理工具-->数据源(ODBC),打开后点添加,如图： 

可以看到，有个Oracle in OraDb10g_home1的驱动，它是Oracle安装完后自动加上去的。 选中后，点完成，再填如下信息,如图： 

  

这样配好后基本就可以了，但为了安全起见，建议大家测试一下，点击 Test Connection按钮， 测试通过后点ok,然后数据源就生成了，如图：

然后把数据源名称写进jdbc.odbc:里。 

这里要注意：jdbcodbc能不能远程连接呢？不能远程连接，也就是你这样写的话就意味着java程序和oracle数据库应该是在同一台机器上，因为这里没有指定IP地址，肯定默认就是本地。如果要远程连，就用jdbc,jdbc是可以远程连的。 

运行TestOracle.java,控制台输出....................... 

可惜我没运行成功，说 

java.sql.SQLException: No suitable driver found for jdbc.odbc:testConnectOracle 
at java.sql.DriverManager.getConnection(Unknown Source) 
at java.sql.DriverManager.getConnection(Unknown Source) 
at com.sp.TestOracle.main(TestOracle.java:18) 
不知道为什么。。。 

接下来讲解用JDBC的方式连接Oracle 

Java代码 

1. package com.sp;   
3. import java.sql.Connection;   
4. import java.sql.DriverManager;   
5. import java.sql.ResultSet;   
6. import java.sql.Statement;   
8. //使用 jdbc连接oracle   
9. public class TestOracle2 {   
11. public static void main(String[] args) {   
12. try {   
14. // 1.加载驱动   
15. Class.forName("oracle.jdbc.driver.OracleDriver");   
17. // 2.得到连接   
18. Connection ct = DriverManager.getConnection   
20. ("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");   
22. // 从下面开始，和SQL Server一模一样   
23. Statement sm = ct.createStatement();   
24. ResultSet rs = sm.executeQuery("select * from emp");   
25. while (rs.next()) {   
26. //用户名   
27. System.out.println("用户名： "+rs.getString(2));   
28. //默认是从1开始编号的   
29. }   
30. } catch (Exception e) {   
31. e.printStackTrace();   
32. }   
33. }   
34. }  

package com.sp;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

//使用 jdbc连接oracle

public class TestOracle2 {

public static void main(String[] args) {

try {

// 1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

// 2.得到连接

Connection ct = DriverManager.getConnection

("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");

// 从下面开始，和SQL Server一模一样

Statement sm = ct.createStatement();

ResultSet rs = sm.executeQuery("select * from emp");

while (rs.next()) {

//用户名

System.out.println("用户名： "+rs.getString(2));

//默认是从1开始编号的

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

记得要把驱动包引入，classes12.jar 
运行，。。。。 再次可惜，我还是没运行成功，错误是： 
java.sql.SQLException: Io 异常: The Network Adapter could not establish the 

connection 
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) 
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) 
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334) 
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:418) 
at oracle.jdbc.driver.OracleDriver.getConnectionInstance 

(OracleDriver.java:521) 
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325) 
at java.sql.DriverManager.getConnection(Unknown Source) 
at java.sql.DriverManager.getConnection(Unknown Source) 
at com.sp.TestOracle2.main(TestOracle2.java:18) 
我也不知道为什么。。。 幽怨了。。 

接下来建个web project，来测试oracle的分页，挺麻烦，不记录了。。 


在oracle中操作数据 - 使用特定格式插入日期值 
 使用 to_date函数n 
请大家思考： 如何插入列带有日期的表，并按照年-月-日的格式插入？ 
insert into emp values  (9998,  'xiaohong',  'MANAGER',  7782,  to_date('1988-12- 

12', 'yyyy-mm-dd'),  78.9,  55.33,  10); 

注意： 
insert into emp values  (9998,  'xiaohong',  'MANAGER',  7782,  '12-12月-1988', 

78.9,  55.33,  10); 
这句语句是可以成功运行的 



使用子查询插入数据 
 介绍n 
当使用valus子句时，一次只能插入一行数据，当使用子查询插入数据时，一条inset语句可以插 

入大量的数据。当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据。 
把emp表中10号部门的数据导入到新表中 
create table kkk(myId number(4), myName varchar2(50), myDept number(5)); 

update emp set job=(select job from emp where ename='SMITH') where ename='SCOTT'; 

update emp set sal=(select sal from emp where ename='SMITH') where ename='SCOTT'; 

insert into kkk (myId, myName, myDept)  select empno, ename, deptno from emp where 

deptno = 10; 

 介绍n 
使用update语句更新数据时，既可以使用表达式或者数值直接修改数据，也可以使用子查询修改 

数据。 
问题：希望员工SCOTT的岗位、工资、补助与SMITH员工一样。 
update emp set(job, sal, comm)=(select job, sal, comm from emp where ename='SMITH') where ename='SCOTT';