Oracle Unleashed- P10

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
44
lượt xem
4
download

Oracle Unleashed- P10

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Unleashed- P10: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products.

Chủ đề:
Lưu

Nội dung Text: Oracle Unleashed- P10

  1. Previous Next TOC Home Page Page q 18 r Object-Oriented Programming with Packages s The Package Specification s The Package Body s Package Variables and Initialization s Overloading s Retrieving Results s Exception Handling s Package Privileges s Accessing Oracle Packages from Client Applications s Object-Oriented Concepts s Summary 18 Object-Oriented Programming with Packages Although Oracle is a relational database, (as opposed to an object-oriented database), it provides a very powerful object- oriented feature in its implementation of packages. An Oracle package is a group of procedures, functions, variables, constants, cursors, and type declarations that function as a logical unit. Packages provide many of the characteristics typically associated with object-oriented languages, including encapsulation, information hiding, and function overloading. Packages can also provide improved performance because when a packaged object is referenced, the entire package is loaded into memory. This reduces or eliminates disk I/O for subsequent calls to objects in the package. As a result, these calls execute more quickly than similar calls to stand-alone functions and procedures, which must be read from disk as requested. There are two parts to a package: the package specification and the package body. The package specification provides the interface through which applications and other subprograms access packaged objects. The package body contains the actual code for objects in the specification, as well as any declarations and subprograms that are private to the package. If a package specification has only variables, constants, and type declarations, it need not have a body at all. This independence from the body of the package enables the specification to be compiled separately, even when a body is required. This can improve the development process by enabling developers to define the application interface before writing the underlying code. Objects referencing the package are dependent only on the specification. Therefore, the package body can also be compiled independently from the specification without affecting any external references, provided that there are no changes to the interface. The following sections demonstrate the creation of package specifications and bodies, highlighting key features. In addition to PL/SQL, an example is provided in C++ to illustrate the use of Oracle packages in object-oriented client applications. The Package Specification Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. The package specification must contain all objects that will be accessed by external subprograms or applications. It can be viewed as the public declarations section of the package. You can construct packages to perform all operations on an underlying database object or to perform operations on groups of similar objects. Any logical grouping of data and subprograms is an acceptable candidate for a package, as dictated by the application or applications that will be accessing the database. Listing 18.1 shows an example of a package specification that encapsulates methods for maintaining lookup tables in the database. Listing 18.1. This package specification contains functions used to maintain lookup tables. CREATE OR REPLACE PACKAGE lookup_admin AS FUNCTION add_address_type(description VARCHAR2) RETURN NUMBER; FUNCTION add_phone_type(description VARCHAR2) RETURN NUMBER; FUNCTION add_contact_type(description VARCHAR2) RETURN NUMBER; FUNCTION add_contact_method(description VARCHAR2) RETURN NUMBER; FUNCTION add_contact_reason(description VARCHAR2) RETURN NUMBER; /* add update and delete functions here */ END lookup_admin; In addition to functions and procedures, the package specification can contain variables, constants, and user-defined exceptions and data types. The code example in Listing 18.2 includes a user-defined data type based on an underlying table and provides functions to operate on the table. Listing 18.2. This package specification contains a user-defined data type. CREATE OR REPLACE PACKAGE manage_individuals AS TYPE indiv_rec IS RECORD( ID NUMBER(10) ,last_name VARCHAR2(30) ,first_name VARCHAR2(30) ,notes VARCHAR2(255) ,date_of_birth DATE ,last_updt_user VARCHAR2(20) ,last_updt_date DATE ); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. FUNCTION insert_individual(indiv_in INDIV_REC) RETURN NUMBER; FUNCTION update_individual(indiv_in INDIV_REC) RETURN NUMBER; FUNCTION delete_individual(indiv_in INDIV_REC) RETURN NUMBER; END manage_individuals; Perhaps the most powerful feature of packaged functions and procedures is overloading. Overloading enables a single function or procedure to accept different sets of parameters. To overload a packaged subprogram in Oracle, simply declare it separately for each desired parameter list, as shown in Listing 18.3. Listing 18.3. This package specification demonstrates function overloading. CREATE OR REPLACE PACKAGE manage_individuals AS FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2) RETURN NUMBER; FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, notes_in VARCHAR2) RETURN NUMBER; FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER; FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, d_o_b DATE) RETURN NUMBER; /* add update and delete functions here */ END manage_individuals; Be careful to avoid ambiguous parameter lists. For example, if the d_o_b parameter in the fourth function declaration of Listing 18.3 was defined as type VARCHAR2, it would become indistinguishable from the second function declaration. In the context of Listing 18.3, that would result in values being inserted into the wrong columns. You should recompile the package specification as infrequently as possible. Other packaged and stand-alone subprograms that reference objects in the package specification will be invalidated when it is recompiled. As a result, objects referencing the package specification must also be recompiled every time the specification is recompiled. The Package Body The package body contains the code for all subprograms defined in the specification, as well as any private variables, constants, cursors, data types, or subprograms. Objects declared within the package body are accessible only by other objects within the body. This enables you to use the package body to hide information and encapsulate subprograms within the package. However, objects within the package body can reference objects in other package specifications, as Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. well as stand-alone objects. A package body cannot exist without a package declaration. If the body does not contain all subprograms and cursors declared in the specification, or if declarations in the body are in conflict with declarations in the specification, compilation errors result. However, you can compile the body separately from the specification, which is extremely useful when you are debugging packaged subprograms. Packaged subprograms that contain explicit commits and rollbacks cannot be accessed by triggers or other subprograms that apply transactions. You should keep this in mind when you are designing packages, along with the effects of any implicit commits and rollbacks that might occur. Transactions applied within a packaged subprogram are rolled back implicitly when an unhandled exception occurs. An implicit commit occurs for all uncommitted transactions when the current session is terminated. In general, packaged subprograms involving transactions should not participate in transactions with other subprograms and should not be referenced by triggers. It is usually preferable to explicitly commit or roll back transactions that occur within packaged subprograms. Package Variables and Initialization The first time a packaged object is referenced, the entire package is loaded into memory. It is important to note that each session gets its own instance of package variables. Packaged data cannot be shared across sessions, and all values stored for a particular session are lost when the session ends. Variables declared within the package body, but outside of subprograms, hold their values for the life of the session. As with stand-alone 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 18.4, which is a portion of the package body for the specification in Listing 18.1, uses only one statement in the package body. Listing 18.4. This package body provides 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. 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 a 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 have included functions in preference to procedures. Each of these functions returns a value that indicates the success or failure of the operation it performs. The same result can be achieved by using an output parameter in a procedure, as illustrated by the package specification in Listing 18.5, which simply redefines the functions declared in Listing 18.3 as procedures. Listing 18.5. This package specification demonstrates 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. 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 subprogram is one of the primary advantages of packages. This feature is not available to stand-alone 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 18.6 shows an example of function overloading in the package body, based on the package specification in Listing 18.3. Listing 18.6. This package demonstrates 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; FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, notes_in VARCHAR2) RETURN NUMBER IS new_id NUMBER; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. 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; 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 would have to ensure that null values are supplied for the fields for which no data exists. It is a much 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 would need logic to determine which fields are actually being updated or would have to update all columns in the table. In the latter case, the application would then be 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 by using a packaged subprogram. Remember that cursors declared at the package level persist for the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. 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. Output parameters can be used to pass data from packaged functions to the calling application. Listing 18.7 shows an example of how these features can be used to return result sets to an application from a packaged subprogram. Listing 18.7. This code example uses a packaged cursor and functions to 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; RETURN(0); EXCEPTION Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. WHEN end_of_cursor THEN RETURN(1); WHEN OTHERS THEN RETURN(1); END get_next_address_type; FUNCTION close_address_type RETURN NUMBER IS BEGIN CLOSE c1; RETURN(0); EXCEPTION WHEN OTHERS THEN RETURN(1); END close_address_type; FUNCTION reopen_address_type RETURN NUMBER IS BEGIN OPEN c1; RETURN(0); EXCEPTION WHEN OTHERS THEN RETURN(1); END reopen_address_type; BEGIN OPEN c1; END address_type_info; Note that the cursor is opened in the body of the package itself. To retrieve the first row, an application need only call address_type_info.get_next_address_type to retrieve the first row. When this function returns 1, it informs the calling application that the end of the cursor has been reached. The application should then call address_type_info. close_address_type. The OPEN c1 statement in the body of the cursor will be executed only once, when the package is first loaded. In order to access the cursor a second time, the application must first call address_type_info. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. reopen_address_type. Subsequent calls to address_type_info.get_next_address_type can then be used to retrieve rows. Although this approach might be somewhat cumbersome, it might be acceptable for retrieving small result sets. This method could also be useful in producing reports that require breaks and subtotals. You could employ additional package-level variables to determine breakpoints and hold summary information as each row is returned to the application. This is just one example of how packages can be used to overcome many of the limitations of PL/SQL. Exception Handling Oracle provides many predefined exceptions, and a number of functions and procedures that can be used to handle them. Oracle implicitly raises predefined exceptions when they occur in PL/SQL blocks. Among these, the OTHERS exception is extremely valuable because it can be used as a catch-all (all other exceptions that are not explicitly handled), which in many cases is all that is needed. Even when specific handlers are used, using the OTHERS exception is a good idea. Using this exception prevents an application from bombing because of an unhandled error in a subprogram. In some cases, defining an exception that does not exist in Oracle might be useful. User-defined exceptions are declared in much the same way as variables. For example, in Listing 18.7, the user-defined exception end_of_cursor is declared in the get_next_address_type function. Control is passed to the exception handler using the RAISE statement. User-defined exceptions are particularly useful in performing sanity checks within PL/SQL blocks. You can use a package variable to associate user-defined text with an exception, which can be accessed by the application through an additional packaged subprogram. Listing 18.8 demonstrates how packaged constructs can be used to give to an application additional information concerning a user-defined error. Listing 18.8. A demonstration of user-defined exception handling. CREATE OR REPLACE PACKAGE manage_individuals AS FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER; FUNCTION get_error_text(text_out OUT VARCHAR2) RETURN NUMBER; END manage_individuals; CREATE OR REPLACE PACKAGE BODY manage_individuals AS user_id VARCHAR2(20); invalid_b_day EXCEPTION; error_text VARCHAR2(255); FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2, d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER IS new_id NUMBER; temp_bd VARCHAR2(20); temp_today VARCHAR2(20); BEGIN Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. temp_bd:=TO_CHAR(d_o_b, 'MMDDYYYY', 'nls_date_language = American'); SELECT TO_CHAR(sysdate, 'MMDDYYYY', 'nls_date_language = American') INTO temp_today FROM dual; IF ((to_date(temp_bd, 'MMDDYYYY', 'nls_date_language = American') > to_date(temp_today, 'MMDDYYYY', 'nls_date_language = American')) OR ((SUBSTR(temp_today, 7, 4) SUBSTR(temp_bd, 7, 4)) > 100)) THEN RAISE invalid_b_day; ELSE 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); error_text:= ' '; RETURN(new_id); END IF; EXCEPTION WHEN invalid_b_day THEN error_text:= 'Date of birth outside normal range.'; RETURN(11); WHEN OTHERS THEN error_text:=SUBSTR(SQLERRM, 1, 255); RETURN(1); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. END insert_individual; FUNCTION get_error_text(text_out OUT VARCHAR2) RETURN NUMBER IS BEGIN text_out:=error_text; RETURN(0); EXCEPTION WHEN OTHERS THEN text_out:='Unable to retrieve error information.'; RETURN(1); END get_error_text; BEGIN SELECT user INTO user_id FROM dual; END manage_individuals; The example in Listing 18.8 uses a package-level variable to store error text and provides a function to retrieve error text. Note the use of the predefined function SQLERRM in the OTHERS handler. In this context, SQLERRM is used to copy the Oracle error message into the package variable. The example in Listing 18.8 is just one way to deal with exceptions in packages. Oracle includes many other predefined functions used to handle exceptions, including SQLCODE, EXCEPTION_INIT, and RAISE_APPLICATION_ERROR. SQLCODE returns the Oracle error number associated with an exception; EXCEPTION_INIT enables the developer to associate a name with an Oracle error number; and RAISE_APPLICATION_ERROR raises a user-defined exception, accepting an error number and error text as parameters. The way in which exceptions are handled depends entirely on the nature of the application. What is most important is that all exceptions are handled. As a general rule, the OTHERS handler should always be used to trap all exceptions that do not have specific handlers. Package Privileges Using packages can greatly simplify the process of granting rights to users and roles. When you grant a user the EXECUTE privilege for a package, the user can access any data and subprograms in the package specification. In the package body, subprograms can access other packaged or stand-alone subprograms and other database objects. The user to which EXECUTE was granted does not need to have any rights to the external objects referenced in the package body. This is another way in which packages can be used for information hiding. In Listing 18.9, the lookup_admin package from Listing 18.4 is redefined to hide the implementation of address_type_info from Listing 18.7. Listing 18.9. A demonstration of indirect function calling. CREATE OR REPLACE PACKAGE lookup_admin AS FUNCTION get_next_address_type(id_out OUT NUMBER, description_out OUT VARCHAR2) RETURN NUMBER; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. FUNCTION close_address_type RETURN NUMBER; FUNCTION reopen_address_type RETURN NUMBER; /* add get_next, close, and reopen functions */ /* for other lookups here */ FUNCTION add_address_type(description VARCHAR2) RETURN NUMBER; FUNCTION add_phone_type(description VARCHAR2) RETURN NUMBER; FUNCTION add_contact_type(description VARCHAR2) RETURN NUMBER; FUNCTION add_contact_method(description VARCHAR2) RETURN NUMBER; FUNCTION add_contact_reason(description VARCHAR2) RETURN NUMBER; /* add update and delete functions here */ END lookup_admin; / CREATE OR REPLACE PACKAGE BODY lookup_admin AS user_id VARCHAR2(40); temp_id NUMBER(10); temp_desc VARCHAR2(40); FUNCTION get_next_address_type(id_out OUT NUMBER, description_out OUT VARCHAR2) RETURN NUMBER IS ret NUMBER(10); BEGIN ret:=address_type_info.get_next_address_type(id_out, description_out); RETURN(ret); EXCEPTION WHEN OTHERS THEN RETURN(1); END get_next_address_type; FUNCTION close_address_type RETURN NUMBER IS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. ret NUMBER(10); BEGIN ret:=address_type_info.close_address_type; RETURN(ret); EXCEPTION WHEN OTHERS THEN RETURN(1); END close_address_type; FUNCTION reopen_address_type RETURN NUMBER IS ret NUMBER(10); BEGIN ret:=address_type_info.reopen_address_type; RETURN(ret); EXCEPTION WHEN OTHERS THEN RETURN(1); END reopen_address_type; BEGIN SELECT USER INTO user_id FROM dual; END lookup_admin; When a user is granted the EXECUTE privilege on lookup_admin, as defined in Listing 18.9, the user gains indirect access to address_type_info, as well as the sequences and lookup tables referenced in the insert functions. Unless other privileges have been granted, however, the user will not be able to access the objects directly. For example, the user can read a row from the address_type table using lookup_admin.get_next_address_type, but will not be able to access address_type_info.get_next_address_type directly, or even use SELECT * FROM address_type. This is an example of how packages can be used to abstract the details of implementation from users and application interfaces. Granting privileges at the package level has the additional advantage of simplifying the entire process of granting rights to users and roles. This should be taken into consideration when you design packages. For example, if a particular role should have read-only access to the lookup tables referenced in Listing 18.9, you should create a separate package that does not include the insert functions. Accessing Oracle Packages from Client Applications Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. Oracle's implementation of the package fits the object model used in C++ particularly well, and using packages exclusively can simplify the process of designing the client application. The development of the client application, in many cases, can begin with the duplication of the structures and subprograms defined in the database packages. Listing 18.9 provides an example of a C++ class that is based on database objects, including packaged constructs. The data members of the class correspond to the columns in a table, and the Insert() member function of the Individual class is mapped to the overloaded insert functions in the manage_individuals package from Listing 18.6. The code example in Listing 18.10 is intended not to illustrate good C++ programming technique, but rather to demonstrate how overloaded C ++ member functions can be mapped directly to overloaded functions in Oracle packages. Listing 18.10. A C++ class illustrating overloading using a host language. class Individual { public: long ID; char LastName[30]; char FirstName[30]; char DateOfBirth[10]; /* DDMONYY */ char Notes[255]; char LastUpdateUser[20]; char LastUpdateTS[20]; /* DDMMYYYY HH:MI:SS */ int Insert(OSession SessionHandle, char* Last, char* First); int Insert(OSession SessionHandle, char* Last, char* First, char* Notes_Or_DOB); int Insert(OSession SessionHandle, char* Last, char* First, char* DateOfBirth, char* Notes); }; The data members of the Individual class are identical to the columns of the Individual table in Oracle, with one exception. The date of birth is stored as a string, requiring the overloaded form Individual::Insert(char*, char*, char*) to be able to distinguish a date from ordinary text in order to call the proper function in Oracle. Perhaps a better implementation of the Individual class would include an overloaded constructor to perform the insertion so that the data members could be protected. Declaring the data members as public is analogous to declaring variables in an Oracle package specification. Despite the shortcomings of the example in Listing 18.10, it demonstrates the point that if Oracle packages are designed properly they can be replicated in the client application. This can simplify the design of the client application, as well as ensure consistency in the object models being used throughout the system. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. Many Windows development tools use ODBC to communicate with the database. Unfortunately, the current Oracle ODBC driver does not support the access of packaged objects through ODBC. In order to access packaged objects, you must create stand-alone functions and subprograms to call the packaged objects from within Oracle. Listing 18.10 is an example of a stub that can be used to access packaged functions. Because overloading is not allowed in stand-alone functions and procedures, you must create separate subprograms to access each form of the overloaded packaged subprogram. When you are developing ODBC applications, you should carefully consider this limitation in the design process. The necessity of external stubs might nullify many of the advantages to using packages. User-defined data types and exceptions, variables, and cursors cannot be accessed from package specifications, and overloading is nullified by the requirement of separate external stubs corresponding to each form of the overloaded function. In addition, you must grant rights to each external stub that accesses the package. In some cases, there is no advantage to using packages when the database is being accessed through ODBC. The exception is when the application needs user-defined types or persistent variables. These can be packaged and accessed indirectly through the external stubs such as the example in Listing 18.11. Listing 18.11. This stand-alone function is needed to access a packaged function through ODBC. CREATE OR REPLACE FUNCTION ins_indiv_stub1 (last_in IN VARCHAR2 ,first_in IN VARCHAR2) RETURN NUMBER IS ret NUMBER; BEGIN ret:=manage_individuals.insert_individual(last_in, first_in, SYSDATE, 'new individual'); RETURN(ret); END ins_indiv_stub1; Products that communicate with SQL*Net and the Oracle Call Interface directly can be used to overcome this ODBC- specific limitation. Using packages in an ODBC application is also inconsistent with one of the primary goals of ODBC, which is to provide database independence. Object-Oriented Concepts As mentioned previously, Oracle packages provide several features that are typically associated with object-oriented programming. Among these are encapsulation, information hiding, and function overloading. In this section you will learn additional object-oriented features that apply not to the database itself, but to several of Oracle's newest development tools. C++, in particular, is used to illustrate these concepts. Encapsulation is simply the grouping of related data, procedures, and functions to form a collection. An object, or a package, is simply a name for this encapsulated data and methods for operating on it. In C++, an object is implemented as a class or an instance of a class. The class itself defines the object's data and methods, whereas an instance contains the data specific to one particular object belonging to the class. In terms of Oracle packages, the package specification and package body make up the class, whereas each session gets a specific instance of the class. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. In C++ terminology, objects are created and destroyed using constructors and destructors. A constructor allocates memory for the new instance of the object and loads it, whereas a destructor unloads the object and frees memory allocated to it. You have the option of placing code in the constructor and destructor of an object. In Oracle, an instance of a package is constructed when it is first referenced in a session and destructed when the session ends. Code in the body of the package itself is fired when an instance is constructed. No code can be specified for the destructor of a package. Listing 18.12 provides a simple example of an object in C++, with a single constructor and a single destructor. Note that in C++ the constructor has the same name as the class and returns a pointer to an instance of an object belonging to the class. Although the arguments to the constructor can be redefined, the return type cannot be. If no constructor is specified, the compiler creates a default constructor that simply allocates memory for the new instance and loads it. Similar rules apply to the destructor, which always has the name of the class preceded by a tilde and returns void, (nothing). In Listing 18.12, the destructor is named ~Car(). Listing 18.12. A simple class, with a constructor and destructor as the only member functions. class Car { public: char *Make; char *Model; unsigned Year; Car(char* CarMake, char* CarModel, unsigned CarYear); ~Car(); }; Car::Car(char* CarMake, char* CarModel, unsigned CarYear) { Make = strdup(CarMake); Model = strdup(CarModel); Year = CarYear; } Car::~Car() { free(Make); free(Model); } Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. The free statements in the destructor are very important. When the class is instantiated, additional memory is allocated for these data members. The default destructor will only free memory allocated for the object itself, which includes only the pointers. To create an instance of car, declare a pointer to Car, which will receive the return value of the constructor: Car *MyCar; MyCar = new Car(ÓFordÓ, ÓMustangÓ, 1967); // To destroy the object, use the delete operator: delete MyCar; This simple example illustrates the encapsulation of data and methods in an object and the instantiation and destruction of an instance of the object. These concepts are the very foundation of object-oriented programming techniques. Information hiding is a form of encapsulation in which data elements or methods can be accessed only by the methods of the object. This point was illustrated in the context of Oracle packages in several ways. In Listing 18.6, the user who last updated a record and the timestamp indicating when the record was last updated were inserted by a function, without any intervention by the user or the calling application. Tables, functions, procedures, and other database objects can also be hidden by Oracle packages as illustrated in Listing 18.9. In general, variables and constructs declared in the package specification are visible, or public. Variables and constructs declared within the package only are hidden, or private. In C++, variables and functions can be declared as public, private, or protected in the class definition. Public constructs can be accessed anywhere in a program, whereas private and protected data and methods can be accessed only through member functions and member functions of friend classes. These subjects are discussed in greater detail in the explanation of Listing 18.14. At this point, it is only important to recognize that this is the how C++ hides information. For example, if the Car class from Listing 18.12 were redefined as in Listing 18.13, the Mileage data member could only be accessed by the constructor and the member functions GetMileage and IncrementMileage. Listing 18.13. A redefinition of the car class, illustrating the use of the protected keyword. class Car { public: char *Make; char *Model; unsigned Year; Car(char* CarMake, char* CarModel, unsigned CarYear ,unsigned long Mileage); ~Car(); unsigned long GetMileage(); void IncrementMileage(unsigned Miles); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. protected: unsigned long Mileage; }; If this were the extent of the implementation of the Car class, Mileage could only be increased after the instance is constructed. If Mileage were declared as public, however, it could be modified at any time through an assignment, such as MyCar->Mileage = 10; Protected data and functions can also be used to abstract implementation details, such as database transactions. The SQL used to insert a car could be declared protected, parameterized, and initialized when an instance is constructed. The application could then add a car to the database by accessing a public member function without knowing the SQL syntax, or that it even exists. An extremely important feature of the object-oriented model is the concept of inheritance. Inheritance defines a class hierarchy in which a descendent class receives the member functions and data elements of the parent class to which it belongs. For example, you can create a base class without any intention of constructing the object. Base classes are often created only to be inherited from. Listing 18.14 illustrates this point in the context of the simple example of the Car class. Listing 18.14. This implementation of the Car class illustrates the concept of inheritance. class Vehicle { public: char *Make; char *Model; unsigned Year; }; class Car : public Vehicle { public: Car(char* CarMake, char* CarModel, unsigned CarYear ,unsigned long Mileage); ~Car(); unsigned long GetMileage(); void IncrementMileage(unsigned Miles); protected: unsigned long Mileage; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản