Oracle-下

tobing
理论学习

发布于：Jan 22, 2020
次浏览

Oracle数据库第二弹。

Oracle-下

1. 视图

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

  1. 数据准备

    1
    2
    -- 查询语句创建表
    create table emp as select * from scott.emp;

  2. 视图操作

    1
    2
    3
    4
    5
    6
    7
    8
    -- 创建视图【必须要dba权限】
    create view v_emp as select ename,job from emp;
    -- 查询视图
    select * from v_emp;
    -- 修改视图【不推荐】
    udpate v_emp set job='LER' where ename='ALLEN';
    -- 创建只读视图【一般设置为只读】
    create view v_emp1 as select ename,job from emp with read only;

    视图的作用：

    1. 视图可以屏蔽一些敏感字段。

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

2. 索引

索引就是在表的列上构建一个二叉树，从而达到大大提高查询效率的目的。

但是索引会影响增删改的效率。

  1. 单列索引

    1
    2
    -- 创建单列索引
    create index idx_ename on emp(ename);

    单列索引触发规则，条件必须是索引列中的原始值。

    单列函数，模糊查询，都会影响索引的触发

  2. 复合索引

    1
    2
    -- 创建复合索引
    create index idx_enamejob on emp(ename,job);

    复合索引中第一列为优先检索列。

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

    1
    2
    3
    select * from emp where ename = 'SCOTT' and job='xx';	-- 触发复合索引
    select * from emp where job = 'xx';						-- 不触发索引
    select * from emp where ename = "SCOTT";				-- 触发单列索引

3. pl/sql编程语言

是对sql语言的扩展，使得sql语言具有过程化编程的特性。

比一般过程编程语言，更加灵活高效。

主要用来编写存储过程和存储函数等。

  1. 声明方法

    赋值操作可以使用**:=**，也可以使用into查询语句赋值。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    declare
    	-- 变量声明
    	i number(2) := 10;
    	s varchar(10) :='tobing';
    	ena emp.ename%type;	--引用型变量
    	emprow emp%rowtype;	--记录型变量
    begin
    	dbms_output.put_line(i);
    	dbms_output.put_line(s);
    	-- 应用型变量赋值
    	select ename into ena from emp where empno = 7788;
    	-- 记录型变量赋值
    	select * into emprow from emp where empno = 7788;
    	dbms_output.put_line(emprow.ename||'工作为：'||emprow.job);
    end;

  2. if判断

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 输入小于18的数字，输入‘less than 18’
    -- 输入大于18小于40的数字，输出‘18·40’
    -- 输入大于40的数字，输出‘older than 40’
    declare
    	i number(3) :=ⅈ
    begin
    	if i<18 then
    		dbms_output.put_line('less than 18');
    	elsif i<40 then
    		dbms_output.put_line('18-40');
    	else
    		dbms_output.put_line('older than 40');
    	end if;
    end;

  3. while循环

    循环输出1-10数值

    1
    2
    3
    4
    5
    6
    7
    8
    declare
    	i number(1) := 1;
    begin
    	while i<11 loop
    		dbms_output.put_line(i);
    		i:=i+1;
    	end loop;
    end;

  4. exit循环【重点掌握】

    1
    2
    3
    4
    5
    6
    7
    8
    9
    declare
    	i number(1) :=1;
    begin
    	loop
    	exit when i>10;
    		dbms_output.put_line(i);
    		i:=i+1;
    	end loop
    end;

  5. for循环

    1
    2
    3
    4
    5
    6
    7
    declare
    	
    begin
    	for i in 1..10 loop
    		dbms_output.put_line(i);
    	end loop;
    end;

  6. 游标

    可以存放多个对象，多行记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    -- 输出emp表中所有员工姓名
    declare
    	cursor c1 is select * from emp; -- 存储emp表所有记录
    	emprow emp%rowtype;		-- 存储一行的变量
    begin
    	open c1;
    		loop
    			fetch c1 into emprow;		-- 取出c1中每行记录，存储到emprow中
    			exit when c1%nutfound;		-- 结束标志
    			dbms_output.put_line(emprow.ename);	-- 输出
    		end loop;
        close c1;
    end;


    -- 给指定部门涨工资
    declare
    	-- 获取指定部门的所有empno
    	cursor c2(eno emp.deptno%type) is select empno from emp where deptno = eno;
    	-- 用于存储每次获取到的empno
    	en emp.empno%type;
    begin
    	open c2(10);	-- 获取10部门的所有empno
            loop
                fetch c2 into en;	-- 循环取出10部门的empno
                exit when c2%notfound;	-- 循环结束标志
                update emp set sal=sal+10 where cempno=en;	-- 通过取出的empno进行操作
            end loop;
        close c2;
    end;

4. 存储过程

存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端，可以直接调用。

这一段pl/sql一般都是固定步骤的业务。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--给指定员工涨100块
--1.定义存储过程
create or replace procedure p1(eno emp.empno%emp)
is
begin
	update emp set sal=sal+100 where empno = eno;
	commit
end;

-- 2.使用存储过程
declare
begin
	p1(7788);	-- 调用存储过程，相当于给empno为7788的员工工资加100
end;

5. 存储函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--通过存储函数实现计算指定员工的年薪
--1.定义存储函数
create or replace function f_yearsal(eno emp.emono%type) return number
is 
	s number(10);
begin
	--将查询到的年薪记录在s
	select sal*12+nvl(comm,0) into s from emp where empno=eno;
	--返回s
	return s;
end;
--2.使用存储函数
declare 
 s number(10);
begin
	s:=f_yearsal(7788);
	dbms_output.put_line(s);
end;

