数据库原理实验二 数据库管理

实验2-1  用户权限管理

一、实验目的

1、对ORACLE数据库系统的用户权限管理有感性认识。

2、事务是由用户定义的一个数据库的操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。 Oracle数据库使用事务机制来确保数据的一致性，数据库的事务机制是发生在第一个SQL语句执行时，结束于COMMIT或ROLLBACK命令执行时。只有一个事务执行完成后，另一个可执行的SQL语句才能执行。SQL执行时，所有的数据改变都是暂时的，只有结束事务的时候，才会真正写入数据库。通过本实验，对事务管理有感性认识。

二、实验原理

（一）系统权限管理：由系统规定用户使用数据库的权限（系统权限是对用户而言)

1、系统权限分类

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

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

CONNECT：只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

→对于普通用户：授予connect, resource权限。

→对于DBA管理用户：授予connect，resource, dba权限。

2、系统权限授权命令

（1）系统权限只能由DBA用户授出：sys, system（最开始只能是这两个用户）

授权命令：grant connect, resource, dba to 用户名1 [,用户名2]...;

说明：普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用

户相同的权限，system用户的权限也可以被回收。

（2）查询用户拥有哪里权限：（只有具有DBA权限的用户才可查询的语句）

//查询数据库所有角色的权限信息，包括用户被授予的角色以及这些角色所具有的权限

select * from dba_role_privs;

//查看数据库所有用户被授予的系统级权限

select * from dba_sys_privs; 

//查看角色所拥有的系统级权限，而不考虑哪些用户被分配了这些角色

select * from role_sys_privs; 

3、系统权限传递

grant connect, resource to 用户名 with admin option;

 //增加with admin option选项可传递所获权限

4、系统权限回收：系统权限只能由DBA用户回收

Revoke dba, resource from 用户名;

5、删除用户

drop user 用户名;  //用户没有建任何实体时

drop user 用户名 CASCADE;  //将用户及其所建实体全部删除

说明：当前正连接的用户不得删除。

（二）实体权限管理

1、实体权限分类

select, update, insert, alter, index, delete, all（包括所有权限）, execute（执行存储过程权限）

例：

user01:

grant select, update, insert on product to user02;

grant all on product to user02;

user02:

select * from user01.product;

//此时user02可以查看user_tables（连接中能显示的，只显示当前用户拥有的表）视图，但user01.product这个表不会显示在结果中（如果查all_tables则可以查到，他可以访问）。

2. 将表的操作权限授予全体用户

grant all on product to public; // public表示所有的用户，这里的all权限不包括drop。

select owner, table_name from all_tables; //用户可以查询的表

select table_name from user_tables; //用户创建的表

select grantor, table_schema, table_name, privilege from all_tab_privs; //被授权可以存取的表

select grantee, owner, table_name, privilege from user_tab_privs;  //授出权限的表

3. DBA用户可以操作全体用户的任意基表（无需授权，包括删除）

4. 实体权限传递(with grant option)

grant select, update on product to user02 with grant option;// user02得到权限，并可以传递。

5. 实体权限回收

Revoke select, update on product from user02;  //传递的权限将全部丢失

说明：如果取消某个用户的对象权限，那么对于这个用户使用WITH GRANT OPTION授予权限的用户来说，同样还会取消这些用户的相同权限，也就是说取消授权时级联的。

三、使用仪器、材料

Oracle 11g，windows11；

四、实验步骤

1、以SYSTEM身份连接到orcl数据库，创建新的用户并授权，以新用户身份建立连接；

2、在新用户中建立新表，输入初始数据；

3、在新用户中确认orcl数据库中有这三个数据表，以及相应的数据；

4、在新用户中查看权限、对象等信息；

5、正式对不同权限的用户进行实验。

五、实验过程

1、以SYSTEM身份连接到orcl数据库，创建新用户CC并授权：

create user cc identified by cc;

grant resource, connect, DBA to cc;

2、以用户CC的身份建立连接，并在此连接下执行后面的操作；

连接用户CC：

在system身份下创建的用户在数据库中被授予的角色和权限有：

select * from user_role_privs;

属性列说明：

Admin_option：是否有授予其他用户该项权限的权限；

Default_role：表示该角色是否是用户的默认角色；

Os_granted：是否通过系统而不是SQL语句授权的；

3、在CC连接中：拷贝代码运行，建立表格Reader、Book、RB及输入数据：

create table Reader

(

    RNO varchar2(4) primary key,

    Rname varchar2(10) not null,

    Rsex varchar2(4),

    Rage integer,

    Rboss varchar2(10),

    Raddress varchar2(30)

) ;

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R001','张三',20,'男','李四','416');



create table Book

(

    BNO varchar2(4),

    Bname varchar2(50) not null,

    Bauthor varchar2(50),

    Bpress varchar2(50),

    Bprice numeric(6,2),

    primary key(BNO)

);

