Oracle 数据库很难么？带你从头到尾捋一遍，不信你学不会！章

Oracle 带参数的存储过程

带参数的存储过程

存储过程允许带有参数，参数的使用将增加存储过程的灵活性，给数据库编程带来极大的方便。

存储过程中如果使用了参数，在执行存储过程时必须为其指定参数。总的来说，参数可以是常量、变量和表达式等。

Oracle有三种参数模式：

• IN
• OUT
• IN OUT

IN参数

该类参数值由调用者传入，并且只能够被存储过程读取。这种模式的参数是最常用的，也是默认的参数模式。

示例：创建一个带有输入参数的存储过程，当使用Insert_student添加学生信息时，用户只需要向该存储过程传入参数值，然后由存储过程从中读取数据。

create or replace procedure Insert_student(SNO IN varchar2,SNAME IN varchar2,SSEX IN char,SAGE IN number,SDEPT IN varchar2) is
begin
 insert into student
 values(SNO,SNAME,SSEX,SAGE,SDEPT);
 exception
   when dup_val_on_index then dbms_output.put_line('重复的学生ID');
   when others then dbms_output.put_line('发生其它错误！'); 
end Insert_student;

执行存储过程并传递参数，共有三种方式：

• 名称传递
  
• 位置传递
  
• 组合传递
  

OUT参数

该类参数值由存储过程写入。

示例：创建一个带有输出参数的存储过程，该存储过程根据学生的姓名返回该学生的所属学院。

create or replace procedure p_getSdept(NAME IN varchar2,DEPT OUT varchar2) 
is
 temp varchar(20);
begin
  select s.SDEPT into temp
  from student s
  where s.SNAME=NAME;
  DEPT := temp;
end p_getSdept;

IN OUT参数

==示例：使用IN OUT参数交换两个变量的值==

create or replace procedure p_swap(value1 in out number,value2 in out number) 
is
 temp number;
begin
  temp := value1;
  value1 := value2;
  value2 := temp;
end p_swap;

执行：

declare 
  var_1 varchar2:=1;
  var_2 varchar2:=2
begin
  p_swap(var_1,var_2);
  dbms_output.put_line('var_1='||var_1);
  dbms_output.put_line('var_2='||var_2);
end;

默认值问题

创建存储过程的同时可以给输入参数提供默认值。OUT和IN OUT参数不具有默认值。

示例：根据传入SNO查询学生姓名，默认查询1号学生姓名

create or replace procedure p_getStudent(s_no in varchar2 default '1')
is
 name varchar2(20);
begin
  select SNAME into name
  from student 
  where SNO = s_no;
  dbms_output.put_line('name='||name);
end p_getStudent;

执行：

Oracle 同义词

同义词

同义词（synonym）是表、索引和视图等模式对象的一个别名。同义词只是数据库对象的替代名，与视图类似，同义词并不占用实际存储空间，只在数据字典中保存同义词的定义。在使用同义词时，Oracle简单地将它翻译成对应方案的名称。

通过使用同义词，一方面可以简化对象访问，如数据字典视图USER_INDEXES的同义词为IND，数据字典视图USER_SEQUENCES的同义词为SEQ；另一方面可以提高对象访问的安全性，如屏蔽对象所有者、对象名和数据库链接名。

在开发数据库应用程序时，应当尽量避免直接引用表、视图或其他数据库对象打的名称，而改用这些对象的同义词。这样可以避免当管理员对数据库对象做出修改和变动之后，必须重新编译应用程序。使用同义词后，即使引用的对象发生变化，也只需要在数据库中对同义词进行修改，而不必对应用程序做任何改动。

在Oracle中可以创建两种类型的同义词：

• 共用同义词（public synonym）： 共有同义词是由PUBLIC用户组所拥有，数据库中所有的用户都可以使用共有同义词。
• 私有同义词（private synonym）： 私有同义词是由创建它的用户（或方案）所拥有，也称方案同义词（schema synonym）。用户可以控制其他用户是否有权使用属于自己的方案同义词。

在创建同义词时，它所基于的对象可以不存在，并且创建同义词的用户也不需要对基础对象有任何访问权限。

语法如下

CREATE [OR REPLACE][PUBLIC] SYNONYM synonym_name
FOR [schema.]object;

为模式TEMP中的表sys_user创建一个共有同义词public_user，尽管该模式并不存在。

create public synonym public_user
for temp.sys_user;

如果同义词所对应的基础对象不存在，则当用户访问时同义词将重新编译，并验证为无效。

在操作过程中，不能修改或改变同义词，而只能删除。 删除刚刚创建的同义词

drop public synonym public_user;

Oracle在数据字典中将保存创建同义词的定义。通过数据字典视图USER_SYNONYMS，可以查询有关同义词的信息。

Oracle 序列

序列

序列（sequence）是一种数据库对象，可以为表中的行自动生成序列号，利用它可生成唯一的整数，产生一组等间隔的数值（类型为数字Number），主要用于生成唯一、连续的序号。序列是一种共享式的对象，多个用户可以共同使用序列中的序号。一般序列所生成的整数通常可以用来填充数字类型的主键列，这样当向表中插入数据时，主键列就使用了序列中的序号，从而保证主键的列值不会重复。用这种方法可以替代在应用程序中产生主键值的方法，可以获得更可靠的主键值。

