Oracle学习笔记0.5

阅读数: 次 2021-09-01

文章导航

Oracle学习笔记

🌞 🌞 🌞

游标，触发器，索引，表分区，数据库优化…

游标

一.游标概念：

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。

概括来讲，SQL的游标是一种临时的数据库对象，即可以用来存放在数据库表中的数据行副本，也可以指向存储在数据库中的数据行的指针。

游标提供了在逐行的基础上操作表中数据的方法。

游标的一个常见用途就是保存查询结果，以便以后使用。

游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

二.游标特点：

SQL语言是面向集合的，是对指定列的操作。如果要对列中的指定行进行操作，就必须使用游标。
当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配一个上下文区
（Context Area）
游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独
处理的方法

三.显式游标

1).显式游标则用于处理SELECT语句返回的多行数据。

2).显示游标可以分为四个阶段：

定义游标（DECLARE）
打开游标（OPEN）
提取游标（FETCH）
关闭游标（CLOSE）
3).显式游标属性：
显式游标属性用于返回显式游标的执行信息。
游标属性使用格式为：游标名 + 属性名。

%ISOPEN
用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE 。
%FOUND
检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE 。
%NOTFOUND
与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE 。
%ROWCOUNT
返回到当前行为止已经提取到的实际行数。

4).静态游标

书写格式

--静态游标 静态的select语句
--定义游标
CURSOR cusor_name[(参数)] IS select_statement;
--打开游标
OPEN cursor_name;
--提取数据
FETCH cursor_name INTO variable1,variable2,...,每次只能处理一行数据，为了处理结果集中多行数据，必须使用循环语句进行处理。
--关闭游标
CLOSE curso_name;

游标示例

--循环打印部门为10的人员信息编号和名称
DECLARE
  CURSOR emps IS SELECT empno,ename from emp WHERE deptno=10;--定义静态游标
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
BEGIN
  OPEN emps;--打开游标
  loop
    FETCH emps INTO v_empno,v_ename;--读取一行
    EXIT WHEN emps%NOTFOUND;--没有值的时候停止
    dbms_output.put_line(v_empno||v_ename);
  END LOOP;
  CLOSE emps;--关闭游标
END;

table类型提取

--循环打印dept表中数据 使用table结构一次性提取
DECLARE
  --声明一个table数据类型
  TYPE table_sinfo IS TABLE of dept%ROWTYPE INDEX BY BINARY_INTEGER;
  v_depts table_sinfo;
  --定义游标
  CURSOR deptsc IS SELECT * FROM dept;
BEGIN
  --打开游标
  OPEN deptsc;
  --读取
  FETCH deptsc BULK COLLECT INTO v_depts;
  CLOSE deptsc;
  --循环取值
  FOR v_i IN v_depts.first..v_depts.last LOOP
    dbms_output.put_line(v_depts(v_i).dname);
  END LOOP;
END;

带参游标

带参游标：在定义了参数游标之后，当使用了不同参数值多次打开游标时，可以生成不同的结果集，定义参数游标时，游标参数只能指定数据类型，而不能指定长度。

--输入部门编号，打印人员信息编号和名称（带参游标）
DECLARE
  CURSOR emps(v_deptno NUMBER) IS SELECT empno,ename from emp WHERE deptno=v_deptno;--定义静态游标
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
BEGIN
  OPEN emps(30);--打开游标
  loop
    FETCH emps INTO v_empno,v_ename;--读取一行
    EXIT WHEN emps%NOTFOUND;--没有值的时候停止
    dbms_output.put_line(v_empno||v_ename);
  END LOOP;
  CLOSE emps;--关闭游标
END;

游标for循环

可直接用子查询

--获取每个部门的部门名称（for in循环）隐式打开,关闭
DECLARE
  CURSOR depts IS SELECT * FROM dept;
  v_dept dept%ROWTYPE;
BEGIN
  FOR v_dept IN depts LOOP
    dbms_output.put_line(v_dept.dname);
  END LOOP;
