Oracle总结及Mysql的分页查询

一、Mysql的分页查询

limit接受一个或两个数字参数，参数必须是一个整数常量。第一个参数指明从哪条记录开始，第二个参数指明页数大小
- 示例
```
-- 查询员工表前3名的员工的编号和姓名
select empno,ename from emp limit 3;
-- 或者
SELECT empno,ename FROM emp LIMIT 0,3;
查询员工表第三页的员工信息信息（假设每页显示4条，总共有14条）
SELECT empno,ename FROM emp LIMIT 8,4;
```
  MySQL的分页语句中 LIMIT后面的第一个参数是（page-1)*pagesize 第二个参数是pagesize 其中page表示页码，pagesize表示每一页显示的最大记录数

二、Oracle总结

（一）数据库模型、关系模型、三范式、有效性和安全性的实现

数据库模型
- 关系型数据模型：Oracle,MySql,SQLServer
- 非关系型数据模型：H2,Redis，MongoDB
关系模型
- 关系模式：是静态的稳定的
- 关系：关系是关系模式在某一时刻的状态或内容，关系是动态的
- 关系模型：关系模型是指用二维表的形式表示实体和实体间联系的数据模型
数据库设计步骤
- doc->从doc文档中找出实体->将实体和属性转换为表格
三范式
- 1NF:字段不可拆分
- 2NF：表中要有主键
- 3NF：表中不能有别的表的非主键列
数据库的有效性和安全性通过约束来实现
- 主键约束：唯一确定一条记录，记录不重复，不能为空
- 唯一约束：唯一确定一条记录，记录不重复，可以为空
- 默认约束：有些列不能为空，必须有值，但客户可能忘记赋值，此时用默认约束强制给空记录赋值一个默认值，保证数据的有效性，完整性
- 检查越俗：让数据有效，不能出现意外的数据
- 外键约束：如果依赖的表中没有相应的主键记录，则当前表就无法添加记录，这就是外键依赖，当然要删除依赖表中的记录时，如果记录有被依赖的情况，则要么报错不能删除，要么级联删除当前表中的记录，保证了数据的安全，避免了依赖不存在的问题。

(二)SQL语言的分类:DDL、DML、DQL、TCL、DCL

SQL语言的描述和作用
- SQL语言是一种结构化查询语言
- 作用是用来管理关系型数据库
SQL语言的分类
- DDL（Data Definition Language):数据定义语言
- DML（Data Manipulation Language):数据操纵语言
- DQL（Data Query Language):数据查询语言
- TCL（Transaction Control Language):事务控制语言
- DCL（Data Control Language):数据控制语言

DDL（数据定义语言）

关键词：create、alter、drop

表空间

定义：表空间是存储数据的地方，存储数据库对象的地方，在磁盘上对应有数据文件
Oracle中默认有一个系统表空间和临时表空间，如果创建用户不指定用户的表空间，则默认把用户创建在系统表空间，系统表空间类似于windows的C盘，如果把所有软件安装在C盘是不合理的，Oracle中把用户分配在系统表空间也是不合理的，最理想的是一个用户一个表空间，在删除用户时可以删除表空间，这样管理比较好。
表空间是虚拟的，可以无限大，数据文件是存储信息的载体
临时表空间：数据库操作时临时使用，如排序，去重，统计等大数据时使用，小规模可以在内存中完成

操作

-- 授予管理员权限
grant dba to user_test;
-- 授予创建表空间的权限
grant create tablespace to user_test;
-- 创建表空间
create tablespace sp1 datafile 'sp1.dbf' size 1m;
-- 创建临时表空间
create temporary tablespace tsp1 tempfile 'tsp1.dbf' size 2m;
-- 创建用户时指定表空间和临时表空间
create user user_test identified by user_test default tablespace sp1 temporary tablespace tsp1;
-- 授予删除表空间权限
grant drop tablespace to user_test;
-- 删除表空间
drop tablespace sp1 including contents and datafiles;
-- 删除临时表空间
drop tablespace tsp1 including contents and datafiles;
-- 授予扩大表空间的权限
grant alter database to user_test;
-- 扩大表空间
alter database datafile 'sp1.dbf' resize 2m;
-- 授予在表空间增加数据文件的权限
grant alter tablespace to user_test;
-- 增加数据文件
alter tablespacesp1 add datafile 'sp1_2.dbf' size 1m;

