数据库实验四五.docx
《数据库实验四五.docx》由会员分享，可在线阅读，更多相关《数据库实验四五.docx（22页珍藏版）》请在冰豆网上搜索。
数据库实验四五
实验四：
数据字典视图
实验目的
在oracle下熟练掌握数据字典视图的应用。
实验要求
1、基本配置：
IntelPentiumIII以上级别的CPU，大于512MB的内存。
2、软件要求：
WindowXP操作系统，ORACLE9i
3、实验学时：
2学时。
4、实验报告。
实验准备
数据字典是oracle数据库中最重要的组成部分，它提供了数据库的一些系统信息。
数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户。
用户只能在数据字典上执行查询操作（select语句），而其维护和修改是由系统自动完成的。
实验内容
上机题1
selecttable_namefromuser_tables;
上机题2
selecttable_namefromall_tables;
上机题3
connectsystem/sys;
selecttable_namefromdba_tables;
上机题4
select*fromdba_role_privswheregrantee='SCOTT';
select*fromsystem_privilege_maporderbyname;
selectdistinctprivilegefromdba_tab_privs;
select*fromdba_roles;
selecttablespace_namefromdba_tablespaces;
上机题5
1.
a.
select*fromdba_sys_privswheregrantee='DBA';
select*fromrole_sys_privswhererole='DBA';
b.
select*fromdba_tab_privswheregrantee='DBA';
select*fromrole_tab_privswhererole='DBA';
2.
select*fromdba_roles;
3.
select*fromdba_role_privswheregrantee='SCOTT';
4.
select*fromdictwherecommentslike'%grant%';
5.
select*fromglobal_name;
实验五：
PL-SQL语言设计
实验目的
在oracle下熟练掌握PL-SQL语言的使用，包括语言语法、例外处理、函数，包等。
实验要求
1、基本配置：
IntelPentiumIII以上级别的CPU，大于512MB的内存。
2、软件要求：
WindowXP操作系统，ORACLE9i
3、实验学时：
4学时。
4、实验报告。
实验准备
pl/sql简单分类
|————过程（存储过程）
|
|————函数
块（编程）—————|
|————触发器
|
|————包
上机题1：
编写规范
1.注释
单行注释--
select*fromempwhereempno=7788;--取得员工信息
多行注释/*...*/来划分
select*fromempwhereempno=7788;--取得员工信息
select*fromglobal_name;
select*fromempwhereempno=7788;--取得员工信息
select*fromdba_sys_privswheregrantee='SCOTT'
/*
or
select*fromrole_sys_privswhererole='SCOTT';
*/
;
2.标志符号的命名规范
1）.当定义变量时，建议用v_作为前缀v_sal
2）.当定义常量时，建议用c_作为前缀c_rate
3）.当定义游标时，建议用_cursor作为后缀emp_cursor
4）.当定义例外时，建议用e_作为前缀e_error
3、pl/sql块：
块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能=-2=可能需要在一个pl/sql块中嵌套其它的pl/sql块。
pl/sql块由三个部分构成：
定义部分，执行部分，例外处理部分：
declare
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分——要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分——处理运行的各种错误*/
end;
setserveroutputon
begin
dbms_output.put_line（'欢迎使用PL/SQLDeveloper!
'）;
end;
setserveroutputon
declare
v_enamevarchar2（5）;
v_salnumber（7,2）;
begin
selectename,salintov_ename,v_salfromemp
whereempno=&no;
dbms_output.put_line（'雇员名：
'||v_ename||'薪水:
'||v_sal）;
end;
setserveroutputon
Declare
v_empnoemp.empno%Type;
v_emprecordemp%Rowtype;
Begin
Select*Intov_emprecordFromempWhereempno=&v_empno;
dbms_output.put_line（'雇员编号:
'||v_emprecord.empno）;
dbms_output.put_line（'雇员姓名:
'||v_emprecord.ename）;
dbms_output.put_line（'入职日期:
'||v_emprecord.hiredate）;
dbms_output.put_line（'职位:
'||v_emprecord.job）;
dbms_output.put_line（'管理员编号:
'||v_emprecord.mgr）;
dbms_output.put_line（'工资:
'||v_emprecord.sal）;
dbms_output.put_line（'奖金:
'||v_m）;
dbms_output.put_line（'部门编号:
'||v_emprecord.deptno）;
exception
whenno_data_found
thendbms_output.put_line（'您输入的编号有误！
'）;
end;
pl/sql块的实例
（1）
实例1-只包括执行部分的pl/sql块
Sql代码
setserveroutputon--打开输出选项
begin
dbms_output.put_line（'hello'）;
end;
相关说明：
dbms_output是oracle所提供的包（类似java的开发包），该包包含一些过程，
put_line就是dbms_output包的一个过程。
pl/sql块的实例
（2）
实例2-包含定义部分和执行部分的pl/sql块
Sql代码
declare
v_enamevarchar2（5）;--定义字符串变量
begin
selectenameintov_enamefromempwhereempno=&aa;
dbms_output.put_line（'雇员名：
'||v_ename）;
end;
/
pl/sql块的实例（3）：
包含定义部分，执行部分和例外处理部分
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误
进行处理，这个很有必要。
a.比如在实例2中，如果输入了不存在的雇员号，应当做例外处理。
b.有时出现异常，希望用另外的逻辑处理，
我们看看如何完成1的要求。
相关说明：
oracle事先预定义了一些例外，no_data_found就是找不到数据的例外。
declare
--定义变量
v_enamevarchar2（5）;
v_salnumber（7,2）;
begin
--执行部分
selectename,salintov_ename,v_salfromempwhereempno=&a
--在控制台显示用户名
dbms_output.put_line（'用户名是：
'||v_ename||'工资：
'||v_sal）;
--异常处理
exception
whenno_data_foundthen
dbms_output.put_line（'朋友，你的编号输入有误！
'）；
end;
/
上机题2函数
输入雇员的姓名，返回该雇员的年薪
createfunctionannual_incomec（namevarchar2）
returnnumberis
annual_salazynumber（7,2）;
begin
--执行部分
selectsal*12+nvl（comm,0）intoannual_salazyfromempwhereename=name;
returnannual_salazy;
end;
/
callannual_incomec（'scott'）into:
income;
printincome
createfunctionannual_income（namevarchar2）
returnnumberis
annual_salarynumber（7,2）;
begin
selectsal*12+nvl（comm,0）intoannual_salary
fromemp
whereename=name;
returnannual_salary;
end;
varincomenumber
callannual_income（'SCOTT'）into:
income;
selectannual_income（'SCOTT'）fromdual;
上机题3、包：
用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
我们可以使用createpackage命令来创建包。
实例：
--创建一个包sp_package
--声明该包有一个过程update_sal
--声明该包有一个函数annual_income
Sql代码
createpackagesp_packageis
procedureupdate_sal（namevarchar2,newsalnumber）;
functionannual_income（namevarchar2）returnnumber;
end;
包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。
包体
用于实现包规范中的过程和函数。
建立包体可以使用createpackagebody命令
--给包sp_package实现包体
Sql代码
createorreplacepackagebodysp_packageis
procedureupdate_sal（namevarchar2,newsalnumber）
is
begin
updateempsetsal=newsalwhereename=name;
end;
functionannual_income（namevarchar2）returnnumberis
annual_salarynumber;
begin
selectsal*12+nvl（comm,0）intoannual_salaryfromemp
whereename=name;
returnannual_salary;
end;
end;
/
如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方
案的包，还需要在包名前加方案名。
如：
callsp_package.update_sal（'SCOTT',1500）;
createpackagesp_packageis
procedureupdate_sal（namevarchar2,newsalnumber）;
functionannual_income（namevarchar2）returnnumber;
end;
createpackagebodysp_packageis
procedureupdate_sal（namevarchar2,newsalnumber）
is
begin
updateemp
setsal=newsal
whereename=name;
end;
functionannual_income（namevarchar2）
returnnumberis
annual_salarynumber;
begin
selectsal*12+nvl（comm,0）intoannual_salary
fromemp
whereename=name;
returnannual_salary;
end;
end;
callsp_package.update_sal（'SCOTT',4000）;
上机题4、pl/sql语句块
下面以输入员工号，显示雇员姓名、工资、个人所得税（税率为0.03）为例。
说
明变量的使用，看看如何编写。
Sql代码
declare
c_tax_ratenumber（3,2）:
=0.03;
--用户名
v_enamevarchar2（5）;
v_salnumber（7,2）;
v_tax_salnumber（7,2）;
begin
--执行
selectename,salintov_ename,v_salfromempwhereempno=&n；
--计算所得税
v_tax_sal:
=v_sal*c_tax_rate;
--输出
dbms_output.put_line（'姓名是：
'||v_ename||'工资：
'||v_sal||'交税：
'||v_tax_sal）;
end;
/
setserveroutputon
declare
c_tax_ratenumber（3,2）:
=0.03;
v_enamevarchar2（5）;
v_salnumber（7,2）;
v_tax_salnumber（7,2）;
begin
selectename,salintov_ename,v_salfromempwhereempno=&empno;
v_tax_sal:
=v_sal*c_tax_rate;
dbms_output.put_line（'姓名是：
'||v_ename||'工资：
'||v_sal||'交税：
'||v_tax_sal）;
end;
上机题5、参照变量——refcursor游标变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标
时（open时）需要指定select语句，这样一个游标就与一个select语句结合
了。
实例如下：
a.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和
他的工资。
declare
typesp_emp_cursorisrefcursor;
sp_cursorsp_emp_cursor;
v_enameemp.ename%type;
v_salemp.sal%type;
begin
opensp_cursor
for
selectename,sal
fromemp
wheredeptno=&no;
loop
fetchsp_cursorintov_ename,v_sal;
exitwhensp_cursor%notfound;
dbms_output.put_line（'name:
'||v_ename||'sal:
'||v_sal）;
endloop;
end;
b.在1的基础上，如果某个员工的工资低于200元，就添加100元。
declare
--定义游标sp_emp_cursor
typesp_emp_cursorisrefcursor;
--定义一个游标变量
test_cursorsp_emp_cursor;
--定义变量
v_enameemp.ename%type;
v_salemp.sal%type;
begin
--执行
--把test_cursor和一个select结合
opentest_cursorforselectename,salfromempwheredeptno=&no
;
--循环取出
loop
fetchtest_cursorintov_ename,v_sal;
--判断是否test_cursor为空
exitwhentest_cursor%notfound;
dbms_output.put_line（'名字：
'||v_ename||'工资：
'||v_sal）;
endloop;
end;
declare
typesp_emp_cursorisrefcursor;
sp_cursorsp_emp_cursor;
v_enameemp.ename%type;
v_salemp.sal%type;
begin
opensp_cursor
for
selectename,sal
fromemp
wheredeptno=&deptno;
loop
fetchsp_cursorintov_ename,v_sal;
if
v_sal<200
then
updateempsetsal=sal+100
whereename=v_ename;
endif;
exit
whensp_cursor%notfound;
dbms_output.put_line（'name:
'||v_ename||'sal:
'||v_sal）;
endloop;
end;
上机题6pl/sql的进阶--控制结构
请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户
编号从1开始增加。
Sql代码
createorreplaceproceduresp_pro6（spNamevarchar2）is
--定义:
=表示赋值
v_numnumber:
=1;
begin
loop
insertintousersvalues（v_num,spName）;
--判断是否要退出循环
exitwhenv_num=10;
--自增
v_num:
=v_num+1;
endloop;
end;
/
createproceduresp_pro6（spNamevarchar2）is
v_numnumber:
=1;
begin
loop
insertintousersvalues（v_num,spName）;
exitwhenv_num=10;
v_num:
=v_num+1;
endloop;
end;
上机题7、例外处理
oracle将例外分为预定义例外，非预定义例外和自定义例外三种。
预定义例外用于处理常见的oracle错误
非预定义例外用于处理预定义例外不能处理的例外
自定义例外用于处理与oracle错误无关的其它情况
预定义例外
a.预定义例外case_not_found
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外：
b.预定义例外cursor_already_open
当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open
c.预定义例外dup_val_on_index
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
d.预定义例外invalid_cursorn
当试图在不合法的游标上执行操作时，会触发该例外
e.预定义例外invalid_number
当输入的数据有误时，会触发该例外
f.预定义例外no_data_found
当执行selectinto没有返回行，就会触发该例外
1.declare
2.v_salemp.sal%type;
3.begin
4.selectsalintov_salfromemp
5.whenename='&name';
6.exception
7.whenno_data_foundthen
8.dbms_output.put_line（'不存在该员工'）；
9.end;
g.预定义例外too_many_rows
当执行selectinto语句时，如果返回超过了一行，则会触发该例外
h.预定义例外value_error
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外
i.login_denied
当用户非法登录时，会触发该例外
j.not_logged_on
如果用户没有登录就执行dml操作，就会触发该例外
k.storage_error
如果超过了内存空间或是内存被损坏，就触发该例外
l.timeout_on_resource
如果oracle在等待资源时，出现了超时就触发该例外
1.请编写一个过程，可以输入雇员编号和新工资，可修改雇员的工资。
createproceduretest_no_data_found_prc（p_empnoininteger）is
v_nameemp.ename%type;
begin
selectenameintov_namefromemp
whereempno=p_empno;
dbms_output_put_line（v_name）;
exception
whenno_d
升级会员 