END;
--或
DECLARE
  v_dept dept%ROWTYPE;
BEGIN
  FOR v_dept IN (SELECT * FROM dept) LOOP
    dbms_output.put_line(v_dept.dname);
  END LOOP;
END;

5).动态游标

打开游标时指定其对应的select语句,只有动态游标可以返回。

书写格式

--动态游标 打开游标时指定其对应的select语句,只有动态游标可以返回
--定义游标
type ref_type_name IS REF CURSOR;
cursor_variable ref_type_name;

游标示例

--输出所有部门(,员工)名称
DECLARE
  --定义一个类型是动态游标
  TYPE cusortype IS REF CURSOR;
  --声明变量 游标变量
  mycursor cusortype;
  v_dept dept%ROWTYPE;
  v_emp emp%ROWTYPE;
BEGIN
  --打开指定sql
  OPEN mycursor FOR SELECT * FROM dept;
  LOOP
    FETCH mycursor INTO v_dept;
    EXIT WHEN mycursor%NOTFOUND;
    dbms_output.put_line('部门名称'||v_dept.dname);
  END LOOP;
  CLOSE mycursor;
  --可以多次打开
  OPEN mycursor FOR SELECT * FROM emp;
  LOOP
    FETCH mycursor INTO v_emp;
    EXIT WHEN mycursor%NOTFOUND;
    dbms_output.put_line('员工名称'||v_emp.ename);
  END LOOP;
  CLOSE mycursor;
END;

--创建一个存储过程，返回所有员工信息。out
CREATE OR REPLACE PROCEDURE getEmpProc(emps OUT SYS_REFCURSOR)--声明个类型是游标的出参
IS 
BEGIN
  OPEN emps FOR SELECT * FROM emp;
END;

6).练习

①——

--创建一个存储过程，以部门号为参数，输出入职日期最早的10个员工信息
CREATE OR REPLACE PROCEDURE pro_dateemps(
  v_deptno NUMBER
)
AS
  v_i NUMBER:=0;
BEGIN
  FOR v_emps IN (SELECT * FROM emp WHERE deptno=v_deptno ORDER BY hiredate) LOOP
    dbms_output.put_line('员工编号：'||v_emps.empno);
    dbms_output.put_line('员工姓名：'||v_emps.ename);
    dbms_output.put_line('员工入职日期：'||to_char(v_emps.hiredate,'yyyy-MM-dd'));
    dbms_output.put_line('员工部门：'||v_emps.deptno);
    IF v_i>=10 THEN
      EXIT;
    END IF;
  END LOOP;
END;

DECLARE
  v_deptno emp.deptno%TYPE:=&did;
BEGIN
  pro_dateemps(v_deptno);
END;

--创建一个存储过程，以一个整数为参数，输出工资最高前几个员工信息
CREATE OR REPLACE PROCEDURE pro_empsal(
  v_mas NUMBER
)
AS
  v_i NUMBER:=0;
BEGIN
  FOR v_emps IN(SELECT * FROM emp ORDER BY sal DESC) LOOP
    dbms_output.put_line('员工编号：'||v_emps.empno);
    dbms_output.put_line('员工姓名：'||v_emps.ename);
    dbms_output.put_line('员工工资：'||v_emps.sal);
    dbms_output.put_line('员工部门：'||v_emps.deptno);
    v_i:=v_i+1;
    IF v_i>=v_mas THEN
      EXIT;
    END IF;
  END LOOP;
END;

DECLARE
  v_mas emp.deptno%TYPE:=&mas;
BEGIN
  pro_empsal(v_mas);
END;

②——

DECLARE
  --定义
  CURSOR cs_a IS SELECT * FROM course;
  courserow course%ROWTYPE;
BEGIN
  OPEN cs_a;--打开游标
  IF cs_a%ISOPEN THEN--判断游标是否打开
    dbms_output.put_line('游标已经打开');
  END IF;
  LOOP
    FETCH cs_a INTO courserow;--提取一行数据
    IF cs_a%FOUND THEN--判断是否取到数据
      dbms_output.put_line(courserow.cid||'：'||courserow.cname);
    ELSE
      EXIT;
    END IF;
  END LOOP;
