Oracle就业课第六课之游标和触发器

举报
tea_year 发表于 2021/12/23 00:19:41 2021/12/23
【摘要】 oracle 游标和触发器 回顾 表空间 表空间:逻辑名词:表、视图、索引;拆分:段、区 、块(最小的存储单元) 物理文件的构成: ​ 1、tcl文件 ​ 2、log文件 ​ 3、dbf文件...

oracle 游标和触发器

回顾

表空间

表空间:逻辑名词:表、视图、索引;拆分:段、区 、块(最小的存储单元)

物理文件的构成:

​ 1、tcl文件

​ 2、log文件

​ 3、dbf文件(数据文件)

一个或者多个表空间----------->一个物理文件(dbf文件)

1、创建表空间和用户

1、创建表空间(切换成管理员)

2、创建用户 关联表空间

3、授权

示例:

--表空间: 数据表空间
create tablespace tbs_331
datafile 'd:/oracledata/tbs_331.dbf' --数据文件
size 50m; --初始大小

--临时表空间
create temporary tablespace tbs_331_tmp
tempfile 'd:/oracledata/tbs_331_tmp.dbf'
size 20m;


--创建用户
create user u331 identified by 123456 
default tablespace tbs_331 --默认表空间
temporary tablespace tbs_331_tmp; --默认临时表空间


--授权
--内置的角色:
--connect:  连接,能够登陆系统,具备最基本的权限;游客
--resource: 资源;查看数据库对象,增删改查数据;正式的用户使用
--dba:管理员 

grant connect,resource to u331;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

2、导出(备份)和导入(还原)

2.1 需要准备虚拟目录:

--创建一个虚拟目录:映射到指定的物理路径上
create directory dir331 as 'd:/oracledata';

--授予u331用户操作dir331的权限:read,write
grant read,write on directory dir331 to u331;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5

2.2 导出:expdp

(数据泵程序)

注意:在cmd窗口执行

expdp u331/123456 schemas=u331 dumpfile=u331.dmp logfile=u331.log directory=dir331

  
 
  • 1
  • u331/123456:用户名和密码
  • schemas=u331:模式,默认情况下模式=用户;
  • dumpfile=u331.dmp:导出的文件名
  • logfile=u331.log:日志文件
  • directory=dir331:虚拟目录:dir331;默认导出的数据文件都在虚拟目录对应的物理路径中;

导入:impdp

impdp u331/123456 schemas=u331 dumpfile=u331.dmp logfile=u331.log directory=dir331

  
 
  • 1

实验:

1、使用新创建的用户登录创建好的表空间,创建一个数据表并录入一些数据供测试

2、导出

3、删除表

4、再次导入,查看数据是否还原

3、删除用户和表空间:

-- 删除用户
drop user u331 cascade; --级联删除

--删除表空间 (包括内容和数据文件)
drop tablespace tbs_331 including contents and datafiles;
drop  tablespace tbs_331_tmp including contents and datafiles;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

如果忘掉管理员密码:

sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 9月 21 15:30:44 2020

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn / as sysdba; 
已连接。
SQL> alter user sys identified by sys;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

学习目标

1、游标

2、触发器

学习内容

1、游标

游标:cursor ;指针;

以往对数据表中数据进行的各种数据操作,本质上都是对列进行操作的。无法对每一行数据进行处理。

游标:将查询到的结果暂时的存放到上下文的空间中(内存区),可以对数据进行逐行定位操作。

游标的分类:

1、显式游标

​ 有名字,定义游标,打开游标…

2、隐式游标

​ DML命令(insert,update,delete) 和select … into 命令

游标的属性

用法:显式游标名%属性名 或者:SQL%属性名

​ %NOTFOUND:如果没有读取到数据,返回true。否则返回false

​ %ROWCOUNT:实际读取到的行数

​ %ISOPEN:游标是否打开,true/false

​ %FOUND:游标是否读取到数据 ;读取到true;否则:false

显式游标

​ 1、声明游标

​ 2、打开游标

​ 3、提取数据

​ 4、关闭游标

declare
       --声明游标
       cursor c1 is 查询命令;
begin
	     --打开游标
			 open c1;
			 --提取数据
			 fetch c1 into 变量名;
			 
			 --关闭游标
			 close c1;
			 
end; 

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

普通显式游标

示例:

declare
       --声明游标
       cursor c1 is select ename,sal from emp where deptno=10;
			 --临时变量
	 vname emp.ename%type;
	 vsal emp.sal%type;
begin
	     --打开游标
			 open c1;
			 --逐行提取数据
			 loop
			           fetch c1 into vname,vsal;
								 --    实际的业务...
								       if vsal>3000 and vsal<=10000 then
			                    dbms_output.put_line('调薪:2000');
												else
													 dbms_output.put_line('调薪:1000');
											end if;
									exit when c1%notfound;
						end loop;
			 --关闭游标
			 close c1;
	end;  

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

带变量的游标

declare
       --声明游标
       cursor c1(vno number)  is select ename,sal from emp where deptno=vno;
       --临时变量
       vname emp.ename%type;
       vsal emp.sal%type;
begin
       --打开游标
       open c1(10);
       --逐行提取数据
       loop
                 fetch c1 into vname,vsal;
								       dbms_output.put_line(vname||':工资:'||vsal);
                 --    实际的业务...
                       if vsal>3000 and vsal<=10000 then
                          dbms_output.put_line('调薪:2000');
                        else
                           dbms_output.put_line('调薪:1000');
                      end if;
                  exit when c1%notfound;
            end loop;
       --关闭游标
       close c1;
  end;  

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

使用for循环简化游标

	--emp_row:行数据 
	begin
		   for emp_row in (select ename,sal from emp where deptno=10) loop
				   dbms_output.put_line(emp_row.ename||emp_row.sal);
				 end loop;
		end;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

游标类型的变量

​ 说明:将游标当作数据类型,来声明变量;比如:存储过程,函数,将查询的多条结果存入到游标中,返回供别的过程使用;

	declare
		     --游标类型
				 type cur_type is ref cursor;
				 --使用该游标类型 声明一个变量
				 cur1 cur_type;
				 --行类型的变量用来存储每次读取到的数据
				 emp_row emp%rowtype;
		begin
		     open cur1 for select * from emp;	
		     loop
					 fetch cur1 into emp_row;
					       dbms_output.put_line(emp_row.ename||emp_row.sal);
								 exit when cur1%notfound;
					 end loop;
				 close cur1;
end;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

隐式游标

begin
	       delete from emp where empno=621;
				 if SQL%NOTFOUND then
					 dbms_output.put_line('数据不存在');
					 else
						    dbms_output.put_line('删除成功');         
					 end if;
end;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

游标测试

create or replace procedure test_cur5
as
   --声明变量
  cursor c1 is select deptno,count(*) from emp group by deptno;
	vno number(3);
	vcount number(5);
begin
	       open c1;
				 loop
					    fetch c1 into vno,vcount;
							      dbms_output.put_line('部门:'||vno||'人数:'||vcount);
							exit when c1%notfound;
					 end loop;
					close c1;
end;  

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

测试:

begin
	 test_cur5;
end;  

  
 
  • 1
  • 2
  • 3

2、触发器

触发器:

​ 隐式(自动)执行的存储过程;当执行:DDL(create,alter,drop)操作,DML(insert,update,delete)操作时,一系列的系统事件(实例加载,登录、登出)时会激活相应类型的触发器;

触发器分类:

1、DDL触发器

2、DML触发器

3、系统触发器

DML触发器

创建触发器:

create or replace trigger 名字 --trigger 触发器
before or after or instead of --执行时间 before:之前  after:之后  instead of :替代(替换)
insert or update or delete --激活触发器的操作(动作)
on 表名 或者 视图名 --触发器操控的数据库对象
for each row -- 行级触发器 :数据操作影响的每一行数据都会激活触发器
begin
	--命令 如果出现错误:
	rasie_application_error();  --触发器执行过程中一旦发生错误,会导致原先的数据操作:一并回滚
	end;
-- 触发器中规定,最多不超过:32k的代码;如果代码过多可以封装成过程,在触发器中调用

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

例子:

1、不允许删除员工的数据

create or replace trigger tg_test1
before delete on emp
begin
	     raise_application_error(-20001,'不允许删除员工表的数据');
end;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5

测试:

delete from emp where empno=66;

  
 
  • 1

触发器中的内置的对象:

逻辑表:

  • :old 旧表:用来存放删除的数据;delete和update时会用到
  • :new 新表:用来存放新的数据;insert和update时会用到

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oSAyVFAF-1601737304231)(6、Oracle游标和触发器.assets/image-20200923151129100.png)]

