Orcal高级

最新推荐文章于 2025-03-30 08:34:20 发布

原创最新推荐文章于 2025-03-30 08:34:20 发布

· 878 阅读

2 ·

版权

文章标签：

#oracle

数据库专栏收录该内容

5 篇文章

订阅专栏

一、游标

一、游标概述

1、游标是什么？

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义，用来临时存储多条查询数据的一种数据结构（‘结果集’）。

2、游标分类

游标有两种类型：显式游标和隐式游标。在用到的SELECT…INTO…DELETE.语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标( cursor is select )，并通过与游标有关的语句进行处理。

3 、优缺点

(1) 提高 sql ‘执行效率’
(2) 牺牲 ‘内存’

二、游标4大属性

属性           返回值类型     作用
SQL%ROWCOUNT     整型      代表DML语句成功执行的数据行数  
SQL%FOUND       布尔型     值为**TRUE**代表插入、删除、更新或单行查询操作成功  
SQL%NOTFOUND    布尔型     与SQL%FOUND属性返回值相反  
SQL%ISOPEN      布尔型     判断游标是否 '开启'

三、游标分类

1、隐式游标

emp表数据
在这里插入图片描述

1.自动创建
   (1) DML
   (2) select into
   
2. 自动管理
   (1) 无需人为干预（自动声明、打开、关闭）
   (2) 默认游标名：'SQL'

BEGIN  
  UPDATE emp SET sal=sal+100 WHERE empno=8888;   
    IF SQL%FOUND THEN    
    DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！数据成功更新' || SQL%ROWCOUNT||'条');   
    COMMIT;    
  ELSE  
    DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');   
  END IF;    
END;

2、显式游标-无参

用游标提取emp表中8888雇员的名称和职务

declare
  -- 1 声明一个游标
  cursor emp_cursor is
    select ename,job,sal from emp where empno=8888; 
  v_emp_info emp_cursor%rowtype; 
  --  %rowtype: 声明 emp表的所有字段
begin
  -- 2 开启游标
  open emp_cursor;

  -- 3 获取数据（一次获取一行）
  fetch cur_stu_info
    into v_stu_info;
  dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name);

  -- 4 关闭游标
  close emp_cursor;

end;

###3、显式游标-有参
用游标提取emp表中部门编号为20工作为ANALYST的信息

