NYPL Labs

Introduction

It is a commonplace of the modern world that electronic  data bases allow us to  store millions and trillions of records, keep then safe, and allow us to sort and search among them. 

SQL

Experience has shown that its best to separate the presentation of data, the layout, colors, fonts , logical organization and so on, from the generation of a result set,  that is, from the  specifican of  which subset of the entire data base is relevant for the current purpose.

The lion’s share of popular commercial and open source data bases use the relational model, in which data is abstracted as a number of tables consisting of rows of data, one row per item of interest, and columns, where the columns represent relevant attributes of the item. 

All relational databases (and many which are not strictly speaking, relational)  offer a version of the now standard SQL, which is a non procedural query language. More about SQL can be had  here

Analytics and Data Mining

Anyone with a potential interest in teasing out the knowlege hidden in all that data should be aware that our Oracle data base SQL implementation provides a number of extentions to the basic SQL language in the form of built in functions to help perform sophisticated  statistical operations, correlations, filtering, moving averages,  and so on directly in non procedural SQL. See ANALYTICS here 

PL/SQL

In addition to the (somewhat) standard SQL non procedural Query language, Oracle provides a proprietary (sorry about that) procedural language called pl/sql. 

Of course, pl/sql’s foremost strength is its close integration with the Oracle database. An ordinary sql “Select column From Table” can become a pl/sql statement “Select column INTO local_Variable From Table” . Better yet, a “cursor FOR Loop” can define a query and then loop over all rows of the returned data set, with the particular instance row of data available for manipulation in the body of the loop. The coder does not separately define, parse, open, fetch, test, and close the cursor. All the mechanics are done for you  by the electonic monkeys.

Data types can by synchronized with the corresponding data base type so that if say a column width changes in the schema, corresponding variables in the pl/sql program can track that change with no recoding.

There is a very well developed mechanism for exception handling. 

The language is not, stricly speaking, “Object Oriented” in the modern meaning, but it does derive from ADA and offers a “package” mechanism which provides data hiding,  private  routines and encapsulation. A package is very much like a Java class except that packages to not “inherit” from one another.

Like  awk, perl, python, or  ruby,  pl/sql has a “hash” or “dictionary” data type (character indexed array) and it has a full panoply of  perl-like regular expressions.

Over the  years, pl/sql has evolved into a “real” language.  For any application or portion of an application that involved going to a from the database, with complex selection criteria, tricky logic based on values in or derived from data base values, or just plain statistical bashing for counts, averages, correlations,  distance measures, and the like, pl/sql is the way to fly. 

Pl/sql is NOT meant for fancy presention, so one would do better to export numerical results, vectors, matrices,  result sets carried by reference cursors etc out of pl/sql to a separate specialized graphics package or some language with more extensive graphics libraries.