关于“oracle基础知识总结”系列博客的说明：

1 本系列博客是在本人学习过程中总结的笔记，学习资料为尚学堂马士兵老师的oracle视频，大部分示例来自于该资料；

2 做为数据库初学者，本系列博客主要讲解了oracle的基础知识，高手勿入勿喷；

3 由于是学习笔记，主要作用是便于本人以后复习和备忘。可能条理不是很清晰，上下文衔接的不是很好，请读本文的读者做好心理准备；

4 本系列博客中的语句主要使用了oracle中scott用户的表结构，该用户是数据库中自带的用户。

一数据库中的三范式

范式：数据库设计的规则。设计比较复杂，不可能用三句话全部概括，有时候并不适用，再设计时有时要打破规则。
数据库设计时一般要求不存在冗余数据。例如为了防止同一个表中存在相同的记录，可以加一个主键，主键标识记录的唯一性，要求主键列非空且唯一。
如果表中的一个字段很长，存储的信息很多，比如info字段中既存储姓名，也存储年龄。这样的情况要考虑把一个列分成多个列。因为在查询时比较灵活，在查询年龄时，不必查询所有的信息。
但是每个列的信息不能重复，比如既然有了一个出生年月字段，就不必有一个年龄字段。这就要求设计时遵循第一范式。

第一范式：

1 每一张表都要有主键；

2 列不可分，列不重复。

如果一张表中的字段有学生姓名，学号，所选课程的教师的姓名，所选课程的教师的编号。这样的话学号和教师编号就作为复合主键，那么学生姓名等信息，依赖于主键学号，而教师姓名等教师信息依赖于主键教师编号，这就引入了部分依赖问题。把一张表拆分成多个表（学生表，教师表，选课表），就可以解决部分依赖的问题，因为每张表都只有一个主键。部分依赖违反了第二范式。第二范式的定义如下：

第二范式：

不能存在部分依赖。如果一个表中有多个主键，那么不是主键的字段不能依赖于部分主键。

有时在设计表结构时，表中包含已在其它表中存在的非主键信息。如学生表中既有他的教师的编号，又有教师的名字等信息。这就违反了第三范式。学生表中只能引入他的教师的编号，而不能引入他的教师的姓名，如果要求教师的姓名，只需要根据编号去教师表中查询。第三范式如下：

第三范式：

表中不能包含已在其它表中存在的非主键信息。

二 PLSQL

plsql是数据库中的一种编程语言。用来写存储过程等。但是用的不是很多。
一门语言包括数据类型和语法等重要元素，plsql中同样有这些元素。
sql语言不能解决所有问题，因为他的灵活性有限。因为没有条件分支和判断等特性。这就需要将sql和PL结合起来。 pl 是 procedure language的缩写，说明pl是一种过程语言，具有分支和判断。能处理一些较为复杂的逻辑。下面主要以示例的方式罗列出pl的语法。

在PLSQL中的注释

-- 注释掉一行

/**/注释掉多行

PL举例1 -- Hello world：

set serveroutput on;
begin:
dbms_output.put_line('Hello World');
end;

PL举例2 -- 声明变量：

declare 
v_name varchar2(20);
begin 
v_name := 'myname';
dbms_output.put_line(v_name);
end;

PL举例3 -- exception

declare 
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then 
dbms_output.put_line('error');
end;

PL中的数据类型：

number
binary_integer
date
constant number
boolean
varchar2

引用已有类型

让PL中的变量的定义和表中字段的定义具有一致性，也就是说表中的某个字段的类型改变，如number(7,2)变成 number(9,3)。那么让PL程序中对应的变量也改变。可以在变量声明的时候引用表中字段的类型。

PL举例4 -- PL中的变量引入表中字段的类型

declare
v_empno munber(4);
v_empno2 emp.empno%type;
begin
dbms_output.put_line('test');
end;

PL举例5 -- PL中的一个变量引用其他变量的类型

declare
v_empno munber(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('test');
end;

PL中的复合变量 table ， PL中的table，类似于java中的数组

PL举例6 -- table

declare 
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin 
v_empnos(0) := 7369;
v_empnos(2) := 7369;
v_empnos(-1) := 7369;
dbms_output.put_line(v_empnos(-1));
end;

PL中的复合变量 record, PL中的record，类似于java中的类

PL举例7 -- record

declare
type type_record_dept is record
(
deptno dept.deptno%type;
dname dept.dname%type;
loc dept.loc%type;
)
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname );
end;

