玩转oracle学习笔记(三)-Oracle操作

发布时间：2020-12-12 15:05:47 所属栏目：百科来源：网络整理

导读：一.Java程序如何操作oracle 1.配odbc数据源，用jdbc_odbc桥连接方式连接数据库注意：odbc只能本地连接，不能远程连接，即java程和Oracle数据库必须在同一台机器上。控制面板-管理工具-数据源[ODBC]，添加-选择Oracle in OraHome90-完成 Data Source Name:t

一.Java程序如何操作oracle

1.配odbc数据源，用jdbc_odbc桥连接方式连接数据库

注意：odbc只能本地连接，不能远程连接，即java程和Oracle数据库必须在同一台机器上。

控制面板->管理工具->数据源[ODBC]，添加->选择Oracle in OraHome90->完成

Data Source Name:test

TNS Service Name:选择Oracle的服务

建议点击Test Connection弹出弹出框，输入用户名、密码测试连接是否OK。

Java代码：

package com.bijian.study;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestOra {

public static void main(String[] args) {
try {
//1.加载驱动
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//2.得到连接
Connection ct = DriverManager.getConnection("jdbc:odbc:test","scott","tigger");
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while(rs.next()) {
//打印用户名
System.out.println("用户名：" + rs.getString(2));
}
//关闭打开的资源
rs.close();
sm.close();
ct.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}

2.jdbc连接oracle

首先需将oracle驱动包加至工程中。

package com.bijian.study;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestOra2 {

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:myora1","tigger");
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while(rs.next()) {
//打印用户名
System.out.println("用户名：" + rs.getString(2));
}
//关闭打开的资源
rs.close();
sm.close();
ct.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}

3.oracle分页案例

分页显示emp表的用户信息

如果Tomcat默认的端口被占用，则打开apache-tomcatconfserver.xml文件，将8080端口改为其它端口即可。

代码如下所示：

<%@ page language="java" import="java.util.*,java.sql.*" contentType="text/html; charset=gbk"
    pageEncoding="gbk"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<h2>oracle分页案例</h2>
<table>
<tr>
<td>用户名</td>
<td>薪水</td>
</tr>
<%
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","tigger");
Statement sm = ct.createStatement();
int pageNow = 1;
//接收pageNow
String s_pageNow = (String)request.getParameter("pageNow");
if(s_pageNow != null) {
pageNow = Integer.parseInt(s_pageNow);
}
//查询总页数
int pageCount = 0;
//记录总条数
int rowCount = 0;
//每页显示几条记录
int pageSize = 3;
ResultSet rs = sm.executeQuery("select count(*) from emp");
if(rs.next()) {
rowCount = rs.getInt(1);
if(rowCount%pageSize==0) {
pageCount = rowCount/pageSize;
}else {
pageCount = rowCount/pageSize + 1;
}
}
rs = sm.executeQuery("select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=" + pageNow*pageSize + ") where rn>=" + ((pageNow-1)* pageSize + 1));
while(rs.next()) {
out.println("<tr>");
//打印用户名
out.println("<td>" + rs.getString(2) + "</td>");
out.println("<td>" + rs.getString(6) + "</td>");
out.println("</tr>");
}
//打印总页数
for(int i=1;i<=pageCount;i++) {
out.print("<a href=MyTest.jsp?pageNow=" + i + ">[" + i + "] </a>");
}
//关闭打开的资源
rs.close();
sm.close();
ct.close();
%>
</table>
</body>
</html>

二.如何在oracle中操作数据

1.使用特定格式插入日期值

使用to_date函数

insert into emp values(9998,’小红’,’MANAGER’,7782,’11-11月-1988’,78.9,55.33,10);
insert into emp values(9997,’小红2’,to_date(’1988-12-12’,’yyyy-mm-dd’),10);
insert into emp values(9996,’小红3’,to_date(’1988/12/12’,’yyyy/mm/dd’),10);

2.使用子查询插入数据

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

create table kkk(myId nuber(4),myname varchar2(50),myDept number(5));
insert into kkk(myId,myname,myDept) select empno,ename,deptno from emp where deptno=10;

3.使用子查询更新数据

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

希望员工scott的岗位、工资、补助与SMITH员工一样。

update emp set job=(select job from emp where ename=’SMITH’),sal=(select sal from emp where ename=’SMITH’),comm.=(select comm. from emp where ename=’SMITH’);

应该这样写：

update emp set (job,sal,comm) = (select job,comm. from emp where ename=’SMITH’) where ename=’SCOTT’;

三.oracle事务处理

1.事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如网上转账就是典型的要用事务来处理，用以保证数据的一致性。

2.当执行事务操作时（dml语句），oracle会在被作用的表上加锁，防止其它用户改表的结构。

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

设置保存点：savepoint a1;
删除操作：delete from emp where empno=9996;
再设置保存点：savepoint a2;
误删除操作：delete from emp where empno=9999;
回滚至a2：rollback to a2;
回滚至a1：rollback to a1;

4.事务的几个重要操作

a.设置保存点：savepint a;

b.取消部分事务：rollback to a;

c.取消全部事务：rollback

5.java程序中如何使用事务

ct.setAutoCommit(false);

ct.commit();

ct.rollback();

6.只读事务

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

设置只读事务：set transaction read only

四.sql函数的使用

1.字符函数

字符函数是oracle中最常用的函数。

lower(char)：将字符串转化为小写的格式
upper(char)：将字符串转化为大写的格式
length(char)：返回字符串的长度
substr(char,m,n)：取字符串的子串
replace(char1,search_string,replace_string)
instr(char1,char2,[,n[,m]])：取子串在字符串的位置

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

select lower(ename),sal from emp;

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

select upper(ename),sal from emp;

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

select * from emp where length(ename)=5;

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

select substr(ename,1,3) from emp;

说明：substr(列名，从第几位取，取多少个)

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

select upper(substr(ename,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

6)以首字线小写的方式显示所有员工的姓名

select lower(substr(ename,length(ename)-1)) from emp;

replace(char1,replace_string)

instr(char1,m]]取子串在字符串的位置

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

select replace(ename,’A’,’a’) from emp;

2.数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round。

round(n,[m])：用于执行四舍五入，如果省掉m，则四舍五入到整数；如果m是正数，则四舍五入到小数点的m位后；如果m是负数，则四舍五入到小数点的m位前。
trunc(n,[m])：用于截取数字，如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位
mod(m,n)
floor(n)：返回小于或是等于n的最大整数
ceil(n)：返回大于或是等于n的最小整数
abs(n)：返回数字n的绝对值
acos(n)：返回数字的反余弦值
asin(n)：返回数字的反正弦值
atan(n)：返回数字的反正切
cos(n)
exp(n)：返回e的n次幂
log(m,n)：返回对数值
power(m,n)：返回m的n次幂

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

select trunc(sal/30),ename from emp;
select floor(sal/30),ename from emp;

2)实例

select round(sal) from emp where ename=’SMITH’;
select round(comm,1),comm from emp where ename=’SMITH’;
select trunc(comm,comm. from emp where ename=’SMITH’;
select trunc(comm),trunk(comm,-1),comm. from emp where ename=’SMITH’;
select floor(comm),comm from emp where ename=’SMITH’;
select ceil(comm),comm from emp where ename=’SMITH’;
select mod(10,2) from dual;--在做oracle测试时，可以用dual(哑原表)

3)select abs(-13) from dual;

3.日期函数

日期函数用于处理date类型的数据，默认情况下日期格式是dd-mon-yy，即12-7月-78

sysdate：返回系统时间select sysdate from dual;

add_months(d,n)

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

1)查询已经入职8个多月的员工

