PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's proprietary procedural extension to the SQL database language, used in the Oracle database. Some other SQL database management systems offer similar extensions to the SQL language.
A D V E R T I S E M E N T
PL/SQL's syntax strongly resembles that of Ada, and just like Ada compilers of the 1980s the PL/SQL runtime system uses Diana as intermediate representation.
The key strength of PL/SQL is its tight integration with the Oracle database. PL/SQL is one of three languages embedded in the Oracle Database, the other two being SQL and Java.
Oracle's PL/SQL (Procedural Language/SQL) is a database-oriented programming language that is a powerful extension of SQL with procedural capabilities. It is similar to ADA in respect to its syntax, and data types and is used in Oracle databases. Oracle implemented it primarily because of its flexibility and ability to wrap - convert the code to a binary (*.plb) which can't be done with SQL statements. Initially PL/SQL was used on the front end. Lately it obtained more ground on the back end.
All PL/SQL programs are made up of blocks and PL/SQL adds selective and iterative loops (constructs) to SQL.
PL/SQL originates from an older language, Pascal, and carries a lot of similarities to ADA in language structure as much of it is based on ADA. PL/SQL incorporates the Descriptive Intermediate Attribute Notation for Ada (DIANA). DIANA compiles the source interpreted code into an m-code for faster execution. There are many of Ada's concepts in procedures, packages with a package in the most conforming to a similar component of Ada.
Introduction to Oracle 9i:SQL,
PLSQL. and SQL *Plus
PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:
DECLARE
/* Declarative section: variables, types, and local subprograms. */
BEGIN
/* Executable section: procedural and SQL statements go here. */
/* This is the only section of the block that is required. */
EXCEPTION
/* Exception handling section: error handling statements go here. */
END;
Only the executable section is required. The other sections are optional. The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. However, the SELECT statement has a special form in which a single tuple is placed in variables; more on this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used.
To execute a PL/SQL program, we must follow the program text itself by
* A line with a single dot ("."), and then
* A line with run;
As with Oracle SQL programs, we can invoke a PL/SQL program either by typing in sqlplus.
PL/SQL can be structured into blocks. It can use
conditional statements, loops and branches to control program flow.
Scope of the variables can be defined to restrict the visibility within the block.
PL/SQL is a database-orientated programming language. It extends Oracle
SQL with procedural capabilities.
PL/SQL programs are organized into functions, procedures and packages and support for object-oriented programming is limited. It's based on the Ada programming language.
The three types of PL/SQL blocks:
Anonymous procedure
:It is an unnamed procedure and can't be called. Anonymous procedure is placed where it
is to be run, normally attached to a database trigger or application event.
Named procedure
:It
may be called and it may accept inbound parameters, but named procedure won't explicitly
return any value.
Named function
:A named function
can be called and it may accept inbound parameters. Named function will always return
a value.
All of these block types share most PL/SQL features so
during this tutorial the features that apply to all block types will be
grouped into single subjects.
DECLARE
Definition of any variables or objects that are
used within the declared block.
BEGIN
Statements that make up the block.
EXCEPTION
All exception handlers.
END;
End of block marker.
DECLARE
TEMP_COST NUMBER(10,2);
BEGIN
SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE
ISBN = 21;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175)
WHERE ISBN = 21;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21;
END IF;
COMMIT;
Oracle PL-SQL Tutorial
EXCEPTIONOracle PL-SQL Tutorial
Oracle PL-SQL Tutorial
WHEN NO_DATA_FOUND THENOracle PL-SQL Tutorial
Oracle PL-SQL Tutorial
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99,
'ISBN 21 NOT FOUND');Oracle PL-SQL Tutorial
Oracle PL-SQL Tutorial
END;
As you can see there are several elements in the
example that haven't been covered in the SQL tutorial, these elements are
the PL/SQL extensions. They include :-
Variables and Constants
These objects are used to store and manipulate block level
data. They can be CHAR, VARCHAR2, NUMBER, DATE or BOOLEAN data
types.
SQL support
All SQL statements are supported within PL/SQL blocks
including transaction control statements.
Composite Datatypes
Records allow groups of fields to be defined and manipulated
in PL/SQL blocks.
Flow Control
Ifs, Loops, GOTOs and labels provide conditional actions,
tests, branching and iterative program control.
Built In functions
Most SQL data functions are supported within PL/SQL blocks.
Cursor handling
Cursors (a memory area holding a result set) can be
explicitly defined and manipulated allowing the processing of
multiple rows. A group of PL/SQL system attributes provide the
ability to test a cursor's internal state.
Exception handling
Blocks have the ability to trap and handle local error
conditions (implicit exceptions). You may also self generate
explicit exceptions that deal with logic and data errors.
Code storage
Blocks may be stored within an Oracle database as
procedures, functions, packages (a group of blocks) and triggers.
Rules of block Structure
Every unit of PL/SQL must constitute a block. As a
minimum there must be the delimiting words BEGIN and END around the
executable statements.
SELECT statements within PL/SQL blocks are
embedded SQL (an ANSI category). As such they must return one row only.
SELECT statements that return no rows or more than one row will generate an
error. If you want to deal with groups of rows you must place the returned
data into a cursor. The INTO clause is mandatory for SELECT statements
within PL/SQL blocks (which are not within a cursor definition), you must
store the returned values from a SELECT.
If PL/SQL variables or objects are defined for use
in a block then you must also have a DECLARE section.
If you include an EXCEPTION section the statements
within it are only processed if the condition to which they refer occurs.
Block execution is terminated after an exception handling routine is
executed.
PL/SQL blocks may be nested, nesting can occur
wherever an executable statement could be placed (including the EXCEPTION
section).
Your first example in PL/SQL will be an anonymous block --that is a
short program that is ran once, but that is neither named nor stored
persistently in the database.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 dbms_output.put_line('First World');
3 END;
4 /
PL/SQL code is compiled by submitting it to SQL*Plus.
Unless your program is an anonymous block, your errors will not be
reported. Instead, SQL*Plus will display the message ``warning: procedure
created with compilation errors''. You will then need to type:
SQL> SHOW ERRORS
to see your errors listed. If yo do not understand the
error message and you are using Oracle on UNIX, you may be able to get a
more detailed description using the oerr utility, otherwise use
Oracle's documentation (see References section).
SQL> SELECT sysdate FROM DUAL
2 /
CREATE OR REPLACE PROCEDURE welcome
IS
user_name VARCHAR2(8) := user;
BEGIN -- `BEGIN' ex
dbms_output.put_line('First World, '
|| user_name || '!');
END;
/
Make sure you understand the changes made in the code:
Once you have compiled the procedure, execute it using the EXEC command.
SQL> EXEC welcome
Generating Output on Screen
The built-in packages offer a number of ways to
generate output from within your PL/SQL program. While updating a database
table is, of course, a form of "output" from PL/SQL, this chapter shows you
how to use two packages that explicitly generate output. UTL_FILE reads and
writes information in server-side files, and DBMS_OUTPUT displays
information to your screen.
Here is a very good chapter from Orially which helps
to understand the complete concept and usage of “Generating Output on
Screen”
View comments on this page.
Posted By Vinotha Selva on: Tuesday, April 13, 2010 Thanking It is a very nice article well explanatory. I got few more ideas also from this page. Thanks
