oracle个人笔记

20.4.27，终于开始了一个写博客的习惯，这是自己写的第一个博客，主要是为了以后知识点的复习，不想边学边丢，也是为了记录学习的新知识，加油！

一. 登录

第一个就写这个主要是因为我自己老是忘记怎么登录，以此谨记

 

  1. 输入用户名：sys as sysdba

    输入口令：Oracle

  2. conn 用户名/密码

    conn sys/oracle as sysdba

二. oracle体系结构

 

1 存储结构

1.1 物理存储结构

用于描述oracle数据库外部数据的存储，即在操作系统中如何组织和管理数据，与具体的操作系统有关

 

1.1.1 控制文件(.CTL)

  1. 每个oracle数据库至少有一个控制文件，每个控制文件只能属于一个数据库

  2. 是记录数据库物理结构的二进制文件

  3. 创建数据库时自动创建控制文件

  4. 查看控制文件信息

    select * from v$controlfile

 

1.1.2 数据文件

存储数据库数据，如：系统数据（数据字典），用户数据（表，索引数据，簇等）排序，散列等操作的中间结果

  1. 每一个表空间可以包含一个或多个数据文件，一个数据文件只能属于一个表空间

  2. 当数据库容量越界时，数据文件能自动扩展

  3. 创造表空间时，会同时创建该表空间的数据文件

 

1.1.3 重做日志文件

  1. 重做日志文件记录所有对数据库数据所做的修改，可以使用它恢复数据库

  2. 重做日志文件存储数据库重做日志信息，称作联机重做日志文件

  3. 每个数据库至少需要两个重做日志文件组，采用循环写的方式进行工作

  4. 查看重做日志文件组及成员信息

    select * from v$logfile

  5. 查看重做重做日志组信息

select * from v$log

1.1.4 初始化参数文件

用来记录数据库的配置文件，包括对初始化参数文件的管理与维护，以不同的方式启动或关闭实例

  1. 静态参数文件（pfile）:可以使用编辑器修改，文件名init.ora

  2. 服务器参数文件（spfile）:二进制文件只能通过sql命令修改，文件名SPFile

1.1.5 口令文件

用于验证sysdba权限的二进制文件

 

1.1.6 警告文件

记录oracle系统运行信息和错误信息的文本文件

1.2 逻辑存储结构

面向用户，oracle数据库使用表空间，段，区间（盘区），数据块等逻辑结构管理对象空间。oracle数据库的逻辑存储空间是由一个或多个表空间组成。

  • 表空间，数据文件，段，区之间的关系

  1. 一个oracle有一个或多个逻辑存储单位组成，这些单位为表空间，表空间负责保存数据库所有的数据，是oracle数据库的最大逻辑容器

  2. 当用户创建对象时，每一个对象分配一个段，它包括一个或多个扩展区(如果对象被分区，如表分区等，每一个分区可分配一个段)每一个区里又包含一系列连续的数据块

  3. oracle数据库中包含有许多表空间，表空间中只有一个或多个数据文件，单一个数据文件只能属于一个表空间，数据文件是oracle所运行的操作系统上的文件

  4. 一个数据库的数据存储在构成数据库中表空间的数据文件上，数据库容量在物理上有数据文件大小与数量决定，在逻辑上由表空间大小与数量决定的

　　

1.2.1表空间

  1. 永久表空间（permanent）：永久表空间中存储数据库中需要永久化存储的对象，如二维表，视图，存储过程，索引。永久表空间按存储内容方式可分为

    • 系统表空间system：system表空间包含着整个数据库的所有数据字典表

    • 辅助表空间sysaux：是system表空间的辅助，用来存放各种oracle产品和特征的信息

    • 用户表空间users：或用户自定义表空间

  2. 临时表空间(temporary)：用来存放各种临时数据（排序数据，索引数据等），不能包含任何持久模式对象。当临时数据不需要时，oracle后台进程smon也会负责将临时段收回。出了问题也不需要恢复，备份，因此也不需要记录日志

  3. 回滚表空间（undo）：存储撤销信息，不可存放表和索引等需要持久保存的数据对象

1.2.2 段

