--说明 CREATE OR REPLACE PACKAGE "PKG_CLEAN_RE_BAS_BOOK" IS PROCEDURE CLEAN_RE_BAS_BOOK; END;
2.在创建一个存储过程
--主体 CREATE OR REPLACE PACKAGE BODY "PKG_CLEAN_RE_BAS_BOOK" IS
PROCEDURE CLEAN_RE_BAS_BOOK IS
BEGIN
--查找重复数据 FOR curZPGHXXData IN (select a.book_no,count(1) renum from bas_book a group by a.book_no having count(a.book_no)>1) LOOP IF(curZPGHXXData.renum=2) THEN --DELETE FROM bas_book a where a.book_no=curZPGHXXData.book_no and a.author is null; DELETE FROM bas_book a where a.book_no=curZPGHXXData.book_no and a.printing is null; END IF; END LOOP; COMMIT;
-- 打印异常代码,异常堆栈 EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line(sqlerrm); dbms_output.put_line(dbms_utility.format_error_stack); END; END;
3.调试教程
这里可以看到已经创建成功的包和存储过程
显示编译成功,否则查看编译错误;查看编译错误的SQL是
select * from SYS.USER_ERRORS where NAME = upper('PKG_CLEAN_RE_BAS_BOOK');