At work, I had to run some updates on the Oracle Database so I took the chance to learn some PL/SQL. It turns out to be very handy. I didn’t have to write any .NET application just to be able to access Oracle, instead I can write a small Stored Procedure and run it to get the result I want.
My PL/SQL knowledge was 0 when I started yesterday, so I borrowed the Oracle 9i PL/SQL Programming book from a work buddy and started digging in. After half a day, I was able to get my little stored procedure running using cursors and reference cursors. And before I get a chance to forget the PL/SQL syntax, I am writing this summary about PL/SQL. It is not too detailed but enough to allow anybody who doesn’t know anything about Oracle PL/SQL get started in half an hour. So say if you know some sql from working with MySQL, you can use this guide to get started fairly quickly. Enjoy!
===== Getting started =====
* Each line in PL/SQL ends with semi-colon “;”
* PL/SQL is case-insensitive
* There are keywords like SELECT, CURSOR, TYPE, etc., so my convention is to CAPITALIZE those KEYWORDS. This way your eyes can pick out the keywords easier when you are debugging the code.
* Here are the naming convention, which is optional but will help you distinguish the variables better
* Local built-in type variables within a stored procedure are prefixed with v_. \\ e.g. v_inspectionId
* Cursors have prefix “c_”\\ e.g. c_Inspections
* Custom type variables have prefix “t_” \\ e.g. t_InspectionRecord
* A type for a dynamic cursor (REF CURSOR) has the Ref suffix \\ e.g. t_ViolationRef
* A record variable has the “Record” suffix \\ e.g. v_InspectionRecord
* Single-line comment follows a double-dash (- -), while the favorite C-style comment style is used for a block. Unfortunately, nested C-style commenting is not allowed.
-- This is a comment
SELECT * FROM someTable;
/*
This is a block comment
Hello! still in a comment
*/
SELECT * FROM someOtherTable;
===== Creating a stored procedure =====
First, let’s start with creating a new stored procedure. A procedure in PL/SQL has this overall structure:
CREATE OR REPLACE PROCEDURE procedure_name IS
... variable declarations
BEGIN
... your PL/SQL statements go here
END procedure_name;
===== Variables =====
==== Primitive-type Variables ====
Before we use any variable, we need to declare it first. We put the variable declarations after the CREATE OR REPLACE and before the BEGIN block. Variable name has to be less than 30 characters. We can declare the variables like this
v_number NUMBER;
v_description VARCHAR2;
In case we’d like the variable to describe a data row, we can declare
v_someVariable someTable.someColumn%ROWTYPE;
**%ROWTYPE** is the special property of the column to describe its data type.
==== CURSORS ====
Cursors can be used as a pointer to the result set returned by a SELECT. We can loop through a cursor and do calculations and so on. There are 2 types of cursor: Static Cursor and Dynamic (Ref) Cursor.
=== Static Cursors ===
Static cursor is declared in the declaration block with a specific SELECT statement. Something like this
-- declaration block
CURSOR c_Individuals IS
SELECT * FROM Individuals;
v_individualRecord c_Individuals%ROWTYPE;
-- begin block
BEGIN
-- .. some code
OPEN CURSOR c_Individuals;
LOOP
FETCH c_Individuals INTO v_individualRecord;
EXIT WHEN c_Individuals %NOTFOUND;
-- do some computations here
END LOOP;
-- be nice and close the cursor
CLOSE c_Individuals;
-- .... the rest of the procedure
What we just did is declare a new Cursor called “c_Individuals”, which is a pointer to the result set of the “SELECT * FROM Individuals” statement. Next, we declare a variable called v_individualRecord, whose type will be the return row of the c_Individuals cursor. Then later on in the actual body of the stored procedure, we open the CURSOR (e.g. execute the SELECT and have the Cursor pointed at the first returned row), then have a loop to fetch the Cursor into the v_individualRecord variable. However, if we don’t have no rows left in the result set, we will bail out from the Loop.
=== Reference (dynamic) Cursors ===
A Reference Cursor is a cursor that can be used to to fetch a SELECT statement within the body of the stored procedure. In contrast, for a static cursor, we can only specify which SELECT the static cursor is pointing to in the declaration area of the stored procedure.
This is the general usage for Reference cursor.
-- declaration block
TYPE t_Individuals IS REF CURSOR RETURN Individuals%ROWTYPE;
c_Individuals t_Individuals;
c_IndividualRecord c_Individuals%ROWTYPE;
BEGIN
-- now open up the cursor dynamically.
OPEN c_Individuals FOR
SELECT * FROM Individuals WHERE department_id = 50;
LOOP
FETCH c_Individuals INTO c_IndividualRecord;
EXIT WHEN c_Individuals%NOTFOUND;
-- the rest of the calculation
END LOOP;
CLOSE c_Individuals;
-- the rest of the stored procedure
First, we need to “invent” a new data type for the reference cursor with the TYPE declaration. We indicate that this t_Individuals type is a Reference cursor and this particular reference cursor will return a row containing all columns from the Individuals table. Next, we declare a cursor using the new t_Individuals type. Now c_Individuals is officially a dynamic cursor. Later on in the body block, we can open the c_Individuals cursor using a SELECT * statement.
Many of you will ask already, “what if I only need a few columns, or the SELECT of the cursor is a complicated JOIN statement, then what is the return type of t_Individuals?” In this case, our declaration for the reference cursor is a little more involved.
Say we need the cursor to return the result of a JOIN between the Individuals and the Departments tables to find out what department an individual belongs to.
-- declaration block
/* t_IndividualRecord defines the data returned by the SELECT & JOIN between Individuals and Departments */
TYPE t_IndividualRecord IS RECORD (
name Individuals.Name%TYPE,
department_id Individuals.Department_id%TYPE,
department_name Departments.Department_Name%TYPE,
);
TYPE t_IndividualRef IS REF CURSOR RETURN t_IndividualRecord;
c_Individuals t_ViolationRef;
v_IndividualRecord c_Individuals%ROWTYPE;
BEGIN
-- now we open up the cursor and fetch it
OPEN c_Individuals FOR
SELECT i.Name, i.Department_id, d.Department_Name
FROM Individuals i, departments d
WHERE i.department_id = d.id;
LOOP
FETCH c_Individuals INTO v_IndividualRecord;
EXIT WHEN c_Individuals%NOTFOUND;
-- ...
END LOOP;
-- the rest of the stored procedure
So first, we declare a new type called “t_IndividualRecord”, which is a Record. A record is a data row with predefined columns. We only need 3 columns for this particular record. Next, we define a reference cursor t_IndividualRef which returns a t_IndividualRecord. Then c_Individuals is our cursor and v_IndividualRecord is the variable to hold our information. Yup, that’s it to use dynamic cursors!
First, make a new user-defined record, then declare a reference type cursor, then the cursor itself, and lastly the variable to hold the returned value of the cursor.
===== Control Structures & Loops =====
PL/SQL supports most basic control structures.
* IF-THEN-ELSE
IF boolean_expression1 THEN
-- ...
[ELSEIF boolean_expression2 THEN
-- ... ]
[ELSE
-- ... ]
END IF;
* CASE
CASE v_IndividualRecord.Department_id
WHEN 1 THEN
-- ...
WHEN 2 THEN
-- ...
WHEN 3 THEN
-- ...
[ELSE
-- ...]
END CASE;
* CASE with no default test expression
CASE
WHEN v_IndividualRecord.Department_id = 1 THEN
-- ...
WHEN v_IndividualRecord.Department_id = 2 THEN
-- ...
WHEN v_IndividualRecord.Department_id = 3 THEN
-- ...
[ELSE
-- ...]
END CASE;
* LOOP
LOOP
-- ...
EXIT [WHEN condition];
END LOOP;
* WHILE
WHILE condition LOOP
-- ...
END LOOP;
* FOR
FOR counter IN [REVERSE] start_index .. end_index LOOP
-- ...
END LOOP;
FOR v_Counter IN 1..100 LOOP
-- ...
END LOOP;
* GOTO \\ I haven’t heard any good things about Goto. The overall opinions on GOTO is that using GOTO extensively is bad practice. Nonetheless, GOTO can be used for error-handling.
===== Debugging and Outputting to log =====
Sometimes we need to debug the stored-procedure to see what’s going on. PL/SQL has very nice line debugging capabilities. I am using the PL/SQL Developer version 7.0.3 by AllaroundAutomations and find that it’s pretty easy to use. However, I wish that the shortcuts for the line debugging are customizable, or made consistent with VisualStudio, so that I don’t have to mentally switch between 2 sets of shortcuts.
To write out to the DBMS output log, you will have to add DBMS_OUTPUT.ENABLE(1000000); before writing out the first log entry, otherwise Oracle will throw you an error. After setting DMBS_OUTPU.ENABLE(1000000), you can write an entry by using DBMS_OUTPUT.PUT_LINE( 'Hello World!' );. A variable can also be used inside the .PUT_LINE() like thisDBMS_OUTPUT.PUT_LINE('Individual: ' || v_IndividualRecord.Name || ' is in department ' || v_IndividualRecord.Department_Name);
However, if you are in a debugging session in PL/SQL Developer, you won’t see the entry in the DBMS Output window until after you are done with the debugging session. Maybe I haven’t found a “flush” method for DBMS_Output yet. Nonetheless, the Watch window always comes handy during debugging.
===== Views =====
Views are named queries, e.g. a view represents a derived table from a SELECT statement. End users can consider a view as a table. As views are treated like tables, we can also run query against the view such as SELECT and UPDATE, making our life a bit easier on complex queries. In comparison with an actual table, a view doesn’t contain data thus we cannot create index on a such view.
To create a view, we use
CREATE VIEW view_name AS
SELECT sub-query
[WITH CHECK OPTION];
The With CHECK OPTION means we cannot update the view if the select sub-query doesn’t retrieve the updated rows.
And to drop a view, we just do
DROP VIEW view_name;
Later on within our query, stored procedure body, or function body, we can refer the view directly by calling out its name.
===== Some “post-it-notes” for SQL syntax in Oracle =====
Here are some tips for SQL syntax in Oracle.
* Select a first few rows
SELECT * FROM Individuals WHERE ROWNUM <= 50;
* Columns with space between words can be put in double quote like “COLUMN NAME”. The equivalent in SQL Server is the square brackets.
* Keyword for Column alias is “**AS**”: \\ e.g. SELECT (1+1) AS one_and_one;
* For table aliasing, it’s “TableName New_Alias” \\ e.g. SELECT new_alias_for_this_table.* FROM this_table new_alias_for_this_table
* SELECT with conditional logic: In case you’d like to select a column value and do some logic with it, here is the sample syntax SELECT (CASE WHEN t.name = 'Alex' THEN 'Alex Le' ELSE t.name END ) AS name from table t
* to be continued
===== Final Remarks =====
I hope that you’ll find this quick primer guide helpful. Sometimes it’s the syntax that bogs you down when you start to work in a new environment, and this is where the guide comes in. I put this together so that everyone who has to work in PL/SQL can quickly pick up the syntax and get started right away in a minimal amount of time.
Comments, suggestions, and corrections are very welcome.
===== Reference =====
* Urman, Scott. “**Oracle9i PL/SQL Programming**”. McGraw-Hill/Osborne, Oracle Press. 2002.
* Upadhye, Suhas. “**Oracle Views: What can they do for you?**”. October 1999. Retrieved from [[http://www.cdoug.org/docs/views-1099.pdf]] on Feb 13, 2007
I was writing some code documentations (I try to make documenting a habit now) and happened to come by this page,