Oracle学习笔记
因为之前学过Mysql，Oracle和Mysql的基础语法都类似，不赘述。本文主要小结Oracle的特殊语法和高级语法如表空间，索引，PL/SQL，存储过程等。
1. 表空间
MySQL 与 Oracle 数据库之间最大的区别要属表空间。在 MySQL 中一个项目对应一个数据库，而在 Oracle中通常一个项目对应一个表空间。
Oracle 数据库的逻辑结构：被划分成一个或多个表空间，而一个表空间则对应着一个或多个物理的数据库文
件。每个数据库至少有一个 SYSTEM 表空间和 USER 表空间，这是系统安装完后自动创建的。
- 一个实例对应多个表空间和多个用户
- 一个表空间可以有多个用户，一个用户只属于一个表空间
- 一个表空间中的数据存储在多个物理文件中
- 一个表空间中包含多张表
1.1 创建表空间
create tablespace 表空间名字 可选项（datafile '文件名.dbf' size 初始大小 autoextend on）
-- 指定文件的名字，也可以指定路径。如果没有指定路径，默认是在下面这个目录：c:\oracle\product\10.2.0\db_1\database
datafile '文件名.dbf'
-- 一开始分配多大的空间给文件，单位是M和K
size 初始大小
-- 如果用完了这些空间，是否自动扩展
autoextend on
1.2 删除表空间
-- 删除表空间，同时删除内容和文件
drop tablespace 表空间名 including contents and datafiles;
2. 视图
视图可以封装一条 SQL 查询语句展现数据。视图是虚拟的表,不是真实存在的表，它的数据从表中得到。为数据库真实的表提供多种展现方式供其他用户查看。使用视图的好处是可以屏蔽一些的敏感数据。
####2.1 创建视图
create or replace view 视图名字 as 查询语句;
####2.2 将视图授权给其他其他用户
grant 权限名 on 视图名 to 其他用户;
案例：创建一个视图并授权给其他用户
--scott 是普通用户,很多权限没有,为了方便我们给 dba 权限分配给 scott
grant dba to scott;
--创建视图 view_emp，只查询 empno,ename,job 三列
create or replace view view_emp as
select empno,ename,job from emp;
--创建用户
create user test identified by orcl;
--给用户授权
grant connect,resource to test;
--给视图 view_emp 查询权限
grant select on view_emp to test;
--使用test用户登录并查询
select * from scott.view_emp;
-- 更新失败
update scott.view_emp set ename='Rose' where empno=7369;
3. 索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 IO，而提高数据访问性能。
3.1 创建索引
- 单列索引
create index 索引名 on 表名( 列名)
- 复合索引
create index 名 索引名 on 表名( 列名 1, 列名 2);
案例：给user创建索引
--范例：给 user 表的 username 建立索引
create index ix_user_name on user(username);
--范例：给 user 表创建一个 username 和 sex 的索引
create index ix_user_name_sex on user(name, sex);
索引的使用原则和注意事项
- 在数据量大的表上建立索引才有意义
- 在经常查询的字段上使用索引，用在 where 子句后面或者是连接条件上的字段建立索引
- 表中数据修改频率高时，不建议建立索引
- 表的主键默认创建索引
4. PL/SQL
PL/SQL（Procedure Language/SQL） 是 Oracle 对 SQL 语言的过程化扩展，指在 SQL 语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。
4.1 PL/SQL语法
-- 如果没有声明部分可以省略declare
declare
[声明变量或游标]
begin
语句序列
end;
4.2 变量类型
#####4.2.1 概念
- 引用型变量：单行单列的值，一列值
- 记录型变量：单行多列的值，一行记录
- 游标：多行多列的值，虚拟表
4.2.2 说明
| 变量类型 | 语法 | 举例 |
|---|---|---|
| 变量 | 变量名 类型 := 初值 | v_name varchar2(10):=‘张三’; |
| 常量 | 变量名 constant 类型 := 初值 | v_salary constant number(8,2):=1000; |
| 引用类型(列类型) | 变量名 表名.列名%type | v_ename emp.ename%type; |
| **记录类型(行类型) ** | 变量名 表名%rowtype | v_emp emp%rowtype |
4.2.3 游标
- 声明游标
cursor 游标名 is SQL语句
- 打开并使用游标
open 游标名;
loop
fetch 游标名 into 记录型变量;
exit when 游标名%notfound;
游标逻辑处理代码;
end loop;
close 游标名;
4.2.3 案例： 使用 plsql 打印出2号部门所有员工的姓名,薪水
-- 查找20号部门所有员工,将其姓名,薪水,职务打印出来
declare
-- 1) 声明游标查询部门是20的员工
cursor cur_emp is select * from emp where dept_id=2;
-- 2) 声明记录类型的变量，用来保存游标中每条记录
v_emp emp%rowtype;
begin
-- 3) 打开游标，创建循环
open cur_emp;
loop
-- 4) 抓取游标中的每条记录
fetch cur_emp into v_emp;
-- 5) 如果游标中没有记录则退出
exit when cur_emp%notfound;
-- 6) 打印输出姓名，薪水
dbms_output.put_line('姓名: ' || v_emp.name);
dbms_output.put_line('薪水: ' || v_emp.salary);
end loop;
-- 7) 关闭游标
close cur_emp;
end;
4.3 过程处理
4.3.1 分支语句
-- 语法 1：条件满足则执行
IF 条件 THEN
逻辑处理;
END IF;
-- 语法 2：两个分支
IF 条件 THEN
逻辑处理1
ELSE
逻辑处理2
END IF;
-- 语法 3：多分支
IF 条件 THEN
逻辑处理
ELSIF 条件 THEN
逻辑处理
ELSE
逻辑处理
END IF;
4.3.2 循环语句
-- 语法1：无条件循环，满足条件的时候退出，否则一直循环
loop
exit when 条件;
循环体;
end loop;
-- 语法2：while循环，当满足条件的时候循环，否则结束循环
while 条件 loop
循环体;
end loop;
-- 格式3: for固定循环次数
for 变量名 in 起始值..结束值 loop
循环体;
end loop;
4.3.3 案例：用 plsql 给不同部门的员工涨不同工资
declare
-- 1) 定义游标查询每一条记录
cursor cur_emp is select * from emp;
-- 2) 定义记录型变量存储一行员工
v_emp emp%rowtype;
-- 3) 定义变量存储工资的增长值
v_sal number;
begin
open cur_emp;
loop
-- 4) 对抓取的每条记录进行判断
fetch cur_emp into v_emp;
exit when cur_emp%notfound;
-- 如果1号部门则增长值为1000，2号部门为800，其它400。
if v_emp.dept_id = 1 then
v_sal := 1000;
elsif v_emp.job = 2 then
v_sal:= 800;
else
v_sal:= 400;
end if;
-- 5) 对当前的记录进行更新，判断条件是empno=记录型变量.empno，即等于当前操作的这条记录的empno
update emp set salary = salary + v_sal where id = v_emp.id;
end loop;
-- 6) 最后提交事务。
commit;
close cur_emp;
end;
5. 存储过程
一组PL/SQL语句的集合，完成一定的功能，并且保存在数据库中。
- 存储过程有名字和参数，类似于Java中方法
- 预编译到数据库中，比执行SQL语句要效率高。
- 可以重用，以后提供参数就可以调用。
5.1 创建存储过程
create or replace procedure 存储过程名(变量名字 [in]|out 数据类型)
is|as
[变量名 数据类型;]
begin
代码;
end;
----------------------------------------------------------
in 表示输入参数，默认就是in，可以省略
形参长度不用写
out 表示输出参数，如果在存储过程中有输出值，可以使用out类型参数
is|as is或as都可以使用，类似于declare。用在存储过程中，可以定义在过程中使用的局部变量
5.2 调用语法
| 调用方式 | 语法 |
|---|---|
| 方式一 | call 存储过程名字(参数) |
| 方式二 | begin 存储过程(参数); end; |
5.3 案例：创建并使用存储过程
-- 创建存储过程
create or replace procedure proc_sal(v_ename in varchar2)
is
--2) 声明数值型变量，用于保存计算年薪的结果。
v_sal number;
begin
--3) 计算指定员工的年薪值，保存到上面的变量中
select salary * 12 into v_sal from emp where ename = v_ename;
-- 4) 在存储过程中打印结果出来
dbms_output.put_line(v_ename || '年薪是：' || v_sal);
end;
-- 调用存储过程
-- 方式一
call proc_sal('SCOTT');
-- 方式二
begin
proc_sal('KING');
end;
6. 存储函数
6.1 创建存储函数
create or replace function 存储函数名字(变量名字 [in]|out 数据类型)
return 返回类型
is|as
[变量名 数据类型;]
begin
return 返回值;
end;
-- 关键字说明
in 输入参数，可以省略
out 输出参数，用于接收函数中输出的值
return 指定函数返回类型
is|as 相当于declare，不能省略，后面定义局部变量
6.2 使用存储函数
- 存储函数是有返回值的，必须做为SQL语句一部分来调用。
6.3 案例：创建并使用存储函数
create or replace function func_annual_sal(v_ename varchar2) return number
is
v_sal number;
begin
-- 计算指定员工的年薪
select salary * 12 into v_sal from emp where ename = v_ename;
return (v_sal);
end;
-- 调用存储函数
-- 查询员工编号，姓名，月薪，年薪
select id, name, salary,func_annual_sal(name) 年薪 from emp;
7. 触发器
类似于JS中事件，当表中的记录执行DML语句，增删改操作的时候，自动执行代码块。
7.1 创建触发器
create or replace trigger 触发器名字
before | after 在DML之前或之后
insert | update | delete [of 列名] --在哪一种操作的时候触发，如果要指定多个操作可以使用or; 'of'可以指定操作哪一列的时候触发
on 表名或视图 -- 要操作的表或视图
[for each row] -- 加这个关键字是行级触发器，不加就是语句级触发器
[declare]
声明变量的部分，可以省略
begin
触发器的代码块；
end;
7.2 案例：创建并使用触发器
create or replace trigger trig_join_emp
after insert on emp
declare
-- local variables here
begin
-- 添加新的员工后，打印一句话“有新入职人员加入”
dbms_output.put_line('有新的员工入职');
end trig_join_emp;
insert into emp values(6390,'Redis','SALESMAN',7698,sysdate,3500,100,30);
commit;
1260
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