insert into book (BNO,Bauthor,Bname, bpress, bprice)

values('B001','严蔚敏','数据结构','清华大学出版社',null);



create table RB

(

    RNO varchar2(4),

    BNO varchar2(4),

    RBdate date default sysdate,

    primary key(RNO,BNO),

    foreign key (RNO) references Reader(RNO),

    foreign key (BNO) references Book(BNO)

);

insert into RB (RNO,BNO) values ('R001','B001');

表格数据：

Reader

Book

RB

4、在CC连接中：查询用户CC的权限信息（每句单独执行）：

查询数据库所有角色的权限信息：

select * from dba_role_privs;

查看数据库所有用户被授予的系统级权限：

select * from dba_sys_privs;

查看角色所拥有的系统级权限：

select * from role_sys_privs;

5、在CC连接中：查询用户创建的表

select table_name from user_tables;

6、在CC连接中：删去数据表BR，成功吗？

drop table rb;

再次查询用户创建的表：

select table_name from user_tables;

没有了RB表

7、回收用户CC的部分权限：以SYSTEM的身份连接（可以在SQL DEVELOPER的右上角切换），在此连接中执行

角色切换：

执行：

Revoke dba, resource from CC;

8、以CC的身份连接数据库（可以在SQL DEVELOPER的右上角选择连接），执行：

Create Table Aa(cola int);

结果：不成功。

原因：回收了resource权限，这些权限包括创建表、序列、过程等。

9、切换为SYSTEM的连接，执行

grant resource to CC;

10、切换为CC的连接，执行

select * from user_role_privs;

可以查看到CC的2种权限：

建表：

Create Table Aa(cola int);

结果成功：表的属性列如下

11、切换为SYSTEM的连接，执行

DROP USER CC;

删除用户CC不成功。

原因：Oracle 不允许在用户仍处于连接状态时删除它。

12、删除连接CC，在SYSTEM的连接中，执行

删除CC连接：

再在system执行删除CC用户语句：

DROP USER CC;

结果不成功。

原因：用户 CC拥有一些对象（比如表、视图、存储过程等），必须通过 CASCADE 选项来删除用户拥有的所有对象。

使用级联删除：

Drop User CC Cascade;

重新连接：不可连接已删除的CC，不能再以用户CC的身份连接。

13、在SYSTEM的连接中，重新创建用户CC和DD：

创建用户和授权：

Create User cc Identified By cc;

Create User dd identified by dd;

grant resource, connect, DBA to cc,dd;

连接：

查看这两个用户的权限（只有SYSTEM才有权执行下面这条语句）：

14、以CC和DD的身份分别建立连接，使得目前系统加上原来的SYSTEM共有三个连接；

选择CC连接，执行：

Create Table from_CC(内容 char(1));

Insert into from_cc values('a');

select * from from_cc;

创建的表属性列：

填内容，并查询from_cc表的内容：

选择DD连接，执行：内容为查找表名“FROM_CC”的所有用户名并创建查询结果视图

select owner, table_name from all_tables where table_name='FROM_CC';

在DD中查询FROM_CC表：

select * from from_cc;

DD中没有此表，也没有对此表的查询权限

选择CC连接，执行：对DD授予CC的所有权限

grant all on from_cc to dd;

选择DD连接，执行：重新执行查找表名“FROM_CC”的所有用户名并创建查询结果视图

select owner, table_name from all_tables where table_name='FROM_CC';

重新查询FROM_CC表：

select * from from_cc;

结果失败：

指定用户查询：

select * from cc.from_cc;

结果成果。

原因：虽然DD用户获得了在FROM_CC表上执行查询操作的权限，但是由于DD用户

并非 FROM_CC表的所有者，因此无法直接通过select * from from_cc这种不指定

所有者的方式来查询。

刷新DD连接下的表：没有“FROM_CC”这个表

选择DD连接，执行：在 'FROM_CC' 表的末尾插入了一个新的行，该行的值为 'd'

Insert into cc.from_cc values('d');

插入成功

 在DD中，间接从 'FROM_CC' 表中检索所有数据：

select * from cc.from_cc;

但是在CC中真实的 'FROM_CC' 表不变；

→INSERT 查询将在指定的表中插入新的行，但它不会改变或影响任何其他现有的行；

→只有当执行 UPDATE 或 DELETE 等修改操作时才会改变行

15、选择CC连接，执行：

回收CC授予DD的对表的INSERT权限：

Revoke insert on FROM_CC from DD;

选择DD连接，执行：从 'CC' 用户拥有的 'FROM_CC' 表中检索所有数据

select * from cc.from_cc;

依然能成功显示表的内容：

接着在 'FROM_CC' 表的末尾插入了一个新的行，该行的值为 'y'：

Insert into cc.from_cc values('y');  

select * from cc.from_cc;

插入成功。猜测原因：DD存在更高级别的权限，允许DD继续插入数据。

