Oracle （4）

最新推荐文章于 2022-09-13 20:00:53 发布

tangiwang

最新推荐文章于 2022-09-13 20:00:53 发布

阅读量524

点赞数

大纲：
1．PLSQL编程：Hello World、程序结构、变量、流程控制、游标、
1．存储过程：概念、无参存储、有参存储（输入、输出）、JAVA调用存储。
2．触发器：概念，分类、语句级触发器、行级触发器、基本应用
3．数据的备份还原：数据库的备份还原、数据表的备份还原、SQL语句的导出。

一、PLSQL编程
a.需求：通过程序在控制台打印一句话，Hello World

--过程化的语言，类似与basic
declare 

begin

  --打印一句话
  --下面这句话相当于java：System.out.println("hello world");
  --dbms_output:Oracle的内置包，相当于java类
  --put_line：方法，相当于println
  dbms_output.put_line('hello world');
end;

在这里插入图片描述
–点击运行或者F8

如果想要在命令窗口运行，需要打开输出选项：set serveroutput on

程序包：dbms_output相当于java中的类(system.out),它是oracle自带的,内置.
调用程序包：dbms_output.put_line(‘Hello World!’)相当于java的方法。

b.程序结构
PL/SQL可以分为三个部分：声明部分、可执行部分、异常处理部分。
在这里插入图片描述

c.变量

待补充。。

二、游标（Cursor）
a.什么是游标?

游标（Cursor），也称之为光标,从字面意思理解就是游动的光标。
游标是映射在结果集中一行数据上的位置实体。
游标是从表中检索出结果集，并从中每次指向一条记录进行交互的机制。
游标从概念上讲基于数据库的表返回结果集，向前移动的指针的，每次只指向一行数据。
游标的主要作用:
用于临时存储一个查询返回的多行数据（结果集），通过遍历游标，可以逐行访问处理该结果集的数据。

（显示）游标的使用方式：声明—>打开—>读取—>关闭
b.语法
在这里插入图片描述
基本原理：
游标刚open的时候，指针结果集的第一条记录之前。
游标与结果集的区别是什么?游标是有位置的。
fetch会向前游动，并获取游标的位置的内容。

注意：游动过的就不能回来了，循环一次就到头。

c.游标的属性
在这里插入图片描述

d.创建和使用
【示例】【引用型变量获取游标的值】：
使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。

--使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
DECLARE
  --声明一个游标
  CURSOR C_EMP IS
    SELECT ENAME, SAL FROM EMP;
  --引用型变量
  V_ENAME EMP.ENAME%TYPE; --姓名
  V_SAL   EMP.SAL%TYPE; --工资

BEGIN
  --打开游标，执行查询
  OPEN C_EMP;
  --使用游标，循环取值
  LOOP  
    --获取游标的值放入变量的时候，必须要into前后要对应（数量和类型）
    FETCH C_EMP INTO V_ENAME, V_SAL;  
    EXIT WHEN C_EMP%NOTFOUND;  
    --输出打印
    DBMS_OUTPUT.PUT_LINE('员工的姓名：' || V_ENAME || '，员工的工资' || V_SAL);  
  END LOOP;
CLOSE c_emp ;--关闭游标，释放资源  
END;

【使用记录型变量存值】：

-使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
DECLARE
  --声明一个游标
  CURSOR C_EMP IS SELECT * FROM EMP;
  --记录型变量
  v_emp emp%ROWTYPE;

BEGIN
  --打开游标，执行查询
  OPEN C_EMP;
  --使用游标，循环取值
  LOOP  
    --获取游标的值放入变量的时候，必须要into前后要对应（数量和类型）
    FETCH C_EMP INTO v_emp;  
    EXIT WHEN C_EMP%NOTFOUND;  
    --输出打印
    DBMS_OUTPUT.PUT_LINE('员工的姓名：' || v_emp.ename || '，员工的工资' || v_emp.sal);  
  END LOOP;
CLOSE c_emp ;--关闭游标，释放资源  
END;

e.带参数的游标

【示例】使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。
-- Created on 2015/5/5 by CLARK 
---查询10号部门的员工的姓名和薪资
declare 
   --定义游标--带参数的游标:需要定一个形式参数
   CURSOR c_emp(v_deptno emp.deptno%TYPE) IS SELECT ename,sal FROM emp WHERE deptno=v_deptno ;
   
   --声明变量
   v_ename emp.ename%TYPE;
   v_sal emp.sal%TYPE;

