Oracle

最新推荐文章于 2025-06-27 15:41:03 发布

原创最新推荐文章于 2025-06-27 15:41:03 发布 · 835 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

第1章数据库技术基础

一、创建用户

-- 1.登录Oracle数据库管理员 
sqlplus sys/suiyi as sysdb
-- 2.启动数据库实例
startup;
-- 3.查看当前用户
show user;
-- 3.1查看所有数据库的名称和创建日期（需要在管理员用户下）:
select name,created from v$database;
-- 4.解开被锁定的scott用户并修改密码，这一步需要管理员才可操作
alter user scott account unlock identified by tiger;
-- 5.连接到scott用户
connect scott/tiger;

-- 创建新用户，并赋予权限（需要在管理员用户下）：
-- 1.创建
create user 用户名 identified by 密码;
-- 2.给管理员权限
grant dba to 用户名;
-- 设置可查看长度：set linesize 120
-- 3.查看用户下的所有表
select table_name from user_tables;

二、登录scott用户

登录scott用户，提前准备两个文件：sqlplus.bat 和 scott.txt 文件，放在根目录

sqlplus.bat文件内容：

sc config oracleserviceorcl start= auto
sc config oracleoradb11g_home1tnslistener start= auto
net start oracleserviceorcl
lsnrctl start
sqlplus sys/Oracle11 as sysdba @c:\scott.txt

scott.txt文件内容：

startup;  -- 启动数据库实例
conn sys/a as sysdba;
-- 解开被锁定的scott用户并修改密码，这一步需要管理员才可操作
alter user scott identified by tiger account unlock; 
conn scott/tiger;
show user;

第3章表的管理

一、建表

create table student3(	       -- 注释
	sno varchar(12),
    sname varchar(11),
    cno char(3),
    sage number(3) default 25, -- 默认值
    rxrq date,
    constraint pkso primary key(sno,cno)   -- 联合主键，约束名pkso
);

1.子查询建表

create table 表名 as select * from 表名 where 条件;

二、查看表结构

desc 表名;  -- describe 表名;

三、删除表

drop table 表名 [cascade constraints];
-- casecade constraints 表示当要删除的表被其他表参照时，删除参照此表的约束条件

四、修改表

-- 1.增加列：增加的新列总是位于表的最后。对于有数据的表，新增列的值为NULL。
alter table 表名 add 列名 数据类型(长度) [默认值] 约束条件;
-- 示例：
alter table p add page number(3) default 25 not null;

-- 2.修改列（类型，长度）
alter table 表名 modify 列名 数据类型(长度) [默认值] 约束条件;

-- 3.修改列名
alter table 表名 rename column 原列名 to 新列名;
--示例：
alter table p rename column pno to pnno;

-- 4.删除列
alter table 表名 drop column 列名; -- 列名可以是多个，放在(列1,列2)中

五、添加数据

-- insert语句用于在指定的表中添加数据。格式
insert into 表名(列1，列2，列3.。。) values(值1，值2，值3.。。)

利用子查询进行多值插入，表示把一个子查询的结果插入到当前表中

insert into 表名1 select * from 表名2;
-- 两个表结构需要完全一致。

六、修改数据

-- 格式
update 表名 set 列名=值 where 条件;
-- 示例：
update p set page=page+1;

七、删除数据

delete from 表名 where 条件; -- 若没有条件，则删除表中所有数据

truncate table 表名; --清空表数据

第4章简单查询

-- 查询语句格式：
select 查询内容 from 表名
where 条件
group by 分组字段 having 分组条件
order by 排序字段 排序方式(asc|desc)

一、字符串连接符：||

select '名字是：'||sname from 表名;
-- 示例
select '名字：'||pname from p;

二、去重、通配符(模糊查找)

