facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Specialized Topics

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

The features and options available in Oracle increase with every version. Some of these features are invisible to the end user and are enhancements to the availability, recoverability and performance of the database. However, many options provide application developers and data analysts new options for application design and data analysis. A few of these options are mentioned below along with guidelines to consider when creating applications that interact with the Oracle database.

Lesson Goals

  • Learn about application partitioning
  • Learn how subprograms can be created in other languages.
  • Learn about Oracle object features
  • Learn how to perform statistical analysis
  • Learn about data mining in Oracle

Application Partitioning

PL/SQL developers often create code that is utilized by web based or desktop applications. In a client/server environment, each client application makes a connection to a server. In some cases, this can be a direct connection to a database server itself. Many applications today are multi-tiered and include client applications, one or more middle tier servers and Oracle databases as the backend data store. In web based applications, the web browser is a client application, a web server (along with various application servers) comprise the middle tier, and the Oracle database is data store.

It is important to identify the way an application is partitioned early on in the design process. The architecture selected will determine to a large degree what program functionality is required in each section of the application. For instance, a Model-View-Controller pattern is often used to separate concerns in middle tier development. In this paradigm, the Oracle database provides the model data in raw form. Formatting is done by the view. Interactions between the model and the view are coordinated by the controller. For a PL/SQL developer, the use of such a design would suggest that very little formatting would be done in PL/SQL code. Values would instead be returned in the data type and precision that they are stored.

Another application concern that benefits from clear application partitioning is validation. In web based applications, validation can occur at many levels. It can occur in the browser itself (using javascript), at the web server/application server (in application code) in user specified code at the database level (PL/SQL triggers) or through database constraints. Some decisions in this area are influenced by the determination of whether a database is accessed by one application only or many different applications. In any case, developers who understand the limits and possibilities available through the use of PL/SQL and database constraints can provide input to system architects to determine an effective design that will result in a well integrated functioning application.

Subprograms in Other Languages

There are times when PL/SQL cannot be used to effectively accomplish a programming task. Although these concerns are not very common, Oracle has provided the ability to extend database functionality beyond what is possible in PL/SQL. Most recent Oracle Versions (other than the free Oracle XE database) include support for writing PL/SQL programs in other languages including Java and C. The use of this functionality requires special administrative rights and configuration. In addition, the use of these features requires both an understanding of the language being used as well as implementation limitations and idiosyncrasies for the implementation used by the Oracle database.

Oracle Object Features

Part of the appeal of using Java is due to its classification as a Object Oriented (OO) language. Oracle has added object oriented programming features to the database and PL/SQL as well. The following demonstration shows how an object can be defined, constructed and accessed.

Code Sample:

Specialized-Topics/Demos/1_create_object_type.sql
CREATE TYPE employee_typ IS OBJECT
(
  id             NUMBER,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25),
  hire_date      DATE,
  salary         NUMBER, 
  MAP MEMBER FUNCTION get_id RETURN NUMBER, 
  MEMBER FUNCTION to_string  RETURN VARCHAR2,
  MEMBER FUNCTION days_service RETURN NUMBER,
  MEMBER FUNCTION months_service RETURN NUMBER
);
/

Code Explanation

By creating an object type a class is effectively defined. This class will have attributes as well as associated behaviors (subprograms). This object type is simply a specification. Like packages in PL/SQL, the implementation of the object is separated from its definition.

Code Sample:

Specialized-Topics/Demos/2_create_object_type_body.sql
CREATE TYPE BODY employee_typ IS
  MAP MEMBER FUNCTION get_id RETURN NUMBER IS
  BEGIN
    RETURN id;
  END;

  MEMBER FUNCTION to_string  
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN TO_CHAR(id) || ' ' || first_name || ' ' || last_name||' '
                       || 'Salary: '
                       || to_char(salary, '$999,999,999.00');
  END;

  MEMBER FUNCTION days_service
  RETURN NUMBER
  IS
  BEGIN
     RETURN trunc(sysdate - hire_date);
  END;
   
  MEMBER FUNCTION months_service
   RETURN NUMBER
  IS
  BEGIN
     RETURN months_between(sysdate, hire_date);
  END;

END;
/

Code Explanation

The type body contains the actual implementation of the type previously defined.

Code Sample:

Specialized-Topics/Demos/3_create_table.sql
CREATE TABLE 
  employee_obj_table OF employee_typ;
  

Code Explanation

The table created will hold specific instances of our class.

Code Sample:

Specialized-Topics/Demos/4_object_inserts.sql
INSERT INTO employee_obj_table 
VALUES
(employee_typ(101, 'Larry', 'Ellison', sysdate - 365, 30000) );

INSERT INTO employee_obj_table
VALUES
(employee_typ(102, 'Edward', 'Codd', sysdate - (2* 365), 30000) );

Code Explanation

This script demonstrates the creation (or instantiation in OO terms) of two person objects.

Code Sample:

Specialized-Topics/Demos/5_object_query.sql
SELECT p.last_name, p.days_service()
FROM employee_obj_table p;

Code Explanation

An object's attributes can be queries and functions called via SQL queries.

Code Sample:

Specialized-Topics/Demos/6_value_query.sql
SELECT VALUE(p)
FROM employee_obj_table p
WHERE p.last_name = 'Ellison';

Code Explanation

The VALUE function returns object instances corresponding to selected rows of the table.

Code Sample:

Specialized-Topics/Demos/7_drop_objects.sql
drop type employee_typ  force;

drop table employee_obj_table;

Code Explanation

When no longer needed, object tables and definitions can be removed using a drop statement. The force option may be required if there are any object dependencies.

Statistical Analysis

Oracle SQL provides an extensive array of statistical capabilities available through functions that can be called in the context of SQL statements. PL/SQL provides even more statistical power through the DBMS_STAT_FUNCS package.

Code Sample:

Specialized-Topics/Demos/summary_statistics_functions.sql
select 
	lpad(TO_CHAR(min(salary),'9,999.99'), 25) as "min", 
	lpad(TO_CHAR(max(salary),'999,999,999.99'), 25) as "max", 
	lpad(TO_CHAR(avg(salary),'9,999.99'), 25) as "avg",
	lpad(TO_CHAR(variance(salary),'999,999,999.99'), 25) as "var",
	lpad(TO_CHAR(stddev(salary),'9,999.99'), 25) as "stddev"
from employees
/

Code Explanation

This demonstration illustrates how to retrieve basic summary statistics using SQL.

Code Sample:

Specialized-Topics/Demos/summary_statistics.sql
DECLARE
 sig   NUMBER := 3; 
 s     dbms_stat_funcs.SummaryType;

BEGIN
  dbms_stat_funcs.summary
          ('HR', 'EMPLOYEES', 'SALARY', sig, s);

  dbms_output.put_line('Min:      ' ||
                       lpad(TO_CHAR(s.min,'9,999.99'), 25));
  dbms_output.put_line('Max:      ' ||
                       lpad(TO_CHAR(s.max,'999,999,999.99'), 25));
  dbms_output.put_line('Mean:     ' ||
                       lpad(TO_CHAR(s.mean,'9,999.99'), 25));
  dbms_output.put_line('Variance: ' ||
                    lpad(TO_CHAR(s.variance,'999,999,999.99'), 25));
  dbms_output.put_line('Std Dev:  ' ||
                       lpad(TO_CHAR(s.stddev,'9,999.99'), 25));
END;
/

Code Explanation

The DBMS_STAT_FUNCS package can be used to retrieve the same information as a single type.

The package goes beyond these simple descriptive calculations. Data can be plotted in a way that suggests a shape: plot of employee salary data There are several procedures in the package that quantify the degree that a data set conforms to an identified distribution.

Data Mining

Statistical techniques have been combined with insights from Machine Learning and other areas of Computer Science to provide sophisticated means for classifying data and making predictions based upon sample data sets. These techniques are often referenced under the heading of Data Mining. Oracle has an optional Data Mining package that provides standard algorithms that support this kind of analysis. They also have a front end application (Oracle Data Miner) that provides an organized user interface that facilitates this work.

Use Oracle Object Features

Duration: 20 to 30 minutes.
  1. Create an object type named car_typ. It should include the attributes for id (NUMBER), maker(VARCHAR2), model_name(VARCHAR2), year_made (NUMBER) and miles per gallon (NUMBER). It should include functions to get the id and a string representation of an instance, as well as a function to determine the age of the car (in years) and the gallons needed to drive a given number of miles.
  2. Create the implementation for the car_typ listed above.
  3. Create a table named car_obj_table to hold instances if this class.
  4. Create a car instance with id set to 101, maker set to Ford, model_name set to Torino, year set to 1971, and miles per gallon set to 11.
  5. Create a query to select the maker, model, age and gallons needed to travel 22 miles for the instance created.
  6. Remove the car_obj_table and type car_typ.

Solution:

Specialized-Topics/Solutions/solution.sql
create or replace TYPE car_typ IS OBJECT (
  id             NUMBER,
  maker          VARCHAR2(25),
  model_name     VARCHAR2(20),
  year_made      NUMBER,
  mpg            NUMBER, 
  MAP MEMBER FUNCTION get_id RETURN NUMBER, 
  MEMBER FUNCTION to_string  RETURN VARCHAR2,
  MEMBER FUNCTION age RETURN NUMBER,
  MEMBER FUNCTION gallons_needed(v_miles VARCHAR2) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY car_typ IS
  MAP MEMBER FUNCTION get_id RETURN NUMBER IS
  BEGIN
    RETURN id;
  END;

  MEMBER FUNCTION to_string  
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN TO_CHAR(id) || ' ' || maker || ' ' || model_name || ' '
                       || 'Year: ' || year_made || ' ' || mpg;
  END;

  MEMBER FUNCTION age
  RETURN NUMBER
  IS
  BEGIN
     RETURN  trunc(months_between(sysdate,
                  to_date('01/01/' || year_made, 'MM/DD/YYYY'))/12);
  END;
   
  MEMBER FUNCTION gallons_needed(v_miles VARCHAR2)
  RETURN NUMBER
  IS
  BEGIN
     RETURN round(v_miles / mpg);
  END;

END;
/

CREATE TABLE 
  car_obj_table OF car_typ;

INSERT INTO car_obj_table 
VALUES
(car_typ(101, 'Ford', 'Torino', 1971, 11) );

SELECT c.maker, c.model_name, c.age(), c.gallons_needed(22)
FROM car_obj_table c;

drop table car_obj_table;

drop type car_typ;

Code Explanation