由一个或多个区组成的逻辑存储单元，每个数据库对象派一个段来存储数据，一个段只属于一个表空间，可以覆盖多个数据文件。段的类型有：数据段，临时段，回滚段，索引段。

 

1.2.3 区

由一系列的数据块组成，当段生成时，首先指派盘区。

 

1.2.4 数据块

是oracle存储体系中在数据文件上执行I/O操作最小的单元，大小在数据库生成时被指定且不可更改

2. 内存结构

是oracle体系结构中最为重要的部分之一，也是影响数据库性能的主要因素。多个用户连接数据库时，服务器必须有足够的内存支持，否则有的用户可能连接不上服务器，或查询速度明显下降

 

2.1 系统全局区SGA

包含一个数据库例程的数据或控制信息，当多个用户同时连接同一个实例时，SGA区数据提供多个用户共享，所以SGA区又称共享全局区。组成部分：数据高速缓存区，共享池，重做日志缓存区，java池，大型池，空池

  1. 存放一个数据库例程的数据或控制信息

  2. 是一组共享数据（共享全局区），所有用户共享进程

  3. 随着数据库实例的启动而被自动分配，数据库实例的关闭而被回收

 

2.2 程序全局区PGA

  1. 为每一个与oracle数据库连接的用户保留的内存区，存储单个进程的数据和控制信息

  2. 是oracle服务进程的私有内存区，不能共享，且只属于某个服务进程

  3. 当oracle创建一个服务器进程是（用户进程连接到数据库并创建一个会话时）要为该服务进程分配一个内存区（PGA），保存每个与oracle数据库连接的用户进程所需要的信息

  4. 随着进程的创建而被分配，进程的终止而被收回

3 进程结构

3.1 前台进程（服务器进程）

用于处理连接到该例程的用户进程的请求，是客户端到服务端的代表。

任务：

  1. 解析并执行用户提交的SQL语句，和PL/SQL程序

  2. 在SGA的数据库高速缓存区中搜索用户进程所要访问的数据

  3. 将用户改变数据库的操作信息写入到重做日志缓存区

  4. 将查询或执行后的结果返回用户进程

3.2 后台进程

后台进程为所有用户异步完成各种任务

4. 数据字典

4.1 静态数据字典

主要有表和视图组成，数据字典中的表是不能直接访问的要通过访问数据字典中的视图

  1. user_*：存储了关于当前用户所拥有的对象

  2. all_*：存储了当前用户能够访问的对象信息

  3. dba_*：存储了数据库中所有对象的信息

4.2 动态性能视图

oracle包含了一些潜在的由系统管理员维护的表和视图，由于数据库运行时，他们会不断更新。在操作过程中，oracle维护了一种“虚拟”表的集合，记录当前的数据库的活动，称为动态性能视图。（以v$开头的视图）

三. 数据库管理

1. 数据库的启动

2. 数据库的关闭

 

四. 表空间的管理

 

1. 创建表空间

语法格式

CREATE[SMALLFILE|BIGFILE][PERMANENT|TEMPORARY|UNDO]TABLESPACE 表名
[DATAFILE|TEMPFILE] '路径'//eg:'%oracle_home%\database\t.dbf'
SIZE integer [K|M]
[REUSE]--文件是否被重用
[AUTOEXTEND[OFF|ON[NEXT integer[K|M]]]]
[MAXSIZE[UNLIMITED|integer[K|M]]]
[ONLINE|OFFLINE]
[LOGGING|NOLOGGING]--是否在重做日志文件下保存记录
SEGMENT SPACE MANAGEMENT[AUTO|MANUAL]--段空间管理模式
[EXTENT MANAGEMENT [DICTIONARY|LOCAL[AUTOALLOCATE |UNIFORM[SIZE integer [K|M]]]]]--使用数据字典表空间管理方式还是本地管理表空间方式
[DEFAULT STORAGE storage_clause]

eg：.通过本地管理方式创建一个表空间t2，数据文件是t2_1.dbf和t2_2.dbf，大小都是2M，指定扩展区间大小为128K。

CREATE TABLESPACE  t2
DATAFILE '%ORACLE_HOME%\database\t2_1.dbf'
SIZE 2M,
'%ORACLE_HOME%\database\t2_2.dbf'
SIZE 2M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