1.取消重复行，distinct(去重）

select distinct 列名 from 表名;
-- distinct取消后面所有列的重复

2.查询满足条件的元素，用where引出查询条件

select * from 表名 where 条件语句;
-- 例如：查询年龄在19岁以下的学生姓名及其年龄
select * from p where page>19;

like 通配符，

%：任意长度，

_：下划线，单个字符

select * from p where pnno like '001';
select * from p where pname like 'bo%';

null 空值：

select * from p where pname is NULL;
select * from p where pname is NOT NULL;

三、排序

select * from stu order by 列1 排序条件,列2 排序条件;
--示例：
select * from student1 order by sno desc,sage asc;

四、统计(集函数)

1.统计使用的集函数

计数：count(*) ，count([distinc|all]列名)

计算总和：sum([distinc|all]列名)

计算平均值：avg([distinc|all]列名)

求最大值：max([distinc|all]列名)

求最小值：min([distinc|all]列名)

distinct短语：在计算时要取消指定列中的重复值

all短语：不取消重复值，all为缺省值

2.分组

select * from 表名 group by 分组列;
--示例：查询选修了超过2门课程的学生学号。
select sno from sc group by sno having count(*)>2;

分组时注意的问题：凡是题目中有“每个”、“各个”，“每次”，“各组”等词的且查询的结果为：统计个数、求和、平均、最大、最小等用group by。

五、(并、交、差)运算

并：

-- 示例：
select cno from sc where sno='1001'
union  -- 默认去掉重复行， union all 保留重复行
select cno from sc where sno='1002';

交：

select cno from sc where sno='1001'
intersect
select cno from sc where sno='1002';

差：

select cno from sc where sno='1001'
minus
select cno from sc where sno='1002';

六、基于派生表的查询

1.派生表的含义，子查询如果出现在from子句中，此时子查询的结果为临时派生表。

select * from (select sno,avg(grade) pjf from sc group by sno) tmp; 
-- 这个子查询产生的表tmp就是临时派生表，pjf（平均分）

注意：子查询中如果有集函数、表达式等，在where中需要引用该列时需要制定别名。

-- 查询至少选修了1和2这两门课程的学生学号
select t1.sno from (select * from sc where cno='C1') t1,
(select * from sc where cno='C2') t2 where t1.sno=t2.sno;

第4章高级查询

一、自身连接查询

举例：查询每一门课的间接先修课（即先修课的先修课）

-- 给表起别名不要用as
select first.cno,second.cpno from course first,course second where first.cpno=second.cno;

1.带有ANY或ALL的子查询

如内层查询结果为多行值与any(some) 或ALL谓词配合使用

ANY：任意一个值
ALL：所有值

例如：查询其他系中比信息学院任意一个（其中某一个）学生年龄大的学生姓名和年龄

select sname,sage from student where sage > ANY(select sage from student where sdept='信息学院') and sdept <> '信息学院';

例：查询其他系中比信息学院所有学生年龄都大的学生姓名及年龄。

select sname,sage from student where sage > ALL(select sage from student where sdept='信息学院') and sdept <> '信息学院';

第5章数据库完整性

一、创建约束

1.primary key（主键约束）

-- 建表时设置主键
create table student1(
	sno varchar(12) primary key,
    sname varchar(8),
    sage number(3),
    ssex varchar(3),
    sdept varchar(20)
);

-- 建表时没有设主键
create table sc1(
    sno varchar(12),
    cno varchar(3),
    grade number(3)
);
-- 使用alter table添加主键，没有设置约束名。
alter table sc1 add primary key(sno,cno);

2.foreign key （外键约束）

建表时添加外键：

create table zy(
	zyh char(2) primary key,
    zym char(20)
);

create table xs1(
	xh char(10) primary key,
    xm char(8) not null,
    nl number(3),
    zyh char(2) references zy(zyh)
);

用alter table命令创建

alter table 外键表 add foreign key(外键字段) references 主表(主键);

用alter table命令创建级联删除：

alter table 外键表 add [constraint 约束名]  foreign key(外键字段) references 主表(主键) on delete cascade;

用alter table命令创建置空：

alter table 外键表 add constraint 约束名 foreign key(外键字段) references 主表(主键) on delete set null;

3.unique（取值唯一约束）

建表的同时指定唯一约束

create table student2(
    sno varchar(12),
    sname varchar(8) unique,
    sage number(3),
    ssex varchar(3),
    sdept varchar(20)
);

为course1表中cname字段添加一个唯一约束

alter table course1 add constraint 约束名 unique(cname);

4.not null （非空）

建表时添加非空举例：

create table student3(
	sno varchar(12),
    sname varchar(8) NOT NULL,
    sage number(3),
    ssex varchar(3),
    sdept varchar(20)
);

用alter table命令

alter table 表名 modify 字段名 [constraint 约束名] NOT NULL;

5.chek（检查约束）

建表时设置举例：

create table chk1(
    sno varchar(10),
    sname varchar(8),
    sage number(3) constraint age18_70 check(sage between 18 and 70)
);

用alter table命令

alter table 表名 add [constraint 约束名] check(约束条件)

二、查看约束

查看某表的所有约束：

select CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from USER_CONSTRAINTS where TABLE_NAME='用户表'; --注意：这个用户表名必须要大写！

查看列的约束：

SELECT CONSTRAINT_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='用户表';

三、删除约束

alter table 表名 drop constraint 约束名;

第6章索引与视图

6.1索引

一、建立索引

create [{unique|bitmap}] index 索引名 on 表名(列名1\[asc|desc][,列名2[asc|desc]\[,...]])

unique代表创建惟一索引，不指明则为创建非惟一索引。
bitmap代表创建位图索引，若不指定该参数，则创建B+树索引。
列名是创建索引的关键字列，可以是一列或多列。
默认升序：ASC，降序：DESC

根据student中的sname字段建立非唯一索引indsn

create index indsn on student(sname);

根据（sage,ssex）创建student表的索引sesaind

create index sesaind on student(sage,ssex);

二、查看索引的信息

-- 这里的表名要大写。
select index_name from USER_INDEXES where table_name='表名';

查看索引是否被引用，步骤：

（1）设置查询计划分析

analyze table 表名 compute statistics;

（2）打开计划分析

set autotrace on explain;

（3）执行查询select命令，查看索引是否被引用

-- 1.创建索引
create index indsn on student(sname);
-- 2.设置计划分析
analyze table student compute statistics;
-- 3.打开计划分析
set autotrace on explain;
-- 4.使用select查询
select * from student where sname='张三';

--关闭计划
set autotrace off explain;

三、删除索引

drop index 索引名;
-- 例：drop index indsn;

6.2视图

一、创建视图

create [or replace] [force|noforce] view 视图名 [(别名1[,别名2...])]
as 子查询
[with check option]
[with read only]

or replace表示替代已经存在的视图。

force表示不管基表是否存在，创建视图。

noforce表示只有基表存在时，才创建视图，是默认值。

别名是为子查询中选中的列新定义的名字，替代查询表中原有的列名。

子查询是一个用于定义视图的select查询语句，可以包含连接、分组及子查询。

with check option 表示进行视图插入或修改时必须满足子查询的约束条件。

with read only表示视图是只读的，不能进行插入、删除、修改等操作。

1.创建简单视图

例：在SCOTT下创建视图SV1，结果为所有学生的学号与姓名。（先要以sys登录，执行grant create view to scott;再以scott登录）

create view sv1 as select sno,sname from student;
create view sv2(学号,姓名) as select sno,sname from student;

2.创建复杂视图

复杂视图是从单个或多个表中导出数据，可包含连接、分组、字符或统计函数等。

例：创建每个学生的学号及平均成绩视图S_G

create view S_G(sno,gavg) as select sno,avg(grade) from sc group by sno;

一般情况下只有简单视图可以省略列名，其他类型的视图一般都不能省略，需要自己指定。

二、查询视图的信息

1.查询当前用户的视图及定义

select VIEW_NAME,TEXT from USER_VIEWS;

2.查询视图的列

DESCRIBE 视图名;

3.查询视图中数据

select 视图列名[,....] from 视图名;

三、删除视图

drop view 视图名;

第7章 PL/SQL 编程

一、基本块结构

SET   SERVEROUTPUT  ON

DECLARE 
--声明部分: 在此声明变量,类型及游标，以及局部的存储过程和函数
	变量名称  数据类型  [:=value | DEFAULT 值] ;
	a2 varchar(10);

-- 根据表中字段的数据类型定义变量的数据类型：变量名 表名.字段名%TYPE;
	ST1 STUDENT.SNAME %TYPE;
--变量ST1是根据表STUDENT的Sname字段定义的，两者的数据类型总是一致的
BEGIN
-- 执行部分:  过程及SQL 语句, 即程序的主要部分
-- 变量名:=值 或 PL/SQL表达式;
	a2:=&a2;  --从键盘接收数据
	SELECT 列名1，列名2... INTO 变量1，变量2... FROM 表名 WHERE 条件;
	DBMS_OUTPUT.PUT_LINE(字符串表达式);
	
EXCEPTION
 -- 执行异常部分: 错误处理，不需要时可省略此部分
	WHEN   NO_DATA_FOUND    THEN
        DBMS_OUTPUT.PUT_LINE('学号错误，没有找到相应学生！');
	WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('发生其他错误！');
END;  --程序结束标志
/

SET   SERVEROUTPUT  ON
DECLARE  
	a1    varchar(10);
BEGIN
	a1:=100;
	DBMS_OUTPUT.PUT_LINE(a1);
END;
/

二、数据类型

1、字符类型：varchar2

2、数字类型：NUMBER[(P, S)]，

3、日期类型：DATE

4、布尔类型：BOOLEAN

函数的使用：在PL/SQL中可以使用绝大部分Oracle函数，但是集函数(如COUNT()、SUM()、AVG( )、MIN( )、MAX( )等)只能出现在SQL语句中，不能在其他语句中使用。

IF 条件1 THEN
	语句序列1;
ELSIF 条件2 THEN
	语句序列2;
ELSE
	语句序列n;
END  IF;

三、if单分支结构

例：如果温度大于30℃，则显示“温度偏高”

SET SERVEROUTPUT ON
DECLARE
	V_temprature	NUMBER(5):=32;
	V_result     BOOLEAN:=false;
BEGIN
	V_result:= v_temprature >30;
	IF    V_result    THEN 
		DBMS_OUTPUT.PUT_LINE('温度'|| V_temprature ||'度,偏高');
	END IF; 
END;
/

四、if二分支

例：根据性别，显示尊称

SET SERVEROUTPUT ON
DECLARE
	v_sex	VARCHAR2(2);
	v_titil  VARCHAR2(10);
BEGIN
	v_sex:='男';
    IF   v_sex ='男'    THEN
    	v_titil:='先生';
    ELSE
        v_titil:='女士';
    END IF; 
    DBMS_OUTPUT.PUT_LINE(v_titil||'您好！');
END;
/

五、if多分支

例：根据雇员工资分级显示税金。

SET SERVEROUTPUT ON
DECLARE
	v_sal  NUMBER(5);
    v_tax  NUMBER(5,2);
BEGIN
    SELECT sal  INTO   v_sal
    FROM    emp
    WHERE empno=7788;
	
	IF v_sal >=3000 THEN 
    	V_tax:= v_sal*0.08;--税率8%
    ELSIF v_sal>=1500 THEN
        V_tax:= v_sal*0.06; --税率6%
    ELSE
        V_tax:= v_sal*0.04; --税率4%
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('应缴税金:'||V_tax);
END;
/

六、CASE结构

1、基本CASE结构

CASE 选择变量名
WHEN 表达式1   THEN 
	语句序列1
WHEN 表达式2   THEN
	语句序列2 
WHEN 表达式n THEN 
	语句序列n
[ ELSE
	语句序列n+1]
END CASE;

2、表达式结构CASE语句

赋值时，END后面不跟CASE子句。出现在赋值中时，最后加分号。

例：根据成绩显示相应的等级：成绩90以上优秀，成绩90-80之间良好，成绩80-70之间中，成绩70-60之间合格，成绩60以下不合格。

SET SERVEROUTPUT ON;
declare
	gd  CHAR(1);
	CJ  NUMBER(3);
	mark  CHAR(8);
BEGIN
	CJ:=94;
	IF CJ>=90            THEN  gd:='A';  END IF;
	IF CJ>=80 and cj<90  THEN  gd:='B';  END IF;
	IF CJ>=70 and cj<80  THEN  gd:='C';  END IF;
	IF CJ>=60 and cj<70  THEN  gd:='D';  END IF;
	IF cj<60             THEN  gd:='E';  END IF;

	Mark:=  CASE  gd
		WHEN  'A' THEN '优秀'
		WHEN  'B' THEN '良好'
		WHEN  'C' THEN '中'
		WHEN  'D' THEN '合格'
		WHEN  'E' THEN '不合格'
		END;
	DBMS_OUTPUT.PUT_LINE(mark);
END;
/

3、搜索CASE结构
直接判断条件表达式的值，根据条件表达式决定转向

SET SERVEROUTPUT ON;
declare
	CJ  NUMBER(3);
	mark  CHAR(8);
BEGIN
	CJ:=94; 
	CASE
		WHEN  CJ>=90             THEN mark:='优秀';
		WHEN  CJ>=80 and cj<90   THEN mark:='良好';
		WHEN  CJ>=70 and cj<80   THEN mark:='中';
		WHEN  CJ>=60 and cj<70   THEN mark:='合格';
		WHEN  cj<60              THEN mark:='不合格';
	END CASE;
	DBMS_OUTPUT.PUT_LINE(mark);
END;
/

4、CASE 在SELECT中的应用
统计各系的男女生人数

-- 这只是单纯的select语句！
select sdept,count(case WHEN  ssex='男'  THEN 1  
                   		else  NULL  end)  AS  男生数,
             count(case WHEN  ssex='女'  THEN 1
                   		else NULL  end)   AS  女生数
from  student
group  by sdept;

七、循环结构

游标循环

set  serveroutput  on;
begin
	for   i    in  (select sno,sname from student)  loop
		DBMS_OUTPUT.PUT_LINE('学号：'||i.sno||'，姓名：'||i.sname);
	end loop;
end;
/

循环嵌套，例：求1！+2！+…+10！的值。

SET SERVEROUTPUT ON;
declare
	sm1 number(8);
    sm  number(8);
BEGIN
    sm1:=1;
    sm:=0;
    for   i  in     1..10  loop  
		sm1:=1;
		
		for   j    in    1..i  loop
			sm1:=sm1*j;
		end loop;
		sm:=sm+sm1;
	
	end loop;
    DBMS_OUTPUT.PUT_LINE('1！+2！+...+10！的值：'||sm);
END;
/

第8章游标循环

set  serveroutput  on;
begin
	for   i    in  (select sno,sname from student)  loop
		DBMS_OUTPUT.PUT_LINE(i.sno||i.sname);
	end loop;
end;
/

第9章存储过程与函数

一、存储过程

1.不带参数存储过程

创建一个显示学生总人数的存储过程prc1

SET   SERVEROUTPUT  ON
create or replace procedure prc1
is
	stct number(3);
begin
	select count(*) into stct from student;
	DBMS_OUTPUT.PUT_LINE('学生的总人数是：'||STCT);
end;
/

--执行
EXECUTE PRC1;

创建一个显示所有学生学号姓名的存储过程PRC3

SET serveroutput on
CREATE OR REPLACE PROCEDURE PRC3
IS
	
BEGIN
	for i in (select sno,sname from student) loop
		DBMS_OUTPUT.PUT_LINE(i.sno||i.sname);
	end loop;
END;
/

-- 执行存储过程：
-- EXECUTE 存储过程名
EXECUTE PRC3;

2.带有参数的存储过程

CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型 DEFAULT 值)]

