PL/SQL

Prerequisites

• Has decent SQL and Java background

• Has understood the basic functionalities of Oracle SQL Developer

PL/SQL is a procedural programming language developed by Oracle, used along with SQL. Its paradigm is based on procedures instead of statements (which are inside its blocks of a procedure). PL stands for Procedural Language, and SQL stands for Structured Query Language. 

Every procedure has four parts of it:

• DECLARE

• BEGIN

• EXCEPTION

• END

DECLARE – This is where the declaration of variables is placed. 

BEGIN – This is where actual execution of SQL statements happen. 

EXCEPTION – This is where handling of errors occurs, and it prevents abrupt interruption of a program caused by the errors. 

END – It is used to terminate the procedure. 

PL/SQL procedures can be ONLY executed inside Oracle SQL Developer. You MUST connect to a connection that you’ve previously created in the IDE, before taking any actions, such as executing a procedure. 

Before you execute a procedure, you MUST execute this line of command to allow text printing to the system output: 

SET SERVEROUTPUT ON 

"Hello world" in PL/SQL: 

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World'); 

END; 

BEGIN keyword introduces the underlying procedure and executes its contents which contain queries or statements, and END keyword terminates the procedure after execution. Note that the semicolon at the END keyword is MANDATORY. Otherwise, the procedure wouldn’t execute or may cause error.

DBMS_OUTPUT.PUT_LINE() function is a built-in function that is equivalent to Java’s System.out.println(), which displays something (such as string, number, etc.) in a system output (in this instance, the Script Output in Oracle SQL Developer). 

Take another example of a lengthy procedure in PL/SQL: 

DECLARE 

emp_id NUMBER := 11; 

emp_salary NUMBER; 

BEGIN 

SELECT salary INTO emp_salary 

FROM employees 

WHERE employee_id = emp_id; 

DBMS_OUTPUT.PUT_LINE(CONCAT('Their salary is ', emp_salary)); 

EXCEPTION 

WHEN NO_DATA_FOUND THEN

  DBMS_OUTPUT.PUT_LINE('No data found'); 

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE('An unexpected error occurred'); 

END;

This procedure has declaration of variables and error handling, allowing to handle errors and store essential pieces of data that can be referenced by the statements from the BEGIN block. 

Declaration Syntax

Any procedure can have or not have variables, so it is not necessary to provide any variables to a procedure. Declaration statements are placed inside the DECLARE block. A declaration statement has this following format:

variable_name data_type := value;

or

variable_name data_type;

Where variable_name is an identifier name for a variable, data_type is any of the allowed SQL data types, and value is any data or value depending on the data type that is explicitly shown.

Examples:

emp_id NUMBER := 21;

emp_salary NUMBER;

At the first example, the variable is declared as a number or has a data type of NUMBER, and it is initialized with the value of 21 using the assignment operator (:=). At the last example, the variable has the same data type as the first example’s variable, and it is not initialized, so there is no value or null. You can declare many variables as many as you want, but make sure to use them all; because every single declaration statement allocates a reasonable amount of memory depending on the capacity of the data type and the value of its variable. 

Behind the BEGIN Block

Since PL/SQL is a procedural language, data retrieval is not the same as from SQL, which displays data by tabulation (displayed in the Query Result view) or sometimes displayed in the Script Output view. Data tabulation is powered by the IDE (Oracle SQL Developer) for ease of data organization. 

The SELECT query has its new keyword, INTO. This keyword is used to assign the result from the SELECT query to a variable declared or existing from the DECLARE block.

In the example at the very above, inside the BEGIN block, it retrieves the salary from the employees table where it satisfies the condition that it has an ID of 11, which is the value from the emp_id variable, and assigns the result to emp_salary variable. After retrieval of the result, it prints the result to the system output by calling the DBMS_OUTPUT.PUT_LINE() function, and it also combines the result with the string "Their salary is " using the CONCAT function. 

Error Handling Syntax

If an error or more occurs from some statements, the program exits immediately and reports these errors at the system output. Luckily, these errors can be handled with care by using the EXCEPTION block. According to our teacher, they discussed the two commonly used exceptions:

• NO_DATA_FOUND

• OTHERS

NO_DATA_FOUND is handled when the SELECT query retrieved no data. OTHERS is handled if an error isn’t caught by other exception handlers, it is often placed at last among the exception handlers. 

An exception handling statement has this format: 

WHEN exception_name THEN
statements...

Where exception_name can be any of these two exceptions above, and statements are only regular PL/SQL or SQL statements, it can have at least one of it. At the example mentioned previously, it has both exceptions NO_DATA_FOUND and OTHERS. Its flow of handling is simple; if NO_DATA_FOUND is caught, then it displays “No data found” by using a function to print something.  And for any other exceptions, it is caught from the exception handler that handles the OTHERS exception and displays "An unexpected error occurred."

Finalizing the Procedure with END

Not putting an END block is like if you’re stuck in an unescapable loop. In conclusion, it is mainly important to terminate the procedure with END strictly including the semicolon; otherwise, the procedure wouldn’t execute as expected. 

Mentioned IDE Views