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

Oracle 数据库体系结构

数据库

Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。

实例

一个Oracle实例（Oracle Instance）由一系列的后台进程（Backguound Processes）和内存结构（Memory Structures）组成。一个数据库可以有n个实例。

数据文件（dbf）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件之只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属的表空间才行。 如果要删除某个数据文件，只能删除其所属的表空间才行。如果要删除某个数据文件，只能删除其所属的表空间才行。

表空间

表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少由一个表空间（称之为sys表空间）。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件（datafile）。一个数据文件只能属于一个表空间。一个数据文件只能属于一个表空间。一个数据文件只能属于一个表空间。

用户

用户是在实例下建立的。不同实例中可以建相同名字的用户。

表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于Oracle的数据库不是普通的概念，Oracle是有由户和表空间对数据进行管理和存放的。但是表不是由表空间去查询的，而是由用户去查的。因为不同的用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

Oracle 常用数据库对象

视图

什么是视图？

• 视图是一个虚表.
• 视图建立在已有表的基础上，视图赖以建立的这些表称为基表.
• 向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句.
• 视图向用户提供基表数据的另一种表现形式.
• 最大的优点就是简化复杂的查询。

视图的优点

创建视图的语法

create [or replace] [force|noforce] view viewName
 [(alias[, alisa]...)]
[with check option [constraint constraint]]
[with read only [constraint constraint]];


• force：子查询不一定存在
• noforce：子查询存在（默认）
• with read only：只能做查询操作
• 子查询可以是复杂的select语句

示例：建立一个视图，此视图包括了20号部门的全部员工信息

create view empView20 as select * from emp t where t.deptno=20;
select * from empView20;


语法2：

create or replace view 视图名称 as 子查询


如果视图已经存在我们可以使用语法2来创建视图，这样已有的视图会被覆盖。

create or replace view wmpView20 as select * from emp t where t.deptno=10;

简单视图和复杂视图

不建议通过视图对表中数据进行修改，因为会受到很多的限制。

创建复杂视图

示例：查询各个部门的最低工资，最高工资，平均工资:

create view dept_sum_vu
 (name, minsal, maxsal, avgsal)
as select d.department_name, MIN(e.salary),
    MAX(e.salary), AVG(e.salary)
 from employees e, departments d
 where e.department_id = d.department_id
 group by d.department_name;


视图中使用DML的规定

• 可以在简单视图中执行DML操作

• 当视图定义中包含以下元素之一时不饿能使用delete：

• 1.组函数
• 2.GROUP BY 子句
• 3.DISTINCT 关键字
• 4.ROWNUM 伪列

• 当视图定义中包含以下元素之一时不能使用update：

• 1.组函数
• 2.GROUP BY 子句
• 3.DISTINCT 关键字
• 4.ROWNUM 伪列
• 5.列的定义方式为表达式

• 当视图定义中包含以下元素之一时不能使用insert：

• 1.组函数
• 2.GROUP BY 子句
• 3.DISTINCT 关键字
• 4.ROWNUM 伪列
• 5.列的定义方式为表达式
• 6.列中非空的列在视图定义中未包括

屏蔽DML操作

• 可以使用WITH READ ONLY 选项屏蔽对视图的DML操作
• 任何DML操作都会返回一个Oracle server 错误

删除视图

删除视图只是删除视图的定义，并不会删除基本表的数据。

drop view vie;

序列

在很多数据库中都存在一个自动增长的列，如果现在要想在oracle中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理。并且Oracle将序列值装入内存可以提高访问效率。

序列：可供多个用户用来产生唯一数值的数据库对象：

• 自动提供唯一的数值
• 共享对象
• 主要用于提供主键值
• 将序列值装入内存可以提高访问效率

语法：

create sequence 序列名
 [increment by n]
 [start with n]
 [{MAXVALUE n| NOMAXVALUE}]
 [{MINVALUE n| NOMINVALUE}]
 [{CYCLE|NOCYCLE}]
 [{CACHE n| NOCACHE}];


示例：

create sequence dept_deptid_seq
 increment by 10
 start with 120
 maxvalue 9999
 nocache
 nocycle;

查询序列

查询数据字典视图USER_SEQUENCES获取序列定义信息：

select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;


• 如果指定NOCACHE选项，则列LAST_NUMBER显示序列中下一个有效的值