选择SYSTEM连接，执行：回收授予DD的dba和resource权限

Revoke dba, resource from DD;

选择DD连接，执行：再次从CC拥有的FROM_CC表中检索所有数据

select * from cc.from_cc;

依然能显示表的内容：

再进行在FROM_CC表的末尾插入了一个新的行，该行的值为 'z'：

Insert into cc.from_cc values('z');  

select * from cc.from_cc;

插入成功

选择CC连接，执行：回收CC授予DD的对表的所有权限

Revoke all on FROM_CC from DD;

select * from cc.from_cc;

CC中查看FROM_CC表的内容：之前在DD中执行的插入都没有记录

选择DD连接，执行：查看CC中FROM_CC表的内容

select * from cc.from_cc; // 显示前面成功插入的记录'y'、'z'

依然能显示前面的插入记录，即仍然不应DD对FROM_CC表的操作权限。

猜测原因：备份会话或授权会话。DD用户已经建立了一个授权会话或者备份会话，处于活动状态，所以可以继续执行之前授予的权限，即使权限已经在数据库层面被撤销了。

16、删除连接DD后，重新添加连接DD

在DD中查找FROM_CC表的内容：

select * from cc.from_cc;

显示这个数据对象不存在：

查看当前DD的权限：

select * from user_role_privs;

只有连接权：

在DD中创建表并插入内容 ’哦’：

Create Table from_DD(哦 char(2));

不能执行：

选择SYSTEM连接，执行：

grant resource to dd;

选择DD连接，执行：

Create Table from_DD(哦 char(2));

删除连接DD后，重新添加连接DD

Create Table from_DD(哦 char(2));

Insert into from_dd values('甲');  

select * from from_dd;

以上同样原因皆不能看到记录“甲”。

选择CC连接，执行：

select * from dd.from_dd;

查看不存在的FROM_DD：肯定是报错

让用户CC看到from_dd表的所有记录和为from_dd表添加正确做法：

①删除DD连接，并在system中对DD授予回之前回收的权限，那么DD可以创建表：

并且可以对此表添加内容：

②在DD中对CC赋予全部对表FROM_DD的权限：

grant all on from_dd to cc;

在CC中即可查看表FROM_DD：

select * from dd.from_dd;

并且可对表经行添加记录：

Insert into dd.from_dd values('乙');

17、创建新的用户并授权：

create user cc identified by cc;

grant resource, connect, DBA to cc;

18、以用户CC的身份建立连接，并在此连接下执行后面的操作；

create table mytable(tno char(2) primary key, tname char(9));

19、拷贝代码运行，建立表格及输入数据：

Insert Into Mytable Values('01','赵老大');

select * from mytable;

Insert Into Mytable Values('02','赵老二');

select * from mytable;

Insert Into Mytable Values('03','赵老三');

select * from mytable;

Insert Into Mytable Values('04','钱老大');

select * from mytable;

Insert Into Mytable Values('05','钱老二');

select * from mytable;

Rollback;

select * from mytable;

插入表格数据：回退前（5行）

回退后（没有数据）：回退到插入数据前

在第三条数据后commit：

Insert Into Mytable Values('01','赵老大');

select * from mytable;

Insert Into Mytable Values('02','赵老二');

select * from mytable;

Insert Into Mytable Values('03','赵老三');

select * from mytable;

commit;

Insert Into Mytable Values('04','钱老大');

select * from mytable;

Insert Into Mytable Values('05','钱老二');

select * from mytable;

Rollback;

select * from mytable;

会退会回到提交之后（3行）

创建Savepoint后再执行后续：

Insert Into Mytable Values('04','钱老大');

Savepoint p1;

select * from mytable;

Insert Into Mytable Values('05','钱老二');

Savepoint p2;

select * from mytable;

Insert Into Mytable Values('06','钱老三');

select * from mytable;

Rollback to p2;

select * from mytable;

Rollback to p1;

select * from mytable;

（回滚前）

回滚到p2（插入了第五条记录后）：

回滚到p1（插入了第四条记录后）：

如果先回退到p1，p2会失效：

六、实验思考

1、删除用户问题：是一个敏感的数据库操作

删除步骤总结

*备份数据：若数据重要需要保存，先备份。

①检查用户依赖关系：若用户拥有依赖于其他对象的对象，如表、视图、存储过程等需要迁移、重命名或删除依赖对象。

②关闭用户会话：若用户当前处于连接状态，需要断开其连接，或使用管理员权限强制断开。

③使用CASCADE选项：若用户拥有对象，比如表、视图等，在删除用户时需要使用 CASCADE 选项，以删除用户拥有的所有对象，确保数据库中不会留下残留对象。

*记录日志（防错）：执行删除操作之前，记录删除用户的操作，包括时间、原因及执行人员。

   

2、用户之间的权限

问：select owner, table_name from all_tables where table_name='FROM_CC'; 显示什么？什么意思？