创建序列

一个序列的是由特殊的Oracle程序自动生成的，因此避免了在应用层实现序列而引起的性能瓶颈。序列号是一个Oracle整数，最多可以有38个数字。创建序列时，需要定义的信息包括序列名、上升或下降、序列号之间的间距和其它信息。

语法如下

CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment_num]
[{MAXVALUE maximum_num | NOMAXVALUE}]
[{MINVALUE minimum_num | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE cache_num | NOCACHE}]
[{ORDER | NOORDER}];

其中各语法选项含义如下：

• sequnce_name: 指定序列名
• start_num： 指定序列从哪个整数开始，该值默认为1
• increment_num： 指定该序列每次增加的整数增量，值默认为1。increment_num的绝对值必须小于maximum_num与minimum_num之差。
• maximum_num： 指定该序列的最大整数。maximum_num必须大于或等于start_num，而且maximum_num必须大于minimum_num。
• NOMAXVALUE： 指定升序序列的最大值为1027，降序序列的最大值为-1。NOMAXVALUE为默认值。
• minimum_num： 指定该序列的最小整数。minimum_num必须小于或等于start_num，而且minimum_num必须小于maximum_num。
• NOMINVALUE： 指定升序序列的最小值为1，降序序列的最小值为-1026。NOMINVALUE为默认值。
• CYCLE： 指定该序列即使已经达到最大值或最小值也继续生成整数。当升序序列达到最大值时，下一个生成的值是最小值。当降序序列达到最小值时，下一个生成的值是最大值。
• NOCYCLE： 指定该序列在达到最大值或最小值之后就不能再生成整数了。NOCYCLE是默认值。
• cache_num： 指定要保留在内存中整数的个数。默认要缓存的整数位20个。可以缓存的整数最少为2个；可以缓存的整数个数最多为：CELL(maximum_num - minimum_num)/ABS(increment_num)。
• NOCACHE： 指定不缓存任何整数。这可以阻止数据库为序列预分配值，从而避免序列产生不连续的情况，但这会降低性能。序列不连续的情况之所以会发生，是因为在关闭数据库时所缓存的值将全部丢失。如果省略CACHE和NOCACHE，则数据库默认缓存的整数位20个。
• ORDER： 确保按照请求次序生成整数。当使用Real Application Clusters(RAC)时一般使用ORDER选项，Real Application Clusters由数据库管理员安装和管理。Real Application Clusters是共享相同内存的多个数据库服务器，它们可以提高性能。
• NOORDER: 不确保按照请求次序生成整数。NOORDER为默认值。

使用默认值创建一个序列s_seq1

创建序列s_seq2，开始值为100，增幅是2，最大值是1000，序列中的序号不在内存中进行缓冲

序列的信息可以从数据字典user_sequences中获得，如下：

使用SELECT语句查看序列s_seq2的最小值、最大值、增幅等信息：

修改序列

ALTER SEQUENCE语句可以用来修改序列。在修改序列时，可以修改的序列内容有如下限制：

• 不能修改序列的初值
• 序列的最小值不能大于当前值
• 序列的最大值不能小于当前值

修改序列S_SEQ2的最小值、最大值、增幅，并使其中的序号可循环使用：

删除序列

对于不再使用的序列，应该及时地将其删除。DROP SEQUENCE可以用来删除序列。
删除序列S_SEQ1

使用序列

对用户而言，序列中的可用资源是其中包含的序号。用户可以通过SELECT命令获得可用的序号，也可以将序号应用于DML语句和表达式中。如果要使用其他用户的序列号，则必须具有对该序列的SELECT权限。

序列提供了两个伪列，即NEXTVAL和CURRVAL，用来访问序列中的序号

• NEXTVAL： 返回序列生成的下一个值。
• CURRVAL： 返回序列的当前值。

需要注意的是，在第一次引用CURRVAL之前，必须引用过一个序列的NEXTVAL，用于初始化序列的值，否则会出现错误提示。

使用序列S_SEQ1为表sys_user表插入一条数据，代码如下：

create table sys_user(
userId number primary key,
userName varchar2(20) not null,
userPWD varchar2(20) not null
);

Oracle 触发器

触发器概述

触发器是一种在发生数据库事件时能够自动运行的PL/SQL代码块，它与特定表或视图上的操作相关联。触发器是大型关系数据库系统都提供的一项技术。

触发器的类型

• 语句级触发器
  在指定的操作语句操作之间或之后执行一次，不管这条语句影响了多少行。
• 行级触发器（FOR EACH ROW）
  触发语句作用的每一条记录都被触发。在行级触发器中使用 :old和:new 伪记录变量，识别值的状态。

语法

