PL/SQL Tutorial: Chapter 7

In this section, you’ll learn:

• What nested blocks are and how to create them
• What packages are, why you should use them, and how to create them

What is a Nested Block in PL/SQL?

A nested block is where a block of PL/SQL code is nested inside another block. A PL/SQL block is a set of code inside the DECLARE, BEGIN, and END keywords. Inside the BEGIN and END keywords, you can place another block, which is the nested block.

Why would you do this? There are a few reasons:

• To keep the code focused on one area. A nested block may contain the code and logic for a smaller part of your overall code.
• To handle exceptions separately. If there is an issue in part of your code, you may want to handle that separately and then continue with the execution of your main code.

A standard PL/SQL block looks like this:

DECLARE
  your_variables;
BEGIN
  your_code;
END;

A nested block goes inside the BEGIN and END keywords:

DECLARE
  your_variables;
BEGIN
  your_code;
  [DECLARE
    more_variables;]
  BEGIN
    more_code;
  END;
  even_more_code;
END;

Your second BEGIN and END block is inside the first BEGIN and END block. You can have code before and after this nested block, and code inside the nested block. The DECLARE section inside the nested block is optional.

Example: Nested Block

Let’s see an example of a nested block.

DECLARE
  l_salary person.salary%TYPE;
  l_fname person.fname%TYPE := 'John';
BEGIN
  SELECT salary
  INTO l_salary
  FROM person
  WHERE fname = l_fname;

  BEGIN
    UPDATE person
    SET salary = l_salary + 5000
    WHERE fname = l_fname;
    DBMS_OUTPUT.PUT_LINE('Salary updated.');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error updating table: ' || SQLERRM);
  END;

  DBMS_OUTPUT.PUT_LINE('More code here.');
END;

In this code, we are selecting a salary value for John into a variable called l_salary.

Then, inside the nested block, we are running an UPDATE statement to update the salary, and then writing a message. If this nested block fails, then we write a message explaining the error that happened.

This is done so that the UPDATE statement can run and any errors are handled separately. If there is an issue updating the data, an error is shown, but the rest of the code keeps running.

Here’s what the output shows:

1 row(s) updated.
Salary updated.
More code here.

If we change the code to make an error occur on the UPDATE statement, this is what happens:

DECLARE
  l_salary person.salary%TYPE;
  l_fname person.fname%TYPE := 'John';
BEGIN
  SELECT salary
  INTO l_salary
  FROM person
  WHERE fname = l_fname;

  BEGIN
    UPDATE person
    SET salary = l_salary + 5000
    WHERE fname = l_fname;
    DBMS_OUTPUT.PUT_LINE('Salary updated.');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error updating table: ' || SQLERRM);
  END;

  DBMS_OUTPUT.PUT_LINE('More code here.');
END;

The output is shown:

1 row(s) updated.
Error updating table:
ORA-01438: value larger than specified precision allowed for this column
More code here.

This error is shown because the UPDATE statement encountered an error. The error was handled by an exception and written to the screen. The rest of the code kept running (the put_line function that wrote “More code here”).

So that’s how you can write and use nested blocks.

Let’s take a look at another concept in PL/SQL: packages.

What is a Package in PL/SQL?

A package in PL/SQL is an object that groups logically related PL/SQL code. They have two parts: a package specification that defines the public interface for the package, and a package body which contains the code required for each procedure inside the package.

If you have experience in other programming languages, you can think of a package as being similar (but not the same as) a library. It contains multiple related procedures and the code can be called from other PL/SQL code.

Earlier in this guide, we just learned how to create procedures and functions. These objects are created on the database, but are standalone objects and are not inside packages. We’ll learn how to create packages in this guide.

Why use Packages?

There are several reasons why you should use packages for your PL/SQL code:

Easier to design applications: You can write the specification first (the names and parameters) of the packages and procedures, and work on the body (the implementation details or code) later if you like.

Better performance: Package code is loaded into memory when the code is first run. Whenever any other code in the package is run, it’s accessed from memory. This means it’s faster than reading and running the code from the disk.

Hide the details: You can provide access to the specification (procedure names and parameters) without providing the details of how they work. You can change the details whenever you want, and the code that refers to the package does not need to change.

Modular development: You can combine all of the related code into a package to make it easier to develop your application. Interfaces between packages can be developed and each package can be self-contained, similar to how classes work in object-oriented programming in other languages.