-- Created on 2021/7/29 by 忘忧 
-- 有参游标的使用
declare
  --定义一个有参游标
  cursor emp_cur(v_deptno emp.deptno%type,v_job emp.job%type) is
    select * from emp e where e.deptno = v_dept_no and e.job = v_job;
    --v_job emp.job%type 等同于  job (VARCHAR2(9)
begin
  -- 使用 for in循坏迭代有参游标
  for emp_record in emp_cur(20,'ANALYST') loop
    dbms_output.put_line('名字：' || emp_record.ename || ',工作岗位：' ||
                         emp_record.job || ',薪资：' || emp_record.sal);
  end loop;
end;

二、存储过程

一、存储过程概述

1. 存储过程是什么：

事先经过编译并存储在数据库中的一组为了完成特定功能的SQL 语句。

2. 存储过程的优点：

(1). 提高了SQL语句的功能和灵活性，‘预编译’ 后存储于内存中，再次使用时，无需再次编译。
(2).可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
(3).提高系统的 ‘安全性’ 减少 sql 注入的危险、权限控制。

3. 存储过程的缺点：

(1).如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用。
(2).增加数据库服务器的负荷。

二、存储过程创建

create or replace procedure 过程名(变量名 in|out 参数的数据类型) 
is | as -- 此处 is 和 as 等同
   声明语句段;
begin
   执行过程语句段;
exception 
   异常处理语句段;
end 过程名;

IN | OUT | IN | IN OUT：指定参数的模式。

IN表示输入参数，在调用存储过程时需要为输入参数返回值，而且其值不能在存储过程中修改;
OUT表示输出参数，存储过程通过输出参数返回值;
IN OUT则表示输入输出参数，这种类型的参数既要接受传递值也允许在过程体重修改其值，并可以返回。默认为IN,在使用IN参数时，还可以使用DEFAULT关键字为该参数设置默认值。

1、存储过程创建示例

从emp表中查询给定职工 （提示：使用&来输入员工编号）的职工姓名和工资。（要求：利用out模式的参数将值传给调用者。）

create or replace procedure pro_emp(i_no   in emp.empno%type,
                                    i_name out emp.ename%type,
                                    i_sal  out emp.sal%type) is
begin
  select ename, sal into i_name, i_sal from emp where empno = i_no;
exception
  when no_data_found then
    dbms_output.put_line('此员工不存在！');

end pro_emp;

三、存储过程调用

1. 在 pl/sql 中调用存储过程

//语法
declare
   begin
      procedure_name; -- 若有参，则加上()
   end;
   
//调用
-- Created on 2021/6/22 by 忘忧 
declare 
  i_name emp.ename%type;
  i_sal  emp.sal%type;
begin
  -- Test statements here
   pro_emp(&员工编号,i_name,i_sal);
    if i_name is not null then
      dbms_output.put_line('姓名:'||rpad(i_name,5)|| '薪资:'||rpad(i_sal,5));
    end if;  
end;

2.在命令窗口调用（仅在 ‘无参’ 或 ‘仅有入参’ 情况下）

   1.exec 存储过程名;
   2.call 存储过程名;
   -- 若有参，则加上()

1.创建存储过程，根据员工编号删除emp表中的相关记录。 （提示：由调用语句提供的员工编号来删除记录，要求员工编号可随机输入。）

create or replace procedure pro_del(i_no in emp.empno%type) is
 begin
  delete from emp where empno =i_no;
  dbms_output.put_line('删除成功!');
 end pro_del;
//sql 命令窗口调用
call pro_del(&员工编号)
输出：删除成功!

四、存储过程创建与调用例题

 1.编写过程，实现交换两个变量的值的功能。并输出交换前和交换后的两个值。（in out参数）

create or replace procedure change(num1 in out number,num2 in out number) is
  z number(5);
 begin
 z:=num1;   --:= 表示把 num1的值赋值给z
 num1:=num2;  
 num2:=z;
end huang;

//sql 命令窗口调用

declare
 num1 number:=10;
 num2 number:=20;
begin
  dbms_output.put_line('交换前:'|| rpad(num1,5)||rpad(num2,5));
  change(num1,num2);
  dbms_output.put_line('交换后:'|| rpad(num1,5)||rpad(num2,5));
end;

2.编写一个过程，指定一个员工编号与一个工资增加的百分比，使emp表中将该员工的工资（sal）增加输入的百分比。

create or replace procedure pro_sal(id in emp.empno%type,parset in float) is

begin
  update emp set sal = sal+sal*parset where empno=id;
    dbms_output.put_line('更新成功!');
end pro_sal;

//sql 命令窗口调用

 call  pro_sal(&id,&幅度)

五、修改与删除存储过程

1.修改存储过程

1. create or replace 过程名... -- 支持重复操作，其本质是删除原有创建新的。

2.删除存储过程

drop procedure procedure_name(过程名); -- 删除

三、函数

一、定义

函数与存储过程的结构类似，但是函数必须有一个return子句，用于返回函数值。
创建函数使用CREATE FUNCTION语句，如下：

CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | OUT | IN OUT } ] Type,   --参数
   argment [ { IN | OUT | IN OUT } ] Type ]
RETURN  data_type
{ IS | AS }
   声明部分,<类型.变量的说明> 
BEGIN
   执行部分,函数体
EXCEPTION
   可选的异常错误处理部分
END;

二、创建无参函数

create or replace function first_fun
return varchar2 is
 begin
  dbms_output.put_line('创建第一个无参函数');
  return 'Hello Function';
end;


-- sql 命令窗口调用
begin
dbms_output.put_line(first_fun);
end;

--输出 
-- 创建第一个无参函数
-- Hello Function

三、创建有参函数

-- 1.创建输入带参数函数,从emp表中查询指定员工编号的职工的工资

create or replace function f_sal(i_no in emp.empno%type) return  emp.sal%type is o_sal emp.sal%type;
begin
  select sal into o_sal from emp where empno=i_no;
     dbms_output.put_line('查询成功!');
  return o_sal;
end;

//sql 命令窗口调用

begin
dbms_output.put_line('工资是:'|| f_sal(8888));
end;

-- 2.创建带输出参数函数，返回emp表中指定职工的工资和姓名。 （提示：返回值是两个，可用return返回一个，另一个用out参数带回）