表

数据类型

number:数字类型
integer:整数
char:定长字符串
varchar:变长字符串
timestamp:时间戳
blob:二进制数据
clob:放大量的字符数据
bfile:二进制文件

对表的操作

-- 创建表
create table t1(id number);
-- 增加一列
alter table t1 add name char(4);
-- 修改列的宽度
alter table t1 modify name char(5);
-- 删除一列
alter table t1 drop column age;
-- 增加系统默认名字的主键约束
alter table t1 add primary key(id);
-- 增加自己命名的主键约束
alter table t1 add constraint t1_pk primary key(id);
-- 删除主键
alter table t1 drop primary key;
-- 删除指定约束的主键
alter table t1 drop constraint t1_pk;
-- 增加唯一约束
alter table t1 add constraint t1_uk unique(name);
-- 增加默认约束
alter table t1 modify age default 18;
-- 增加检查约束
alter table t1 add constraint t1_sex check(sex = 0 or sex = 1);
-- 增加外键约束
(什么都不写相当于no action,当子表有关联时删除父表记录会报错)
alter table t1 add constraint t1_fk foreign key(clazz) references t_clazz(id);
(设置为set null,删除父表记录时，把子表相应外键置为空)
alter table t1 add constraint t1_fk foreign key(clazz) references t_clazz(id) on delete set null;
(设置为cascade级联删除，删除父表时，子表相应的记录也会被删除)
alter table t1 add constraint t1_fk foreign key(clazz) references t_clazz(id) on delete cascade;
-- 删除外键约束
alter table t1 drop constraint
-- 删除表
drop table t1;
truncate table t1;

DML（数据操纵语言）

关键词：insert、delete、update

对表中的数据进行操作

-- 插入数据
-- 不指定添加的字段
insert into t_clazz values(1,'name1','');
-- 指定添加的字段
insert into t1(id,name,age,sex,clazz) values(3,'name1',10,0,1);
-- 修改
-- 修改所有记录
update t_clazz set name='新名称';
-- 修改指定记录
update t_clazz set name='新名称1' where id=1;
-- 删除
delete from t_clazz where id=1;

DQL(数据查询语言)

关键词：select

查询表中的信息

-- 查询当前用户下所有表的信息
select * from user_tables t;
-- 查询当前表的所有字段信息
select * from user_tab_columns where table_name='T_CLAZZ';
-- 给表起别名
as 或者是空格
select sal,empno,ename,sal+100 sal2 from emp;
-- 判断字段是否为空
select sal from emp where sal is null;
-- where过滤
select empno,ename,sal from emp where sal>=3000;
-- distinct去重
-- 查询部门里有员工的部门号
select distinct deptno from emp;
-- distinct对后面的字段都进行过滤
select distinct deptno,sal from emp;
-- 排序,升序asc,降序desc
-- 默认为升序
select * from emp order by sal;
-- 部门升序，部门中的员工工资降序
select * from emp order by deptno asc,sal desc;

TCL(事务控制语言)

-- 提交事务
commit;
-- 事务回滚
rollback；
-- 设置标志
savepoint p1;
-- 插入数据
insert into emp(empno) values(5);
-- 回滚到标志处
rollback to p1;

DCL(数据控制语言)

-- 创建角色
create role 角色名;
-- 分配权限
grant select on class to 角色名;

truncate,drop,delete三个的区别
- truncate和drop属于DDL，针对的是表，delete属于DML，针对的是表中的数据
- delete删除后不会释放空间，可以恢复数据，truncate和drop都会释放空间，不能恢复，truncate只删除表里的内容，drop删除的是全部，表上的所有对象都会删除掉
- 有害化排序：delete->truncate->drop

(三)索引

作用：快速查询数据
优缺点：
- 优点：DQL快速查询，为了保证查询速度，可以牺牲一定的空间和DML的操作时间
- 缺点：占空间（在保存正常业务数据的同时，还要额外的存储索引信息),在维护正常业务数据的同时，还要维护索引的数据（占时间）
什么情况下适合建立索引
- 一个条件经常出现在where中，就要考虑是否要建立索引
- 对于列中的数据区分度比较高的列也可以建立索引（反例：性别不适合）
- 如果同时又多个查询条件，where c1=a and c2 = b,建立联合索引(c1,c2),索引中的列的顺序和where条件中的顺序尽量一致
  - 索引(c1,c2...)条件是where c1 = a and c2=b 这个是好的，条件是where c2=b这个不是特别理想，或者where c2=b and c1 = a这个也不好
