Oracle数据库试题.docx
《Oracle数据库试题.docx》由会员分享，可在线阅读，更多相关《Oracle数据库试题.docx（13页珍藏版）》请在冰豆网上搜索。
Oracle数据库试题
constraintpk_spjprimarykey（sno,pno,jno）,
constraintfk_spj_snoforeignkey（sno）referencess（sno）,
constraintfk_spj_pnoforeignkey（pno）referencesp（pno）,
constraintfk_spj_jnoforeignkey（jno）referencesj（jno）
实验二游标和函数
1、定义一个游标完成显示所有供应商名。
declare
v_snames.sname%type;
cursorcursor_snameisselectsnamefroms;
begin
forcursoincursor_sname
loop
dbms_output.put_line（curso.sname）;
endloop;end;
2、定义、调用一个简单函数：
查询返回指定供应商编号的供应商名及其供应零件总数量。
createorreplacefunctionfun（f_snoins.sno%type,f_snameouts.sname%type）
returnnumberasf_qtynumber;
begin
SELECTs.sname,sum（qty）intof_sname,f_qtyfroms,spjWHEREs.sno=spj.snoGROUPBYs.sname,spj.snohavingspj.sno=f_sno;
returnf_qty;end;
declare
v_snos.sno%type:
='&sno';
v_snames.sname%type;
v_qtyspj.qty%type;
begin
v_qty:
=fun（v_sno,v_sname）;
dbms_output.put_line（v_sname||v_qty）;
end;
3、定义一个函数：
对于给定的供应商号，判断是否存在，若存在返回0，否则返回-1。
写一段程序调用此函数，若供应商号存在则在spj插入一元组。
createorreplacefunctionfun1（f1_snoins.sno%type）
returnnumberasanumber;
begin
selectcount（sno）intoafromswheresno=f1_sno;
ifa=0then
return-1;
elsereturn0;
endif;end;
declare
v_snos.sno%type:
='&sno';
begin
iffun1（v_sno）=0then
insertintospjvalues（v_sno,'P4','J5',120）;
endif;end;
select*fromspjwheresno='S1';
4、定义、调用一个类似于SUM功能的函数：
计算指定供应商编号的供应零件总数量。
createorreplacefunctionfsum（fs_snoins.sno%type）
returnnumberassummnumber;
cursorcursor_snoisselectqtyfroms,spjwheres.sno=fs_snoandspj.sno=s.sno;
begin
summ:
=0;
forcursoincursor_snoloop
summ:
=summ+curso.qty;
endloop;
returnsumm;end;
declare
v_snos.sno%type:
='&sno';
cnumber;
begin
c:
=fsum（v_sno）;
dbms_output.put_line（c）;
end;
5、将题2中函数改用包定义。
createorreplacepackagepack
Isfunctionfun（f_snoins.sno%type,f_snameouts.sname%type）
returnnumber;end;
createorreplacepackagebodypack
Isfunctionfun（f_snoins.sno%type,f_snameouts.sname%type）
returnnumberasf_qtynumber;
begin
SELECTs.sname,sum（qty）intof_sname,f_qtyfroms,spjWHEREs.sno=spj.snoGROUPBYs.sname,spj.snohavingspj.sno=f_sno;
returnf_qty;endfun;end;
declare
v_snos.sno%type:
='&sno';
v_snames.sname%type;
v_qtyspj.qty%type;
begin
v_qty:
=pack.fun（v_sno,v_sname）;
dbms_output.put_line（v_sname||v_qty）;
end;
实验三存储过程
1、定义、调用简单存储过程：
计算所有供应商供应零件总数量并修改供应商相关列sqty。
createorreplaceprocedurepro1
Asp_qtynumber;
cursorcur1isselectsno,sum（qty）asp_qtyfromspjgroupbyspj.sno;
begin
forcincur1loop
updatessets.sqty=c.p_qtywheresno=c.sno;
endloop;end;
begin
pro1;end;
2、定义、调用参数存储过程：
查询返回指定供应商的供应零件总数量。
比较与函数不同。
createorreplaceprocedurepro2（p_snoins.sno%type,p_qtyoutspj.qty%type）
Asbegin
selectsum（qty）intop_qtyfromspjWHEREspj.sno=p_snoGROUPBYspj.sno;
dbms_output.put_line（'供应商'||p_sno||'的总数量为：
'||p_qty）;
end;
declare
v_snos.sno%type:
='&sno';
v_qtyspj.qty%type;
Begin
pro2（v_sno,v_qty）;
end;
3、定义、调用存储过程：
插入一个供应商信息（所有信息由参数提供）。
createorreplaceprocedurepro3（p_snos.sno%type,p_snames.sname%type,p_statuss.status%type,p_citys.city%type）
Ascoutnumber;
begin
selectcount（*）intocoutfromswheres.sno=p_sno;
ifcout>0then
dbms_output.put_line（'编号为'||p_sno||'的供应商已存在！
'）;
elsifcout=0then
insertintos（sno,sname,status,city）values（p_sno,p_sname,p_status,p_city）;
dbms_output.put_line（'插入成功！
'）;
elsedbms_output.put_line（'出现其它错误！
'）;
endif;end;
declare
v_snos.sno%type:
='&sno';
v_snames.sname%type:
='&sname;
v_statuss.status%type:
='&status';
v_citys.city%type:
='&city';
begin
pro3（v_sno,v_sname,v_status,v_city）;
end;
select*froms;
4、定义、调用存储过程：
删除指定代码的零件信息，并给出删除元组数。
createorreplaceprocedurepro4（p_pnoinp.pno%type,p_cutoutnumber）
Asbegin
deletefromspjwherespj.pno=p_pno;
deletefrompwherepno=p_pno;
p_cut:
=SQL%ROWCOUNT;
dbms_output.put_line（'已经删除'||p_cut||'行'）;
end;
declare
v_pnop.pno%type:
='&pno';
v_cutnumber;
begin
pro4（v_pno,v_cut）;
end;
5、定义、调用存储过程：
修改指定代码项目的其它信息（所有信息由参数提供）。
createorreplaceprocedurepro5（p_jnoinj.jno%type,p_jnamej.jname%type,p_cityj.city%type）
Asp_coutnumber;
begin
selectcount（*）intop_coutfromjwherejno=p_jno;
ifp_cout=0then
dbms_output.put_line（'编号'||p_jno||'的项目不存在'）;
elseupdatejsetjname=p_jname,city=p_citywherejno=p_jno;
dbms_output.put_line（'修改成功！
'）;
endif;end;
declare
v_jnoj.jno%type:
='&jno';
v_jnamej.jname%type:
='&jname';
v_cityj.city%type:
='&city';
begin
pro5（v_jno,v_jname,v_city）;
end;
select*fromj;
实验四触发器
1、定义一个触发器，完成及时计算所有供应商供应零件总数量。
createorreplacetriggert_sno
afterinsertorupdateordeleteonspj
declare
v_countnumber;
cursorcur1isselectsno,sum（qty）asv_countfromspjgroupbysno;
beginforcincur1loop
updatessets.sqty=c.v_countwheresno=c.sno;
endloop;end;
updatespjsetqty=122wheresno='S1'andpno='P1'andjno='J1';
selectsno,sqtyfroms;
2、定义触发器，实现实体完整性（以s表供应商代码sno为例）。
createorreplacetriggert2
afterinsertons
declare
v_snos.sno%type;v_countnumber;
cursorcur2isselectsnofromsgroupbysnohavingcount（*）>1;
begin
selectcount（*）intov_countfromswheresnoisnull;
ifv_count<0then
raise_application_error（-20001,'sno为NULL'）;
endif;
opencur2;
fetchcur2intov_sno;
ifcur2%foundthen
raise_application_error（-20002,'sno已存在'）;
endif;closecur2;end;
insertintos（sno,sname,status,city）values（null,'b',10,'c'）;
3、定义触发器，实现参照完整性（以spj表供应商代码sno参照s表供应商代码sno为例）。
--当在SPJ表插入数据时，如果S,P,J表不存在相应的记录时，则插入失败
createorreplacetriggert3
beforeinsertorupdateofsno,jno,pnoonspj
foreachrow
declare
v_snonumber;
begin
selectcount（*）intov_snofromswheresno=:
new.sno;
ifv_sno<1then
raise_application_error（-20001,'编号'||to_char（:
new.sno）||'不存在'）;
endif;
selectcount（*）intov_pnofrompwherepno=:
new.pno;
endif;end;
insertintospj（sno,pno,jno,qty）values（'S5','P10','J4',500）;
--当删除或更新S表记录时，当SPJ表上有引用时抛出异常
createorreplacetriggert4
beforedeleteorupdateons
foreachrow
declarev_countnumber;
begin
selectcount（*）intov_countfromspjwheresno=:
old.sno;
ifv_count>0then
raise_application_error（-20001,'无法删除编号'）;--||to_char（:
old.sno）||'的项目'
endif;end;
deletefromswheresno='S1';
select*froms;
--级联删除,删除S表中的记录时，同时删除SPJ表中的记录
createorreplacetriggert5
beforedeleteons
foreachrow
declarev_countnumber;
begin
selectcount（*）intov_countfromspjwheresno=:
old.sno;
ifv_count>1then
deletefromspjwheresno=:
old.sno;
endif;end;
deletefromswheresno='S1';
select*froms
--级联更新，更新S表中的SNO时，同时更新SPJ表中的SNO记录
createorreplacetriggert6
beforeupdateons
foreachrow
declarev_countnumber;
begin
selectcount（*）intov_countfromspjwheresno=:
old.sno;
ifv_count>1then
updatespjsetsno=:
new.snowheresno=:
old.sno;
endif;end;
updatessetsno='S9'wheresno='S2';
select*froms
实验五高级技术
1、定义序列并完成基本表spj重新定义。
createsequencespj_test
incrementby1startwith1;
createtabletest1（
snonumber,
pnochar（3）,
jnochar（3）,
qtynumber）;
insertintotest1values（spj_test.nextval,'P1','J1',222）;
insertintotest1values（spj_test.nextval,'P1','J1',333）;
insertintotest1values（spj_test.nextval,'P1','J1',444）;
insertintotest1values（spj_test.nextval,'P1','J1',555）;
insertintotest1values（spj_test.nextval,'P1','J1',666）;
select*fromtest1;
selectspj_test.nextvalfromdual;
3、数据库安全性
（1）定义用户TESTUSER，授予s表查询权限，观察授权前后不同情况。
--System下执行
createusertestuser
identifiedbyoracle
defaulttablespaceusers
temporarytablespacetemp;
grantcreatesessiontotestuser;--授予会话权限，从而可以用此用户登录
revokecreatesessionfromtestuser;
grantselectonhr.stotestuser;
revokeselectfromtestuser;
--用户testuser下执行
select*fromhr.s;
grantcreatetabletotestuser;
（2）定义角色TESTROLE，并授予存储过程执行权限，将用户TESTUSER加入；观察加入前后不同情况。
droproletextrole;
createroletestrole;
grantexecuteanyproceduretotestrole;
granttestroletotestuser;
revoketestrolefromtestuser;
--用户hr下执行
createorreplaceprocedurepro
ascursorcur_snameisselectsnamefroms;
begin
forcurincur_snameloop
dbms_output.put_line（cur.sname）;
endloop;end;
--用户testuser重新登录后执行，
begin
hr.pro;
end;
4、BLOB数据类型定义及其数据导入（以p表零件图片为例）
--管理员用户下执行
createtableimage
（employeeidchar（6）notnullprimarykey,
pictureblob）;
createdirectory"image_dir"as'D:
\test';
createorreplaceprocedureimg_insert（p_numchar,filenamevarchar2）
Asf_lobbfile;b_lobblob;p_lnnumber;
begin
insertintoimagevalues（p_num,empty_blob（））;
selectpictureintob_lobfromimagewhereemployeeid=p_num;
f_lob:
=bfilename（'image_dir',filename）;
p_ln:
=dbms_lob.getlength（f_lob）;
dbms_lob.fileopen（f_lob,dbms_lob.file_readonly）;
dbms_lob.loadfromfile（b_lob,f_lob,p_ln）;
dbms_lob.fileclose（f_lob）;
commit;
end;
begin
img_insert（'S1','1.jpg'）;
end;
select*fromimage;
5、将数据增加到1万以上，在spj定义包括sno、pno、jno索引，对同一查询观察前后的时间变化。
createindexidx_spj1onspj1（sno,pno,jno）;
begin
foriin1..10000loop
insertintospj1values（i,i,i,i）;
endloop;end;
select*fromspj1;
select*fromspj1wheresno=10000;
升级会员 