Oracle的学习三:java连接Oracle、事务、内置函数、日期函数、转换函数、系统函数

时间:2024-04-25 18:34:39

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;

  • 用户和方案的关系:

建立一个用户的同时,数据库自动建立一个和用户名同名的方案,方案中存储用户的数据对象(表、视图、...)。