索引的分类
- 聚集索引：会改变记录的物理位置
- 非聚集索引：数据的顺序和它插入时的顺序一致，在索引中保存的是记录的rowid，找到索引，然后找到rowid，然后找到记录

(四)函数

单行函数

字符函数

-- 返回字符的ascii值：ascii函数
-- 将连个字符或字符串拼接起来：concat函数
-- 查找字符：instr函数
-- 返回字符串的长度：length函数
-- 将所有字符全部转换为小写：lower函数
-- 将所有字符全部转换为大写：upper函数
-- 去掉字符串中的空格：ltrim(去掉左边的空格),rtrim(去掉字符串右边的空格),trim(去掉字符串两边的空格)
-- 替换字符：replace函数
-- 截取字符中的一段：substr函数

数字函数

-- 求绝对值：abs函数
-- 求大于或等于某个数的最小值：ceil函数
-- 求小于或等于某个数的最大值：floor函数
-- 四舍五入：round函数
-- 截断函数：trunc函数
-- 取余：mod函数

日期函数

-- 显示当前时间：sysdate关键字
-- 在当前时间上增加一个月：add_months函数
-- 求当前月的最后一天十几号：last_day函数
-- 四舍五入日期：round函数
-- 求月份差：months_between函数
-- 求当前日期的下一个星期几是几号：next_day函数
-- 提取员工的入职月份：extract函数
-- 截断日期：trunc函数

转换函数

-- 转换成字符：to_char函数
-- 转换成数字：to_number函数
-- 转换成日期：to_date函数

多行函数

统计函数

-- 求最大值：max函数
-- 求最小值：min函数
-- 求员工的个数：count函数
-- 求平均值：avg函数
-- 求和：sum函数

解决空值：nvl

-- nv1(comm,0)函数：如果奖金（comm)为空的话，则转换为0
-- nv2(comm,1,0)函数：如果奖金(comm)不为空，则转换为1，否则，转换为0

decode函数

-- decode(job,'CLERK','店员');
-- 注释：如果job=’CLERK‘，则显示为店员

应用

行转列

CREATE TABLE resident(NAME VARCHAR(6) NOT NULL,r_month number(2) NOT NULL,sal NUMBER(8) NOT NULL);
INSERT INTO resident VALUES('赵一',1,10000);
INSERT INTO resident VALUES('赵一',2,10500);
INSERT INTO resident VALUES('赵一',3,9000);
INSERT INTO resident VALUES('赵一',4,10030);
INSERT INTO resident VALUES('赵一',5,10500);
INSERT INTO resident VALUES('赵一',6,10000);
INSERT INTO resident VALUES('赵一',7,12000);
INSERT INTO resident VALUES('赵一',8,10500);
INSERT INTO resident VALUES('赵一',9,10800);
INSERT INTO resident VALUES('赵一',10,10900);
INSERT INTO resident VALUES('赵一',11,10010);
INSERT INTO resident VALUES('赵一',12,10800);
SELECT * FROM resident;
DROP TABLE resident;
-- 显示每个人每月的工资，以列的形式显示，2020我国的平均收入是40000元左右，显示该居民是否达到或拖后腿
SELECT NAME,SUM(DECODE(r_month,1,sal)) 一月,
SUM(DECODE(r_month,2,sal)) 二月,
SUM(DECODE(r_month,3,sal)) 三月,
SUM(DECODE(r_month,4,sal)) 四月,
SUM(DECODE(r_month,5,sal)) 五月,
SUM(DECODE(r_month,6,sal)) 六月,
SUM(DECODE(r_month,7,sal)) 七月,
SUM(DECODE(r_month,8,sal)) 八月,
SUM(DECODE(r_month,9,sal)) 九月,
SUM(DECODE(r_month,10,sal)) 十月,
SUM(DECODE(r_month,11,sal)) 十一月,
SUM(DECODE(r_month,12,sal)) 十二月,
DECODE(TRUNC(SUM(sal)/40000),0,'拖后腿','合格') 是否合格
 FROM resident r GROUP BY NAME;