END;

--提取全部数据
DECLARE
  CURSOR cs_a IS SELECT * FROM course;
  --定义变量接受多行数据，索引表
  TYPE ctable IS TABLE OF course%ROWTYPE
  INDEX BY BINARY_INTEGER;--数字/字符串
  ct ctable;
BEGIN
  OPEN cs_a;
  FETCH cs_a BULK COLLECT INTO ct;
  CLOSE cs_a;
  FOR i IN ct.first..ct.last LOOP
    dbms_output.put_line('第'||i||'行:'||ct(i).cname);
  END LOOP;
END;

--设置班级毕业
DECLARE
  cname classes.classname%TYPE;
  nums NUMBER;
BEGIN
  cname:=&cname;
  --判断班级是否存在
  SELECT COUNT(*) INTO nums FROM classes WHERE classname=cname;
  IF nums=1 THEN
    --修改班级毕业
    UPDATE classes SET state=0 WHERE classname=cname;
    --判断修改成功
    IF SQL%ROWCOUNT=1 THEN
      SELECT classid INTO v_classid FROM classes WHERE classname=cname;
      DELETE student WHERE Classid=v_classid;
      IF SQL%NOTFOUND THEN
        dbms_output.put_line('修改失败！');
        ROLLBACK;
      ELSE
        dbms_output.put_line('修改成功！');        
        COMMIT;
      END IF;
    END IF;
  ELSE
    dbms_output.put_line('班级信息不存在');
  END IF;
END;

四.隐式游标

1).隐含游标用于处理SELECT INTO 和DML语句。

2).隐含游标：

当执行一条DML语句或者SELECT…INTO语句时（非SELECT查询语句），都会创建一个隐含游标。
隐含游标的名称是SQL，不能对SQL游标显式地执行OPEN、FETCH和CLOSE语句。
Oracle隐式地打开、提取，并总是自动地关闭SQL游标。隐式游标针对紧邻的SQL语句。
隐式游标属性包括SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT。

-- 隐式游标,不需要自行打开，关闭，名为SQL(针对相邻的sql语句)
--删除指定编号员工是否存在sql%found
DECLARE
  v_empno NUMBER(10):=&e;
begin 
  DELETE emp WHERE empno=v_empno;
  --SQL%隐式游标名
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('查无此人');
  ELSE
    dbms_output.put_line('删除成功');
    COMMIT;
  END IF;
END;

触发器

一.触发器概述：

触发器是在事件发生时隐式地自动运行的PL/SQL程序块，不能接收参数，不能被调用，触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发；当发生特定事件（如修改表数据、登录到数据库）时，Oracle会自动执行触发器的相应代码。

对于同一个对象,事件可以有多个触发器
达到触发条件全部执行，执行顺序和创建顺序无关
当一个触发器出现错误时，之后所有触发器都不执行
触发器可以调用函数，过程，但其中不能使用commit，rollback

二.触发器组成：

触发事件
DML或DDL语句。
触发时间
是在触发事件发生之前(before)还是之后(after)触发
触发操作
使用PL/SQL块进行相应的数据库操作
触发对象
表、视图、模式、数据库

触发频率
触发器内定义的动作被执行的次数。
语句级的在事件发生时执行一次，行级的影响几条记录执行几次

三.触发器的限制：

触发器代码的大小不能超过32K。
如果确实需要使用大量的代码创建触发器，则应该首先创建过程，然后在触发器中使用CALL语句调用过程
触发器代码只能包含DML(INSERT、UPDATE和DELETE)语句。
不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）。

四.触发器基本语法

自定义错误：

RAISE_APPLICATION_ERROR用法：
RAISE_APPLICATION_ERROR(errorNumber,errorString)
errorNumber是数值在-20000到-20999之间，errorString为自定义的错误信息

