实验五：高级PLSQL程序设计.doc

实验五：高级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 test GROUP BY paperid);END del_du_test;执行存储过程：SET SERVEROUT ONbegindel_du_test;end;存储过程：create or replace PROCEDURE del_du_cursor_test ASvar_paperid test.paperid%TYPE;CURSOR test_cursor IS SELECT paperid FROM test GROUP BY paperid HAVING count(paperid)1;BEGINDELETE FROM testWHERE rowid not in (SELECT min(rowid) FROM test GROUP BY paperid);END del_du_cursor_test;执行存储过程：SET SERVEROUT ONbegindel_du_cursor_test;end;（2） 把（1）中实现的存储过程，改用函数实现。CREATE OR REPLACE FUNCTION f_del_du_test RETURN VARCHAR2ASBEGIN DELETE FROM test WHERE rowid not in (SELECT min(rowid) FROM test GROUP BY paperid); RETURN Duplicated rows deleted;END f_del_du_test;DECLARE l_str VARCHAR2(100) := NULL;BEGIN l_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 IS SELECT paperid FROM test GROUP BY paperid HAVING count(paperid)1;BEGIN OPEN test_cursor; LOOP FETCH test_cursor INTO var_paperid; EXIT WHEN test_cursor%NOTFOUND; DELETE FROM test WHERE paperid = var_paperid and rowid (SELECT min(rowid) FROM test WHERE paperid = var_paperid); END LOOP; CLOSE test_cursor;RETURN Duplicated rows deleted;END f_del_du_cursor_test;DECLARE l_str VARCHAR2(100) := NULL;BEGIN l_str := f_del_du_cursor_test(); DBMS_OUTPUT.PUT_LINE(l_str);END;（3） 建立一个程序包，包含（1）和（2）中实现的存储过程和函数，并通过包来调用相应的存储过程和函数。包头：CREATE OR REPLACE PACKAGE del_duAS PROCEDURE del_du_test; PROCEDURE del_du_cursor_test; FUNCTION F_DEL_DU_TEST RETURN VARCHAR2; FUNCTION F_DEL_DU_CURSOR_TEST RETURN VARCHAR2;END del_du;包体：create or replace package body DEL_DU isfunction F_DEL_DU_CURSOR_TEST return VARCHAR2asvar_paperid test.paperid%TYPE;CURSOR test_cursor IS SELECT paperid FROM test GROUP BY paperid HAVING count(paperid)1;BEGIN OPEN test_cursor; LOOP FETCH test_cursor INTO var_paperid; EXIT WHEN test_cursor%NOTFOUND; DELETE FROM test WHERE paperid = var_paperid and rowid (SELECT min(rowid) FROM test WHERE paperid = var_paperid); END LOOP; CLOSE test_cursor;RETURN PKG_FUNC - Duplicated rows deleted;end F_DEL_DU_CURSOR_TEST;procedure DEL_DU_CURSOR_TESTasvar_paperid test.paperid%TYPE;CURSOR test_cursor IS SELECT paperid FROM test GROUP BY paperid HAVING count(paperid)1;BEGINDELETE FROM testWHERE rowid not in (SELECT min(rowid) FROM test GROUP BY paperid);end DEL_DU_CURSOR_TEST;function F_DEL_DU_TEST return VARCHAR2asbegin DELETE FROM test WHERE rowid not in (SELECT min(rowid) FROM test GROUP BY paperid); RETURN PKG_FUNC - Duplicated rows deleted;end F_DEL_DU_TEST;procedure DEL_DU_TESTasbegin DELETE FROM testWHERE rowid not in (SELECT min(rowid) FROM test GROUP BY paperid);end DEL_DU_TEST;end DEL_DU;调用：DECLARE l_str VARCHAR2(100) := NULL;BEGIN l_str := del_du.f_del_du_cursor_test(); DBMS_OUTPUT.PUT_LINE(l_str);END;DECLARE l_str VARCHAR2(100) := NULL;BEGIN l_str := del_du.f_del_du_test(); DBMS_OUTPUT.PUT_LINE(l_str);END;begindel_du.del_du_cursor