SQL递归的实现：

-- SELECT *  FROM XX STRAT WITH 从什么地方开始  CONNECT BY PRIOR 递归条件
-- 找id为1的子辈
SELECT * FROM cow START WITH ID=1 CONNECT BY PRIOR ID = parent_id;

(五)序列

创建序列

-- 创建序列：minvalue:最小值，maxvale：最大值，start with：开始值，increment：步长，cache：缓存
-- 缓存有最大值限制：
(最大值-最小值)/步长的绝对值，向上取整
create sequence seq1
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20;
-- 将序列改为循环使用:cycle
alter sequence seq1 cycle;

序列的两个属性

-- 下一个值
select seq1.nextval from dual;
-- 当前值
select seq1.currval from dual;

删除序列
```
drop sequence seq1;
```

(六)视图

引入视图的原因

-- 有些表不能给程序员开放，DBA可以建一个视图，给程序员开放视图的权限，这样就可以间接来访问受限的表。
-- 建立视图可以避免误修改数据的风险，对数据进行横向或纵向的保护

作用
- 把SQL存储起来，运行方便
- 可以有效的保护数据，对权限加以控制
特点
- 视图是虚表，逻辑存在的（不是真实的，抽象的）
- 视图关键字：view
- 以SQL的形式存在
```
create view v_emp as
select * from emp;
```
- 视图的定义：vw或v

建立视图

-- 建立视图，若视图已经存在，就进行更新
create or replace view v_emp as
select * from emp;

-- 创建带约束的视图:with check option
CREATE OR REPLACE FORCE VIEW v_test AS 
SELECT * FROM emp WHERE empno>7788
WITH CHECK OPTION;

-- 创建只能查看的视图
CREATE OR REPLACE FORCE VIEW v_test AS 
SELECT * FROM emp
WITH READ ONLY;

-- 视图涉及多个表
CREATE OR REPLACE VIEW v_emp_dept AS SELECT empno,dname FROM emp e, dept d WHERE e.deptno=d.deptno;

-- force:不存在权限也能创建视图
CREATE OR REPLACE FORCE VIEW v_t1 AS SELECT * FROM user22.t1;
-- 分配权限，能够查看此视图
grant select on t1 to scott;
-- 收回权限
revoke select on t1 from scott;

-- 查看视图
SELECT * FROM v_emp_dept;

物化视图
- 引入原因
  
  普通视图在执行SQL时要消耗性能，因为他们是一个SQL，物化视图执行时只查询自己的数据（不再进行计算）
- 分类：ON DEMAND 、ON COMMIT
  - ON DEMAND :在需要时（在查询视图时）再从基表中更新数据到物化同步（物化视图的同步
  - ON COMMIT:在更新视图的同时，同步物化视图，物化视图始终是最新的。（维护数据效率低）
SQL优化：索引和物化视图

(七)in和exists，PL/SQL编程

in和exists执行顺序
- in:由内而外，先执行子查询，将子查询作为结果集，再执行外查询
- exists：由外而内，相当于loop循环，先从外表取出一条记录，然后进入子查询看这条记录是否符合条件，若符合，返回true,否则返回false，然后继续判断下一条记录
in和exists例题
- 员工表employee,有2个字段salary , deptid ,查询所有数据,按照部门号从高到低,工资从低到高顺序输出
```
SELECT * FROM employee ORDER BY deptid DESC,salary;
```
- 员工表employee,有2个字段salary , deptid ,查询各个部门中高于所有员工平均工资的人数:查询的结果列为: 部门,人数
```
SELECT deptid 部门,COUNT(*) 人数 FROM employee WHERE salary>(
SELECT AVG(salary) FROM employee) GROUP BY deptid;
```
- 员工表employee,有2个字段salary , deptid ,查询出最高工资和最低工资的差是多少
```
SELECT MAX(salary)-MIN(salary) FROM employee;
```
- 用exists替换下面的sql中的in SELECT * FROM tb1 WHERE u_id IN (SELECT u_id FROM tb2 WHERE name = ‘张三’);
```
SELECT * FROM tb1 WHERE EXISTS (
SELECT * FROM tb2 WHERE NAME='张三' AND tb1.u_id=tb2.u_id);
```
当子查询返回的结果集很少时，采用in比较好；

