0653-0656
Page 653
Variables declared within the package body but outside subprograms hold their values for the life of the session. As with standalone functions and procedures, variables declared within packaged subprograms persist only within the scope of the subprograms in which they are declared.
Variables and cursors declared at the package level can be accessed by all subprograms within the package body. Any code in the body of the package itself is executed only once when the package is first loaded. For this reason, package code is typically used only to initialize package variables. Listing 26.4, which is a portion of the package body for the specification in Listing 26.1, uses only one statement in the package body.
Listing 26.4. A package body providing functions to insert records into lookup tables.
CREATE OR REPLACE PACKAGE BODY lookup_admin AS user_id VARCHAR2(20); FUNCTION add_address_type(description VARCHAR2) RETURN NUMBER IS BEGIN INSERT INTO address_type VALUES(address_type_ids.nextval, description, user_id, sysdate); COMMIT; RETURN(0); EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN(1); END add_address_type; /* all functions in the specification must be defined in the body */ BEGIN SELECT user INTO user_id FROM dual;
END lookup_admin;
Packaged subprograms and data are accessed using owner.package_name.object_name notation. You can create public synonyms for packages, as with other objects, to eliminate the need for the owner prefix.
Note that the SELECT statement in the package body is executed only once, which is somewhat of an optimization when multiple transactions are applied using the functions in the package. For example, the SELECT statement stores the user_id upon package instantiation (first function call). All subsequent calls do not execute the SELECT statement.
To this point, the code listings in this chapter include functions in preference to procedures. Each of these functions returns a value that indicates the success or failure of the operation it performs . You can achieve the same result using an output parameter in a procedure, as illustrated by the package specification in Listing 26.5, which simply redefines the functions declared in Listing 26.3 as procedures.
Page 654
Listing 26.5. A package specification demonstrating the use of an output parameter in an overloaded procedure.
CREATE OR REPLACE PACKAGE manage_individuals AS PROCEDURE insert_individual(ret_code OUT NUMBER, last_in IN VARCHAR2, first_in IN VARCHAR2); PROCEDURE insert_individual(ret_code OUT NUMBER, last_in IN VARCHAR2, first_in IN VARCHAR2, notes_in IN VARCHAR2); PROCEDURE insert_individual(ret_code OUT NUMBER, last_in IN VARCHAR2, first_in IN VARCHAR2, d_o_b IN DATE, notes_in IN VARCHAR2); PROCEDURE insert_individual(ret_code OUT NUMBER, last_in IN VARCHAR2, first_in IN VARCHAR2, d_o_b IN DATE); /* add update and delete functions here */
END manage_individuals;
The use of functions instead of procedures is merely a design consideration based on the assumption that it is better to clearly distinguish return codes from actual data.
Overloading
The capability to overload a function or procedure is one of the primary advantages of packages. This feature is not available to standalone procedures and functions. Overloading is particularly useful when you are inserting records into tables with optional fields or when you are updating existing records. When overloading is implemented correctly, you can minimize the data passed between the application and the database and reduce the possibility of error. Listing 26.6 shows an example of function overloading in the package body, based on the package specification in Listing 26.3.
Listing 26.6. A package demonstrating function overloading.
CREATE OR REPLACE PACKAGE BODY manage_individuals AS user_id VARCHAR2(20); FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2) RETURN NUMBER IS new_id NUMBER; BEGIN SELECT individual_ids.nextval INTO new_id FROM dual; INSERT INTO individual (id, last_name, first_name, last_updt_user, last_updt_date) VALUES (new_id, last_in, first_in, user_id, sysdate); COMMIT; RETURN(new_id); EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN(1); END insert_individual;
Page 655
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, notes_in VARCHAR2) RETURN NUMBER IS new_id NUMBER; BEGIN SELECT individual_ids.nextval INTO new_id FROM dual; INSERT INTO individual (id, last_name, first_name, notes, last_updt_user, last_updt_date) VALUES (new_id, last_in, first_in, notes_in, user_id, sysdate); COMMIT; RETURN(new_id); EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN(1); END insert_individual; FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER IS new_id NUMBER; BEGIN SELECT individual_ids.nextval INTO new_id FROM dual; INSERT INTO individual (id, last_name, first_name, date_of_birth, notes, last_updt_user, last_updt_date) VALUES (new_id, last_in, first_in, d_o_b, notes_in, user_id, sysdate); COMMIT; RETURN(new_id); EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN(1); END insert_individual; FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, d_o_b DATE) RETURN NUMBER IS new_id NUMBER; BEGIN SELECT individual_ids.nextval INTO new_id FROM dual; INSERT INTO individual (id, last_name, first_name, date_of_birth, last_updt_user, last_updt_date) VALUES (new_id, last_in, first_in, d_o_b, user_id, sysdate); COMMIT; RETURN(new_id); EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN(1); END insert_individual; BEGIN SELECT user INTO user_id FROM dual;
END manage_individuals;
Page 656
Consider how you might accomplish this insert by using a user-defined record type or a single function that accepts all values. Using either alternative, applications calling the packaged insert function must ensure that null values are supplied for the fields for which no data exists. It is a better programming practice to encapsulate all default values within the packaged routines rather than in various calling routines.
The potential for problems is magnified for update operations. In update operations, the function needs logic to determine which fields are actually being updated or must update all columns in the table. In the latter case, the application is then responsible for supplying all values accurately to avoid accidental column updates. Function overloading simplifies application development by enabling applications to supply only the values required for each transaction. Passing only the values needed to perform the update can improve performance through minimizing disk writes of unnecessary data.
Retrieving Results
Oracle stored procedures and functions currently do not support the retrieval of result sets. However, you can overcome this limitation using a packaged subprogram. Remember that cursors declared at the package level persist for the duration of the session. This enables a set of functions to open a cursor and perform operations on it, maintaining the current position within the cursor from one call to the next. You can use output parameters to pass data from packaged functions to the calling application. Listing 26.7 shows an example of how you can use these features to return result sets to an application from a packaged subprogram.
Listing 26.7. A packaged cursor and functions that retrieve a result set.
CREATE OR REPLACE PACKAGE address_type_info AS FUNCTION get_next_address_type(id_out OUT NUMBER, description_out OUT VARCHAR2) RETURN NUMBER; FUNCTION close_address_type RETURN NUMBER; FUNCTION reopen_address_type RETURN NUMBER; END address_type_info; CREATE OR REPLACE PACKAGE BODY address_type_info AS last_id NUMBER(10); CURSOR c1 IS SELECT id, description FROM address_type; FUNCTION get_next_address_type(id_out OUT NUMBER, description_out OUT VARCHAR2) RETURN NUMBER IS end_of_cursor EXCEPTION; temp_id NUMBER(10); temp_desc VARCHAR2(40); BEGIN FETCH c1 INTO temp_id, temp_desc; IF (temp_id = last_id) THEN RAISE end_of_cursor; ELSE last_id := temp_id; id_out := temp_id; description_out := temp_desc; END IF;
EAN: 2147483647
Pages: 391