BEGIN
  --用
  --打开游标
  OPEN c_emp(10);
  --循环fetch
  LOOP      
      --取出数据
      FETCH c_emp INTO v_ename,v_sal;
--退出条件
      EXIT WHEN c_emp%NOTFOUND;
      --打印--写任何的逻辑
      dbms_output.put_line('姓名：'||v_ename||'，薪资：'||v_sal);   
  END LOOP;
  --关闭
  CLOSE c_emp;
end;


--使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。
DECLARE
  --声明一个带参数的游标
  CURSOR C_EMP(v_deptno emp.deptno%TYPE) IS SELECT * FROM EMP WHERE deptno=v_deptno;
  --记录型变量
  v_emp emp%ROWTYPE;

BEGIN
  --打开游标，执行查询
  --打开游标的时候需要传入参数
  OPEN C_EMP(20);
  --使用游标，循环取值
  LOOP  
    --获取游标的值放入变量的时候，必须要into前后要对应（数量和类型）
    FETCH C_EMP INTO v_emp;  
    EXIT WHEN C_EMP%NOTFOUND;  
    --输出打印
    DBMS_OUTPUT.PUT_LINE('员工的姓名：' || v_emp.ename || '，员工的工资' || v_emp.sal);  
  END LOOP;
  
  CLOSE c_emp ;--关闭游标，释放资源  
END;

三、存储过程。
存储过程：就是一块PLSQL语句包装起来，起个名称。
存储作用：
1，在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源)。这种就需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.
2.ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可以会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

b.语法
在这里插入图片描述
根据参数的类型，我们将其分为3类讲解：

不带参数的
带输入参数的
带输入输出参数的。

c.无参存储
最简单，就是包装了一个代码块.
在这里插入图片描述

create or replace procedure p_hello IS

begin
  
       dbms_output.put_line('hello world');

end p_hello;

查询是否创建：
在工具procedures这里
在这里插入图片描述

关于写存储的3个窗口的选择
在这里插入图片描述
编译发布的时候使用command、测试使用test.

c2.调试存储
在这里插入图片描述

c3.调用方法

两种方法:

一种是是用exec命令来调用—用来测试存储
命令调用的方式:
一种是用其他的程序（plsql和java）来调用
程序调用

d.带输入参数in

【示例】
某个员工（如7839号员工）的姓名和薪水–存储过程：要求，调用的时候传入员工编号，自动控制台打印。
–查询并打印某个员工（如7839号员工）的姓名和薪水–存储过程：要求，调用的时候传入员工编号，自动控制台打印。

create or replace procedure p_queryempsal(i_empno IN emp.empno%TYPE)--i_empno输入参数的名字，IN代表是输入值的参数，
 IS
 --声明变量
 v_ename emp.ename%TYPE;
 v_sal emp.sal%TYPE;
BEGIN
  --赋值
  SELECT ename ,sal INTO v_ename,v_sal FROM emp WHERE empno= i_empno;
  --打印
  dbms_output.put_line('姓名：'||v_ename||'，薪水：'||v_sal);  
end p_queryempsal;

命令调用：
在这里插入图片描述
程序调用：

e.带输入in和输出参数out
【示例】输入员工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。
----输入员工号查询某个员工（7839号（老大）员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。

CREATE OR REPLACE PROCEDURE p_queryempsal_out( i_empno IN emp.empno%TYPE,o_sal OUT emp.sal%TYPE)
AS
BEGIN
  --赋值:将薪水的值赋给输出的参数o_sal
  SELECT sal INTO o_sal FROM emp WHERE empno=i_empno;  

END;

调用（使用plsql程序调用）：

DECLARE
  --输入参数值
  v_empno emp.empno%TYPE:=7839;
  --声明一个变量来接收输出参数
  v_sal emp.sal%TYPE;

BEGIN

  p_queryempsal_out(v_empno,v_sal);--第二个参数是输出的参数，必须有变量来接收！！
  --当上面的语句执行之后，v_sal就有值了。
  
  dbms_output.put_line('员工编号为：'||v_empno||'的薪资为：'||v_sal);
