Oracle PL/SQL tutorial

Table of contents:
[IMAGE] Define PL/SQL and understand PL/SQL basics
[IMAGE] PL/SQL datatypes in Oracle
[IMAGE] PL/SQL functions and triggers in Oracle
[IMAGE] Stored procedures in PL/SQL

  Define PL/SQL and understand PL/SQL basics 

PL/SQL definition:

What is PL/SQL? PL/SQL is a procedural language extension to Structured Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural programming language. Because PL/SQL allows you to mix SQL statements with procedural constructs, it is possible to use PL/SQL blocks and subprograms to group SQL statements before sending them to Oracle for execution. Without PL/SQL, Oracle must process SQL statements one at a timeand, in a network environment, thi...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Using Oracle PL/SQL Oracle tutorial library: SearchOracle.com's learning guides SELECT statement syntax and examples PL/SQL datatypes in Oracle PL/SQL functions and triggers in Oracle Stored procedures in PL/SQL How to concatenate rows into a single CLOB in PL/SQL Working with substitution variables and using EXECUTE IMMEDIATE in PL/SQL How to open a ref cursor in a PL/SQL procedure Oracle's free SQL Developer adds database migration tool Confused about Oracle certification exams Oracle and SQL Oracle tutorial library: SearchOracle.com's learning guides Can I specify Oracle column order in my database table? Review: Oracle's 11g R2 database has some good and bad SELECT statement syntax and examples PL/SQL datatypes in Oracle PL/SQL functions and triggers in Oracle Stored procedures in PL/SQL Do I need a license for SQL Developer Data Modeler in Oracle? Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary PL/SQL  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

s can affect traffic flow and slow down response time. (The full PL/SQL definition can be read at Whatis.com).

In an excerpt from their book "Learning Oracle PL/SQL", authors Bill Pribyl & Steven Feuerstein define PL/SQL as the following:

"Structured Query Language is a language based on set theory, so it is all about manipulating sets of data. SQL consists of a relatively small number of main commands such as SELECT, INSERT, CREATE, and GRANT; in fact, each statement accomplishes what might take hundreds of lines of procedural code to accomplish. That's one reason SQL-based databases are so widely used. The big joke about the name "SQL" is that it is not really structured, is not just for queries, and (some argue) is not even a real language. Nevertheless, it's the closest thing there is to a lingua franca for relational databases such as Oracle's database server, IBM's DB2 and Microsoft's SQL Server.

"A language extension is a set of features that somehow enhance an existing language. This phrase might imply, incorrectly, that PL/SQL is a special version of SQL. That isn't the case, however. PL/SQL is a programming language in its own right; it has its own syntax, its own rules, and its own compiler. You can write PL/SQL programs with or without any SQL statements. Some authors assert that PL/SQL is a superset of SQL, but that's a bit of an overstatement, because only the most common SQL statements can be used easily in a PL/SQL program.

"PL/SQL, then, is a language that is closely related to SQL, but one that allows you to write programs as an ordered series of statements. Or, if you want a definition of PL/SQL that befits a programmer:

Pribyl & Feuerstein's book, Learning Oracle PL/SQL, can be purchased at the O'Reilly Media web site.

PL/SQL basics

Here are some basic PL/SQL definitions and terms (some taken from an excerpt of the book Oracle 11g: PL/SQL Basics by Michael McLaughlin):

Procedural programming language (like PL/SQL): Allow the programmer to define an ordered series of steps to follow in order to produce a result.

Anonymous- block program: An anonymous-block PL/SQL program that supports batch scripting; requires only the execution section.

Named-block program: A named-block program is a PL/SQL program that delivers stored programming units.

Execution section: Starts with a BEGIN statement and stops at the beginning of the optional EXCEPTION block or the END statement of the program.

Stored procedure: A stored procedure set of SQL statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs.

Trigger: A PL/SQL trigger is a PL/SQL stored procedure that is implicitly started when an INSERT, UPDATE or DELETE statement is issued against an associated table.

Control structures: PL/SQL control structures either check a logical condition and branch program execution, or they iterate over a condition until it is met or they are instructed to exit.

Conditional structures: PL/SQL conditional statements check whether a value meets a condition before taking action. There are two types of conditional structures in PL/SQL, the IF statement and the CASE statement.