--语法
CREATE OR REPLACE TRIGGER 触发器名称
{befor/AFTER}--时机(事件发生前，后)
{DELETE OR UPDATE OR INSERT}--条件谓词（操作）
[fo 列名]--针对一列
ON 表名
[FOR EACH ROW]--行级触发器
[WHEN 条件]--行级执行条件
代码块 BEGIN
END;

五.触发器类型：

1).DML触发器

在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。

before触发器

示例

--周一，周五不能修改emp表
CREATE OR REPLACE TRIGGER tri_updateemp
BEFORE UPDATE ON emp
BEGIN
  --判断今天星期几
  IF to_char(SYSDATE,'day') IN('星期五','星期一') THEN
    raise_application_error(-20001,'星期五和星期一不可修改emp表');
  END IF;
END;
UPDATE emp SET emp.ename=emp.ename WHERE emp.empno='7499';
--触发器失效
ALTER TRIGGER tri_updateemp DISABLE;

条件谓语(inserting,updating,deleting)

--周一，周五emp表不可增删改
CREATE OR REPLACE TRIGGER tri_updateemp
BEFORE UPDATE OR INSERT OR DELETE ON emp
BEGIN
  --判断今天星期几
  IF to_char(SYSDATE,'day') IN('星期五','星期一') THEN
    IF updating THEN
      raise_application_error(-20001,'星期五和星期一不可修改emp表');
    ELSIF deleting THEN
      raise_application_error(-20001,'星期五和星期一不可删除emp表');      
    ELSIF inserting THEN
      raise_application_error(-20001,'星期五和星期一不可新增emp表');
    END IF;
  END IF;
END;

--触发器启用
ALTER TRIGGER tri_updateemp ENABLE;
UPDATE emp SET emp.ename=emp.ename WHERE emp.empno='7499';
INSERT INTO emp (empno,ename)VALUES(1000,'张三');

行级触发器

FOR EACH ROW选项说明触发器为行触发器。

行触发器和语句触发器的区别表现在：

行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发器约束条件，均激活一次触发器；
而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW选
项时，BEFORE和AFTER触发器为语句触发器，而INSTEAD OF触发器则只能为行触发器。

--行级触发器（可以指定条件，视图触发器一般都是）
--部门30的人工资不能降低，奖金不能低，也不能删除
CREATE OR REPLACE TRIGGER tri_salcomm
BEFORE UPDATE OR DELETE ON emp
FOR EACH ROW--行级
  WHEN(old.deptno=30)
BEGIN
  IF updating('sal') THEN
    IF :new.sal<:old.sal THEN
      raise_application_error(-20000,'工资不能降低');
    END IF;
  ELSIF updating('comm') THEN
    IF :new.comm<:old.comm THEN
      raise_application_error(-20000,'奖金不能降低');
    END IF;
  ELSIF deleting THEN
    raise_application_error(-20000,'部门30不能删除');
  END IF;
END;
DELETE emp WHERE deptno=30;

after触发器

如果指定了AFTER关键字，则表示在执行DML操作之后触发触发器利用行触发器实现级联更新。

在修改了主表dept中的deptno之后（AFTER），级联的、自动的更新子表emp 中原来在该部门的deptno。

-- after触发器（级联操作，备份）
--在修改dept表中deptno后，顺便把它级联的人员表中部门编号更新
CREATE OR REPLACE TRIGGER tri_updateEmpforDept
AFTER
UPDATE ON dept
FOR EACH ROW
BEGIN
  UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;--把旧的部门编号改成新的
END;
UPDATE dept SET deptno=60 WHERE deptno=20;
SELECT * FROM emp;
--删除dept表中记录时，将原有的记录保存到一个回收表delDept中
--回收表
CREATE TABLE delDept(
  deptno NUMBER(10),
  dname VARCHAR2(200),
  loc VARCHAR2(200)
)
--新建存储过程
CREATE OR REPLACE PROCEDURE insertDelDept(v_deptno NUMBER,v_dname VARCHAR2,v_loc VARCHAR2)
IS
BEGIN
  INSERT INTO deldept (deptno,dname,loc)VALUES(v_deptno,v_dname,v_loc);
