oracle

Learning PL/SQL Tutorial

By On May 10, 2012 · 2 Comments

I wrote this learning tutorial to give a simple overview to developers who never used PL/SQL before. To show them how simple and useful it is. This tutorial is not a reference for PL/SQL developers. This is just an inroduction into it.

About PL/SQL

PL/SQL stands for Procedural Language extension of SQL. Oracle uses a PL/SQL engine to process the PL/SQL statements. A PL/SQL code can be stored in the client system or in the database.

Let’s Get Started

I use Toad to work with Oracle database. Suggest you use the same. However you might use SQL Manager which is a simular application.

Launch Toad, connect to your database instance and open SQL editor.

Comments / Remarks

In any programming language, meaning of a good coding is not just when code works. It’s when another developer can take the script and make changes to it without a headache and without saying “Hell with it, I will re-write it from scratch”.
When you code, leave comments as much as you can, to make your code easy to read.

PL/SQL is no different from any other language and requires comments and remarks to be left in the code.

Always start your script with the remark simular to what I have:

rem -----------------------------------------------------------------------
rem Filename:   helloWorld.SQL
rem Purpose:    Learning tutorial script
rem DATE:       1-May-2012
rem Author:     Dima Svirid
rem Description:Does this AND that
rem History:
rem 	1-May-2012 Dima Svirid - modified it TO ADD something
rem 	2-May-2012 Alex - modified it TO ADD NEW features
rem -----------------------------------------------------------------------

To add a comment block you can use – - or /* … */

-- this is my comments
 
/*
Even more comments are here
*/

Displaying PL/SQL debugging information

The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

DBMS_Output.PUT_LINE('Hello World!');

HelloWorld PL/SQL script

Below is a simple PL/SQL script that will output ‘Hello World’ into the DBMS Output window. To show DBMS Output window, in Toad from the top menu select View :: DBMS Output

rem -----------------------------------------------------------------------
rem Filename:   helloWorld.SQL
rem Purpose:    Learning tutorial script
rem DATE:       1-May-2012
rem Author:     Dima Svirid
rem Description:Does this AND that
rem History:
rem     1-May-2012 Dima Svirid - modified it TO ADD something
rem     2-May-2012 Alex - modified it TO ADD NEW features
rem -----------------------------------------------------------------------
 
DECLARE
 
	-- Declare variables here
 
BEGIN
 
	-- Your main script logic goes here
 
	-- Lets output Hello World inot DBMS output window
	DBMS_Output.PUT_LINE('Hello World!');
 
END;
/

PL/SQL variables and placeholders

Variables in PL/SQL are basically simple placeholders that store different values. On the other side placeholders are temporary storage areas. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.

-- DECLARE PLACEHOLDER
variable_name datatype; 
 
-- DECLARE VARIABLE
variable_name datatype := VALUE;

Variable/placeholder available data types:

  • Number(n,m)
  • Char(n)
  • Varchar2(n)
  • Date
  • Long
  • Long raw
  • Raw
  • Blob
  • Clob
  • Nclob
  • Bfile

IF ELSE conditional statements in PL/SQL

Syntax:

IF condition
THEN
 statement 1;
ELSIF condition2 THEN
	statement 2;
ELSE
 statement 3;
END IF;
rem -----------------------------------------------------------------------
rem Do NOT forget remarks block....
rem -----------------------------------------------------------------------
 
DECLARE
 
	-- Declare variables and placeholders
	counter INTEGER := 1; -- declare variable
 
	mystring Varchar2(64); -- declare placeholder to be used inside BEGIN
 
BEGIN
 
	-- Starting up
 
	-- Lets assign a value to placeholder 'mystring'
	mystring := 'Hello World!';
 
	-- if counter is 1 lets output value
    IF counter = 1 OR counter = 2
    THEN
        DBMS_Output.PUT_LINE(mystring);
    ELSE
        DBMS_Output.PUT_LINE('Do nothing!');
    END IF;
 
END;
/

Associative arrays in PL/SQL

They are particularly useful for name-value pair type arrays where you want to look up the value of a particular element without looping over the entire array. If you never tried them before, go a head, give it a try and you will love them.
Let’s try to create associative array with such structure as decribed below:

assoc_array = [
	'main_menu' => 'This is my Menu Title',
	'secn_menu' => 'This is my Second Menu Title'
]

In Oracle OL/SQL you will do something like this:

rem -----------------------------------------------------------------------
rem Do NOT forget remarks block....
rem -----------------------------------------------------------------------
 
DECLARE
 
    /* Declare associative array variable
        assoc_array -  is a name of the type for array
            VARCHAR2(255) - is a datatype you use for values
            VARCHAR2(64) - is a dataype you use for keys
    */
    TYPE assoc_array IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(64);
 
    -- Declare associative array placeholder named: my_assoc_array
    my_assoc_array assoc_array;
 
    current_key VARCHAR2(64); --this placeholder will be our cursor
 
BEGIN
 
    -- Fill it with some data
    my_assoc_array ('main_menu') := 'This is my Menu Title';
    my_assoc_array ('secn_menu') := 'This is my Second Menu Title';
 
    -- You can explicitly get the value from array
    DBMS_Output.PUT_LINE('Explicitly get value from key: secn_menu, Value: ' || my_assoc_array ('secn_menu') );
 
    -- Lets loop over the array elements
 
    -- First move to the first element
    current_key := my_assoc_array.FIRST;  -- Get first element of array
 
    WHILE current_key IS NOT NULL LOOP
 
        DBMS_Output.PUT_LINE('Key: ' || current_key || ', Value: ' || my_assoc_array (current_key) );
 
        current_key := my_assoc_array.NEXT(current_key);  -- Get next element of array
    END LOOP;    
 
END;
/

Let’s try to create a more complex associative array, like this:

assoc_array = [
	'main_menu' => [
		'item1' => 'Homepage',
		'item2' => 'Contact'
	],
	'secn_menu' => [
		'subitem1' => 'Footer 1',
		'subitem2' => 'Footer 2'
	]
]

In PL/SQL we would do simular thing we did before but declare another array type for the value.

rem -----------------------------------------------------------------------
rem Do NOT forget remarks block....
rem -----------------------------------------------------------------------
 
DECLARE
 
    /* Declare type for the complex value of the array
        value_array -  is a name of the type for array value
            VARCHAR2(255) - is a datatype you use for values
            VARCHAR2(64) - is a dataype you use for keys
    */
    TYPE value_array IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(64);
 
    /* Declare associative array placeholder
        assoc_array -  is a name of the type for array
            value_array - is a complex datatype you use for values
            VARCHAR2(64) - is a dataype you use for keys
    */
    TYPE assoc_array IS TABLE OF value_array INDEX BY VARCHAR2(64);
 
    -- Declare associative array placeholder named: my_assoc_array
    my_assoc_array assoc_array;
 
BEGIN
 
    -- Fill it with some data
    my_assoc_array ('main_menu') ('item1') := 'Homepage';
    my_assoc_array ('main_menu') ('item2') := 'Contact';
    my_assoc_array ('secn_menu') ('subitem1') := 'Footer 1';
    my_assoc_array ('secn_menu') ('subitem2') := 'Footer 2';
 
    -- You can explicitly get the value from array
    DBMS_Output.PUT_LINE('Explicitly get value from key: item2, Value: ' || my_assoc_array ('main_menu') ('item2') );
 
END;
/

Loop over a query results in PL/SQL

DECLARE
 
BEGIN
 
  FOR current_row IN (SELECT column1, column2 FROM mytable)
  LOOP
 
    DBMS_OUTPUT.PUT_LINE(current_row.column1);
 
  END LOOP;
 
END;

Conclusion

There is a lot more to learn, however I think this article should give beginner in PL/SQL a bit of understanding of what it is and how to use it. Good Luck with PL/SQL!

Dima Svirid

Software architect. Ajax/Javascript, HTML5, Android, iPhone/iPad, JAVA, PHP, Cold Fusion, SQL, Air, Flash, Open source software, Frameworks

More Posts

Follow Me:

Tagged with: •
 

2 Responses to Learning PL/SQL Tutorial

  1. Sarah Yasmin says:
    September 10, 2012 at 11:10 am

    Thanks for all the information, it was very helpful I really like that you are providing information on PHP and MYSQL with basic JAVASCRIPT, being enrolled in http://www.wiziq.com/course/5871-php-mysql-with-basic-javascript-integrated-course I was looking for such information online to assist me on php and mysql and your information helped me a lot. Thanks.

  2. Tran CE says:
    November 30, 2012 at 11:03 am

    Hello, I would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online? or tell me any other guidance…
    would really appreciate help… and Also I would like to thank for all the information you are providing on sql training.

Leave a Reply