END;
/

注意:调用的时候,参数要与定义的参数的顺序和类型一致.

f.存储函数
在这里插入图片描述
存储过程和存储函数的区别：

一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。

一般我们会直接选择使用存储过程，原因是：Oracle的新版本中，已经不推荐使用存储函数了。函数是必须有返回值，存储可以有也可以没有，存储的更灵活！

g.java程序调用
需求:如果一条语句无法实现结果集的查询，(比如需要多表查询,或者需要复杂逻辑查询,),你可以选择调用存储查询出你的结果.
在这里插入图片描述

//获取连接
		Connection conn = JDBCUtils.getConnection();
		
		//获取CallableStatement
		//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql="{call p_queryempsal_out(?,?)}";//转义sql
		CallableStatement call = conn.prepareCall(sql);
		
		//设置参数（占位符的）
		//1.输入参数
		call.setInt(1, 7839);//索引位置
		//2.输出参数:(如果使用结果参数，则必须将其注册为 OUT 参数)
		//引包：oracle.jdbc.driver
		call.registerOutParameter(2, OracleTypes.DOUBLE);//第一个参数是占位符，第二个参数数据类型
//		Types
		
		//执行存储
		call.execute();//执行的时候，会自动将参数传入数据库,将输出参数返回的数据，封装会call对象中。
		
		//获取输出参数的值
		double sal = call.getDouble(2);
		System.out.println("薪资是："+sal);
		
		//释放资源
		JDBCUtils.release(conn, call, null);

四、触发器
数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
解释：
首先，它也是一段plsql程序。
然后，它是来触发与表数据操作相关的（insert，update，delete）。
然后，在进行表数据操作的时候，会自动触发执行的一段程序。

换句话说：触发器就是在执行某个操作（增删改）的时候触发一个动作(一段程序)。

b.语法
在这里插入图片描述

c.第一个触发器
【示例】-每当dept表中添加了一个新部门时，打印”成功插入新部门”

在这里插入图片描述

create or replace trigger tri_adddept
  AFTER INSERT 
  on dept  
declare
begin
  dbms_output.put_line('插入了新部门');
    end ;

测试
SELECT * FROM dept;
INSERT INTO dept VALUES(80,'itcast1','上海');
SELECT * FROM dept;

d.触发器的类型
在这里插入图片描述

e.语句级触发器和行级触发器的区别

【示例】目标：演示语句级触发器和行级触发器的区别
复制出来一张表depttemp，分别建立语句级和行级触发器，然后进行批量插入操作测试。

CREATE TABLE depttemp AS SELECT * FROM dept WHERE 1<>1;
SELECT * FROM depttemp;

--语句级别
create or replace trigger tri_adddepttemp_yuju
  after insert on depttemp  
declare
begin--plsql语句
  dbms_output.put_line('成功插入了一个部门：语句级触发器触发了。。:');
end tri_adddepttemp_yuju; 

--行级别：
create or replace trigger tri_adddepttemp_hangji
  after insert on depttemp  
  
  for each row
  
declare
begin--plsql语句
  dbms_output.put_line('成功插入了一个部门：行级触发器触发了。。:');
end tri_adddepttemp_hangji;

批量插入数据测试：
–先建立两种触发器
–批量插入数据
INSERT INTO depttemp SELECT * FROM dept;
在这里插入图片描述

语句级触发器和行级触发器区别：
1．在语法上，行级触发器就多了一句话：for each row
2．在表现上，行级触发器，在每一行的数据进行操作的时候都会触发。但语句级触发器，对表的一个完整操作才会触发一次。
简单的说：行级触发器，是对应行操作的；语句级触发器，是对应表操作的。

f: 行级别触发器的伪记录变量
在这里插入图片描述
：new代表操作之后的数据，只出现在INSERT/UPDATE中，
：old代表操作（cud）之前的那条数据，出现在UPDATE/DELETE，

INSERT时:NEW表示新插入的行数据，UPDATE时:NEW表示要替换的新数据，:OLD表示要被更改的原来数据，DELETE时:OLD表示要被删除的数据。

【示例】涨工资：涨后的工资不能少于涨前的工资

