tailieunhanh - Oracle Built−in Packages- P114

Oracle Built−in Packages- P114: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk PL SQL procedure successfully completed. SQL print unused_blks UNUSED BLKS 10 By retaining the current segment context set by the previous call and using default values a complex procedure call UNUSED_SPACE with ten parameters is transformed into a simple function call requiring only a target variable for its result. Now this is something I might be able and want to actually use. The set_segment procedure is used to set the current segment context which amounts to establishing the default IN parameters for all the functions. Astute readers will suspect that private package globals play a part in this trickery and they are correct. They may also raise questions about the performance implications of splitting the OUT parameters of UNUSED_SPACE into individual function calls as this is a relatively expensive procedure call to make and should not be redundantly or needlessly invoked. Well segspace is designed to be both useful and efficient. Here is the package body for segspace an explanation follows the code Filename on companion disk CREATE OR REPLACE PACKAGE BODY segspace AS record type to hold data on segment TYPE segdata_rectype IS RECORD name VARCHAR2 30 .schema VARCHAR2 30 DEFAULT USER type VARCHAR2 30 DEFAULT TABLE .partition VARCHAR2 30 DEFAULT NULL .total_blocks NUMBER .total_bytes NUMBER .unused_blocks NUMBER .unused_bytes NUMBER .last_extent_file NUMBER .last_extent_block NUMBER .last_block NUMBER .last_segload DATE SYSDATE - 1 global rec for current segment data segdata_rec segdata_rectype reload timeout in seconds segload_timeout INTEGER 60 flag for new segment newseg_TF BOOLEAN TRUE returns the segment name from segdata_rec FUNCTION current_name RETURN VARCHAR2 IS BEGIN RETURN END current_name DBMS_SPACE Examples 556 Appendix A What s on the Companion Disk returns the segment type from segdata_rec FUNCTION current_type RETURN VARCHAR2 IS BEGIN RETURN END .