参数类型	说明
IN	定义一个输入参数变量，用于传递参数给存储过程
OUT	定义一个输出参数变量，用于从存储过程获取数据
IN OUT	定义一个输入、输出参数变量，兼有以上两者的功能

例：创建一个带有输入参数的存储过程PRC4，查询任意学生的平均成绩。

-- 用于在程序中显示输出打印
SET serveroutput on
-- 使用name传递参数
create or replace procedure prc4(name in varchar default '张三')
IS
	avgd number(3);
BEGIN
	select avg(grade)  into avgd
	from student,sc
	where student.sno=sc.sno and sname = name;
	DBMS_OUTPUT.PUT_LINE(name||'的平均成绩是：'||AVGD);
	
END;
/

EXEC PRC4('ok');

使用show error查看错误

创建一个带有两个输入参数的存储过程PRC5：查询任意系院，任意性别学生的平均成绩。

SET serveroutput on
create or replace procedure prc5(sdt in varchar,ssx varchar)
IS
	avgd number(3);
BeGIN
	select avg(grade) into avgd
	from student,sc
	where student.sno=sc.sno and sdept=sdt and ssex=ssx;
	DBMS_OUTPUT.PUT_LINE(sdt||ssx||'生的平均成绩是：'||AVGD);
END;
/
EXECUT PRC5('信息学院','男');

