正文
Oracle的学习三：java连接Oracle、事务、内置函数、日期函数、转换函数、系统函数
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
1.java程序操作Oracle
- java连接Oracle
- JDBC_ODBC桥连接
1.加载驱动：
Class.forName("sun.jdbc.odbc.JdbcodbcDriver");
2.得到连接：(和SQL Server一样)
首先，引用数据包： import java.sql.*;
连接： Connection ct=DriverManager.getConnection("jdbc:odbc:testds","用户名","密码")； 需配置数据源，数据源名称设为：testds
3.下面和SQL Server一摸一样：
创建执行状态对象： Statement sm =ct.createStatement();
查询的结果集： ResultSet rs =sm.executeQuery("select * from emp");
取得数据： rs.getString(字段序号，从1开始);
4.关闭各种资源：
rs.close();
sm.close();
ct.close();
解释：JDBC_ODBC不能远程连接。
- JDBC
1.加载驱动：
Class.forName("oracle.jdbc.driver.OracleDriver");
2.得到连接：(和SQL Server一样)
首先，引用数据包： import java.sql.*;
连接： Connection ct=DriverManager.getConnection("jdbc:orcle:thin:@127.0.0.1:1521:myOrcl","用户名","密码")；
3.下面和SQL Server一摸一样：
创建执行状态对象： Statement sm =ct.createStatement();
查询的结果集： ResultSet rs =sm.executeQuery("select * from emp");
取得数据： rs.getString(字段序号，从1开始);
4.关闭各种资源：
rs.close();
sm.close();
ct.close();
注意：JDBC能远程连接，并且需要引用jdbc驱动。
- Java实现分页
--以下是在JSP中实现的代码：
<h2>数据分页</h2>
<table>
<tr><td>UserName</td><td>Salary</td></tr>
<%
Connection ct =null;
Statement sm=null;
ResultSet rs=null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
ct =DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myOrcl", "scott", "scott");
sm =ct.createStatement();
//
String s_pageNow = (String)request.getParameter("newPage");
int pageNow =1;
if(s_pageNow !=null)
{
pageNow =Integer.parseInt(s_pageNow);
}
//
int pageCount =0;
int recordCount =0;
int pageSize =3;
rs = sm.executeQuery("select count(*) from emp");
if(rs.next())
{
recordCount =rs.getInt(1);
if(recordCount%pageSize ==0)
{
pageCount =recordCount/pageSize;
}
else
{
pageCount =recordCount/pageSize + 1;
}
}
rs =sm.executeQuery("select * from (select a1.*, rownum RN from (select * from emp) a1 where rownum<="+ pageSize*pageNow +") where RN>=" + (pageSize*(pageNow-1)+1));
while(rs.next())
{
out.println("<tr><td>" + rs.getString(2) +"</td><td>" + rs.getDouble(6)+ "</td><tr>");
}
out.println("</table>");
//
for(int i=1; i<pageCount; i++)
{
out.print("<a href=myTest.jsp?newPage=" + i + "> [" + i + "] </a>");
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try
{
rs.close();
sm.close();
ct.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
%>
</body>
2.Oracle中的事务处理
数据存储的逻辑单位是数据块，数据操作的逻辑单位是事务。
事务---用户定义的一组操作序列，由一组相关的DML语句组成，是数据库应用程序的基本逻辑单位。用于保证数据的一致性，该组的DML语句要么全部成功，要么全部失败。事务管理技术主要包括数据库的恢复技术和并发控制技术。
- 事务和锁
当执行事务操作时，Oracle会在被作用的表上加锁，防止其他用户更改表的结构。事务中的SQL语句只限于DML命令，而DDL、DCL语句时不能会回退的。
- 事务控制
- 事务的开始与结束
Oracle是没有"开始事务处理"的语句。
事务开始：连接到数据库，并开始执行第一条DML语句；当前一个事务结束或者执行一条会自动提交事务的语句；
事务结束：执行一条commit语句或rollback语句时；执行一条会自动提交事务的语句时；执行一条DML语句，却该语句失败了会自动回退。
回退部分事务(rollback to savapoint)不会终止一个事务，也不会开始下一个事务。
事务自动提交：执行一条DDL语句；执行一条DCL语句；断开与数据库的连接(disconnect、exit、关闭sqlplus、意外关闭)；
sql *plus中可以设置自动提交功能： set autocommit on/off;
- 设置事务
- 提交事务
执行commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁。当使用commit语句结束事务后，其他会话将可以查看到事务变化后的新数据。exit也会自动提交。
- 回退事务
回退全部事务：当执行rollback时，可以回退全部事务。 rollback;
回退部分事务：
ex:保存点--事务中的中间标志，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。
设置保存点：savepoint aa;(可以创建多个保存点)
回退到aa保存点： rollback to aa; (确保事务没有被commit，commit后所有保存点会删除)
- java程序中如何使用事务 Connection ct=null;
设置不自动提交事务： ct.setAutoCommit(false);
无异常时提交事务： ct.commit();
异常时事务回滚： ct.rollback();
- 只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其它DML操作的事务，使用只读事务可以确保用户只能取得某个时间点的数据。在设置了只读事务后，尽管其它会话可能会提交新的事务，但是只读事务将不能取得最新数据的变化，从而可以保证取得特定时间点的数据信息。
设置只读事务： set transaction read only;
- 事务的状态与性质
事务的状态：事务初态、活动状态、失败状态、终止状态、提交状态。
事务的性质： ACID属性
原子性(不可分割的逻辑单位)、一致性(操作前后逻辑上的一致性)、隔离性(一个事务内部操作的数据对其他事务是隔离的。脏读、不可重复读、幻像)、持久性(一旦提交成功，永久保存)。
3.SQL函数的使用
Oracle中的dual表是一个虚拟的表，它有一行一列，所有人是sys，可供所有用户使用，但是不能插入数据。
- 单行字符函数
ASCII(c)
--返回c的首字符在ASCII码中对应的十进制
char(n)
--返回十进制ASCII码n对应的字符
concat(c1,c2)
--返回将c2添加到c1后面而形成的字符串。
initcap(c)
--返回将c的每个首字符都大写，其它字符都小写的字符串。单词之间以空格等分界。
instr(c1,c2[, n[, m]]
-- 在c1中从n开始搜索c2第m次出现的位置，并返回该位置数字。若n为负数，则搜索从右向左进行，但位置数字仍按从左到右计算。
length(c)
-- 返回字符串c的长度，包括所有的后缀空格，c=null，则值为null。
lower(char)
-- 将字符串转化为小写的格式。
lpad(c1,n[,c2])
--在c1的左边填充c2，使之总长度为n。c2默认为空格。若c1>n,返回c1的左边n个字符。
ltrim(c1[,c2])
--去掉c1左边所包含的c2中的任何字符，直至遇到不是c2的字符结束，返回剩余的字符串，c2默认是空格。
nanvl(x,value)
--如果x匹配NaN(非数字)，则返回value，否则返回x
nvl(x,value)
--如果x为NULL(空)，就返回value，否则返回x
nvl2(x,value1,value2)
--如果x为NULL(空)，就返回value1，否则返回value2
replace(c1,c2[,c3])
--把c1中出现的c2都替换成c3,返回剩余的字符串。c3默认为null。
rpad(c1,n[,c2])
--在c1的右边填充c2，使之总长度为n。c2默认为空格。若c1>n,返回c1的右边n个字符。
rtrim(c1[,c2])
--去掉c1右边所包含的c2中的任何字符，直至遇到不是c2的字符结束，返回剩余的字符串，c2默认是空格
substr(c,m,n)
-- 取字符串c的子串(从第m个开始(1,2...)，长度为n的字符串)。
translate(c1,c2,c3)
--把c1中的所有在c2中出现的字符用c3来代替，返回替换后的c1。c1，c2，c3任何一个为null，值为null；
upper(char)
-- 将字符串转化为大写的格式。
- 单行数学函数
介绍：数学函数的输入和返回值的数据类型都是数字类型，数学函数包括cos、cosh、exp、ln、log、sin、sinh、sqrt、tan、tanh、acos、asin、atan、round等。三角函数用于处理角度。
abs(n)
--返回数字n的绝对值
acos(n)
--返回数字的反余弦值
asin(n)
--返回数字的反正弦值
atan(n)
--返回数字的正切值
atan2(x,y)
--返回x/y的正切值，y不能为0
cos(n)
--返回数字的反余弦值
cosh(n)
--返回数字的双反余弦值
ceil(n)
--返回大于或等于n的最小整数。
注：在Oracle测试，可以使用dual表(虚拟表、测试表)。
exp(n)
--返回e的n次幂
floor(n)
--返回小于或等于n的最大整数。
log(m,n)
--返回x的自然对数值
ln(x)
--返回对数值
mod(m,n)
--取模运算 m%n；n为0，则返回y
power(m,n)
--返回m的n次幂
round(n,[m])
--用于执行四舍五入，如果省掉m，则四舍五入到整数；如果m是整数，则四舍五入到小数点的m位后，如果时负数，则四舍五入到小数点的m位前。
sign(x)
--检测x的正负，如果x小于0，则返回-1；如果x大于0，则返回1；
sin(x)
--返回x的正弦值。
sinh(x)
--返回x的双正弦值。
sqrt(x)
--返回x的平方根，x>=0。
tan(x)
--返回x的正切值。
tanh(x)
--返回x的双正切值。
trunc(n,[m])
--用于截取数字。如果省掉m，就截取小数部分，如果m是正数，就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位。
- 单行日期函数
介绍：用于处理date类型的数据
sysdate
--返回系统时间
add_months(d,n)
--在时间d的基础上，加n个月。
eg:返回入职时间大于8个月的纪录： sysdate>add_months(时间字段，8)；
last_day(d)
--返回指定日期所在月份的最后一天。
- 转换函数
介绍：转换函数用于将数据类型从一种转换为另外一种，在某些情况下，oracle server允许值的数据类型和实际的不一样，这时oracle server会隐含的转化数据类型。为提高可靠性，应使用转换函数。
to_char
日期转换：to_char(日期值或日期字段，'格式') eg:'yyyy-mm-dd hh24:mi:ss'
格式：yy：两位数字的年份；
yyyy：四位数字的年份；
mm：两位数字的月份；
dd：两位数字的天；
hh24：24小时制的小时数；
hh12：12小时制的小时数；
mi、ss：小时分钟/秒；
货币转换：to_char(货币值或货币字段, '格式')： eg:'L9,999.99'
格式：9：显示数字，并忽略前面0；
0：显示数字，如位数不足，则用0补齐；
.：在指定位置显示小数点；
$：在数字前显示美元符号；
L：在数字前加本机货币符号；
C：在数字前加国际货币符号；
G：在指定位置显示组分隔符；
D：在指定位置显示小数点符号(.)；
to_date
函数to_date用于将字符串转换成date类型的数据。
- 系统函数
sys_context
(1) terminal --当前会话客户所对应的终端的标识符
(2) lanuage --语言
(3) db_name --当前数据库名称
(4) nls_date_format --当前会话客户所对应的日期格式
(5) session_user --当前会话客户所对应的数据库用户名
(6) current_schema --当前会话客户所对应的默认方案名
(7) host --返回数据库所在的主机的名称
eg： select sys_context('userenv', 'db_name') from dual;
- 用户和方案的关系：
建立一个用户的同时，数据库自动建立一个和用户名同名的方案，方案中存储用户的数据对象(表、视图、...)。
