54、Oracle

Oracle以数据存储量大，处理速度快，安全性高，容错性强等出色的特征，长期以来占据着全球数据库市场的主导地位。

 

安装：百度搜索安装教程

 

连接：使用PLSQL Developer对Oracle进行连接

 

体系结构：

数据库：Oracle数据库是数据的物理存储。这就包括数据文件ORA或者DBF，控制文件，联机日志，参数文件。其实Oracle数据库的概念和其他数据库不一样，这里的数据库是一个操作系统只有一个库，可以看作是Oracle就只有一个大数据库

实例：一个Oracle实例有一个系列的后台进程和内存结构组成，一个数据库可以有N个实例

用户：用户是在实例下建立的，不同的实例可以建相同名字的用户。

表空间：表空间是Oracle对物理数据库上相关数据文件的逻辑映射，一个数据库在逻辑上被划分到一个到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构，每个数据库至少有一个表空间

数据文件：数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的。真正是在某一个或多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间，一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

 

创建表空间：

create tablespace demo

datafile'c:\demo.dbf'

size 100m

autoextend on

next 10m;

 

删除表空间

drop tablespace demo；

 

创建用户

create user demoUser

identified by 123456

default tablespace demo;

 

给用户授权

grant dba to demo；

补充：oracle数据库中的常用角色：1、connect，连接角色，基本角色；2、resource，开发者角色；3、dba，超级管理员角色

 

Oracle数据类型

1、Varchar，varchar2，表示一个字符串

2、number，number（n）表示一个整数，长度为n；number（m，n），总长度m，小数n，整数是m-n

3、data，表示日期类型

4、clob，大对象，表示大文本数据类型

5、blob，大对象，表示二进制数据

 

表的管理

1、建立表

create table person（

    id number（20），

    name varchar（10）

）；

 

2、修改表的结构

2.1、添加一列

alter table person add gender number（1）；

2.2、修改列类型

alter table person modify gender char（1）；

2.3、修改列名称

alter table person rename column gender to sex；

2.4、删除列

alter table person drop column sex；

 

3、数据增删改：

3.1、添加一条记录

insert into person （id，name）value （1，‘读者’）;

commit；

3.2、修改一条记录

update person set name=‘新读者’ where id =1 ;

commit;

3.3、删除

delete from person；--删除表中所有记录

drop table person；--删除表结构

truncate table person；--类似deldte，但是效率更高

 

注意：id（主键的值）一般使用序列，默认从1开始，依次递增，主要用来给主键赋值使用

创建一个序列：create sequence s_person；

使用序列：insert into person （id，name）vlaue （s_person.nextval,'读者');

 

scott用户，密码tiger

解锁scott用户：alter user scott account unlock；

解锁scott用户密码：alter user scott identified by tiger；

 

函数：

单行函数：作用于一行，返回一个值

多行函数：作用于多行，返回一个值

 

字符函数：

select upper（‘yes’） from dual：--YES

select lower（‘YES’） from dual；--yes

注意：dual表示虚表，仅用于补全语句

 

数值函数：

select round（12.22，1）from dual；--四舍五入，后面的参数表示保留的小数位数

select trunc（12.22，1）from dual；--直接截取

select mod（10，3） from dual；--求余数

 

日期函数：

select sysdate from dual；--查询现在的时间

select sysdate-e.hiredate from emp e;--相差的天数

select months_between（sysdate，e.hiredate）from emp e;--相差的月数

 

转换函数：

select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;--日期转字符串

select to_date('2018-6-7 16:39:50','fm yyyy-mm-dd hh24:mi:ss') from dual;--字符串转日期

 

通用函数：

select e.sal+nvl(e.comm,0) from emp e;-- 如果e.comm的值为null，则用默认值0

 

条件表达式：

第一种用法：

select e.name

    case e.name

        when‘SMITH’ then ‘史密斯’

            when'ALLEN' then'阿伦'

                else ‘无’ --可省略

                    end