eg：通过本地管理方式创建一个表空间t3，数据文件是t3_1.dbf，大小2M，由系统自动分配区间，采用自动段管理方式。

CREATE TABLESPACE t3
DATAFILE '%ORACLE_HOME%\database\t3_1.dbf'
SIZE 2M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

2. 修改表空间

语法格式:

ALTER TABLESPACE 表名
[ADD|DROP][DATAFILE|TEMPFILE'路径'[SIZE integer [K|M]]]
[REUSE]
[AUTOEXTEND[OFF|ON [NEXT[K|M]]]]
[MAXSIZE[UNLIMITED|integer [K|M]]]
[RENAME DATAFILE '路径',...n TO'路径',...n]--重命名
[DEFAULT STORAGE<存储参数>]
[ONLINE|OFFLINE[NORMAL|TEMPORARY|IMMEDIATE]]
[LOGGING|NOLOGGING]
[REDY ONLY|WAITE]//读写性

eg:修改表空间t1，将数据文件是t1_1.dbf大小修改为3M。

ALTER DATABASE
DATAFILE'%ORACLE_HOME%\database\t1_1.dbf'
RESIZE 3M ;

3. 删除表空间

删除表空间时，仅仅是咋控制文件和数据字典中删除与表空间和数据文件有关的信息。默认情况下不会再操作系统中删除相应的数据文件，因此在删除表空间后要手动删除在操作系统中对应的数据文件

语法格式

DROP TABLESPACE 表名
[INCLUDING CONTENTS[AND DATAFILE]]--表空间非空时应使用
[CASCADE CONSTRAINTS]

eg:删除表空间t2，同时删除该表空间的数据和数据文件。

DROP TABLESPACE t2 INCLUDING CONTENTS AND DATAFILES;

五. PL/SQL程序设计

PL/SQL是一种块结构语言，即构成一个PL/SQL程序的基本单位是程序块。程序块由过程，函数，无名块三部分组成。PL/SQL块由声明部分，程序代码和异常处理等组成

[DECLARE]  --声明变量，可选
BEGIN--执行部分，程序体开始
 [EXCEPTION]--异常处理,可选
 END;--程序结束
 /

1. 声明部分

1.1 变量

  • 变量定义

<变量名> <数据类型> [(宽度)：=<初始值>];

  • 变量命名规则

    1. 变量名以字母开头，不区分大小写

    2. 变量名由字母，数字, $ , # , _ 和特殊字符组成

    3. 变量最多包含30个字符

    4. 变量名中不能有空格

    5. 尽可能避免缩写，用一些有意义的单词命名

    6. 不能用保留字命名

     

1.2 数据类型

  • %TYPE：声明一个和指定列一样的数据类型，紧跟在列名后

  • RECORD：记录类型，可以存储由多个列值组成的一行数据

  • %ROWTYPE：根据数据表中行的结构定义一种特殊的类型，用来存储从数据表中检索到的一行数据

 

2. 游标

游标是从结果集中逐条提取记录的一种机制，存放SELECT查询语句执行的结果集。分为显性游标和隐性游标

使用游标的步骤：

  • 声明游标

  • 打开游标

OPEN 游标名[(参数)]

  • 读取游标数据

FETCH 游标名 INTO 变量列表|记录型变量

  • 关闭游标

CLOSE 游标名

 

2.1 声明游标

DECLARE
CURSOR 游标名[(参数)]
IS
SELECT语句
[FOR UPDATE]

 

2.2 常用游标属性

  • %ISOPEN：游标是否打开，TRUE或FALSE

  • %FOUND：游标所在行是否有效，是否找得到，TRUE或FALSE

  • %NOTFOUND：游标所在乎是否无效，TRUE或FALSE

  • %ROWCOUNT：返回当前位置为止游标读取的记录行数

 

2.3 游标的使用

eg1:声明一个带参数的游标查询指定部门号（deptno）的雇员的员工号、姓名。打开游标，显示游标的数据，要求使用WHILE……Loop……END LOOP循环读取数据。

DECLARE
  cursor cur_emp is select * from emp where deptno=20;
  v_empRow emp%ROWTYPE;
