Introduction

BASIC PROGRAM

This is the most basic Hello World program in PL/SQL. Hello World will be displayed on the screen.

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World number ' || TO_CHAR(1));
END;
/

To show some value that is not a character we have to convert it with TO_CHAR function.
String concatenation is done with ||

We could also add now DECLARATION part. Every variable that is used in a PL/SQL program must be declared first.

DECLARE
phrase VARCHAR2(10) := 'Vladimir';
BEGIN
/* this is a comment. || is for string concatenation */
DBMS_OUTPUT.PUT_LINE('HELLO ' || phrase);
END;
/

DATATYPES
NUMBER(number of characters, number of characters after the decimal) – numeric data
CHAR – fixed size characters
VARCHAR2(n) – variable size characters
DATE – date values (format DD-MON-YY)
BOOLEAN – TRUE, FALSE or NULL

DATE, CHAR and VARCHAR2 should be enclosed in ‘single quotes’

CONSTANT
Constant value is a value that will not change in the program. You just need to add CONSTANT keyword when declaring a value.

variable_name [CONSTANT] datatype [NOT NULL] [{ := | DEFAULT } initial_value];

DECLARE
tax_percent CONSTANT := 12;
BEGIN
tax_percent := 20; /* THIS IS AN ERROR */
END;
/

NOT NULL
One can also declare that a value can not take value NULL with NOT NULL keyword and if it does get value of NULL there will be an error

birthday DATE NOT NULL := '10-FEB-99';

SELECT INTO
Storing a value in a variable so that we can work with it later using SELECT statement.

DECLARE
salary NUMBER(10,2);
department_name VARCHAR2(30);
BEGIN

SELECT salary_column, department_name_column INTO salary, department
FROM employee_table
WHERE some_column=’some value’;

DBMS_OUTPUT.PUT_LINE(‘The salary for some value in department ‘ || department_name || ‘ is: ‘ || TO_CHAR(salary));

END;
/

Block Structure

Above we have seen anonymous block with DECLARE part. Blocks can also be named.
Those include functions, procedures, packages and triggers. Their structure looks like the following:

Block Header
IS
Declaration Section
BEGIN
Execution Section
EXCEPTION
Exception Section
END;

Stored Procedures and Functions

Procedure – a program that executes one or more statements
Function – a program that returns a value
Trigger – a program that executes in response to database changes
Package – a container for procedures, functions and data structures