Q: What is PL/SQL and what is
it used for?
SQL is a declarative language
that allows database programmers to write a SQL declaration and hand it to the
database for execution. As such, SQL cannot be used to execute procedural code
with conditional, iterative and sequential statements. To overcome this
limitation, PL/SQL was created.
PL/SQL is Oracle's Procedural
Language extension to SQL. PL/SQL's language syntax, structure and data types
are similar to that of Ada.
Some of the statements provided by PL/SQL:
Conditional Control
Statements:
- IF ... THEN ... ELSIF ... ELSE ... END IF;
- CASE ... WHEN ... THEN ... ELSE ... END CASE;
Iterative Statements:
- LOOP ... END LOOP;
- WHILE ... LOOP ... END LOOP;
- FOR ... IN [REVERSE] ... LOOP ... END LOOP;
Sequential Control Statements:
The PL/SQL language includes
object oriented programming techniques such as encapsulation, function
overloading, information hiding (all but inheritance).
PL/SQL is commonly used to write
data-centric programs to manipulate data in an Oracle database.
Example PL/SQL blocks:
/* Remember to SET SERVEROUTPUT ON to see the output */
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/
BEGIN
-- A PL/SQL cursor
FOR cursor1 IN (SELECT * FROM table1) -- This is an embedded SQL statement
LOOP
DBMS_OUTPUT.PUT_LINE('Column 1 = ' || cursor1.column1 ||
', Column 2 = ' || cursor1.column2);
END LOOP;
END;
/
[edit] What is the
difference between SQL and PL/SQL?
Both SQL and PL/SQL are languages
used to access data within Oracle databases.
SQL is a limited language that
allows you to directly interact with the database. You can write queries
(SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL
doesn't include all the things that normal programming languages have, such as
loops and IF...THEN...ELSE statements.
PL/SQL is a normal programming
language that includes all the features of most other programming languages.
But, it has one thing that other programming languages don't have: the ability
to easily integrate with SQL.
Some of the differences:
- SQL is executed one statement at a time.
PL/SQL is executed as a block of code.
- SQL tells the database what to do
(declarative), not how to do it. In contrast, PL/SQL tell the database how
to do things (procedural).
- SQL is used to code queries, DML and DDL
statements. PL/SQL is used to code program blocks, triggers, functions,
procedures and packages.
- You can embed SQL in a PL/SQL program, but
you cannot embed PL/SQL within a SQL statement.
[edit] Should one use
PL/SQL or Java to code procedures and triggers?
Both PL/SQL and Java can be used
to create Oracle stored procedures and triggers. This often leads to questions
like "Which of the two is the best?" and "Will Oracle ever
desupport PL/SQL in favour of Java?".
Many Oracle applications are
based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL.
In fact, all indications are that PL/SQL still has a bright future ahead of it.
Many enhancements are still being made to PL/SQL. For example, Oracle 9i
supports native compilation of Pl/SQL code to binaries. Not to mention the
numerous PL/SQL enhancements made in Oracle 10g and 11g.
PL/SQL and Java appeal to
different people in different job roles. The following table briefly describes
the similarities and difference between these two language environments:
PL/SQL:
- Can be used to create Oracle packages,
procedures and triggers
- Data centric and tightly integrated into the
database
- Proprietary to Oracle and difficult to port
to other database systems
- Data manipulation is slightly faster in
PL/SQL than in Java
- PL/SQL is a traditional procedural
programming language
Java:
- Can be used to create Oracle packages,
procedures and triggers
- Open standard, not proprietary to Oracle
- Incurs some data conversion overhead between
the Database and Java type
- Java is an Object Orientated language, and
modules are structured into classes
- Java can be used to produce complete
applications
PS: Starting with Oracle 10g,
.NET procedures can also be stored within the database (Windows only). Nevertheless,
unlike PL/SQL and JAVA, .NET code is not usable on non-Windows systems.
PS: In earlier releases of Oracle
it was better to put as much code as possible in procedures rather than
triggers. At that stage procedures executed faster than triggers as triggers
had to be re-compiled every time before executed (unless cached). In more
recent releases both triggers and procedures are compiled when created (stored
p-code) and one can add as much code as one likes in either procedures or
triggers.
[edit] How can one see
if somebody modified any code?
The source code for stored
procedures, functions and packages are stored in the Oracle Data Dictionary.
One can detect code changes by looking at the TIMESTAMP and LAST_DDL_TIME
column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
Note: If you recompile an object,
the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated.
If you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are
updated.
[edit] How can one
search PL/SQL code for a string/ key value?
The following query is handy if
you want to know where certain tables, columns and expressions are referenced
in your PL/SQL source code.
SELECT type, name, line
FROM user_source
WHERE UPPER(text) LIKE UPPER('%&KEYWORD%');
If you run the above query from
SQL*Plus, enter the string you are searching for when prompted for KEYWORD. If
not, replace &KEYWORD with the string you are searching for.
[edit] How does one keep
a history of PL/SQL code changes?
One can build a history of PL/SQL
code changes by setting up an AFTER CREATE schema (or database) level trigger
(available from Oracle 8.1.7). This will allow you to easily revert to previous
code should someone make any catastrophic changes. Look at this example:
CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*
FROM ALL_SOURCE WHERE 1=2;
CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY',
'TYPE', 'TYPE BODY')
THEN
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, all_source.* FROM ALL_SOURCE
WHERE TYPE = ORA_DICT_OBJ_TYPE -- DICTIONARY_OBJ_TYPE IN 8i
AND NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
A better approach is to create an
external CVS or SVN repository for the
scripts that install the PL/SQL code. The canonical version of what's in the
database must match the latest CVS/SVN version or else someone would be
cheating.
[edit] How can I protect
my PL/SQL source code?
Oracle provides a binary wrapper
utility that can be used to scramble PL/SQL source code. This utility was
introduced in Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin
directory.
The utility use human-readable
PL/SQL source code as input, and writes out portable binary object code
(somewhat larger than the original). The binary code can be distributed without
fear of exposing your proprietary algorithms and methods. Oracle will still
understand and know how to execute the code. Just be careful, there is no
"decode" command available. So, don't lose your source!
The syntax is:
wrap iname=myscript.pls oname=xxxx.plb
Please note: there is no way to
unwrap a *.plb binary file. You are supposed to backup and keep your *.pls
source files after wrapping them.
[edit] Can one print to
the screen from PL/SQL?
One can use the DBMS_OUTPUT
package to write information to an output buffer. This buffer can be displayed
on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON;
command. For example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/
DBMS_OUTPUT is useful for
debugging PL/SQL programs. However, if you print too much, the output buffer
will overflow. In that case, set the buffer size to a larger value, eg.: set
serveroutput on size 200000
If you forget to set serveroutput
on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;.
If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable
procedure, SQL*Plus will display the entire contents of the buffer when it
executes this dummy PL/SQL block.
Note that DBMS_OUTPUT doesn't
print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT
WRAP; Look at this example with this option first disabled and then enabled:
SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line();
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line();
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
[edit] Can one
read/write files from PL/SQL?
The UTL_FILE database package can
be used to read and write operating system files.
A DBA user needs to grant you
access to read from/ write to a specific directory before using this package.
Here is an example:
CONNECT / AS SYSDBA
CREATE OR REPLACE DIRECTORY mydir AS '/tmp';
GRANT read, write ON DIRECTORY mydir TO scott;
Provide user access to the
UTL_FILE package (created by catproc.sql):
GRANT EXECUTE ON UTL_FILE TO scott;
Copy and paste these examples to
get you started:
Write File
DECLARE
fHandler UTL_FILE.FILE_TYPE;
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'w');
UTL_FILE.PUTF(fHandler, 'Look ma, Im writing to a file!!!\n');
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/
Read File
DECLARE
fHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'r');
UTL_FILE.GET_LINE(fHandler, buf);
dbms_output.put_line('DATA FROM FILE: '||buf);
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/
NOTE: UTL_FILE was introduced
with Oracle 7.3. Before Oracle 7.3 the only means of writing a file was to use
DBMS_OUTPUT with the SQL*Plus SPOOL command.
[edit] Can one call DDL
statements from PL/SQL?
One can call DDL statements like
CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE
IMMEDIATE" statement (native SQL). Examples:
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
begin execute Immediate 'TRUNCATE TABLE emp'; end;
DECLARE
var VARCHAR2(100);
BEGIN
var := 'CREATE TABLE temp1(col1 NUMBER(2))';
EXECUTE IMMEDIATE var;
END;
NOTE: The DDL statement in quotes
should not be terminated with a semicolon.
Users running Oracle versions
below Oracle 8i can look at the DBMS_SQL
package (see FAQ about Dynamic SQL).
[edit] Can one use
dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can
use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and
PL/SQL statements (statements created at run-time). Look at these examples.
Note that the statements within quotes are NOT semicolon terminated:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
-- Using bind variables...'
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older
DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these
examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
More complex DBMS_SQL example
using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/
[edit] What is the
difference between %TYPE and %ROWTYPE?
Both %TYPE and %ROWTYPE
are used to define variables in PL/SQL as it is defined within the database. If
the datatype or precision of a column changes, the program automatically picks
up the new definition from the database without having to make any code
changes.
The %TYPE and %ROWTYPE
constructs provide data independence, reduces maintenance costs, and allows
programs to adapt as the database changes to meet new business needs.
%TYPE
%TYPE is used to declare a field
with the same type as that of a specified table's column. Example:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/
%ROWTYPE
%ROWTYPE is used to declare a record
with the same types as found in the specified database table, view or cursor.
Examples:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/
[edit] How does one get
the value of a sequence into a PL/SQL variable?
As you might know, one cannot use
sequences directly from PL/SQL; Oracle prohibits this:
i := sq_sequence.NEXTVAL;
However, one can use embedded SQL
statements to obtain sequence values:
select sq_sequence.NEXTVAL into :i from dual;
This restriction has been removed
in oracle 11g and the former syntax can be used.
[edit] Can one execute
an operating system command from PL/SQL?
There is no direct way to execute
operating
system commands from PL/SQL. PL/SQL doesn't have a "host" command, as
with SQL*Plus,
that allows users to call OS commands. Nevertheless, the following workarounds
can be used:
Database Pipes
Write an external program (using
one of the precompiler
languages, OCI or Perl with Oracle access
modules) to act as a listener on a database pipe (SYS.DBMS_PIPE).
Your PL/SQL program then put requests to run commands in the pipe, the listener
picks it up and run the requests. Results are passed back on a different
database pipe. For an Pro*C
example, see chapter 8 of the Oracle Application Developers Guide.
CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
RETURN INTEGER IS
status NUMBER;
errormsg VARCHAR2(80);
pipe_name VARCHAR2(30);
BEGIN
pipe_name := 'HOST_PIPE';
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message(pipe_name);
RETURN status;
END;
/
External Procedure Listeners:
From Oracle 8 one
can call external 3GL
code in a dynamically linked library (DLL or shared object). One just write a
library in C/ C++ to do whatever is required. Defining this C/C++ function to
PL/SQL makes it executable. Look at this External Procedure
example.
Using Java
See example at http://www.orafaq.com/scripts/plsql/oscmd.txt
DBMS_SCHEDULER
In Oracle 10g
and above, one can execute OS commands via the DBMS_SCHEDULER
package. Look at this example:
BEGIN
dbms_scheduler.create_job(job_name => 'myjob',
job_type => 'executable',
job_action => '/app/oracle/x.sh',
enabled => TRUE,
auto_drop => TRUE);
END;
/
exec dbms_scheduler.run_job('myjob');
[edit] How does one loop
through tables in PL/SQL?
One can make use of cursors to
loop through data within tables. Look at the following nested loops code
example.
DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is '||emp_rec.ename);
END LOOP;
END LOOP;
END;
/
[edit] How often should
one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular belief, one
should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555
(Snapshot too old) errors. The higher the frequency of commit, the sooner the
extents in the undo/ rollback segments will be cleared for new transactions,
causing ORA-1555 errors.
To fix this problem one can
easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
COMMIT;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) = 0 THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
COMMIT;
If you still get ORA-1555 errors,
contact your DBA to increase the undo/ rollback segments.
NOTE: Although fetching across
COMMITs work with Oracle, is not supported by the ANSI standard.
[edit] I can SELECT from
SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges
given directly to the user, but does not observe privileges given through
roles. The consequence is that a SQL statement can work in SQL*Plus, but will
give an error in PL/SQL. Choose one of the following solutions:
- Grant direct access on the tables to your
user. Do not use roles!
GRANT select ON scott.emp TO my_user;
- Define your procedures with invoker rights
(Oracle 8i and higher);
create or replace procedure proc1
authid current_user is
begin
...
- Move all the tables to one user/schema.
[edit] What is a
mutating and constraining table?
"Mutating" means
"changing". A mutating table is a table that is currently being
modified by an update, delete, or insert statement. When a trigger tries to
reference a table that is in state of flux (being changed), it is considered
"mutating" and raises an error since Oracle should not return data
that has not yet reached its final state.
Another way this error can occur
is if the trigger has statements to change the primary, foreign or unique key columns
of the table off which it fires. If you must have triggers on tables that have
referential constraints, the workaround is to enforce the referential integrity
through triggers as well.
There are several restrictions in
Oracle regarding triggers:
- A row-level trigger cannot query or modify a
mutating table. (Of course, NEW and OLD still can be accessed by the
trigger).
- A statement-level trigger cannot query or
modify a mutating table if the trigger is fired as the result of a CASCADE
delete.
- Etc.
[edit] Can one pass an
object/table as an argument to a remote procedure?
The only way to reference an
object type between databases is via a database link. Note that it is not
enough to just use "similar" type definitions. Look at this example:
-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
BEGIN
-- do something with TabX from database B
null;
END;
/
-- Database B: sends a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
TabX DBMS_SQL.VARCHAR2S@DBLINK2;
BEGIN
pcalled@DBLINK2(TabX);
END;
/
[edit] What is the
difference between stored procedures and functions?
- Functions MUST return a value, procedures
don't need to.
- You can have DML (insert,update, delete)
statements in a function. But, you cannot call such a function in a SQL
query. For example, if you have a function that is updating a table, you
cannot call that function from a SQL query.
- select myFunction(field) from sometable; will throw error.
However an autonomous transaction function can.
- You cannot call a procedure in a SQL query.
[edit] Is there a PL/SQL
Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does
not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly
to the database engine for execution. This makes it much more efficient as SQL
statements are not stripped off and sent to the database individually.
[edit] Is there a limit
on the size of a PL/SQL block?
Yes, the max size is not an
explicit byte limit, but related to the parse tree that is created when you
compile the code. You can run the following select statement to query the size
of an existing package or procedure:
SQL> select * from dba_object_size where name = 'procedure_name';
[edit] What are the
PL/SQL compiler limits for block, record, subquery and label nesting?
The following limits apply:
Level of Block Nesting: 255
Level of Record Nesting: 32
Level of Subquery Nesting: 254
Level of Label Nesting: 98
[edit] Can one COMMIT/
ROLLBACK from within a trigger?
A commit inside a trigger would
defeat the basic definition of an atomic transaction (see ACID). Trigger logic is
by definition an extension of the original DML operation. Changes made
within triggers
should thus be committed or rolled back as part of the transaction
in which they execute. For this reason, triggers are NOT allowed to execute COMMIT or ROLLBACK
statements (with the exception of autonomous triggers). Here is an example of
what will happen when they do:
SQL> CREATE TABLE tab1 (col1 NUMBER);
Table created.
SQL> CREATE TABLE log (timestamp DATE, operation VARCHAR2(2000));
Table created.
SQL> CREATE TRIGGER tab1_trig
2 AFTER insert ON tab1
3 BEGIN
4 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
5 COMMIT;
6 END;
7 /
Trigger created.
SQL> INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (1)
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TAB1_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.TAB1_TRIG'
Autonomous transactions:
As workaround, one can use autonomous transactions. Autonomous
transactions execute separate from the current transaction.
Unlike regular triggers,
autonomous triggers can contain COMMIT and ROLLBACK statements. Example:
SQL> CREATE OR REPLACE TRIGGER tab1_trig
2 AFTER insert ON tab1
3 DECLARE
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
7 COMMIT; -- only allowed in autonomous triggers
8 END;
9 /
Trigger created.
SQL> INSERT INTO tab1 VALUES (1);
1 row created.
Note that with the above example
will insert and commit log entries - even if the main transaction is
rolled-back!
PL/SQL
From Wikipedia, the free encyclopedia
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. PL/SQL's syntax strongly resembles that of Ada, and just like some 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.
[edit] Functionality
PL/SQL supports variables, conditions, loops,
arrays
(in somewhat unusual way) and exceptions. Implementations from version 8 of
Oracle Database onwards have included features associated with object-orientation. PL/SQL, however, as a Turing-complete procedural language that fills
in these gaps, allows Oracle database developers to interface with the
underlying relational database in an imperative manner.
SQL statements can make explicit in-line calls to PL/SQL functions, or can
cause PL/SQL triggers to fire upon pre-defined Data Manipulation Language (DML) events.
PL/SQL stored
procedures (functions, procedures, packages, and
triggers) performing DML will get compiled into an Oracle database: to this
extent, their SQL code can undergo syntax-checking. Programmers working in an
Oracle database environment can construct PL/SQL blocks
of functionality to serve as procedures, functions; or they can write in-line segments
of PL/SQL within SQL*Plus scripts.
While programmers can readily
incorporate SQL DML statements into PL/SQL (as cursor definitions, for example,
or using the SELECT ... INTO syntax), Data Definition Language (DDL) statements
such as CREATE TABLE/DROP INDEX etc. require the use of "Dynamic
SQL". Earlier versions of Oracle Database required the use of a complex
built-in DBMS_SQL package
for Dynamic SQL where the system needed to explicitly parse and execute an SQL
statement. Later versions have included an EXECUTE IMMEDIATE syntax called "Native Dynamic
SQL" which considerably simplifies matters. Any use of DDL in an Oracle
database will result in an implicit COMMIT. Programmers can also use Dynamic
SQL to execute DML where they do not know the exact content of the statement in
advance.
PL/SQL offers several pre-defined
packages for specific purposes. Such PL/SQL packages include:
- DBMS_OUTPUT - for output operations to
non-database destinations
- DBMS_JOB - for running specific
procedures/functions at a particular time (i.e. scheduling)
- DBMS_XPLAN - for formatting Explain
Plan output
- DBMS_SESSION - provides access to SQL ALTER
SESSION and SET ROLE statements, and other session information.
- DBMS_METADATA - for extracting meta data from
the data dictionary (such as DDL statements)
- DBMS_EPG - for managing the built-in
webserver (Embedded PL/SQL Gateway) in the database
- UTL_FILE - for reading and writing files on
disk
- UTL_HTTP - for making requests to web servers
from the database
- UTL_SMTP - for sending mail from the database
(via an SMTP server)
and many more - Oracle
Corporation customarily adds more packages and/or extends package functionality
with each successive release of Oracle Database.
[edit] Basic code
structure
Anonymous
blocks are the basis of standalone PL/SQL scripts, and have the following
structure:
<<label>>
DECLARE
TYPE / item / FUNCTION / PROCEDURE declarations
BEGIN
Statements
EXCEPTION
EXCEPTION handlers
END label;
The <<label>> and the DECLARE and EXCEPTION sections are optional.
Exceptions, errors which arise
during the execution of the code, have one of two types:
- Predefined exceptions
- User-defined exceptions.
User-defined exceptions are
always raised explicitly by the programmers, using the RAISE or RAISE_APPLICATION_ERROR commands, in any situation where they
have determined that it is impossible for normal execution to continue. RAISE command has the syntax:
RAISE <exception name>;
Oracle Corporation has
pre-defined several exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, etc. Each exception has a SQL
Error Number and SQL Error Message associated with it. Programmers can access
these by using the SQLCODE
and SQLERRM functions.
The DECLARE section defines and
(optionally) initialises variables. If not initialised specifically, they
default to NULL.
For example:
DECLARE
number1 NUMBER(2);
number2 NUMBER(2) := 17; -- value default
text1 VARCHAR2(12) := 'Hello world';
text2 DATE := SYSDATE; -- current date and time
BEGIN
SELECT street_number
INTO number1
FROM address
WHERE name = 'BILLA';
END;
The symbol := functions as an
assignment operator to store a value in a variable.
The major datatypes in
PL/SQL include NUMBER, INTEGER, CHAR, VARCHAR2, DATE, TIMESTAMP, TEXT etc.
[edit] Functions
Functions in PL/SQL are a
collection of SQL and
PL/SQL statements that perform a task and should return a value to the calling
environment.
CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
<IS|AS>
[declaration block]
BEGIN
<PL/SQL block WITH RETURN statement>
[EXCEPTION
EXCEPTION block]
END;
[edit] Procedures
Procedures are the same as
Functions, in that they are also used to perform some task with the difference
being that procedures cannot be used in a SQL statement and although they can
have multiple out parameters they do not return a value. This is not alway's
true for when an NVL function is used.
[edit] Anonymous Blocks
Anonymous PL/SQL blocks can be embedded in an Oracle
Precompiler or OCI program. At run time, the program, lacking a local PL/SQL
engine, sends these blocks to the Oracle server, where they are compiled and
executed. Likewise, interactive tools such as SQL*Plus and Enterprise Manager,
lacking a local PL/SQL engine, must send anonymous blocks to Oracle.
[edit] Packages
Packages are groups of
conceptually linked Functions, Procedures,Variable,Constants & Cursors etc.
The use of packages promotes re-use of code. Packages usually have two parts, a
specification and a body, although sometimes the body is unnecessary. The
specification (spec for short) is the interface to your applications; it
declares the types, variables, constants, exceptions, cursors, and subprograms
available for use. The body fully defines cursors and subprograms, and so
implements the spec.
[edit] Numeric variables
variable_name NUMBER(P[,S]) := VALUE;
To define a numeric variable, the
programmer appends the variable type NUMBER to the name definition. To
specify the (optional) precision(P) and the (optional) scale (S), one can
further append these in round brackets, separated by a comma. ("Precision"
in this context refers to the number of digits which the variable can hold,
"scale" refers to the number of digits which can follow the decimal
point.)
A selection of other datatypes
for numeric variables would include: binary_float,
binary_double, dec, decimal, double precision, float, integer, int, numeric,
real, smallint, binary_integer
[edit] Character variables
variable_name varchar2(L) := 'Text';
To define a character variable,
the programmer normally appends the variable type VARCHAR2 to the name
definition. There follows in brackets the maximum number of characters which
the variable can store.
Other datatypes for character
variables include:
varchar, char, long, raw, long raw, nchar, nchar2, clob,
blob, bfile
[edit] Date variables
variable_name date := '01-Jan-2005';
Oracle provides a number of data
types that can store dates (DATE, DATETIME, TIMESTAMP etc.), however
DATE is most commonly used.
Programmers define date variables
by appending the datatype code "DATE" to a variable name. The TO_DATE function can be used to convert strings
to date values. The function converts the first quoted string into a date,
using as a definition the second quoted string, for example:
TO_DATE('31-12-2004','dd-mm-yyyy')
or
TO_DATE ('31-Dec-2004','dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')
To convert the dates to strings
one uses the function TO_CHAR (date_string, format_string).
PL/SQL also supports the use of
ANSI date and interval literals.[1]
The following clause gives an 18-month range:
WHERE dateField BETWEEN DATE '2004-12-31' - INTERVAL '1-6' YEAR TO MONTH
AND DATE '2004-12-31'
[edit] Datatypes for
specific columns
Variable_name Table_name.Column_name%type;
This syntax defines a variable of
the type of the referenced column on the referenced tables.
Programmers specify user-defined
datatypes with the syntax:
type data_type is record (field_1 type_1 :=xyz, field_2 type_2 :=xyz, ..., field_n type_n :=xyz);
For example:
DECLARE
TYPE t_address IS RECORD (
name address.name%TYPE,
street address.street%TYPE,
street_number address.street_number%TYPE,
postcode address.postcode%TYPE);
v_address t_address;
BEGIN
SELECT name, street, street_number, postcode INTO v_address FROM address WHERE ROWNUM = 1;
END;
This sample program defines its
own datatype, called t_address, which contains the fields name,
street, street_number and postcode.
so according the example we are
able to copy the data from database to the fields in program. Using this
datatype the programmer has defined a variable called v_address and
loaded it with data from the ADDRESS table.
Programmers can address
individual attributes in such a structure by means of the dot-notation, thus: "v_address.street :=
'High Street';"
[edit] Conditional
Statements
The following code segment shows
the IF-THEN-ELSIF construct. The ELSIF and ELSE parts are optional so it is
possible to create simpler IF-THEN or, IF-THEN-ELSE constructs.
IF x = 1 THEN
sequence_of_statements_1;
ELSIF x = 2 THEN
sequence_of_statements_2;
ELSIF x = 3 THEN
sequence_of_statements_3;
ELSIF x = 4 THEN
sequence_of_statements_4;
ELSIF x = 5 THEN
sequence_of_statements_5;
ELSE
sequence_of_statements_N;
END IF;
The CASE statement simplifies
some large IF-THEN-ELSE structures.
CASE
WHEN x = 1 THEN sequence_of_statements_1;
WHEN x = 2 THEN sequence_of_statements_2;
WHEN x = 3 THEN sequence_of_statements_3;
WHEN x = 4 THEN sequence_of_statements_4;
WHEN x = 5 THEN sequence_of_statements_5;
ELSE sequence_of_statements_N;
END CASE;
CASE statement can be used with
predefined selector:
CASE x
WHEN 1 THEN sequence_of_statements_1;
WHEN 2 THEN sequence_of_statements_2;
WHEN 3 THEN sequence_of_statements_3;
WHEN 4 THEN sequence_of_statements_4;
WHEN 5 THEN sequence_of_statements_5;
ELSE sequence_of_statements_N;
END CASE;
[edit] Array handling
PL/SQL refers to arrays
as "collections". The language offers three types of collections:
- Index-by tables (associative arrays)
- Nested tables
- Varrays (variable-size arrays)
Programmers must specify an upper
limit for varrays, but need not for index-by tables or for nested tables. The
language includes several collection methods used to manipulate collection
elements: for example FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE, etc.
Index-by tables can be used to simulate associative arrays, as in this example of a memo function for Ackermann's function
in PL/SQL.
[edit] Looping
As a procedural language by
definition, PL/SQL provides several iteration
constructs, including basic LOOP statements, WHILE loops,
FOR loops,
and Cursor FOR loops.
[edit] LOOP statements
Syntax:
<<parent_loop>>
LOOP
statements
<<child_loop>>
LOOP
statements
EXIT parent_loop WHEN <condition>; -- Terminates both loops
EXIT WHEN <condition>; -- Returns control to parent_loop
END LOOP;
EXIT WHEN <condition>;
END LOOP parent_loop;
Loops can be terminated by using
the EXIT keyword, or by raising an exception.
[edit] FOR loops
[edit] Cursor FOR loops
FOR RecordIndex IN (SELECT person_code FROM people_table)
LOOP
DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
END LOOP;
Cursor-for loops automatically
open a cursor, read in their data and close the cursor again
As an alternative, the PL/SQL
programmer can pre-define the cursor's SELECT-statement in advance in order
(for example) to allow re-use or to make the code more understandable
(especially useful in the case of long or complex queries).
DECLARE
CURSOR cursor_person IS
SELECT person_code FROM people_table;
BEGIN
FOR RecordIndex IN cursor_person
LOOP
DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
END LOOP;
END;
The concept of the person_code
within the FOR-loop gets expressed with dot-notation ("."):
RecordIndex.person_code
[edit] Example
DECLARE
var NUMBER;
BEGIN
/*N.B. for loop variables in pl/sql are new declarations, with scope only inside the loop */
FOR var IN 0 ... 10 LOOP
DBMS_OUTPUT.put_line(var);
END LOOP;
IF (var IS NULL) THEN
DBMS_OUTPUT.put_line('var is null');
ELSE
DBMS_OUTPUT.put_line('var is not null');
END IF;
END;
Output:
0
1
2
3
4
5
6
7
8
9
10
var is null
[edit] Similar languages
PL/SQL functions analogously to the
embedded procedural languages associated with other relational databases. Sybase ASE and Microsoft SQL Server have Transact-SQL,
PostgreSQL
has PL/pgSQL
(which tries to emulate PL/SQL to an extent), and IBM DB2
includes SQL Procedural Language,[2]
which conforms to the ISO
SQL’s SQL/PSM
standard.
The designers of PL/SQL modelled
its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL
also resembles Pascal in numerous aspects. The structure of a PL/SQL package
closely resembles the basic Pascal program structure or a Borland
Delphi unit. Programmers can define global data-types, constants and static
variables, public and private, in a PL/SQL package.
PL/SQL also allows for the
definition of classes and instantiating these as objects in PL/SQL code. This
resembles usages in object-oriented programming languages
like Object
Pascal, C++
and Java. PL/SQL refers to a class as an
"Advanced Data Type" (ADT) or "User Defined Type"(UDT), and
defines it as an Oracle SQL data-type as opposed to a PL/SQL
user-defined type, allowing its use in both the Oracle
SQL Engine and the Oracle PL/SQL engine. The constructor and methods
of an Advanced Data Type are written in PL/SQL. The resulting Advanced Data
Type can operate as an object class in PL/SQL. Such objects can also persist as
column values in Oracle database tables.
PL/SQL does not resemble Transact-SQL,
despite superficial similarities. Porting code from one to the other usually
involves non-trivial work, not only due to the differences in the feature sets
of the two languages, but also due to the very significant differences in the
way Oracle and SQL Server deal with concurrency and locking.
The Fyracle project
aims to enable the execution of PL/SQL code in the open-source
Firebird database.