-- 自己指定传递的顺序：
exec prc5(ssx=>'男',sdt=>'信息学院');

3.执行若干个存储过程

通过匿名块调用

BEGIN
	-- 若干个存储过程名称
	prc1;
	
END;
/

4.重新编译存储过程

ALTER PROCEDURE 存储过程名 COMPILE

5.删除存储过程

DROP PROCEDURE 存储过程名

例：DROP PROCEDURE PRC1;

6.查看存储过程

查看存储过程详细内容：

select TEXT from user_source [where name=‘存储过程名’]

二、函数

1.创建函数

create [or replace] function 函数名 [(参数[IN]\[OUT][IN OUT] 数据类型...)]
RETURN 数据类型
{AS|IS}
	[声明部分]
BEGIN
	可执行部分
	RETURN(表达式)
[EXCEPTION 
	错误处理部分]
END [函数名];
/

创建一个函数f1：向函数传递一个学生姓名，返回该学生的平均成绩

create or replace function f1 (snm in varchar)
return number
is 
	agd number(3);
begin
	select avg(grade) into agd from student,sc
	where student.sno=sc.sno and sname=snm;
	return(agd);
end;
/

调用函数f1：

①PL/SQL程序块中调用

set serveroutput on
declare 
	zc number(3);
begin
	zc:=f1('张三');
	dbms_output.put_line('张三的平均成绩是：'||zc);