from emp e；

第二种用法：

select e.sal

    case e.sal

        when e.sal>3000 then ‘高收入’

            when e.sal>1500 then ‘中等收入’

                else ‘低收入’

                    end

from emp e；

补充：oracle专用写法

select e.name

    decode（e.name

        'SMITH' , '史密斯',

            'ALLEN','阿伦'）中文名

from emp e；

 

多行函数：

select count（1）from emp；--查询总数量

select sum（sal） from emp；--查询总和

select max（sal） from emp； --查询最大

select min（sal） from emp；--查询最小

select avg（sal） from emp；--查询平均值

 

分组查询：

select e.deptno,avg(e.sal) 

from emp e

where e.sal >800

group by e.deptno

having avg(e.sal) >2000

 

多表查询的概念

笛卡尔积：表1，n条记录，表2，m条记录，结果n*m

等值连接：

select * from emp e ， dept d

where  e.deptno=d.deptno

内连接：

select * from emp e inner join dept d

on e.deptno = d.deptno

外连接：

select * from emp e right join dept d 

on e.deptno = d.deptno；--右外连接，右表所有内容和左表交集内容

select *from emp e left join dept d

on e.deptno = d.deptno;--左外连接，左表所有内容和右边交集内容

 

 补充：oracle中专用外连接

select * from emp e ，dept d

where e.deptno(+) = d.deptno;-- 等同于外连接的第一条语句

 

自连接：就是站在不同的角度把一张表看成多张表

select e1.name ，e2.name

from emp e1, emp e2

where e1.mgr=e2.empno;

 

子查询

子查询返回一个值：

select * from emp where sal =（select sal from emp where id = 1）；

子查询返回一个集合：

select * from emp where sal in（select sal from emp where deptno = 10）；

子查询返回一个表：

select t.deptno,t.msal,e.ename,d.dname

from(select deptno,min(sal) msal from emp group by deptno) t,emp e,dept d where t.deptno = e.deptno and t.msal = r.sal and e.deptno=d.deptno;

 

分页查询：

select * from （

        select rownum rn， e.* from（

                select * from emp order by sal desc

） e where rownum<11

）where rn > 5;

 

视图：就是提供一个查询的窗口，所有的数据来自于原表

补充：通过查询语句创建表；create table emp as select * from scott.emp；--可以跨用户查询

创建视图：create view v_emp as select name，job from emp；

查询视图：select * from v_emp

修改视图：update v_emp set job= ‘CLERK’ where name = ‘ALLEN’ ；commit；

创建只读视图：create view v_emp as select name ，job from emp with read only；

视图的作用：

1、视图可以屏蔽掉一些敏感字段

2、保证总部和分部数据及时统一

 

索引：就是在表的列上构建一个二叉树，达到大幅度提高查询效率的目的，但是索引会影响增删改的效率

单列索引：

创建单列索引：create index idx_name on emp(name);

单列索引触发规则，条件必须索引中的原始值，单行函数和模糊查询不会触发

复合索引：

创建复合索引：create index idx_namejob on emp(name,job);

复合索引中的第一列为优先检索列，如果要触发复合索引，必须包含有优先检索列中的原始值。

 

pl/sql编程语言

pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程语言的特性；比一般的过程化编程语言更加灵活，主要用来编写存储过程和存储函数。

 

声明方法：

declare 

    i number（2）：=10；

    s varchar（10）：=‘小明’；

    ena emp.name%type；

    emprow emp%rowtype;

begin

    dbms_output.put_line(i);

    dbms_output.put_line(s);

    select ename into ena from emp where empno = 7788;

    dbms_output.put_line(ena);

    select * into emprow from emp where empno = 7788;

    dbms_output.put_line(emprow.name || emprow.job);

end；

 

if语句：

declare 

    i number（3）：=&age；

begin

    if i<18 then

        dbms_output.put_line(' 未成年')；

    if i<50 then

        dbms_output.put_line('中年人')；

    else

         dbms_output.put_line('老年人');

    end if;