答：操作将检索所有数据库所有表，返回所有表名为 'FROM_CC' 的表的所有者（owner）和表名（table_name）。

问：select * from from_cc; 显示什么？什么意思？

答：此操作是从数据库中名为 'FROM_CC' 的表中检索所有行和列的数据，并将其返回给用户。如果数据库中不存在名为 'FROM_CC' 的表，那么查询将会返回一个错误，指示找不到该表。

问：执行select * from cc.from_cc;才能显示查找内容，怎么回事？

答：此句将会从 'CC' 用户的 'FROM_CC' 表中检索所有数据，并将其返回给用户。在Oracle数据库中，当用户要查询另一个用户拥有的表时，需要明确指定表的所有者。虽然DD用户获得了在FROM_CC表上执行查询操作的权限，但是由于DD用户并非 FROM_CC表的所有者，因此无法直接通过select * from from_cc这种不指定所有者的方式来查询。

问：CC回收了DD对FROM_CC表的INSERT权限，并且在system中回收了DD的权限，为什么DD还能对FROM_CC表的INSERT操作无影响？

答：备份会话或授权会话。用户已经建立了一个授权会话或者备份会话，所以可以继续执行之前授予的权限，即使这些权限已经在数据库层面被撤销了。

3、事务控制语句COMMIT和SAVEPOINT

·COMMIT：用于将事务中的所有操作永久性地应用到数据库。

作用：①将事务中所有的数据修改操作（DML）作为一个原子操作提交给数据库；

②将这些修改操作记录到事务日志中，以便进行数据恢复和备份；

③释放事务所占用的资源，并结束该事务；

④使得这些修改对其他事务可见，并且将它们永久保存到数据库中，并且不会被回滚或撤销；

·SAVEPOINT：在事务中创建一个标记点，用于在稍后的时间回滚到该标记点。

作用：①部分回滚，而不是整个事务。可将事务回滚到之前创建的 SAVEPOINT。

②创建多个 SAVEPOINT，可形成多层次的标记点，但回退到最前的SAVEPOINT后，其他的SAVEPOINT会失效。

七、实验体会

数据库管理中的用户和角色区别：

【用户】：

 1、用户代表着访问数据库系统的个体（主体），可以执行操作、访问数据和管理对象。

 2、每个用户都有一个唯一的标识符（通常是用户名）和一组认证凭据（如密码）。

 3、用户可以直接被授权特定的权限，也可以作为角色的成员间接地获得权限。

【角色】：

1、角色是一组权限的命名集合，可以将权限赋予角色，然后将角色分配给用户。

2、通过角色，可以在数据库中定义常见的权限集合，如 "管理员"、"普通用户" 等。

3、角色可以被其他角色包含，这样可以实现权限的层级和继承关系。

---

实验2-2  Oracle数据库对象

一、实验目的

Oracle 数据库包含许多数据库对象，例如表、视图、索引、序列、存储过程、触发器等。表、视图、索引的操作在前面的实验中已经做了相应的练习，本实验将介绍如何使用序列、触发器和存储过程。同学们可以通过本实验掌握如何定义、使用删除这些数据库对象。

二、实验原理

1、序列（Sequence）

*简述：

①序列是Oracle数据库中独立于表的对象，可以在数据库中创建和管理。

②序列是数据库内部维护一个计数器，每次调用NEXTVAL函数时，计数器的值增加相应的递增值，并返回给调用者。

③序列可以用于生成主键值或其他需要唯一标识的值，例如订单号或员工编号。

*使用：

创建序列时，指定一个起始值（start with）、递增（步长）值（increment by）、最小值（minvalue）、最大值（maxvalue）和循环（生成新值）选项（cycle）等参数，通过CREATE SEQUENCE语句进行创建。使用NEXTVAL函数可以获取序列的下一个值，使用CURRVAL函数可以获取序列的当前值。

2、触发器（Trigger）：

*简述：

①触发器是一种数据库对象，它在指定的数据库事件发生时自动执行特定的操作。

②触发器通过在数据库内部注册一个事件监听器，当触发器所关联的事件发生时，触发器内部定义的操作会被执行。

③触发器可以用于实现数据约束、审计功能、自动化业务规则等，通常与特定的表相关联，并在表的数据被修改时触发。

*使用：

创建触发器时，需要指定触发器所监听的事件，以及触发时机（BEFORE或AFTER）等，通过CREATE TRIGGER语句进行创建。当触发器所监听的事件发生时，触发器内部定义的操作会被自动执行。

3、存储过程（Stored Procedure）

*简述：

①存储过程是一组预编译的SQL语句和逻辑操作，存储在数据库中以便重复使用。

②存储过程可以接受参数并返回结果集，可以在应用程序中调用，还可以执行复杂的业务逻辑、数据操作和数据处理。

③存储过程是将SQL语句和逻辑操作存储在数据库内部，当存储过程被调用时，数据库会执行其中定义的操作，并返回结果给调用者。