序列创建完成之后，所有的自动增长应该由用户自己处理，所以在序列中提供了以下的两种操作：

• nextval：取得序列的下一个内容
• currval：取得序列的当前内容

在插入数据时需要自增的主键中可以这样使用:

序列可能产生裂缝的原因：

• 回滚
• 系统异常
• 多个表公用一个序列

修改序列

修改序列的增量，最大值，最小值，循环选项或是否装入内存。

alter sequence dept_deptid_seq
 increment by 20
 maxvalue 999999
 nochche
 noccle;


修改序列的注意事项

• 必须时序列的拥有者或对序列有ALTER权限
• 只有将来的序列值会被改变
• 改变序列的初始值只能通过删除序列之后重建序列的方法实现

删除序列

• 使用drop sequence 语句删除序列
• 删除之后，序列不能再次被引用

drop sequence dept_deptid_seq;

索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o的次数，从而提高数据访问性能。

• 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
• 索引被删除或损坏，不会对 表产生影响，其影响的只是查询的速度
• 索引一旦建立，Oracle管理系统会对其进行自动维护，而且由Oracle管理系统决定何时使用索引，用户不用再查询语句中指定使用哪个索引
• 再删除一个表时，所有基于该表的索引会自动被删除
• 通过指针加速Oracle服务器的查询速度
• 通过快读定位数据的方法，减少磁盘 I/O

单列索引

单列索引是基于单个列所建立的索引，比如：

create index 索引名 on 表明(列名);


复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，例如：

create index emp_idxl on emp(ename, job);
create index emp_idx1 on emp(job, ename);


示例：给person表的name属性建立索引

create index pname_index on person(name);


示例：给person表创建一个name和gender的索引

create index pname_gender_index on person(name, gender);


什么时候创建索引

• 列中数据值分布范围很广
• 列经常在where子句或连接条件中出现
• 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%

什么时候不要创建索引

• 表很小
• 列不经常作为连接条件或出现在where子句中
• 查询的数据大于2%到4%
• 表经常更新

查询索引

可以使用数据字典视图USER_INDEXES和USER_IND_COLUMNS查看索引的信息。

删除索引

• 使用DROP INDEX命令删除索引

drop index indexName;


• 删除索引UPPER LAST NAME IDX

drop index upper_last_name_idx;

• 只有索引的拥有者或拥有drop any index 权限的用户才可以删除索引

同义词

• 方便范文其它用户的对象
• 缩短对象名字的长度

create [public] SYNONYM synonym
for object;


创建同义词

示例：为视图DEPT_SUM_VU创建同义词

create synonym d_sum
for dept_sum_vu;

删除同义词

drop synonym d_sum;

Oracle 基本查询详解

sql简介

结构化查询语言（Structured Query Language）简称SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统：同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存取方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以实用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言可以嵌套，这使得它具有极大的灵活性和强大的功能。

DML（数据库操作语言）

其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

DDL（数据库定义语言）

其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREATE TABLE 或 DROP TABLE）：为表加入索引等。DDL包括许多与数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

DCL（数据库控制语言）

它的语句通过GRANT 或 REVOKE 获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

Select语句的语法格式

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;


当前用户下的表

select * from tab;


查询表结构

SQL> desc emp


查询所有员工信息

select * from emp;


通过列名查询

SQL优化：使用列名查询效率高于使用*查询。

select empno,ename,job,mgr,hiredate,sal,comm,deptno
from emp;


别名的用法

别名的用法共有三种:

• as “xxx”
• “xxx”
• xxx

三种写法的区别：

• 其中第1种和第2种完全相同。
• 第3种写法无法包含空格、无法为纯数字

select empno as "员工号",ename "雇员名",job 工作 
from emp;


消除重复数据

注意：distinct关键字作用于后面所有的列，即只要列组合起来唯一即可。

select distinct job from emp;


查询中的四则运算

查询雇员的月薪和年薪

select ename,sal,sal*12 年薪 from emp;


空值问题

什么空值：

• 空值是无效的，未指定的，未知的或不可预知的值。
• 空值不是空格或者0.

查询雇员的姓名、月薪、奖金、年薪和年收入（年薪+奖金）

select ename,sal,comm,sal*12 年薪,sal*12+comm 年收入
from emp;


由上图可知，查询结果显然是不对的。因为不可能出现年收入为空值的情况。进一步观察可以发现，年收入为空值的奖金也是空值。