A package in PL/SQL contains two parts: a package specification and a package body. Let’s take a look at what these are.

What is a Package Specification?

A package specification in PL/SQL is where public code is declared for a package. It contains any variables, functions, and procedures that you want others to know about and access. The functions and procedures contain the parameter names and types. This is so other users and programs can access them and run them, without knowing the details of how they work.

How can you create a package specification?

You use the CREATE PACKAGE statement. It contains the name of the package and any code you want to include in the package specification.

For example, a package for calculating customer order shipping information could be:

CREATE PACKAGE order_shipping AS 
  PROCEDURE calc_shipping(dest_country VARCHAR2);
END order_shipping;

This code shows a few things:

• A new package is created, called order_shipping.
• It contains a single procedure called calc_shipping.
• The procedure takes one parameter: dest_country.

If you’re calling this procedure, this is all you need to know. You pass the value for the country and the shipping is calculated.

What is a Package Body?

A package body contains the implementation details, or code, that the package specification requires. This includes the code for each of the procedures or functions, and details of cursors. This is where the code inside each of the procedures and functions go.

A package body is created with the CREATE PACKAGE BODY statement. The declarations of the objects in the body must match those of the specification (procedure names, parameters).

Following on from the earlier example, we can write a package body that details what the calc_shipping function does:

CREATE PACKAGE BODY order_shipping AS
  PROCEDURE calc_shipping(dest_country VARCHAR2) AS
  l_fee shipping_lookup.shipping_fee%TYPE;
  BEGIN
    SELECT shipping_fee
    INTO l_fee
    FROM shipping_lookup
    WHERE source_country = 'USA'
    AND destination_country = dest_country;
    DBMS_OUTPUT.PUT_LINE('Shipping fee is ' || l_fee);
  END;
END order_shipping;

This code looks up the shipping fee from a table and displays it on the screen. If you run this code, it will create the package body for this package.

Note: If you want to drop a package to make changes and create it again, run DROP PACKAGE package_name.

Example of Using a PL/SQL Package

We have a package and package body declared and created on the database, after running the code above.

Let’s set up the sample data, and run the package code.

CREATE TABLE shipping_lookup (
  source_country VARCHAR2(100),
  destination_country VARCHAR2(100),
  shipping_fee NUMBER(10,2)
);

INSERT ALL
INTO shipping_lookup (source_country, destination_country, shipping_fee)
VALUES ('USA', 'USA', 5)
INTO shipping_lookup (source_country, destination_country, shipping_fee)
VALUES ('USA', 'UK', 18)
INTO shipping_lookup (source_country, destination_country, shipping_fee)
VALUES ('USA', 'France', 20)
INTO shipping_lookup (source_country, destination_country, shipping_fee)
VALUES ('USA', 'Canada', 7)

SELECT * FROM dual;

Now we can check the shipping_lookup table.

SELECT * FROM shipping_lookup;

We should have all we need to run our package code.

To run your package code, you refer to the package name and procedure name in PL/SQL:

BEGIN
  order_shipping.calc_shipping('UK');
END;

The output we get is

Statement processed.
Shipping fee is 18

This shows the shipping fee is 18, which is what our table shows. When we call the package, we don’t care how the shipping fee is calculated, we only want to get the value.

Some improvements to this package could be:

• Return the shipping_fee value from the procedure, rather than show it using PUT_LINE.
• Add an exception section to handle values that are not found in the table.
• If the majority of shipping is done from the USA but some is done from another country, perhaps add the source country as another parameter.

So that’s how you can create a package in PL/SQL. First create the package object, then the package body with the details. The packages can then be run from any PL/SQL code block.

Conclusion

A nested block is a BEGIN/END block within another BEGIN/END block. They are useful for containing related code and for handling errors without impacting the entire program.

Packages are used to contain related functionality, such as variables and procedures. They help with application design and performance. A package specification contains the public interface, or what other users can see, such as the procedure names and parameters. The package body contains the details of the procedures, including the code inside them.

Chapter Quiz

CREATE PACKAGE emp_calc AS
 PROCEDURE calc_bonus(emp_id NUMBER);
END emp_calc; 
/

CREATE PACKAGE BODY emp_calc AS
 PROCEDURE calculate_bonus(emp_id NUMBER) AS
 BEGIN
 UPDATE emp
 SET bonus = salary * 0.1
 WHERE id = emp_id;
 END;
END emp_calc;
/

Time's up

< Back to Tutorial Index