文章目录

• Oracle知识点
• 表空间、用户、角色
• 表空间
• 获取表空间与数据文件的关系
• 查看数据块大小
• 查询当前用户所拥有的表的信息、可以访问的表的信息、当前数据库所有表的信息
• 查询系统中所有表空间的信息
• 创建表空间
• 改变表空间的大小,添加新的数据文件
• 修改表空间的已有的数据文件大小
• 设置默认表空间
• 查看当前数据库所有用户信息
• 查询数据库中有哪些容器
• 打开pdb--
• 将当前公共区的会话转到容器--
• 创建新用户test01并绑定到demospace--
• 赋予text01连接数据库的权限--
• 赋予test01查询scott.emp表的权限(并获得权限的传递性)--
• 再创建新用户test02--
• 查看所有对象--
• 为用户授予登陆权限--
• 为用户授予创建表、创建序列的系统权限--
• 为用户授予表空间'USERS'权限--
• 将marcs模式的id表的部分对象权限授予用户test--
• 角色
• 创建一个角色 role_emp--
• 将对emp表的查询权限赋予角色role_mep--
• 将对emp表的所有权限赋予角色role_mep--
• 把role_emp角色赋予给test01--
• 把role_emp角色从test01回收--
• 查询test01所具有的所有角色--
• 表管理
• 表的创建
• 在当前用户默认表空间创建department表--
• 查看表结构--
• 在users表空间创建employee表--
• 表的维护
• 添加列--
• 修改列定义--
• 修改列
• 删除列--
• 将列标记为unused状态(禁用某一列)，然后删除unused状态列--
• 为表添加注释--
• 为列添加注释--
• 修改表名--
• 删除department表及对其的外键引用--
• 查询当前用户的所有表的名称、表空间及状态
• 查询当前用户所有表的注释信息--
• 查询某个表中某列的描述信息--
• 完整性约束
• 创建表时为各表添加列级约束--
• 创建工资发放记录表时添加的表级约束--
• 查询payroll表的所有约束信息--
• 查询payroll表中的各个约束所作用的列--
• 创建职位表job和部门表deptment--
• 添加主键约束--
• 添加唯一性约束--
• 添加外键约束--
• 添加检查约束 需要建立表级约束(起个名字)--
• 添加非空约束--
• 对外键约束fk_job_dept重命名--
• 通过名称删除外键约束--
• 删除主键约束--
• 删除主键约束时保留唯一索引--
• 删除唯一性约束--
• SQL基础
• 数据管理
• 向部门表插入一行记录--
• 向员工表插入一条记录--
• 将scott模式的dept表的记录插入deparment表中--
• 将scott模式的emp表的记录插入employee表中--
• 查询部门表中所有列的记录--
• 查询符合条件的员工的部分列的信息表
• 将员工表中所有员工的奖金更新为200元--
• 将工号为7210的员工的工资更新为原有工资与奖金的和--
• 依据条件字句删除记录--
• 删除有外键约束引用的表中的所有记录(表中内容)--
• 使用truncate语句删除有外键约束引用的表中的所有记录(无法用rollba恢复)--
• 事务控制
• 事务的开始与结束
• commit命令显示提交事务--
• rollback命令回滚单条dml语句事务--
• rollback命令回滚多条dml语句事务--
• 设置保存点--
• 并发事务
• 并发事务可能导致的3类问题
• 事务的隔离级别
• 设置数据库事务为serializable隔离级别--
• 设置数据库事务为read committed隔离级别--
• 设置数据库事务为read only隔离级别--
• 修改数据库事务为serializable隔离级别--
• 修改数据库事务为read committede隔离级别--
• 幻读实例--
• 修改事务的隔离级别为序列化实例--
• 修改事务的隔离级别为read only实例--
• 数据查询
• 基本查询
• distinct运算符（去除重复数据）--
• 连接运算符||
• 限定查询与排序(where)
• 查询工资在1500到2900之间的员工信息
• 查询职位为salesman的人员名单
• between and 操作符(闭区间)|not between and
• 查询在1981年1月1日和1981年12月31日之间加入公司的员工--
• in：用来测试某些值是否在列表中|not in
• 查询职位为销售、文员或经理的人员
• null(表示为空)--
• like模糊查询--
• 查询员工姓名是以M开头的所有员工的信息--
• 查询姓名以A开头、姓名最后连个字符为E和一个任意字符的员工信息--
• 使用转义操作符escape,使字符表示本意--
• order by 排序-- asc升序(默认)--desc(降序)--
• 单行函数
• 字符函数
• lower(列|字符串)的使用--转换为小写
• upper(列|字符串)的使用--转换为大写
• length(列|字符串)的使用--返回长度*
• initcap(列|字符串)的使用--开头首字母大写
• substr(列|字符串,开始点[,长度])的使用--截取*
• replace(列|字符串,要搜索的字符串，替换的字符串)的使用--替换*
• concat(列|字符串,列|字符串)的使用--拼接*
• rpad(列|字符串,长度，填充字符)的使用--填充
• ltrim(字符串)、rtrim(字符串)的使用--去左空格、去右空格
• trim(列|字符串)的使用--去左右空格
• instr(列|字符串, 要查找的字符串[,开始位置])的使用--查找出现位置*
• 数值函数
• 日期函数 默认:DD-MON-RR
• 转换函数
• 其他函数
• 分组函数
• count()函数--
• 查询公司里有多少员工是由经理管理的、多少员工是有奖金的--
• group by--分组
• 查询公司中按照职位分类后，每类员工的平均工资、最高工资、最低工资
• 执行逻辑
• having--在分组后对组进行筛选
• 查询平均工资高于2000的职位--
• 多表查询
• 等值连接--两张表之间的连接查询
• 查询每位员工所属部门的名称和地点
• 查询工资为2000元或以上的员工所属部门和所在地点--
• 自连接(同一张表之间的连接查询，主要用于显示上下级或层次关系)
• 查询每一位销售人员的直属领导信息--
• 查询职位为文员和分析员的员工的姓名、职位、直属经理姓名和部门名称、显示结果按员工编号升序排序。
• 内连接 (表)inner join(表)on(条件)
• 查询部门号为20的部门的部门名称及员工姓名。
• 使用内连接查询职位为文员和分析员的员工的姓名、职位、直属经理姓名和部门名称、显示结果按员工编号升序排序。
• 外连接 --没有“+”那边全返
• 左外连接(左边全返)
• 查询所有部门信息及其对应的员工信息(保括没有员工的部门)--
• 右外连接(右边全返)
• 查询所有部门信息及其对应的员工信息(保括没有员工的部门)--
• 全外连接(左右全返)
• 依据employee表和dept表查询员工及其所有部门的信息，无对应关系的员工及其部门用空值填充
• 不等连接 ">" "<" "between...and"
• 查询员工信息及其工资所对应的级别。其中工资级别保存在Scott的模式对象salgrade(工资等级表)中
• 子查询
• 查询工资高于平均工资的员工信息--
• 查询每个部门最高工资的员工信息--
• 查询与ward职位和工资等级都相同的员工--
• 查询哪些员工的工资为所任职位最高的-- in 与子查询返回结果中任何一个值相等 (not in 不相等)
• 查询哪些员工的工资高于最低的职位平均工资-- > any 比子查询的返回结果中某一个值大 all(比所有值都大)
• 查询有所属员工的部门信息-- exists比较运算符 子查询若满足条件返回true (not exists 不返回任何一行true)
• 查询各部门信息
• with起名引用
• 集合查询
• 并集操作:union(不包括重复的)、union all(包括重复出现的内容)
• 查询20号部门和30号部门所拥有的职位
• 交集操作:intersect
• 补集操作:minus (第一个有第二个没有)
• cross join 产生笛卡尔积现象
• TopN查询
• 查询员工表的第1行员工信息--
• 查询员工表的前2行员工信息--
• 先rownum(优先级高)再order by
• 查询工资最高的前3名员工的信息(先排序再伪列限定)--temp为临时表
• 查询员工表的第2行员工信息--
• 通过补集minus--
• 查询第6行到第10行的记录--
• fetch子句--
• 层次化查询
• 查询员工之间的领导关系，职位由高到低排列
• 常用模式对象
• 模式对象
• 视图
• create view权限(自己模式中创建视图)
• create any view权限(其他模式中创建视图)
• 创建一个包含员工基本信息，并按员工升序排序的员工基本信息视图--
• 对v_emp_base视图的查询--
• 查看v_emp_base视图--
• 创建一个简单只读视图--
• 创建一个包含多表连接，以及分组查询的视图--
• -with check option选项--使修改视图要满足where条件
• 创建一个工资大于2000的员工年薪信息的视图--
• 修改视图v_emp_salsry，增减对部门的限制条件
• 删除视图v_emp_salsry--需要有drop view权限
• 创建一个雇员表employee,创建一个对此表进行查询操作的视图v_test,演示对此视图数据的修改操作(对视图的更新，实际上对基表的更新)
• 无法对一个包含表达式列的视图进行跟新和插入操作(with check option选项)--
• 对包含多表连接查询的视图也无法进行数据更新操作
• emp是键值保存表(外键所在的表叫做从表,从表会作为键值保存表)，dept是非键值保存表
• 序列
• 创建一个初始值为1、最大值为1000、步长为1的序列--
• 创建一个初始值为10、步长为10、最大值为50、最小值为10、达到最大值时继续循环产生值、服务器预先缓存3个值的序列
• 利用序列seq_dept向表department中添加、查询、修改数据
• 利用序列seq_dept向表department中添加、查询、修改数据
• 查看当前用户的序列信息-- user_sequences
• 查看所有用户的所有序列信息-- dba_sequencrs
• 删除seq_dept序列--
• 同义词
• 用户在自己模式下创建私有同义词：create synonym 权限
• 在其它用户模式下创建私有同义词：create any synonym 权限
• 创建公有同义词权限：create public synonym 权限
• 为scott模式下的emp表创建同义词scottemp
• 创建或替换现有同义词：create or replace 语句(替换此同义词下的表)--
• 替换公有同义词scottdept所对应的表，将对应的表由dept改为department
• 查看当前用户所创建的同义词--user_synonyms
• 查看所有用户所创建的同义词--all_synonyms
• 删除私有同义词scottemp--drop synonym语句(删除公有同义词drop public synonym语句)
• 索引
• emp表中所有记录的rowid、empno值
• 创建唯一性B树索引
• 对储存空间的显示表示--
• 创建new_employee表的主键约束时，为产生的索引指定储存空间分配
• 为employee表的deptno创建一个位图索引
• 使用位图索引
• 为employee表中的hiredate列创建一个基于函数to_char()的函数索引
• 使用index_employee_hiredates索引
• 为employee表的empno列和ename列创建复合索引
• 删除索引：drop index
• PL/SQL基础
• PL/SQL
• 根据输入的员工编号进行员工姓名查询--
• 程序结构
• 开启输出
• 包含声明、执行部分的PL/SQL块
• 包含子块的PLSQL块
• 根据用户输入的员工编号，计算该员工所属部门的平均工资
• 定义常量--constant
• 数字类型：number、binary_integer、binary_float、binary_double--
• 字符类型：char、varchar2、nchar、nvarchar2、rowid
• 日期类型：date、timestamp
• 布尔类型：
• %type、%rowtype类型：表示表中某一列的类型、表示一行记录的类型
• 使用%rowtype装载一行记录
• 记录类型：type
• 控制结构
• if语句：if、if...else、if...elsif...else
• case语句：
• loop循环：loop、while...loop
• while...loop
• for循环--reverse递减
• 实现一个倒三角的九九乘法表
• exit：结束整个循环
• continue：结束当次循环
• goto语句：跳转语句
• 异常处理
• 用户自定义异常
• 游标
• 隐式游标：系统自动操作。通过隐式游标属性获得SQL语句
• 数据更新影响行数判断--
• 根据员工编号查询员工信息--
• 显示游标：用户创建。用于处理select语句返回多行数据
• 定义游标
• 打开游标
• 检索游标
• 关闭游标
• 查询emp表中的员工编号和姓名
• 使用loop循环检索emp表
• 使用while循环检索游标
• 使用for循环检索游标
• 参数化显示游标的使用--
• 需要修改游标数据的游标定义--for update 子句 行级锁定
• 需要修改多表查询游标数据的游标定义
• 避免死锁的游标定义
• 修改游标数据--where currend of 子句
• 游标变量
• 定义游标引用类型语法
• 声明游标变量语法
• 打开游标变量语法
• 检索游标变量语法
• 通过游标变量动态绑定
• PL/SQL高级应用
• 存储过程的创建与管理procedure
• 各种权限
• 调用存储过程--
• 通过存储名称对存储过程的调用
• 参数模式
• in参数模式
• out参数模式
• in out参数模式
• 查看存储过程及其源码信息
• 查看当前用户的子程序信息--user_procedures：查看当前用户所有的储存过程、函数信息
• 查看储存过程定义内容--user_source：查看当前用户所有对象的源代码
• 查询储存过程与数据库对象的依赖关系--user_dependencies
• 重新编译存储过程--alter procedure...compile语句
• 删除存储过程--drop procedure
• 函数的创建与管理function
• 创建无参函数--
• 创建有参函数--
• 调用函数
• SQL语句中调用
• 在PL/SQL块中调用
• with函数创建简单函数
• 查看当前用户的所有函数和源码--user_source
• 函数重编译
• 删除函数
• 触发器
• dml触发器
• 创建一个针对emp表的语句级dml触发器，要求在非工作时间禁止对emp表进行dml操作
• 通过scott用户创建一个语句级dml触发器，实现只有scott用户才可以对emp表进行dml操作的权限验证功能
• 行级触发器
• 创建一个行级触发器，对emp表工资的更新进行限制，要求加薪比例最高不能超过10%
• 触发器谓词inserting、updating、deleting
• 对dept表执行一个操作日志功能，当用户对dept表操作时，自动在dept的日志记录表dept_log中保存
• 执行顺序：
• follows子句指定触发器的执行顺序
• 复合触发器：
• 复合触发器的使用
• 替代触发器instead of ：建立在视图上的触发器
• 创建替代触发器来实现试图数据的删除操作
• 系统触发器
• 实现对数据库所有模式对象的ddl操作的日志记录
• 创建一个监控用户登录及注销的系统触发器
• 监控用户注销的系统触发器
• 触发器的查询
• 禁用触发器
• 重新编译触发器
• 删除触发器
• 重点：
• 输出所有比本部门平均工资高的员工信息