*使用：

创建存储过程时，编写一段包含SQL语句和逻辑操作的代码，并使用CREATE PROCEDURE语句进行创建。

三、使用仪器、材料

Oracle 11g，windows11；

四、实验步骤

1.序列

a)创建序列

b)查看创建的序列对象

c)使用序列

d)修改序列

e)删除序列

2.存储过程

a)创建三个数据表

b)插入数据创建存储过程

c)创建存储过程，更新表中的数据

d)执行存储过程，并比较存储过程执行前后的数据变化情况

e)删除存储过程

f)创建存储过程

g)运行存储过程

3.触发器

a)创建触发器

b)创建触发器credit_id

c)查看刚创建的触发器对象

d)激活刚创建的触发器

五、实验过程

1、以SYSTEM连接数据库ORCL，执行以下语句查看对象：

select object_name,owner from all_objects where owner = 'SYSTEM';

显示有502行

2、创建新的用户并授权：

drop user cc cascade;

create user cc identified by cc;

grant resource, connect, DBA to cc;

3、以用户CC的身份建立连接，并在此连接下执行序列的操作：

查找system的所有的对象：

select object_name,owner from all_objects where owner = 'SYSTEM';

502行，和在sys连接中的查询相同

查找CC下的对象用户：

select object_name,owner from all_objects where owner = 'CC';

0行。CC是新用户，还没有创建自己的对象。

在CC中创建序列：

create sequence my_seq_01 increment by 1 start with 1 nomaxvalue nocycle;

create sequence my_seq_02 increment by 2 start with 1;

select object_name,owner from all_objects where owner = 'CC';

创建视图常看CC的对象：2行

查看system的序列：

select object_name,Object_Type, owner

from all_objects where owner = 'SYSTEM' and OBJECT_TYPE='SEQUENCE';

SYSTEM有20行

创建视图查看CC的序列：CC有2行

//从序列"my_seq_01"中获取下一个值

//"dual"是一个虚拟表，"my_seq_01.nextval"是一个伪序

select my_seq_01.nextval from dual;

重复执行上面的这条语句，得到序列： 1，2，3，4，……，只有一行

每次执行这个查询时，序列会自动递增并返回下一个值：

改变取值步长为10：

alter sequence my_seq_01 increment by 10 ;

select my_seq_01.nextval from dual;

重复执行上面的这条语句，得到序列： 15，25，35，45，……从上一个值继续

select my_seq_02.nextval from dual;

重复执行上面的这条语句，得到序列： 1，3，5，7，……，my_seq_02步长是2

删除序列2：

drop sequence my_seq_02;

select my_seq_02.nextval from dual;

再次查询：不存在

重新创建序列2：

create sequence my_seq_02 increment by 3 start with 100;

select my_seq_02.nextval from dual;

重复执行上面的这条语句，得到序列： 100，103，106，109，……与上被删除的同名序列没有关系了。

4、在CC的连接中，执行存储过程的操作；

检查并删除数据库中特定的表格、存储过程和序列：

Declare

tmp integer default 0;

Begin

select count(*) into tmp from user_tables where table_name='SC';

if(tmp>0) then

execute immediate 'drop table SC ';

end if;

select count(*) into tmp from user_tables where table_name='STUDENT';

if(tmp>0) then

execute immediate 'drop table STUDENT ';

end if;

select count(*) into tmp from user_tables where table_name='COURSE';

if(tmp>0) then

execute immediate 'drop table COURSE ';

end if;

Select count(*) into tmp

From All_Objects

Where OBJECT_NAME='SC_INS' and OBJECT_TYPE='PROCEDURE';

if(tmp>0) then

execute immediate 'drop PROCEDURE SC_INS ';

end if;

Select count(*) into tmp

From All_Objects

Where OBJECT_NAME='STUDENT_NO' and OBJECT_TYPE='SEQUENCE';

if(tmp>0) then

execute immediate 'drop SEQUENCE STUDENT_NO ';

end if;

end;

然后重新创建数据表：成功了；但是不能任意调上面执行顺序，SC表必须在STUDENT和COURSE表之后创建。

create table student(sno int primary key, sname varchar(8));

create table course(cno int primary key, cname varchar(12));

create table sc(sno int, cno int, grade int, 

                primary key(sno,cno), 

                foreign key (sno) references student(sno), 

                foreign key (cno) references course(cno));

select object_name, Object_Type, owner from all_objects where owner = 'CC';

显示8行；（隐式创建索引）在创建表时，定义了主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）等，Oracle会隐式地为这些约束创建索引。

到SOL DEVELOPER的可视化窗口查看：

由STUDENT和COURSE表的SNO、CNO属性列确定，依据如表，索引名为SYS_C007088。

创建一个名为 "student_no" 的序列：起始值为 2012001

create sequence student_no increment by 1 start with 2012001;

插入、查看student表、course表的内容：

