· 236·
SQL> select * from popular_products;
ID PRODUCT_ID PRODUCT_NAME
---------- ------------------------ ----------------------------------------------- 1 2 格力中央空调
2．假设有一个考勤数据表attendance，该表要求不能在周末被更新。创建一个基于该表的触发器tr_attendance，实现禁止周末更新的功能。
1）创建测试表attendance
SQL> create table attendance(id number, time date, employee_id number);
Table created
2）该触发器应该是一个表级触发器，并且应该根据是否周末进行判断
create or replace trigger tr_attendance before insert or update or delete on attendance begin
declare weekday varchar2(1); begin select to_char(sysdate, 'd') into weekday from dual; if (weekday='1' or weekday='7') then raise_application_error(-20001, '不能在周末修改出勤信息！'); end if; end; end;
3）当前是星期六，那么尝试修改表attendance
SQL> insert into attendance values(1, sysdate, 1);
insert into attendance values(1, sysdate, 1)
ORA-20001: 不能在周末修改出勤信息！
ORA-06512: at \
ORA-04088: error during execution of trigger 'SYSTEM.TR_ATTENDANCE'
4）注意，利用to_char(sysdate, ‘d’)获得的是一周的第几天。该返回值从星期天开始，其值为1。星期一至星期六的值递增加1。
3．利用alter trigger命令禁用触发器tr_attendance，然后保证数据库服务器时间为周末。尝试向表attendance中插入数据。
1）利用disable选项禁用触发器tr_attendance
SQL> alter trigger tr_attendance disable;
Trigger altered.
2）此时，可以向表attendance中插入数据
SQL> insert into attendance values(1, sysdate, 1);
1 row created.
精品文档
·237·
第11章 序列
1．创建一个起始值5、步长5、最大值20的序列seq_test，并利用seq_test.nextvalue来验证这些属性。
1）利用如下SQL语句创建序列seq_test
SQL> create sequence seq_test start with 5 increment by 5 maxvalue 20;
Sequence created
2）序列seq_test创建成功之后，第一次调用sql_test.nextvalue获得的是start with属性的值。
SQL> select seq_test.nextval from dual;
NEXTVAL -------------- 5
3）继续调用sql_test.nextvalue来查看序列的步长和最大值
SQL> select seq_test.nextval from dual;
NEXTVAL ---------- 10
SQL> select seq_test.nextval from dual;
NEXTVAL ---------- 15
SQL> select seq_test.nextval from dual;
NEXTVAL ---------- 20
SQL> select seq_test.nextval from dual;
select seq_test.nextval from dual
ORA-08004: sequence SEQ_TEST.NEXTVAL exceeds MAXVALUE and cannot be instantiated
4）从查询结果可以看出，序列sql_test的步长为5。
SQL> alter sequence seq_test increment by 1;
Sequence altered
5）而最大值要通过将其步长修改为1才能确定。
SQL> select seq_test.nextval from dual;
select seq_test.nextval from dual 精品文档
· 238·
ORA-08004: sequence SEQ_TEST.NEXTVAL exceeds MAXVALUE and cannot be instantiated
2．如果一个序列经过多次调用，而又希望将其恢复至默认状态，可以首先将其删除，然后重建。利用drop命令删除序列seq_test，并进行重建。
1）利用drop命令，删除该序列
SQL> drop sequence seq_test;
Sequence dropped.
2）利用create sequence命令重建该序列
SQL> create sequence seq_test start with 5 increment by 5 maxvalue 20;
Sequence created
3）可以使用初始状态的序列seq_test
SQL> select seq_test.nextval from dual;
NEXTVAL -------------- 5
3．某些情况下，并不适合利用删除——重建的方式来重置序列状态。这是因为该序列有可能被其他函数/存储过程调用，删除——重建之后，还需要重新测试那些函数和存储过程是否能够成功编译，增加了开发者的工作量。尝试利用修改属性increment by，并调用sequence.nextval来重置序列seq_test。
1）查看此时序列的nextval属性的值
SQL> select seq_test.nextval from dual;
NEXTVAL -------------- 10
2）临时修改序列的步长，以便调用序列的nextval属性时，返回start with的前一个值。例如，实际步长为5，初始值为5，那么，需要返回的值为0。而当前值为10，所以临时步长为-10。
SQL> alter sequence seq_test minvalue 0;
Sequence altered.
SQL> alter sequence seq_test increment by -10;
Sequence altered.
SQL> select seq_test.nextval from dual;
NEXTVAL -------------- 0
3）将序列的步长恢复至5。
SQL> alter sequence seq_test increment by 5; 精品文档
Sequence altered.
·239·
4）当下次调用seq.nextval时，获得的值，即从5开始。当然，在seq.nextval的值大于1之后，还应该将minvalue属性重置为1。。
SQL> select seq_test.nextval from dual;
NEXTVAL -------------- 5
SQL> alter sequence seq_test minvalue1;
Sequence altered.
第12章 用户角色与权限控制
1．创建新的用户mike，并分别授予mike两种不同的权限：create table和create any table，比较这二者的区别。
1）创建用户mike
SQL> create user mike identified by abc123;
User created.
2）为mike赋予最基本的权限connect和create session
SQL> grant connect, create session to mike;
Grant succeeded.
3）利用用户mike重新登录数据库 4）利用用户mike尝试创建数据表
SQL> create table mike_test(id number); create table mike_test(id number) *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
5）利用用户system为mike分配create table权限，以及表空间配额
SQL> grant create table to mike;
Grant succeeded.
SQL> alter user mike quota 20m on users;
User altered.
6）再次利用用户mike创建数据表
SQL> create table mike_test(id number);
Table created.
7）利用用户mike在用户system的schema下创建数据表system.mike_test
精品文档
· 240·
SQL> create table system.mike_test(id number);
create table system.mike_test(id number) *
ERROR at line 1:
ORA-01031: insufficient privileges
这是因为，在当前权限状态下，用户mike具有create table的权限。而这里的create table权限仅限于在用户自身schema中创建数据表。
8）利用用户system收回mike的create table权限，并为其分配create any table的权限
SQL> revoke create table from mike;
Revoke succeeded.
SQL> grant create any table to mike;
Grant succeeded.
9）利用用户mike创建表system.mike_test
SQL> create table system.mike_test(id number);
Table created.
10）从该操作实例可以得出这样的结论，权限create table仅限于用户在自身的schema中创建数据表；而create any table则允许用户在所有schema中创建数据表。
2．利用with admin option选项，为mike分配系统权限create table的同时，允许其将权限传播给用户bill。
1）在当前状态下，利用用户system创建新的用户bill，并为其分配connect, create session权限，以及在表空间users上10M的配额。
SQL> create user bill identified by abc123 ;
User created.
SQL> grant connect, create session to bill;
Grant succeeded.
SQL> alter user bill quota 10M on users;
User altered.
2）尝试利用mkie为bill分配create table的权限，将会导致操作失败
SQL> grant create table to bill; grant create table to bill *
ERROR at line 1:
ORA-01031: insufficient privileges
3）利用用户system为mike分配create table的权限，并添加with admin option选项，以允许mike将该权限传播出去
SQL> grant create table to mike with admin option;
Grant succeeded. 精品文档
21天学通Oracle 课后答案（第三版）