Oracle知识点

表空间、用户、角色

表空间

获取表空间与数据文件的关系

select t.name, d.name from v$tablespace t, v$datafile d;

查看数据块大小

show parameter db_block_size;

查询当前用户所拥有的表的信息、可以访问的表的信息、当前数据库所有表的信息

select * from user_tables;
select * from all_tables;
select * from dba_table;

查询系统中所有表空间的信息

select tablespace_name, status, alloction_type from dba_tablespaces;

创建表空间

create tablespace demospace
logging
datafile 'D:\Oracle\orsx\oradata\demospace\demospace.dbf' size 20M
autoextend on;

改变表空间的大小,添加新的数据文件

alter tablespace newsmgm_space
add datafile 'D:\Oracle\orsx\oradata\orcl\pdborcl\newsmgm_space_1.dbf' size 10M；

修改表空间的已有的数据文件大小

alter database datafile 'D:\Oracle\orsx\oradata\orcl\pdborcl\newsmgm_space_1.dbf' resize 20M；

设置默认表空间

select property_name, property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

查看当前数据库所有用户信息

select username, account_status, default_tablespace from dba_users;

查询数据库中有哪些容器

select con_id,dbid,name,open_mode from v$pdbs;

打开pdb–

alter pluggable database pdborcl open;

将当前公共区的会话转到容器–

alter session set container = pdborcl;

创建新用户test01并绑定到demospace–

create user c##test01 identified by 123456 default tablespace demospace;

赋予text01连接数据库的权限–

grant create session to c##test01;

赋予test01查询scott.emp表的权限(并获得权限的传递性)–