create [or replace] trigger 触发器名
{before | after}
{delete | insert | update [列名]|
on 表名
[for each row [when(条件)]]
PLSQL块

示例：在emp表创建触发器，插入数据时打印“成功插入新员工”

create trigger firsttrigger
after insert
on emp
declare
begin
  dbms_output.put_line('成功插入新员工');
end;

insert into emp(empno, ename, sal, deptno) values(1001, 'Tom', 3000,10);

触发器可用于

• 数据确认
• 实施复杂的安全性检查
• 做审计，跟踪表上所做的数据操作等
• 数据的备份和同步

示例：限制非工作时间向数据库插入数据

create or replace trigger securityEmp
before insert on emp
declare
begin
  if to_char(sysdate,'day') in ('星期四','星期六','星期天')
    or to_number(to_char(sysdate, 'hh24')) not between 8 and 18 then
       raise_application_error(-200001,'不能在工作时间插入数据');
  end if;
end;

触发语句与伪记录变量的值

示例：检查emp表中sal的修改值不低于原值

create or replace trigger checkSal
before update of sal on emp
for each row
declare
begin
  if :new.sal < :old.sal then
    raise_application_error(-20002,'更新后的薪水比更新前小');
  end if;
end;  

update emp set sal = 100 where empno = 1001;

查询触发器、过程及函数

select * from user_triggers;
select * from user_source;

Oracle 管理触发器

查看触发器

触发器是一种特殊的存储过程，从被创建之日起，触发器就被存储在数据库中，直到被删除。触发器与一般存储过程或者存储函数的区别在于触发器可以自动执行，而一般的存储过程或者存储函数需要调用才能执行。

Oracle提供检索与触发器相关信息的数据字典。与触发器相关的数据字典主要包括以下几种：

• USER_TRIGGERS： 存储当前用户所拥有的触发器。
• DBA_TRIGGERS： 存储管理员所拥有的触发器。
• ALL_TRIGGERS： 存储所有的触发器。
• USER_OBJECTS： 存储当前用户所拥有的对象，包括触发器。
• DBA_OBJECTS： 存储管理员所拥有的对象，包括触发器。
• ALL_OBJECTS： 存储数据库中所有的对象，包括触发器。

USER_TRIGGERS表主要列的说明如下表：

启用和禁用触发器

Oracle提供的ALTER TRIGGER语句用于启用和禁用触发器。禁用触发器就将它挂起，它仍然存储在数据库中，但是不会被触发，就好像根本没有触发器一样。禁用触发器常常用在当DBA有大量记录要导入到数据库中时，且DBA知道这些数据时安全和可靠的。但禁用触发器后可以节省大量的处理时间。

禁用和启用触发器的语法如下：

ALTER TRIGGER [schema.] trigger_name DISABLE | ENABLE;

修改触发器

当创建的触发器需要修改时可以使用REPLACE关键字。如果在SQL Plus中创建触发器时带上了OR REPLACE关键字，可以完成过程的修改，也就是覆盖。

删除触发器

对应不用的触发器除了设置禁用属性外也可以直接将其删除。可以利用如下两种方式删除触发器：

• 第一种方式利用SQL语句删除触发器。

  DROP TRIGGER [schema.]trigger_name;

• 第二种方式，利用PL/SQL Developer工具删除触发器。选中需要删除的触发器，右击触发器，在弹出的快捷菜单中选择DROP即可。

Oracle 系统事件触发器、用户事件触发器

系统事件触发器

系统事件触发器是指由数据库系统事件触发的数据库触发器。数据库系统事件通常包括以下几种：

• 数据库的启动（STARTUP）
• 数据库的关闭（SHUTDOWN）
• 数据库服务器出错（SERVERERROR）

创建一个系统事件触发器，数据库启动后记录启动时间。创建系统时间触发器测试表：

create table DB_Log
(op_date timestamp);

创建系统事件触发器：

create or replace trigger tr_db_sstartup
after startup
on database
begin
 insert into DB_Log values(sysdate);
end tr_db_startup;

在创建系统事件触发器时，对于STARTUP和SERVERERROR事件，只可以创建AFTER STARTUP触发器，而对于SHUTDOWN事件，只可以创建BEFORE SHUTDOWN触发器。

SERVERERROR事件的触发器能够与数据库相关联，也能够与特定的模式相关联。与特定模式相关联时，表示只有该模式上的操作所导致的错误才会触发。

用户事件触发器

用户事件触发器指与数据库定义语句DDL或用户的登录/注销等事件相关的触发器。这些事件包括以下语句，并且可以规定触发时间BEFORE或AFTER。

• CREATE
• ALTER
• DROP
• ANALYZE
• ASSOCIATE STATISTICS
• DISASSOCIATE STATISTICS
• AUDIT
• NOTAUDIT
• COMMENT
• GRANT
• REVOKE
• RENAME
• TRUNCATE

LOGOFF事件只可以指定触发时间为BEFORE。SUSPEND和LOGON事件只可以触发时间为AFTER。

创建一个用户事件的触发器，用来记录用户SCOTT所删除的所有对象。

以用户SCOTT身份连接到数据库，并创建一个用户存储信息的表：

CREATE TABLE SCOTT.DROP_OBJ
(object_name varchar2(20),
object_type varchar2(20),
dropped_date date);

创建BEFORE DROP触发器，以便在用户删除对象之前记录到日志信息表DROPPED_OBJECTS中：

CREATE OR REPLACE TRIGGER SCOTT.TR_DROPPED_OBJ
BEFORE DROP
ON SCOTT.SCHEMA
BEGIN
  INSERT INTO SCOTT.DROP_OBJ
  VALUES(ora_dict_obj_name,ora_dict_obj_type,SYSDATE);
END TR_DROPPED_OBJ;

Oracle 替代触发器

INSTEAD OF触发器

INSTEAD OF触发器主要用来对另一个表或视图进行DML操作。与DML触发器不同，在定义替代触发器后，用户对表的DML操作将不再被执行，而是执行触发器主体中的操作。使用替代触发器的一个常用情况是对视图的操作，如果一个试图由多个基表连接而成，则该视图不允许进行INSERT、UPDATE和DELETE这样的DML操作。当在视图上编写替代触发器后，用户对视图的DML操作就不会执行，而是执行触发器中的PL/SQL语句块，这样就可以通过在替代触发器中编写适当的代码，完成对组成视图的各个表进行操作。

使用触发器对基于多个表建立的视图进行更新。创建一个视图，显示学生信息，课程信息和选课信息

create view stu_course_sc as
select s.*,c.*,sc.grade from student s,course c,SC sc
where s.Sno=sc.Sno and c.Cno=sc.Cno;

编写触发器：

create or replace trigger tr_stu_view
instead of update
on stu_course_sc
for each row
begin
  --更新Student表--
  update student set SNAME=:new.SNAME where SNO=:NEW.SNO;
  --更新course表--
  update course set CNAME=:new.CNAME where CNO=:old.CNO;
  --更新sc表--
  update sc set GRADE=:new.GRADE where SNO=:new.SNO and CNO=:old.CNO;
end;

测试：

查看基表：

需要注意的是，视图的更新操作也要遵循基表自身的约束机制。

另外，在创建替代触发器时，没有BEFORE和AFTER关键字，INSTEAD OF总是等同于使用AFTER关键字的行级触发器，它会为每个受到影响的行触发一次，用户不能修改其中的：NEW值。

Oracle DML触发器

语句级触发器

创建语句级触发器，当添加学生时弹出“用户注册窗口”。

create or replace trigger tr_student
before insert on student
begin
  dbms_output.put_line('欢迎新同学！');
end;

执行：

利用触发器实现日志管理功能。该触发器用来实现当在Student表中删除一条数据时，在stu_log表中添加一条包含当前操作用户（USER）和当前操作日期（SYSDATE）的记录。

创建日志表：

create table stu_log(
 WHO varchar2(20),
 WHEN DATE);

创建触发器：

create or replace trigger tr_stu_log
after delete
on student
begin 
  insert into stu_log values(USER,SYSDATE);
end;

测试：

行级触发器

如果在某个表上创建了一个触发器，在对这个表进行DML操作时，每当影响一行数据，该触发器都将被激发执行一次，那么这个触发器就是行级触发器。

在行级触发器中有一个很重要的特点，当创建BEFORE行级触发器时，可以在触发器中引用受到影响的行值，甚至可以用在触发器中设置它们。

创建一个简单的行级触发器，如果对表Student进行DELETE操作，每删除一条符合条件的记录，就显示一次字符串“1行已经被删除”。

create or replace trigger tr_stu_del
after delete
on student
for each row
declare
begin
  dbms_output.put_line('1行已经被删除!');
end;

测试：

需要注意的是，在创建行级触发器时，可以指定一些条件，这样只有当特定的数据受到DML影响时，触发器才被执行。创建触发器时，可以在FOR EACH ROW子句之后使用WHEN子句指定条件。

重新编写上面例子的触发器，要求对SNAME为“张三”进行监视，只有当从表Student中删除SNAME列值为‘张三’的行时，才激发触发器。

create or replace trigger tr_stu_del
after delete
on student
for each row when(old.SNAME='张三')
declare
begin
  dbms_output.put_line('1行已经被删除!');
end;

测试：

在行级触发器中，同样可以使用条件谓词INSERT、UPDATING和DELETING，以判断当前所进行的DML操作。行级触发器通常用于对用户的DML操作进行合法性检查，使得用户修改数据的操作必须按照一定的规则进行。

为了能够比较修改前和修改后的数据，在触发器的可执行代码中，可以使用两个关联行——NEW和OLD。它们为别表示触发器被激发时，当前行的原数据和新数据。：NEW和：OLD也称之为系统变量，由 Oracle 系统管理，存储在内存中，不允许用户直接对其进行修改。：NEW和：OLD变量的结构总是与执行DML操作的表的结构相同。当触发器工作完成以后，这两个变量也随之消失。这两个变量的值是只读的，即用户不能向这两个变量写入内容，但可以引用变量中的数据。

• ：OLD 变量用于DELETE和UPDATE操作所影响的行的副本。当执行DELETE或UPDATE操作时，行从触发表中被删除，并传输到：OLD变量中。
• ：NEW 变量用户存储INSERT和UPDATE操作所影响的行的副本。当执行INSERT或UPDATE操作时，新行被添加到：NEW变量和触发表中，：NEW变量中的行即为触发表中新行的副本。

另外，需要注意的是，在触发器的可执行代码中，如果要通过OLD和NEW引用某个列的值，要在前面加上“：”，在其他地方不能使用“：”。

Oracle 事务

事务

事务是一系列的数据库操作，是数据库应用程序的基本逻辑单元。

概述

事务在数据库中主要用于保证数据的一致性，防止出现错误数据。在事务内部的语句都会被看成一个单元，一旦有一个失败，那么所有的都会失败。在编程过程中也经常用到事务。

事务是一组数据库操作的逻辑工作单元，每个事物都是一个原子单位。在一个事务中可以包含一条或多条DML（数据操纵语言）、DDL（数据定义语言）和DCL（数据控制语言）语句，这些语句组成一个逻辑整体。在事务中包含的数据库操作是不可分割的整体，要么在一起被执行，要么回滚到执行事务之前的状态。对事务的操作有两个：提交（COMMIT）和回滚（ROLLBACK）。提交事务时，对数据库所做的修改便永久写入数据库。回滚事务时，对数据库所做的修改全部撤销，数据库恢复到操作前的状态。事务可用于操作数据库的任何场合，包括应用程序、存储过程和触发器等。

在数据库的应用中，经常需要使用到事务的概念。例如：银行账户之间的汇款转账操作。该操作在数据库中由以下三步完成：

• 源账户减少存储金额，例如减少10000。
• 目标账户增加存储金额，增加10000。
• 在事务日志中记录该事务。

整个交易过程，我们看做一个事务，如果操作失败，那么该事务就会回滚，所有该事务中的操作将撤销，目标账户和源账户上的资金都不会出现变化：如果操作成功，那么将对数据库永久修改，即使季候服务器断点，也不会影响该修改结果。

事务的特性

事务有4个特性，简称ACID属性：

• 原子性（Atomicity）： 事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
• 一致性（Consistency）： 事务把数据库从一个一致性状态带入另一个一致性状态。
• 隔离性（Isolation）： 对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应该以任何方式依赖于或影响其他事务。
• 永久性（Durability）： 事务完成后，它对数据库的修改永久有效，事务日志能够保持事务的永久性。

事务的类型

一个事务中可以包含多条DML语句，或者包含一条DDL语句，或者包含一条DCL语句。事务开始于第一条SQL语句，在下列之一情况结束：

• 遇到COMMIT或ROLLBACK命令。
• 遇到一条DDL或者DCL命令。
• 系统发生错误、退出或者崩溃。

事务是一系列可以把系统带入一个新的状态的操作，如果事务被提交，则数据库进入一个新的状态，否则数据库恢复到事务以前的状态。在数据库中使用事务的好处是首先可以确保数据的一致性，其次对数据做永久修改之前可以预览以前的数据改变，还可以将逻辑上相关的操作进行分组。

控制事务的方式有两种：

• 隐式控制
• 显示控制

隐式控制

该类型的事务没有明确的开始和结束标志。它由数据库自动开启，当一个程序正常结束或使用DDL语言时会自动提交。如果从SQL PLUS非正常退出或发生系统崩溃，那么系统将自动回滚事务。如果设置AUTOCOMMIT为打开状态（默认关闭），则每次执行DML操作都会自动提交。

语法格式如下：

SET AUTOCOMMIT ON/OFF

显示控制

显示方式就是利用commit和rollback命令”显示“地结束事务。

Oracle中的事务不需要设置开始标识，通常遇到登录数据库后，第一次执行DML语句时；或者是当事务结束后，第一次执行DML语句时，事务就开始了。

事务的保存点

在事务中可以根据用户的需要设置保存点。如果让事务回滚到指定位置，需要在事务中预先设置事务保存点（Save Point）。所谓保存点，是指在其所在位置之前的事务语句不能回滚的位置，回滚事务后，保存点之后的事务语句被回滚，但保存点之前的语句依然被有效执行，即不能回滚。

使用SAVEPOINT命令可以设置事务保存点，其基本语法如下：

SAVEPOINT <保存点名>;

需要说明的是，可以将事务的语句分为几个部分，设置多个保存点，这样在实施事务回滚时，可以根据需要回滚事务到不同的保存点位置。

Oracle 锁

锁概述

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

Oracle通过使用锁（Lock）机制维护数据的完整性、并发性和一致性。

Oracle在两个不同级别上提供读取一致性：语句级读取一致性和事务级读取一致性。

• 语句级读取一致性： Oracle总是实施语句级读取一致性，保证单个查询所返回的数据与该查询开始时保持一致。
• 事务级读取一致性： 事务级读取一致性是指在同一个事物中的所有数据对时间点是一致的。

锁的分类

在数据库中有两种基本的锁类型：排他锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。

排他锁， 也称之为写锁。这种模式的锁防止资源的共享，用做数据的修改。假如有事务T给数据A加上该锁，那么其他的事务将不能对A加任何锁，所以此时只允许T对该数据进行读取和修改，直到事务完成将该类型的锁释放为止。

共享锁， 也称之为读锁。该模式锁下的数据只能被读取，不能被修改。如果有事务T给数据A加上共享锁后，那么其他事务不能对其加排他锁，只能加共享锁。加了该锁的数据可以被并发地读取。

锁的类型

• DML锁（Data Locks，数据锁）： 用户保护数据的完整性。
• DDL锁（Dictionary Locks，字典锁）： 用于保护数据库对象的结构，如表、索引等的结构定义。
• 内部所和闩（Internal Locks and Latches）： 保护数据库的内部结构。

DMl锁的目的在于保证并发情况下的数据完整性，在Oracle数据库中，DML锁主要包括TM锁和TX锁， 其中TM锁称为表级锁，TX锁称为事物锁或行级锁。

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志了，只需要检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式， 在数据库中用0~6来表示。

不同的SQL操作产生不同类型的TM锁，如下表

在数据行上只有X锁（排他锁）。在Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生。这些现象都会对实际应用产生极大的危害，如长时间未响应、大量事务失败等。

在日常工作中，如果发现执行SQL语句时，数据库长时间没有响应，很可能是产生了TX锁等待的现象。为了解决这个问题，首先应该找出持锁的事务，然后再进行相关的处理，如提交事务或强行中断事务。

Oracle在动态状态表**V$LOCK** 中存储与数据库中的锁有关的所有信息。查看V$LOCK表的结构如下图：

Oracle 加锁的方法

行共享锁（Row Share ，RS）

对数据表定义了行共享锁后，如果被事务A获得，那么其他事务可以进行并发查询、插入、删除及加锁，但不能以排他方式存取该数据表。

为Student表添加行共享锁

行排他锁（Row Exclusive，RX）

当事务A获得行排他锁后，其他事务可以对同一数据表中的其他数据行进行并发查询、插入、修改、删除及加锁，但不能使用行共享锁、行共享排他锁和行排他锁3种方式加锁。

为Student表添加行排他锁

共享锁（Share，S）

当事务A获得共享锁后，其他事务可以对同一数据表中的其他数据行进行并发查询、加共享锁但不能修改表，不能使用排他锁、行共享排他锁和行排他锁3种方式加锁。

为Student表添加共享锁

第一步：为表Student添加排他锁

第二步：打开一个新的SQL Plus会话，分别执行查询和更新操作

此时，新打开的会话可以查询，但是更新语句一直处于锁等待的状态。回到第一个会话，当解除锁，例如输入rollback回滚操作后，新会话会立刻执行完成。

共享行排他锁（Share Row Exclusive，SRX）

当事务A获得共享排他锁后，其他事务可以执行查询和对其他数据行加锁，但不能修改表，也不能再添加共享锁、共享行排他锁、行排他锁和排他锁。

为Student添加共享行排他锁

第一步：为Student添加共享行排他锁

第二步：新会话中为student添加共享锁

发现，新打开的会话窗口，为已经添加共享排他锁的事务再次添加共享锁时，一直处于等待状态，当第一个会话释放资源时，第二个会话方可执行。

排他锁（Exclusive，X）

排他锁是最严格的锁，当事务A获得排他锁后，事务A可以执行对数据表的读写操作，其他事务可以执行查询但不能执行插入、修改和删除操作。

其他事务可以执行查询和对其他数据行加锁，但不能修改表，也不能再添加共享锁、共享行排他锁，行排他锁和排他锁。

为Student表添加排他锁

第一步：为表Student添加排他锁

第二步：打开一个新的SQL Plus会话，分别执行查询和更新操作

此时，新打开的会话可以查询，但是更新语句一直处于锁等待状态。回到第一个会话，解除锁，新会话立刻执行完成。

Oracle 使用DML语句处理数据及控制事务

数据操作语言

DML（Data Manipulation Language - 数据操作语言）可以在下列条件执行：

• 向表中插入数据
• 修改现存数据
• 删除现存数据

事务是由完成若干项工作的DML语句组成的。

插入数据

insert语句语法：

insert into table [(column [, column...]}]
values (value [, value...]);

使用这种语法一次只能向表中插入一条数据。

• 为每一列添加一个新值。
• 按列的默认顺序列出各个列的值。
• 在insert子句中随意列出列名和它们的值。
• 字符和日期型数据应包含在单引号中。

insert into departments(department_id, department_name, manager_id, location_id)
values(17, 'Public Relations', 100, 1700);

1 row created.

向表中插入空值

• 隐式方式：在列名表中省略该列的值。

  insert into department (department_id, department_name)
  values (30, 'Purchasing');

• row created.

• 显示方式：在VALUES子句中指定空值。

  insert into departments
  values (100, 'Finance', NULL, NULL);

• row created.

插入指定的值

SYSDATE记录当前系统的日期和时间

insert into employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205,100);