BEGIN
  IF cur_emp%ISOPEN THEN
    NULL;
  ELSE
    OPEN cur_emp;
  END IF;
  FETCH cur_emp INTO v_empRow;
  WHILE cur_emp%FOUND LOOP
    DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT||'员工号'||v_empRow.empno||'  姓名'||v_empRow.ename);
    FETCH cur_emp INTO v_empRow;
  END LOOP;
  CLOSE cur_emp;
END;
/

eg2:修改第1题代码实现一样的功能，要求使用FOR……Loop……END LOOP循环读取数据。

DECLARE
  cursor cur_emp RETURN emp%ROWTYPE is select * from emp where deptno=20;
  v_empRow emp%ROWTYPE;
BEGIN
 FOR v_empRow IN cur_emp LOOP
  EXIT WHEN cur_emp%NOTFOUND;
  DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT||'员工号'||v_empRow.empno||'  姓名'||v_empRow.ename);
  END LOOP;
END;
/

eg3:声明一个游标查询指定员工号的员户信息，通过游标定位将员工号empno为7839的薪水sal加1000。可使用FOR循环或者WHILE循环实现。

DECLARE
  cursor cur_emp RETURN emp%ROWTYPE is select * from emp where empno=7839 for update;
BEGIN
 FOR v_row IN cur_emp LOOP
    UPDATE emp set sal=sal+1000 WHERE CURRENT OF cur_emp;
  END LOOP;
END;
/

eg4:创建一个带参数游标查询指定job的员工的员工号，姓名，使用标量变量接收游标数据，显示job为MANAGER的所有员工的员工号和姓名

DECLARE
  cursor cur_job(djob VARCHAR2) is select * from emp where job=djob;
  v_empRow emp%ROWTYPE;
BEGIN
  IF cur_job%ISOPEN THEN
    NULL;
  ELSE
    OPEN cur_job('MANAGER');
  END IF;
  FETCH cur_job INTO v_empRow;
  WHILE cur_job%FOUND LOOP
    DBMS_OUTPUT.put_line(cur_job%ROWCOUNT||'员工号'||v_empRow.empno||'  姓名'||v_empRow.ename);
    FETCH cur_job INTO v_empRow;
  END LOOP;
  CLOSE cur_job;
END;
/

eg5:声明一个游标查询指定deptno的部门号、部门名和地点，通过游标定位删除部门号为50的部门信息。

DECLARE
    cursor cur_dept return dept%ROWTYPE is select * from dept where deptno=50
    FOR UPDATE;
BEGIN
    FOR v_row IN cur_dept LOOP
    DELETE FROM dept  WHERE CURRENT OF cur_dept ;
    END LOOP;
END;
/

 

3. 存储过程

是能完成一定处理/计算功能并存储在数据字典中的程序，对他们的使用可以通过调用函数存储过程名或函数名的方式来实现

参数说明：

  • IN：输入参数，默认参数类型

  • OUT：输出参数，在过程中被赋值，并传递到过程体外

  • IN OUT：具备IN和OUT参数的特征

 

3.1 （存储）过程

3.1.1 创建过程

CREATE [OR REPLACE] PROCEDURE 过程名
[参数 [IN|OUT|IN OUT] DATATYPE]
IS|AS
[局部声明]
BEGIN
<过程体>
EXCEPTION
[异常处理程序]
END;

eg1:创建一个带输入和输出参数的存储过程，根据输入参数课程号和学号，查询并输出选修学生姓名和成绩。执行存储过程，其中输入参数课程号为1、学号为95001，显示结果格式为：“姓名：，课程号：，成绩：**”。

CREATE OR REPLACE PROCEDURE GET_PROC
    (v_sno IN student.sno%TYPE, v_cno IN OUT course.cno%TYPE,
    v_sname OUT student.sname%TYPE, v_grade OUT sc.grade%TYPE)
AS
BEGIN
    SELECT student.sname,course.cno,grade INTO v_sname,v_cno,v_grade
    FROM student,course,sc
    WHERE   student.sno=v_sno AND course.cno=v_cno AND
            student.sno=sc.sno AND sc.cno=course.cno ;
END;

eg2:定义一个带输入(in)参数的过程可根据雇员号（empno）查询雇员的员工号、姓名。执行定义的存储过程实现根据指定雇员号（empno）查询雇员信息。