END;
--触发器调用存储过程
CREATE OR REPLACE TRIGGER tri_delDept
AFTER DELETE ON dept
FOR EACH ROW
BEGIN
  insertDelDept(:old.deptno,:old.dname,:old.loc);
END;
DELETE dept WHERE deptno=73
SELECT * FROM deldept;

2).替代触发器

是oracle8专门为进行视图操作的一种触发器。

3).系统触发器

对数据库系统事件进行触发，如启动、关闭等。

--系统触发器
CREATE TABLE loginlog(
  loguser VARCHAR2(2000),
  loginip VARCHAR2(200),
  logindate DATE
)
SELECT * FROM loginlog
--登陆退出时触发器
CREATE OR REPLACE TRIGGER logintest_tri
AFTER logon ON DATABASE
BEGIN
  INSERT INTO loginlog VALUES(ora_login_user,ora_client_ip_address,SYSDATE);
END;

4).练习

①——

--周六周日，不能修改emp表，不在早八点到晚六点也不能修改emp表；提示：非工作时间不能修改emp表
CREATE OR REPLACE TRIGGER tri_updateEmp
BEFORE
UPDATE OR DELETE OR INSERT ON emp
BEGIN
  IF to_char(SYSDATE,'day')IN('星期六','星期日') OR
  to_number(to_char(SYSDATE,'hh24'))>=18 OR
  to_number(to_char(SYSDATE,'hh24'))<8 then
    raise_application_error(-20001,'非工作时间不能修改emp表');
  --to_number(to_char(SYSDATE,'hh24:mi'))>=18 NOT between'8:00' AND '18:00' THEN
  END IF;
END;
--启用触发器
ALTER TRIGGER tri_updateemp ENABLE;
SELECT to_char(SYSDATE,'hh24')-8 FROM dual;

SELECT * FROM emp FOR UPDATE

--编写一个触发器实现如下功能
--a) 修改后的薪金要大于修改前的薪金
--b) 工资增量不能超过原工资的10%
--c) 目前没有部门的职工不能涨工资
CREATE OR REPLACE TRIGGER tri_empsal
BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
  IF updating('sal') THEN
    IF :new.sal<=:old.sal THEN
      raise_application_error(-20001,'修改后的薪金要大于修改前的薪金');
    ELSIF (:new.sal-:old.sal)>(:old.sal*0.1) THEN
      raise_application_error(-20001,'工资增量不能超过原工资的10%');
    ELSIF :old.deptno IS NULL THEN
      raise_application_error(-20001,'目前没有部门的职工不能涨工资');
    END IF;
  END IF;
END;
ALTER TRIGGER tri_updateemp ENABLE;--启用
ALTER TRIGGER tri_updateemp DISABLE;--失效
UPDATE emp SET comm=1110 WHERE empno=1

--在emp表上编写一个触发器，实现如下功能
--当插入和删除的员工记录属于10号部门时，记录下操作时间和语句种类（插入/删除）
CREATE TABLE emp10deptno(
  operationdate DATE,
  operation VARCHAR2(10)
)
CREATE OR REPLACE TRIGGER tri_10empdate
AFTER INSERT OR DELETE ON emp
BEGIN
  IF inserting THEN
    INSERT INTO emp10deptno VALUES(SYSDATE,'插入');
  ELSIF deleting THEN
    INSERT INTO emp10deptno VALUES(SYSDATE,'删除');   
  END IF;
END;
insert into emp (empno,ename,deptno)VALUES(2,'张三',10);
DELETE emp WHERE empno=1;
SELECT * FROM emp10deptno

②——

--主键自增,可以使用序列+触发器完成主键自增
CREATE OR REPLACE TRIGGER tri_course
BEFORE INSERT ON course
FOR EACH ROW
DECLARE
  nums NUMBER;