end;
/

②select中调用

-- dual是Oracle特有的一个不固定表
select f1('张三') from dual;

2.查看函数

select text from user_source [where name=‘函数名’];

3.删除函数

drop function 函数名

4.函数的重新编译

alter function 函数名 compile;

第10章触发器

一、触发事件

触发器的触发事件分可为3类：DML事件、DDL事件、数据库事件。

种类	关键字	含义
DML事件（3种）	INSERT	在表或视图种插入数据时触发
	update	修改表或视图种的数据时触发
	delete	删除表或视图中的数据时触发
DDL事件（3种）	create	创建新对象时触发
	alter	修改数据库或数据库对象时触发
	drop	删除对象时触发
数据库事件（5种）	startup	数据打开时触发
	shutdown	在使用normal或者immediate选项关闭数据库时触发
	logon	当用户连接到数据库并建立会话时触发
	logoff	当一个绘画从数据库中断开时触发
	servererror	发生服务器错误时触发

二、触发时间

触发的时间有BEFORE和AFTER两种，分别表示触发动作发生在DML语句执行之前和语句执行之后。

三、 DML触发器的创建

CREATE [OR REPLACE]     TRIGGER 触发器名
	{BEFORE|AFTER|INSTEAD OF} 触发事件1 [OR 触发事件2...]
	ON    表名
	[WHEN   触发条件]
	[FOR  EACH   ROW]