CREATE OR REPLACE PROCEDURE get_proc(v_empno  IN emp.empno%TYPE)
IS
    v_ename emp.ename%TYPE;
BEGIN
    SELECT ename into v_ename FROM emp WHERE empno=v_empno ;
    DBMS_OUTPUT.put_line('员工号：'||v_empno||'员工姓名：'||v_ename);
END;
/

 

3.3.2 调用过程

EXEC 过程名[参数]；

eg1:调用上面的eg1

DECLARE 
    v_sno  student.sno%TYPE;
    v_cno  course.cno%TYPE;
    v_sname  student.sname%TYPE;
    v_grade  sc.grade%TYPE;
BEGIN
    v_sno:='95001';
    v_cno:=1;
    GET_PROC(v_sno,v_cno,v_sname,v_grade);
    dbms_output.put_line('姓名:'||v_sname||',课程号：'||v_cno||',成绩：'||v_grade);
END;

eg2:调用上面的eg2,通过数据字典user_procedures查询当前用户的存储过程。

EXEC get_proc(7369);
select * from user_procedures;

 

3.4 函数

3.4.1 创建函数

CREATE [OR REPLACE] FUNCTION 函数名
[参数 [IN|OUT|IN OUT] DATATYPE]
[RETURN DATATYPE]
IS|AS
[局部声明]
BEGIN
<过程体>
EXCEPTION
[异常处理程序]
RETURN DATATYPE;
END;

eg1:创建一个函数，根据输入参数课程号的值，查询返回课程的名称。调用函数，显示课程号为2的课程名

create or replace function get_fun(v_cno  in course.cno%type)
return course.cname%type
as
    v_cname course.cname%type;
begin
    select cname into v_cname  from course where cno=v_cno ;
    return v_cname;
end;
/

 

3.4.2 调用函数

eg1:调用上面的eg1

select get_fun(2) from dual;

 

3.3 包

是一组相关过程，函数，变量，常量，游标等PL/SQL程序设计元素的组合。

3.3.1 创建包头

CREATE OR REPLACE PACKAGE 包名
IS|AS 
[声明部分]
END;

  • 包头部分应只包含全局元素，元素要尽可能少，只含外部访问接口所需的元素

  • 过程和函数的声明只包括接口说明，不包括具体实现

  • 包头内变量如果没有赋初始值，则默认NULL

 

3.3.2 创建包体

CREATE [OR REPLACE]PACKAGE BODY 包名
IS|AS
[声明部分]
[过程体]
[函数体]
[初始化部分]
END;

  • 包体中函数和过程的接口必须与包头中的说明一样

  • 包内的过程和函数不要CREATE OR REPLACE 语句

  • 在包内声明的数据类型，变量，常量都是私有的，只能在包内使用

  • 在包内声明常量，变量，异常，游标等不使用DECLARE

  • 包体和包头分两次执行

eg:创建一个包，包含一个存储过程和一个函数，存储过程实现根据指定的课程号，从sc表中统计该课程的人数，然后插入到临时表StuCnt中；函数实现根据指定的课程号，返回该课程的平均成绩。调用包中的存储过程，指定课程号为3，调用包中的函数，指定课程号为1，输出平均成绩。

CREATE OR REPLACE PACKAGE  pkg
AS
    PROCEDURE insert_proc(proc_cno IN sc.cno%TYPE);
    FUNCTION getGrade_fun(fun_cno IN sc.cno%TYPE)
    RETURN sc.grade%TYPE;
END;
​
CREATE OR REPLACE PACKAGE BODY pkg
AS
PROCEDURE insert_proc(proc_cno IN sc.cno%TYPE)
AS
v_cno sc.cno%TYPE;
v_scnt INT;
BEGIN
INSERT INTO stucnt(cno,scnt)
    SELECT cno,COUNT(cno)
    FROM sc WHERE cno=proc_cno GROUP BY cno;
    
END ;
FUNCTION getGrade_fun(fun_cno IN sc.cno%TYPE)
RETURN sc.grade%TYPE
AS
    avegrade sc.grade%TYPE;
