实验五：高级plsql程序设计

《实验五：高级plsql程序设计》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、实验五：高级PL/SQL程序设计1.实验目的熟悉和掌握PL/SQL中关于存储过程、函数、包等高级程序设计技术。2.实验内容（1）在实验四的基础上，把去除表的重复数据以建立主键约束的匿名程序块，建立相应的存储过程，并学会使用OracleSQLDEVELOPER工具对存储过程进行调试。存储过程：createorreplacePROCEDUREdel_du_testASBEGINDELETEFROMtestWHERErowidnotin(SELECTmin(rowid)FROMtestGROUPBYpaperid);ENDdel_du_test;执行存储过程：SETSERVEROUTONbegin

2、del_du_test;end;存储过程：createorreplacePROCEDUREdel_du_cursor_testASvar_paperidtest.paperid%TYPE;CURSORtest_cursorISSELECTpaperidFROMtestGROUPBYpaperidHAVINGcount(paperid)>1;BEGINDELETEFROMtestWHERErowidnotin(SELECTmin(rowid)FROMtestGROUPBYpaperid);ENDdel_du_cursor_test;执行存储过程：SETSERVEROUTONbegindel_d

3、u_cursor_test;end;（1）把（1）中实现的存储过程，改用函数实现。CREATEORREPLACEFUNCTIONf_del_du_testRETURNVARCHAR2ASBEGINDELETEFROMtestWHERErowidnotin(SELECTmin(rowid)FROMtestGROUPBYpaperid);RETURN'Duplicatedrowsdeleted';ENDf_del_du_test;DECLAREl_strVARCHAR2(100):=NULL;BEGINl_str:=f_del_du_test();DBMS_OUTPUT.PUT_LINE(l_s

4、tr);END;CREATEORREPLACEFUNCTIONf_del_du_cursor_testRETURNVARCHAR2ASvar_paperidtest.paperid%TYPE;CURSORtest_cursorISSELECTpaperidFROMtestGROUPBYpaperidHAVINGcount(paperid)>1;BEGINOPENtest_cursor;LOOPFETCHtest_cursorINTOvar_paperid;EXITWHENtest_cursor%NOTFOUND;DELETEFROMtestWHEREpaperid=var_paperidan

5、drowid<>(SELECTmin(rowid)FROMtestWHEREpaperid=var_paperid);ENDLOOP;CLOSEtest_cursor;RETURN'Duplicatedrowsdeleted';ENDf_del_du_cursor_test;DECLAREl_strVARCHAR2(100):=NULL;BEGINl_str:=f_del_du_cursor_test();DBMS_OUTPUT.PUT_LINE(l_str);END;（1）建立一个程序包，包含（1）和（2）中实现的存储过程和函数，并通过包来调用相应的存储过程和函数。包头：CREATEORR

6、EPLACEPACKAGEdel_duASPROCEDUREdel_du_test;PROCEDUREdel_du_cursor_test;FUNCTIONF_DEL_DU_TESTRETURNVARCHAR2;FUNCTIONF_DEL_DU_CURSOR_TESTRETURNVARCHAR2;ENDdel_du;包体：createorreplacepackagebody"DEL_DU"isfunctionF_DEL_DU_CURSOR_TESTreturnVARCHAR2asvar_paperidtest.paperid%TYPE;CURSORtest_cursorISSELECTpap

7、eridFROMtestGROUPBYpaperidHAVINGcount(paperid)>1;BEGINOPENtest_cursor;LOOPFETCHtest_cursorINTOvar_paperid;EXITWHENtest_cursor%NOTFOUND;DELETEFROMtestWHEREpaperid=var_paperidandrowid<>(SELECTmin(rowid)FROMte