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

「爱情、让人受尽委屈。」 2022-04-06 01:52 522阅读 0赞

**一.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","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();
    }
    }
    }

**3.oracle分页案例**

分页显示emp表的用户信息

如果Tomcat默认的端口被占用，则打开apache-tomcat\\conf\\server.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","scott","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’,’MANAGER’,7782,to_date(’1988-12-12’,’yyyy-mm-dd’),78.9,55.33,10);
    insert into emp values(9996,’小红3’,’MANAGER’,7782,to_date(’1988/12/12’,’yyyy/mm/dd’),78.9,55.33,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,sal,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,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

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

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

replace(char1,search\_string,replace\_string)

instr(char1,char2,\[,n\[,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,1),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,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’),to_char(sal,’L99999.99’) from emp;
    select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’),to_char(sal,’L99,999.99’) from emp;
    select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’),to_char(sal,$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;