DECLARE
	声明部分
BEGIN
	主体部分
END;

例1：创建一个行级触发器TRG1，基于STUDENT表，触发时间为BEFORE,触发事件：INSERT、DELETE和UPDATE，功能：执行插入时显示“该操作为插入”；执行删除时显示：“该操作为删除”；执行修改时显示：“该操作为修改”。

create  or replace  trigger   trg1    
	before  delete or insert  or update    
	on  student 
	for each row
declare
	
begin
	if   inserting   then
		dbms_output.put_line('该操作为插入');
	elsif deleting then   
		dbms_output.put_line('该操作为删除');
	else
		dbms_output.put_line('该操作为修改');
	end  if;
end;
/
    
-- 执行如下操作查看触发器trg1的执行：
INSERT INTO STUDENT VALUES('1018','赵六18',19,'男','工学院',TO_DATE('2025-6-9', 'yyyy-mm-dd'),TO_DATE('2000/4/25', 'yyyy/mm/dd'));
UPDATE  STUDENT SET SAGE=SAGE+1;
DELETE   FROM STUDENT WHERE  SNO='003';

例3：创建一个行级触发器trg3，基于SC表，触发时间为BEFORE,触发事件：UPDADTE（对成绩（grade）的修改），当修改成绩<60时的成绩才进行触发，功能：当修改成绩<60的学生成绩时，把修改的信息存入成绩修改日志表sc_log(该表中记录了修改的用户，修改前的成绩和修改后的成绩，修改的日期)

-- 首先创建一个表SC_LOG
CREATE TABLE SC_LOG( 
    ID       NUMBER(3)  NOT NULL,
    WHO      VARCHAR(8) ,
    MESSAGE  VARCHAR(60),
    RQ       DATE);

--创建触发器TRG3
create  or replace  trigger  trg3  before  update  of grade
	on  sc      for each row
	when (old.grade<60 )
declare
	gno number(10);
begin
	select     count(*)  into    gno     from sc_log;
	insert  into  sc_log  values(gno+1,USER,'学生'||:new.sno||'课程'
    ||:new.cno||'：原成绩'||:old.grade||':新成绩'||:new.grade,SYSDATE);   
end;
/

--执行修改命令：
update  sc set grade=grade+10  where   sno='1005'  and cno='C1';
--查询日志表查看信息：
SELECT  *   FROM   SC_LOG;

例4：创建一个行级触发器trg4，基于SC表，触发时间为BEFORE,触发事件：UPDADTE，功能：不得修改成绩<60时的成绩，当修改成绩<60的学生成绩时，取消该操作，并显示“禁止修改不及格的成绩，操作取消”

create  or replace  trigger trg4   before    update   OF    GRADE  
     on  sc   for each row
begin
	if (:old.grade<60 ) then
	raise_application_error(-20001,'禁止修改不及格的成绩,操作取消');
	end if;
end;
/

-- 执行如下命令：
UPDATE SC  SET GRADE=GRADE+10 WHERE SNO='1006' AND CNO='C1';
UPDATE SC  SET GRADE=GRADE+10 WHERE SNO='1008' AND CNO='C2';

例5：创建一个级联修改触发器trg5，当修改STUDENT中的学号时，SC表的学号也自动修改

create  or replace  trigger trg5   after  update  of sno
	on  student    for each row
begin
   update sc set sc.sno=:new.sno where sc.sno=:old.sno;
end;
/

-- 执行如下命令：
UPDATE student SET SNO='1003'  where SNO='1002';
SELECT * FROM   SC;

四、查看触发器

查看某表上的触发器：

-- select * from all_triggers WHERE table_name='表名'; 

-- 名称大写！
select text from user_source [where name='触发器名'];

查看某触发器详细信息

select text from all_source where type='TRIGGER' AND name='触发器名';

查看某用户下的所有的触发器

SELECT * FROM USER_SOURCE WHERE TYPE='TRIGGER';

五、删除触发器