BEGIN
    SELECT AVG(grade) INTO avegrade
    FROM sc WHERE cno=fun_cno;
    RETURN avegrade;
END ;
END ;
DECLARE 
    proc_cno  sc.cno%TYPE;
    fun_cno  sc.cno%TYPE;
    avegrade sc.grade%TYPE;
BEGIN 
    proc_cno:=3;
    fun_cno:=1;
    PKG.INSERT_PROC(proc_cno);
    avegrade:=PKG.GETGRADE_FUN(fun_cno);
    dbms_output.put_line('课程号为1的课程,平均成绩为：'||avegrade);
END;

 

4. 触发器

触发器是一种数据对象，一种特殊的存储过程。是由一个事件来启动运行的，不能接收参数。oracle不能对SYS用户拥有的对象创建触发器。（最好不要使用触发器）

 

4.1 触发器类型

4.1.1 DML触发器（表级触发器）

  • 执行INSERT,UPDATE,DELETE等语句时触发

  • DML语句对每一行数据进行操作时都会执行触发器

  • 无论DML语句影响多少行，其所引起的触发器仅执行一次

  • 触发器中不能使用数据库事务控制语句如：COMMIT,ROLLBACK,SAVEPOINT

  • 由触发器所调用的过程和函数也不能使用数据库事务控制语句

  • 触发器语句中不能使用LONG,LONG RAW类型

4.1.2 DDL触发器（语句级触发器）

执行CREATE,ALTER,DROP等语句时触发

4.1.3 替代触发器

替代触发器代替数据库视图上DML操作，由于oracle不能直接对由两个以上的表建立的视图进行操作，所以替代触发器是专门为进行视图操作的一种处理方式。

4.1.4 系统触发器

执行oracle数据库系统时间时触发如：STARTUP,SHUTDOWN

4.2 创建触发器

语句格式：

CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE|AFTER|INSTEAD OF}触发事件
{DELETE|INSERT|UPDATE[OF column1，...]}
ON 表名|视图名
{REFERENCING {OLD[AS]old | NEW[AS]new}}
[FOR EACH ROW]--指定操作的触发器为操作修改的每一行都调用一次
声明部分
BEGIN
PL/SQL程序体
END[触发器名];

eg1：表级触发器：当学生表的学号、姓名字段发生修改时，提示不能修改这两个字段。编写触发器，实现以上功能，并验证结果是否正确。

CREATE OR REPLACE TRIGGER forbid_stud_trigger
BEFORE UPDATE
ON 学生表
WHEN(NEW.学号!=OLD.学号 OR NEW.姓名!=OLD.姓名)
         BEGIN
         RAISE_APPLICATION_ERROR(-20008,'不允许修改学号和姓名!');
END;

eg2:行级触发器：在选课表中，当修改成绩表中某一门课的成绩，让所有不及格的记录成绩都+5分。如果修改前成绩<60分,修改后成绩>=60分，则在学生表中对应学生表的累计学分要+2，否则不要修改累计学分。编写触发器，实现以上功能，并验证结果是否正确。

CREATE OR REPLACE TRIGGER class_trigger
AFTER INSERT
ON 选课表
FOR EACH ROW
WHEN(new.成绩!=old.成绩)
BEGIN   
    UPDATE 选课表 SET 成绩=成绩+5
    WHERE 成绩<60;
    UPDATE 学生表 SET 累计学分=累计学分+2
    WHERE :old.成绩<60 AND :new.成绩>=60;
END;
/

eg3:替代触发器：修改视图v_Stugrd，把学号为'S060202'且开课号为'010101'的记录累计学分+2，成绩改为80。编写触发器，实现以上功能，并验证结果是否正确。

CREATE OR REPLACE TRIGGER tri_view
INSTEAD OF UPDATE
ON v_Stugrd
FOR EACH ROW
BEGIN
    UPDATE 学生表 SET 累计学分=:new.累计学分
    WHERE 学号=:new.学号;
    UPDATE 选课表 SET 成绩=:new.成绩
WHERE 学号=:new.学号 AND 开课号=:new.开课号;
END;
/

eg4:DDL触发器：设计触发器，当scott用户删除数据库对象时发出警告，并验证结果