select * from emp where sysdate>add_months(hiredate,8);

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

select * from emp where sysdate>=add_months(hiredate,12*10);

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

select trunc(sysdate-hiredate) “入职天数”,ename from emp;

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

select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

4.转换函数

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

比如：

1)create table t1(id int);

insert into t1 values(‘10’)--这样oracle会自动的将’10’转为10

2)create table t2(id varchar2(10));

insert into t2 (id varchar2(10));--这样oracle就会自动的将1转为’1’

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

3)to_char

可以使用select ename,hiredate,sal from emp where deptnoo=10;显示信息，可是，在某些情况下，这个并不能满足你的需求。

a.日期是否可以显示时分秒？

select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp;

yy：两位数字的年份2004->04
yyyy：四位数字的年份2004年
mm：两位数字的月份8月->08
dd：2位数字的天，30号->30
hh24：8点->20
hh12：8点->08
mi、ss：显示分钟、秒

b. 薪水是否可以显示指定的货币符号？

9：显示数字，并忽略前面0
0：显示数字，如位数不足，则用0补齐
.：在指定位置显示小数点,：在指定位置显示逗号
$：在数字前加美元
L：在数字前加本地货币符号
C：在数字前加国际货币符号
G：在指定位置显示组分隔符
D：在指定位置显示小数点符号(.)

select ename,’yyyy-mm-dd hh24:mi:ss’),to_char(sal,’L99999.99’) from emp;
select ename,’L99,999.99’) from emp;
select ename,$99,999.99’) from emp;

显示1980年入职的所有员工

select * from emp where to_char(hiredate,’yyyy’)=1980;

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

select * from emp where to_char(hiredate,’mm’)=12;

5.系统函数

sys_context

1)terminal：当前会话客户所对应的终端的标识符

2)language：语言

3)db_name：当前数据库名称

4)nls_date_format：当前会话客户所对应的日期格式

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

6)current_schema：当前会话客户所对应的默认方案名

7)host：返回数据库所在主机的名称

通过该函数，可以查询一些重要信息，比如你怎在使用哪个数据库？

select sys_context(‘userenv’,’db_name’) from dual;
select sys_context(‘userenv’,’language’) from dual;
select sys_context(‘userenv’,’session_user’) from dual;
select sys_context(‘userenv’,’current_schema’) from dual;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!