Oracle 核心概念

1、序列

一、含义

序列是Oracle数据库中特有的，使用序列可以生成类似于 auto_increment 这种ID自动增长 1,2,3,4,5… 的效果

二、语法

1
2
3
4
5
6
7
create sequence 序列名称
start with 从几开始
increment by 每次增长多少
[maxvalue 最大值] | nomaxvalue
[minvalue 最小值] | nominvalue
cycle | nocycle --是否自动循环
[cache 缓存数量] | nocache;

三、演示

1
2
3
4
5
6
7
8
9
10
11
12
--创建序列
create sequence auto_increment_seq
start with 1
increment by 1
nomaxvalue
minvalue 1
nocycle
cache 10000;

--调用序列
select auto_increment_seq.nextval from dual;
select auto_increment_seq.currval from dual;

2、PLSQL编程

2.1、格式

1
2
3
4
5
6
7
declare
  --声明变量
          
begin
  --业务逻辑

end;

2.2、变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
declare
  --声明变量
  -- 格式一：变量名 变量类型;
  -- 格式二：变量名 变量类型 := 初始值;
  -- 格式三：变量名 变量类型 := &文本框名;
  -- 格式四：变量名 表名.字段名%type;
  -- 格式五：变量名 表名%rowtype;
  vnum number;
  vage number := 28;
  vabc number := &abc;--输入一个数值，从一个文本框输入
  vsal emp.sal%type;  --引用型的变量，代表emp.sal的类型
  vrow emp%rowtype;   --记录型的变量，代表emp一行的类型          
begin
  --业务逻辑
  dbms_output.put_line(vnum);                       --输出一个未赋值的变量
  dbms_output.put_line(vage);                       --输出一个已赋值的变量
  dbms_output.put_line(vabc);                       --输出一个文本框输入的变量
  select sal into vsal from emp where empno = 7654; --将查询到的sal内容存入vsal并输出
  dbms_output.put_line(vsal);
  select * into vrow from emp where empno = 7654;   --将查询到的一行内容存入vrow并输出  
  dbms_output.put_line(vrow.sal);
  dbms_output.put_line(123);                        --输出一个整数
  dbms_output.put_line(123.456);                    --输出一个小数  
  dbms_output.put_line('Hello,World');              --输出一个字符串
  dbms_output.put_line('Hello'||',World');          --输出一个拼接的字符串，||拼接符Oracle特有
  dbms_output.put_line(concat('Hello',',World'));   --输出一个拼接的字符串，concat函数比较通用
end;

2.3、if判断

语法

1
2
3
4
5
6
7
if 条件1 then
     
elsif 条件2 then
       
else 
     
end if;

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
declare
  age number := &age;
begin
  if age < 18 then
    dbms_output.put_line('小屁孩');
  elsif age >= 18 and age <= 24 then
    dbms_output.put_line('年轻人');
  elsif age > 24 and age < 40 then
    dbms_output.put_line('老司机');
  else
    dbms_output.put_line('老年人');
  end if;
end;

2.4、while循环

语法

1
2
3
while 条件 loop
        
end loop;

示例

1
2
3
4
5
6
7
8
9
--输出1~10
declare
  i number := 1;
begin
  while i <= 10 loop
    dbms_output.put_line(i);
    i := i + 1;
  end loop;
end;

2.5、for循环

语法

1
2
3
for 变量  in [reverse] 起始值..结束值 loop
        
end loop;

示例

1
2
3
4
5
6
7
8
--输出1~10
declare

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

2.6、loop循环

语法

1
2
3
4
5
loop
  
  exit when 条件
  
end loop;

示例

1
2
3
4
5
6
7
8
9
10
--输出1~10
declare
  i number := 1;
begin
  loop
    exit when i > 10;
    dbms_output.put_line(i);
    i := i + 1;
  end loop;
end;

2.7、意外

一、含义

意外是程序运行的过程发生的异常，相当于是Java中的异常

二、语法

1
2
3
4
5
6
7
8
9
10
11
12
13
declare
   --声明变量
begin
   --业务逻辑
exception
   --处理异常
   when 异常1 then
     ...
   when 异常2 then
     ...
   when others then
     ...处理其它异常
end;

三、分类

  • 系统异常
    • zero_divide ：除数为零异常
    • value_error ：类型转换异常
    • no_data_found : 没有找到数据
    • too_many_rows : 查询出多行记录，但是赋值给了%rowtype一行数据变量
  • 自定义异常
