PLSQL Tutorial
-
This PLSQL tutorial is just a short introduction to the language.
Probably the best way to learn PL/SQL (or any programming language) is
to go on
an instructor-led course and then practice what you've
learnt. However
if you prefer (or have no choice but) to learn on your own
then this tutorial
will get you off to a flying start. |
|
The most important thing that any tutorial on PLSQL should state - and
the most important thing you should remember - is that you shouldn't
use PL/SQL when it
would be quicker and easier to use SQL. That may sound obvious but it is a common mistake. Developer's get stuck in the mindset that they're using PL/SQL or SQL so everything has to be done in that. Let's take the example of |
UPDATE my_table SET col_1 = new_val
WHERE col_1 = old_val;
Whereas in PL/SQL we would have to do something like this:
FOR rec IN (SELECT key_col,col_1 FROM my_table) LOOP
IF
rec.col_1 = old_val THEN
UPDATE
my_table SET col_1 = new_val WHERE key_col = rec.key_col;
END
IF;
END LOOP;This is completely unnecessary. In fact it's worse than that - it's really a crime against good programming practice! Not only have you created more lines of code to be maintained, but the performance will be much worse due to the context switching between PL/SQL and SQL and the repeated issuing of the update command.
That's not to say you shouldn't use PLSQL or that PL/SQL is inefficient - just don't use PL/SQL when using SQL would be more appropriate.
The reverse is also true - there are times when it is far easier to write some simple logic in PLSQL than to construct some highly complex SQL statement full of sub-queries and views that nobody is ever going to be able to understand and is probably less efficient anyway.
With that said let's continue this PLSQL tutorial to give you a feel for the language by writing a "hello world" program as our first example.
How To Display "Hello World"
BEGINDBMS_OUTPUT.PUT_LINE('Hello
World');
END;That's it! Run this piece of PL/SQL code in SQL*Plus/TOAD/SQL Developer and you'll get "Hello World" displayed on your screen.
A couple of things to note when using SQL*Plus to run your PL/SQL: you'll need to enter "/" (without the quotes) on a new line after your last line of code to get it run in SQL*Plus and if you want to see the output you'll need to issue the command set serveroutput on at the command prompt. You only need to issue this once when you log in or change to a new user.
Let's continue our PLSQL tutorial by dissecting this wonderful PLSQL program to see how it works.
We start with BEGIN which is a reserved word (notice there's no "CLASS" or "FUNCTION" or "PROCEDURE" or "PROGRAM") then we call a library routine PUT_LINE which is part of a package called DBMS_OUTPUT and we finish with END - another reserved word.
What we've done here is create an anonymous block (literally a block with no name).
What Is A PL/SQL Block?
Before we go further with this PLSQL tutorial, we need to cover some of the theory.PLSQL is a block-structured language based on ADA so the syntax is not like Java or C++. PL/SQL does have object-oriented features but that topic is best left to an advanced PLSQL tutorial.
Oracle
Tips & Tricks to
SKYROCKET Your Career!
In PLSQL, a block is anything sandwiched between BEGIN and END statements and can be anonymous or be a named procedure, function or in-line block.
PLSQL blocks can be nested and can be grouped together into a PACKAGE - an example of which is DBMS_OUTPUT, one of the Oracle supplied packages.
What Are The Data Types In PL/SQL?
Now let's continue this PLSQL tutorial with a look at the available datatypes and then make our example a little less simple.The datatypes include all the types available in the Oracle database (CHAR, VARCHAR2, NUMBER, DATE, etc) plus BOOLEAN, PLS_INTEGER and BINARY_INTEGER and you can define your own datatypes based on these built-in ones.
Other Language Elements In PL/SQL
Control structures in PL/SQL include 4 types of loops (basic, cursor, FOR and WHILE loops); IF THEN ELSE, and CASE statements as well as the GOTO statement which enables you to jump to a label elsewhere in your code.Comments in PL/SQL are defined by preceding the text with -- for single line comments or by wrapping the text in /* and */ for multi-line comments.
And of course there are type and variable declaration statements. For example:
TYPE my_tab is TABLE of VARCHAR2 INDEX BY BINARY_INTEGER;
-- PL/SQL type
names my_tab; -- PL/SQL variable
idx BINARY_INTEGER; -- array index
N.B. PL/SQL is not case sensitive but by convention reserved words are written in upper case.
In the above PLSQL example we've defined a new type (my_tab), a variable (names) and an index (idx) for our table.
PL/SQL Examples
Let's continue our PLSQL tutorial by using these features.DECLARE
i
NUMBER:
BEGIN
FOR
i in 1..100 LOOP
DBMS_OUTPUT.PUT_LINE('Hello World');
END
LOOP;
END;Here we've taken our PL/SQL program, declared a variable and used a FOR loop, the result of which would be "Hello World" printed on your screen 100 times!
The next example in our PLSQL tutorial will show you how to nest PL/SQL blocks, retrieve data from your Oracle database, the use of the IF statement and the EXCEPTION handler;
DECLARE
i
NUMBER:
out_str
VARCHAR2(15);
sfx
VARCHAR2(5);
FUNCTION
op_msg RETURN varchar2 IS
/* nested PLSQL function */
/* nested PLSQL function */
msg
VARCHAR2(80);
BEGIN
SELECT
'Hello World' INTO msg FROM DUAL;
RETURN
msg;
END
op_msg;
BEGIN
out_str
:=
op_msg; -- call PL/SQL function
FOR
i in 1..100 LOOP
IF
i MOD 2 = 0 THEN sfx := ' even';
ELSE sfx := ' odd';
END IF;
ELSE sfx := ' odd';
END IF;
DBMS_OUTPUT.PUT_LINE('Hello
World'||sfx);
END
LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FAILED!!');
END; In this last example in our PLSQL tutorial we nested a function inside our anonymous block and called it from the first line of executable code in our anonymous block.
The PLSQL function queried the database (in this case the special table DUAL but it could have been any table as long as only 1 row was returned) and returned the data retrieved to the caller. If you expect to receive more than 1 row of data you'll need to use an explicit cursor or a PL/SQL cursor for loop (see the PLSQL examples page for this).
We also put an IF THEN ELSE statement inside the FOR loop in the anonymous PLSQL block to change the message depending on whether the current value of the index is odd or even.
The PL/SQL exception handler is used to trap errors at run time. In this case we would just display the message "FAILED!!" if any errors were to occur when running our PL/SQL program.
Looking for PL/SQL training? Training is a highly cost-effective, proven method of boosting productivity. Click here for details of our Oracle training courses or let us know your requirements.
