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.
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.
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.
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.
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 ); /
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.
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; /
The type body contains the actual implementation of the type previously defined.
CREATE TABLE employee_obj_table OF employee_typ;
The table created will hold specific instances of our class.
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) );
This script demonstrates the creation (or instantiation in OO terms) of two person objects.
SELECT p.last_name, p.days_service() FROM employee_obj_table p;
An object's attributes can be queries and functions called via SQL queries.
SELECT VALUE(p) FROM employee_obj_table p WHERE p.last_name = 'Ellison';
The VALUE function returns object instances corresponding to selected rows of the table.
drop type employee_typ force; drop table employee_obj_table;
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.
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.
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 /
This demonstration illustrates how to retrieve basic summary statistics using 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; /
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: There are several procedures in the package that quantify the degree that a data set conforms to an identified distribution.
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.
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;