PL/SQL (Procedural Language/Structured Query Language) is Oracle
Corporation's procedural extension language for SQL and the Oracle
relational database.
PL/SQL is a blocked programming language. Program units can be
named or unnamed blocks. Unnamed blocks are known as anonymous
blocks because it’s not going to be saved in the database, so it will
never have a name. We typically use anonymous blocks when building
scripts to seed data or perform one-time processing activities.
They are also effective when we want to nest activity in another
PL/SQL block’s execution section.
If you want to watch video lesson, visit PL/SQL introduction
BEGIN
DBMS_OUTPUT.put_line ('Hello world!');
END;
Basic anonymous block.
The BEGIN reserved word starts the exception block, and END word ends it.
The dbms_output.put_line procedure allows us to write data to flat file or to direct our PL/SQL output to a screen.
The basic anonymous-block structure must contain an execution section.
We can also put optional declaration section.
DECLARE
var1 INTEGER;
-- here we can declare variables
BEGIN
var1 := 5;
DBMS_OUTPUT.put_line ('Hello world!');
/*
this is
an executable section
*/
END;
Single-line comments start with a double-dash ( --). Multiline comments
start with a slash and asterisk ( /*) and end with an asterisk and slash ( */).
The declaration block lets us define datatypes, structures, and variables.
Defining a variable means that we give it a name, a datatype and, optionaly,
a value.
Variable names begin with letters and can contain alphabetical characters,
ordinal numbers (0 to 9), the $, _, and # symbols. Variables have local scope only.
The execution block lets us process data. The execution block can contain
variable assignments, comparisons, conditional operations, and iterations.
Also, the execution block is where we access other named program units
(e.g. functions, procedures). We can also nest anonymous-block programs
inside the execution block.
Assignment statement sets the current value of a variable. The assignment
operator in PL/SQL is a colon plus an equal sign (:=)
DECLARE
var1 varchar2(6) := 'world!';
BEGIN
DBMS_OUTPUT.put_line ('Hello ' || var1);
END;
String literals are delimited by single quotes.
Concatenation operator (||) allows us to concatenate two or more
strings together.
Basic datatypes:
VARCHAR2(size) - variable-length character string having maximum length size bytes. Maximum size is 4000, and minimum is 1. You must specify size for VARCHAR2.
NVARCHAR2(size) - variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
NUMBER(p,s) - number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
LONG - character data of variable length up to 2 gigabytes, or 231 -1 bytes.
DATE - valid date range from January 1, 4712 BC to December 31, 9999 AD.
RAW(size) - raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
ROWID - hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
CHAR(size) - fixed-length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.
NCHAR(size) - fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.
CLOB - a character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the CHAR database character set. Maximum size is 4 gigabytes.Â
BLOB - A binary large object. Maximum size is 4 gigabytes.Â
BFILE- contains a locator to a large binary file stored outside
the database. Enables byte stream I/O access to external LOBs residing
on the database server. Maximum size is 4 gigabytes.
There are many sub-types, which are derived from a type and usually
add a constraint to a type. For example, an INTEGER is a sub-type of
NUMBER and only whole numbers are allowed. If we want to convert
values to different data types, we should use conversion functions:
to_char( value, [ format_mask ] ) - converts a number or date
to a string. VALUE can either be a number or date that will be converted
to a string, FORMAT_MASK is optional, this is the format that will be
used to convert value to a string.
to_number( string1, [ format_mask ]) - converts a string to
a number, string1 is the string that will be converted to a number.
format_mask is optional. This is the format that will be used to
convert string1 to a number.
to_date( string1, [ format_mask ] ) - converts a string to a date.
string1 is the string that will be converted to a date. format_mask is optional.
This is the format that will be used to convert string1 to a date.
DECLARE
num_var NUMBER (4, 2) := 11.25;
int_var INTEGER := 5;
date_var DATE := TO_DATE ('11/03/2012', 'dd/mm/yyyy');
string_var VARCHAR2 (50) := 'string 1';
string_no_var VARCHAR2 (50) := '5.30';
char_var CHAR (50) := 'string 2';
BEGIN
DBMS_OUTPUT.put_line ('num_var value: ' || num_var);
DBMS_OUTPUT.put_line ('int_var value: ' || int_var);
DBMS_OUTPUT.put_line ('date_var value: ' || date_var);
DBMS_OUTPUT.put_line ('string_var value: ' || string_var);
DBMS_OUTPUT.put_line ('char_var value: ' || char_var);
DBMS_OUTPUT.put_line ('We can convert numeric value to a string: '
|| TO_CHAR (num_var));
DBMS_OUTPUT.put_line ('... or string value to a number: ' || TO_NUMBER (string_no_var, '9.99'));
END;
Example of use data types and converting functions.
Sometimes we need to have a special mechanism with which a variable
should find the data type automatically at runtime. To achieve that,
we can use the %TYPE or %ROWTYPE attribute, which lets use
the datatype of a table field.
DECLARE
emp_name employees.NAME%TYPE;
emp_surname employees.surname%TYPE;
BEGIN
SELECT NAME, surname
INTO emp_name, emp_surname
FROM employees
WHERE employeeid = 5;
DBMS_OUTPUT.put_line (emp_name || ' ' || emp_surname);
END;
"emp_name" is declared as being of type "employees.name%type."
It means that the data type of "emp_name" would be the same as
the data type of the "name" column in the "employees" table.
Similarly, the other variable, "emp_surname," is also declared.
The SELECT INTO statement retrieves data from one or more
database tables, and assigns the selected values to variables
or collections.
DECLARE
employee_rec employees%ROWTYPE;
BEGIN
SELECT *
INTO employee_rec
FROM employees
WHERE employeeid = 5;
DBMS_OUTPUT.put_line ('name: ' || employee_rec.NAME);
DBMS_OUTPUT.put_line ('surname: ' || employee_rec.surname);
DBMS_OUTPUT.put_line ('boss id: ' || employee_rec.bossid);
END;
The %ROWTYPE attribute provides a record type that represents
a row in a database table. The record can store an entire row of data
selected from the table or fetched from a cursor or cursor variable.
Variables declared using %ROWTYPE are treated like those declared
using a datatype name. You can use the %ROWTYPE attribute in
variable declarations as a datatype specifier.
Conditional structures
DECLARE
var1 INTEGER := 5;
BEGIN
IF var1 = 5
THEN
DBMS_OUTPUT.put_line ('var1 value is equal to 5');
ELSE
DBMS_OUTPUT.put_line ('var1 value is not equal to 5');
END IF;
END;
An explicit assignment declares a variable with a not-null value.
We can use the default value or assign a new value in the execution block.
A declaration of a number variable without an explicit assignment makes
its initial value null.
The IF-THEN-ELSE statement checks a Boolean value or expression
and if true, executes the statements in the THEN clause.
If the condition is false, the statements in the THEN clause are skipped
and ELSE statement is executed.
There are three different syntaxes for this statement.
IF condition THEN
{...statements...}
END IF;
or
IF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
or
IF condition THEN
{...statements...}
ELSIF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
DECLARE
var1 INTEGER := 5;
BEGIN
case
when var1 < 5 then DBMS_OUTPUT.put_line ('var1 value is less than 5');
when var1 = 5 then DBMS_OUTPUT.put_line ('var1 value is equal to 5');
when var1 > 5 then DBMS_OUTPUT.put_line ('var1 value is greater than 5');
else DBMS_OUTPUT.put_line ('var1 value is unknown');
END CASE;
END;
We can use the case statement within an SQL statement. It has the functionality
of an IF-THEN-ELSE statement. The syntax for the case statement is:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
WHEN condition_n THEN result_n
ELSE result
END CASE
expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.
result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.
Iterative structures
Basic loop
DECLARE
i INTEGER := 0;
BEGIN
LOOP
i := i + 1;
DBMS_OUTPUT.put_line ('The index value is ' || i);
EXIT WHEN i >= 10;
END LOOP;
END;
The program prints index values from 1 to 10.
Simple loops are explicit structures. They require that you manage both
loop index and exit criteria. The simple loops have a variety of uses.
Typically, this loops are used in conjunction with locally defined cursor
statements and reference cursors.
WHILE loop
WHILE condition
LOOP
{.statements.}
END LOOP;
The WHILE loop, also called a conditional loop, evaluates a condition
before each loop executes, and if false, the loop is terminated.
If the expression is false when the program reaches the WHILE loop,
the loop code is jumped and never executed. Use a WHILE loop when
the condition test is required at the start of the loop or if you are not sure
how many times you will execute the loop body. Since the WHILE
condition is evaluated before entering the loop, it is possible that the loop
body may not execute even once.
DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 10
LOOP
DBMS_OUTPUT.put_line ('The index value is ' || i);
i := i + 1;
END LOOP;
END;
FOR loop
PL/SQL supports numeric and cursor FOR loops. The numeric FOR loop
iterates across a defined range, while the cursor FOR loop iterates across
rows returned by a SELECT statement. FOR loops manage how they begin
and end implicitly. We can override the implicit END LOOP phrase by using
an explicit CONTINUE or EXIT statement to respectively skip an iteration
or force a premature exit from the loop.
FOR i IN starting_number..ending_numberÂ
LOOP
statement;
END LOOP;
The starting_number and ending_number must be integers.
The loop index is the i variable, and the loop index scope is limited
to the FOR loop. The index variable is a PLS_INTEGER datatype number.
FOR i IN (select_statement)
LOOP
statement;
END LOOP;
BEGIN
FOR i IN 1 .. 10
LOOP
DBMS_OUTPUT.put_line ('The index value is ' || i);
END LOOP;
END;
The program prints index values from 1 to 10.
BEGIN
FOR i IN (SELECT surname
FROM employees
WHERE depid = 2)
LOOP
DBMS_OUTPUT.put_line ('Department 1: ' || i.surname);
END LOOP;
END;
The program prints employees from department 2.
The index variable is not a PLS_INTEGER number in a cursor
FOR loop. It is a reference to the record structure returned by
the select statement. We combine the index variable and column
name with a dot, also known as the component selector.
The component selector lets us select a column from the row
returned by the select statement.
More Oracle PL/SQL lessons, visit Oracle tutorial
|
