Oracle实战笔记（第三天）

导读

　　今天的主要内容有：java连接Oracle、事务、Oracle中的事务处理、Oracle函数。

一、Java连接Oracle的两种方式

　　第一种：桥连接（JDBC_ODBC）（不推荐）

　　1、准备工作：桥连接需要配置ODBC数据源，本机必须有安装了Oracle数据库。

　　步骤：打开控制面板 > 管理工具 > ODBC数据源 > 添加 > 选择你安装的Oracle驱动程序：

　　测试一下：

　　如果用户名和密码没有错误，就会出现连接成功的标志！

　　2、使用java代码连接Oracle数据库

　　这些步骤几乎都是固定的，具体看代码即可：

 1 package test;
 2 
 3 import java.sql.*;
 4 
 5 import org.junit.Test;
 6 
 7 /*
 8  * 使用JDBC_ODBC桥连接Oracle数据库。
 9  */
10 public class TestJDBC_ODBC {
11     @Test
12     public void test(){
13         try {
14             //1、加载驱动(注意：jdk1.8版本已经删除了这个驱动，如果要运行需要切换到其他版本)
15             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
16             //2、获得连接(其中myOracle是数据源的名称，scott和tiger分别是要连接的账户和密码)
17             Connection conn = DriverManager.getConnection("jdbc:odbc:myOracle", "scott", "tiger");
18             
19             //下面就是使用conn这个连接对象来操作数据库
20             
21             //获得sql语句执行对象
22             Statement sm = conn.createStatement();
23             //执行sql语句并返回到结果集对象ResulSet中
24             ResultSet rs = sm.executeQuery("select * from emp");//查询emp表
25             
26             //处理结果集：打印到控制台
27             while(rs.next()){  
28                 System.out.println("员工姓名："+rs.getString(2));
29             }
30         } catch (Exception e) {
31             e.printStackTrace();
32         }
33     }
34 }

　　第二种：使用JDBC直接连接（推荐）

　　1、准备工作：导包。这种方式不需要配置数据源而是使用商家提供的jar包进行连接，而且支持远程连接，就是说本机不安装数据库也能连接到远程的数据库。

　　Oracle不同的版本有不同的jar包，我们可以上网搜索，或者更简单的办法，打开安装目录就能找到相应jar包：（比如我的安装目录）

　　2、java代码连接数据库

 1 package test;
 2 
 3 import java.sql.*;
 4 
 5 import org.junit.Test;
 6 
 7 //使用jdbc连接Oracle
 8 public class Testjdbc {
 9     @Test
10     public void test(){
11         try {
12             //1、加载驱动
13             Class.forName("oracle.jdbc.driver.OracleDriver");
14             //2、获得连接(注：@后面写连接的ip地址，后面分别是Oracle默认端口号1521:数据库名称,账户名和密码)
15             Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.183.1:1521:ORCL", "scott", "tiger");
16             
17             //3、获得sql语句执行对象
18             Statement st = conn.createStatement();
19             //4、书写sql语句
20             String sql = "select * from emp";//查询emp表
21             //5、执行sql并将结果保存到结果集ResultSet中
22             ResultSet rs = st.executeQuery(sql);
23             
24             //6、处理结果集
25             while(rs.next()){
26                 System.out.println("员工姓名："+rs.getString(2));//打印
27             }
28             
29             //7、关闭资源
30             rs.close();
31             st.close();
32         } catch (Exception e) {
33             e.printStackTrace();
34         }
35     }
36 }

　　测试结果：

　　总结：很明显，jdbc的连接方式更好，而桥连接不仅不支持远程连接，就连更新后的jdk1.8都删掉有odbc驱动包，所以尽量使用jdbc以减少不必要的麻烦。

二、子查询：补充内容

　　1、使用子查询插入数据

昨天的内容中我们使用子查询创建了新表，即：

1 --使用子查询创建新表
2 create table emp(eid,ename,sal) as select empno,ename,sal from emp;

　　这样的创表方式会将旧表中对应字段所有数据都更新到新表中，如果我们只需要工资大于2000的员工数据，除了删除数据我们还可以用子查询查入需要的数据：（相当于values()用select语句替换了）

--清空emp2表
trancate table emp2;
--使用子查询插入数据
insert into emp2(eid,ename,sal) select empno,ename,sal from emp where sal>2000;

　　2、使用子查询更新数据

　　同样的，除了插入数据，也能更新数据：（修改语句为：update 表名 set column1=value1,...where...，同样的我们将Value部分用select语句替代即可）

--将BLAKE的job、sal和comm改为和SMITH一样
update emp set (job,sal,comm) = (select job,sal,comm from emp where ename='SMITH') where ename='BLAKE';