加入新成员

insert into employees
values (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3,1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30);

1 row created.

创建脚本

• 在SQL语句中使用 & 变量指定列值。
• & 变量放在VALUES子句中。

insert into departments (department_id, department_name, location_id)
values(&department_id, '&department_name', &location);

从其它表中拷贝数据

• 在INSERT语句中加入子查询。

  insert into sales_reps(id, name, salary, commission_pct)
  select employee_id, last_name, salary, commission_pct
  from employees
  where job_id like '%REP%';

• rows created.

• 不必书写VALUES子句
• 子查询中的值列表应与INSERT子句中的列名对应

更新数据

• 使用UPDATE语句更新数据

  update table
  set column = value[, column = value, ...]
  [where condition];

• 可以一次更新多条数据

• 使用where子句指定需要更新的数据

  update employees
  set department id = 70
  where employee_id = 113;

• row updated.

• 如果省略where子句，则表中的所有数据都将被更新

  update copy_emp
  set department_id = 110;

• rows updated.

在update语句使用子查询

示例：更新114号员工的工作和工资使其与205号员工相同

update employees
set job_id = (select job_id
    from employees
    where employee_id = 205), salary = (select salary
        from employees
        where employee id = 205)
where employee_id = 114;

1 row updated.

在update语句中使用子查询