SQL中的null：

• 包含null的表达式都为空
• null永远!=null

解决方法：滤空函数 nvl 和nvl2

• nvl
  NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。
• nvl2
  NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。

sql修改为：

select ename,sal,comm,sal*12 年薪,sal*12+nvl(comm,0) 年收入
from emp;


查询空值

查询奖金为null的雇员信息

select * from emp where comm is null;


连接符 ||

连接符：

• 把列与列，列与字符连接在一起。
• 用“ || ” 表示。
• 可以用来‘合成’列。

字符串：

• 字符串可以是SELECT列表中的一个字符，数字，日期。
• 日期和字符只能在单引号中出现。
• 每当返回一行时，字符串被输出一次。

查询员工信息：的薪水是

select ename||'的薪水是'||sal 信息 from emp;


Oracle 条件查询、模糊查询和排序

使用where语句对结果进行过滤

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
WHERE condition(s)};


比较运算符其它比较运算符逻辑运算符

Where语句示例

字符和日期

• 字符和日期要包含在单引号中。
• 字符大小写敏感，日期格式敏感。
• 默认的日期格式是DD−MON−RR。

示例：查询名叫KING的员工

select * from emp where ename='KING';


示例：查询入职日期是17-11月-81的员工

select * from emp where hiredate='17-11月-81';


非空和空的限制

• 示例：查询每月能得到奖金的雇员
• 分析：只要字段中存在内容表示不为空，如果不存在内容就是null。
• 语法：列名 IS NOT NULL
• 为空：列名 IS NULL

select * from emp where sal is not null;


• 示例：查询工资大于1500或者有奖金的雇员。
• 分析：多个查询条件或满足，条件之间使用“OR”

select * from emp where sal > 1500 or comm is not null;


• 示例：查询工资不大于1500和没有奖金的人
• 语法：NOT（查询条件）

select * from emp where not(sal > 1500) and comm is null;


范围限制

• 示例：基本工资大于1500但是小于3000的全部雇员
• 分析：sal>1500, sal1500 and sal=1500 and sal
              (select sal
              from emp
              where empno=7782);
  

  示例2：

  select ename,job,sal
from emp
where sal=
     (select min(sal)
     from emp);


  示例3：

  select deptno,min(sal)
from emp
group by deptno
having min(sal) >
      (select min(sal)
      from emp
      where deptno=20);


  示例4：查询出比雇员7654的工资高，同时从事和7788的工作一样的员工。

  select *
from emp t1
where t1.sal>
      (select t.sal 
      from emp t 
      where t.empno=7654) and t1.job=
            (select t2.job
            from emp t2
            where t2.empno=7654);


  示例5：要求查询每个部门的最低工资和最低工资的雇员和部门名称

   select d.dname, a.minsal, e.ename
 from dept d,
      (select deptno, min(sal) minsal 
      from emp
      group by deptno) a,emp e
 where d.deptno=a.deptno and e.sal=a.minsal;


  

  非法使用单行子查询示例

  select empno,ename
from emp
where sal=
     (select min(sal)
     from emp
     group by deptno);


  

  多行子查询

  • 返回了多条记录
  • 多行操作符

  示例：in 在集合中。查询部门名称是SALES和ACOUNTING的员工。

  select * from emp where deptno in
       (select deptno from dept where dname='SALES' or dname='ACCOUNTING');


  

  示例：any 和集合中的任意一个值比较 。查询工资比30号部门员工高的员工信息。

  select * from emp where sal > any
       (select sal from emp where deptno=30);


  示例：all 和集合中的所有值比较。查询工资比30号部门所有员工高的员工信息。

  select * from emp where sal > all
       (select sal from emp where deptno=30);


  

  子查询需要注意的问题

  • 括号

  • 合理的书写风格

  • 可以在主查询的where、select、having、from后面使用子查询

  • 不可以在group by使用子查询

  • 强调from后面的子查询

  • 主查询和子查询可以不是同一张表；只有子查询返回的结果 主查询可以使用 即可

  • 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序

  • 一般先执行子查询，再执行主查询；相关子查询列外

  • 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

  • 子查询中的null

  • 示例：在select语句后使用子查询

  • 注意：select语句后的子查询必须是单行子查询

  查询员工号，姓名，薪水，7839的职位

  select empno,ename,sal,
       (select job 
       from emp 
       where empno=7839)