1、不允许降薪

	create or  replace trigger tg_test2
	before update on emp
	for each row --行级
	begin
		     -- 获取原来的薪资 ,获取修改后的薪资
				 if :old.sal>:new.sal then
					  raise_application_error(-20002,'不允许降薪操作');
					 end if;
	end;
	

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

触发器中的:3个条件谓词:

  • ​ updating: 判断是否是修改操作
  • ​ deleting: 判断是否是删除操作
  • ​ inserting: 判断是否是录入操作

​ 用来判断当前是那种数据操作激活了触发器;直接当作条件使用;


create or replace trigger tg_test3
	after insert or update or delete
	on emp
	begin
		 if updating then
			  dbms_output.put_line('修改操作激活了触发器');
				elsif inserting then
					dbms_output.put_line('录入操作激活了触发器');
					elsif deleting then
							dbms_output.put_line('删除操作激活了触发器');
						end if;
		end;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

准备一个员工表相同结构的的历史记录表;

-- 复制表结构
create table emp_his as select * from emp where 1=2;
--复制表
create table emp_his2 as select * from emp;

  
 
  • 1
  • 2
  • 3
  • 4

例子:删除员工数据时,将删除的数据插入到历史表中

create or replace trigger tg_test4
before delete on emp for each row
begin
	    insert into emp_his(empno,ename,job,sal) values(:old.empno,:old.ename,:old.job,:old.sal);
end;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5

替代触发器只能作用于视图上面:

什么是视图(view):虚拟表,本质上是一个查询命令;

1、授予该用户创建视图的权限:

grant create view to scott;

  
 
  • 1

2、创建视图

​ 将一个复杂的查询命令存储起来,以供重复使用;

创建视图
create view v_selectemp as
select dt.deptno,dt.dname,count(e.empno) vcount from dept dt,emp e where dt.deptno=e.deptno
group by dt.deptno,dt.dname; 

--测试
select * from v_selectemp where vcount>6 order by vcount ;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

视图的作用:简化查询操作,不允许:insert,update,delete

例子:

-- 测试录入
insert into v_selectemp(deptno,dname,vcount) values(30,'帅哥部',30);

create or replace trigger tg_test5
instead of insert on v_selectemp for each row --替换录入操作
begin
	      delete from emp where deptno=:new.deptno; --换成了删除操作
end;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

应用场景:

1、限制数据的修改规则

2、实现自动记录历史记录

3、模拟自增列

create or replace trigger tg_test6
before insert on emp for each row
begin
	     select 序列.nextval into :new.empno from dual;
end;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5

Java中jdbc调用oracle存储过程:

获取驱动包:

D:\app\MrLang\product\11.1.0\db_1\jdbc\lib

  
 
  • 1

maven中注册jar包:

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.1.0.6.0 -Dpackaging=jar -Dfile=驱动包路径

  
 
  • 1

引入依赖项:

 <dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.1.0.6.0</version>
        </dependency>
</dependencies>

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

测试调用过程:

public class Test1 {
    static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    static final String USERNAME = "scott";
    static final String PWD = "tiger";
    public static void main(String[] args) {
        Connection connection=null;
        //操作过程
        CallableStatement statement=null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PWD);
//            与编译命令:指定要调用的过程,
            statement=connection.prepareCall("{call sp_zhuanzhang3(?,?,?,?)}");
//            绑定参数
//            输入参数:传参
            statement.setInt(1,2);
            statement.setInt(2,1000);
//            输出参数:只需要指定对应的数据类型;注册输出参数
            statement.registerOutParameter(3,OracleTypes.NUMBER);
            statement.registerOutParameter(4,OracleTypes.NUMBER);
//            执行命令
            statement.execute();
//            执行后:可以获取到输出参数的值
           double b1= statement.getDouble(3);
            double b2=statement.getDouble(4);
            System.out.println("转账后的余额:"+b1+":"+b2);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

  
 
  • 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
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49

总结

tStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}




# 总结


  
 
  • 1
  • 2
  • 3
  • 4
  • 5

文章来源: aaaedu.blog.csdn.net,作者:tea_year,版权归原作者所有,如需转载,请联系作者。

原文链接:aaaedu.blog.csdn.net/article/details/108914509

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。