DROP TRIGGER 触发器名

第11章用户的管理

一、用户创建

1、使用SQL命令创建

CREATE USER 用户名 INDENTIFIED BY 口令
[DEFAULT TABLESPACE 表空间名]
[TEMPORARY TABLESPACE 表空间名]
[QUOTA {正整数[K|M] |UNLIMITED } ON表空间名…]
[PASSWORD EXPIRE]
[ACC0UNT {LOCKL|UNLOCK}]
[PR0FILE 环境文件名|DEFAULT];

各子句含义：
IDENTIFIED BY子句：可为用户设置口令
DEFAULT TABLESPACE子句：可为用户指定默认表空间，如没有指定则为SYSTEM作为默认表空间。
TMPORARY TABLESPACE子句：为用户指定临时表空间，如没有指定则TEMP为临时表空间
QUOTA n：如果指定了默认表空间之后，一般需使用QUOTA子句来为用户在默认表空间中分配空间配额。
PASSWORD EXPIRE子句：设置用户口令的初始状态为过期。用户下次登录后，口令立即失效，必须为其设置新口令。
ACCOUNT LOCK子句：设置用户账户的初始状态为锁定，默认为ACCOUNT UNLOCK。

PROFILE子句：为用户指定一个概要文件。如果没有为用户显式地指定概要文件，则Oracle将自动为其指定DEFAULT概要文件。

（2）举例
只有管理员（或具有创建用户权限的用户）才能创建新用户，要先以管理员用户连接到数据库，才能创建用户

CONNECT   SYS/Oracle11   as   sysdba;
CREATE    USER     USER1  IDENTIFIED   by  a1234;

例2：密码失效
CREATE USER USER2 IDENTIFIED by a1234 PASSWORD EXPIRE
执行：CONNECT USER2/a1234

例3：用户锁定
CREATE USER USER3 IDENTIFIED by a1234 ACCOUNT LOCK
执行：CONNECT USER2/a1234

二、修改用户

1、使用命令修改
命令：ALTER USER 用户名……
例1:修改用户USER1的密码
ALTER USER USER1 IDENTIFIED BY oracle
执行：CONNECT USER1/a1234

例2:修改用户USER1的密码过期
ALTER USER USER1 PASSWORD EXPIRE;
执行：CONNECT USER1/oracle
例3：解除USER3的账户锁定
ALTER USER USER3 account UNLOCK
执行：CONNECT USER3/a1234

三、删除用户

1、命令
DROP USER 用户名 [CASCADE]
执行该语句的用户必须具有DROP USER系统权限
如果要删除的用户模式中包含模式对象，则必须在DROP USER子句中指定CASCADE关键字，否则Oracle将返回错误信息。
如果用户当前正连接到数据库，则不能删除这个用户
查看有哪些用户：SELECT USERNAME FROM DBA_USERS
~~drop USER USER3~~

四、用户权限

1、权限的含义
权限是用户对数据库所能执行的行为
如果没有对新户授予一定的权限，则用户不能执行任何操作
因此首先需要给用户授予基本的权限，这样用户才能够登录数据库，进而在所属方案中进行创建、删除、修改数据库对象等操作
如果不允许进行相应操作，可以通过回收其权限来达到对数据库相应的安全控制。

2、权限分类：
系统权限：
系统规定用户使用数据库的权限。（系统权限是对用户而言)
实体权限（或对象权限）：
某种用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）

3、常用的系统权限
DBA:
拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:
拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:
拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

其他的权限
CREATE SESSION：允许用户登录
CREATE USER : 创建用户
权限说明
对于普通用户：
授予connect, resource权限。
对于DBA管理用户：
授予connect，resource, dba权限。

4、系统权限授权
命令：
GRANT SYSTEM_PRIV[,SYSTEM_PRIV,…]
TO {PUBLIC|ROLE|USER}[,PUBLIC|ROLE|USER}]…
[WITH ADMIN OPTION]

各字句的含义：
SYSTEM_PRIV：表示要授予的系统权限的名称，该选项允许为用户同时授予多个系统权限，之间用逗号隔开。
USER：表示获得该系统权限的用户名称，该选项允许同时为多个用户授予相同的权限，之间用逗号隔开。
ROLE：表示被授予的角色。
PUBLIC:表示对系统中所用用户授权，可以使用它为系统中的每个用户快速设定权限。
WITH ADMIN OPTION：它是可选项，表示将系统权限授予某个用户后，该用户不仅获得该权限的使用权，还获得该权限的管理权，包括可以将该权限继续授予其它用户，或从其它用户处回收该权限。该选项的影响力较大，要慎重使用。