from emp;


  示例：在from语句后面使用子查询。查询员工信息：员工号，姓名，月薪。

  select * 
from (select empno, ename, sal from emp);


  示例：top-n分析问题，rownum行号：

  • rownum永远按照默认的顺序生成
  • rownum只能使用小于等于，不能使用大于等于

  查询员工表中工资最高的前三名

  select rownum, empno, ename, sal
from (select * from emp order by sal desc)
where rownum  d.avgsal;


  

  相关子查询

  将主查询中的值 作为参数传递给子查询。使用相关子查询解决上面的示例。

  select empno, ename, sal, (select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno);


  示例：统计每年入职员工的个数

  select count(*) Total,
       sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) "1980",
       sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) "1981",
       sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) "1982",
       sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) "1987"
from emp;


  

  子查询中的null值问题

  • 单行子查询中的null值问题
    

  • 多行子查询中的null值问题

  示例：查询不是老板的员工
  查询错误，集合中有null值不可以使用not in。

  Oracle 集合运算

  什么是集合运算？

  

  并集

  

  示例：查询10号和20部门的员工

  select * from emp where deptno=10
union
select * from emp where deptno=20;


  

  交集

  示例：显示薪水同时位于级别1（700 ~ 1300）和级别2（1201 ~ 1400）的员工信息。

  select ename,sal from emp where sal between 700 and 1300 
intersect
select ename,sal from emp where sal between 1201 and 1400; 


  

  差集

  示例：显示薪水同时位于级别1（700 ~ 1300），但不属于级别2（1201 ~ 1400）的员工信息。

  select ename,sal from emp where sal between 700 and 1300 
minus
select ename,sal from emp where sal between 1201 and 1400; 


  

  集合运算的注意事项

  • select语句中参数类型和个数要一致
  • 可以使用括号改变集合执行的顺序
  • 如果有order by子句，必须放到最后一句查询语句后
  • 集合运算采用第一个语句的表头作为表头

  Oracle PL/SQL基本语法、判断、循环

  什么是PL/SQL？

  PL/SQL（Procedure Language/SQL）是Oracle对sql语言的过程化扩展，指在SQL命令语言种增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

  示例：为职工涨工资，每人涨10%的工资。

  update emp set sal = sal * 1.1;


  示例：按职工的职称涨工资，总裁涨1000元，经理涨800元，其他人员涨400元。

  这样的需求我们就无法使用一条SQL来实现，需要借助其他程序来帮助完成，也可以使用pl/sql。

  PL/SQL的语法

  declare
 说明部分（变量说明，光标声明，例外说明）
begin
 语句序列（DML语句）...
exception
 列外处理语句
End；
/


  变量和变量的定义

  • 说明变量（char, varchar2, date, number, boolean, long）
    

  • 引用变量

  Myname emp.ename%type;


  引用型变量，即my_name的类型与emp表种ename列的类型一样，在sql种使用into。

  declare
 emprec emp.ename%type;
begin
 select t.ename into emprec from emp t where t.empno = 7369;
 dbms_output.put_line(emprec);
end;


  • 记录型变量

  Emprec emp%rowtype


  记录变量分量的引用

  emp_rec.ename := 'ADAMS';


  declare
 p emp%rowtype;
begin
 select * into p from emp t where t.empno = 7369;
 dbms_output.put_line(p.ename || ' ' || p.sal);
end;


  

  If语句

  语法1：

  IF 条件 THEN 语句1：
 语句2：
END IF；


  语法2：

  IF 条件 THEN 语句序列1：
 ELSE 语句序列2：
END IF；


  语法3：

  IF 条件 THEN 语句：
 ELSIF 语句序列2：
 ELSE 语句：
END IF；


  示例1：如果从控制台输入1则输出我是1

  declare
 pnum number := #
begin
 if pnum = 1 then
  dbms_output.put_line('我是1');
 end if;
end;


  示例2：如果从控制台输入1则输出我是1，否则输出我不是1

  declare
  mynum number := #
begin
  if mynum = 1 then
    dbms_output.put_line('我是1');
  else
    dbms_output.put_line('我不是1');
  end if;
end;


  示例3：判断人的不同年龄段，18岁以下是未成年人，18以上40以下是中年人，40以上是老年人。

  declare
  mynum number := #