三、Oracle事务（Transaction）

　　1、事务的概念

　　事务机制是为了保证数据的一致性，由一组DML（数据操作语言）语句组成，在一个事务中，这些语句要么全部失败，要么全部成功。

　　转账就是一个很典型的事务管理机制。比如张三给李四转账1000大洋，银行的转账业务处理过程大概可以分为两个步骤：1、从张三的账户减1000大洋；2、然后再往李四的账户加1000大洋。这两个过程对于数据库就相当于两句DML语句，一句是update张三的账户金额，另一句是update李四的账户金额。如果转账的时候顺顺利利是没什么问题的，关键在于如果在转账这一过程中出现了问题，比如李四的账号输错，变成了不存在的账号，那么张三的账户会扣掉1000大洋而李四的账户并不会收到转账，最终这1000大洋不翼而飞。

　　总而言之，为了保证数据的一致性（好比转账的这1000大洋），我们通常使用事务来管理数据库。上面的转账例子中，如果使用了事务，我们知道DML语句要么失败、要么成功，所以当无法转账到李四账户时第二条update语句失败，就会导致整个转账失败，进而之前的一切操作都变为无效。

　　2、事务的4个特性

原子性：说的是在一个事务内是不可分割的，要么成功，要么失败。
一致性：事务的前后数据的变化的一致性。
隔离性：是指事务之间互不干扰和影响，即并发执行事务时应当按照是连续地执行、互不干扰地执行(一个接一个)。（数据库可设置隔离级别，见下文）。
持久性：简单说就是事务一旦执行成功就持久化到数据库中。

　　3、事务的隔离级别

　　事务的隔离级别从低到高有：（不同的隔离级别涉及不同的并发访问问题）

Read Uncommitted（读未提交）：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都可能会发生。　　
Read Committed（读已提交） ：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题。
Repeated Read（可重复读） ：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读。
Serialization（串行化） ：理想的真正的事务隔离性，事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。

　　4、隔离级导致的并发访问问题

脏读(Drity Read)：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。[读未提交]
不可重复读(Non-repeatable read) : 在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这导致锁竞争加剧，影响性能。[读已提交，但另一事务修改了数据导致结果不同]
幻读（虚读）(Phantom Read) : 在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读仅指由于并发事务增加记录导致的问题，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。[读已提交，另一事务插入数据导致查询记录数增加]

四、Oracle中的事务处理

　　1、相关概念

　　回滚：发送异常时，使数据恢复到事务开启前的状态，即整个事务执行失败。

　提交：事务执行成功，数据会持久化到数据库。

　　2、基本演示

　　Oracle中的事务默认是关闭的，所以需要手动开启事务，并设置回滚和提交的位置：

 1 public void test(){
 2     //Oracle事务案例：
 3     Connection conn = null;
 4     try {
 5         //连接数据库
 6         Class.forName("oracle.jdbc.driver.OracleDriver");
 7         conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.183.1:1521:ORCL", "scott", "tiger");
 8         
 9         //-------[开启事务]：我们在执行sql前开启事务
10         conn.setAutoCommit(false);
11         
12         //执行sql语句
13         Statement st = conn.createStatement();
14         String sql = "update emp set sal=sal-500 where ename='SMITH'";//将smith的工资降低500
15         st.executeUpdate(sql);
16         sql = "update emp set sal=sal+500 where ename='BLAKE'";//将blake的工资涨500
17         st.executeUpdate(sql);
18         
19     } catch (Exception e) {
20         try {
21             //--------[回滚]：如果发生异常，说明事务执行失败，需要回滚
22             conn.rollback();
23         } catch (SQLException e1) {
24             e1.printStackTrace();
25         }
26         e.printStackTrace();
27     }finally{
28         //------------[提交事务]：如果程序执行到最后，说明一切顺利，需要提交事务来持久化数据
29         try {
30             conn.commit();
31             conn.close();
32         } catch (SQLException e) {
33             e.printStackTrace();
34         }
35     }
36 }

　　ps：在命令行中：我们也可以通过设置保存点savepoint达到开启事务的效果，用法如下：

　　3、只读事务：read only

　　说明：只读事务处理顾名思义，就是只能让用户进行读取（查询）操作。只读事务不只是限制作用，使用只读事务能确保用户读取某个时间点的数据，比如购票系统管理员需要每天18时开始统计售票情况，但可能在18时的时候任然有人在进行购票或退票的操作，这样统计出来的数据就不够准确，所以管理员可以在18时这个时间点设置只读事务，那么管理员就可以读取这个时间点的数据而不受其他并发操作的影响。所以只读事务常用于某个时间点的信息统计。

