Understanding PL/SQL Language Basics
What Is PL/SQL?
PL/SQL is a modern, block-structured programming language. It provides several features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional statements, that are not available in standard SQL.
PL/SQL code runs on the server, so using PL/SQL lets you centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.
What Are the Types PL/SQL Code Blocks?
There are 3 types of PL/SQL code blocks:
- Anonymous Block - A block of codes with no name. It may contain a declaration part, an execution part, and exception handlers.
- Stored Program Unit - A block of codes with a name. It is similar to an anonymous block. But it can take parameters and return values.
- Trigger - A block of code that can be defined to fire based an specific event.
How To Define an Anonymous Block?
An anonymous block must have an execution part, which is a group of other PL/SQL statements enclosed in the BEGIN ... END statement. Here is a script on how to define a simple anonymous block with SQL*Plus:
SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line('Hello world!');
3 end;
4 /
Hello world!
PL/SQL procedure successfully completed.
"set serveroutput on;" allows dbms_output.put_line() to work.
"/" runs the anonymous block, which print the "Hello world!" message.
How Many Anonymous Blocks Can Be Defined?
An anonymous block is stored in the user's current session without any name. So you can only define one anonymous block at any time. If you define another anonymous block, the new block will replace the previously defined block, as shown in the following script:
SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line('Hello world!');
3 end;
4 /
Hello world!
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_output.put_line('This is a PL/SQL FAQ.');
3 end;
4 /
This is a PL/SQL FAQ.
PL/SQL procedure successfully completed.
How To Run the Anonymous Block Again?
If you have an anonymous block defined in your session, you can run it any time by using the "/" command as shown in the following script:
SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line('This is a PL/SQL FAQ.');
3 end;
4 /
This is a PL/SQL FAQ.
PL/SQL procedure successfully completed.
SQL> /
This is a PL/SQL FAQ.
PL/SQL procedure successfully completed.
What Is Stored Program Unit?
A stored program unit is a named block of codes which:
- Has a name.
- Can take parameters, and can return values.
- Is stored in the data dictionary.
- Can be called by many users.
How To Create a Stored Program Unit?
If you want to create a stored program unit, you can use the CREATE PROCEDURE or FUNTION statement. The example script below creates a stored program unit:
SQL> set serveroutput on;
SQL> CREATE PROCEDURE Hello AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('Hello world!');
4 END;
5 /
Procedure created.
How To Execute a Stored Program Unit?
If you want to execute a stored program unit, you can use the EXECUTE statement. The example script below shows how to executes a stored program unit:
SQL> set serveroutput on;
SQL> CREATE PROCEDURE Hello AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('Hello world!');
4 END;
5 /
Procedure created.
SQL> EXECUTE Hello;
Hello world!
How Many Data Types Are Supported?
PL/SQL supports two groups of data types:
- SQL Data Types - All data types used for table columns.
- PL/SQL Special Data Types - Like BOOLEAN or PLS_INTEGER.
The script below shows some data type examples:
SQL> set serveroutput on;
SQL> DECLARE
2 title VARCHAR(8);
3 salary NUMBER;
4 seeking_job BOOLEAN;
5 BEGIN
6 title := 'DBA';
7 salary := 50000;
8 seeking_job := TRUE;
9 DBMS_OUTPUT.PUT_LINE('Job Title: ' || title);
10 DBMS_OUTPUT.PUT_LINE('Expected salary: '
11 || TO_CHAR(salary));
12 END;
13 /
Job Title: DBA
Expected salary: 50000
What Are the Execution Control Statements?
PL/SQL supports three groups of execution control statements:
- IF Statements - Conditionally executes a block of statements.
- CASE Statements - Selectively executes a block of statements.
- LOOP Statements - Repeatedly executes a block of statements.
- GOTO Statements - Unconditional changes the execution flow to a specified statement.
The script below shows execution control statements:
SQL> set serveroutput on;
SQL> DECLARE
2 total NUMBER;
3 BEGIN
4 total := 0;
5 LOOP
6 total := total+1;
7 IF total >= 10 THEN
8 GOTO print;
9 END IF;
10 END LOOP;
11 <>
12 DBMS_OUTPUT.PUT_LINE('Total counts: '
13 || TO_CHAR(total));
14 END;
15 /
Total counts: 10
This sample script shows you how to use LOOP, IF, and GOTO statements.
How To Use SQL Statements in PL/SQL?
SQL DML (Data Manipulation Language) statements can be included in PL/SQL code blocks directly without any changes. See the script below for examples:
SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
2 subject VARCHAR(80) NOT NULL,
3 description VARCHAR(256) NOT NULL);
Table created.
SQL> BEGIN
2 INSERT INTO tip VALUES(1, 'PL/SQL',
3 'Good for beginners.');
4 UPDATE tip SET description = 'Good for beginners.';
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> COL subject FORMAT A12;
SQL> COL description FORMAT A24;
SQL> SELECT * FROM tip;
ID SUBJECT DESCRIPTION
---------- ------------ -------------------
1 PL/SQL Good for beginners.
SQL> DROP TABLE tip;
Table dropped.
This script example actually has 3 parts:
- Part 1 - A single DDL (Data Definition Language) statement, CREATE TABLE, outside the PL/SQL code block.
- Part 2 - A PL/SQL code block defined and executed to insert and updated a record.
- Part 3 - A couple of SQL statements outside the PL/SQL code block to view the record and trop the table.
How To Process Query Result in PL/SQL?
You can run queries (SELECT statements) in a PL/SQL code blocks, and process the results a loop as shown in the following script example:
SQL> set serveroutput on;
SQL> BEGIN
2 FOR row IN
3 (SELECT * FROM employees WHERE manager_id = 101)
4 LOOP
5 DBMS_OUTPUT.PUT_LINE('Name = ' || row.last_name);
6 END LOOP;
7 END;
8 /
Name = Greenberg
Name = Whalen
Name = Mavris
Name = Baer
Name = Higgins
The loop statement in this script automatically sets a temporary variable "row" each row in the result set returned from the SELECT statement.
How To Create an Array in PL/SQL?
If you want create an array data structure, you can use the collection type VARRAY. VARRAY stands for Variable Array. Here is a sample script on how to use VARRAY:
SQL> set serveroutput on;
SQL> DECLARE
2 TYPE list IS VARRAY(3) OF VARCHAR(16);
3 domain_list list;
4 BEGIN
5 domain_list := list('google','fyicenter','myspace');
6 DBMS_OUTPUT.PUT_LINE('Second domain: '
7 || domain_list(2));
8 END;
9 /
Second domain: fyicenter
As you can see, to define an array variable, you need to create your own array type, and use that type to define array variables.
How To Manage Transaction Isolation Level?
Transaction isolation level can be managed in a procedure by using the SET TRANSACTION and COMMIT statements. Here is a sample script on how to manage transaction isolation level:
SQL> CREATE OR REPLACE PROCEDURE HR.TOTAL_SALARY AS
2 total_salary NUMBER(12,2);
3 BEGIN
4 SET TRANSACTION READ ONLY;
5 SELECT SUM (salary) INTO total_salary FROM employees;
6 DBMS_OUTPUT.PUT_LINE('Total salary 1: '
7 || total_salary);
8 -- Other sessions may change salaries of some records
9 SELECT SUM (salary) INTO total_salary FROM employees;
10 DBMS_OUTPUT.PUT_LINE('Total salary 2: '
11 || total_salary);
12 COMMIT;
13 END;
14 /
SQL> EXECUTE TOTAL_SALARY;
Total salary 1: 691400
Total salary 2: 691400
"READ ONLY" transaction level takes a read only snapshot of the database. This allows other sessions to update the database without any locks. All queries in the session will produces identical results. So both SELECT statements in this script will return the same value guaranteed.
How To Pass Parameters to Procedures?
Store procedures or functions can take parameters. You need to define parameters while defining the procedure, and providing values to parameters while calling the procedure. The script below shows you how to do this:
SQL> CREATE OR REPLACE PROCEDURE DBA_TASK (day VARCHAR2) AS
2 BEGIN
3 IF day = 'MONDAY' THEN
4 DBMS_OUTPUT.PUT_LINE('Checking log files.');
5 ELSIF day = 'FRIDAY' THEN
6 DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('Reading some papers.');
9 END IF;
10 END;
11 /
SQL> EXECUTE DBA_TASK('MONDAY');
Checking log files.
SQL> EXECUTE DBA_TASK('SUNDAY');
Reading some papers.
As you can see, procedures with parameters can make procedures more flexible.
How To Define a Procedure inside Another Procedure?
Define a procedure inside another procedure is supported by PL/SQL. The following tutorial script shows you an example:
SQL> CREATE OR REPLACE PROCEDURE HR.DBA_WEEK AS
2 PROCEDURE DBA_TASK (day VARCHAR2) AS
3 BEGIN
4 IF day = 'MONDAY' THEN
5 DBMS_OUTPUT.PUT_LINE('Checking log files.');
6 ELSIF day = 'FRIDAY' THEN
7 DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
8 ELSE
9 DBMS_OUTPUT.PUT_LINE('Reading some papers.');
10 END IF;
11 END;
12 BEGIN
13 DBA_TASK('MONDAY');
14 DBA_TASK('TUESDAY');
15 END;
16 /
SQL> EXECUTE DBA_WEEK;
Checking log files.
Reading some papers.
Remember that procedures used inside a procedure must be defined in the declaration block.
What Do You Think about PL/SQL?
After following through the tutorials in the FAQ collection, you probably agree that PL/SQL is indeed a general purpose database programming language. PL/SQL is a natural extension of SQL. It is very useful for DBA to automate specific administration tasks or for developers to developer simple business backend
What Is PL/SQL Language Case Sensitive?
PL/SQL language is not case sensitive:
- Reserved words are not case sensitive. For example: CASE and Case are identical.
- Variable names and other names are not case sensitive. For example: TOTAL_SALARY and total_salary are identical.
But values in string literals are case sensitive. For example: 'DBA' and 'dba' are different.
How To Place Comments in PL/SQL?
There are two ways to place comments into PL/SQL codes:
- SQL Statement Style: Starts you comment any where in the line but prefixed with '--'. The comment ends at the end of the line.
- C Language Style: Starts you comment any where in the line with '/*' and ends it with '*/' in the same line or some lines below.
Here is some example of PL/SQL comments:
BEGIN
-- This is a comment
/* To do:
Need to write some codes here
*/
END;
What Are the Types PL/SQL Code Blocks?
There are 3 types of PL/SQL code blocks:
- Anonymous Block - A block of codes with no name. It may contain a declaration part, an execution part, and exception handlers.
- Stored Program Unit - A block of codes with a name. It is similar to an anonymous block. But it can take parameters and return values.
- Trigger - A block of code that can be defined to fire based an specific event.
What Is an Anonymous Block?
An anonymous block is a PL/SQL code block with no name. It consists of three parts:
- Declaration Part - Defining local variables and local procedures. Declaration part is optional.
- Execution Part - Defining execution logic with executable statements. Execution part is required.
- Exception Part - Defining error handling logics. Exception part is optional.
Here how a complete anonymous block should look like:
DECLARE
-- Declaration statements
BEGIN
-- Executable statements
EXCEPTION
-- Error handling statements
END;
What Is a Named Program Unit?
A named program unit is a PL/SQL code block with an name. It consists of three parts:
- Declaration Part - Defining the program unit name, calling parameters, local variables and local procedures. Declaration part is required.
- Execution Part - Defining execution logic with executable statements. Execution part is required.
- Exception Part - Defining error handling logics. Exception part is optional.
There are two types of named program units:
- Procedure - Has no return values.
- Function - Has a return value.
What Is a Procedure?
A procedure is a named program unit. It consists of three parts:
- Declaration Part - Defining the procedure name, calling parameters, local variables and local procedures. Declaration part is required.
- Execution Part - Defining execution logic with executable statements. Execution part is required.
- Exception Part - Defining error handling logics. Exception part is optional.
Here how a complete procedure should look like:
PROCEDURE name (parameter_1, parameter_2) AS
-- Declaration statements
BEGIN
-- Executable statements
EXCEPTION
-- Error handling statements
END;
What Is a Function?
A function is a named program unit. It consists of three parts:
- Declaration Part - Defining the function name, calling parameters, return value type, local variables and local procedures. Declaration part is required.
- Execution Part - Defining execution logic with executable statements. Execution part is required.
- Exception Part - Defining error handling logics. Exception part is optional.
Here how a complete procedure should look like:
FUNCTION name (parameter_1, parameter_2) RETURN type AS
-- Declaration statements
BEGIN
-- Executable statements
RETURN value;
EXCEPTION
-- Error handling statements
END;
How To Declare a Local Variable?
A local variable can be defined in the declaration part with a declaration statement, which is a variable name followed a data type identifier. Below are some examples of declaration statements:
PROCEDURE proc_var_1 AS
domain VARCHAR2(80);
price REAL;
is_for_sale CHAR;
BEGIN
-- Executable statements
END;
How To Initialize Variables with Default Values?
There are two ways to assign default values to variables at the time of declaration:
- Using key word DEFAULT - Appending "DEFAULT value" to the end of declaration statements.
- Using assignment operator - Appending ":= value" to the end of declaration statements.
The script below show you some examples of declaration statements with default values:
PROCEDURE proc_var_1 AS
domain VARCHAR2(80) := 'fyicenter.com';
price REAL DEFAULT 999999.99;
is_for_sale CHAR := 'N';
BEGIN
-- Executable statements
END;
How To Assign Values to Variables?
You can use assignment statements to assign values to variables. An assignment statement contains an assignment operator ":=", which takes the value specified on the right to the variable on left. The script below show you some examples of assignment statements:
PROCEDURE proc_var_2 AS
is_done BOOLEAN;
counter NUMBER := 0;
message VARCHAR2(80);
BEGIN
is_done := FASLE;
counter := counter + 1;
message := 'Hello world!';
END;
What Are the Arithmetic Operations?
There are 4 basic arithmetic operations on numeric values as shown in the following sample script:
PROCEDURE proc_arithmetic AS
addition NUMBER;
subtraction NUMBER;
multiplication NUMBER;
division NUMBER;
BEGIN
addition := 7 + 8;
subtraction := addition - 7;
multiplication := subtraction * 5;
division := multiplication / 8;
-- division should contain 5 now
END;
What Are the Numeric Comparison Operations?
PL/SQL supports 6 basic numeric comparison operations as shown in the following sample script:
PROCEDURE proc_comparison AS
res BOOLEAN;
BEGIN
res := 1 = 2;
res := 1 < 2;
res := 1 > 2;
res := 1 <= 2;
res := 1 >= 2;
res := 1 <> 2;
-- more statements
END;
What Are the Logical Operations?
PL/SQL supports 3 logical operations as shown in the following sample script:
PROCEDURE proc_comparison AS
x BOOLEAN := TRUE;
y BOOLEAN := FALSE;
res BOOLEAN;
BEGIN
res = x AND y;
res = x OR y;
res = NOT x;
-- more statements
END;
How Many Categories of Data Types?
PL/SQL data types are grouped into 4 categories:
- Scalar Data Types: A scalar data type holds a single value.
- Composite Data Types: A composite data type has internal components, such as the elements of an array.
- LOB Data Types: A LOB data type holds a lob locator that specify the location of a large object.
- Reference Data Types: A reference data type holds a pointer that points to another data object.
How Many Scalar Data Types Are Supported in PL/SQL?
PL/SQL supports many scalar data types divided into 4 groups:
- Numeric Types: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT.
- Character Types: CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, STRING, UROWID, VARCHAR, VARCHAR2.
- Boolean Types: BOOLEAN.
- Date Types: DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND.
How To Convert Character Types to Numeric Types?
You can convert character types to numeric types in two ways:
- Explicitly by using TO_NUMBER() function.
- Implicitly by putting character data in a numeric operation.
The sample script below shows you how to convert character types to numeric types:
PROCEDURE proc_convert_1 AS
start_time CHAR(5);
finish_time CHAR(5);
elapsed_time NUMBER(5);
BEGIN
start_time := '12052';
finish_time := '15314';
elapsed_time := TO_NUMBER(finish_time)
- TO_NUMBER(start_time);
elapsed_time := finish_time - start_time; -- same as above
END;
What Are the Execution Control Statements?
PL/SQL supports three groups of execution control statements:
- IF Statements - Conditionally executes a block of statements.
- CASE Statements - Selectively executes a block of statements.
- LOOP Statements - Repeatedly executes a block of statements.
- GOTO Statements - Unconditional changes the execution flow to a specified statement.
The script below shows some execution control statements:
DECLARE
total NUMBER;
BEGIN
total := 0;
LOOP
total := total+1;
IF total >= 10 THEN
GOTO print;
END IF;
END LOOP;
<>
DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));
END;
This script should print this:
Total counts: 10
`
How To Use "IF" Statements on Multiple Conditions?
If you have multiple blocks of codes to be executed based on different conditions, you can use the "IF ... ELSIF" statement. Here is a sample script on IF statements:
DECLARE
day VARCHAR2;
BEGIN
day := 'SUNDAY';
IF day = 'THURSDAY' THEN
DBMS_OUTPUT.PUT_LINE('Checking log files.');
ELSIF day = 'TUESDAY' THEN
DBMS_OUTPUT.PUT_LINE('Helping developers.');
ELSIF day = 'FRIDAY' THEN
DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
ELSE
DBMS_OUTPUT.PUT_LINE('Reading some papers.');
END IF;
END;
This script should print this:
Reading some papers.
How To Use "WHILE" Statements?
If you have a block of codes to be executed repeatedly based a condition, you can use the "WHILE ... LOOP" statement. Here is a sample script on WHILE statements:
DECLARE
total NUMBER;
BEGIN
total := 0;
WHILE total < 10 LOOP
total := total+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));
END;
This script should print this:
Total counts: 10
How To Use "FOR" Statements?
If you have a block of codes to be executed repeatedly over a range of values, you can use the "FOR ... LOOP" statement. Here is a sample script on FOR statements:
DECLARE
total NUMBER := 0;
BEGIN
FOR i IN 1..10 LOOP
total := total + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total: ' || TO_CHAR(total));
END;
Note that temporary variable "i" used in the FOR loop needs no declaration. This script should print this:
Total: 55
What Is NULL in PL/SQL?
NULL is a reserved key word and it stands for two things in PL/SQL:
- NULL is an executable statement, and means doing nothing.
- NULL is a data balue, and means no value.
The following sample script shows you examples of using NULL keyword:
DECLARE
next_task CHAR(80);
BEGIN
next_task := NULL; -- NULL value
IF next_task IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('I am busy.');
ELSE
DBMS_OUTPUT.PUT_LINE('I am free.');
END IF;
IF next_task IS NULL THEN
NULL; -- NULL statement
ELSE
DBMS_OUTPUT.PUT_LINE('... working on ' || next_task);
END IF;
END;
This script should print this:
I am free.
How To Test NULL Values?
There ate two special comparison operators you can use on NULL values:
- "variable IS NULL" - Returns TRUE if the variable value is NULL.
- "variable IS NOT NULL" - Return TRUE if the variable value is not NULL.
The following sample script shows you examples of comparing NULL values:
DECLARE
next_task CHAR(80);
BEGIN
next_task := NULL;
IF next_task IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('I am busy.');
ELSE
DBMS_OUTPUT.PUT_LINE('I am free.');
END IF;
IF next_task IS NULL THEN
NULL;
ELSE
DBMS_OUTPUT.PUT_LINE('... working on ' || next_task);
END IF;
END;
Note that "variable = NULL" is not a valid operation. This script should print this:
I am free.
========================================================================