1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare
   --声明变量
   异常名称 exception;
begin
   --业务逻辑
   if 触发条件 then
      raise 异常名称; --抛出自定义的异常
exception
   --处理异常
   when 异常名称 then
     dbms_output.put_line('输出了自定义异常');  
   when others then
     dbms_output.put_line('输出了其它的异常');  
end;

四、演示

1、内置系统异常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
  vi   number;
  vrow emp%rowtype;
begin
  --以下四行对应四个异常，测试请依次放开
  vi := 8/0;  
  --vi := 'aaa';
  --select * into vrow from emp where empno = 1234567;
  --select * into vrow from emp;
exception
  when zero_divide then
    dbms_output.put_line('发生除数为零异常');
  when value_error then
    dbms_output.put_line('发生类型转换异常');
  when no_data_found then
    dbms_output.put_line('没有找到数据异常');
  when too_many_rows then
    dbms_output.put_line('查询出多行记录，但是赋值给了%rowtype一行数据变量');
  when others then
    dbms_output.put_line('发生了其它的异常' || sqlerrm);
end;

2、抛出系统异常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--查询指定编号的员工，如果没有找到，则抛出系统异常
declare
  --1.声明一个变量 %rowtype
  vrow emp%rowtype;
begin
  --查询员工信息，保存起来
  select * into vrow from emp where empno = 8000;
  --判断是否触发异常的条件
  if vrow.sal is null then
    --抛出系统异常
     raise_application_error(-20001,'员工工资为空');
  end if;
exception
  when others then
    dbms_output.put_line('输出了其它的异常' || sqlerrm);
end;

3、抛出自定义异常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--查询指定编号的员工，如果没有找到，则抛出自定义异常
declare
  --1.声明一个变量 %rowtype
  vrow emp%rowtype;
  --2.声明一个自定义的异常
  no_emp exception;
begin
  --查询员工信息，保存起来
  select * into vrow from emp where empno = 8000;
  --判断是否触发异常的条件
  if vrow.sal is null then
    raise no_emp; --抛出自定义的异常
  end if;
exception
  when no_emp then
    dbms_output.put_line('输出了自定义异常');
  when others then
    dbms_output.put_line('输出了其它的异常' || sqlerrm);
end;

3、索引

一、含义

索引相当于是一本书的目录，能够提高我们的查询效率

二、语法

1、创建索引

1
create [UNIQUE]|[BITMAP] index 索引名 on 表名(列名1,列名2,...);

2、修改索引

1
2
3
4
5
6
7
8
9
10
11
--重命名索引
alter index 索引名称 rename to 新的名称;

--合并索引
alter index 索引名称 coalesce;

--重建索引
alter index 索引名称 rebuild;

--修改某列
先删除，在创建

3、删除索引

1
drop index 索引名称;

三、演示

1、创建索引

1
create index INX_CATEGORY_CNAME on category(cname);

2、修改索引

1
2
3
4
5
6
7
8
9
10
11
--重命名索引
alter index INX_CATEGORY_CNAME rename to INX_CATEGORY_CNAME_NEW;

--合并索引
alter index INX_CATEGORY_CNAME_NEW coalesce;

--重建索引
alter index INX_CATEGORY_CNAME_NEW rebuild;

--修改某列
先删除，在创建

3、删除索引

1
drop index INX_CATEGORY_CNAME;

4、视图

一、含义

视图是对查询结果的一个封装，视图里面所有的数据，都是来自于它查询的那张表，视图本身不存储任何数据，但是可以修改原数据，但是不建议这样使用

二、语法

1、创建视图

1
2
3
create view 视图名称
as 查询语句
[with read only];

2、修改视图

1
2
3
create or replace view 视图名称
as 查询语句
[with read only];

3、删除视图

1
drop view 视图名称;

三、演示

1、创建视图

1
2
create view view_emp as
select ename,job,mgr from emp;

2、修改视图

1
2
create or replace view view_emp as
select ename,job,mgr,deptno from emp;

3、删除视图

1
drop view view_emp;

5、同义词

一、含义

同义词就是别名的意思和视图的功能类似，就是一种映射关系

二、语法

1、创建同义词

1
create [public] synonym 同义词名称 for 对象的名称;

2、修改同义词

1
create or replace [public] synonym 同义词名称 for 对象的名称;

