Oracle实验代码.doc
- 文档编号:1213269
- 上传时间:2023-04-30
- 格式:DOC
- 页数:10
- 大小:36KB
Oracle实验代码.doc
《Oracle实验代码.doc》由会员分享,可在线阅读,更多相关《Oracle实验代码.doc(10页珍藏版)》请在冰点文库上搜索。
游标与函数的使用
1、定义一个游标完成显示所有供应商名。
DECLARE
v_snames.sname%TYPE;
CURSORcu_snameISSELECTsnameFROMs;
BEGIN
FORcurINcu_snameLOOP
dbms_output.put_line(cur.sname);
ENDLOOP;
END;
2、定义、调用一个简单函数:
查询返回指定供应商编号的供应商名及其供应零件总数量。
CREATEORREPLACEFUNCTIONcx(cx_snoINs.sno%TYPE,cx_snameOUTs.sname%TYPE)
RETURNNUMBER
IS
V_sqtyNUMBER;
BEGIN
SELECTsname,SUM(qty)INTOcx_sname,V_sqtyFROMs,spjWHEREs.sno=spj.snoGROUPBYspj.sno,s.snameHAVINGspj.sno=cx_sno;
RETURNV_sqty;
ENDcx;
DECLARE
V_snos.sno%TYPE:
='&sno';
V_sqtys.sqty%TYPE;
V_snames.sname%TYPE;
BEGIN
V_sqty:
=cx(V_sno,V_sname);
dbms_output.put_line(V_sname||'供应商'||V_sno||'所供应的零件总数为:
'||V_sqty);
END;
3、定义一个函数:
对于给定的供应商号,判断是否存在,若存在返回0,否则返回-1。
写一段程序调用此函数,若供应商号存在则在spj插入一元组。
CREATEORREPLACEFUNCTIONCZ(cx_snoINs.sno%TYPE)
RETURNNUMBER
IS
jg_snoNUMBER;
BEGIN
SELECTCOUNT(*)INTOjg_snoFROMsWHEREs.sno=cx_sno;
IFjg_sno=0
THEN
RETURN(-1);
ELSE
RETURN0;
endIF;
END;
DECLARE
C_snos.sno%TYPE:
='&sno';
hfNUMBER;
BEGIN
hf:
=CZ(C_sno);
IFhf=0
THEN
dbms_output.put_line(C_sno||'存在');
--INSERTINTOspjVALUES(C_sno,'');
ELSE
dbms_output.put_line(C_sno||'不存在');
ENDIF;
END;
4、定义、调用一个类似于SUM功能的函数:
计算指定供应商编号的供应零件总数量。
CREATEORREPLACEFUNCTIONfun_sum(v_snoINs.sno%TYPE)
RETURNNUMBER
IS
CURSORcur_qtyISSELECTqtyFROMspjWHEREsno=v_sno;
v_sqtyNUMBER:
=0;
tempNUMBER;
BEGIN
FORtempINcur_qtyLOOP
v_sqty:
=v_sqty+temp.qty;
ENDLOOP;
returnv_sqty;
END;
DECLARE
v_snos.sno%TYPE:
='&sno';
v_sqtys.sqty%TYPE;
BEGIN
v_sqty:
=fun_sum(v_sno);
dbms_output.put_line('供应商号'||v_sno||'所供应的零件总数为:
'||v_sqty);
END;
5、将题2中函数改用包定义。
CREATEORREPLACEPACKAGEpkage_spj
IS
FUNCTIONfun_sno(s_snoINs.sno%TYPE,s_snameOUTs.sname%TYPE)
RETURNNUMBER;
endpkage_spj;
CREATEORREPLACEPACKAGEBODYpkage_spj
IS
FUNCTIONfun_sno(s_snoINs.sno%TYPE,s_snameOUTs.sname%TYPE)
RETURNNUMBER
AS
v_sqtyNUMBER;
BEGIN
SELECTsname,SUM(qty)INTOs_sname,v_sqtyFROMs,spjWHEREs.sno=spj.snoGROUPBYspj.sno,s.snameHAVINGspj.sno=s_sno;
RETURNv_sqty;
END;
endpkage_spj;
DECLARE
v_snos.sno%TYPE:
='S1';
v_snames.sname%TYPE;
v_sqtyspj.qty%TYPE;
BEGIN
v_sqty:
=pkage_spj.fun_sno(v_sno,v_sname);
dbms_output.put_line(v_sname||'供应商'||v_sno||'所供应的零件总数为:
'||v_sqty);
END;
存储过程的使用
1、定义、调用简单存储过程:
计算所有供应商供应零件总数量并修改供应商相关列sqty。
CREATEORREPLACEPROCEDUREp1
AS
cursorcur_1
is
selectsno,sum(qty)assumqtyfromspjgroupbysno;
BEGIN
forcurincur_1loop
updatessetsqty=cur.sumqtywheres.sno=cur.sno;
endloop;
END;
begin
p1;
end;
2、定义、调用参数存储过程:
查询返回指定供应商的供应零件总数量。
比较与函数不同。
CREATEORREPLACEprocedurep2(sno_snoINs.sno%type,s_snameOUTs.sname%type,v_sqtyouts.sqty%type)
IS
BEGIN
SELECTsname,SUM(qty)INTOs_sname,v_sqtyFROMs,spjWHEREs.sno=spj.snoGROUPBYspj.sno,s.snameHAVINGspj.sno=sno_sno;
end;
DECLARE
v_snos.sno%TYPE:
='S1';
v_sqtys.sqty%TYPE;
v_snames.sname%TYPE;
BEGIN
pro2(v_sno,v_sname,v_sqty);
dbms_output.put_line(v_sname||'供应商'||v_sno||'所供应的零件总数为:
'||v_sqty);
END;
3、定义、调用存储过程:
插入一个供应商信息(所有信息由参数提供)。
createorreplaceprocedurep3
(s_snoins.sno%type,s_snameins.sname%type,s_statusins.status%type,s_cityins.city%type)
is
v_countnumber;
begin
selectcount(sno)intov_countfromswheres.sno=s_sno;
if(v_count>0)then
dbms_output.put_line('插入的供应商存在');
else
insertintos(sno,sname,status,city)values(s_sno,s_sname,s_status,s_city);
endif;
end;
declare
v_snos.sno%type:
='S6';
v_snames.sname%type:
='科院';
v_statuss.status%type:
=50;
v_citys.city%type:
='十堰';
begin
pro3(v_sno,v_sname,v_status,v_city);
end;
4、定义、调用存储过程:
删除指定代码的零件信息,并给出删除元组数。
CREATEORREPLACEPROCEDUREproc4(p_pnop.pno%type,p_countoutnumber)
is
v_countnumber;
begin
deletefromspjwherespj.pno=p_pno;
deletefrompwherepno=p_pno;
v_count:
=sql%rowcount;
p_count:
=v_count;
end;
declare
v_pnop.pno%type:
='&pno';
v_countnumber;
begin
proc4(v_pno,v_count);
dbms_output.put_line('删除元组的数目为:
'||v_count);
end;
5、定义、调用存储过程:
修改指定代码项目的其它信息(所有信息由参数提供)。
CREATEORREPLACEPROCEDUREproc5(p_pnop.pno%type,p_pnamep.pname%type,p_colorp.color%type,p_weightp.weight%type)
is
v_countnumber:
=0;
begin
selectcount(p_pno)intov_countfrompwherep.pno=p_pno;
if(v_count=0)
then
dbms_output.put_line('你所要修改的零件号不存在...');
else
updatepsetpname=p_pname,color=p_color,weight=p_weightwherep.pno=p_pno;
endif;
end;
declare
v_pnop.pno%type:
='&pno';
v_pnamep.pname%type:
='螺丝刀';
v_colorp.color%type:
='红';
v_weightp.weight%type:
=15;
begin
proc5(v_pno,v_pname,v_color,v_weight);
end;
触发器的使用
1、定义一个触发器,完成及时计算所有供应商供应零件总数量。
`
createorreplacetriggert1_spj
afterinsertordeleteorupdateonspj
declare
cursorcur_1isselectsno,sum(qty)assumqtyfromspjgroupbysno;
begin
forcurincur_1loop
updatessetsqty=cur.sumqtywheres.sno=cur.sno;
endloop;
endt1_spj;
insertintospjvalues('S5','P6','J4',700);
2、定义触发器,实现实体完整性(以s表供应商代码sno为例)。
createorreplacetriggert2_spj
afterinsertons
declare
v_snos.sno%type;
v_countnumber;
cursorcur_snoisselectsnofromsgroupbysnohavingcount(*)>1;
begin
selectcount(*)intov_countfromswheresnoisnull;
ifv_count>0then
raise_application_error(-20008,'主码sno不能取空值');
endif;
opencur_sno;
fetchcur_snointov_sno;
ifcur_sno%foundthen
raise_application_error(-20012,'主码sno不能重复');
endif;
endt2_spj;
insertintoS(SNO,SNAME,STATUS,CITY)values('S1','竟仪',20,'天津');
3、定义触发器,实现参照完整性(以spj表供应商代码sno参照s表供应商代码sno为例)。
--当在SPJ表插入数据时,如果S,P,J表不存在相应的记录时,则插入失败
createorreplacetriggertr1_spj
beforeinsertorupdateofsno,pno,jno
onspj
foreachrow
declare
v_count1number;
v_count2number;
v_count3number;
begin
selectcount(*)intov_count1fromswheresno=:
new.sno;
ifv_count1<1then
raise_application_error(-20001,'供应商编号为'||to_char(:
new.sno)||'不存在');
endif;
selectcount(*)intov_count2frompwherepno=:
new.pno;
ifv_count2<1then
raise_application_error(-20002,'供应商编号为'||to_char(:
new.pno)||'不存在');
endif;
selectcount(*)intov_count3fromjwherejno=:
new.jno;
ifv_count3<1then
raise_application_error(-20003,'供应商编号为'||to_char(:
new.jno)||'不存在');
endif;
endtr1_spj;
insertintospjvalues('S10','P9','J4',500);
--当删除或更新S表记录时,当SPJ表上有引用时抛出异常
createorreplacetriggertr2_spj
beforedeleteorupdateofsno
ons
foreachrow
declare
v_countnumber;
begin
selectcount(*)intov_countfromspjwheresno=:
old.sno;
ifv_count>1then
raise_application_error(-20005,'供应商编号为'||to_char(:
new.sno)||'在spj表中有引用');
endif;
endtr2_spj;
deletefromswheres.sno='S1';
--级联删除,删除S表中的记录时,同时删除SPJ表中的记录
createorreplacetriggertr3_spj
afterdelete
ons
foreachrow
declare
begin
deletefromspjwherespj.sno=:
old.sno;
endtr3_spj;
deletefromswheres.sno='S1';
--级联更新,更新S表中的SNO时,同时更新SPJ表中的SNO记录
createorreplacetriggertr4_spj
afterupdateofsno
ons
foreachrow
declare
begin
updatespjsetsno=:
new.snowheresno=:
old.sno;
endtr3_spj;
updatessetsno='S9'wheresno='S1';
Oracle高级技术
1、定义序列并完成基本表spj重新定义。
createsequences_test
incrementby1startwith1;
createtabletest(
snonumber,
snamevarchar2(10)
);
insertintotestvalues(s_test.nextval,'aa');
insertintotestvalues(s_test.nextval,'bb');
insertintotestvalues(s_test.nextval,'cc');
select*fromtest
selects_test.nextvalfromdual;
3、数据库安全性
(1)定义用户TESTUSER,授予s表查询权限,观察授权前后不同情况。
createusertestuser
identifiedbyoracle
defaulttablespaceusers
temporarytablespacetemp;
grantcreatesessiontotestuser;
revokecreatesessionfromtestuser;
grantselectonhr.stotestuser;
revokeselectonhr.sfromtestuser;
(2)定义角色TESTROLE,并授予存储过程执行权限,将用户TESTUSER加入;观察加入前后不同情况。
createroletestrole;
grantexecuteanyproceduretotestrole;
granttestroletotestuser;
revoketestrolefromtestuser;
createorreplaceprocedureproc_sname
as
cursorcur_snameisselectsnamefroms;
begin
forrecincur_snameloop
dbms_output.put_line(rec.sname);
endloop;
end;
begin
hr.proc_sname;
end;
4、BLOB数据类型定义及其数据导入(以p表零件图片为例)
droptablep_photo;
createtablep_photo(
pnochar
(2)primarykey,
zpblob
);
createdirectory"mydir"as'd:
\test';
createorreplaceprocedureimg_insert(p_pnop_photo.pno%type,filenamevarchar2)
as
f_lobbfile;
b_lobblob;
lennumber;
begin
insertintop_photo(pno,zp)values(p_pno,empty_blob());
selectzpintob_lobfromp_photowherepno=p_pno;
f_lob:
=bfilename('mydir',filename);
len:
=dbms_lob.getlength(f_lob);
dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,f_lob,len);
dbms_lob.fileclose(f_lob);
commit;
end;
begin
img_insert('S1','1.jpg');
end;
select*fromp_photo;
5、将数据增加到1万以上,在spj定义包括sno、pno、jno索引,对同一查询观察前后的时间变化。
droptablespj;
createtablespj(
snonumber,
pnonumber,
jnonumber,
qtynumber
);
createindexidx_spjonspj(sno,pno,jno);
begin
foriin1..100000loop
insertintospjvalues(i,i,i,i);
endloop;
end;
select*fromspj;
select*fromspjwheresno=10010;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 实验 代码