实验2 角色和用户管理
- 通过手工方式创建一个用户NEWUSER,由数据库密码认证。
- 利用企业管理器中对该新用户进行设置,授予其合适的对象权限、系统权限(CREATE SESSION、CREATE TABLE等)、定额。
授予对象权限
授予系统权限
修改定额
- 通过手工方式创建一个新角色NEWROLE,为该角色指定合适的对象权限和系统权限,然后利用企业管理器为用户NEWUSER指定该角色。
创建NEWROLE,并指定一些权限
为NEWUSER指定该角色
实验3 创建表、视图、索引
1、使用Oracle企业管理器或手工方法创建两张表USERS和USERTYPES。
2、使用Oracle企业管理器或手工方法做如下操作:
1)创建视图,包含用户姓名,密码,以及用户类型名称。
2)创建基于表USERS中TYPE字段上的一个索引。
创建表、视图、索引、注意usertype的主键和use表中的外键数据类型要保持一致
create table use(id number primary key ,name varchar(20) not null,
pwd varchar(20) default '111111',type number not null,
foreign key(type) references usertype(id));
create table usertype(id number primary key,name varchar(30) not null);
create view usersview as select name,pwd,type from use;
create index type on use(type);
向表中插入数据
insert into usertype values(1,'ADMIN');
insert into usertype values(2,'RESOURCE');
insert into usertype values(3,'CONNECT');
insert into use values(100,'JOHN','John123',1);
insert into use values(101,'TOM','Tom123',1);
insert into use values(102,'ALA','Ala123',2);
insert into use values(103,'TONNY','Tonny123',2);
insert into use values(104,'Jimmy','Jimmy123',2);
insert into use values(105,'Anny','Anny123',3);
实验4 数据查询
- 将实验三中的USERS, USERTYPES表重新导入
- 开始查询
- 分组查询命令的使用(例):
利用聚集函数统计不同类型用户的个数;
- 连接查询命令的使用:
分别使用多表连接和内连接查询用户的姓名以及类型名称
- 子查询命令的使用(例):
使用嵌套语句查询用户类型为‘ADMIN’的用户姓名
select type,count(*) from use group by type;
select use.name,usertype.name from use,usertype where use.type=usertype.id;
select use.name,usertype.name from use inner join usertype on usertype.id=use.type;
select use.name from use where use.type=(select usertype.id from usertype where name='ADMIN');
实验5 PL/SQL 编程
1、编写一个PL/SQL程序块,计算100以内的奇数和。
2、编写PL/SQL块计算并输出 S=1!+2!+…+10!。
3、编写一个PL/SQL块用于计算两个正整数的最大公约数。
在实验开始之前,首先创建两张表(实验3,4完成的内容),
Create table users(USERID number, USERNAMEE varchar2(40),USERTYPE number,USERPWD varchar2(40));
Create table usertypes (TYPEID number,TYPENAME varchar(40));
Insert into users values(100, 'john', 1, 'john');
Insert into users values(101, 'allen', 2, 'allen');
Insert into users values(102, 'lee', 2, 'lee');
Insert into usertypes values(1, 'admin');
Insert into usertypes values(2, 'normal');
在sql plus中用@{脚本存储路径}\xxx.sql 命令执行文件
编写一个PL/SQL程序块,计算100以内的奇数和。
SET ServerOutput ON;
DECLARE
v_Num number :=1;
v_Sum number :=0;
BEGIN
loop
v_Sum :=v_Sum+v_Num;
v_Num :=v_Num+2;
exit when v_Num>100;
end loop;
dbms_output.put_line('sum is');
dbms_output.put_line(v_Sum);
END;
/
编写PL/SQL块计算并输出 S=1!+2!+…+10!。
SET ServerOutput ON;
declare
factorial number :=1;
total number :=0;
begin
for i in 1..10 loop
factorial :=factorial*i;
total :=total+factorial;
end loop;
dbms_output.put_line('1!+..+10!');
dbms_output.put_line(total);
end;
/
编写一个PL/SQL块用于计算两个正整数的最大公约数
SET ServerOutput ON;
declare
num1 number :=60;
num2 number :=48;
temp number;
begin
loop
temp :=num2;
num2 :=MOD(num1,num2);
num1 :=temp;
exit when num2=0;
end loop;
dbms_output.put_line('60 and 40 gcd');
dbms_output.put_line(num1);
end;
/
实验6 使用存储过程和触发器
A了解企业管理器创建存储过程和触发器的步骤和方法;
B采用PL/SQL编写自定义存储过程和函数;
C采用PL/SQL编写触发器;
1、采用PL/SQL编写自定义存储过程
创建过程:
在SQL PLUS中创建过程
将创建代码写入脚本文件,用@{脚本存储路径}\xxx.sql 命令执行文件
执行过程:
执行命令:EXECUTE SYS.XXX (参数);
查看结果:
用查询语句查看结果
CREATE OR REPLACE PROCEDURE RESETPWD
( v_USERID in NUMBER )
as
begin
UPDATE USERS SET USERPWD = '111111' WHERE USERID = v_USERID;
end;
/
执行过程resetpwd,参数设置为101,依据实验5,user表中userid=101的用户allen将会执行该过程,此时,该用户的密码将被设置为111111,我们通过select语句可以查看到此时allen的密码已经被修改
2.采用PL/SQL编写自定义函数;
创建函数代码:
CREATE OR REPLACE FUNCTION GETPWD
( v_NAME IN USERS.USERNAMEE%TYPE )
RETURN USERS.USERPWD%TYPE
AS
outpwd USERS.USERPWD%TYPE;
BEGIN
SELECT USERPWD INTO outpwd FROM USERS
WHERE USERNAMEE = ''||v_NAME||'';
RETURN outpwd;
END;
/
注意,在实验5中user表的用户名字段为usernamee
执行函数代码:
SET ServerOutput ON;
DECLARE
varPwd USERS.USERPWD%TYPE;
BEGIN
varPwd := GETPWD('test1);
dbms_output.put_line(‘PWD IS’);
dbms_output.put_line(varPwd);
END;
/
请注意,由于在实验5中我们未插入’test1’的记录,因此在执行上述函数时需要先将test1修改为已存在的用户名如john,allen,也可以通过向users表中插入新的’test1’语句以使得该语句成功执行
3、采用PL/SQL编写触发器
触发器代码块:
CREATE OR REPLACE TRIGGER USERTYPETRIGGER
AFTER UPDATE ON USERTYPES
FOR EACH ROW
BEGIN
UPDATE USERS
SET USERTYPE = :NEW.TYPEID
WHERE USERTYPE = :OLD.TYPEID;
END;
/
在USERTYPES表更新后创建一个触发器,以更新USERS表中的相应记录
可以看到实验5中usertype为1的john已经被修改为3