上面record表示的是表dept中的一条记录，那么如果表结构变了，例如加了一个字段，那么record的结构也要改变以复合表dept的结构。怎样才能让他们自动对应呢？那就可以使用%rowtype声明record中的变量。

PL举例8 -- 使用%rowtype声明record中的变量

declare 

v_temp dept%rowtype;

begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname );
end;

PL中的select语句必须返回一条记录，并且只能返回一条记录，select语句中必须有一个into关键字。

PL举例9 -- PL中的select

declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_ename, v_sal from emp where empno = 7369;
dbms_output.put_line(v_ename || ' ' || v_sal );
end;

PL举例10 -- PL中select和record的使用

declare 
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(emp.ename);
end;

PL举例11 -- PL中的insert

declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values (v_deptno, v_dname, v_loc);
commit;
end;

PL举例12 -- 获取update语句的返回值（多少条记录被影响）

declare 
v_deptno emp2.deptno%type := 50;
v_count number;
begin
update emp2 set sal = sal/2 where deptno = v_deptno;
dbms_output.put_line(sql%rowcount || '条记录被影响');
commit;
end;

sql是一个关键字，代表上面执行的那条sql语句， rowcount是sql中的一个属性，代表上面的那条sql语句影响的记录的条数

PL中的DDL和DCL语句

PL举例13 -- PL中的create语句

begin
execute immediate 'create table T (name varchar2(20) default ''aaa'')';
end;

PL中的分支和循环

PL举例14 -- PL中的if语句

declare 
v_sal emp.sal%type;
begin
select sal int v_sal from emp where empno = 7369;
if (v_sal < 1200) then
dbms_output.put_line('low');
elsif (v_sal < 2000) then 
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;

PL举例15 -- PL中的do-while循环

declare 
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i +1;
exit when (i >= 11);
end loop;
end;

PL举例16 -- PL中的while循环

declare 
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;

PL举例17 -- PL中的for循环

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


for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;

PL举例18 -- exception的类型 too_many_rows

declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多条记录');
when others then
dbms_output.put_line('error');
end;

PL举例19 -- exception的类型 no_data_found

declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 100000000;
exception
when no_data_found then
dbms_output.put_line('未找到数据');
when others then
dbms_output.put_line('error');
end;

PL举例20 -- 将错误日志记录在一张表中

create table
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
create sequence seq_errorlog_id start with 1 increment by 1;

declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception 
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate );
commit;
end;

SQLCODE和SQLERRM是PL中的关键字。

PL中的游标cursor

select可以产生一个结果集，用游标可以遍历这个结果集

PL举例21 -- cursor

declare
cursor c is select * from emp;
-- 声明一个record
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;

上面程序取出一条记录。
cursor是一个关键字，也可以看做一种数据类型，即游标类型

PL举例22 -- 使用do-while循环遍历cursor

declare
cursor c is select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;

notfound是c中的一个属性，当遍历到最后，没有找到记录时， c%notfound会返回true；

PL举例23 -- 使用while循环遍历cursor

declare
cursor c is select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while (c%found) loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;

found是c中的一个属性，如果找到一条记录，就返回true。

PL举例24 -- 使用for循环遍历cursor

declare
cursor c is select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;

使用for循环遍历cursor：
不必显示声明一个record v_emp, 在执行for语句时自动创建变量；
不必打开游标，遍历时自动打开；
不必关闭游标，遍历结束自动关闭；
不必显示的fetch一条记录，在遍历过程中自动fetch。
for循环遍历游标的语法比较简洁。

PL举例25 -- 带参数的游标，获取特定条件的记录

declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type) 
is 
select ename, sal from emp where deptno = v_deptno and job = v_job;
begin
for v_emp in c(30, 'CLARK') loop
dbms_output.put_line(v_emp.ename);
end loop;
end;

PL举例26 -- 可更新的游标

declare
cursor c is select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_emp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;

for update 和 current of 为关键字

三存储过程