当子查询返回的结果集很多时，采用exists比较好

PL/SQL编程

区域：在Test window进行编程

组成：

declare部分进行一些变量的定义

begin end;部分里面写执行程序

declare 
  -- Local variables here
  -- 进行一些变量的定义
  i integer;
begin
  -- Test statements here
  -- 执行程序在这里编写
  
end;

变量定义

直接定义字段类型
```
  i integer;
  v_username VARCHAR(10);
```
常量的定义
```
 pai CONSTANT NUMBER := 3.14;
```
字段类型来源于表中
```
v_empno emp.empno%TYPE;
```
行变量的定义
```
 v_emprow emp%ROWTYPE;
```

变量的赋值（用:=进行赋值）

-- 在定义变量时进行赋值，或者在使用前进行赋值
  v_clazz NUMBER:=11;
  v_college NUMBER(10) DEFAULT 1;
-- 在查询时进行赋值:将查询到的最高工资赋值给i
SELECT MAX(sal) INTO i FROM emp;
-- 行变量的赋值
SELECT emp.empno,emp.ename INTO v_emprow.empno,v_emprow.ename FROM emp WHERE rownum=1;

输出

dbms_output.put():输出到缓存

dbms_output.put_line()：将缓存中的和要输出的进行输出

dbms_output.put(1);
dbms_output.put_line(2);
 dbms_output.put_line(v_username||'-'||v_clazz||'-'||v_college);

异常处理：exception

-- sqlcode:异常编号，sqlerrom:异常信息

-- 捕获异常
  EXCEPTION
    WHEN no_data_found THEN 
      dbms_output.put_line('没有数据');
    WHEN too_many_rows THEN
      dbms_output.put_line('返回太多行');
    WHEN OTHERS THEN -- 对未知异常的处理
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);

选择语句(在编程窗口执行)：IF ELSIF

-- 成绩大于小于60为没有通过，大于60通过
declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  i:=60;
  IF i<60 THEN
    dbms_output.put_line('no pass');
    ELSE
      dbms_output.put_line('pass');
      END IF;
end;

选择语句(在SQL窗口执行): case when

SELECT score,CASE 
  WHEN score BETWEEN 0 AND 59 THEN '不及格'
   WHEN  score BETWEEN 60 AND 80 THEN '普通'
    WHEN  score BETWEEN 80 AND 90 THEN '良好'
      WHEN  score BETWEEN 90 AND 100 THEN '优秀' 
        END 
        FROM t_score;

for循环

declare 
  -- Local variables here
  v_score INTEGER;-- 成绩
  i integer;
begin
  -- Test statements here
  DELETE FROM t_score;-- 测试前先删除表数据
  FOR i IN 1..10 LOOP -- 2. for循环
 v_score:=abs(mod(dbms_random.random,100)); -- 1. 随机数
    INSERT INTO t_score(NAME,score) VALUES('name'||seq1.nextval,v_score);
  END LOOP;
  COMMIT;
  dbms_output.put_line(i);
end;

while循环

-- 变量定义时为初始化为null，无法进行比较
declare 
  -- Local variables here
  i INTEGER;
BEGIN
  IF i IS NULL THEN
    dbms_output.put_line('i is not init');
    i:=1;
    ELSE
      IF i<10 THEN
         dbms_output.put_line('i<10');
      ELSE
        dbms_output.put_line('i>=10');
  -- Test statements here
  WHILE i<10 LOOP
    i:=i+1;
    dbms_output.put_line(i);
  END LOOP;
end;

loop end loop

declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  /*
  for ... loop
    end loop
    while loop
      end loop
  */
  LOOP 
    IF i IS NULL THEN 
      i:=0;
      ELSIF i<10 THEN
        i:=i+1;
        ELSE
          EXIT; -- 退出循环
          END IF;
          dbms_output.put_line(i);
          END LOOP;
end;

-- 示例2
declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  i:=0;
  LOOP
    i:=i+1;
    EXIT WHEN i>10;
    dbms_output.put_line(i);
    END LOOP;
end;

(八)异常、游标、存储过程、自定义函数

自定义一个异常，当i未初始化时抛出异常，处理异常

declare 
  -- Local variables here
  i INTEGER :=0;
  myex EXCEPTION;