create or replace function fu_sal(i_no   in emp.empno%type,
                                  o_name out emp.ename%type)
  return emp.sal%type is
  o_sal emp.sal%type;
begin
  select e.sal, e.ename into o_sal, o_name from emp e where e.empno = i_no;
  dbms_output.put_line('查询成功!');
  return o_sal;
end;

//sql 命令窗口调用

declare
  o_name emp.ename%type;
  o_sal  emp.sal%type;
begin
  o_sal := fu_sal(&员工编号, o_name);
  dbms_output.put_line('姓名:' || rpad(o_name, 10) || '薪水' || rpad(o_sal, 5));
end;

四、删除函数

drop function 函数名称;

五、存储过程与函数的区别与联系

1、相同点：

.创建语法结构相似，都可以携带多个传入参数和传出参数。
都是一次编译，多次执行。

2、不同点：

.存储过程定义关键字用procedure，函数定义用function。
.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。procedur不叫返回值，叫带出，可以带出多个值。
.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select function( ) form dual ）
在sql数据操纵语句中只能调用函数而不能调用存储过程。

四、触发器

一、什么是触发器？

1.触发器是一种特殊的存储过程，触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。
2.ORACLE数据库有四种触发器，分别是DML、Instead-of、DDL、DB触发器，一般的应用系统中都是用到DML、Instead-of触发器，DDL、DB两种触发器是DBA管理数据库用得比较多

二、触发器用途

通过记录修改内容和修改者来审计表中的信息。
在表内容发生变更时，自动通知其他程序采取相应的处理。
在订阅发布环境下，发布有关各种事件的信息。

三、触发器类型

1.DML触发器

ORACLE可以在DML(增删改)语句进行触发，可以在DML操作前（before触发器）或操作后（after触发器）进行触发，并且可以对每个行或语句操作上进行触发（行级触发器和语句级触发器),是最简单和常用的一种触发器。

2.Instead-of触发器

又称为替代触发器，用于执行一个替代操作来代替触发事件的操作，由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。

3.DB触发器

又称系统事件触发器，在发生如数据库启动或者关闭等系统事件时开始触发。

4.DDL触发器

由DDL语句触发，例如CREATE、ALTER和DROP语句。可分为BEFORE触发器和AFTER触发器。

四、创建触发器

1、创建语法

 CREATE [ OR REPLACE ] TRIGGER 触发器名称。

 { BEFORE | AFTER | INSTEAD OF }
-- BEFORE和AFTER表示触发器执行的时间在触发事件的前后，INSTEAD OF表示触发器中的事件代替触发事件执行。

 { INSERT | DELETE | UPDATE [ OF column [, column … ] ] } 
 --激活触发器的事件，例如 INSERT、DELETE、UPDATE 。

 ON [ schema. ] table_name | [ schema.] view_name | [ DATAASE ]
-- table_name为DML触发器所针对的表，如果是替代触发器则需要指定视图名称（view_name），如果是DDL触发器或者系统事件触发器，则使用ON DATABASE。
 [ REFERENCING  { OLD [ AS ] old | NEW [ AS ]  new | PARENT as parent } ]

 [ FOR EACH ROW ]
--表示是行级触发器，如果未指定则为语句级触发器。
 [ WHEN trigger_condition ]
--为触发的运行指定限制条件
 [ DECLARE declaration_statements ; ]
--定义
 BEGIN

触发器体，包含触发器的内容;

 END 触发器名称

2、DML语句触发器

在指定的操作语句之前或之后执行一次,不管这条语句影响了多少行（如果该语句只影响一行，则语句级与行级触发效果相同。）

1、实现数据的安全保护

create [or replace] trigger 触发器名称
  {before|after}
  {delete|insert|update|[of列名]}
on 表名
--禁止在周五、周六改变emp表数据
create or replace trigger emp_change
before insert or update or delete
on emp
begin
  if to_char(sysdate,'day') in ('星期五','星期六') then
    raise_application_error(-20008,'不能在休息日改变员工信息!');
  end if;
end;

update emp set sal=1002 where empno=8888
在这里插入图片描述

--利用触发器，在星期一、周末及每天下班时间（每天9：00以前、18：00以后）后不允许更新emp数据表。
create or replace trigger p_ls

  before insert or delete or update on emp