3、删除同义词

1
drop [public] synonym 同义词名称;

三、演示

1、创建同义词

1
2
3
4
--创建
create synonym syno_emp for emp;
--调用
select * from syno_emp;

2、修改同义词

1
2
3
4
--创建
create or replace synonym syno_emp_update for emp;
--调用
select * from syno_emp_update;

3、删除同义词

1
drop synonym syno_emp_update;

6、游标

一、含义

游标是用来操作查询结果集，相当于是JDBC中ResultSet，它可以对查询的结果一行一行的获取

二、语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--第一步：定义游标
    --第一种：普通游标
    cursor 游标名[(参数 参数类型)] is 查询语句;
    --第二种：系统引用游标
    游标名 sys_refcursor;

--第二步：打开游标
    --第一种：普通游标
    open 游标名[(参数 参数类型)];
    --第二种：系统引用游标
    open 游标名 for 查询语句;

--第三步：获取一行
	fetch 游标名 into 变量;

--第四步：关闭游标
	close 游标名;

三、演示

1、普通游标使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--输出指定部门下的员工姓名和工资
declare
  --1.声明游标
  cursor vrows(dno number) is select * from emp where deptno = dno;
  --声明变量
  vrow emp%rowtype;
begin
  --2.打开游标 
  open vrows(10);
  --3.循环遍历
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal);
  end loop;
  --4.关闭游标
  close vrows;
end;

2、系统引用游标使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--输出员工表中所有的员工姓名和工资
declare
  --1.声明系统引用游标
  vrows sys_refcursor;
  --声明变量
  vrow emp%rowtype;
begin
  --2.打开游标
  open vrows for select * from emp;
  --3.循环遍历
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal);
  end loop;
  --4.关闭游标
  close vrows;
end;

3、使用for循环输出

1
2
3
4
5
6
7
8
9
--输出员工表中所有的员工姓名和工资
declare
  cursor vrows is select * from emp;
begin
  --自动定义变量vrow，自动打开游标，自动关闭游标
  for vrow in vrows loop
    dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal || '工作:' || vrow.job);
  end loop;
end;

7、存储过程

一、含义

存储过程实际上是封装在服务器上一段PLSQL代码片断，它已经编译好了，如果客户端调用存储过程，执行效率就会非常高效

二、语法

1、创建存储过程

1
2
3
4
5
6
create procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...)
is|as
 --声明部分
begin
 --业务逻辑 
end;

2、修改存储过程

1
2
3
4
5
6
create [or replace] procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...)
is|as
 --声明部分
begin
 --业务逻辑 
end;

3、删除存储过程

1
drop procedure 存储过程名称;

4、调用存储过程

1
2
3
4
5
6
7
8
9
--方式一：
call 存储过程名称(...);

--方式二：
declare

begin
  存储过程名称(...);
end;

三、演示

1、创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--给指定员工涨薪并打印涨薪前和涨薪后的工资
create procedure proc_update_sal(vempno in number,vnum in number)
is
  --声明变量
  vsal number;
begin
  --查询当前的工资
  select sal into vsal from emp where empno = vempno;
  --输出涨薪前的工资
  dbms_output.put_line('涨薪前:' || vsal);
  --更新工资
  update emp set sal = vsal + vnum where empno = vempno;
  --输出涨薪后的工资
  dbms_output.put_line('涨薪后:' || (vsal + vnum));
  --提交事物
  commit;
end;

--给员工编号为7521的员工涨工资10元
call proc_update_sal(7521, 10);

2、修改存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--给指定员工涨薪并打印涨薪前和涨薪后的工资
create or replace procedure proc_update_sal(vempno in number,vnum in number)
is
  --声明变量
  vsal number;
begin
  --查询当前的工资
  select sal into vsal from emp where empno = vempno;
  --输出涨薪前的工资
  dbms_output.put_line('涨薪前:' || vsal);
  --更新工资
  update emp set sal = vsal + vnum where empno = vempno;
  --输出涨薪后的工资
  dbms_output.put_line('涨薪后:' || (vsal + vnum));
  --提交事物
  commit;
end;

--给员工编号为7521的员工涨工资10元
call proc_update_sal(7521, 10);

3、删除存储过程

1
drop procedure proc_update_sal;

8、函数

一、含义

