`
kb5706
  • 浏览: 40978 次
  • 性别: Icon_minigender_1
  • 来自: 大连
文章分类
社区版块
存档分类
最新评论

oracle实战第三天--事务处理与函数

 
阅读更多

内容介绍

  1. java程序如何操作oracle
  2. 如何在oracle中操作数据
  3. Oracle事务处理
  4. Sql函数的使用

期望目标：

  1. 掌握oracle表对数据操作技巧。
  2. 掌握在java程序中操作oracle。
  3. 理解oracle事务概念。
  4. 掌握oracle各种sql函数。

Java连接oracle

前面我们一直在pl/sql中操作oracle，那么如何在java程序中操作数据库呢？

下面我们举例说明：分页显示emp表的用户信息

Testoracle.jsp

<%@ page language="java"import="java.util.*,java.sql.*"pageEncoding="utf-8"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>oracle分页案例</title>

<meta http-equiv="pragma"content="no-cache">

<meta http-equiv="cache-control"content="no-cache">

</head>

<body>

<h2>oracle分页案例</h2>

<table>

<tr>

<td>雇员名</td>

<td>薪水</td>

</tr>

<%

//查询总页数

intpageCount = 0;

//总共有几条记录

introwCount = 0;

//每页显示几条记录

intpageSize = 3;

//接收当前的页数

String strPageNow = request.getParameter("pageNow");

intpageNow = 1;

if(strPageNow!=null)

{

pageNow = Integer.valueOf(strPageNow);

}

Connection conn =null;

Statement stmt =null;

try

{

//加载驱动

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

//得到连接

conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");

stmt = conn.createStatement();

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

while(rs.next())

{

rowCount = rs.getInt(1);

if(rowCount%pageSize == 0)

{

pageCount = rowCount/pageSize;

}

else

{

pageCount = rowCount/pageSize + 1;

}

}

rs = stmt.executeQuery("select * from (select t.*,rownum rn from (select * from emp) t where rownum <=" + pageNow*pageSize + ") where rn >="+((pageNow-1)*pageSize + 1)+"");

while(rs.next())

{

out.print("<tr>");

out.print("<td>"+rs.getString(2)+"</td>");

out.print("<td>"+rs.getInt(6)+"</td>");

out.print("</tr>");

}

out.print("<tr>");

for(inti =1 ; i<=pageCount; i++ )

{

out.print("<a href='testoracle.jsp?pageNow="+i+"'>["+i+"]</a>");

}

out.print("</tr>");

}

catch(Exception e)

{

thrownewRuntimeException(e);

}

finally

{

try

{

if(stmt !=null)

{

stmt.close();

}

if(conn !=null)

{

conn.close();

}

}

catch(Exception ex)

{

thrownewRuntimeException(ex);

}

}

%>

</table>

</body>

</html>

在oracle中操作数据

使用特定格式插入日期值--使用to_date()函数

请思考：如何插入列带有日期的表，并按照年-月-日的格式插入？

insertintoemp
values
(7950,'ZHANGSAN','MANAGER','7782',
to_date('1988-11-11','yyyy-mm-dd'),
1200.00,50.00,10);

insertintoemp
values
(7951,'ZHANGSAN','MANAGER','7782',
to_date('1988/11/11','yyyy/mm/dd'),
1200.00,50.00,10);

使用子查询插入数据

当使用values子句时，一次只能插入一行数据，当使用子查询插入数据时，一条insert语句可以插入大量的数据，当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据。

createtablemyemp
(
myIdnumber(4),
myNamevarchar2(50),
myDeptnumber(5)
);

insertintomyemp(myId,myname,mydept)
selectempno,ename,deptnofromemp
wheredeptno = 10;

select*frommyemp;

使用子查询更新数据

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

如：希望员工scott的岗位、工资、补助与smith员工一样

updateempset(job,sal,comm)
= (selectjob,sal,commfromempwhereename='SMITH')
whereename = 'SCOTT';

Oracle中事务处理

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。

如：网上转帐就是典型的要用事务处理，用以保证数据的一致性。

事务和锁

当执行事务操作时（dml语句），oracle会在被作用的表上加锁，防止其它用户改表的结构，这里对我们用户来讲是非常重要的。

提交事务

当执行commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁，当使用commit语句结束事务后，其它会话将可以查看到事务变化后的新数据。

回退事务

在介绍回退事务前，我们先介绍一下保存点（savepoint）的概念和作用，保存点是事务中的一点，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。当执行rollback时，通过指定保存点可以回退指定的点。

savepointa1;

deletefromempwhereempno = 7900;

savepointa2;

deletefromempwhereempno = 7788;

rollbacktoa2;

rollbacktoa1;

事务的几个重要操作

a.设置保存点

svepoint a;

b.取消部分事务

Rollback to a;

c.取消全部事务

Rollback;

在java程序中如何使用事务

在java操作数据库时，为了保证数据的一致性，比如转帐操作

1） 在一个帐户减掉10元

2） 在另一个帐户加上10元

我们看看如何使用事务？

packagecom.anllin.jdbc.oracle;

importjava.sql.*;

publicclassTestTransation

{

publicstaticvoidmain(String[] args)

{

Connection conn =null;

Statement stmt =null;

try

{

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

conn = DriverManager.getConnection(

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

//加入事务处理

conn.setAutoCommit(false);

stmt = conn.createStatement();

stmt.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");

//制造异常

inti = 7/0;

stmt.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");

//提交事务

conn.commit();

}

catch(Exception e)

{

try

{

//回滚事务

conn.rollback();

}

catch(SQLException e1)

{

thrownewRuntimeException(e1);

}

thrownewRuntimeException(e);

}

finally

{

if(stmt !=null)

{

try

{

stmt.close();

}

catch(SQLException e)

{

thrownewRuntimeException(e);

}

}

if(conn !=null)

{

try

{

conn.close();

}

catch(SQLException e)

{

thrownewRuntimeException(e);

}

}

}

}

}

只读事务

只读事务是指只允许执行查询的操作，而不允许执行其它dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据，

假定机票代售点每天18点开始统计今天的销售情况，这时可以使用只读事务，在设置了只读事务后，尽管其它会话可能会提交新的事务，但是只读事务将不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。

设置只读事务

settransactionreadonly;

用system用户登录，然后执行以下操作：

settransactionreadonly;

再另外起一个客户端，用scott用户登录，执行以下操作：

insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8000,'LISI','CLERK',7902,to_date('1983-05-16','yyyy-mm-dd'),1200.00,50.00,10);

select*fromemp;--可以看到新增的数据

此时，再用system用户操作

select*fromscott.emp;--将看不到新增的数据

Sql函数的使用

字符函数

介绍：

字符函数是oralce中最常用的函数，我们来看看有哪些字符函数：

Lower(char):　将字符串转化为小写的格式

Upper(char):　将字符串转化为大写的格式

Length(char):　返回字符串的长度

Substr(char,m,n):　取字符串的子串，m表示截取起始位置，n表示截取的长度

将所有员工的名字按小写的方式显示

selectlower(ename),sal,jobfromemp;

将所有员工的名字按大写的方式显示

selectupper(ename),sal,jobfromemp;

显示正好为5个字符的员工的姓名

select*fromempwherelength(ename) = 5;

显示所有员工姓名的前三个字符。

selectsubstr(ename,1,3)fromemp;

以首字母大写的方式显示所有员工的姓名

selectupper(substr(ename,1,1))
||
lower(substr(ename,2,length(ename)-1))
fromemp;

以首字母小写的方式显示所有员工的姓名。

selectlower(substr(ename,1,1))
||
upper(substr(ename,2,length(ename)-1))
fromemp;

Replace(char1,search_string,replace_string)：将字符中某段字符串替换成所需的字符串

Instr(char1,char2,[n[,m]])：取子串在字符串的位置

显示所有员工的姓名，用a替换所有”A”

selectreplace(ename,'A','a')fromemp;

数学函数

介绍：

数学函数的输入参数和返回值的数据类型都是数字类型的，数学函数包括

Abs(n)　返回数字n的绝对值。

Cos(n)　返回数字的余弦值。

Cosh(n)返回数字的双曲余弦值

exp(n)返回数字e的n次幂

power(m,n)返回m的n次幂

ln(n)返回e为底的n的对数

log(m,n)返回m为底的n的对数

sin(n)返回数字的正弦值

sinh(n)返回数字的双曲正弦值

sqrt(n)返回数字的平方根

tan(n)返回数字的正切值

tanh(n)返回数字的双曲正切值

acos(n)返回数字的反余弦值

asin(n)返回数字的反正弦值

atan(n)返回数字的反正切值

我们最常用的有：

Round(n,[m])：该函数用于执行四舍五入。如果省掉m，则四舍五入到整数;如果m是正数，则四舍五入到小数点的m位后;如果m是负数，则四舍五入到小数点的m位前。

Trunk(n,[m])：该函数用于截取数字。如果省掉m，就截去小数部分，如果m是正数，就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位。

Mod(m,n)：取模

Floor(n)：返回小于或等于n的最大整数

Ceil(n)：返回大于或等于n的最小整数

对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果。

案例：

显示在一个月为30天的情况所有员工的薪金，忽略余数。

selecttrunc(sal/30),enamefromemp;

output:

TRUNC(SAL/30) ENAME

------------- ----------

40 LISI

43 zhangsan

51 wangwu

49 zhaoliu

30 SMITH

53 ALLEN

41 WARD

99 JONES

41 MARTIN

95 BLAKE

81 CLARK

20 SCOTT

166 KING

50 TURNER

36 ADAMS

31 JAMES

100 FORD

43 MILLER

示例：

insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8001,'zhangsan','CLERK',7902,to_date('1983-06-18','yyyy-mm-dd'),1300.34,50.52,10);
insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8002,'wangwu','CLERK',7902,to_date('1983-09-22','yyyy-mm-dd'),1530.69,50.14,10);
insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8003,'zhaoliu','CLERK',7902,to_date('1983-10-20','yyyy-mm-dd'),1480.52,50.73,10);

selectround(sal),salfromemp

Output:

ROUND(SAL) SAL

---------- ---------

1200 1200.00

1300 1300.34

1531 1530.69

1481 1480.52

selectround(sal,1),salfromemp;

output:

ROUND(SAL,1) SAL

------------ ---------

1200 1200.00

1300.3 1300.34

1530.7 1530.69

1480.5 1480.52

selecttrunc(sal),salfromemp;

output:

TRUNC(SAL) SAL

---------- ---------

1200 1200.00

1300 1300.34

1530 1530.69

1480 1480.52

selecttrunc(sal,1),salfromemp;

output:

TRUNC(SAL,1) SAL

------------ ---------

1200 1200.00

1300.3 1300.34

1530.6 1530.69

1480.5 1480.52

selectfloor(sal),salfromemp;

output:

FLOOR(SAL) SAL

---------- ---------

1200 1200.00

1300 1300.34

1530 1530.69

1480 1480.52

selectceil(sal),salfromemp;

output:

CEIL(SAL) SAL

---------- ---------

1200 1200.00

1301 1300.34

1531 1530.69

1481 1480.52

selectmod(10,3)fromdual;

output:

MOD(10,3)

----------

1

日期函数

介绍：

日期函数用于处理date类型的数据。

默认情况下日期格式是dd-mon-yy 即12-7月-78

1)sysdate：该函数返回系统时间

2)add_months(d,n)：给指定日期增加n 个月份

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

查找已经入职8个月多的员工

select*fromempwheresysdate> add_months(hiredate,8);

显示满10年服务年限的员工的姓名和受雇日期。

select*fromempwheresysdate>= add_months(hiredate,12*10);

对于每个员工，显示其加入公司的天数。

selecttrunc(sysdate-hiredate) days,enamefromemp;

找出各月倒数第3天受雇的所有员工。

selecthiredate,last_day(hiredate),enamefromemp
wherelast_day(hiredate)-2 = hiredate;

转换函数

转换函数用于将数据类型从一种转为另外一种，在某些情况下，oracle server 允许值的数据类型和实际的不一样，这时oracle server会隐含的转化数据。

比如：

createtablet1(idint);
insertintot1values('10');--这里oracle会自动将'10'->10

createtablet2(idvarchar2(10));
insertintot2values(1);--这里oracle会自动将1->'1'

我们要说的尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数。

To_char()

你可以使用

selectename,hiredate,salfromempwheredeptno=10;

显示信息，可是，在某些情况下，这个并不能满足你的需求.

格式

描述

示例

yy

两位数字的年份

2004à04

yyyy

四位数字的年份

2004年à2004

mm

两位数字的月份

8月à08

dd

2位数字的天

30号à30

hh24

小时用24小时制

8点à20

hh12

小时用12小时制

8点à08

mi

分钟

15

ss

秒

50

格式

描述

9

显示数字，并忽略前面0

0

显示数字，如位数不足，则用0补齐

.

在指定位置显示小数点

,

在指定位置显示逗号

$

在数字前加美元符

L

在数字前加本地货币符号

C

在数字前加国际货币符号

G

在指定位置显示组分隔符、

D

在指定位置显示小数点符号.

如：日期是否可以显示时、分、秒

insertintoemp
values(8004,'Test','MANAGER',7782,sysdate,1360.52,100,20);

selectename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')
fromemp;

Output:

Test 2012-02-09 20:09:45

薪水是否可以显示指定的货币符号。

selectename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),
to_char(sal,'L99999.99')
fromemp;
output:

ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L99999.99')

---------- ------------------------------ ------------------------

Test 2012-02-09 20:09:45 ￥1360.52

LISI 1983-05-16 00:00:00 ￥1200.00


selectename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),
to_char(sal,'L99,999.99')
fromemp;

output:

ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L99,999.99')

---------- ------------------------------ -------------------------

Test 2012-02-09 20:09:45 ￥1,360.52

LISI 1983-05-16 00:00:00 ￥1,200.00

显示1980年入职的所有员工

select*fromempwhereto_char(hiredate,'yyyy') = 1980;

output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980-12-17 900.00 20

显示所有12月份入职的员工

select*fromempwhereto_char(hiredate,'mm') = 12;

output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980-12-17 900.00 20

7900 JAMES CLERK 7698 1981-12-03 950.00 30

7902 FORD ANALYST 7566 1981-12-03 3000.00 20

To_date()

用于将字符串转换成date类型的数据。

能否按照中国人习惯的方式年-月-日添加日期。

insertintoemp
values(8005,'ChenWen','ANALYST',7566,to_date('1986-9-22','yyyy-mm-dd'),1850.52,100,20);

系统函数

1）terminal: 当前会话客户所对应的终端的标识符。

2）language: 语言。

3）db_name: 当前数据库名称。

4）nls_date_format: 当前会话客户所对应的日期格式。

5)　session_user: 当前会话客户所对应的数据库用户名。

6）current_schema: 当前会话客户所对应的默认方案名。

7）host:返回数据库所在主机的名称。

通过该函数，可以查询一些重要信息，

比如你正在使用哪个数据库？

selectsys_context('userenv','db_name')asdbnamefromdual;

output:

DBNAME

--------------------------------------------------------------------------------

Orcl

select
sys_context('userenv','terminal')asdbterminal,
sys_context('userenv','language')asdblanguage,
sys_context('userenv','session_user')assessionuser,
sys_context('userenv','current_schema')ascurrentschema,
sys_context('userenv','host')asdbhost,
sys_context('userenv','nls_date_format')asdateformat,
sys_context('userenv','db_name')asdbname
fromdual;

output:

DBTERMINAL DBLANGUAGE SESSIONUSER CURRENTSCHEMA DBHOST DATEFORMAT DBNAME

--------------------------------------------------------------

2011-20030430JR SIMPLIFIED-CHINESE_CHINA.AL32UTF8 SCOTT SCOTT WORKGROUP\2011-20030430JR DD-MON-RR orcl

分享到：
| oracle实战第二天--基本查询与复杂查询
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    精通Oracle核心技术和项目实战之Oracle安装配置

    精通oracle核心技术与项目实战共分4 篇。...第3 篇介绍oracle 编程基础、游标、存储过程、函数、触发器、序列、事务和锁等数据库技术；第4 篇介绍表空间管理、数据库安全管理、备份与恢复、控制文件和日志文件等技术。

    亮剑.NET深入体验与实战精要3

    第3章 ASP．NET开发大杂烩 113 3.1 页面生命周期 114 3.1.1 独立页面生命周期事件顺序 114 3.1.2 具有Master页的生命周期事件顺序 116 3.1.3 ASP．NET生命周期详解 118 3.2 页面状态管理 120 3.2.1 Cookie 121 3.2.2...

    李兴华 java_web开发实战经典 源码 完整版收集共享

    第3章 XML简介 3.1、认识XML 3.2、XML解析 3.2.1、DOM解析操作 3.2.2、SAX解析操作 3.2.3、XML解析的好帮手：JDOM 3.2.4、最出色的解析工具：DOM4J 3.3、使用JavaScript操作DOM 3.4、开发实战讲解（基于...

    李兴华 Java Web 开发实战经典_带源码_高清pdf 带书签 下

    第3章 XML简介 3.1、认识XML 3.2、XML解析 3.2.1、DOM解析操作 3.2.2、SAX解析操作 3.2.3、XML解析的好帮手：JDOM 3.2.4、最出色的解析工具：DOM4J 3.3、使用JavaScript操作DOM 3.4、开发实战讲解（基于...

    李兴华Java Web开发实战经典.pdf (高清版) Part1

    5.2.3、第三种Scriptlet： 5.3、Scriptlet标签 5.4、page指令 5.4.1、设置页面的MIME 5.4.2、设置文件编码 5.4.3、错误页的设置 5.4.4、数据库连接操作 5.5、包含指令 5.5.1、静态包含 5.5.2、动态包含 ...

    李兴华Java Web开发实战经典(高清版) Part2

    5.2.3、第三种Scriptlet： 5.3、Scriptlet标签 5.4、page指令 5.4.1、设置页面的MIME 5.4.2、设置文件编码 5.4.3、错误页的设置 5.4.4、数据库连接操作 5.5、包含指令 5.5.1、静态包含 5.5.2、动态包含 ...

    李兴华 Java Web 开发实战经典_带源码_高清pdf 带书签 上

    第3章 XML简介 3.1、认识XML 3.2、XML解析 3.2.1、DOM解析操作 3.2.2、SAX解析操作 3.2.3、XML解析的好帮手：JDOM 3.2.4、最出色的解析工具：DOM4J 3.3、使用JavaScript操作DOM 3.4、开发实战讲解（基于...

    PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part1

    第3章 Web页面交互 178 3.1 获取表单元素的值 179 实例149 设计论坛登录界面 179 实例150 可以上传图片的表单 180 实例151 通过POST方法提交表单元素 182 实例152 通过$_POST方法获取表单元素 182 实例153 通过GET...

    李兴华 Java Web 开发实战经典 高清扫描版Part3

    第3章 XML简介 3.1、认识XML 3.2、XML解析 3.2.1、DOM解析操作 3.2.2、SAX解析操作 3.2.3、XML解析的好帮手：JDOM 3.2.4、最出色的解析工具：DOM4J 3.3、使用JavaScript操作DOM 3.4、开发实战讲解（基于...

    亮剑.NET深入体验与实战精要2

    第3章 ASP．NET开发大杂烩 113 3.1 页面生命周期 114 3.1.1 独立页面生命周期事件顺序 114 3.1.2 具有Master页的生命周期事件顺序 116 3.1.3 ASP．NET生命周期详解 118 3.2 页面状态管理 120 3.2.1 Cookie 121 3.2.2...

    MLDN+李兴华+Java+Web开发实战经典.part3.rar )

    5.2.3、第三种Scriptlet： 5.3、Scriptlet标签 5.4、page指令 5.4.1、设置页面的MIME 5.4.2、设置文件编码 5.4.3、错误页的设置 5.4.4、数据库连接操作 5.5、包含指令 5.5.1、静态包含 5.5.2、动态包含 ...

    Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part3

    第3章 数组与集合的应用 51 3.1 数组演练 52 实例042 获取一维数组最小值 52 实例043 将二维数组中的行列互换 53 实例044 利用数组随机抽取幸运观众 54 实例045 用数组设置JTable表格的列名与列宽 55 3.2 数组操作 ...

    PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part2

    第3章 Web页面交互 178 3.1 获取表单元素的值 179 实例149 设计论坛登录界面 179 实例150 可以上传图片的表单 180 实例151 通过POST方法提交表单元素 182 实例152 通过$_POST方法获取表单元素 182 实例153 通过GET...

    java web 视频、电子书、源码（李兴华老师出版）

    第3章 XML简介 3.1、认识XML 3.2、XML解析 3.2.1、DOM解析操作 3.2.2、SAX解析操作 3.2.3、XML解析的好帮手：JDOM 3.2.4、最出色的解析工具：DOM4J 3.3、使用JavaScript操作DOM 3.4、开发实战讲解（基于...

Magicbox
Global site tag (gtag.js) - Google Analytics