　　设置：sql > set transaction read only ;

五、Oracle函数

　　1、字符函数

lower(char) ：将字符串转化为小写
upper(char) ：将字符串转化为大写
length(char) ：获得字符串的长度

substr(char,m,n)：截取字符串（从1标号）角标为[m,n]的子串。

1 --将员工姓名首字母大写，其余小写
2 select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename))) from emp;

replace(char,search_str,replace_str)：字符串替换。

--假设员工名中的字母S需要用*和谐后才显示
select replace(ename,'S','*') from emp;

instr(char1,char2,[,n[,m]])：返回字符串char2在char1中首次出现的的位置。
```
--返回S在员工名字中首次出现的位置
select instr(ename,'S') from emp;
```

　　2、数学函数

　　数学函数的参数和返回值都是数字类型，数学函数包括：cos、cosh、exp、In、log、sin、sinh、sqrt、tan、tanh、acos、atan、asin、round等，我们掌握常用的即可：

round(n [,m])：四舍五入数字n：如果省略m，那么四舍五入为整数；如果m是整数，则四舍五入到小数点后m位（相当于保留m位小数）；如果m是负数，则四舍五入到小数点前-m位。
trunc(n [,m])： 截取数字n：如果省略m，那么就截取整数部分，即保留整数部分；如果m是整数，则截取到小数点后m位（相当于保留m位小数）；如果m是负数，则截取到小数点前-m位。
mod(m,n) ：取模。（取模运算和取余运算的数值是相同的，区别在于符号不同：取模运算结果的符号和除数的符号一致，而取余运算的符号和被除数的符号一致,比如：10 模 -3 = -1；10 余 -3 = 1;）。
floor(n) ：floor是地板的意思，所以返回 ≤ n的最大整数（向下取整）。
ceil(n) ：ceil是天花板的意思，所以返回 ≥ n的最小整数（向上取整）。
abs(n) ：返回绝对值。

　　3、日期函数

　　默认的date格式为：yy-mon-dd即18-1月-23的形式。

sysdate：返回系统时间

add_months(d,n)：返回当前时间增加n个月后的时间。

1 --查找入职满3年的员工姓名
2 select ename from emp where sysdate>=add_months(hiredate,12*3);
3 --返回员工加入公司的天数（保留整数）
4 select ename,ceil(sysdate-hiredate) "入职天数" from emp;

last_day(d)：返回指定日期所在月份的最后一天。

--查询入职月刚好最后一天入职的员工姓名和入职时间
select ename,hiredate from emp where hiredate = last_day(hiredate);
--查询入职月倒数前5天的员工姓名
select ename,hiredate from emp where hiredate >= last_day(hiredate)-4;

　　4、转换函数

　　转换函数是Oracle用来进行数据类型转换的函数，比如number = 9527，就能转换为char类型。注意这个现象：比如我们往emp表中插入数据时，ename即员工姓名是vachar2类型，但是如果我们插入时输入的值是数字，那么这个语句不会报错，因为Oracle在插入数据时会尝试将不匹配的数据类型进行转换，如果成功就继续插入，这就是Oracle隐含转换的一个例子。

　有的转换函数需要指定转换后的格式，Oracle格式符号规定如下：

to_char(data [,str_form])：将数据转换为char类型，第二个可选参数为格式。注意：对于数字格式如果输入错误会返回若干#，代表转换失败。
```
--查询1980年入职的员工
select ename,hiredate from emp where to_char(hiredate,'yyyy')='1980';
```
to_date(str,form)：将字符串为对应格式的日期。
to_number(date)：将数据转换为number类型。

　　5、用户环境函数

　　userenv()即用户环境函数，是UserEnvironment的缩写，用法：userenv('参数名')：返回用户所属环境的指定参数的参数值。

　　比如select userenv('language') from emp;就会返回emp表的环境语言。但userenv是一个遗留功能，这些功能是保留向后兼容性。甲骨文建议您使用SYS_CONTEXT函数使用内建的USERENV命名空间的当前功能。所以，我们尽量使用sys_context('usernev','参数名')来代替userenv函数。

　　参数名是固定可选的，常用的有以下几种参数名：

terminal ：当前会话客户所对应的终端标识符。比如我的就是：LenovoPC。
language ：语言。
db_name ：数据库名。
nls_date_format：当前会话客户对应的日期格式。
session_user ：当前会话客户对应的数据用户名。
current_schema：当前会话客户对应的默认方案名。（其实就是用户名，注意，一个用户对应一个方案，方案存储了很多数据对象）
host 　　：返回数据库所在主机的名称。