begin
  if mynum = 18 and mynum  40 then
    dbms_output.put_line('老年人');
  end if;
end;


  循环

  语法：
  示例1：使用语法1输出1到10的数字

  declare
  step number := 1;
begin
  while step  10;
    dbms_output.put_line(step);
    step := step + 1;
  end loop;
end;


  示例3：使用语法3输出1到10的数字

  declare
  step number := 1;
begin
  for step in 1 .. 10 loop
    dbms_output.put_line(step);
  end loop;
end;


  Oracle PL/SQL游标（光标）

  在写Java程序中有集合的概念，那么在pl/sql中也会用到多条记录，这时候就需要用到游标，游标可以存储查询返回的多条数据。

  游标的属性

  语法

  CURSOR 游标名 [(参数名 数据类型，参数名 数据类型，...)] IS SELECT 语句；


  例如：

  cursor c1 is select ename from emp;


  游标的使用步骤：

  • 打开游标： open c1;（打开游标执行查询）
  • 取一行游标的值：fetch c1 into pjob;（取一行到变量中）
  • 关闭游标：close c1；（关闭游标释放资源）
  • 游标的结束的方式：exit when c1%notfound

  注意：上面的pjob必须与emp表中的job列类型一致

  • 定义：pjob emp.empjob%type;

  示例1：使用游标方式输出emp表中的员工编号和姓名

  declare
  cursor pc is
     select * from emp;
  pemp emp%rowtype;
begin
  open pc;
  loop
    fetch pc
      into pemp;
    exit when pc%notfound;
    dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
  end loop;
  close pc;
end;


  示例2：写一段PL/SQL程序，为部门号为10号的员工涨工资

  declare
  cursor pc(dno emp.deptno%type) is
     select empno from emp where deptno = dno;
  pno emp.empno%type;
begin
  open pc(20);
  loop
    fetch pc
      into pno;
    exit when pc%notfound;
    update emp t set t.sal = t.sal + 1000 where t.empno = pno;
  end loop;
  close pc;
end;


  Oracle 存储过程概述、创建和执行

  存储过程

  存储过程是Oracle开发者在数据转换或查询报表时经常使用的方式一。存储过程是一种命名PL/SQL程序块，它将一些相关的SQL语句、流程控制语句组合在一起，用于执行某些特定的操作或者任务，可以将经常需要执行的特定的操作写成过程。通过过程名，就可以多次调用过程，从而实现程序的模块化设计。这种方式极大地节省了用户的时间，也提高了程序的效率。

  概述

  在Oracle中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这种子程序称为存储过程（Procc-Dure）。存储过程可以通俗地理解为是存储在数据库服务器中的封装了一段或多段SQL语句的PL/SQL代码块。在数据库中有一些是系统默认的存储过程，那么可以直接通过存储过程的名称进行调用。另外，存储过程还可以在编程语言中调用，如Java、C#等等。

  存储过程优点：

  • 存储过程在服务器端运行，执行速度快。
  • 存储过程执行一次后驻留在Oracle数据库服务器的高速Cache中，以后再次执行存储过程时，只需从高速Cache中调用已经编译好的代码即可，从而提高了系统性能。
  • 存储过程确保了数据库的安全。使用存储过程，可以在禁止用户直接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的存储过程。
  • 自动完成需要预先执行的任务。存储过程可以设置为系统启动时自动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。

  创建存储过程

  CREATE [OR REPLACE] PROCEDURE procedure_name
[Parameter [IN | OUT | IN OUT] data_type1,
Parameter [IN | OUT | IN OUT] data_type2,
......]
IS|AS
 声明部分
BEGIN
 执行部分
EXCEPTION
 异常处理部分
END procedure_name;


  • OR REPLACE: 表示如果指定的过程已经存在，则覆盖同名的存储过程。
  • Procedure_name: 表示存储过程的名称。
  • Parameter: 表示存储过程中的参数。
  • IN: 表示向存储过程传递参数。
  • OUT: 表示从存储过程返回参数。
  • Date_type: 表示传入参数的数据类型，也可以带有默认值。
  • AS或IS 后声明的变量主要用于过程体内，且不能加DECLARE语句。

  执行存储过程

  语法：

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

  方式二：在另一个PL/SQL中调用
  begin
   Procedure_name(parameter1,parameter2,...);
  end;

  示例：创建一个简单的存储过程Insert_student，该过程可用于向Student表添加数据。表信息如下：
  创建存储过程：

  create or replace procedure Insert_student is
