SQL and Objects: Methods and Stored Procedures

In object-oriented languages, objects encapsulate both the data and programming code that they contain; the details of the data structures within an object and the programming instructions that manipulate those data structures are explicitly hidden from view. The only way to manipulate the object and obtain information about it is through methods, which are explicitly defined procedures associated with the object (or more accurately with the object class). For example, one method associated with a customer object might obtain the customer’s current credit limit. Another method might provide the ability to change the credit limit. The credit limit data itself is encapsulated, hidden within the customer object.

The data within the tables of a relational database is inherently not encapsulated. The data and its structure are directly visible to outside users. In fact, one of the main advantages of a relational database is that SQL can be used to carry out ad hoc queries against the database. When the system catalog of a relational database is considered, the contrast with the object-oriented ideal is even more extreme. With the catalog, the database is self-describing, so that even applications that don’t know the internal structure of the database in advance can use SQL queries to find out what it is.

Stored procedures provide a way for relational databases to offer capabilities that resemble those of object-oriented methods. At the extreme, all users of a relational database could be granted permission only to execute a limited set of stored procedures, and no underlying data access permissions on the base tables at all. In this case, the users’ access would approach the encapsulation of the object-oriented ideal. In practice, stored procedures are often used to provide application designers with the limited database access they need. However, the ad hoc capabilities of the database are almost always exploited by query tools or reporting programs.

Oracle formalizes the linkage between object methods and database stored procedures by allowing you to explicitly define a stored procedure as a member function of an abstract data type. Once defined in this way, the member function can be used in queries involving the abstract data type, just as if it were a built-in function of the DBMS designed to work on that type. Here is a redefinition of the ADDR_TYPE abstract data type that is used to store addresses, with a relatively simple member function, named GET_FULL_POST. The function takes the postal-code part of the address, which stores both a five-digit main postal code and a four-digit suffix as two separate numbers, and combines them into one nine-digit number, which it returns:

In object-oriented languages, objects encapsulate both the data and programming code that they contain; the details of the data structures within an object and the programming instructions that manipulate those data structures are explicitly hidden from view. The only way to manipulate the object and obtain information about it is through methods, which are explicitly defined procedures associated with the object (or more accurately with the object class). For example, one method associated with a customer object might obtain the customer’s current credit limit. Another method might provide the ability to change the credit limit. The credit limit data itself is encapsulated, hidden within the customer object.

The data within the tables of a relational database is inherently not encapsulated. The data and its structure are directly visible to outside users. In fact, one of the main advantages of a relational database is that SQL can be used to carry out ad hoc queries against the database. When the system catalog of a relational database is considered, the contrast with the object-oriented ideal is even more extreme. With the catalog, the database is self-describing, so that even applications that don’t know the internal structure of the database in advance can use SQL queries to find out what it is.

Stored procedures provide a way for relational databases to offer capabilities that resemble those of object-oriented methods. At the extreme, all users of a relational database could be granted permission only to execute a limited set of stored procedures, and no underlying data access permissions on the base tables at all. In this case, the users’ access would approach the encapsulation of the object-oriented ideal. In practice, stored procedures are often used to provide application designers with the limited database access they need. However, the ad hoc capabilities of the database are almost always exploited by query tools or reporting programs.

Oracle formalizes the linkage between object methods and database stored procedures by allowing you to explicitly define a stored procedure as a member function of an abstract data type. Once defined in this way, the member function can be used in queries involving the abstract data type, just as if it were a built-in function of the DBMS designed to work on that type. Here is a redefinition of the ADDR_TYPE abstract data type that is used to store addresses, with a relatively simple member function,
named GET_FULL_POST. The function takes the postal-code part of the address, which stores both a five-digit main postal code and a four-digit suffix as two separate numbers, and combines them into one nine-digit number, which it returns:

CREATE TYPE ADDR_TYPE AS OBJECT (
     STREET VARCHAR(35),
       CITY VARCHAR(15),
      STATE CHAR(2),
   POSTCODE POST_TYPE,
     MEMBER FUNCTION GET_FULL_POST(POSTCODE IN POST_TYPE)
     RETURN NUMBER,
     PRAGMA RESTRICT_REFERENCES(GET_FULL_POST, WNDS));
     CREATE TYPE BODY ADDR_TYPE AS
     MEMBER FUNCTION GET_FULL_POST(POSTCODE POST_TYPE)
     RETURN NUMBER IS
     BEGIN
        RETURN((POSTCODE.MAIN * 10000) + POSTCODE.SFX);
     END;
     . . .