grant select on scott.emp to test01 with grant option;

再创建新用户test02–

create user c##test02 identified by 123456;

查看所有对象–

select username from dba_users;

为用户授予登陆权限–

grant create session to marc;

为用户授予创建表、创建序列的系统权限–

grant create table, create sequence to marc;

为用户授予表空间’USERS’权限–

alter user c##marcs quota unlimited on USERS;

将marcs模式的id表的部分对象权限授予用户test–

grant select, insert, update on marcs.identified to test;

角色

创建一个角色 role_emp–

create role role_emp;

将对emp表的查询权限赋予角色role_mep–

grant select on scott.emp to role_emp;

将对emp表的所有权限赋予角色role_mep–

grant all on scott.emp to role_emp;

把role_emp角色赋予给test01–

grant role_emp to test01;

把role_emp角色从test01回收–

revoke role_emp from test01;

查询test01所具有的所有角色–

select grantee, granted_role, admin_option, default_role
from dba_role_privs where grantee = 'C##TEST01';

表管理

表的创建

grant create table, create sequence to c##test01;
alter user c##test01 quota unlimited on DEMOSPACE;

在当前用户默认表空间创建department表–

create table department(
  deptno number(2)primary key,
  dname varchar2(14),
  loc varchar2(13)
);

查看表结构–

desc department;

在users表空间创建employee表–

create table employee(
  empno number(4)primary key,
  ename varchar2(10),
  job varchar2(20),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2)not null
         constraint fk_emp_dept references department(deptno)
)
  tablespace users;

表的维护

添加列–

alter table employee add(
  sex char(3),
  registdate date default sysdate not null
);

修改列定义–

alter table employee modify job varchar2(10);
alter table employee modify sex default'男';

修改列

alter table employee rename column registdate to regist_date;

删除列–

alter table employee drop column regist_date; alter table employee add registdate date;
alter table employee drop (sex, registdate);

将列标记为unused状态(禁用某一列)，然后删除unused状态列–

alter table employee add registdate date;
alter table employee set unused column registdate;

为表添加注释–

comment on table employee is '员工表';

为列添加注释–

comment on column employee.empno is '工号';

修改表名–

comment on table employee rename to tb_employee;

删除department表及对其的外键引用–

drop table department cascade constraint;

查询当前用户的所有表的名称、表空间及状态

select table_name, tablespace_name, status from user_tables;

查询当前用户所有表的注释信息–

select table_name, comments from user_tab_comments;

查询某个表中某列的描述信息–

select column_name, data_type, data_length, nullable
from user_tab_columns
where table_name = 'EMPLOYEE';

完整性约束

• 主键约束 primary key
• 外键约束 foreign key
• 唯一性约束 unique 可以定义在一列或多列上
• 检查约束 check
• 非空约束 not null

创建表时为各表添加列级约束–

create table board(
  bid number(2) primary key,  
  bname varchar2(20) not null,  
  status number check(status in(0,1))  
);

create table register(
  rid number(10) primary key,
  logname varchar2(20) unique,
  password varchar2(10) not null,
  age number(3) check (age>=13 and age<=80),
  registboard number(2) not null references board(bid)
);

创建工资发放记录表时添加的表级约束–

create table payroll(
  empno number(4) references employee(empno),
  payrolldate date not null,
  deptno number(2),
  sal number(7,2),
  comm number(7,2),
  constraint pk_payroll primary key(empno, payrolldate),
  constraint fk_dept foreign key(deptno) references department(deptno)
);

查询payroll表的所有约束信息–

select constraint_name, constraint_type, status
from user_constraints
where table_name='PAYROLL';

查询payroll表中的各个约束所作用的列–

select constraint_name, table_name, column_name
from user_cons_columns
where table_name='PAYROLL';

创建职位表job和部门表deptment–

create table job(
  jobid number,
  jobname varchar2(20),
  jobdesc clob,
  workplace varchar2(20),
  minsalary number(6),
  maxsalary number(6),
  department number(2)
)tablespace users;

create table department(
  deptno number(2) primary key,
  dname varchar2(14),
  loc varchar2(13)
);

添加主键约束–

alter table job add primary key(jobid);

添加唯一性约束–

alter table job add unique(jobname);

添加外键约束–

alter table job add constraint fk_job_dept foreign key(department)
references department(deptno);

添加检查约束 需要建立表级约束(起个名字)–

alter table job add constraint ck_job_salary check( maxsalary > minsalary);

添加非空约束–

alter table job modify jobname not null;

对外键约束fk_job_dept重命名–

alter table job rename constraint fk_job_dept to fk_jobdept;

通过名称删除外键约束–

alter table job drop constraint fk_jobdept;

删除主键约束–

alter table job drop primary key;

删除主键约束时保留唯一索引–

alter table job drop primary key keep index;

删除唯一性约束–

alter table job drop unique(jobname);

SQL基础

• 数据定义语言：DDL
• 数据查询语言：DQL
• 数据操纵语言：DML
• 事务控制语言：TCL

数据管理

向部门表插入一行记录–

insert into department(deptno, dname, loc) values(50,'研发部'，'青岛');

向员工表插入一条记录–

insert into employee values(
7210,'jenny','programmer',null,to_date('20150302','yyyy-MM-dd'),
3000,null,50);

将scott模式的dept表的记录插入deparment表中–

insert into department select * from scott.dept;

将scott模式的emp表的记录插入employee表中–

insert into employee select * from scott.emp;

查询部门表中所有列的记录–

select * from department;

查询符合条件的员工的部分列的信息表

select empno, ename, job, sal, comm
from employee
where comm is null;

将员工表中所有员工的奖金更新为200元–

update employee set comm = 200;

将工号为7210的员工的工资更新为原有工资与奖金的和–

update employee set sal = sal + comm where empno = 7210;
select empno,ename,job,sal,comm from employee;

依据条件字句删除记录–

delete from employee where empno = 7210;

删除有外键约束引用的表中的所有记录(表中内容)–

delete from employee;
delete from department;

使用truncate语句删除有外键约束引用的表中的所有记录(无法用rollba恢复)–

alter table employee drop constraint fk_emp_dept;
truncate table department;

事务控制

• 事务四大特性：原子性、一致性、隔离性、持久性

事务的开始与结束

• 开始：dml: insert；insert； update； delete；
• 结束：commit； dll:create table；

create table account(
  account_id varchar2(16),
  account_name varchar2(10),
  account_balance number(16,3),
  constraint pk_accountid primary key(account_id)
);

insert into account values('1001', '张三', 1000);

第一个dml语句执行，事务A开始

insert into account values('1002', '李四', 1);
select * from account;
commit;

事务提交，事务A结束

update account set account_balance = account_balance-1000
where account_id='1001';

执行dml语句，新的事务B开始

update account set account_balance = account_balance+1000
where account_id='1002';
alter table account add constraint ck_accountbalance check(account_balance>=0);

执行ddl语句，事务B自动提交，事务B结束

select * from account;
delete from account where account_id='1001';

新的事务C开始

exit;

正常退出，事务C被自动提交，事务C结束

commit命令显示提交事务–

create table department(
  deptno number(2) primary key,
  dname varchar2(14),
  loc varchar2(13)
)tablespace users;

insert into department values(50, '研发部', '青岛');
commit;
select * from department;

insert into department values(60, '产品部', '青岛');
update department set loc = '高新区'where deptno = 60;
commit;
select * from department;

rollback命令回滚单条dml语句事务–

select * from department;
delete from department where deptno = 60;
rollback;
select * from department;

rollback命令回滚多条dml语句事务–

insert into department values(70, '市场部', '青岛');
update department set loc = '高新区'where deptno = 70;
rollback;
select * from department;

设置保存点–

select * from department;
update department set loc = 'QING DAO'where deptno = 50;
savepoinit sp1;

delete department where deptno = 50;
savepoinit sp2;

rollback to sp1;
select * from department;

rollback;
select * from department;

并发事务

并发事务可能导致的3类问题

• 脏读：一个事务对数据的修改在提交之前被其他事务读取
• 不可重复读：在某个事务读取一次数据之后，其他事务修改了这些数据并进行了
  提交，当该事务重新读取了这些数据时就会得到与之前一次不一样的结果。