begin
  insert into Student
  values(5,'二狗','男',28,'体育学院');
  exception
    when dup_val_on_index then
      dbms_output.put_line('重复的学生ID');
    when others then
      dbms_output.put_line('发生其它错误！');
end Insert_student;


  执行存储过程：

  SQL> set serveroutput on;  --保证DBMS_OUTPUT.PUT_LINE成功输出
SQL> exec Insert_student;
重复的学生ID
PL/SQL procedure successfully completed


  查看存储过程

  存储过程一旦被创建就会存储到数据库服务器上，Oracle通过使用视图USER_SOURCE查看已经存在的存储过程脚本。

  查看存储过程Insert_student的脚本
  user_procedures视图的部分类说明

  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 2000;


  

  WHERE和HAVING的区别

  最大区别在于：where后面不能有组函数

  多个列的分组

  • 示例：按照部门不同的职位，统计工资的总和
  • 分析：先按照第一个列分组，如果相同，再按第二个列分组，以此类推

  select deptno, job, sum(sal) from emp group by deptno,job;


  

  Oracle 使用DDL语句管理表、约束

  创建表空间

  表空间是Oracle数据库的逻辑单元。数据库–表空间。一个表空间可以与多个数据库文件（物理结构）关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

  create tablespace zhangs
datafile 'd:zhangs.dbf'
size 100m
autoextend on
next 10m;


  • zhangs：为表空间名称
  • datafile：指定表空间对应的数据文件
  • size：定义的是表空间的初始大小
  • autoextend on：自动增长，当表空间存储都占满时，自动增长
  • next：指定的时一次自动增长的大小

  用户

  创建用户

  create user acton
identified by 930205
default tablespace zhangs;


  • identified by：用户的密码
  • default tablespace：表空间名称

  Oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

  用户赋权限

  新创建的用户没有任何全新，登录后会提示。Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。

  CONNECT角色

  是授予最终用户的典型权力，最基本的

  ALTER SESSION：修改会话  
CREATE CLUSTER：建立聚簇  
CREATE DATABASE LINK：建立数据库连接  
CREATE SEQUENCE：建立序列  
CREATE SESSION：建立会话  
CREATE SYNONYM：建立同义词  
CREATE VIEW：建立视图


  RESOURCE角色

  是授予开发人员的

  CREATE CLUSTER：建立聚簇  
CREATE PROCEDURE：建立过程  
CREATE SEQUENCE：建立序列  
CREATE TABLE：建表  
CREATE TRIGGER：建立触发器  
CREATE TYPE：建立类型


  DBA角色

  拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意表，包括删除。

  grant dba to acton;


  进入system用户下给用户赋予dba权限，否则无法正常登录。

  创建表

  语法：

  create table [schema.] table
 (column datatype [default expr][, ...]);


  数据类型：使用子查询创建表的语法：

  create table t_table
 [(column, column...)]
