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

实验五：高级PL/SQL程序设计 实验目的 熟悉和掌握PL/SQL中关于存储过程、函数、包等高级程序设计技术。 实验内容 在实验四的基础上，把去除表的重复数据以建立主键约束的匿名程序块，建立相应的存储过程，并学会使用Oracle SQL DEVELOPER工具对存储过程进行调试。 存储过程： create or replace PROCEDURE del_du_test AS BEGIN DELETE FROM test WHERE rowid not in (SELECT min(rowid) FROM test GROUP BY paperid); END del_du_test; 执行存储过程： SET SERVEROUT ON begin del_du_test; end; 存储过程： create or replace PROCEDURE del_du_cursor_test AS var_paperid test.paperid%TYPE; CURSOR test_cursor IS SELECT paperid FROM test GROUP BY paperid HAVING count(paperid)1; BEGIN DELETE FROM test WHERE rowid not in (SELECT min(rowid) FROM test GROUP BY paperid); END del_du_cursor_test; 执行存储过程： SET SERVEROUT ON begin del_du_cursor_test; end; 把（1）中实现的存储过程，改用函数实现。 CREATE OR REPLACE FUNCTION f_del_du_test RETURN VARCHAR2 AS BEGIN 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 VARCHAR2 AS var_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; 建立一个程序包，包含（1）和（2）中实现的存储过程和函数，并通过包来调用相应的存储过程和函数。 包头： CREATE OR REPLACE PACKAGE del_du AS PROCEDURE del_du_test; PROCEDURE del_du_cursor_test; FUNCTION F_DEL_DU_TEST RETURN VARCHAR2; FUNCTION F_DEL_DU_CURSOR_TEST RETURN VAR