• 在update中使用子查询，使更新基于另一个表中的数据

  update copy_emp
  set department_id = (select department_id
    from employees
    where employee_id = 100)
  where job_id = (select job_id
    from employees
    where employee_id = 200);

• row updated.

更新中的数据完整性错误

update employees
set department_id = 55
where department_id = 110;

不存在55号部门

使用DELETE语句从表中删除数据

delete [from] table
[where condition];

• 使用where子句指定删除的记录

  delete from departments
  where department_name = 'Finace';

• row deleted.

• 如果省略where子句，则表中的全部数据将被删除

  delete from copy_emp;

• rows deleted.

在DELETE中使用子查询

• 在delete中使用子查询，使删除基于另一个表中的数据

  delete from employees
  where department_id = (select department_id
    from departments
    where department_name like '%Public%');

• row deleted.

删除中的数据完整性错误

delete from departments
where department_id = 60;

Delete 和 Truncate

• 都是删除表中的数据
• Delete操作可以rollback，可以闪回
• Delete操作可能产生碎片，并且不释放空间
• Truncate：清空表

控制事务

回滚到保留点

• 使用SAVEPOINT 语句在当前事务中创建保存点
• 使用 ROLLBACK TO SAVEPOINT语句回滚到创建的保存点

update...
savepoint update done;