函数实际上是封装在服务器上一段PLSQL代码片断，它已经编译好了，如果客户端调用存储过程，执行效率就会非常高效，它跟存储过程没有什么本质区别，存储过程能做的函数也能做，只不过函数有返回值

二、语法

1、创建函数

1
2
3
4
5
6
create function 函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) return 返回的参数类型
is|as
 --声明部分
begin
 --业务逻辑 
end;

2、修改函数

1
2
3
4
5
6
create [or replace] function 函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) return 返回的参数类型
is|as
 --声明部分
begin
 --业务逻辑 
end;

3、删除函数

1
drop function 函数名称;

4、调用函数

1
2
3
4
5
6
7
8
9
--方式一：
select 函数名称(...) from dual;

--方式二：
declare
  变量名 变量类型;
begin
  变量名 = 函数名称(...);
end;

三、演示

1、创建函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--查询指定员工的年薪
/*
    参数 : 员工的编号
    返回 : 员工的年薪          
*/
create function func_getsal(vempno number) return number
is
  vtotalsal number;
begin
  select sal * 12 + nvl(comm, 0) into vtotalsal from emp where empno = vempno;
  return vtotalsal;
end;

--查询员工编号为7788的年薪
declare
  vsal number;
begin
  vsal := func_getsal(7788);
  dbms_output.put_line(vsal);
end;

2、修改函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--查询指定员工的年薪
/*
    参数 : 员工的编号
    返回 : 员工的年薪          
*/
create or replace function func_getsal(vempno number) return number
is
  vtotalsal number;
begin
  select sal * 12 + nvl(comm, 0) into vtotalsal from emp where empno = vempno;
  return vtotalsal;
end;

--查询员工编号为7788的年薪
declare
  vsal number;
begin
  vsal := func_getsal(7788);
  dbms_output.put_line(vsal);
end;

3、删除函数

1
drop function func_getsal;

9、触发器

一、含义

当用户执行了 insert | update | delete 这些操作之后，可以触发一系列其它的动作、业务逻辑，使用触发器可以协助应用在数据库端确保数据的完整性、日志记录 、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在Oracle触发器不仅支持行级触发，还支持语句级触发

二、分类

触发器类型 NEW 和 OLD的使用
INSERT 型触发器 NEW 表示将要或者已经新增的数据
UPDATE 型触发器 OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器 OLD 表示将要或者已经删除的数据

三、语法

1、创建触发器

1
2
3
4
5
6
7
8
9
10
create trigger 触发器名称
before|after
insert|update|delete 
on 表名称
[for each row]--行级触发器
declare
 --声明部分
begin
 --业务逻辑 
end;

2、修改触发器

1
2
3
4
5
6
7
8
9
10
create [or replace] trigger 触发器名称
before|after
insert|update|delete 
on 表名称
[for each row]--行级触发器
declare
 --声明部分
begin
 --业务逻辑 
end;

3、删除触发器

1
drop trigger 触发器名称;

四、演示

1、INSERT 型触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
--新员工入职之后，输出一句话: 欢迎加入我们
create or replace trigger tri_emp_insert
after
insert
on emp
declare

begin
  dbms_output.put_line('欢迎加入我们');
end;

--插入数据就可以自动触发触发器
insert into emp(empno, ename) values(9527, '马哈哈');

2、UPDATE 型触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--判断员工涨工资后的工资一定要大于涨工资前的工资
create or replace trigger tri_emp_update_sal
before
update
on emp
for each row
declare

begin
  if :old.sal > :new.sal then
    raise_application_error(-20002,'旧的工资不能大于新的工资');
  end if;
end;

--更新数据就可以自动触发触发器（无异常）
update emp set sal = sal + 10;
select * from emp;

--更新数据就可以自动触发触发器（有异常）
update emp set sal = sal - 100;
select * from emp;

3、DELETE 型触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
--老员工离职之后，输出一句话: 有员工离职了
create or replace trigger tri_emp_delete
after
delete
on emp
declare

begin
  dbms_output.put_line('有员工离职了');
end;

--删除数据就可以自动触发触发器
delete from emp where empno = 9527;

4、删除触发器

1
2
3
drop trigger tri_emp_insert;
drop trigger tri_emp_update_sal;
drop trigger tri_emp_delete;
Oracle
#Oracle
Oracle 核心概念
https://flepeng.github.io/042-Oracle-31-核心概念-Oracle-核心概念/
作者
Lepeng
发布于
2025年4月1日
许可协议