insert into student values(student_no.nextval, 'aaaaaa');

insert into student values(student_no.nextval,'bbbbbbb');

insert into student values(student_no.nextval,'ccccccc');

insert into student values(student_no.nextval,'ddddddd');

commit;

select * from student;

insert into course values (105,'程序设计');

insert into course values (908,'大学英语');

insert into course values (433,'数据结构');

commit;

select * from course;

创建存储：成功

//创建存储过程

create procedure sc_ins(ino int,cno int,grade int) is //is 表示开始存储过程的主体

begin //存储过程的开始

  if(grade>=0) then insert into sc values (ino,cno,grade);

  else insert into sc values (ino,cno,null);

end if; //结束条件语句块

end; //结束存储过程的主体

显示的内容：

显示1行存储：

Select Object_Name, Object_Type, Owner

From All_Objects Where Owner = 'CC' and OBJECT_TYPE='PROCEDURE';

执行存储过程的语句：成功

//执行存储过程的语句，调用名为sc_ins存储过程，并传递参数给该存储过程

exec sc_ins (2012001,105,60);

exec sc_ins (2012001,908,0);

exec sc_ins (2012001,433,98);

exec sc_ins (2012002, 105,75);

exec sc_ins (2012002, 433,-1);

exec sc_ins (2012003, 105,64);

exec sc_ins (2012003, 908,90);

exec sc_ins (2012003, 433,-100);

显示：

select student.sno,sname,cname,grade

from student,course,sc

where student.sno=sc.sno and course.cno=sc.cno;

5、在CC的连接中，执行触发器的操作；

①触发器只影响当前表：

//向名为 "sc" 表格中添加一个名为 "gradelevel" 的新列

alter table sc add (gradelevel char(1));

//向名为 "sc" 表格中添加一个名为 "gradelevel" 的新列

//看到和上一步的显示结果的区别：给出的是等级评价，屏蔽了具体分数

update sc set gradelevel='A' where grade>=85;

update sc set gradelevel='B' where grade>=75 AND grade<85;

update sc set gradelevel='C' where grade>=60 AND grade<75;

update sc set gradelevel='D' where grade<60;

//根据学生的成绩更新表格 "sc" 中的 "gradelevel" 列并查询

select student.sno,sname,cname,grade,gradelevel

from student,course,sc

where student.sno=sc.sno and course.cno=sc.cno;

创建触发器：成功

//创建或替换触发器sc_ins

create or replace trigger sc_ins before insert or update on sc

for each row //每一行数据都会执行一次

begin

  if :new.grade>=85 then :new.gradelevel:='A';

  else if :new.grade>=75 then :new.gradelevel:='B';

      else if :new.grade>=60 then :new.gradelevel:='C';

           else if :new.grade>=60 then :new.gradelevel:='D';

 end if;

  end if;

   end if;

    end if;

 end;

执行：

select * from sc where sno=2012002;

insert into sc(sno,cno,grade) values (2012002,908,80);

select * from sc where sno=2012002;

插入数据前：

刚刚插入的行，gradelevel列的值的由来：触发器可以在每次插入或更新操作时都会自动执行操作

②触发器影响其他表：

增加表属性列maxgrade：

alter table course add (maxgrade int);

update course set maxgrade=0;

select * from course;

为属性列赋值，表的结构改变了，修改也成功了：

创建触发器course_ins：

//每次插入或更新 sc 表中的行时，检查与新插入或更新的行相关联的课程（由 cno 指定），

如果新的成绩高于当前该课程的最高成绩，则更新该课程的最高成绩。

create or replace trigger course_ins before insert or update on sc

for each row

declare oldg int;

begin

 select maxgrade into oldg from course where cno=:new.cno;

 if oldg<:new.grade then update course set maxgrade=:new.grade where cno=:new.cno;

 end if;

 end course_ins;

当前触发器目录下有2项：

执行：

select * from course;

//记下当前各科的最高分数

insert into sc(sno,cno,grade) values (2012004,908,99);

insert into sc(sno,cno,grade) values (2012004,433,88);

insert into sc(sno,cno,grade) values (2012004,105,59);

select * from sc;

select * from course;

插入前

插入后：course表改变了，但是这种改变屏蔽了之前的数据，只有在触发器有之后才有。

查看2012003号同学的105号课程的分数和级别：

select * from sc where sno=2012003 and cno=105;

更新20120003同学的105科目成绩：成功

update sc set grade=100 where sno=2012003 and cno=105;

select * from sc where sno=2012003 and cno=105;

select * from course;

级别(sc.gradelevel)有相应变化：

当前各科的最高分(course.maxgrade)有变化：一个修改语句可以同时触发两个表的数据改变

查看SC表当前的触发器：

六、实验思考

显示结果说明，成绩为负数的话，在数据表里是null内容？存储过程有什么好处？

分数为0，和分数为负数，有区别吗？