CREATE OR REPLACE TRIGGER tri_drop
BEFORE DROP
ON database
BEGIN 
    IF ora_sysevent='DROP' THEN
        RAISE_APPLICATION_ERROR(-20000,'警告！不允许删除！');
    END IF;
END;
/

eg5：创建一个行触发器T1，在往学生选修表SC中插入记录前，如果学生表中没有该学号或者课程表中没有该课程号，则显示提示信息“该学生不存在”或“该课程不存在”,终止插入语句

CREATE OR REPLACE TRIGGER T1
BEFORE INSERT
ON SC
FOR EACH ROW
DECLARE
    v_sno integer;
    v_cno integer;
BEGIN
    select count(*) into v_sno from Student where Sno=:new.sno;
    if v_sno=0 then
    raise_application_error(-20000, '该学生不存在，不允许插入该记录');
    End if;
    select count(*) into v_cno from SC where cno=:new.cno;
    if v_cno=0 then
    raise_application_error(-20000, '该课程不存在，不允许插入该记录');
    End if;
END;

eg6：在users表空间创建日志表sc_log，包含ope_action（动作类型）和ope_date(操作日期)两个字段。创建一个语句触发器T2，当对学生选修表中的记录进行增删改操作时，在日志表sc_log中记录相应的操作以及时间，并显示信息“插入成功”或“删除成功”或“修改成功”。

CREATE TABLE sc_log
(ope_action varchar2(20),
 ope_data DATE);
​
CREATE OR REPLACE TRIGGER T2
AFTER INSERT OR UPDATE OR DELETE
ON SC
DECLARE
v_action VARCHAR2(20);
BEGIN
IF INSERTING THEN v_action:='插入成功';
ELSIF UPDATING THEN v_action:='修改成功';
ELSIF DELETING THEN v_action:='删除成功';
ELSE DBMS_OUTPUT.put_line('..');
END IF;
INSERT INTO sc_log VALUES(v_action,SYSDATE);
END;

 

4.3 启动，禁用触发器

ALTER TRIGGER 触发器名 DISABLE|ENABLE--禁用某个触发器
ALTER TRIGGER 表名 DISABLE|ENABLE ALL TRIGGERS--禁用表上的所有触发器

4.4 删除触发器

DROP TRIGGER 触发器名

 

六. 安全管理

1. 用户管理

1.1 创建用户

只有DBA有权限创建，修改，删除用户

  1. CREATE USER语句

    CREATE USER 用户名
    IDENTIFIED BY 口令
    DEFAULT TABLESPACE --默认永久表空间(如没有指定，默认USERS)
    TEMPORARY TABLESPACE --临时表空间(如没有指定，默认TEMP)
    [QUOTA 正整数 K|M|UNLIMITED ON 表空间名,QUOTA...]
    [PASSWORD EXPIRE]
    [ACCOUNT LOCK|UNLOCK]
    [PROFILES 概要文件名|DEFAULT];

    eg:创建一个用户myuser,密码为myuser,默认表空间USERS，临时表空间TEMP,在默认表空间上为用户分配2M空间

    CREATE USER myuser
    IDENTIFIED BY myuser
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    QUOTA 2M ON USERS;

1.2 修改用户

ALTER USER

  1. 修改密码

    ALTER USER myuser
    IDENTIFIED BY 123456;

  2. 设置密码过期，在下一次登录时必须修改密码

    ALTER USER myuser
    PASSWORD EXPIRE;

  3. 锁定/解锁用户

    ALTER USER myuser
    ACCOUNT LOCK|UNLOCK;

  4. 为用户分配空间配额,在表空间分配的空间修改为3M

    ALTER USER myuser
    QUOTA 3M ON USERS;

1.3 删除用户

删除用户很危险，如果该用户方案包含了对象，则使用CASCADE,删除用户同时删除方案中的所有对象，不能删除当前正在与oracle服务器相连的用户

DROP USER myuser;

 

2. 权限管理

2.1 系统权限