Savepoint created.

insert...
rollback to update_done;

Rollback complete.

数据库的隔离级别

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

脏读： 对于两个事务T1，T2， T1读取了已经被T2更新但还没有提交的字段。之后，若T2回滚，T1读取的内容就是临时且无效的。

不可重复读：对于两个事务T1， T2， T1读取了一个字段，然后T2更新了该字段。之后，T1再次读取同一个字段，值就不同了。

幻读：对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行。

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，是他们不会相互影响，避免各种并发问题。

一个事务与其它事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性越好，但并发性越弱。

数据库提供的4种事务隔离级别

Oracle支持的2种事务隔离级别：

• READ COMMITED
• SERIALIZABLE
• Oracle默认的事务隔离级别为：READ COMMITED
• MySQL默认的事务隔离级别为：REPEATABLE READ

总结

Oracle PL/SQL存储过程、存储函数

存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

存储过程语法：

create [or replace] PROCEDURE 过程名 [(参数名 in/out 数据类型)]
AS 
begin
    PLSQL子程序体;
end;

或者

create [or replace] PROCEDURE 过程名 [(in/out 数据类型)]
is 
begin
    PLSQL子程序体：
end 过程名;

示例：打印hello world

create or replace procedure sayhelloworld
as
begin
  dbms_output.put_line('hello world');
