表与表数据操作（重点）
DDL：操作数据库、表、列等（这些对象进行操作），使用的关键字：CREATE、 ALTER、 DROP。
DML是对表中的数据进行增、删、改的操作。关键字：insert、delete、updata
表结构和数据类型
表空间
-
表空间(Tablespace)是数据库的逻辑划分，一个Oracle数据库由若干个表空间组成。
任何数据库对象在存储时都必须存储在某个表空间中。
一个表空间对应于磁盘上的一个或多个数据文件。
表空间大小由数据文件决定，表空间存储容量等于数据文件大小之和。 -
创建表空间：
create tablespace 表空间名 datafile '地址' size 10m;
建表时未指定表空间，会存放在默认表空间，一般都是users，这个表空间是系统自己建立的。
- 把表移入指定表空间：
alter table 表名 move tablespace 表空间名;
- 删除表空间
drop tablespace 表空间名 including contents and datafiles;
说明：including contents 表示删除表空间时，删除该空间的所有数据库对象，而 datafiles 表示将数据库文件也删除。
表结构
- 进入PDB
alter session set container=pdborcl;
alter pluggable database pdborcl open;
- 查看数据表
desc 数据表名；
- 进入CDB
alter pluggable database pdborcl close immediate;
alter session set container = cdb$root;
数据类型在这里插入图片描述
DATE只记录日期
TIMESTAMP还保存时间
CLOB存放海量文字
BLOB存放图片、电影、音乐等的二进制文件
命令方式操作表（增删改）
修改表操作
- 查询当前用户的表
select table_name from user_tables;
- 创建表：create table 表名（字段属性）
- 修改表
（1）添加列
alter table 表名 add 列名 列类型 default 值，……
alter table 表名 modify 列名 列类型 约束条件 default 值......
modify表示修改数据表中已有的字段
（2）修改列名（修改宽度，不建议做）
Alter table 表名 rename column 旧列名 to 新列名
（3）删除列
Alter table 表名 drop column 列名
（4）修改表名
Rename 旧表名 to 新表名
（5）删除表
Drop table 表名
修改表记录
（1）添加数据记录
insert into 用户名.表名 values (值);--顺序与数据类型需要与表结构完全一致
insert into 用户名.表名 (字段名) values (值);
（2）修改数据记录
update 方案名.表名 set 字段名=值,...where条件表达式;
（3）删除数据记录
delete from 用户名.表明 where 条件表达式;
truncate table 用户名.表明;
这种方式只是删除了数据表中的全部记录，数据表的结构和约束仍然存在。删除大量数据时，truncate table更高效，但数据表中有外键约束存在，或者应用了视图和索引不能使用。
数据库的查询（重点）
select语句语法格式：
SELECT {[DISTINCT|ALL] columns | * | expression}
FROM {tables | views | other select}
[WHERE conditions ]
[GROUP BY columns ]
[HAVING conditions ]
[ORDER BY columns ] [asc|desc]
distinct 去掉重复值
where限定条件
- 以语句的形式输出，例：
select '编号为'||empno||'的雇员姓名是'||ename||'，其基本工资是：'||sal 雇员信息 from emp;
- 比较运算符的使用
例如： 查询scott用户的emp表中薪金大于2000的员工信息。
SELECT * FROM scott.emp WHERE sal>2000;
- 逻辑运算符的使用
作用：在WHERE条件子句中可以使用逻辑运算符把若干个查询条件连接起来，从而实现比较复杂的选择查询。
例：查询scott用户emp表中薪金在2000~3000元之间的雇员记录。
SELECT empno,ename,job,sal FROM scott.emp
WHERE sal>2000 AND sal<3000;
- BETWEEN和AND范围比较
作用：在WHERE子句中可以使用BETWEEN和AND关键字对表中某一范围内的数据进 行查询，这个指定范围内为一个连续的闭区间，包含区间的左右两个边界值。
例：查询scott用户下emp表中雇佣日期为1987年的员工记录。
SELECT * FROM scott.emp
WHERE hiredate BETWEEN ‘1-1月-1987’ AND ’31-12月-1987’;
- IN操作符
- 作用：用来查询某列的值在某个列表中的数据行
【例】对scott用户emp表进行检索。在WHERE子句中使用IN操作符
要求检索出empno列的值为7788、7800或7900的记录。
SELECT * FROM scott.emp
WHERE empno IN(7788,7800,7900);
- LIKE字符串匹配
作用：在WHERE中可以使用LIKE关键字进行模糊查询，用来查看某一列中的字符串是否匹配指定的模式。
a.下划线字符“_”：匹配指定位置的任意一个字符。
b.百分号字符“%”：匹配从指定位置开始的任意多个字符
注意：如果匹配的字符串中有下划线和百分号本身，则需要用ESCAPE选项标识这些字符即类似C、Java语言中的转义符号。
like '\_%'; --下划线开头
like '\%%'; --以百分号开头
like '$%%' escape '$'--自定义转义字符
- 空值查询
作用：在WHERE中可以使用IS NULL进行空值查询，is not null进行不为空的查询。
null参与运算时运算结果仍为null。a<>null永假。
order by排序
使用 ORDER BY 子句排序
ASC: 升序（默认）
DESC: 降序
ORDER BY 子句在SELECT语句的结尾。
注意：
- order by子句必须是查询语句的最后一个子句
- 如果在排序的列中有null值，该值被认为是最大，升序就会排在最后，降序就会排在最前
- 使用表达式排序
select * from emp order by sal*12 desc; - 使用列位置排序
select empno,ename,sal from emp order by 1;
当select子句有字段时，序号按字段的顺序计算；如果select子句使用*，则序号按表中字段顺序计算。 - 可以使用不在SELECT 列表中的列排序。
- 多列进行排序时，会先按照第1列进行排序，然后使用第2列对前面的排序查询结果中相同的值再进行排序。
分组函数（多行函数）与group by 子句
- 分组函数作用于一组数据，并对一组数据返回一个值。包括max,min,avg,count,sum。
COUNT(字段名) 返回 字段不为空的记录总数。除count外其余不能用*。 - group by 子句
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
包含在 GROUP BY 子句中的列可以不包含在SELECT 列表中
不能在 WHERE 子句中使用组函数。
可以在HAVING 子句中使用组函数。
因为分组函数是忽略空值的，如果需要对空值加以处理，则要使用函数nvl。NVL函数的格式：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
分组函数可以嵌套，但如果有嵌套函数不能进行分组。
连接查询
通常建立在有相互关系的父子表之间
主要包括内连接、外连接和自连接三种
连接查询通常需要使用表别名，以缩减语句的长度
别名一经定义，在整个的查询语句中就只能使用别名而不能使用表名。
别名只在所定义的查询语句中有效
查询语句的执行顺序：from子句最先执行，然后是where子句，最后是select子句
内连接
根据指定的连接条件进行连接查询，只有满足连接条件的数据才会出现在结果集中。
一般使用inner join来实现。其中inner关键字可以省略
通常在from子句之后定义一个on子句，用来指定连接条件
也可以通过using 子句指定连接条件
还可以通过where子句指定连接条件
如果多个表之间存在同名的列，则必须使用表别名来限定列的引用
例：查询部门编号为10的员工编号和姓名，及所在的部门名称和地点
- 第一种方式：使用on连接
select e.ename,e.job,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno where e.deptno=10;
- 第二种连接方式：使用using子句（关联字段名称相同，也有join，不能有表别名）
select empno,ename,deptno,dname from emp join dept USING(deptno);
- 第三种连接方式：使用where子句
select e.ename,e.job,d.dname,d.loc from emp e ,dept d where e.deptno=d.deptno and e.deptno=10;
外连接
扩展了内连接的结果集
不只列出与连接条件匹配的行，还能够列出左表（左外连接）、右表（右外连接）或两个表（完全外连接）中所有符合搜索条件的数据行。
左外连接：关键字为left outer join或left join
右外连接：关键字为right outer join或right join
完全外连接：关键字为full outer join 或 full join
例：
完全外连接
SELECT e.empno,e.ename,d.deptno,d.dname from emp e full join dept d on e.deptno=d.deptno;
左外连接
SELECT e.empno,e.ename,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno;
右外连接
SELECT e.empno,e.ename,d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno;
- （+）的使用
（+）操作符要放在非主表的一方（左外连接中左表为主表，右外连接中右表为主表），并且需要使用where子句，不能存在outer join关键字。
select ename,sal,dname from emp e,dept d where e.deptno=d.deptno(+);--左外连接
select ename,sal,dname from emp e,dept d where e.deptno(+)=d.deptno;--右外连接
自连接
- 主要用在自参考表上显示上下级关系或层次关系
自参考表是指在同一张表的不同列之间具有参照关系或主从关系的表
例：显示雇员对应的管理者
select m.ename 领导者, e.ename 雇员 from emp e join emp m on e.mgr= m.empno;
子查询
- 指嵌入在其他sql语句中的select语句，也叫嵌套查询
常见形式：
select 字段列表 from 表 where = (select……);
分类:单行子查询、多行子查询
单行子查询
- 只返回一行数据的子查询语句
例：查询与SMITH同部门的所有员工
思路：
--查询出SMITH的部门号
select deptno from emp WHERE ename = 'SMITH';
--显示
SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH');
多行子查询
- 返回多行数据的子查询，其运算符包含：in all any
例：查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
思路：
查询部门10的工作
Select job from emp where deptno=10;
显示
Select ename,job,sal,deptno from emp where job in(Select job from emp where deptno=10);
例：查询工资比任意一个部门的平均工资高的员工信息。
Select * from emp where sal>any(select avg(sal) from emp group by deptno);
例：查询工资比所有部门的平均工资都高的员工信息
Select * from emp where sal>all(select avg(sal) from emp group by deptno);
关联子查询
- 内查询的执行需要借助外查询，而外查询的执行又离不开内查询的执行，这时，内查询和外查询是相互关联的，这种子查询就被称为关联子查询。
- 在关联子查询中，内查询能够引用外部查询的列，对外部查询的每一行，内部查询都要执行一次。
例：查询工资高于同职位平均工资的员工信息 - 第一种思路：表连接方式
（1）先求各职位的平均工资
Select job,avg(sal) avgsal from emp group by job;
（2）将（1）的结果集看作一张表，与emp表连接
From emp,( Select job,avg(sal) avgsal from emp group by job) ae where emp.job=ae.job;
（3）两表连接后，搜索满足工资大于平均工资的员工
Where sal>avgsal
完整写法：
select empno,ename,e.job,sal,avgsal from emp e,(select job,avg(sal) avgsal from emp group by job) ae where e.job=ae.job and sal>avgsal;
- 第二种思路：使用关联子查询
先写外部查询：
select empno,ename,job,sal from emp where sal>
再写子查询：各职位平均工资：
select job,avg(sal) avgsal from emp group by job
最后加条件：job=e.job
伪列
不属于任何表，但可以在任何表中都直接使用
- rowid：用于表示地址
在数据表中，即使各行中所有列值都相同，rowid的值也不相同 - rownum:主要作用是生成行号
对每一个结果集，其值都从1开始递增，直到末尾
使用rownum时
只支持<、<=、和、！＝符号
不支持>、>=、=和between…and符号
例：查找emp表中第3到第5条记录
先求前5条记录
Select rownum,empno,ename from emp where rownum<=5;
把上面的查询结果作为一张表，这样rownum就可以当作一个普通字段使用，此时需要给该列起别名
完整写法：
select * from (select rownum r,empno,ename,sal from emp where rownum<=5) re where r>=3;
数据完整性（重点）
- 约束就是保证数据库完整性的方法，数据库的完整性就是为了保证数据的正确性。
- 概念：在数据表上强制执行的一些数据校验规则。当执行增、删、改操作时，数据必须符合在相关字段上设置的校验规则
- 作用：保证数据的准确性
- 内容：
非空、唯一、主键、外键和检查 - 分类：列级约束＼表级约束
创建
约束条件是依附于表的，可以在建表时创建，也可以在建表后创建。
它是一种规则，和表一样存放在数据字典中。也需要命名，或者由系统自动命名。如果是用户命名，一般遵循以下格式：表名_列名_约束条件类型；而Oracle命名，则按照SYS_Cn的格式，其中n为一串数字。
- 语法
建表时：直接加在列名后面
建表后：使用alter table 表名
＋add constraint 约束名 约束类型（字段名）
或
使用修改方式添加约束
Alter table 表名 modify 字段名 约束类型
非空
- 含义：(not null)确保该字段值不为空。默认情况下，任何字段值都可以为空
操作： - 在建表时创建非空约束
create table book(bid number(4) NOT NULL,title varchar2(20));
insert into book(title) values(‘tmie’);
- 建表后创建约束
alter table book modify(title NOT null);
alter table book modify(title varchar(2) NOT null);
- 添加列时，也可以直接添加约束
alter table book add author varchar2(10) NOT NULL;
- 在数据字典中查询约束
desc user_constraints;
select owner,constraint_name,constraint_type,table_name from user_constraints
where table_name='BOOK';
注意：如果建表后为某列添加非空约束，并且表中该列数据已经存在null值，则向该列添加约束的行为将失败。这是因为列应用非空约束时，Oracle会检查表中所有的行，以验证所有行在对应列上是否存在空值。
唯一
- 含义：(unique)保证字段或字段的组合不出现重复值。当给表的某个列定义了唯一约束条件时，该列的值不允许重复，但允许值是空值。
- 建表时：创建唯一约束
Create table student(sid number(4) constraint student_sid_nn not null,user_name varchar2(10),
email varchar2(20),constraint student_email_uk unique(email));
- 建表后：创建唯一约束
alter table student add constraint student_user_name_uk unique(user_name);
定义唯一约束后，字段值不允许重复
insert into student values(1001,'jone','3245@qq.com');
insert into student values(1001,'nice','3245@qq.com');
非空允许重复，所以第一个值没错。第三个值错了。
添加列时指定约束
alter table student add salary number(5,2) unique;
可以在多列上建立约束，必须是表级约束。
alter table student add(ri varchar2(4),qq varchar2(3));
alter table student add constraint ss unique(ri,qq);
- 在数据字典中查看约束信息
select owner,constraint_name,constraint_type from ser_constraints
where table_name='STUDENT';
主键
- 含义：从功能上看相当于非空且唯一。主键字段可以是单字段或多字段组合，意味着在主键约束下的单字段或多字段组合上不允许有空值，也不允许有重复值。
- 主键可以用来在表中唯一确定一行数据
- 在一个表上只允许建立一个主键，而其他约束条件则没有明确的个数限制。
- 建表同时创建列级主键约束，由系统自动赋予约束条件名称
Create table student(sid number(4) primary key,user_name varchar2(10));
- 建表同时创建表级主键约束，自定义约束条件名称
Drop table student;
Create table student(sid number(4),user_name varchar2(10),
constraint student_sid_pk primary key(sid));
- 建表后创建表级主键约束，自定义约束条件名称
Create table student1(sid number(4),user_name varchar2(10));
alter table student1 add constraint student1_sid_pk primary key(sid);
默认值
- 创建表时创建：
Create table table_name(columnu data_type default value);
- 修改表时创建：
Alter table table_name modify column data_type defaule value;
外键
- 含义：该条件定义在两个表的两个字段或一个表的两个字段上，用于保证相关两个字段的关系。简单来说，就是外键用于与另一张表的关联。
- 条件
外键约束条件包括两个方面的数据约束：- 子表上定义的外键的列值，必须从主表被参照的列值中选取，或者为null
- 当主表参照列的值被子表参照时，主表的该行记录不允许被删除。
- 创建外键约束：
（1）主表要有主键：
（2）子表上对应主表主键的列建立外键约束
（3）验证：
不能删除主表上的值（如果子表使用了这个值）
子表中也不能插入主表主键没有的值
子表中可以插入空值（主表主键上对应的列）
alter table dept2 add constraint dept2_deptno_pk primary key(deptno);
alter table employee add constraint employee_deptno_fk foreign key(deptno) references dept2(deptno);
- 在数据字典中查询
select constraint_name,constraint_type from user_constraints
where table_name='EMPLOYEE';
- 删除操作中关键字on的使用
在创建约束时指定
（1）禁止删除，这是oracle默认的。
（2）on delete casecade：用于指定级联删除选项。
（3）on delete set null：用于删除主表时，将从表的外键列的值设置为null
检查
- 含义：强制在字段上的每个值都要满足check中定义的条件。当定义了check约束的列新增或修改数据时，数据必须符合check约束中定义的条件。
- 建表时创建：
create table usertable1(userid number(3),username varchar2(10),salary number(7,2)
check (salary>800)); - 建表后创建 ：
alter table usertable add constraint usertable_salary check (salary>800); - 验证：
insert into usertable values(101,‘rowd’,897);//执行成功
insert into usertable values(102,‘rtyu’,798);//执行失败
数据库其他对象
视图：创建、查询、更新和修改
概念
从一个或多个表或视图中导出的虚表，其结构和数据是建立在对表的查询基础上的
- 实质：一条查询语句
- 优点：安全、方便、一致性
创建
Create [or replace] [force |no force]view 视图名 as Select语句
Create view v_emp as
select * from emp where sal>2000
提示1：用户需要有create view的权限
grant create view to scott;
提示2：视图中如果对字段进行了函数或数学计算，则必须为该字段定义别名。
- 默认情况下创建的视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图。
create view v_emp3(no,new_sal) as select empno,sal from emp;
要求：视图的列数必须与子查询的列数相同。
- 创建没有源表的视图
Create force view view_name
As select子句
注：Select语句中的表是不存在的
查询
desc 视图名--查看视图结构
select * from 视图名--查看视图内容
select * from user_views;--查看视图定义
DML操作
对视图进行DML操作，实际上就是对视图的基表中的字段执行DML操作。
Oracle会自动判断哪些视图可以更新
desc user_updatable_columns;
- 查看已创建视图v_emp2中的字段是否支持DML操作。
select * from user_updatable_columns where table_name='V_EMP2';
- 两条子句
- with check option
限定对视图的DML操作必须满足子查询的条件 - with read only
只能读取视图中的数据，不允许执行增删改操作
更新和修改
- 通过增加or replace子句修改视图定义
create or replace view v_emp as select * from emp
- 删除视图
drop view view_name
删除视图，只是删除定义，相当于删除了它和表之间的关联，对表中数据没有影响。
- 键值保存表规则：
键值保存表：在连接视图中，一个基础表的主键在视图中存在，并且在视图中仍然是主键。 - 规则：在连接视图中，只能修改键值保存表的数据，不能修改非键值保存表的数据。
索引：概念、创建、维护和删除
概念
- 是一个单独的、物理的数据库对象
用于存放表中每一条记录的位置的对象。
在创建索引时，先要对索引字段进行排序。
索引由Oracle自动维护
优点：提高查询速度
缺点：创建和维护索引需要时间；索引需要物理空间，随着数据量的增大而增大； - 分类
按照索引数据的存储方式可以将索引分为B树索引、位图索引、函数索引、簇索引、反序索引等。
按照索引列的唯一性又可以分为唯一索引和非唯一索引
按照索引列的个数索引可以分为单列索引和复合索引
创建
自动创建：通过约束，系统创建
手工创建
create [unique] [bitmap] index index_name on table_name(column_name)
[reverse] [tablespace tablespace_name]
- 创建B树索引
create index dname_dept on dept(dname);
默认情况下，创建的索引是不唯一索引
- 创建基于函数的索引
create index ename_lower_index
on emp(lower(ename));
select * from emp
where lower(ename)='smith';
- 创建复合索引
create index emp_idx on emp(job,ename);
--适合于查询where job=‘’ and ename=‘’
维护和删除
- 使用数据字典user_indexes，查看当前用户下所建立的索引。
select index_name, index_type , table_name, tablespace_name from user_indexes;
- 查看相应的列
select * from user_ind_columns;
- 重命名索引
alter index index_name rename to new index_name
alter index dept_deptno_pk rename to pk_deptno_dept;
- 删除索引与索引创建时采用的方式有关
drop index index_name;
通过禁用约束或删除约束的方式来删除对应的索引。
完全删除所有索引
序列：创建、使用和修改
- 概念：
是数据库对象。
用于产生一系列唯一的数字值
序列号的生成独立于表
同一序列生成器可用于一个或多个表
经常用来产生主键值
创建
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
- 创建一个名称为 dept_seq 的序列，可以给DEPT表的主键提供值.
create sequence dept_seq
increment by 10
start with 50
maxvalue 100
cycle
nocache;
- 使用视图 USER_SEQUENCES 可以查到序列信息.
desc user_sequences;
select equence_name,
min_value,max_value,cache_size
from user_sequences;
使用
- NEXTVAL 返回下一个有效的序列值.
每执行一次会返回一个新的数值。 - CURRVAL返回当前的序列值.
- 通过插入记录使用序列
insert into dept values(myseq.nextval,' MARKETING ',SHANDONG');
修改
- 可以修改增量值，最大值，最小值，循环选项，缓存选项.
不能修改初始值
ALTER SEQUENCE dept_deptno
INCREMENT BY 1
MAXVALUE 999999
NOCACHE
NOCYCLE;
使用 DROP SEQUENCE 语句删除序列.
drop sequence dept_seq;
同义词：创建
- 同义词是数据库对象的别名。
通过同义词可以简化对象的访问。
CREATE [PUBLIC] SYNONYM 同义词名
FOR 对象名;
- 使用drop synonym
DROP SYNONYM dept_seq;
PL/SQL语言
匿名块
- PL/SQL块由三个部分组成：声明部分、执行体和异常处理。其中声明和异常处理是可选的，执行部分是必须的。
DECLARE --标记声明部分
…… --此处用来定义常量、变量、类型和游标等
BEGIN --标记程序体部分开始
…… --此处用来编写各种PL/SQL语句、函数和存储过程
EXCEPTION --标记异常处理部分开始
…… --此处用来编写异常处理代码（when…then结构）
END; --标记程序体部分结束
- 输出语句：dbms_output.put_line
- 替代变量：&用于接收用户的输入
- 连接符：||
- 显示输出结果：set serveroutput on
- 简例：根据员工编号输出员工的工资
set serveroutput on
declare
v_sal number;
v_no number:=&no; --提醒：如果该变量为字符类型，则需加引号，写成'&no'的形式
begin
select sal into v_sal from emp where empno=v_no;
dbms_output.put_line('编号为'||v_no||'的员工工资是：'||v_sal);
EXCEPTION
when no_data_found then
dbms_output.put_line('没有这个员工');
end;
PL/SQL变量、常量和数据类型
数据类型：number varchar2 char int date long Boolean
变量：（一）基本变量：变量名 数据类型
（二）复合变量：
%type 以表中字段定义变量类型，或以已经声明过的变量作为变量类型
%rowtype 记录变量，表示的是一行数据。可以是表中的一条记录，也可以是自定义的一条记录。
表示表中一行记录：emp%rowtype ，意思是该变量中保存的数据结构和emp表完全一样。
自定义记录变量语法：
TYPE type_name IS RECORD (field_name field_type,….);
Identifier type_name;
–identifier是标识符，表示一个变量名，type_name就是自定义的记录类型。这句话其实就是声明一个变量，变量的类型是自定义的记录类型。
案例：
DECLARE
TYPE emp_record_type IS RECORD(name emp.ename%type,sal number(5,1),job emp.job%type);--自定义一个记录类型，包含emp表中部分字段。
myemp emp_record_type;--使用自定义的类型声明一个记录变量
BEGIN
select ename,sal,job into myemp from emp where empno=7369;
DBMS_OUTPUT.PUT_LINE(myemp.name||' '||myemp.sal );
END;
提醒：
- select语句后面不能用*，因为emp表中包含的字段更多，必须表明有哪些字段。
- 输出语句中不能只写myemp，必须用加点形式输出每一个字段值。
PL/SQL语言基本结构
流程控制
选择结构：if语句和case语句
- 最简单的分支：
if condition then
Statement1
….
Statement
End if
- 双分支：
IF condition THEN
Statement1
ELSE
Statement2
END IF
Statement3
如果条件为真，则执行语句1，如果为假，则执行语句2，无论执行了哪一条语句，在执行结束后，都会继续执行语句3。
- 案例：查询emp表中JAMES的工资，如果大于1500元，则发放奖金100元；如果工资大于900元，则发放奖金800元，否则发放奖金400元。
分析：通常需要判断条件的字段，应该被定义为变量。
DECLARE
v_sal emp.sal%type;
BEGIN
select sal into v_sal from emp where ename='JAMES';
IF v_sal>1500 THEN
UPDATE emp set comm=100 where ename='JAMES';
ELSIF v_sal>900 THEN
UPDATE emp set comm=800 where ename='JAMES';
ELSE
UPDATE emp set comm=400 where ename='JAMES';
END IF ;
END;
- case 表达式
分为两种：一种叫选择器，一种叫搜索式
- 选择器形式的语法
case selector
when expr1 then statement1
when expr2 then statement2
…
else statement
end case;
Selector：待求值的表达式，即选择器
expr1是与选择器进行比较的表达式。如果二者相等，则执行then后面的语句1
依此类推，一旦有一个表达式与选择器值相等，则执行其后面的语句，而其他语句则不再进行比较和执行。
如果所有表达式都与选择器不匹配，则执行else后面的语句。
- 搜索式语法
case
when condition1 then statement1
when condition2 then statement2
…
else statement
end case
- 搜索式case语句没有选择器，并且，它的when子句只能包含结果为布尔类型的表达式，产生其他类型结果的表达式是不允许的。
- 当搜索条件（condition）的计算结果为真时，执行其then后的语句。如果任何搜索条件都为false，则就会执行与else子句相关的语句。其中else子句是可选的。
存储过程和触发器（重点）
创建过程
- 概念
是一个命名的程序块
这种有名字的PL/SQL块称之为PL/SQL存储程序单元或子程序，是被存储到数据库中的。
子程序又分为4种：存储过程、函数、包和触发器。
过程主要用于在数据库中完成特定的操作或者任务。 - 存储过程优点
简化客户端应用程序的开发和维护
提高应用程序的运行性能
无需通过网络传输源代码，降低了网络通信的负担，运行速度快
自动完成需要预先执行的任务：
可以在系统启动时自动执行，而不必再进行手工操作，大大方便了用户的使用。
create or replace procedure name [parameter,…] --头部
IS｜AS--两者都可，选其一
变量，常量声明; --即使没有变量、常量，is|as也不能省略
BEGIN
Sql语句和PL/SQL语句
[EXCEPTION]
异常处理语句
END;
创建简单无参的存储过程
--创建一个过程，能输出“hello world”
create or replace procedure sayhello
is
begin
dbms_output.put_line('hello world');
end;
过程的调用
- 使用execute命令
exec sayhello(); --括号必须有 - 使用call命令
call sayhello(); - 在匿名的程序块中直接以过程名调用
BEGIN
sayhello();
END;
有参的存储过程
- 在建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)。
- 通过在过程中使用输入参数，可以将数据传递到执行部分；
- 通过使用输出参数，可以将执行部分的数据传递到应用环境。
- 过程参数的语法：
parameter_name in|out|in out|datatype [:=|default expr]
In：表示参数是输入给过程的
out：表示参数在过程中将被赋值，可以传给过程体的外部
例：创建一个过程，用于给指定的员工涨100元钱。并且输出涨前和涨后的工资。
create or replace procedure raisesal(p_no emp.empno%type) is
p_sal emp.sal%type;
begin
select sal into p_sal from emp where empno=p_no;
update emp set sal=sal+100 where empno=p_no;
dbms_output.put_line('涨前：'|| p_sal||'，涨后：'||(p_sal+100));
end;
begin
raisesal(7369);
end;
带in参数的过程的调用
- 按位置调用
exec raisesal(7369); - 按名称调用
在调用存储过程的参数列表中不仅提供参数名，还指定给它传递的参数值两部分。
exec raisesal(eno=>7369) - 可以设置参数的默认值
如果调用时不指定值，则为默认值
create or replace PROCEDURE raisesal(eno in emp.empno% TYPE default 7369)
exec raisesal();
带out参数的存储过程
例：查询指定员工的姓名和薪水，并利用out模式参数值传给调用者。
create or replace procedure show(p_no emp.empno%type,p_name out emp.ename%type,p_sal out emp.sal%type)
is
begin
select ename,sal into p_name,p_sal from emp where empno=p_no;
exception
when NO_DATA_FOUND then
dbms_output.put_line('没有这个员工');
end;
在匿名块中调用
declare
eno emp.empno%type:=&eno;
v_name emp.ename%type;
v_sal emp.sal%type;
begin
show(eno,v_name,v_sal);
dbms_output.put_line('编号为'||eno||'的员工姓名是：'||v_name||'，其薪水是：'||v_sal);
end;
在命令窗口通过绑定变量调用
var v_name varchar2(10); --不能用字段类型
var v_sal number; --number类型不能加长度
exec show(7369,:v_name,:v_sal); --变量绑定，调用存储过程
print v_name v_sal;
select :v_name,:v_sal from dual;
管理过程：查询、执行、修改和删除
查询
- 存储过程的信息都存储在数据字典中
（一）user_objects显示有关对象的信息。
（二）user_source显示源代码文本。
select object_name,object_type,status from user_objects where object_name='SHOW';
select line,text from user_source where name='SHOW';
修改和删除
- 修改
or replace - 删除
当某个过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。
语法格式：
DROP PROCEDURE 过程名
drop procedure raisesal
函数的创建和调用
函数
- 可以接收传递的值
- 必须有返回值
- 函数必须有return子句
- 函数中不能包含对数据库执行操作的语句，如DML语句（对表进行操作）
函数的创建
Create or replace function function_name[(argment in|out|in out type)]
Return return_type
Is|as
[declare_section]
Begin
Function_body—也必须要有return语句
Exception
…
End;
例：
定义一个函数，用于计算emp表中某个部门的平均工资。
create or replace function get_avgsal (dno emp.deptno%type)
return emp.sal%type
is
avgsal emp.sal%type;
begin
select avg(sal) into avgsal from emp where deptno=dno;
return avgsal;
end;
函数的调用
- 在SQL语句中直接调用
select distinct deptno,get_avgsal(10) from emp where deptno=10;
select get_avgsal(10) 平均值 from dual;
- 使用输出语句调用函数
set serveroutput on
BEGIN
dbms_output.put_line(avgsal(10));
END ;
函数的修改、删除和查看
- 修改：or replace
- 删除：drop function 函数名
注意：删除时不要加括号和参数 - 查看（注意大写）
select object_name from user_objects where object_type='FUNCTION';
select line,text from user_source where name='GET_AVGSAL';
触发器
概念
- 触发器定义：与数据库操作有关的某个事件发生时数据库将要执行的操作
简单说：触发器是在特定事件出现的时候，自动执行的代码块 - 触发器存放在数据库内，也是命名的PL/SQL块
- 一种特殊的存储过程，与存储过程的区别：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。其执行过程是隐式的。
另一个区别：没有参数
触发器的作用
- 安全性
如：基于时间限制用户的操作
如：基于数据限制用户的操作（涨工资、股票） - 审计
跟踪用户对数据库的操作（日志）
实现复杂的数据完整性规则
实现数据的备份和同步 - 触发器会造成DML性能的下降
触发条件
- 触发事件（类型）
DML语句事件，执行insert、update、delete等语句时触发的事件
DDL语句事件，执行create、alter、drop语句时触发的事件
系统错误，当oracle数据库系统出现错误时触发的事件
数据库事件，startup、shutdown、logon、logoff、servererror - 触发时间
Before在指定的事件发生之前执行触发器
After在指定的事件发生之后执行触发器 - 触发级别
行触发：对触发事件影响的每一行执行触发器
语句触发：对于触发事件只能触发一次，而且不能访问受触发器影响的每一行的值 - 触发限制
- 触发对象
创建触发器
Create or replace trigger <触发器名>
[before|after]
<触发事件> on <表名>
[for each row]
[when <条件表达式>]
<PL/SQL程序体>
DML触发器
- 语句级触发器
例：禁止在非工作时间插入新员工，这是基于时间限制用户的操作
create or replace trigger adduser_trigger before insert or delete on emp
declare
riqi VARCHAR2(20):=to_char(sysdate,'dd');
begin
if riqi <>10 then
raise_application_error(-20001, '不是操作日');--错误编号20000~29999避开系统错误标号
end if;
end;
- 行级触发器
行级触发器中有两个伪变量，用于识别值的状态
:old 操作之前，是记录变量，使用形式：:old.字段名
:new 操作之后，是记录变量，使用形式：:new.字段名
只在DML触发表中字段时才有效，只能在触发器内部使用 - 例：数据完整性检查：
限制雇员工资的调整，只允许提高，不能降低。
create or replace trigger addsal before update on emp for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20001, '工资降低');
end if;
end;
- 数据库的审计：
当员工工资超过6000元时，查看这个员工的信息。
操作：建表存储该员工的信息
Create table shji_info(info varchar(200));
创建触发器，当更新员工工资时触发，如果员工资超过了6000，则向表中插入一条记录，记录该员工的编号，姓名和工资
create or replace
trigger shji_info
after update on emp
for each row
begin
if :new.sal>6000 then
insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
end if;
end;
- 数据的备份和同步
员工涨工资后，会同步备份到备份表中
操作：建立一个备份表emp_copy
Create table emp_copy as select * from emp;
当emp表中的员工涨工资后，在触发器中更新备份表中员工的工资
create or replace trigger copysal
after update on emp
for each row
begin
update emp_back set sal=:new.sal where empno=:new.empno;
end;
触发器执行顺序
当一个表中有多个触发器时，通常按如下顺序执行
- before表级触发器
- before行级触发器
- 更新操作
- after行级触发器
- ater表级触发器
- 三个谓词：inserting deleting updating
用于判断在DML操作中触发的具体事件
如：如果事件是insert，则inserting的值为true，否则为false
替代触发器（instead of）
事件触发器
DDL事件
数据库事件
安全管理
用户管理：创建、修改和删除
用户
也称为账户，它是Oracle使用者的身份证明
用户需要使用正确的用户名和密码才能连接数据库
用户权限不同，允许访问的对象和执行的操作是不一样的。
- 方案（模式schema）：一个特定用户下的对象集合，创建一个用户就会创建一个方案，一个用户只能关联一个方案。用户名就是方案名
初始用户
- 管理员：sys和system
sys：可以创建、启动、关闭和修改数据库
system：不能创建和启动、关闭数据库 - 普通用户：scott、hr(pdb模式)
除管理员外，其余用户在创建后处于锁定状态
软件项目一般不建议使用这些初始用户
创建用户
创建用户：
CREATE USER username
IDENTIFIED BY password
DEFAULT TABLESPACE tablespacename
TEMPORARY TABLESPACE tablespacename
QUOTA size ON tablespacename;
例：创建一个mr用户，口令为mrsoft，并设置默认的表空间为users，临时表空间为temp的用户
create user c##mr identified by mrsoft
default tablespace users
temporary tablespace temp;
-- 创建scott用户
create user c##scott identified by tiger;
-- 设置用户使用的表空间
alter user c##scott default tablespace users;
alter user c##scott temporary tablespace temp;
-- 为用户授权
grant connect,resource,unlimited tablespace to c##scott container=all;
查看用户信息
desc dba_users;
desc user_objects;
select * from dba_users;
select * from user_objects
修改用户
--修改密码
Alter user 用户名 identified by newpwd
password
--用户锁定与解锁
Alter user username account lock
Alter user username account unlock
--分配表空间配额
Alter user username quota size on tablespace
- 修改用户east在表空间上的磁盘限额为20m（原始为10m，增加10m）。
alter user c##east quota 20m on tbsp_1;
- 修改用户east的新口令为123456（原始为mrsoft）。
alter user c##east identified by 123456;
删除用户
drop user username [cascade]
- 如果用户方案中包括任何对象，在删除用户时，必须加上cascade短语。
- 如果用户当前正与Oracle服务器连接，则不能删除。
- 只有管理员才能删除用户
权限管理：分类、授权和回收
- 概念：执行特定类型的SQL语句或访问一个用户的对象的权利
Oracle数据库使用权限来控制用户对数据的访问和用户所能执行的操作。 - 分类：系统权限和对象权限
系统权限
- 数据库级别执行某些操作的权限
create session
create tablespace
create table
create view
Alter命令
Drop命令
对象权限
- 用户对某个特定的数据库对象执行某种操作的权限
- 指对数据库中的特定对象（如表、视图、序列等）的访问控制权限，以及用户可以在这些数据库对象上执行哪些操作
- 数据库用户拥有对自己的对象的所有对象权限，所以对象权限管理实际上是对象所有者对其他用户操作该对象的权限管理。
- 常用权限：增删改查
授权
Grant 系统权限 to user
Grant 对象权限 on 数据对象 to user
grant create session to xiaoming
grant select on emp to xiaoming
权限的传递
with admin option
with grant option
允许被授予者进一步为其他用户授予权限
回收权限
- 撤销系统权限
revoke 权限 from user
撤销系统权限时没有级联效果。 - 撤销对象权限
revoke object_privilege [all] on object from user
级联问题：如果对象是用with grant option授予的，则撤销对象权限也将导致级联撤销 - 从xiaoming用户撤销scott.emp表的update和delete权限
revoke delete,update on scott.emp from c##xiaoming;
- 撤销xiaoming用户的create session系统权限
revoke create session from c##xiaoming;
查看权限
可以通过查询以下视图来获取有关权限的信息：
dba_sys_privs：列出授予用户和角色的系统权限
session_privs：列出用户当前可用的权限
dba_tab_privs：列出对于数据库中所有对象的所有权限
dba_col_privs：描述数据库中的所有对象的授权
角色管理：概念、创建、授权、应用（将角色分配给用户）和回收
概念
- 角色：一系列权限的集合，目的在于简化对权限的管理
Oracle数据库允许将一组相关权限授予某个角色，然后将这个角色授予需要的用户，拥有该角色的用户将拥有该角色包含的所有权限。 - 分类：预定义角色和自定义角色
- 预定义角色
connect：登录数据库，执行查询语句和操作
resource：create table,对该表的所有权限，提供给用户另外的权限以创建他们自己的表、序列、过程、触发器、索引和簇。
DBA：执行某些授权命令、创建表，以及对任何表的数据进行操纵。它包括了上面两种角色的操作。 - 自定义角色
用户根据业务需要自己创建具有某些权限的角色，然后为角色授权，最后再将权限分配给用户。
创建
有两种方式
- 不验证
create role 角色名 - 数据库验证
Create role 角色名 identified by 密码
授权和应用
- 为角色授权
grant all on emp to testrole; - 为用户授予角色
grant testrole,create session to tom; - 回收权限
revoke testrole,create session from tom - 对象权限只能单独授权和单独回收
- 系统权限和角色可以在一条语句中完成授权和回收
- 一个用户可以被授予多个角色
- 一个角色也可以被授予多个用户
- 一个角色还可以被授予另一个角色
回收
- 设置角色口令
alter role role_name not idenitfied|identified by password
- 删除角色
drop role role_name
删除角色后，原来拥有该角色的用户将失去相应的权限
9469
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