①如果是负数则填null；②存储过程的好处如下：

·封装复杂逻辑：存储过程可以封装复杂的业务逻辑，将其存储在数据库中，使得应用程序只需调用存储过程而无需了解其内部实现细节。

·提高性能：存储过程在数据库中编译并存储，可以减少每次执行时的解析和编译时间。

·安全性：帮助实现对数据库操作的权限控制，只需授予用户对存储过程的执行权限，而无需直接授予对底层表的访问权限。

·重用性：存储过程可被多个应用程序或查询调用，从而实现代码的重用，减少重复编写的工作。

③一般情况下，0可能表示特定情况下的有效分数，而负数则可能表示无效或错误的数据。

七、实验体会

（1）数据库用户和对象、对象所有者

【用户】：

·用户是指被授权访问数据库的个体或实体，它们是数据库中的主体。

·用户可以是人、应用程序或其他数据库。

·每个用户都有自己的用户名和密码，用于登录和访问数据库。

【对象】：

·对象是指在数据库中存储数据或定义数据结构的实体。

·对象包括表、视图、索引、序列、触发器、存储过程等。

·对象用于组织和管理数据，并且可以通过SQL语句进行操作和访问。

·每个对象都有一个所有者，该所有者可以是数据库中的任何有效用户。

【对象所有者】：

·对象所有者是创建该数据库对象（如表、视图、索引、序列等）的用户或模式。

·所有者负责管理和维护所拥有的对象，并可以授予其他用户对这些对象的访问权限。

（2）PL/SQL中不允许在DECLARE-BEGIN-END块之后执行DDL语句，把前面的PL/SQL删掉。

（3）解决编译器报错

点击错误提示右键转到源来定位错误：使用单句执行

---

实验2-3  数据备份与恢复

一、实验目的

掌握ORACLE数据库系统的一种备份/恢复方法。

二、实验原理

（一）Oracle数据库的三种标准备份方法

1）导出／导入（逻辑备份）

2）热备份（被备份的数据库不必关闭）

3）冷备份（被备份的数据库必须关闭）

说明：冷备份和热备份是物理备份（文件级）；实验做的属于逻辑备份。

 

（二）Oracle数据库逻辑备份 

*四种模式：表空间备份(TABLESPACE)

表备份(TABLE)

用户备份(USER)

完全备份(FULL)

#使用命令：IMP（导出）&EXP（导入）

说明：使用命令时需要CREATE SESSION系统权限，若要导出其他的表，必须拥有

EXP_FULL_DATABASE权限。 EXP命令导出的文件是二进制文件（*.DMP）只能由对应的 IMP

命令进行读取恢复。

注：(1) 查看 EXP 帮助文档的命令：EXP HELP=Y

       (2) 查看 IMP 帮助文档的命令：IMP HELP=Y

三、使用仪器、材料

Oracle 11g，windows11；

四、实验步骤

1、在orcl数据库中创建新的用户并授权（如果原来已有这个用户，可以忽略本步骤）：

2、以用户CC的身份建立连接，并在此连接下执行后面的操作；

3、拷贝代码运行，删去旧的同名数据表：

4、拷贝代码运行，建立表格及输入数据：

5、将要删除的数据导出；

6、确认D盘有文件“export from orcl.sql”

7、到ORACLE SQL DEVELOPER中以SYSTEM用户的身份建立新的连接（这个密码是前面自己新建数据库输入的密码）：

8、在该连接下执行以下语句，即在数据库MYDB里增加用户CC，以便与备份的用户名一致

9、以用户CC的身份建立新的连接到数据库mydb：  

10、确认mydb中没有数据表READER、BOOK、RB：

11、在MYDB的CC用户连接中打开文件（或用记事本打开再复制粘贴）运行； 

12、确认MYDB中含有这三个表，且表的内容、约束等与数据库ORCL中的一致；

13、为避免占用太多的系统资源，删去数据库MYDB。先删去有关MYDB的所有连接；

14、打开“Database Configuration Assistant”，删去数据库MYDB；

  

五、实验过程

1、在orcl数据库中创建新的用户并授权（如果原来已有这个用户，可以忽略本步骤）：

create user cc identified by cc ;

grant resource, connect, DBA to cc;

2、以用户CC的身份建立连接，并在此连接下执行后面的操作；

3、拷贝代码运行，删去旧的同名数据表：

Declare

tmp integer default 0;

Begin

select count(*) into tmp from user_tables where table_name='RB';

if(tmp>0) then

execute immediate 'drop table RB';

end if;

select count(*) into tmp from user_tables where table_name='READER';

if(tmp>0) then

execute immediate 'drop table READER';

end if;

select count(*) into tmp from user_tables where table_name='BOOK';

if(tmp>0) then

execute immediate 'drop table BOOK';

end if;

end;

4、拷贝代码运行，建立表格及输入数据：

create table Reader