end;

调用：

方式一：直接调用
SQL> exec sayhelloworld;

方式二：在另一个PL/SQL中调用
begin
    syahelloworld();
end;

带参数的存储过程

示例：给指定的员工涨100工资，并打印出涨前和涨后的工资

create or replace procedure raiseSalary(eno in number)
is
       --定义变量保存涨前的薪水
       psal emp.sal%type;
begin
       --得到涨前的薪水
       select sal into psal from emp where empno=eno;
       
       --涨100
       update emp set sal=sal+100 where empno=eno;
       
       --要不要commit？
       
       dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));
end raiseSalary;

调用：

begin
  raiseSalary(eno => 7902);
  commit;
end;

存储函数

函数（Function）为一命名的存储程序，可以带参数，并返回一个计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

建立存储函数的语法：

CREATE [OR REPLACE] FUNCTION 函数名（参数列表）
RETURN 函数值的类型
AS
    PLSQL子程序体；
END[函数名];

存储过程和存储函数的区别：

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

示例：查询某个员工的年薪

create or replace function queryEmpIncome(eno in number) 
return number
is
       --定义变量保存月薪和奖金
       psal emp.sal%type;
       pcomm emp.comm%type;
begin
       --得到月薪和奖金
       select sal,comm into psal,pcomm from emp where empno=eno; 
       
       --返回年收入
       return psal*12+nvl(pcomm,0);