The member function is identified as such within the CREATE TYPE statement for the abstract data type, following the lines that describe the data items. The additional PRAGMA clause tells Oracle that the function does not modify the contents of the database, which is a requirement for a function that is to be used within query expressions. There are several more options, which are beyond the scope of this discussion. A separate CREATE TYPE BODY statement defines the actual procedural code for the function. After the first few words of the statement, it follows the same format as the standard CREATE PROCEDURE or CREATE FUNCTION statements. Once the member function is defined, it can be used in query expressions like this one, which finds employees living in postal code 12345-6789:

123456789;

Informix Universal Server doesn’t have an extended mechanism like Oracle’s to turn stored procedures into object-oriented methods. Instead, it’s possible to use an Informix row type (corresponding to an Oracle object type) as the parameter of a stored function. When called, the function is passed a data item with the appropriate row type (such as the POSTCODE abstract data item in the preceding Oracle example) and can perform appropriate calculations on it. You could, for example, define an Informix stored function GET_FULL_POST() with a single parameter of type POST_TYPE. With that definition, the preceding Oracle SELECT statement could be used, unmodified, in the equivalent Informix database.

Another powerful feature associated with object-relational stored procedures is the overloading of procedure definitions to allow them to process different types of data. In an object class hierarchy, it’s frequently necessary to define a method that carries out the same or very similar operations on different classes of objects. For example, you may want to define a GET_TGT_WAGES method (function) that can obtain the target total annual wages for any of the subclasses of the PERSONNEL class in our example database. The method (which will be implemented as a stored function) should return the target total wages for the employee to which it is applied. The particulars of the calculation differ, depending on the type (class) of employee:

  • For technicians, total wages are the hourly rate times a normal 40-hour week x 52 weeks per year.
  • For managers, total wages are equal to their annual salary plus bonus.
  • For all other engineers, total wages are equal to their annual salary.

To solve this problem, a different GET_TGT_WAGES routine is defined for each class. The routine takes an object (a row of the TECHNICIANS, ENGINEERS, or MANAGERS table) as its parameter and returns the calculated amount. The three routines are identically named, which is the reason why the procedure name is said to be overloaded—a single name is associated with more than one actual stored procedure. When the routine is called, the DBMS looks at the particular data type of the argument (that is, the particular class of the object) and determines which of the routines is the appropriate one to call.

Informix Universal Server implements this stored procedure overloading capability without any additional object-oriented extensions. It allows you to define many different stored procedures with identical names, provided that no two of them have the identical number of arguments with identical data types. In the previous example, there would be three CREATE FUNCTION definitions like this:

/* Calculates target wages for a technician */

CREATE FUNCTION GET_TGT_WAGES(PERSON TECH_TYPE)

    RETURNS MONEY(9,2) AS RETURN (PERSON.WAGE_RATE * 40 * 52)

END FUNCTION;

 

/* Calculates target wages for a manager */

CREATE FUNCTION GET_TGT_WAGES(PERSON MGR_TYPE)

   RETURNS MONEY(9,2) AS RETURN (PERSON.SALARY + PERSON.BONUS)

END FUNCTION;

 

/* Calculates target wages for an engineer */

CREATE FUNCTION GET_TGT_WAGES(PERSON ENGR_TYPE)

   RETURNS MONEY(9,2) AS RETURN (PERSON.SALARY)

END FUNCTION;

With these definitions in place, you can invoke the GET_TGT_WAGES() function and pass it a row from the ENGINEERS, MANAGERS, or TECHNICIANS table. The DBMS automatically figures out which of the functions to use and returns the appropriate calculated value.

Stored procedures are made even more valuable for typed tables through Informix Universal Server’s substitutability feature. If you call a stored procedure whose argument is a row type and pass it one of the rows from a typed table, Informix will first search for a stored procedure with the appropriate name whose argument data type is an exact match. For example, if you call a GET_LNAME() stored procedure to extract the last name from a TECH_TYPE row (probably from the TECHNICIANS table), Informix searches for a procedure written to process TECH_TYPE data. But if Informix doesn’t find such a stored procedure, it does not immediately return with an error. Instead, it searches upwards in the type hierarchy, trying to find a procedure with the same name that is defined for a supertype of TECH_TYPE. If there is a GET_LNAME () stored procedure defined for the ENGR_TYPE type, Informix will execute that stored procedure to obtain the required information. If not, it will continue up the hierarchy, looking for a GET_LNAME () stored procedure defined for the PERS_TYPE type. Thus, substitutability means that you can define stored procedures (methods) for the highest-level type in the hierarchy to which they apply. The stored procedures are automatically available to process all subtypes of that type. (That is, all subclasses inherit the method from the class.)

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *