Lucian Lazar

Using PL/SQL Subprograms

Lucian Lazar's photo
Lucian Lazar
·

4 min read

I released my first video course for Pluralsight: Using PL/SQL Subprograms.

The course is included in the path for the Oracle certification for PL/SQL programming and it will teach you how to create and use subprograms.

The first module is free, for the rest of the modules a subscription is needed. Anyone interested in a one month free subscription at Pluralsight please contact me privately and I will share a code.

Main Topics

The main topics covered by this course are:

  • What are anonymous blocks of code and how to use them for development and testing;

  • What are subprograms, what are their advantages over anonymous blocks and how to use the two main types of subprograms: procedures and functions;

  • How to extend the PL/SQL language using subprograms with new statements that will perform the exact operations you need;

  • How to integrate procedures and functions in applications and write modular, reusable, and maintainable code.

I will present in the following sections the main topics in the course and a selection of examples and code for some topics.

Anonymous Blocks

In PL/SQL the code is not executed in single line format, but it is executed by grouping the code statements in a single element called block. All the statements are executed as a whole and not as a single instruction at a time. Because of this, a block is the most basic program unit in the PL/SQL programming language.

Anonymous blocks are blocks that: do not have a name, are not saved in the database, have one-time usage and cannot be reused. Anonymous blocks are not used in applications, but they are used in development for creating test data and for testing purposes by calling named blocks inside of them.

image.png

Let’s see the simplest possible PL/SQL anonymous block, the one that prints the message “Hello, world!”:

begin
  dbms_output.put_line('Hello, world!');
end;

Subprograms

A subprogram is a block that: has a name, can take parameters, and return values, is saved in the database and can be called anytime by users from the Oracle database or from other applications.

image.png

There are two types of subprograms: procedures and functions. Generally, we use a procedure to perform a sequence of actions and we use a function to compute a value.

Functions

A function is a named PL/SQL block that can accept parameters and must return a value.

image.png

Let’s see an example of function that identifies the senior employees in a certain department in order to increase their salaries. First we see how the function is created, then how it is called to return the most senior employee from the IT department:

create or replace function get_senior_employee(
  seniority_degree in number, 
  department in varchar2)
return number as
  v_employee_id number;
begin
  select employee_id into v_employee_id
    from employees
   where department_name = department 
     and seniority = seniority_degree;
  return v_employee_id;
exception
  when no_data_found then
    dbms_output.put_line('There is no employee in the department matching the seniority');
end;

declare
  v_employee_id number;
begin
  v_employee_id := get_senior_employee(1, 'IT');
end;

Procedures

A procedure is a named PL/SQL block that can accept parameters, performs a sequence of actions, and optionally returns values.

image.png

Let’s see a procedure that increases the salary of a certain employee with a certain percent. First we see how the procedure is created, then how it is called to update the salary of an employee with 20%:

create or replace procedure increase_salary(
  employee in number,
  percent in number) as
begin
  update employees 
     set salary = salary * (1 + percent) 
   where employee_id = employee;
end;

begin
  increase_salary(105, 0.2);
end;

Benefits of Subprograms

The five benefits of using subprograms in PL/SQL are: extensibility, modularity, reusability, maintainability, and abstraction.

Reusability means that once created and tested, a subprogram can be reused in any number of applications or programming languages without needing to rewrite it.

image.png

Final Thoughts

Keep in mind that I covered in this article just the main topics in the course and a selection of examples and code for some topics. For understanding all the concepts please watch the entire video course: Using PL/SQL Subprograms. If you do not have a subscription, please contact me privately and I will share a code for a one month free subscription at Pluralsight.

By the end of the Using PL/SQL Subprograms course, you will know how to create anonymous blocks of code and use them in development, and how to write procedures and functions and integrate them efficiently in applications. And you will be prepared to complete the subprograms section of the 1Z0-149 Oracle certification exam.

Check here a list of all the video courses created by me for Pluralsight.

OraclePL/SQLfunctionssubprogramsprocedures