end queryEmpIncome;

调用：

begin
  :result := queryempincome(7839);
end;

使用存储过程替换上面的示例：

create or replace procedure empincomep(eno in emp.empno%type, income out number)
is
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  select t.sal, t.comm into psal, pcomm from emp t 
  where t.empno = eno;
    income := psal*12+nvl(pcomm,0);
end empincomep;

调用：

declare
  income number;
begin
  empincomep(7369, income);
  dbms_output.put_line(income);
end;

Oracle PL/SQL例外

例外

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

系统定义例外

• No_data_found：没有找到数据
• Too_many_rows：select …into语句匹配多个行
• Zero_Divide：被零除
• Value_error：算数或转换错误
• Timeout_on_resource：在等待资源时发生超时

示例：被0除

declare
  pnum number;
begin
  pnum := 1/0;
  
exception
  when zero_divide then dbms_output.put_line('1: 0不能做分母');
                        dbms_output.put_line('2: 0不能做分母');
  when others then dbms_output.put_line('其他例外');
end;

自定义例外

用户可以自定义异常，在声明中来定义异常

DECLARE
    自定义异常名 exception;

DECLARE
    My_job char(10);
    v_sal emp.sal%type;
    No_data exception;
    cursor c1 is select distinct job from emp order by job;

示例：查询部门号是50的员工

declare
  no_emp_found exception;
  cursor pemp is
    select t.ename from emp t where t.deptno = 50;
  pename emp.ename%type;
begin
  open pemp;
  fetch pemp
    into pename;
  if pemp%notfound then
    raise no_emp_found;
  end if;
  close pemp;
exception
  when no_emp_found then
    dbms_output.put_line('没有找到员工');
  when others then
    dbms_output.put_line('其他错误');
end;

Oracle 使用Java程序调用存储过程

创建工程并导入Oracle的驱动jar包

创建一个JDBCUtils工具类

package pers.zhang.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {

    private static String driver = "oracle.jdbc.OracleDriver";
    private static String url = "jdbc:oracle:thin:localhost/orcl";
    private static String user = "scott";
    private static String password = "123456";
    
    static{
        //注册驱动
        //DriverManager.registerDriver(driver)
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /*
     * 运行Java：
     * java -Xms100M -Xmx200M HelloWorld
     * 
     * 技术方向：
     * 1、性能优化
     * 2、故障诊断：死锁(JDK: ThreadDump)
     *               Oracle: 自动处理
     */
    public static void release(Connection conn,Statement st,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                rs = null; ///-----> 原因：Java GC: Java的GC不受代码的控制
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                st = null;
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                conn = null;
            }
        }
    }
}

实现过程的调用

过程定义：

--统计年薪的过程
create or replace procedure proc_countyearsal(eno in number, esal out number)
as
begin
  select sal * 12 + nvl(comm, 0) into esal from emp where empno = eno;
end;  

--调用
declare
   esal number;
begin
  proc_countyearsal(7839,esal);
  dbms_output.put_line(esal);
end;

过程调用：

    @Test
    public void testProcedure01() {
        Connection conn = null;
        CallableStatement callSt = null;
        
        try {
            conn = JDBCUtils.getConnection();
            callSt = conn.prepareCall("{call proc_countyearsal(?,?)}");
            
            //对于in参数赋值
            callSt.setInt(1, 7839);
            //对于out参数声明
            callSt.registerOutParameter(2, OracleTypes.NUMBER);
            
            callSt.execute();
            
            System.out.println(callSt.getObject(2));
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

运行JUnit测试输出：

60000

游标引用的Java测试

定义过程，并返回引用型游标

--定义过程
create or replace procedure proc_cursor_ref (dno in number, empList out sys_refcursor)
as
begin
  open empList for select * from emp where deptno = dno;
end;

--在pl/sql中调用
declare
  mycursor_c sys_refcursor;
  myempc emp%rowtype;
begin
  proc_cursor_ref(20,mycursor_c);
  loop
    fetch mycursor_c into myempc;
    exit when mycursor_c%notfound;
    dbms_output.put_line(myempc.empno || ',' || myempc.ename);
  end loop;
  close mycursor_c;
end;

java代码调用游标类型的out参数:

    @Test
    public void testFunction01() {
        Connection conn = null;
        CallableStatement callSt = null;
        ResultSet rs = null;
        
        try {
            conn = JDBCUtils.getConnection();
            callSt = conn.prepareCall("{call proc_cursor_ref(?,?)}");
            
            callSt.setInt(1, 20);
            callSt.registerOutParameter(2, OracleTypes.CURSOR);
            
            callSt.execute();
            
            rs = ((OracleCallableStatement)callSt).getCursor(2);
            while(rs.next()) {
                System.out.println(rs.getObject(1) + "," + rs.getObject(2));
            }
            
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

运行JUnit测试输出：

7369,SMITH
7566,JONES
7788,SCOTT
7876,ADAMS
7902,FORD

来源：Oracle 数据库很难么？带你从头到尾捋一遍，不信你学不会！