as subquery;

  //如果只想创建表结构，不需要表中的数据，可以给一个永远为假的条件
  create table t_table1 as
  select * from t_table2 where 1=2;

  示例：创建person表：

  create table person(
       pid number(10),
       name varchar2(10),
       gender number(1) default 1,
       birthday date);

  insert into person(pid,name,gender,birthday)
  values(1,'张三',1,to_date('1999-12-22','yyyy-MM-dd'));

  select * from person;

  

  修改表

  在sql中使用alter可以修改表

  • 添加语法：

  alter table 表名称 add(类名1 类型 [default 默认值]， 列名2 类型 [default 默认值]...)


  • 修改语法：

  alter table 表名称 modify(列名1 类型 [default 默认值]，列名2 类型 [default 默认值]...)


  • 修改列名：

  alter table 表名称 rename column 列名1 to 列名 2


  示例：在person表中增加列address

  alter table person add(address varchar2(10));


  示例：把person表的address列的长度修改成20

  alter table person modify(address varchar2(20));


  删除表

  drop table 表名


  约束

  在数据库开发中，约束是必不可少的，使用约束可以更好的保证数据库的完整性。在Oracle数据库中，约束的类型包括：

  • 主键约束：Primary Key
  • 非空约束：Not Null
  • 唯一约束：Unique
  • 外键约束：Foreign Key
  • 检查性约束 Check

  主键约束

  主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。创建一张表，把pid作为主键。

  create table person(
 pid number(10) primary key,
 name varchar2(10),
 gender number(1) default 1,
 birthday date);


  主键不可重复，SCOTT.SYSC0017981是系统自动分配的约束名字主键不可为空
  我们可以自己来指定主键约束的名字

  create table person(
 pid number(10),
 name varchar2(10),
 gender number(1) default 1,
 birthday date,
 constraint person_pk_pid primary key(pid));


  

  非空约束

  使用非空约束，可以使指定的字段不可以为空。

  示例：建立一张pid和name不可以为空的表

  create table person(
 pid number(10) not null,
 name varchar2(10) not null,
 gender number(1),
 birthday date);


  

  唯一约束（unique）

  表中的一个字段的内容是唯一的

  范例：建一个name是唯一的表

  create table person(
 pid number(10),
 name varchar2(10) unique,
 gender number(1),
 birthday date);


  唯一约束的名字也可以自定义

  create table person(
 pid number(10),
 name varchar2(10),
 gender number(1),
 birthday date,
 constraint person_name_unique(name));


  

  检查约束

  使用检查约束可以来约束字段值的合法范围。

  示例：创建一张表性别只能是1 或 2

  create table person(
 pid number(10),
 name varchar2(10),
 gender number(1) check(gender in (1, 2)));


  检查约束也可以自定义

  create table person(
 pid number(10),
 name varchar2(10),
 gender number(1),
 birthday date,
 constraint person_gender_ck check(gender in (1, 2)));


  

  外键约束

  之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。
  示例：创建两张表，一张订单表，一张订单明细表，订单和明细是一对多的关系

  create table orders(
 order_id number(10),
 total_price number(10, 2),
 order_time date,
 constraint orders_order_id_pk primary key(order_id));

  create table order_detail(
   detail_id number(10),
   order_id number(10),
   item_name varchar2(10),
   quantity number(10),
   constraint order_detail_detail_id_pk primary key(detail_id));

  insert into orders values(1, 200, to_date('2015-12-12', 'yyyy-MM-dd'));

  insert into order_detail values(1, 2, 'java', 1);

  向两张表中插入如上两条数据，我们发现在order_detail表中插入的order_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。

  再次建表：

  create table orders(
 order_id number(10),
 total_price number(10, 2),
 order_time date,
 constraint orders_order_id_pk primary key(order_id));

  create table order_detail(
   detail_id number(10),
   order_id number(10),
   item_name varchar2(10),
   quantity number(10),
   constraint order_detail_detail_id_pk primary key(detail_id)
   constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id));

  

  注意：

  • 外键一定是主表的主键
  • 删表时一定先删子表再删主表，如果直接删主表会出现由于约束存在无法删除的问题

  SQL> drop table orders  
drop table orders 


  ORA-02449：表中的唯一/主键被外键引用，但是可以强制删除：

  drop table orders cascade constraint;  


  但是不建议使用

  删除主表的数据可以先删除子表的关联数据，再删出表，也可以使用级联删除。级联删除在外键约束上要加上on delete cascade

  constraint order_detail_order_id_fk foreign key(order_id)
 references orders(order_id) on delete cascade


  这样删除主表数据的时候会把子表的关联数据一同删除

  终极示例：

   create table student
(
   sid number constraint student_pk primary key,
   sname varchar2(20) constraint student_name_notnull not null,
   gender varchar2(2) constraint student_gender check (gender in ('男','女')),
   email varchar2(40) constraint student_email_unique unique
                      constraint student_email_notnull not null,
deptno number constraint student_fk references dept(deptno) on delete set null
);


  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');

  1 row created.

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

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

  1 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%';

  4 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;

  1 row updated.

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

  update copy_emp
set department_id = 110;

  22 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);

  1 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';

  1 row deleted.

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

  delete from copy_emp;

  22 rows deleted.

  在DELETE中使用子查询

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

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

  1 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

  —   特色专栏  —

   MySQL|PostgreSQL|Redis|MongoDB|Tools

  ElasticSearch|Kubernetes|Docker|Hadoop

   Kafka|RabbitMQ|Zookeeper|OpenStack

  企业监控平台|应用与服务|DevOps|集群管理