存储过程和存储函数的参数都不能带长度。

存储函数的返回值不能带长度。

  1. 存储过程和存储函数的区别
    1. 语法区别：关键字不一样，存储函数比存储过程多了两个return
    2. 本质区别：
      • 存储函数有返回值，而存储过程没有返回值。
      • 如果存储过程想实现返回值的业务，必须要使用out类型的参数
      • 即使是存储过程使用了out类型的参数，其本质也不是真的有了返回值
      • 而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出参数类型的值。

可以利用存储函数的特性，封装为一个函数。提供查询语句使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 需求：查询员工姓名及其对应部门
-- 1.常规方法
select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno;
-- 2. 定义函数+查询
-- 2.1定义存储函数：根据deptno查询dname
create or replace function f_dna(eno emp.empno%type) return dept.dname%type
is
	dna dept.dname%type;	-- 存储查询到的dname
begin
	-- 查询dname，并返回
	select dname into dna from dept where deptno = eno;
	return dna;
end;
-- 2.2调用
select e.ename ,f_dna(e.deptno)
from emp e;

6. out类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--使用存储过程来算年薪
--1.定义存储过程
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;
--2.使用存储过程
declare
	yearsal number(10);
begin
	p_yearsal(7788,yearsal);
	dbms_output.put_line(yearsal);
end;

in和out类型参数的区别是什么？

  • 凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。

7. 触发器

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

  1. 分类

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

    行级触发器：包含了for each row的就是行级触发器。

    加for each row 是为了使用:old或者:new对象或者一行记录

  2. 语句级触发器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    ---需求：插入一条记录，输出一个“新员工入职”
    ---分析：insert、操作之后、监听的表

    --创建触发器
    create or replace trigger t1
    after 
    insert
    on person
    declare

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

    --触发t1
    insert into person value(10,'zenyet');
    commit

  3. 行级触发器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    ---需求：不能给员工降薪
    ---分析：update、操作之前、监听表

    --创建触发器
    create or replace trigger t2
    before
    update
    on person
    for each row
    declare

    begin
    	if :old.sal>:new.sal then
    		--抛出异常阻止执行
    		raise_application_error(-20001,'不能给员工降薪');
    	end if;
    end;

    --触发t2
    update emp set sal=sal-1 where empno=7788;
    commit;

    raise_application_error(-20001~-20999,’错误提示信息’);

  4. 触发器应用

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    ---需求：实现主键自增长
    ---分析：insert、插入之后、监听表

    --创建触发器
    create or replace triggle t3
    before
    insert
    on person
    for each row
    declare

    begin
    	select s_person.nextval into:new.pid from dual;
    end;


    --触发t3
    insert into emp(ename) values('zzz');
    commit;

8. Java操作Oracle

Maven环境下使用Java连接Oracle执行SQL语句。

  1. 普通SQL语句

    pom.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    <dependencies>
        <!--连接oracle-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    测试方法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    public class OJdbcTest {

        /**
         * 测试直接执行SQL语句
         * @throws Exception
         */
        @Test
        public void ojdbcCallTrodiction() throws Exception {
            //1.注册驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //2.获取连接
            Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "tobing", "tobing");
            //3.获取预处理对象
            PreparedStatement pstmt = connection.prepareStatement("select * from emp where empno=?");
            //4.设置参数
            pstmt.setObject(1,7788);
            //5.执行查询
            ResultSet rs = pstmt.executeQuery();
            //6.遍历结果集
            while(rs.next()){
                String ename = rs.getString("ename");
                System.out.println(ename);
            }
            //7.关闭资源
            rs.close();
            pstmt.close();
            connection.close();
        }
    }

  1. 存储过程

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    /**
         * 调用存储过程
         * @throws Exception
         */
    @Test
    public void ojdbcCallProcedure() throws Exception {
        //1.注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "tobing", "tobing");
        //3.获取预处理对象
        CallableStatement cstat = connection.prepareCall("{ call p2(?,?) }");
        //4.设置参数
        cstat.setObject(1,7788);
        cstat.registerOutParameter(2, OracleTypes.NUMBER);
        //5.执行完毕
        cstat.execute();
        System.out.println("执行完毕");
        System.out.println("年薪为："+cstat.getObject(2));
        //6.关闭资源
        cstat.close();
        connection.close();
    }

  1. 存储函数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    /**
     * 测试调用存储函数
     * @throws Exception
     */
    @Test
    public void ojdbcCallFunction() throws Exception {
        //1.注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "tobing", "tobing");
        //3.获取预处理对象
        CallableStatement cstat = connection.prepareCall("{ ?=call f_yearsal(?) }");
        //4.设置参数
        cstat.setObject(2,7788);
        cstat.registerOutParameter(1, OracleTypes.NUMBER);
        //5.执行完毕
        cstat.execute();
        System.out.println("执行完毕");
        System.out.println("年薪为："+cstat.getObject(1));
        //6.关闭资源
        cstat.close();
        connection.close();
    }

  2. CallableStatment接口

    CallableStatement是PreparedStatement子接口，是用于执行 SQL 存储过程的接口。

    JDBC API 提供了一个存储过程 SQL 转义语法，该语法允许对所有 RDBMS 使用标准方式调用存储过程。此转义语法有一个包含结果参数的形式和一个不包含结果参数的形式。如果使用结果参数，则必须将其注册为 OUT 参数。其他参数可用于输入、输出或同时用于二者。参数是根据编号按顺序引用的，第一个参数的编号是 1。

    {?= call [(,, …)]}
    {call [(,, …)]}

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：https://tobing.top/2020/01/22/Oracle-%E4%B8%8B/

更新于：Jan 15, 2021

Oracle

评论