OK, it looks like I’m going to have to work with Oracle again after some considerable time working exclusively with MS SQL Server. I’m now so used to T-SQL I thought I’d better spend some time getting up to speed with PL/SQL again. As usual there’s no detail here; this post is just an aide-mémoire.
What is PL/SQL?
The abbreviation PL/SQL refers to Oracle's Procedural Language extension to SQL. In general PL/SQL is executed on the database server but some tools (e.g. SQL Developer) can execute PL/SQL on the client. PL/SQL is a procedural language that also enables you to embed SQL statements within its procedural code.
PL/SQL basics
A PL/SQL Block consists of three sections:
- The Declaration section
- Optional
- Starts with the keyword DECLARE
- Used to declare any placeholders like variables, constants, records and cursors
- The Execution section
- Mandatory
- Starts with the keyword BEGIN and ends with END
- The Exception (or Error) Handling section
- Optional
- Starts with the keyword EXCEPTION
Basic PL/SQL looks like this:
[DECLARE
-- Variable declaration here]
BEGIN
-- Program Execution here
[EXCEPTION
-- Exception handling here]
END;
For example:
DECLARE
x NUMBER;
BEGIN
x := 123456;
dbms_output.put_line('x = ');
dbms_output.put_line(x);
END;
/
The general syntax for declaring a variable is either variable_name:= value; or assigning a value in a SQL statement using the INTO keyword. Note that if the dbms_output.put_line function doesn’t print anything try using SET SERVEROUTPUT ON first. We can select values into a variable like this:
SELECT column_name INTO variable_name FROM table_name [WHERE condition];
Records
Records are a composite data types (i.e. a combination of different scalar data types like char, varchar, number etc.). Each scalar data type in the record holds a value. A record is therefore somewhat analogous to a row of data.
TYPE record_type_name IS RECORD (first_col_name column_datatype, second_col_name column_datatype, ...);
To assign values to elements of a record use the following syntax:
record_name.column_name := value;
For example:
SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause];
Functions
NB: A function must always return a value. Basic function definitions look like this:
CREATE OR REPLACE FUNCTION my_function RETURN NUMBER AS x NUMBER; BEGIN x := 123456; RETURN x; END; /
Want to see errors?
SHOW ERRORS;
Execute the function:
SELECT my_function FROM DUAL;
Procedures
NB: A procedure may or may not return a value. A procedure is a PL/SQL Block that is stored and named for reuse. Parameters can be passed to and from a procedure in the following ways:
- IN-parameters
- OUT-parameters
- IN OUT-parameters
The basic syntax for creating a procedure is:
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] IS Declaration section BEGIN Execution section EXCEPTION Exception section END;
To execute a procedure use the following syntax. From the SQL prompt.
EXECUTE [or EXEC] procedure_name;
Or from within another procedure:
procedure_name;
The DUAL table
Why select from DUAL? The DUAL table is used in Oracle when you need to run SQL that does not have a table name. It is a special table that has exactly one row and one column (called DUMMY). Because it has one row it is guaranteed to return exactly one row in SQL statements.
Packages
“A package is a schema object that groups logically related PL/SQL types, items, and subprograms. 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.” - http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96624/09_packs.htm