end;

 

loop语句

 

whlie循环：

declare 

    i number（2） ：=1；

begin

    while i<11 loop

        dbms_output.put_line(i);

        i:=i+1;

    end loop;

end;

 

exit语句：

declare 

    i number（2）：= 1；

begin

    loop

        exit when i>10；

        dbms_output.put_line(i);

        i:=i+1;

     end loop;

end;

 

for语句

declare 

begin   

    for i in 1.. 10 loop

        dbms_output.put_line(i);

    end loop;

end;

 

游标：类似与Java中的集合，可以存放多个对象，多行记录

declare 

    cursor c2 （eno emp.deptno%type）

    is select empno from emp where deptno =eno;

    en emp.empno&type;

begin 

    open c2(10);

    loop

        fetch c2 into en;

        exit when c2%notfound;

        update emp set sal = sal+100 where empno = en;

        commit;

    end loop;

    close c2;

end;

 

存储过程：就是提前编译好的一段pl/sql语言，放置在数据库，可以直接被调用。这段pl/sql一般都是固定步骤的业务。

create or replace procedure p1（eno emp.empno%type）

is

begin

    update emp set sal = sal+100 where empno = eno;

    commit;

end;

 

调用存储过程

declare

begin

    p1(7788);

end;

 

存储函数：

create or replace function f_yearsal （eno emp.empno%type）return number

is

    s number(10);

begin

    select sal*12+nvl(comm,0) into s from emp where empno   = eno;

    return s;

end;

 

调用存储函数

declare 

    s number(10);

begin

    s:=f_yearsal(7788);

    dbms_output.put_line(s);

end;

 

out类型

create or replace procedure p_yearsal （eno emp.empno%type,yearsal out number）

is

    s number(10);

    c emp.comm%type;

begin

    select sal*12,nvl(comm,0) into s,c from emp where empno = eno;

    yearsal:=s+c;

end;

 

调用：

declare

    yearsal number(10);

begin

    p_yearsal(7788,yearsal);

end;

 

触发器：就是制定一个规则，在我们做增删改操作的时候，只要满足改规则，自动触发，无需调用。

语句级触发器：不包含for each row的触发器

行级触发器：包含for each row的触发器

加入for each row 是为了使用：old 和：new

语句级触发器代码：

create or replace trigger t1 

after 

insert 

on person

declare

begin

    dbms_output.put_line('一个新员工入职');

end;

行级触发器代码：

create or replace trigger t2

before

update

on emp

for each row

declare

begin

    if ：old.sal>:new.sal then

        reise_application_error(-20001,'不能降低工资');

    end if;

end;

 

java调用：

导入jar包，oracle10g对应jar包ojdba14.jar，oracle11g对应jar包ojdbc6.jar

@Test
public class testOracle()throws Exception(){
Class.forName("oracle.jdbc.driver.OraceDriver");
Connection    connection=DriverManager.getConnection("jdbc:oracle:localhost:1521:orcl","user","123456");
PreparedStatement pstm = connection.preparedStatement("select * from emp where = ?");
pstm = setObject(1,7788);
ResultSet rs = pstm.executeQuery();
while(rs.next()){
System.out.println("rs.getString(name)");
}

rs.close();
pst.close();
connection.close();

}

java调用存储过程和存储函数：

使用CallableStatement，进行调用。

@Test
public class testOracle()throws Exception(){
Class.forName("oracle.jdbc.driver.OraceDriver");
Connection    connection=DriverManager.getConnection("jdbc:oracle:localhost:1521:orcl","user","123456");
CallableStatement pstm = connection.prepareCall("{call p_yearsal(?,?)}");
pstm.setObject(1,7788);
pstm.registerOutParameter(2,OracleTypes.NUMBER);
pstm.execute();

System.out.println(pstm.getObject(2));


pst.close();
connection.close();

}

 

 

转载于:https://my.oschina.net/u/4131739/blog/3083173