• 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入或删除操作，
  每次返回不同的结果集。

事务的隔离级别

• read uncommitted:称为未授权读取或读取未提交。 (脏、不、幻)
• read committed：称为授权读取或读提交。 (不、幻)
• repeatable read：称为可重复读取。 (幻)
• serializable：称为序列化。

设置数据库事务为serializable隔离级别–

set transaction isolation level serializable;

设置数据库事务为read committed隔离级别–

set transaction isolation level read committed;

设置数据库事务为read only隔离级别–

set transaction read only;

修改数据库事务为serializable隔离级别–

alter session set isolation_level = serializable;

修改数据库事务为read committede隔离级别–

alter session set isolation_level = read committed;

幻读实例–

会话1

create table tran_test(num number);
set transaction isolation level read committed;
insert into tran_test values(10);
select * from tran_test;

会话2

insert into tran_test values(20);
commit;

会话1

select * from tran_test;

修改事务的隔离级别为序列化实例–

会话1

delete from tran_test;
exit;
alter session set isolation_level = serializable;
insert into tran_test values(10);
select * from tran_test;

会话2

insert into tran_test values(20);
commit;

会话1

select * from tran_test;

修改事务的隔离级别为read only实例–

会话1

delete from tran_test;
exit;
set transaction read only;
insert into tran_test values(10); ×
select * from tran_test; ×

会话2

insert into tran_test values(20); 
commit;

会话1

select * from tran_test; ×

数据查询

基本查询

select * from emp;

distinct运算符（去除重复数据）–

select distinct * from emp;

连接运算符||

限定查询与排序(where)

查询工资在1500到2900之间的员工信息

select empno, ename, sal from emp where sal>=1500 and sal<=2900;

查询职位为salesman的人员名单

select empno, ename, job from emp where job='SALESMAN'

between and 操作符(闭区间)|not between and

查询在1981年1月1日和1981年12月31日之间加入公司的员工–

select empno, ename, hiredate
from emp where hiredate between '01-1月-81' and '31-12月-81';

in：用来测试某些值是否在列表中|not in

查询职位为销售、文员或经理的人员

select empno, ename, job from emp where job in('SALESMAN','CLEAR','MANAGER');

null(表示为空)–

• 和任何值进行算术运算，结果为null
• 和任何值进行比较运算，结果为unknown

like模糊查询–

查询员工姓名是以M开头的所有员工的信息–

select empno, ename, job from emp where like 'M%';

查询姓名以A开头、姓名最后连个字符为E和一个任意字符的员工信息–

select empno, ename, job from emp where like 'A%E_';

使用转义操作符escape,使字符表示本意–

select * from department where dname like'IT\_%' escape' \ ';

order by 排序-- asc升序(默认)–desc(降序)–

select ename, job, sal from emp where deptno = 30 order by sal desc;

单行函数

字符函数

lower(列|字符串)的使用–转换为小写

select lower('Structural|Query language')from dual;

upper(列|字符串)的使用–转换为大写

select empno, ename, job from emp where job = upper('clerk');

length(列|字符串)的使用–返回长度*

select length('SQL is an english like language')from dual;

initcap(列|字符串)的使用–开头首字母大写

select empno, ename, initcap(ename) from emp;

substr(列|字符串,开始点[,长度])的使用–截取*

select substr('SQL is an english like language', 3)from dual;
select substr('SQL is an english like language', 1, 3)from dual;

replace(列|字符串,要搜索的字符串，替换的字符串)的使用–替换*

select replace('SQL Plus supports loops or if statements','supports','not supports')from dual;

concat(列|字符串,列|字符串)的使用–拼接*

select concat('hello,','world!')from dual;

rpad(列|字符串,长度，填充字符)的使用–填充

select rpad(ename, 10, '*'), lpad(ename, 10, '*')from emp;

ltrim(字符串)、rtrim(字符串)的使用–去左空格、去右空格

select ' QST ', ltrim(' QST '), rtrim(' QST ')from dual;

trim(列|字符串)的使用–去左右空格