系统规定用户使用数据库的权限，对用户而言可以让用户执行特定的命令集

  1. sysdba：最高数据库权限，允许创建，修改，启动，关闭数据库

  2. sysoper：能够启动，关闭，修改，安装，备份，恢复数据库，能够创 造或修改spfile,比sysdba级别低一些

  3. select any dictionary：允许读取任意一个数据字典

  4. analyze any：允许对任意表，索引，聚集执行analyze语句

  5. grant any object privilege：允许将任意对象的权限授予其他对象

  6. grant any privilege ：允许给其他用户或角色分配任意系统权限

  7. grant any role：允许给其他用户或角色分配任意角色

  8. alter database：允许用户执行alter database语句的权限

  9. alter system：允许用户执行alter system语句的权限

  10. audit system：允许客户执行audit和noaudit语句来完成语句审计

  11. audit any：允许用户执行audit和noaudit语句来完成任意方案的对象审计

 

2.2 对象权限

用户对数据库对象（表，图等）的访问权限

  1. select：允许查询数据

  2. insert：允许插入数据

  3. update：允许修改数据

  4. delete：允许删除数据

 

2.3 权限授予与回收

  1. 授予用户权限的两种方法

  • 直接对用户授权

  • 先对角色授权，然后为用户指定角色进而实现对用户授权

  1. 授予系统权限

GRANT 系统权限名
TO 用户名|角色名
WITH ADMIN OPTION; --允许系统权限传递

  1. 系统权限收回

    REVOKE 权限
    FROM 用户名|角色名;

  2. 系统权限回收问题

    • 回收系统权限时不级联 ：如果使用WITH ADMIN OPTION为某个用户或角色授予系统权限，那么对于这个用户再转授予相同权限的所有用户来说，撤销该用户的系统权限并不会级联撤销转授予用户的相同权限。

    • 系统权限可以跨用户回收

    eg：A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响。A也可以直接回收C的权限

  3. 授予对象权限

    GRANT 对象权限
    ON 数据对象   //列名1,列名2...
    TO 用户名|角色名
    WITH GRANT OPTION;  --允许对象权限传递，无法用在角色上，只能用在用户上

  4. 对象权限回收

    REVOKE 权限
    ON 数据对象
    FROM 用户名|角色名;

  5. 对象权限回收问题

    回收对象权限时级联：如果使用WITH GRANT OPTION为某个用户授予对象权限，那么对于被这个用户再转授予相同权限的所有用户来说，撤销该用户的对象权限时会级联撤销转授用户的相同权限

3. 角色管理

  • DBA：拥有全部特权，系统的最高权限，只有DBA才可以创建数据库结构

  • RESOURCE：只可以创建实体，不可以创建数据库结构

  • CONNECT：只可以登入oracle，不可以创建实体，数据库结构

  • 对于普通用户和开发用户：授予connect，resource角色权限

  • 对于DBA用户：授予connect，resource，dba角色权限

3.1 创建角色

CREATE ROLE 角色名
[IDENTIFIED BY 验证口令]
[NOT IDENTIFIED]

3.2 为用户指定/撤销角色

GRANT 角色名 TO 用户名
[WITH ADMIN OPTION];
REVOKE 角色名 FROM 用户名;

3.3 修改角色

ALTER ROLE 角色名 NOT IDENTIFIED;--取消角色的密码验证
ALTER ROLE 角色名 IDENTIFIED MYROLEPWD;--将角色的验证方法改为口令标识

3.4 删除角色

DROP ROLE 角色名

3.5 查看角色

SELECT * FROM DBA_ROLES;--查看所有角色信息

 

 

 

 

 

 

杂

  • 发现自己是个憨憨，写了那么久才发现单行注释是用--，还一直用java的//，现在肯定记住了。（补充：但多行注释还是/**/）;

  • 字符串界定符是单引号‘ ’，而不是双引号“ ”

  • 终于要完结了，这是正式地第一篇，只是一个小小的笔记或者说是总结，还有很多地方都没有写到，在以后的学习中再陆续补上吧

  • 这个部分应该不会写关于查询的，因为。。。所以如果写可能会在SQL SERVER里面写吧。

  • 哇，看了看时间这篇我竟然查不到写了一个月，，，加油，后面还有很多个月

  • OEM的部分也没有写到，因为很少接触到

posted on 2020-05-31 20:38  bell_*  阅读(390)  评论(0)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 bell_*
Powered by .NET 8.0 on Kubernetes