PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and datatypes are similar to that of Ada. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
History and support status
PL/SQL version 2.0 shipped with Oracle 7.0, PL/SQL version 2.1 with Oracle 7.1, PL/SQL version 2.2 with Oracle 7.2 and PL/SQL version 2.3 with Oracle 7.3. Starting with Oracle 8, PL/SQL's version numbers were syncronized with the database version numbers.
Today PL/SQL is a standard feature (not a cost option anymore) of the database and one of Oracle's premier development languages.
Interestingly enough, PL/SQL was not Oracle's first attempt at a procedural language for its database. In the early to mid-80's there was a product called SPUFI (Sql Procedural User Friendly Interface). It was 95% functional but had serious bugs and given other priorities, Oracle decided to shelve the effort, so SPUFI never made it out of beta.
- Addition: +
- Subtraction: -
- Multiplication: *
- Division: /
- Power: **
Give an employee a 5% raise:
v_salary := v_salary + v_salary * .05;
Determines an employee's after tax wage:
v_sal_after_tax := v_wage - v_tax;
Order Precedence of Arithmetic Operators
- +,- (as UNARY operators) - Identity, Negation
- *,/ "Multiplication, Division"
- +,- (as BINARY operators) "Addition, Subtraction"
SELECT 8+9*4 AS example1 FROM dual; EXAMPLE1 -------- 44
The incorrect answer would be 68 if you applied your arithmetic from left to right ignoring precedence.
SELECT -7+3*5 AS example1 FROM dual; EXAMPLE1 -------- 8
SELECT -2*6+1 AS example1 FROM dual; EXAMPLE1 -------- -11
- Greater Than: >
- Greater Than or Equal To: >=
- Less Than: <
- Less Than or Equal to: <=
- Equivalence: =
- Inequality: != ^= <> ¬= (depends on platform)
IF v_salary > 40000 THEN ... END IF;
- Concatenate: ||
v_newstr := v_str1 || ', ' || v_str2;
- Addition: +
- Subtraction: -
v_tomorrow := SYSDATE + 1;
Basic PL/SQL types
Declaring a variable with the same type as a database column. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
The syntax for anchored type declarations is:
<var-name> <obj>%type [not null][:= <init-val>];
DECLARE v_title Books.title%type; /* name is defined as the same type as column 'title' of table Books */
DECLARE v_commission number(5,2) := 12.5; v_var1 v_commission%type; /* x is defined as the same type as variable 'v_commission' */
- Anchored variables allow for the automatic synchronization of the type of anchored variable with the type of <obj> when there is a change to the <obj> type.
- Anchored types are evaluated at compile time, so recompile the program to reflect the change of <obj> type in the anchored variable.
Declaring a record with the same columns and types as a database table. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
DECLARE v_books_rec Books%rowtype;
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar data types. Each element has a unique subscript that determines its position in the collection.
--Define a PL/SQL record type representing a book: TYPE book_rec IS RECORD (title book.title%TYPE, author book.author_last_name%TYPE, year_published book.published_date%TYPE)); --define a PL/SQL table containing entries of type book_rec: Type book_rec_tab IS TABLE OF book_rec%TYPE INDEX BY BINARY_INTEGER; v_book_rec book_rec%TYPE; v_book_rec_tab book_rec_tab%TYPE; ... v_book_rec := v_book_rec_tab(5); find_authors_books(v_book_rec.author); ...
Some of the reasons why one should use collections:
- Dramatically faster execution speed, thanks to transparent performance boosts including a new optimizing compiler, better integrated native compilation, and new data types that help for number-crunching applications.
- The FORALL statement, made even more flexible and useful. For example, FORALL now supports nonconsecutive indexes.
- Regular expressions are available in PL/SQL in the form of three new functions (REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR) and the REGEXP_LIKE operator for comparisons.
- Collections, improved to include such things as collection comparison for equality and support for set operations on nested tables.
IF v_salary > 40000 THEN ... ELSIF v_salary < 1000 THEN ... ELSE ... END IF;
CASE v_grade WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); WHEN 'D' THEN dbms_output.put_line('Fair'); WHEN 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('Error: No such grade'); END CASE
i := 1; LOOP i := i + 1; dbms_output.put_line('i = '|| i); EXIT WHEN i > 10; END LOOP;
FOR i IN 1..10 LOOP CONTINUE WHEN (i = 5); dbms_output.put_line('i = '|| i); END LOOP;
WHILE i < 10 LOOP i := 1 + 1; dbms_output.put_line('i = '|| i); END LOOP;
- To exit a loop use the EXIT WHEN statement;
- To skip to the next iteration of the loop, use the CONTINUE WHEN statement (Oracle 11g and above).
The GOTO statement. Not recommended to use.
goto the_end; <<the_end>> dbms_output.put_line ('The End.');
Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors:
PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. An implicit cursor is of the variety:
SELECT employee_fname INTO :memployee_fname FROM employee WHERE employee_no = 100
For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:
DECLARE CURSOR c1 IS SELECT employee_no, employee_fname FROM employee WHERE deptno = 20; ...
When to use
A SELECT INTO clause will make two accesses to the database when it queries in a value, to determine whether or not it should raise the TOO_MANY_ROWS exception. With an explicit cursor, one fetch can be explicitly stated to be performed, assuming somewhat correctly that there are no duplicate primary keys returned. Thus on average, an implicit cursor will make two accesses to the database on a single row query while an explicit cursor will make only one. This could be a significant performance advantage, especially in client-server environments.
Anonymous PL/SQL blocks
An anonymous PL/SQL block is a block of code that is not stored in the database. When submitted to the database, the code will be executed immediately. An anonymous PL/SQL block always starts with DECLARE or BEGIN
BEGIN dbms_output.put_line('Hi Ma, I can write PL/SQL'); END; /
Stored PL/SQL blocks
A function returns a value to the caller.
The syntax for a function is
CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section return [return value] [EXCEPTION exception_section] END [procedure_name];
CREATE OR REPLACE FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2) RETURN DATE IS BEGIN IF dateString IS NULL THEN return NULL; ELSE return to_date(dateString, dateFormat); END IF; END to_date_check_null; /
A procedure differs from a function in that it does not return a value to the caller.
The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [IN|OUT|IN OUT] [DEFAULT value] [,parameter]) ] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name];
When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
- IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
- OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
- IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
The following is a simple example of a procedure:
CREATE OR REPLACE PROCEDURE GetNumberOfStudents ( courseId IN number, numberOfStudents OUT number ) IS /* There are better ways to compute the number of students, but this is a good opportunity to show a cursor in action */ cursor student_cur is select studentId, studentName from course where course.courseId = courseId; student_rec student_cur%ROWTYPE; BEGIN LOOP FETCH student_cur INTO student_rec; EXIT WHEN student_cur%NOTFOUND; numberOfStudents := numberOfStudents + 1; END LOOP; CLOSE student_cur; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Error encountered - '||SQLCODE||' - '||SQLERRM); END GetNumberOfStudents; /
Stored functions and procedures can be packaged into a larger unit, essentially a library of procedures and functions, called a package. Public declarations are put in the PACKAGE SPECIFICATON, or header, while the procedure or function code, or private code, is put in a PACKAGE BODY with the same name.
CREATE OR REPLACE PACKAGE student_pkg AS FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2) RETURN DATE; PROCEDURE GetNumberOfStudents( courseId IN number, numberOfStudents OUT number); END; /
CREATE OR REPLACE PACKAGE BODY student_pkg AS FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2) RETURN DATE IS BEGIN ... END; PROCEDURE GetNumberOfStudents( courseId IN number, numberOfStudents OUT number) IS BEGIN ... END; END; /
In addition to performing the function of being a common library of procedures and functions there are three aspects of Oracle packages that are unique. They are:
- The initialization section. A package's initialization section is executed once the first time any portion of a package is used within a session. This can be valuable for setting session variables.
- Packages, by default, retain the values of variables within a session after execution of a procedure or function, within the package, is completed. This behavior can be modified to be identical to that of procedures and functions by using PRAGMA SERIALLY_REUSABLE.
- Packages can be overloaded. A package can contain multiple procedures and functions with the same name provided the parameters passed in and returned are different.
Oracle provides some predefined exceptions that can be used, including: NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, VALUE_ERROR, ZERO_DIVIDE, INVALID_CURSOR, NOT_LOGGED_ON, etc.
Here is an example of how to define a custom expection:
DECLARE view_doesnot_exist EXCEPTION; PRAGMA EXCEPTION_INIT(view_doesnot_exist, -942); BEGIN ... EXCEPTION WHEN view_doesnot_exist THEN null; END;