BEGIN
  -- 抛出一个系统自己编号的异常
  IF i IS NULL THEN
    RAISE myex;
  END IF;
  -- 抛出一个带有编号和信息的异常,自己定义的编号范围为（-20000,-29999)
  IF i=0 THEN
    raise_application_error(-20000,'i值不能为0');
  END IF;
  EXCEPTION
    WHEN myex THEN
      dbms_output.put_line('i未初始化');
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);
  -- Test statements here
end;

异常不能重复抛出，但是异常可以同时捕获
```
when ex1 or ex2 or ex3 then
...
```

游标：cursor

定义

游标是数据的集合，也可以说是数据集合的指针，可以从游标中获取集合中的值，一般在程序中使用，如：存储过程，函数，触发器
类型：
- 系统游标（也叫隐式游标）
- 用户游标：包含静态游标和动态游标
游标的操作：打开游标，遍历游标，关闭游标

用户游标

静态游标

手动打开游标，关闭游标，需要写指针移动的语句

declare 
  -- Local variables here
  -- 静态游标：后面的select语句是固定的
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
  -- 定义一个变量，接收游标所指向的记录
  v_row emp%ROWTYPE;
begin
  -- Test statements here
  -- 1.打开游标
  OPEN c_emp;
  -- 2. 遍历游标
  LOOP
    -- 游标指针的移动,取出游标所指向的记录，赋值给变量
    FETCH c_emp INTO v_row;  dbms_output.put_line(v_row.empno||'-'||v_row.ename);
    -- 当遍历完成的时候，跳出循环
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  -- 3.关闭游标
  IF c_emp%ISOPEN THEN
    CLOSE c_emp;
  END IF;
  -- 4.异常处理
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);
end;

简单操作，不用打开和关闭游标

declare 
  -- Local variables here
  i integer;
  -- 定义一个静态游标
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
  -- 定义一个变量，用来循环
  v_row emp%ROWTYPE;
begin
  -- Test statements here
  -- 使用for循环
  FOR v_row IN c_emp LOOP
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
  END LOOP;
end;

带参数的静态游标

