Learning PL/SQL Tutorial
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, JAVA, Spring, Hibernate, AngularJs, Backbone, MongoDB, Oracle. CTO and Co-Founder of Homeadnet.com
Follow Me:
