Learn PL/SQL: Manage Data In Oracle Databases

SQL, or Structured Query Language, is the language used by most relational database management systems (RDBMS) for data description, recording, manipulation, and querying. It is very useful for describing how a database is to be structured, and what data needs to be stored or retrieved. But it isn’t really a programming language.

It is, trivially, Turing complete — but lots of things are Turing complete and still not useful for writing programs. SQL, by itself, simply doesn’t provide the sorts of things you need for writing an application, or even for particularly complex scripts. This means that, in order to do anything programatic with SQL, you usually have to use a second language.

PL/SQL was created by Oracle, for their database management system, in order to close this gap. “PL” stands for “procedural language,” and PL/SQL is an extension — a superset — of SQL, which adds procedural programming features to the language. This includes basic programming language constructs like conditionals (“if” statements) and loops, as well as functions, procedures, objects, types, and triggers.

These language constructs, especially data types and objects, are integrated with the Oracle database in a way not possible when accessing it from another language. Besides making code more efficient, this also helps overcome the object-relational impedance mismatch — what happens in the application code tracks closely with what is happening in the database.

PL/SQL Tutorials

• PL/SQL Tutorial from Tutorials Point offers a clear, methodical introduction to the language.
• Using Oracle PL/SQL is an overview of the topic for computer science students at Stanford.
• PLSQLTutorial is an entire website filled with tutorials, from beginner to advanced.
• PL/SQL Tutorial for Beginners is exactly what it sounds like, with individual lessons on dozens of PL/SQL topics.
• Day 1: Learning the Basics of PL/SQL is a one-sitting overview of the language, and the first chapter of Sams Teach Yourself PL/SQL in 21 Days (2000), by Gennick and Luers.
• Oracle / PLSQL provides a tutorial on both the PL/SQL language and the Oracle database system.
• Oraclecoach has a 34-part video tutorial series on PL/SQL.

Additional Learning Resources

• Oracle’s PL/SQL page has a “Getting Started” section with links to dozens of tutorials and additional resources for using PL/SQL.
• Practically Perfect PL/SQL with Steven Feuerstein is a YouTube channel with dozens of video tutorials on PL/SQL. Feuerstein also writes a blog on PL/SQL.

PL/SQL Tools

• Oracle SQL Developer is an integrated development environment (IDE), made by Oracle, for working with Oracle databases; it includes built-in support for working with PL/SQL.
• PL/SQL Developer is a commercial IDE for PL/SQL.
• Toad Development Suite for Oracle, from Dell, is a set of integrated tools for PL/SQL development and working with Oracle databases.
• PLEdit is a lightweight PL/SQL editor, with a built-in compiler; Benthic Software, the maker of PLEdit, has several other useful tools for working with Oracle databases.
• Log 4 PL/SQL is a framework for logging PL/SQL code.
• PLPDF allows you to easily write queries in PL/SQL and generate PDF reports directly.
• PLDoc is a tool, similar to Javadoc, that lets you build documentation directly out of specially-formatted comments in PL/SQL code.
• TOra is an open source alternative to Toad Development Suite or Oracle SQL Developer; it works with a number of relational database systems, but has special tools for working with PL/SQL.

Community and Ongoing Learning

• Oracle Community has a section devoted to SQL and PL/SQL.
• OraFAQ Forum also has a PL/SQL section.

Books on PL/SQL

• Oracle PL/SQL Programming (2014), by Feuerstein and Pribyl, is the definitive guide to the language, co-written by PL/SQL expert, and host of Practically Perfect PL/SQL, Steven Feuerstein; and don’t miss the companion books:
  • Oracle PL/SQL Best Practices (2007), by Steven Feuerstein;
  • Oracle PL/SQL Language Pocket Reference (2007), by Feuerstein, Pribyl, and Dawes.
• Oracle Database 12c PL/SQL Programming (2014), by Michael McLaughlin, is an official Oracle Press publication, and represents a sort of “orthodox opinion” about both the language and the database; that seal of approval, combined with the fact that it is extremely well organized and exceptionally clear, makes this book an essential resource.
• Murach’s Oracle SQL and PL/SQL for Developers (2014), by Joel Murach, provides a great overview of the language, in Murach’s signature two-stream style, placing content and outline on facing pages so that you can easily use the book for reference and review.
• Oracle PL/SQL For Dummies (2006), by Rosenblum and Dorsey — “dummies” is a relative term, since this is still a highly technical book, and a “real” dummy wouldn’t get much out of it; still, the books easy style and step-by-step explanations are a great help, especially for people without a lot of prior database experience.
• Oracle PL/SQL by Example (2008), by Rosenzweig and Rakhimov, is a great companion book for any of the other books covering language basics; it contains a series of exercises and examples which you can use to expand or test your PL/SQL abilities.

Advanced PL/SQL Books

Once you’ve worked through one or more of the introductory books above, you may want to move on to more advanced topics. Oracle Database 12c PL/SQL Advanced Programming Techniques (2014), by McLaughlin and Harper, is another official Oracle Press title, cowritten by the author of the introductory book mentioned above. You may also want to check out Oracle Database 12c The Complete Reference (2013), by Bryla and Loney. Finally, Expert PL/SQL Practices: for Oracle Developers and DBAs (2011), by Rosenblum et al, provides the collected wisdom of 15 expert co-authors.

Should I Learn PL/SQL?

That depends on your career trajectory.

PL/SQL is a very specific language. It is only used in one context: the Oracle database. Oracle is a proprietary database system primarily used by large enterprises.

So, if you are a database developer or DBA who works (or wants to work) in large enterprises, then PL/SQL will be a very helpful skill.

Further Reading and Resources

We have more guides, tutorials, and infographics related to coding and development:

• SQL Resources: our general SQL resource that is critical for all relational database developers.
• Introduction to ADO.NET: learn all about this system for using any database at all inside the .NET framework.
• PHP Introduction and Resources: learn about a great language for working with other databases like MySQL and PostgreSQL.

Ultimate Guide to Web Hosting

If you are going to be creating database driven applications, you will likely have to host them somewhere. Check out our Ultimate Guide to Web Hosting. It will explain everything you need to know in order to make an informed choice.

Ultimate Guide to Web Hosting