declare
  v_a VARCHAR(20);
  v_b VARCHAR(20);
begin

  select to_char(sysdate, 'day'), to_char(sysdate, 'hh24') into v_a, v_b  from dual;

  if trim(v_a) = '星期一' or trim(v_b) = '星期六' or trim(v_b) = '星期日' then  
     raise_application_error(-2008, '在周末及周一不允许更新emp数据表！');
     
  elsif trim(v_b) < '9' or trim(v_b) > '18' then
     raise_application_error(-20009, '在下班时间不能够修改emp表数据！');
  end if;
end ;
--测试
 update emp set sal=2000 where deptno=10

3、DML行级触发器

如果该语句影响多行，则行级触发的次数比语句级触发的次数多。
在触发器正在处理过程中我们用:old和:new 两个相关标识符来访问行级触发器中的行数据。
使用“:old”和“:new”应注意的问题：
在BEFORE类型行级触发器和AFTER类型行级触发器中使用这些标识符。
在语句级触发器中不要使用这些标识符。
在PL/SQL语句或SQL语句中，这些标识符前加上冒号（:）来引用它们。
在行级触发器的WHEN条件中使用该标识符时，前面不要加冒号（:）.
在BEFORE触发器中修改 “:new”，不能修改“:old”，在AFTER触发器中不能修改“:new”
比如：在行级触发器中获取某列的新值和旧值，为EMP表中的数据保留历史记录

2、实现数据审计

create [or replace] trigger trigger_name
 {before|after}
 {delete|insert|update|[of列名]}
 on 表名
[for each row [when(条件)]]
 

 --功能: 审计员工信息表数据的变化,审计删除时间,以及被删除的雇员名.
--创建审计表
create table delete_emp_audit(
  name varchar2(10), --员工姓名
  delete_time date   --删除时间
);
 
--创建触发器
create or replace trigger del_emp_tigger
after delete 
on emp
for each row
begin
  insert into delete_emp_audit values(:old.ename,sysdate);
end;


--测试
delete from emp where empno =7499

3、实现数据完整性

-- 功能: 要求员工涨后的工资不能低于原来的工资,并且所涨的工资不能超过原工资的50% 
create or replace trigger tr_check_sal
before update of sal --of 指定更新sal列时触发 触发器
on emp
for each row
when (new.sal < old.sal or new.sal > old.sal * 1.5) --此处new和old不加冒号(:),因为when语句不在qlsql块内部
begin
  raise_application_error(-20028,'工资只升不降,并且升幅不能超过50%');
end;

--测试
update emp set sal=1000*2 where empno=8888

4、实现参照完整性

--创建一个行级触发器CASCADE_DEL_UPD，当修改部门编号时， EMP表的相关行的部门编号也自动修改；当删除部门表中某个部门号是，EMP表该部门号的员工一并删除 
create or replace trigger CASCADE_DEL_UPD
  before update or delete
  on  dept
  for each row
declare
  -- local variables here
begin
  if deleting then
    delete from emp e where e.empno=:old.deptno;
  elsif updating then
    update emp e set e.deptno=:new.deptno where e.deptno=:old.deptno;
    end if;
end CASCADE_DEL_UPD;

 -- 测试
 update emp e set e.deptno='80' where e.deptno=10;

五、包

一、程序包的相关知识

1.程序包是？

是对相关过程、函数、变量、游标和异常等对象的封装，由规范和主体两部分组成。

2.程序包规范

声明类型、变量、常量、异常、游标和子程序等元素条目（不包含这些元素的实际代码）。

3.程序包主体

用于实现在程序包规范中定义的游标、子程序（包含了元素的实际代码）。

4.程序包规范

包规范中的条目为共有项目，可供所有的数据库用户访问；而包体中创建的规范中没有提到的项目，属于私有项目，只能在包体中使用。

二、创建包

CREATE [ OR REPLACE ] PACKAGE 包名

 { IS | AS }

--可以使用的公共存储过程、函数、类型和对象。

 END ;

--示例

create or replace package pack_first2 is

  -- Author  : 忘忧
  -- Created : 2021/8/5 19:44:58
 procedure pro_del(i_no in emp.empno%type);
function f_sal(i_no in number) return  number;
 
end ;

三、创建包体

--sql 窗口