-- 方式一
declare 
  -- Local variables here
  -- 定义游标
  CURSOR c_emp(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno;
  -- 定义一个行变量接收游标指向的值
  v_row emp%ROWTYPE;
begin
  -- Test statements here
  -- 1.打开游标
  OPEN c_emp(10);
  -- 2.遍历游标
  LOOP
    FETCH c_emp INTO v_row;
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
    -- 跳出条件
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  -- 3.关闭游标
  IF c_emp%ISOPEN THEN
    CLOSE c_emp;
  END IF;
end;
-- 方式二
declare 
  -- Local variables here
  -- 定义一个游标
  CURSOR c_emp(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno;
  -- 定义一个行变量，用来实现for循环
  v_row emp%ROWTYPE;
begin
  -- Test statements here
  FOR v_row IN c_emp(10) LOOP
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
  END LOOP;
end;

动态游标

declare 
  -- Local variables here
  -- 1.定义动态游标的类型
  TYPE dync IS REF CURSOR;
  -- 2.定义游标
  c_emp dync;
  -- 5.定义一个行变量，接收游标所指向的记录
  v_row emp%ROWTYPE;
  -- 定义一个sql语句
  v_sql VARCHAR(100);
  v_dno emp.deptno%TYPE;
begin
  -- Test statements here
  -- 3.打开游标
  SELECT deptno INTO v_dno FROM emp WHERE empno=7369;
  v_sql :='SELECT * FROM emp WHERE deptno='||v_dno;
  OPEN c_emp FOR v_sql;
  -- 4.遍历游标
  LOOP
    FETCH c_emp INTO v_row;
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
    -- 跳出循环
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  -- 6.关闭游标
  IF c_emp%ISOPEN THEN
    CLOSE c_emp;
  END IF;
end;

静态游标和动态游标在定义和打开有区别，动态游标后面的select语句可以用变量来实现拼接

系统游标（隐式游标）

系统已经定义好的，在做DML操作时会触发，输出影响到的行数的信息

declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  UPDATE emp SET sal=sal+0;
  dbms_output.put_line('影响到的行数'||SQL%ROWCOUNT);
  IF SQL%ROWCOUNT>0 THEN
    dbms_output.put_line('update success');
  ELSE 
    dbms_output.put_line('update failure');
  END IF;
end;

存储过程:procedure

定义

存储过程是一段已经编译好，并且已经有名称的程序，可以通过名称来调用他

简单的存储过程

-- 1.在sql窗口定义
CREATE OR REPLACE PROCEDURE p1
IS 
BEGIN
  dbms_output.put_line('hello world');
END p1;
-- 2.调用，可以在test窗口使用名称直接调用，也可以在sql窗口右键p1，执行

带参存储过程:in表示入参，可以省略，out表示出参

案例一

CREATE OR REPLACE PROCEDURE p1(v_dno emp.deptno%TYPE)
IS
-- 定义了一个游标
CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_dno;
v_row emp%ROWTYPE;
BEGIN
  FOR v_row IN c_emp LOOP
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
  END LOOP;
END p1;

案例二

CREATE OR REPLACE PROCEDURE p1(v_dno IN emp.deptno%TYPE,v_count OUT NUMBER)
IS
BEGIN
  SELECT COUNT(*) INTO v_count FROM emp WHERE deptno=v_dno;
END p1;

存储过程处理业务逻辑，如果有返回通过出参返回
拓展
- 集群：尽量把业务或操作分开执行，就像以前一个人做事情，现在分成了多个部门，自己做好自己的事情
- 如果把逻辑都给数据库，则数据库处理就成了瓶颈，影响系统的性能，我们会把工作交由应用服务器来处理，数据库只管数据，不管业务，给数据库松绑

自定义函数
- 定义
  
  自定义函数也是一段编译好的由名称的程序，和存储过程的区别是主要是为了得到一些数据（强调的是返回的东西），存储过程强调的是做什么事情，以及过程
- 简单的自定义函数及使用
```
-- 获取某部门的员工人数
CREATE OR REPLACE FUNCTION f1(v_dno emp.deptno%TYPE)
-- 1. 返回类型
RETURN NUMBER
IS
-- 2.定义变量
v_count NUMBER;
BEGIN
  -- 3.赋值
  SELECT COUNT(*) INTO v_count FROM emp WHERE deptno=v_dno;
  -- 4.返回
  RETURN v_count;
END f1;
```
- 带参的自定义函数：in入参，out出参，和存储过程一样

(九)触发器

概念

触发器不能主动调用，只有在对相应对象操作时自动触发。
触发器分类
- DML触发器
- 替代触发器
- 系统触发器

DML触发器

触发器的粒度

语句级触发器：对sql操作只做一次触发
行级触发器：对每行数据都触发

示例

insert into emp(empno) where empno in (1,2);
-- 语句级触发器就只触发一次
-- 行级触发器会触发两次，因为这条sql语句影响两行

创建触发器

语句级触发器

create or replace trigger 触发器名称
after/before insert or update or delete 
on 表名
delclare
变量声明的地方
begin
end;

行级触发器

create or replace trigger 触发器名称
after/before insert or update or delete 
on 表名
for each row
delclare
变量声明的地方
begin
end;

DDL触发器

当创建表，删除表，修改表时触发

DDL触发器的创建

CREATE OR REPLACE TRIGGER trg_ddl
AFTER DDL 
ON scott.schema
DECLARE
BEGIN
  INSERT INTO t_log(ID,log_user,log_date,log_text)
  VALUES(seq1.nextval,USER,SYSDATE,ora_sysevent||'-'||ora_dict_obj_name||'-'||ora_dict_obj_type);
END;

替代触发器

解决复杂视图不能插入数据的问题

替代触发器的创建

CREATE OR REPLACE TRIGGER trg_vemp
INSTEAD OF INSERT
ON v_emp
FOR EACH ROW
DECLARE
BEGIN
  INSERT INTO emp(empno,sal,comm) VALUES(:new.empno,:new.sal,0);
END;

(十)事务、锁

事务的四大特点
- 原子性：要么都做，要么都不做
- 一致性：事务操作前和操作后是平衡的
- 隔离性：多个事务并发对同一数据进行操作时，会有顺序，互不影响
- 永久性：当事务提交后，数据会永久保存，断电或重启不会产生影响
锁的分类
- 从限制程序角度
  - 排他锁
  - 共享锁
- 从产生时机角度
  - 自动锁
  - 显示锁
- 从产生的操作角度
  - DML锁
  - DDL锁
- 悲观锁和乐观锁

(十一)PGA、SGA、存储空间、分区表、union和union all、导入导出、SQL优化

PGA
- 程序缓存区，为了特定用户进程服务，是私有的
- 功能
  - 排序区
  - 会话区
  - 堆栈区
  - 游标区
SGA
- 系统全局区，所有用户都能使用，是共享的，一个oracle实例，一个SGA区
- 组成
  1. 固定区：通过这个区查找其他区的地址
  2. 重做缓冲区
  3. 块缓冲区
  4. 共享池
  5. 大池
  6. Java池
  7. 流池
存储空间

从小到大依次为：数据块，分区，段，表空间
- 数据块设置的大小应合理
- 分区是一系列连续的数据块的集合
- 数据段是分区的上层组织单位
- 表空间中有多个段，段和文件相对应
分区表

将一张表的数据存储到不同的表空间
优点
1. 增强可用性，一个分区坏了，其他分区还可以用
2. 均衡I/O，可以把表的不同分区分配到不同的磁盘I/O来平衡I/O改善性能
3. 提高性能，对大表的查询，增加，修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快
4. 分区对用户透明，用户感觉是同一张表
分类：按范围分区，hash分区，复杂分区

建立分区

-- 1.建立3个表空间
CREATE TABLESPACE tb1 DATAFILE 'tb1.dbf' SIZE 1m;
CREATE TABLESPACE tb2 DATAFILE 'tb2.dbf' SIZE 1m;
CREATE TABLESPACE tb3 DATAFILE 'tb3.dbf' SIZE 1m;
-- 2.建立分区表，
-- 根据范围分区
CREATE TABLE t_area(ID NUMBER PRIMARY KEY,money NUMBER)
PARTITION BY RANGE(money)
(
PARTITION p1 VALUES LESS THAN (1500) TABLESPACE tb1,
PARTITION p2 VALUES LESS THAN (3000) TABLESPACE tb2,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tb3
);
-- hash分区
CREATE TABLE t_area(ID NUMBER PRIMARY KEY,money NUMBER)
PARTITION BY HASH(money)
(
PARTITION p1  TABLESPACE tb1,
PARTITION p2  TABLESPACE tb2,
PARTITION p3  TABLESPACE tb3
);
-- 3.插入数据
INSERT INTO t_area VALUES(sq.nextval,1000);-- 将插入到分区p1
INSERT INTO t_area VALUES(sq.nextval,2000);-- 将插入到分区p2
INSERT INTO t_area VALUES(sq.nextval,5000);-- 将插入到分区p3
-- 4.查询数据，在相应分区查询相应的money
SELECT * FROM t_area PARTITION(p1);-- 查询p1分区
SELECT * FROM t_area PARTITION(p2);-- 查询p2分区
SELECT * FROM t_area PARTITION(p3);-- 查询p3分区
-- 查询6000工资员工的信息，在p3分区查找
select * from t_area partition(p3) where money=6000;
-- 内容，分区都删除
ALTER TABLE yourTable DROP PARTITION partionName1;
-- 只清除数据
ALTER TABLE yourTable TRUNCATE PARTITION partionName1;

为分区建立索引

全局索引

create index 索引名称 on 表名(字段) global

partition by range (字段)
(
  partition index_part1 values less than (60),

  partition index_part2 values less than (80),

  partition index_partmax values less than (maxvalue)
);

局部索引

create index 索引名 on 表名 (字段1, 字段2) local;

union和union all
- union和union all的要求
  - 每条sql的列数相同
  - 每条sql的列的类型要相互匹配，char对char，number对number
- 区别
  
  union结果没有重复的,union all结果重复

导入导出

导出
- pl/sql导出：tools->export
- 命令行导出：
```
exp scott/scott@orcl file="d:/1.sql"
```

导入

pl/sql导入：tools->import

命令行导入：

imp scott/scott@orcl file="d:/1.sql" full=y
-- 成功终止导入，说明导入成功

SQL优化
- 写出具体的列名，不写*，减少SQL的分析时间，不用再去数据字典中找列的信息。
- 创建索引
- 对索引列的过滤时，不用函数，不要隐匿转换数据类型，不用模糊匹配开头查询
- 创建合理的表结构，可以适当对数据进行冗余，减少子查询

软件下载提取码：qwer