(RNO varchar2(4) primary key,

Rname varchar2(10) not null,

Rsex varchar2(3),

Rage integer,

Rboss varchar2(10),

Raddress varchar2(30)) ;

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R001','张三',20,'男','李四','416');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R002','张三',35,'女',null,'417');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R003','李四',30,'男',null,'416');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R004','王五',20,'男',null,'417');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R005','马六',40,'男',null,'416');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R006','刘三',20,'男',null,'417');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R007','王四',40,'男','李四','416');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R008','李小龙',20,'男','李四','417');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R009','王小倩',40,'男','李四','416');

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress)

values('R010','王一小',20,'男','李四','417');



create table Book

(BNO varchar2(4),

Bname varchar2(50) not null,

Bauthor varchar2(50),

Bpress varchar2(50),

Bprice numeric(6,2),

primary key(BNO));

insert into book (BNO,Bauthor,Bname, bpress, bprice)

values('B001','严蔚敏','数据结构','清华大学出版社',null);

insert into book (BNO,Bauthor,Bname, bpress, bprice)

values('B002','唐发根','数据结构','北航出版社',24);

insert into book (BNO,Bauthor,Bname, bpress, bprice)

values('B003','王珊','数据库原理','高等教育出版社',40);

insert into book (BNO,Bauthor,Bname, bpress, bprice)

values('B004','张飞','数据库原理','清华大学出版社',30);

insert into book (BNO,Bauthor,Bname, bpress, bprice)

values('B005','王珊','数据库原理','清华大学出版社',null);



create table RB

(RNO varchar2(4),

BNO varchar2(4),

RBdate date default sysdate,

primary key(RNO,BNO),

foreign key (RNO) references Reader(RNO),

foreign key (BNO) references Book(BNO));

insert into RB (RNO,BNO) values ('R001','B001');

insert into RB (RNO,BNO) values ('R001','B002');

insert into RB (RNO,BNO) values ('R001','B004');

insert into RB (RNO,BNO) values ('R002','B001');

insert into RB (RNO,BNO) values ('R003','B001');

insert into RB (RNO,BNO) values ('R004','B001');

insert into RB (RNO,BNO) values ('R004','B002');

insert into RB (RNO,BNO) values ('R005','B001');

insert into RB (RNO,BNO) values ('R006','B001');

insert into RB (RNO,BNO) values ('R006','B003');

insert into RB (RNO,BNO) values ('R006','B005');

insert into RB (RNO,BNO) values ('R006','B002');

insert into RB (RNO,BNO) values ('R006','B004');

原始数据：

Reader

Book

RB

5、先将要删除的数据导出：

6、确认D盘有文件“export from orcl.sql”

7、到ORACLE SQL DEVELOPER中以SYSTEM用户的身份建立新的连接（这个密码是前面自己新建数据库输入的密码）：

查看端口：

创建连接：

8、在该连接下执行以下语句，即在数据库MYDB里增加用户CC，以便与备份的用户名一致（打开文件“export from orcl.sql”可见里面的语句带了用户名）：

create user cc identified by cc;

grant resource, connect, DBA to cc;

9、以用户CC的身份建立新的连接到数据库mydb：

10、确认mydb中没有数据表READER、BOOK、RB：

11、在MYDB的CC用户连接中打开文件（或用记事本打开再复制粘贴）运行

12、确认MYDB中含有这三个表，且表的内容、约束等与数据库ORCL中的一致

13、为避免占用太多的系统资源，删去数据库MYDB。先删去有关MYDB的所有连接   

如果按照上述步骤，应该有两个连接：SYSTEM用户的连接和CC用户的连接。

14、打开“Database Configuration Assistant”

15、删去数据库MYDB：

注意：如果未删除数据库的连接，则无法删除数据库。

六、实验体会

导出导入常见问题

（1）数据库对象已经存在

一般情况, 导入数据前应该彻底删除目标数据下的表、序列、函数/过程、触发器等。如果数据库对象已经存在, 按缺省的 imp 参数，则会导入失败，此时使用参数 ignore=y，会把 exp 文件内的数据内容导入，数据的导入必须满足关系表的完整性约束条件，否则数据导入失败。

（2）权限不够

如果要把 A 用户的数据导入 B 用户下，A 用户需要有 imp_full_database 权限。

（3）导入大表(大于80M ) 时，存储分配失败

使用 EXP 时，默认情况下，compress = Y，也就是把所有的数据压缩在一个数据块上。导入时如果不存在连续一个大数据块，则会导入失败。导出 80M 以上的大表时，记得 compress= N，则不会引起这种错误。

（4） IMP 和 EXP 使用的字符集不同

如果字符集不同，导入会失败，可改变 UNIX 环境变量或者 NT 注册表里NLS_LANG 相关信息。

（5）IMP 和 EXP 版本不能往上兼容

IMP 可以成功导入低版本 EXP 生成的文件, 不能导入高版本 EXP 生成的文件。