BEGIN
  --判断插入的cid是否为空
  IF :new.cid IS NULL THEN
    --主键不重复，求最大值+1
    SELECT MAX(cid)+1 INTO :new.cid FROM course;
  END IF;
  SELECT count(*) INTO nums FROM course WHERE cid=:new.cid;
  IF nums != 0 THEN
    raise_application_error(-20000,'主键重复');    
  END IF; 
END;
INSERT INTO course VALUES(NULL,'AAA');
SELECT * FROM course;

--修改语句的触发器中不能操作同一张表的数据
CREATE OR REPLACE TRIGGER tri_course
BEFORE update ON course
FOR EACH ROW
DECLARE
  nums NUMBER;
BEGIN
  --SELECT COUNT(*) INTO nums FROM course WHERE cid=:old.cid;
  --DELETE course WHERE cid=6;
  dbms_output.put_line(nums);
END;
UPDATE course SET cname='S9' WHERE cid=1;

--视图可以操作，操作的是原表的原数据
SELECT * FROM v_score
/*试图触发器
1.没有befor/after，换为INSTEAD OF
2.必须有FOR EACH ROW(行级)
*/
CREATE OR REPLACE TRIGGER trie_vscore
INSTEAD OF UPDATE
ON v_score
FOR EACH ROW
DECLARE
BEGIN
  dbms_output.put_line('操作试图');
END;

数据库优化

一.SQL语句

1).使用大写

2).尽量避免sql语句进行全表扫描

-- 1).值不要为空（null），可以添加默认值
-- 2).减少在where中使用（！，=，>，<）
-- 3).减少使用in，not in,可以使用between and(..到..)
-- 4).尽量避免在where子句中使用or,and来连接条件，可使用 union all
SELECT * FROM student WHERE stuno='S001'
UNION --合并查询结果，去重
SELECT * FROM student WHERE stuno='S002'

SELECT * FROM student
UNION ALL--合并查询结果
SELECT * FROM student
-- 与要合并的查询结果的类型和列数相关
SELECT stuname,state FROM student WHERE stuno='S001'
UNION
SELECT tname,posts FROM teacher WHERE tid='T01'

二.数据库设计

1).对于常用操作，可以使用视图/函数/过程简化操作；

2).尽量减少使用中文（性别,状态等）;

索引

一.索引特点：

跟表相关的数据逻辑结构，用来调高数据的检索效率
索引表相当于一个键值集合，键是唯一的，用于查找对应的值

二.书写格式

对于常用来当作查询条件的列可以创建索引

CREATE 索引类型 INDEX [SCHEMA.] 索引名称 ON 表名
(列名)
[TABLESPACE ts]--表示索引存储的表空间
[STORAGE s]--表示存储参数
[PCTFREE pf]--表示索引数据块空闲空间的百分比
[NOSORT ns]--表示不再排序
SCHEMA:--表示oracle模式，省略后默认为当前用户

三.分类：

1.单列索引和复合索引
2.唯一索引和非唯一索引

--B树索引
CREATE INDEX INDEX_stu_name ON student(stuname);
SELECT * FROM student;
--复合索引(多列)
CREATE INDEX INDEX_score_nocid ON score(stuno,cid);
SELECT * FROM score;
--唯一索引(创建表时，主键列会自动创建唯一索引)
CREATE TABLE stu(
  stuno NUMBER PRIMARY KEY,
  sname VARCHAR2(10),
  phone NUMBER(11)
)
--创建时要求当前列的值不能重复
CREATE UNIQUE INDEX INDEX_stu_phone ON student(phone);
SELECT * FROM student;

INSERT INTO stu VALUES(1,'admin','15812223456');I
INSERT INTO stu VALUES(2,'tom','15812223456');
--位图索引:索引列基数较小时使用（性别/状态）
CREATE BITMAP INDEX index_student_state ON student(state);

表分区

一.范围分区