例1：授予用户USER1的connect权限
CONNECT USER1/1234;
CONNECT SYS/Oracle11 AS SYSDBA;
GRANT CONNECT TO USER1
再执行：CONNECT USER1/1234;

例2：授予用户USER2登录的权限，USER2并能把该权限授予其他人
CONNECT USER2/1234;
CONNECT SYS/Oracle11 AS SYSDBA;
Create user user4 IDENTIFIED BY 1234;
GRANT CREATE SESSION TO USER2 WITH ADMIN OPTION
再执行：CONNECT USER4/1234;
再执行：CONNECT USER2/1234;
GRANT CREATE SESSION TO USER4
再执行：CONNECT USER4/1234;

例3：授予用户USER4具有DBA的权限
CONNECT SYS/Oracle11 AS SYSDBA;
GRANT connect, resource ,create user TO USER4
CONNECT USER4/1234 ;
create USER USER5 IDENTIFIED BY 1234

用户权限的查询
select *
from dba_sys_privs
where grantee=‘用户名’ ;

5、系统权限回收
命令：
REVOKE SYSTEM_PRIV[,SYSTEM_PRIV,…]
FROM {PUBLIC|ROLE|USER}[,USER|ROLE|PUBLIC]]…
具有回收权限的用户才能回收权限
例：把 USER4的resource权限收回
CONNECT SYS/Oracle11 AS SYSDBA;
REVOKE create user FROM USER4;
CONNECT USER4/1234 ;
执行：create USER USER6 IDENTIFIED BY 1234

五、对象权限

1、对象权限的含义
对象权限是指访问其他用户模式对象的权力。
Oracle数据库中总共有9种不同的对象权限。
常用的对象权限包括对某个数据库对象中数据的查询、插入、修改、删除等权限。
对于表TABLE对象而言，ALL表示ALTER、DELETE、INDEX、SELECT、INSERT、UPDATE、REFERENCES权限
对于PROCEDURE存储过程，ALL表示EXECUTE权限

2、常用的权限

权限名称	适应的对象类型
表	视图	序列	进程	快照
SELECT	*	*	*		*
INSERT	*	*
UPDATE	*	*
DELETE	*	*		*
EXECUTE				*
ALTER	*		*
INDEX	*
REFERENCES	*

3、授权
命令
GRANT object_privilege[,object_privilege] ON object_name TO user_name[,user_name][WITH GRANT OPTIONJ
例：SCOTT用户授予用户USER1 对表EMP的查询权限
CONNECT USER1/1234 ;
SELECT * FROM SCOTT.EMP;
CONNECT SCOTT/tiger;
GRANT SELECT ON EMP TO USER1;
CONNECT USER1/1234 ;
SELECT * FROM SCOTT.EMP;

4、回收权限
命令：
REVOKE object_privilege [,object_privilege] ON object_name FROM user_name [, user_name];
例: SCOTT把user1用户对EMP的查询权限收回
CONNECT SCOTT/tiger;
REVOKE SELECT ON emp FROM USER1;
CONNECT USER1/1234 ;
SELECT * FROM SCOTT.EMP;

六、角色

1、含义
是一组系统权限和对象权限的集合。
Oracle数据库中引入角色的概念是为了简化数据库权限的管理。

2、引入角色优点：
减少权限管理的工作量。
实现动态权限管理。
权限的选择具有可用性和灵活性。
应用安全性。

3、角色的分类
系统预定义角色
CONNECT:连接到数据库,最终用户角色.
RESOURCE:申请资源创建对象,开发人员角色.
DBA:具有全部系统权限,可以创建数据库.
例1：查询当前数据库的所有预定义角色。
select * from dba_roles;
例2：grant resource to hr_user1;

自定义角色
以SYS用户登录
命令：
CREATE ROLE role_name [IDENTIFIED BY PASSWORD] [NOT IDENTIFIED]
例：CREATE ROLE testrole ;

4、角色授权和回收权限
(1)为角色授予系统权限
GRANT system_privilege [,system_privilege] TO role_name
(2)为角色授予对象权限
GRANT object_privilege [,object_privilege] ON object_name TO role_name
(3)回收角色的系统权限
REVOKE system_privilege [,system_privilege] FROM role_name
(4)回收角色的对象权限
REVOKE object_privilege [,object_privilege] ON object_name FROM role_name

例：
GRANT CREATE SESSION TO testrole ;
GRANT SELECT ON scott.emp TO testrole;

4、将角色授予用户
将角色授予用户的命令与授予权限的命令基本相同，格式如下：
GRANT role_name TO user_name
例：
GRANT RESOURCE,testrole TO user1;

5、删除角色
DROP ROLE role_name
例：DROP ROLE testrole