PL / SQL language
PL / SQL is a procedural language specific to Oracle which constitutes an extension to the SQL language (non-procedural language).
SQL commands can be embedded within PL / SQL code. These commands are called Embedded SQL Commands.
PL / SQL code can be used in program units stored in the database such as procedures, functions, and packages (groups of procedures and functions). These program units are said to be stored.
If these procedures are not stored in the database but incorporated into an application (development tools such as Oracle FORMS DEVELOPER and Oracle REPORT DEVELOPER), they are called application procedures.
PL / SQL code embedded in an application program unit or stored but not named is called an anonymous block. An anonymous block which does not have a name, therefore, cannot be stored in the database.
The advantage of a stored unit of programs is that it can be called by name from your application code and Oracle stores the result of the parse analysis of the unit in the database (persistent storage on disk). Considerable performance gains are generated as the parse output is made available and persistent for future reuse of unit code.
Example of PL / SQL code:
DECLARED
numerator NUMBER;
denominator NUMBER;
the_ratio NUMBER;
lower_limit CONSTANT NUMBER: = 0.72;
samp_num CONSTANT NUMBER: = 132;
BEGIN
SELECT x, y INTO numerator, denominator FROM result_table
WHERE sample_id = samp_num;
the_ratio: = numerator / denominator;
IF the_ratio> lower_limit THEN
INSERT INTO ratio VALUES (samp_num, the_ratio);
ELSE
INSERT INTO ratio VALUES (samp_num, -1);
END IF;
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (samp_num, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
END;
Explanations:
This PL / SQL code declares several variables and constants, then calculates a ratio value (the_ratio) for values (numerator, denominator) selected from a table (result_table).
Very few times, Oracle DBA needs to address about PL/SQL because there are developers available for every company to utilize this PL/SQL for making forms and reports. Understanding PL/SQL is essential for Oracle database administrators. Stay connected with our blog for getting more advance knowledge of Oracle DBA tips and Tutorials.