分析：行级触发器，数据确认示例--行级触发器
-涨工资：涨后的工资不能少于涨前的工资
create or replace trigger tri_checkempsal
  BEFORE UPDATE ON emp--更新之前拦截触发
  for each row--行级触发器
declare
BEGIN
      --如果涨后小于涨前，则，终止更新操作
  IF :new.Sal<:old.Sal THEN
    --终止程序继续运行，也就终止了更新操作了。
    raise_application_error(-20001,'涨后的工资不能少于涨前的工资！！涨前的工资：'||:old.Sal||'，涨后的工资：'||:new.sal);
    --相当于抛出异常（throw）,(使用了oracle内置的一个函数来抛出异常)
  END IF;
end tri_checkempsal;

g.触发器的应用场景
在这里插入图片描述
【示例】
数据的备份：
业务的原理：在更新或者删除数据的时候，将旧的数据备份出来到另外一张表中。

--数据的备份：
create or replace trigger tri_deptbak
  AFTER UPDATE
  on dept
  FOR EACH ROW--行级触发器 
declare
  
begin
 INSERT INTO depttemp VALUES(:OLD.DEPTNO,:OLD.DNAME,:OLD.LOC,SYSDATE);
 --COMMIT;
END ;

注意:触发器会引起锁，降低效率！使用时要慎重。如无必要，尽量不要使用触发器。行级触发器会引发行级锁（锁行数据）;语句级触发器可能会引起表级锁（锁表）.

五、数据字典DICTIONARY
a.概念
为什么要有数据字典？
数据库是数据的集合，数据库维护和管理这用户的数据，那么这些用户数据表都存在哪里，用户的信息是怎样的，存储这些用户的数据的路径在哪里，这些信息不属于用户的信息，却是数据库维护和管理用户数据的核心.这些信息就是数据库的数据字典来维护的，数据库的数据字典就汇集了这些数据库运行所需要的基础信息。
什么是数据字典？
Oracle的数据字典是Oracle数据库安装之后，自动创建的一系列表。
数据字典表和用户创建的表没有什么区别，不过数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据而已。
在这里插入图片描述
对于数据字典表，里面的数据是有数据库系统自身来维护的。切记记住不要去修改这些表里的内容。，所以，数据字典主要用来查询的。

b.数据字典的命名规则
数据字典表的用户都是sys，存在在system这个表空间里，表名都用"$"结尾，Oracle对这些数据字典都分别建立了用户视图，不仅有更容易接受的名字，还隐藏了数据字典表与表之间的关系.
racle针对这些对象的范围，分别把视图命名为DBA_XXXX, ALL_XXXX和USER_XXXX，所以我们说的数据字典一般是指数据字典视图。

在这里插入图片描述
dictionary视图，该视图里记录了所有的数据字典视图的名称。所以当我们需要查找某个数据字典而又不知道这个信息在哪个视图里的时候，就可以在dictionary视图里找。该视图还有个同义词dict。

c常用的数据字典
在这里插入图片描述

六、数据的备份还原
使用oracle自带的备份还原命令：exp(备份),imp（导入）
注意：你的系统中必须有这个命令才能去执行这个命令
在这里插入图片描述
导入和导出命令，既可以直接写命令+参数，也可以使用向导的方式。
下面采用向导的方式：
目标：备份scott用户下的所有对象。

导入：
在这里插入图片描述

b.使用plsqlDeveloper工具：
在这里插入图片描述

你可以使用ctrl或shilft键进行选择。

目标：导出emp表中的10号部门的数据
在这里插入图片描述

还原方法：

【导入的前提】
先建立用户，然后在用户下面导入数据。

[扩展补充:]
1.降龙十八掌
2.表分区的资料(可以将一张表分成很多个表空间来存储,为了提供查询的效率.)—跟分表区分开
3.数据库的Job或schedule
4.Dblink—跨数据库访问用的。

tangiwang

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle （4）

大纲：1．PLSQL编程：Hello World、程序结构、变量、流程控制、游标、1．存储过程：概念、无参存储、有参存储（输入、输出）、JAVA调用存储。2．触发器：概念，分类、语句级触发器、行级触发器、基本应用3．数据的备份还原：数据库的备份还原、数据表的备份还原、SQL语句的导出。一、PLSQL编程a.需求：通过程序在控制台打印一句话，Hello World--过程化的语言，类似...
复制链接

扫一扫