存储过程是带有名字的一个可执行的PL程序块

存储过程举例1 -- 创建存储过程

create or replace procedure p
is 
cursor c is select * from emp2 for update;
begin 
for v_emp in c loop
if (v_emp.deptno = 10) then
update emp2 set sal = sal + 10 where current of c;
elsif (v_emp.deptno = 20) then
update emp2 set sal = sal + 20 where current of c;
else
update emp2 set sal = sal + 50 where current of c;
end if;
end loop;
end;

存储过程举例2 -- 执行存储过程的两种形式

exec p;


begin
p;
end;

存储过程举例3 -- 创建带参数的存储过程

create or replace procedure p
(v_a in number, v_b number, v_ret out number , v_temp in out number)
is 


begin 
if (v_a > v_b) then 
v_ret := v_a;
else 
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;

in, out和in out 是关键字，分别代表传入参数，传出参数和传入传出参数，如果不使用这几个关键字，默认是传入参数

存储过程举例4 -- 执行带参数的存储过程

declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;

存储过程创建时如果有语法错误，会提示编译错误。
执行

show error

会提示准确的错误信息
删除procedure 使用drop

四函数 function

函数举例1 -- 创建函数

create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif (v_sal < 2750) then
return 0.15;
else 
return 0.20;
end if;
end;

function 关键字，表示创建一个函数

函数举例2 -- 执行函数

select lower(ename), sal_tax(sal) from emp;

五触发器 trigger

响应一个具体的事件，在发生这个事件时，执行一些触发器定义的操作

触发器举例1 -- 创建触发器

create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);

create or replace trigger trig
after insert or update or delete on emp2 for each row
begin
if inserting then
insert into emp2_log values (USER, 'insert', sysdate);
elsif updating then
insert into emp2_log values (USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
end if;
end;

trigger, after, or, for each row, inserting, updating, deleting , USER ， sysdate是关键字
after是在执行之后，触发触发器
before是在执行之前，触发触发器
如果不写for each row，每次操作虽然影响了多条记录，但是只会触发一次触发器
使用for each row，每更改一条记录，都会触发一次触发器

触发器的应用：如果一张表中的一个字段被其他表参考（外键约束），那么是不能更新这个字段的。但是使用触发器就可以。

触发器举例2 -- 更新被参考字段时，其他表中所有参考他的字段一同更新

create or repalce trigger trig
after update on dept
for each row
begin 
update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;

:NEW 和 :OLD是关键字，表示更新之后的新状态和更新之前的新状态
在更新数据时，先触发触发器，再检查约束条件

六树状结构的存储与展示

使用递归。并且在表中加入了表示层级关系的冗余字段。

创建树形数据：

create table articale

(

id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1), -- 0 代表非叶子节点， 1 代表叶子节点
alevel number(2)
);


insert into articale values (1, '蚂蚁大战大象', 0, 0, 0);
insert into articale values (2, '大象被打趴下了', 1, 0, 1);
insert into articale values (3, '蚂蚁也不好过', 2, 1, 2);
insert into articale values (4, '瞎说', 2, 0, 2);
insert into articale values (5, '没有瞎说', 4, 1, 3);
insert into articale values (6, '怎么可能', 1, 0, 1);
insert into articale values (7, '怎么没有可能', 6, 1, 2);
insert into articale values (8, '可能性是很大的', 6, 1, 2);
insert into articale values (9, '大象进医院了', 2, 0, 2);
insert into articale values (10, '护士是蚂蚁', 9, 1, 3);

commit;

写存储过程展示树状结构：

create or replace prodedure p (v_pid articale.pid%type, v_level binary_integer) is
cursor c is select select * from article where pid = v_pid;
v_prestr varchar2(1024) := '';
begin
-- 在内容之前加空格
for i in 1..v_level loop
v_prestr := v_prestr || '****';
end loop;


for v_articale in c loop
dbms_output.put_line(v_prestr || v_articale.cont);
if (v_articale.isleaf = 0) then
p (v_articale.id, v_level + 1);
end if;
end loop;
end;

执行存储过程展示数据:

set serveroutput on;
exec p(0);

oracle基础知识总结 part 3 ： 三范式，PLSQL，存储过程，函数，触发器

一 数据库中的三范式