1、实验五：高级 PL/SQL 程序设计1. 实验目的熟悉和掌握 PL/SQL 中关于存储过程、函数、包等高级程序设计技术。2. 实验内容（1） 在实验四的基础上，把去除表的重复数据以建立主键约束的匿名程序块，建立相应的存储过程，并学会使用 Oracle SQL DEVELOPER 工具对存储过程进行调试。存储过程：create or replace PROCEDURE del_du_test ASBEGINDELETE FROM testWHERE rowid not in(SELECT min(rowid)FROM testGROUP BY paperid);END del_du_test;执
2、行存储过程：SET SERVEROUT ONbegindel_du_test;end;存储过程：create or replace PROCEDURE del_du_cursor_test ASvar_paperid test.paperid%TYPE;CURSOR test_cursor ISSELECT paperidFROM testGROUP BY paperidHAVING count(paperid)1;BEGINDELETE FROM testWHERE rowid not in(SELECT min(rowid)FROM testGROUP BY paperid);END de
3、l_du_cursor_test;执行存储过程：SET SERVEROUT ONbegindel_du_cursor_test;end;（2） 把（1）中实现的存储过程，改用函数实现。CREATE OR REPLACE FUNCTION f_del_du_test RETURN VARCHAR2ASBEGINDELETE FROM testWHERE rowid not in(SELECT min(rowid)FROM testGROUP BY paperid);RETURN Duplicated rows deleted;END f_del_du_test;DECLAREl_str VARC
4、HAR2(100) := NULL;BEGINl_str := f_del_du_test();DBMS_OUTPUT.PUT_LINE(l_str);END;CREATE OR REPLACE FUNCTION f_del_du_cursor_test RETURN VARCHAR2ASvar_paperid test.paperid%TYPE;CURSOR test_cursor ISSELECT paperidFROM testGROUP BY paperidHAVING count(paperid)1;BEGINOPEN test_cursor;LOOPFETCH test_curso
5、r INTO var_paperid;EXIT WHEN test_cursor%NOTFOUND;DELETE FROM testWHERE paperid = var_paperid and rowid 1;BEGINOPEN test_cursor;LOOPFETCH test_cursor INTO var_paperid;EXIT WHEN test_cursor%NOTFOUND;DELETE FROM testWHERE paperid = var_paperid and rowid 1;BEGINDELETE FROM testWHERE rowid not in(SELECT
6、 min(rowid)FROM testGROUP BY paperid);end DEL_DU_CURSOR_TEST;function F_DEL_DU_TEST return VARCHAR2asbeginDELETE FROM testWHERE rowid not in(SELECT min(rowid)FROM testGROUP BY paperid);RETURN PKG_FUNC - Duplicated rows deleted;end F_DEL_DU_TEST;procedure DEL_DU_TESTasbeginDELETE FROM testWHERE rowid
7、 not in(SELECT min(rowid)FROM testGROUP BY paperid);end DEL_DU_TEST;end DEL_DU;调用：DECLAREl_str VARCHAR2(100) := NULL;BEGINl_str := del_du.f_del_du_cursor_test();DBMS_OUTPUT.PUT_LINE(l_str);END;DECLAREl_str VARCHAR2(100) := NULL;BEGINl_str := del_du.f_del_du_test();DBMS_OUTPUT.PUT_LINE(l_str);END;begindel_du.del_du_cursor_test;end;begindel_du.del_du_test;end;3. 实验总结通过本次试验，我熟悉了 PL/SQL 中关于存储过程、函数、包等高级程序设计技术，同时对存储过程、函数、程序包有了更加深入的认识。当我做出一个代码段，并且执行成后之后，我有了成就感，这也使我对这门课程产生了更大的兴趣。课后仍需多加实践，多加思考。