--范围分区：将数据按照指定的范围保存在不同的空间上
--创建表空间
CREATE TABLESPACE tsp_a
DATAFILE 'F:/tablespace/a.dbf'
SIZE 5m
CREATE TABLESPACE tsp_b
DATAFILE 'F:/tablespace/b.dbf'
SIZE 5m
-- 表分区：必须和表一起创建
CREATE TABLE tests(
  tid NUMBER PRIMARY KEY,
  tname VARCHAR2(10),
  age NUMBER
)
PARTITION BY RANGE(age)(
  PARTITION part_a VALUES LESS THAN(18) TABLESPACE tsp_a,
  PARTITION part_a VALUES LESS THAN(60) TABLESPACE tsp_b--第二个分区会从第一个的最大值开始
)
INSERT INTO tests VALUES(1,'tom',15);
INSERT INTO tests VALUES(2,'tom',18);
INSERT INTO tests VALUES(3,'tom',20);
SELECT * FROM tests;
DROP TABLESPACE tsp_a INCLUDING CONTENTS AND DATAFILES;

二.列表分区

--列表分区：按照值分配到不同空间
CREATE TABLE tests2(
  tid NUMBER PRIMARY KEY,
  tname VARCHAR2(10),
  age NUMBER,
  state NUMBER--0正常 1异常
)
PARTITION BY LIST(state)(
  PARTITION part_a VALUES(0) TABLESPACE tsp_a,
  PARTITION part_b VALUES(1) TABLESPACE tsp_b
)

三.散列分区

--散列分区：将数据信息按照Hash值分区
CREATE TABLE tests3(
  tid NUMBER PRIMARY KEY,
  tname VARCHAR2(10),
  age NUMBER
)
PARTITION BY HASH(tname)(
  PARTITION part_a TABLESPACE tsp_a,
  PARTITION part_b TABLESPACE tsp_b
)

四.本地索引分区

--本地索引分区(本地索引会区分表结构)
CREATE TABLE tests4(
  tid NUMBER PRIMARY KEY,
  tname VARCHAR2(10)
)
PARTITION BY RANGE(tname)(
  PARTITION part_a VALUES LESS THAN(10) TABLESPACE tsp_a,
  PARTITION part_b VALUES LESS THAN(20) TABLESPACE tsp_b
)
--和表分区相对应
CREATE INDEX test_index ON tests4(tname) GLOBAL HASH(
  PARTITION part_a TABLESPACE tsp_a,
  PARTITION part_b TABLESPACE tsp_b
)

五.全局索引分区

--全局索引分区
CREATE TABLE tests5(
  tid NUMBER PRIMARY KEY,
  tname VARCHAR2(10)
)
PARTITION BY RANGE(tname)(
  PARTITION part_a TABLESPACE tsp_a,
  PARTITION part_b TABLESPACE tsp_b
)
--和表分区相对应
CREATE INDEX test_index ON tests5(tname) LOCAL(
  PARTITION part_a TABLESPACE tsp_a,
  PARTITION part_b TABLESPACE tsp_b
)

本文作者： 腾飞
本文链接： https://www.tengfei.eu.org/article/b28af766.html
版权声明： 本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。转载请注明出处！

游标

一.游标概念：

二.游标特点：

三.显式游标

1).显式游标则用于处理SELECT语句返回的多行数据。

2).显示游标可以分为四个阶段：

3).显式游标属性：

4).静态游标

书写格式

游标示例

table类型提取

带参游标

游标for循环

5).动态游标

书写格式

游标示例

6).练习

①——

②——

四.隐式游标

1).隐含游标用于处理SELECT INTO 和DML语句。

2).隐含游标：

触发器

一.触发器概述：

二.触发器组成：

三.触发器的限制：

四.触发器基本语法

五.触发器类型：

1).DML触发器

before触发器

行级触发器

after触发器

2).替代触发器

3).系统触发器

4).练习

①——

②——

数据库优化

一.SQL语句

1).使用大写

2).尽量避免sql语句进行全表扫描

二.数据库设计

1).对于常用操作，可以使用视图/函数/过程简化操作；

2).尽量减少使用中文（性别,状态等）;

索引

一.索引特点：

二.书写格式

三.分类：

表分区

一.范围分区

二.列表分区

三.散列分区

四.本地索引分区

五.全局索引分区