select’ QST ‘, trim(’ QST ')from dual;

instr(列|字符串, 要查找的字符串[,开始位置])的使用–查找出现位置*

select distinct job, instr(job, upper('man'))from emp;

select distinct job, instr(job, upper('man'), 2)from emp;

数值函数

• round(列|数字[，保留位数])–四舍五入*
• trunc(列|数字[，截取位数])–截断*
• mod(列|数字,数字)–取余*

日期函数 默认:DD-MON-RR

• 获得系统当前时间

select sysdate from dual;

• 修改日期显示格式

alter session set nls_format='yyyy-mm-dd hh24:mi:ss';

• add_months(日期, 数字)–计算增加月数日期

• last_day(日期)–计算此月最后一天

• next_day(日期, 星期)–计算下一个星期几的日期

• months_between(日期1, 日期2)–计算两个日期相隔月数

转换函数

• to_char(列|日期|数字，转换格式)–转换为字符串

• to_date(字符串，转换格式)–将字符串转换为日期型(有分秒：to_timestamp(字符串，转换格式))

• to_number(字符串)–将数字内容的字符串转变为字符型

其他函数

• nvl(列,替换值)–
• nvl2(列, 替换值1, 替换值2)-
• nullif(表达式1, 表达式2)–
• decode(列值, 判断值1, 显示结果1, ···)–

分组函数

count()函数–

查询公司里有多少员工是由经理管理的、多少员工是有奖金的–

select count(mgr), count(comm)from emp;

• avg(列)函数：求平均值–

• sum(列)函数：求和–

• max(列)函数：求最大值–

• min(列)函数：求最小值–

group by–分组

查询公司中按照职位分类后，每类员工的平均工资、最高工资、最低工资

select job, avg(sal), max(sal), min(sal) from emp group by job;

执行逻辑

• select…from…where…group by…order by…
• from->where->group by->select->order by

having–在分组后对组进行筛选

查询平均工资高于2000的职位–

select job, avg(sal)from emp group by job having avg(sal)>2000;

多表查询

等值连接–两张表之间的连接查询

查询每位员工所属部门的名称和地点

select empno, ename, emp.deptno, dname, loc
from emp, dept
where emp.deptno = dept.deptno;

查询工资为2000元或以上的员工所属部门和所在地点–

select e.empno, e.ename, e.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.sal >= 2000
order by d.loc;

自连接(同一张表之间的连接查询，主要用于显示上下级或层次关系)

查询每一位销售人员的直属领导信息–

select e.empno, e.ename, e.job, e.mgr, e.ename 经理, m.job 职位
from emp e, emp m
where e.mgr = m.empno and e.job like 'sal%';

查询职位为文员和分析员的员工的姓名、职位、直属经理姓名和部门名称、显示结果按员工编号升序排序。

select e.ename 员工名称, e.job 职位, m.ename 经理名称, d.dname 部门名称
from emp e, emp m, dept d 
where e.mgr = m.empno
and e.deptno = d.deptno
and e.job in('CLERK', 'ANALYS')
order by e.empno;

内连接 (表)inner join(表)on(条件)

查询部门号为20的部门的部门名称及员工姓名。

select e.ename, d.dname
from emp e inner join dept d 
on e.deptno = d.deptno and d.deptno = 20;

使用内连接查询职位为文员和分析员的员工的姓名、职位、直属经理姓名和部门名称、显示结果按员工编号升序排序。

select e.ename 员工名称, e.job 职位, m.ename 经理名称, d.dname 部门名称
from emp e
join emp m on e.mgr = e.empno
join dept d on e.deptno = d.deptno
where e.job in('CLERK', 'ANALYS')
order by e.empno;

外连接 --没有“+”那边全返

左外连接(左边全返)

查询所有部门信息及其对应的员工信息(保括没有员工的部门)–

select e.empno, e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
on d.deptno = e.deptno
order dy d.deptno desc;

select e.empno, e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
where d.deptno = e.deptno(+)
order by dy d.deptno desc;

右外连接(右边全返)

查询所有部门信息及其对应的员工信息(保括没有员工的部门)–

select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e left join dept d 
on e.deptno = d.deptno
order dy d.deptno desc;

select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e left join dept d
where d.deptno(+) = e.deptno
order by dy d.deptno desc;

全外连接(左右全返)

依据employee表和dept表查询员工及其所有部门的信息，无对应关系的员工及其部门用空值填充

select e.ename, e.deptno, d.deptno, d.dname, d.loc
from employee e full join dept d
on e.deptno = d.deptno
order by d.deptno;

不等连接 “>” “<” “between…and”

查询员工信息及其工资所对应的级别。其中工资级别保存在Scott的模式对象salgrade(工资等级表)中

select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

子查询

查询工资高于平均工资的员工信息–

select empno, ename, sal 
from emp 
where sal >(select avg(sal)from emp);

查询每个部门最高工资的员工信息–

select a.empno, a.ename, a.sal, a.deptno
from emp a
where (select count(*)from emp where deptno = a.deptno and sal>a.sal)=0;

查询与ward职位和工资等级都相同的员工–

select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.ename != 'WARD' and (e.sal, s.grade)=(
select sal, grade from emp, salgrade
where ename = 'WARD' and sal between losal and hisal);

查询哪些员工的工资为所任职位最高的-- in 与子查询返回结果中任何一个值相等 (not in 不相等)

select empno, ename, job, sal 
from emp 
where sal in(select max(sal)from emp group by job);

查询哪些员工的工资高于最低的职位平均工资-- > any 比子查询的返回结果中某一个值大 all(比所有值都大)

select empno, ename, job, sal 
from emp 
where sal > any(select avg(sal)from emp group by job);

查询有所属员工的部门信息-- exists比较运算符 子查询若满足条件返回true (not exists 不返回任何一行true)

select deptno, dname, loc
from dept d
where exists(select * from emp e where d.deptno = e.deptno);

查询各部门信息

select d.deptno, d.dname, e.amount, a.avgsal
from dept d, 
(select deptno, count(*) amount, avg(sal) avgsal from emp group by deptno) e,
where e.deptno = d.deptno;

with起名引用

with subdept as(
  select deptno, count(*)count
  from emp 
  group by deptno)
select deptno, dname, loc
from dept
where deptno in(
select deptno from subdept
where count = (select max(count)from subdept));

集合查询

并集操作:union(不包括重复的)、union all(包括重复出现的内容)

查询20号部门和30号部门所拥有的职位

select job from emp where deptno = 30
union 
select job from emp where deptno = 20;

交集操作:intersect

补集操作:minus (第一个有第二个没有)

cross join 产生笛卡尔积现象

select * from emp, dept;

select empno, ename, emp.deptno, dept.deptno, dname
from emp cross join dept;

TopN查询

查询员工表的第1行员工信息–

select * from emp where rownum = 1;

查询员工表的前2行员工信息–

select * from emp where rownum < 3;

先rownum(优先级高)再order by

查询工资最高的前3名员工的信息(先排序再伪列限定)–temp为临时表

select temp.*from(
 select empno, ename, job, sal
 from emp
 order by sal desc)temp
where rownum <= 3;

查询员工表的第2行员工信息–

select temp.*from(
 select rownum rn, empno, ename, job, sal 
 from emp 
 where rownum < 3)temp
where temp.rn > 1;

通过补集minus–

select * from emp where rownum < 3;
minus
select * from emp where rownum < 2;

查询第6行到第10行的记录–

select temp.*from(
 select rownum rn, empno, ename, job, sal
 from emp 
 where rownum <= 10)temp
where temp.rn >= 6;

fetch子句–

fetch first 3 row only :表示取得前3行记录(放在排序后)

offset 2 rows fetch next 6 row only :表示从第2行开始取得6行记录，即第3行到第8行记录(不包括边界值)

fetch next per percent row only :表示按照指定的百分比per%取得相关行数的记录

层次化查询

查询员工之间的领导关系，职位由高到低排列

select empno, ename, mgr
from emp 
start with job = 'PRESIDENT'
connect by prior empno = mgr;

常用模式对象

模式对象

视图

create view权限(自己模式中创建视图)

grant create view to scott;

create any view权限(其他模式中创建视图)

创建一个包含员工基本信息，并按员工升序排序的员工基本信息视图–

create view scott.v_emp_base
as 
select empno, ename, sal, deptno from emp order by empno;

对v_emp_base视图的查询–

select * from scott.v_emp_base;

查看v_emp_base视图–

select ename, deptno from v_emp_base;

创建一个简单只读视图–

create view scott.v_emp_base_read
as
select empno, ename, sal, deptno from emp order by empno
with read only;

• or replace 表示若所创视图存在，自动重建该视图
• force 不管基表是否存在都创建视图

创建一个包含多表连接，以及分组查询的视图–

grant create view to scott;
create or replace view v_dept_sal(name, minsal, maxsal, avgsal)
as
select d.dname, min(e.sal), max(e.sal), avg(e.sal)
from dept d, emp e
where d.deptno = e.deptno
group by d.dname;

-with check option选项–使修改视图要满足where条件

创建一个工资大于2000的员工年薪信息的视图–

create view v_emp_salsry
as
select empno, ename, sal*12 salsay
from emp
where sal > 2000
with check option;

修改视图v_emp_salsry，增减对部门的限制条件

create or replace view v_emp_salsry
as
select empno, ename, sal*12 salsay
from emp
where sal > 2000 and deptno = 10
with check option;

• dba_views：包含数据库中所有视图的信息
• all_views：包含当前用户可以访问的所有视图的信息
• user_views：包含当前用户拥有的所有视图的信息

select view_name, text from user_views;

删除视图v_emp_salsry–需要有drop view权限

drop view v_emp_salsry;

创建一个雇员表employee,创建一个对此表进行查询操作的视图v_test,演示对此视图数据的修改操作(对视图的更新，实际上对基表的更新)

create table emplyee as select * from emp;
create or replace view v_test
as
select empno, ename, sal, deptno
from employee 
where deptno = 20
with check option;

update v_test set sal = 1000 where ename = 'SMITH';
select empno, ename, sal, deptno from employee where ename = 'SMITH'

无法对一个包含表达式列的视图进行跟新和插入操作(with check option选项)–

create or replace view v_test
(empno, ename, salsry, deptno)
as
select empno, enamen, sal * 12, deptno
from employee
where deptno = 20
with check option;

对包含多表连接查询的视图也无法进行数据更新操作

create or replace view v_emp_dept
as
select empno, ename, dname
from enp e, dept d
where e.deptno = d.deptno;

update v_emp_dept set sal = 800 where ename = ‘SMITH’ √
update v_emp_dept set dname = ‘XXX’ where ename = ‘SMITH’ ×

emp是键值保存表(外键所在的表叫做从表,从表会作为键值保存表)，dept是非键值保存表

• 对视图的DML操作，只能操作属于键值保存表的列

序列

创建一个初始值为1、最大值为1000、步长为1的序列–

create sequence seq_emp
start with 1
increment by 1
maxvalue 1000;

创建一个初始值为10、步长为10、最大值为50、最小值为10、达到最大值时继续循环产生值、服务器预先缓存3个值的序列

create sequence seq_dept
start with 10
increment by 10
maxvalue 50
minvalue 10
cycle
cache 3;

• nextval:返回序列的下一个值
• currval:返回序列当前值，并且只有在发出至少一个nextval之后才能使用

利用序列seq_dept向表department中添加、查询、修改数据

create table department as select * from scott.dept where 1 = 2;

• 只复制表结构，不复制表内容

insert into department(deptno, dname, loc)
values(seq_dept.nextval,'RESEARCH', 'QINGDAO');

select seq_dept.currval from department;

利用序列seq_dept向表department中添加、查询、修改数据

update department set deptno = seq_dept.nextval where deptno = 10;

查看当前用户的序列信息-- user_sequences

select sequence_name, min_value, max_value,
increment_by, cycle_flag, cache_size
from user_sequences;

查看所有用户的所有序列信息-- dba_sequencrs

删除seq_dept序列–

drop sequence seq_dept;

同义词

用户在自己模式下创建私有同义词：create synonym 权限

grant create synonym to scott;

在其它用户模式下创建私有同义词：create any synonym 权限

创建公有同义词权限：create public synonym 权限

为scott模式下的emp表创建同义词scottemp

grant create synonym to scott;
create synonym scottemp for emp;

创建或替换现有同义词：create or replace 语句(替换此同义词下的表)–

替换公有同义词scottdept所对应的表，将对应的表由dept改为department

create or replace public synonym scottemp for scott.department;

查看当前用户所创建的同义词–user_synonyms

select synonym_name, table_owner, table_name from user_synonyms

查看所有用户所创建的同义词–all_synonyms

删除私有同义词scottemp–drop synonym语句(删除公有同义词drop public synonym语句)

drop synonym scottemp;

索引

emp表中所有记录的rowid、empno值

select rowid, empno, ename, from emp order by empno;

• unique：建立唯一性索引
• bitmap：建立位图索引

创建唯一性B树索引

create index index_employee_ename on employee(ename);

对储存空间的显示表示–

create index index_employee_ename
on pmployee(ename)
tablespce users storage(initial 20k next 10k pctincrease 65);

创建new_employee表的主键约束时，为产生的索引指定储存空间分配

create table new_employee(
  empno number(4) primary key using index tablespace users pactfree 0,
  ename varchar2(10));

为employee表的deptno创建一个位图索引

create bitmap index index_employee_deptno on employee(deptno);

使用位图索引

select empno, ename, deptno from employee where deptno = 10 or deptno;

为employee表中的hiredate列创建一个基于函数to_char()的函数索引

create index index_employee_hiredate
on employee(to_char(hiredate,'YYYY-MM-DD'));

使用index_employee_hiredates索引

select empno, ename, hiredate
from employee
where ton_char(hiredate,'YYYY-MM-DD')=='1981-11-17';

为employee表的empno列和ename列创建复合索引

create index index_empno_ename on employee(empno, ename);

• 修改索引：alter index (合并、重建、重命名)

• 合并索引：alter index…coalesce

• 重建索引：alter index…rebuild

• 索引重命名：alter index…rename to

• 打开索引监视状态：alter index…monitoring usage

• 关闭索引监视状态：alter index…nomonitoring usage

• 视图dba_indexes:描述数据库中的所有索引信息

• 视图user_indexes:描述数据库中的当前用户索引信息

删除索引：drop index

PL/SQL基础

PL/SQL

根据输入的员工编号进行员工姓名查询–

declare
  v_empno number;
  v_ename varchar2(10);
begin
  dbms_output.put_line('请输入员工编号:');
  v_empno:=&input_empno;
  select ename into v_ename from emp where empno=v_empno;
  dbms_output.put_line('编号为:'||v_empno||' 姓名为:'||v_ename);
exception
  when no_data_found then
  dbms_output.put_line('此编号员工不存在');	   
end;

程序结构

开启输出

set serveroutput on;

包含声明、执行部分的PL/SQL块

declare
  v_num number;
begin
  v_num:=100/0;
exception
  when zero_divide then
    dbms_output.put_line('除0异常');
end;

包含子块的PLSQL块

declare
  v_x number:=10;
begin
  declare
    v_x number:=20;
  begin    
    dbms_output.put_line('子块变量v_x='||v_x);
  end;
  dbms_output.put_line('外部语句块变量v_x='||v_x);
end;

根据用户输入的员工编号，计算该员工所属部门的平均工资

declare
  v_empno number;
  v_deptno number;
  v_sal number;
begin
  begin
    v_empno:=&员工编号;
	  select deptno into v_deptno from emp where empno = v_empno;
  end;
  select round(avg(sal),2)into v_sal from emp where deptno = v_deptno;
  dbms_output.put_line(v_empno||'员工所在部门的平均工资为'||v_sal);
end;

定义常量–constant

declare
  v_cons constant varchar2(20):='Hello,world.';
begin
  dbms_output.put_line('v_cons常量值为:'||v_cons);
end;

数字类型：number、binary_integer、binary_float、binary_double–

declare
  v_num1 number(3):=100;
  v_num2 number(5,2):=100.99;
  v_binary binary_integer:=-100;
  v_float binary_float:=1000.99F;
  v_double binary_double:=10000.99D;
begin
  v_num1:=v_num1+v_num2;
  v_binary:=v_binary+100;
  v_float:=v_float+1000.99;
  v_double:=v_double+10000.99;
  dbms_output.put_line('number类型数据计算和:'||v_num1);
  dbms_output.put_line('binary_integer类型数据计算和:'||v_binary);
  dbms_output.put_line('binary_float类型数据计算和:'||v_float);
  dbms_output.put_line('binary_double类型数据计算和:'||v_double);
end;

字符类型：char、varchar2、nchar、nvarchar2、rowid

declare 
  v_char char(20);
  v_varchar2 varchar2(20);
  v_nchar nchar(20);
  v_nvarchar2 nvarchar2(20);
  v_dept_rowid rowid;
begin
  v_char:='静水流深';
  v_varchar2:='静水流深';
  v_nchar:='静水流深';
  v_nvarchar2:='静水流深';
  select rowid into v_dept_rowid from scott.dept where deptno=10;
  dbms_output.put_line('v_char内容长度:'||length(v_char));
  dbms_output.put_line('v_varchar2内容长度:'||length(v_varchar2));
  dbms_output.put_line('v_nchar内容长度:'||length(v_nchar));
  dbms_output.put_line('v_nvarchar2内容长度:'||length(v_nvarchar2));
  dbms_output.put_line('10号部门的rowid:'||v_dept_rowid);
end;

日期类型：date、timestamp

declare
  v_date1 date:=sysdate;
  v_date2 date:=systimestamp;
  v_date3 date:='04-03月-2020';
  v_timestamp1 timestamp:=systimestamp;
  v_timestamp2 timestamp:=sysdate;
  v_timestamp3 timestamp:='04-03月-2020 12.20.40 上午';
begin
  dbms_output.put_line('v_date1:'||to_char(v_date1,'yyyy-MM-dd hh24:mi:ss'));
  dbms_output.put_line('v_date2:'||v_date2);
  dbms_output.put_line('v_date3:'||v_date3);
  dbms_output.put_line('v_timestamp1:'||v_timestamp1);
  dbms_output.put_line('v_timestamp2:'||v_timestamp2);
  dbms_output.put_line('v_timestamp3:'||v_timestamp3);
end;

布尔类型：

declare
  v_flag boolean;
begin 
  v_flag:=true;
  if v_flag then
    dbms_output.put_line('条件为真');
  end if;
end;

%type、%rowtype类型：表示表中某一列的类型、表示一行记录的类型

declare
  v_name emp.ename%type;
  v_salary emp.sal%type;
  v_hiredate emp.hiredate%type;
begin
  select ename, sal, hiredate, into v_name, v_salary, v_hiredate
  from emp where empno=&empno;
  dbms_output.put_line('雇员号:'||v_name);
  dbms_output.put_line('工资:'||v_salary);
  dbms_output.put_line('入职日期:'v_hiredate);
exception
  when no_date_found then
    dbms_output.put_line('你输入的员工号不存在');
end;

使用%rowtype装载一行记录

declare
  emp_record emp%rowtype;
begin
  select * into emp_record from emp where empno=&empno;
  dbms_output.put_line('雇员号:'||emprecoord.ename);
  dbms_output.put_line('工资:'||emp_record.sal);
  dbms_output.put_line('入职日期:'||emp_record.hiredate);
exception
  when no_data_found then
    dbms_output.put_line('你输入的员工号不存在');
end;

记录类型：type

控制结构

if语句：if、if…else、if…elsif…else

declare
  v_sal number;
begin
  select sal into v_sal from emp where empno=7934;
  if v_sal<1000 then
    update emp set sal=sal+200 where empno=7934;
  elsif v_sal>=1000 and v_sal<2000 then
    update emp set sal=sal+150 where empno=7934;  
  else
    update emp set sal=sal+100 where empno=7934;
  end if;
end;

case语句：

declare
  v_sal number;
begin
  case 
    when XXX then
	  update...where...;
	when YYY then
	  update...where...;
	when MMM then
	  update...where...;
	when NNN then
      update...where...;
  end case;
end;

loop循环：loop、while…loop

declare
  v_i number:=1;
  v_sum number:=0;
begin   
  loop
    v_sum:=v_sum+v_i;
	v_i:=v_i;
    exit when v_i>100;
  end loop;
  dbms_output.put_line('1~100的和为:'||v_sum);
end;

while…loop

declare
  v_i number:=1;
  v_sum number:=0;
begin
  while v_i<=100 loop
    v_sum:=v_sum+v_i;
	v_i:=v_i+1;
  end loop;
  dbms_output.put_line('1~100的和为:'||v_sum);
end;

for循环–reverse递减

declare
  v_sum number:=0;
  for v_i in 1..100 loop
    v_sum:=v_sum+v_i;
  end loop;
  dbms_output.put_line('1~100的和为:'||v_sum);

实现一个倒三角的九九乘法表

begin
 for v_i reverse 1..9 loop
   for v_j in 1..v_i loop
     dbms_output.put_line(v_i||'*'||v_j||'='||v_i*v_j||'');
   end loop;
   dbms_output.put_line('');
   end loop;
end;

exit：结束整个循环

continue：结束当次循环

goto语句：跳转语句

declare
begin
  for v_i in 1..5 loop
    if
	  goto
	  
	end if;
  end loop;

end;

异常处理

exception 
  when...others then

end;

• others：捕获所有异常
• sqlerrm：输出异常信息
• sqlcode：输出异常代码

用户自定义异常

XXX exception;
pragma exception_init(XXX, -20000~-29999);

• raise：抛出异常

declare 
  v_myexp

游标

隐式游标：系统自动操作。通过隐式游标属性获得SQL语句

sql%isopen
sql%found
sql%notfound
sql%rowcount

数据更新影响行数判断–

begin
  update employee set deptno=20 where ename like'%s%';
  if sql%rowcount = 0 then
    dbms_output.put_line('数据更新失败');
  else
    dbms_output.put_line('数据已更新'||sql%rowcount||'行');
  end if;
end;

根据员工编号查询员工信息–

declare
  v_empno emp.empno%type;
  v_emp emp%rowtype;
begin
  v_empno:=&职员编号;
  select * into v_emp from emp where empno=v_empno;
  if sql%found then
    dbms_output.put_line('职员的姓名是'||v_emp.ename);
  end if;
exception
  when no_data_found then
    dbms_output.put_line('该编号的职员未找到');
end;

显示游标：用户创建。用于处理select语句返回多行数据

定义游标

• cursor 游标名称 is 查询语句;

打开游标

• open 游标名称;

检索游标

• fetch 游标名称 into 变量;

关闭游标

• close 游标名称；

查询emp表中的员工编号和姓名

declare
  cursor cursor_emp is select empno, ename from emp;  --声明游标
  v_empno emp.empno%type;
  v_ename emp.ename%type;
begin
  open cursor_emp;   --打开游标
  loop
    fetch cursor_emp into v_empno, v_ename;   --检索游标指向数据给变量
    exit when cursor_emp%notfound;
	dbms_output.put_line('员工号:'||v_empno||'姓名:'||v_ename);
  end loop;
  close cursor_emp;
end;

使用loop循环检索emp表

declare
  cursor cursor_emp is select empno, ename, sal from emp;
  v_emp cursor_emp%rowtype;
begin
  open cursor_emp;
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(cursor_emp%rowcount||''||v_emp.empno||''||v_emp.ename||''||v_emp.sal);
  end loop;
  close cursor_emp;
end;

使用while循环检索游标

declare
  cursor cursor_sal is
    select deptno, round(avg(sal),2)avgsal from emp group by deptno;
  v_sal sursor_sal%rowtype;
begin
  if cursor_sal%isopen then
    null;
  else
    open cursor_sal;
  end if;
  fetch cursor_sal into v_sal;   --游标指向第一行
  while cursor_sal%found loop
    dbms_output.put_line(v_sal.deptno||' '||v_sal.avgsal);
	fetch cursor_sal into v_sal;   --把游标指向下一行
  end loop;
  close cursor_sal;  
end;

使用for循环检索游标

declare
  cursor cursor_emp is select * from emp;
begin
  for v_emp in cursor_emp loop
    dbms_output.put_line(cursor_emp%rowcount||' '||v_emp.ename);
  end loop;
end;

参数化显示游标的使用–

declare
  cursor cursor_emp(p_empno emp.empno%type)is
    select * from emp where empno = p_empno;
  v_emp cursor_emp%rowtype;
begin
  open cursor_emp(7369);
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(v_emp.empno||''||v_emp.ename);
  end loop;
  close cursor_emp;
  open cursor_emp(7499);
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(v_emp.empno||''||v_emp.ename);
  end loop;
  close cursor_emp;
end;

需要修改游标数据的游标定义–for update 子句 行级锁定

cousor cursor_emp is;
select * from emp for update;

需要修改多表查询游标数据的游标定义

cursor cursor_emp is
select e.empno, e.ename, e.sal, d.dname from emp e, dept d
where e.deptno = d.deptno for update of e.sal;

避免死锁的游标定义

cursor cursor_emp is
select * from emp for update nowait;

修改游标数据–where currend of 子句

declare
  cursor cursor_emp is select * from employee where comm is null for update;
begin 
  for v_emp in cursor_emp loop
    update employee set comm = 500 where current of currsor_emp;
  end loop;
  commit;
end;

游标变量

定义游标引用类型语法

• type 游标引用类型名称 is ref cursor ;

声明游标变量语法

• 游标变量名称 游标引用类型；

打开游标变量语法

• open 游标变量 for 查询语句;

检索游标变量语法

loop
  fetch 游标变量 into 变量;
  exit when 游标变量%notfound;
  ...
end loop;

通过游标变量动态绑定

declare
  type ref_cursor_type is ref cursor;
  ref_cursor ref_cursor_type;
  v_emp emp%rowtype;
  v_dept dept%rowtype;
  v_choose varchar2(1):=upper(substr('&D或E',1,1));
begin
  if v_choose='E' then
    open ref_cursor for select * from emp;
    dbms_output.put_line('员工信息');
    loop
      fetch ref_cursor into v_emp;
      exit when ref_cursor%notfound;
      dbms_output.put_line(v_emp.empno||' '||v_emp.ename);
    end loop;
  close ref_cursor;
  elsif v_choose='D'then
    open ref_cursor for select * from dept;
	dbms_output.put_line('部门信息');
    loop
      fetch ref_cursor into v_dept;
      exit when ref_cursor%notfound;
      dbms_output.put_line(v_dept.deptno||' '||v_dept.dname);
    end loop;
    close ref_cursor;
  else 
    dbms_output.put_line('请输入E或D'); 
  end if;
end;

PL/SQL高级应用

存储过程的创建与管理procedure

create or replace procedure

各种权限

-create any procedure 任意用户创建储存过程

• create procedure
• alter procedure
• execute any procedure 执行任意储存过程
• execute procedure
• drop any procedure

调用存储过程–

• execute|exec|call

通过存储名称对存储过程的调用

create or replace procedure proc_test(p_str1 varchar2, p_str2 varchar2)
as
begin
  dbms_output.put_line(p_str1||p_str2);
end;

declare
  v_var1 varchar2(20):='Hello,';
  v_var2 varchar2(20):='Prodcedure!';
begin
  proc_test(v_var1, v_var2);
end;

参数模式

• in：数值传递，常量或表达式，子程序中修改不会影响原始数值
• out：初始值为null，只能是变量，子程序可以通过此变量将数值返回给调用处
• in out：只能是变量，将值传递到子程序，同时将子程序中对变量的修改返回到调用处

in参数模式

create or replace procedure proc_in(p_empno in number)
as
  v_ename scott.emp.ename%type;
  v_sal scott.emp.sal%type;
begin
  select ename, sal into v_ename, v_sal from scott.emp where empno=p_empno;
  dbms_output.put_line('雇员的姓名是：'||v_ename||'工资是：'||v_sal);
exception
  when no_data_found then
    dbms_output.put_line('雇员编号未找到');    
end;

begin
  proc_in(7369);
end;

out参数模式

create or replace procedure proc_out(
  p_deptno number,
  p_num out number,
  p_avgsal out number)
as
begin
  select count(*)num, round(avg(sal),2)avgsal into p_num, p_avgsal
  from scott.emp where deptno=p_deptno;
exception
  when no_data_found then
    raise_application_error(-2000,'该部门编号不存在');
end;

declare
  v_num number;
  v_avgsal number;
begin
  proc_out(10, v_num, v_avgsal);
  dbms_output.put_line('10号部门的总人数为'||v_num||'平均工资为'||v_avgsal);
end;

in out参数模式

create or replace procedure proc_dept_dname_exist(
  p_io_value in out varchar2) is v_count number;
begin
  select count(*)into v_count from scott.dept where dname=p_io_value; 
  if(v_count>0)then
    p_io_value:='已存在';
  else
    p_io_value:='不存在'；
  end if;
end;  

declare
  v_io_value varchar2(20):='ACCOUNTING';
begin
  proc_dept_dname_exist(v_io_value);
  dbms_output.put_line('部门名称ACCOUNTING'||v_io_value||'!');
end;

查看存储过程及其源码信息

查看当前用户的子程序信息–user_procedures：查看当前用户所有的储存过程、函数信息

select object_name, object_type from user_procedures;

查看储存过程定义内容–user_source：查看当前用户所有对象的源代码

select name, text from user_source where type='PROCEDURE';

• user_errors：查看当前所有的储存过程或函数的错误信息

查询储存过程与数据库对象的依赖关系–user_dependencies

select name, type, referenced_name from user_dependencies
where referenced_name='EMP'or referenced_name='DEPT';

重新编译存储过程–alter procedure…compile语句

alter procedure proc_dept_insert compile;

删除存储过程–drop procedure

drop procedure proc_test;

函数的创建与管理function

创建无参函数–

create or replace function func_hello
return varchar2
as
begin
  return'hello function!';
end;

创建有参函数–

create or replace function func_get_dname(p_deptno dept.deptno%type)
return varchar2
as
  v_dname dept.dname%type;
begin
  select dname into v_dname from scott.dept where deptno=p_deptno;
  return v_dname;
end;

调用函数

SQL语句中调用

select func_hello from dual;

select func_get_dname(10)from dual;

在PL/SQL块中调用

declare
  v_info varchar2(100);
begin
  v_info:=func_hello;
  dbms_output.put_line('返回：'||v_info);
end;

declare
  v_no number;
  v_info varchar2(50);
begin
  v_no:=&no;
  v_info:=func_get_dname(v_no);
  dbms_output.put_line('部门名称：'||v_info);
end;

with函数创建简单函数

with function fun_isnumber(param in varchar2)
return varchar2
is
begin 
  if(to_number(param)is not null)then
    return 'Y';
  else
    retuen 'N';
  end if;
exception
  when others then 
    return'N';
end;
select fun_isnumber('abcd')from dual;

查看当前用户的所有函数和源码–user_source

select name, text from user_source where type='FUNCTION';

函数重编译

alter function func_get_dname compile;

删除函数

drop function func_hello;

触发器

• DML触发器
• 替代触发器
• 系统触发器

dml触发器

• before|after：前触发|后触发

• for each row：行级触发器

创建一个针对emp表的语句级dml触发器，要求在非工作时间禁止对emp表进行dml操作

create or replace trigger trig_emp_dml
  before insert or update or delete on scott.emp
begin
  if(to_char(sysdate,'DAY'))in('星期六','星期日')
    or(to_char(sysdate, 'HH24:MI')not between'08:30'and'17:30')then
    raise_application_error(-20002,'只要在工作时间进行操作。')；
  end if;
end trig_emp_dml;

通过scott用户创建一个语句级dml触发器，实现只有scott用户才可以对emp表进行dml操作的权限验证功能

create or replace trigger trig_emp_authority
  before insert or update or delete on emp
begin
  if user <> 'SCOTT' then
    raise_application_error(-20001,'您无权操作emp表')
  end if;
end trig_emp_authority;

行级触发器

• :old. :new.

创建一个行级触发器，对emp表工资的更新进行限制，要求加薪比例最高不能超过10%

create or replace trigger trig_emp_addsal
  before update of sal on emp
  for each row
declare
  v_scale number;
begin
  v_scale:=(:new.sal-:old.sal)/:old.sal;
  if v_scale>0.1 then
    :new.sal:=:old.sal*1.1;
	dbms_output.put_line('加薪不能超过10%，薪水更新成:'||：new.sal);
  end if;
end;

触发器谓词inserting、updating、deleting

对dept表执行一个操作日志功能，当用户对dept表操作时，自动在dept的日志记录表dept_log中保存

create table dept dept_log(
  logid number,
  type varchar2(20)not null,
  logdate date,
  deptno number(2),
  dname varchar2(20)not null,
  loc varchar2(30)not null,
  constraint pk_logid primary key(logid));

create sequence seq_dept_log;

create or replace trigger trig_dept_dml
before insert or update or delete on dept
for each row
begin
  when inserting then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'INSERT',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  when updating then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'UODATE',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  when deleting then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'DELETE',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  end case;

end;

执行顺序：

语句前触发器(before statement)->行级前触发器(before row)
->更新操作
->行级后触发器(after row)->语句级后触发器(after statement)

follows子句指定触发器的执行顺序

create or replace trigger dept_insert_one
before insert on dept 
for each row
begin
  dbms_output.put_line('执行第1个触发器');
end;

create or replace trigger dept_insert_two
before insert on dept
for each row
follows dept_insert_one
begin
  dbms_output.put_line('执行第2个触发器');
end;

复合触发器：

• before statement is 语前
• before each row is 行前
• after statement is 语后
• after each row is 行后

复合触发器的使用

替代触发器instead of ：建立在视图上的触发器

创建替代触发器来实现试图数据的删除操作

系统触发器

• on schema：表示对一个具体模式的的触发
• on database：对数据库级的触发，需要管理员权限

实现对数据库所有模式对象的ddl操作的日志记录

create table operate_log(
  logid number constraint pk_logid primary key, --主键标识
  operater varchar2(50),  --操作者名称
  operate_date date,  --操作时间
  object_name varchar2(50),  --对象名称
  object_type varchar2(50),  --对象类型
  object_owner varchar2(50)  --对象所有者名称
  );
create sequence seq_operate_log;
  
create or replace trigger trig_object_ddl
  after ddl on database
begin
  insert into operate_log(logid, operate_date, object)
end;

创建一个监控用户登录及注销的系统触发器

create table 


create or replace trigger trig_userlogon
  after logon

监控用户注销的系统触发器

触发器的查询

• user_triggers：当前的
• all_triggers：可以访问的
• bda_triggers：所有的

禁用触发器

alter trigger trig_object_ddl disable;

重新编译触发器

alter trigger trig_object_ddl compile;

删除触发器

drop trigger trig_object_ddl;

重点：

• 什么是数据库、关系型数据库
• 表空间的创建、管理
• 用户的创建、管理
• 权限的授予、回收
• 表
• 事务四大特性
• 事务的并发
• 数据库的隔离级别
• 查询
• 模式、视图、序列
• PL\SQL语法

输出所有比本部门平均工资高的员工信息

create or replace procedure p_test(v_deptno in number)
as
  cursor cursor_emp is select empno, ename, sal from emp where sal > (select avg(sal) from emp where deptno=v_deptno) and deptno = v_deptno;
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  a_sal number;
begin
  select avg(sal)into a_sal from emp where deptno=v_deptno;
  dbms_output.put_line('本部门平均为:'||a_sal);
  dbms_output.put_line('比本部门平均工资高的员工为');
  open cursor_emp; 
  loop
    fetch cursor_emp into v_empno, v_ename, v_sal;  
    exit when cursor_emp%notfound;
	dbms_output.put_line('员工号:'||v_empno||' 姓名:'||v_ename||' 工资:'||v_sal);
  end loop;
  close cursor_emp;
end;

begin
  p_test(10);
end;