CREATE [ OR REPLACE ] PACKAGE BOODY 包名

 { IS | AS }

 package_body ;

 END;

create or replace package body pack_first2 is

procedure pro_del(i_no in emp.empno%type) is
 begin
   delete from emp where empno =i_no;
   dbms_output.put_line('删除成功!');
end pro_del;

function f_sal(i_no in number) return  number is o_sal number(7,2);
 begin
     select sal into o_sal from emp where empno=i_no;
     dbms_output.put_line('查询成功!');
  return o_sal;
end;
end;

四、调用包

call pack_first2.pro_del(8888);

begin
dbms_output.put_line('工资是:'|| pack_first2.f_sal(8888));
end;

六、事务

一、事务概念

概念：在数据库中事务是工作的逻辑单元 , 一个事务是由一个或多个完成一组的相关行为的 SQL 语句组成 , 通过事务机制确保这一组 SQL 语句所作的操作要么完全成功执行 , 完成整个工作单元操作 , 要么一点也不执行。
主要特性：确保数据库的完整性。

二、事务的 ACID 特性

对一组 SQL 语句操作构成事务 , 数据库操作系统必须确保这些操作的原子性 , 一致性 , 隔离性 , 持久性 .

1、原子性（Atomic）

事务中所有数据的修改，要么全部执行，要么全部不执行，以保证数据库的一致性。

2、一致性（Consistence）

事务的一致性是指数据库在事务操作前和事务处理后 , 其中数据必须满足业务的规则约束。

3、隔离性（Isolation）

隔离性是指数据库允许多个并发的事务同时对其中的数据进行读写或修改的能力 , 隔离性可以防止多个事务的并发执行时 , 由于它们的操作命令交叉执行而导致数据的不一致性。

4、持久性（Durability）

事务的持久性是指在事务处理结束后 , 它对数据的修改应该是永久的。即便是系统在遇到故障的情况下也不会丢失 , 这是数据的重要性决定的。

三、事务组成

一条或者多条DML，[一条DDL]和一条DCL。

DML 数据操纵语言 SELECT、UPDATE、INSERT、DELETE 对数据库里的数据进行操作。
DDL 数据定义语言主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。
DCL 数据控制语言是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括(grant,deny,revoke等)语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。

四、事务控制语句

在 oracle 数据库中 , 没有提供开始事务处理语句 , 所有的事务都是隐式开始的 , 也就是说在 oracle 中 , 用户不可以显示使用命令来开始一个事务 .oracle 任务第一条修改数据库的语句 , 或者一些要求事务处理的场合都是事务的隐式开始。但是当用户想要终止一个事务处理时 , 必须显示使用 commit 和 rollback 语句结束。

1、 Set transaction 设置事物属性

（1）read only：表示建立只读事务，在此事务中只有select、select into、open、fetch、close、lock table、commit、rollback语句才允许出现，不可以执行insert、update、select for update等非法操作，对于这种事务模式不用指定回滚段。
 
（2）read write：建立读写事务，该事务既可读、也可写，oracle默认的事务就是这种类型。

（3）isolation level：用来设置事务的隔离级别，可以设置为serializable和read committed这两个选项。serializable会使得对已经修改但没有提交的数据对象的DML事务失败；read committed会使得对已经修改但没有提交的数据库对象的DML事务在进行修改时，会等待先前DML锁释放，这也是oracle默认的事务的隔离级别。

（注意：这些语句是互斥的 . 即不能够同时设置两个或者两个以上的选项。set transaction只对当前事务有效,事务终止,事务当前的设置将会失效。）

（1）示例
在这里插入图片描述

2、 Set constrains 设置事物的约束模式

在事务中修改数据时 , 数据库中的约束立即应用于数据 , 还是将约束推迟到当前事务结束后应用。

3、 Savepoint 设置存储点

在事务中建立一个存储的点 . 当事务处理发生异常而回滚事务时 , 可指定事务回滚到某存储点 . 然后从该存储点重新执行。

4、 Release savepoint 删除存储点

5、 Rollback 回滚事务

取消对数据库所作的任何操作

6、 Commit 提交事务

对数据库的操作做持久的保存。

示例
select * from emp;

在这里插入图片描述


savepoint A;  --设置储存点a

delete from emp;  --删除数据

rollback to A;  --回滚到储存点A