PL/SQL Tutorial: Chapter 4

calendar Dec 15, 2018 · 23 min read  ·
Share on: linkedin copy

In this chapter, you'll learn:

  • What procedures and functions are
  • How to create a procedure
  • How to create a function
  • What an exception is and how to handle one

Let's get started!

What is a PL/SQL Procedure?

A PL/SQL procedure, or stored procedure, is a set of code stored on the database and has a specific name. This procedure can be run by calling it in other code using this name, and the code inside the procedure will run and perform the actions inside.

Why create stored procedures?

  • It's where most of your PL/SQL code will go, rather than writing the unnamed code like we have done so far.
  • It allows for the code to be reused by calling it from other code
  • It allows for an interface or API to the database.

Earlier in this guide I mentioned one of the benefits of PL/SQL was to provide a common interface to the database. Procedures let you do that.

For example, rather than relying on every application or developer trying to write an INSERT statement that works, you can create a procedure that accepts a few parameters and runs an INSERT statement with them. This means the INSERT statements are consistent and the developers know what they need to provide.

 

What is a PL/SQL Function?

A PL/SQL function is a named piece of code on the database that lets you provide parameters, perform a calculation, and return a result. They can be used in PL/SQL or in regular SQL. PL/SQL functions are similar to procedures but have some differences.

 

What's the Difference Between a PL/SQL Procedure and Function?

The main differences between a PL/SQL procedure and a PL/SQL function are:

  • Functions must return a value. Procedures don't return values but can use OUT parameters.
  • Functions can be called from regular SQL, but procedures cannot.

Those are the main two differences. Procedures and functions are often used for two different things:

  • Performing calculations and returning a value? Use a function.
  • Performing steps and inserting, updating, or deleting database records? Use a procedure.
  • Need to call the code from SQL? Use a function.

Let's take a look at how you can create a procedure and a function.

 

How to Create a PL/SQL Procedure

A PL/SQL procedure is created with the CREATE PROCEDURE statement. The syntax of the statement looks like this:

1CREATE [OR REPLACE] PROCEDURE procedure_name
2[ ( parameter_name [ IN | OUT | IN OUT ] parameter_type [, ...] ) ] 
3{ IS | AS } 
4BEGIN 
5  procedure_body
6END procedure_name;

There are a few things to notice here:

  • The procedure_name is the name of the procedure to create.
  • The OR REPLACE keyword is optional. If you include it, it means that when you run this statement it will replace the procedure with the same name. If you don't include it and a procedure with this name already exists, you'll get an error.

One or more parameters can be provided:

  • Parameter_name is the name of the parameter, which is used in your procedure code
  • A parameter can be an IN parameter (value is provided to the procedure to use), an OUT parameter (value is determined by the procedure and returned to the code that has called it), or IN OUT (combination of IN and OUT).
  • A parameter has a parameter_type which is an SQL data type, such as NUMBER.
  • Further parameters can be added and separated by commas.

After you define the parameters:

  • Specify either IS or AS to start the procedure code.
  • BEGIN is specified just like we have learned so far.
  • You add the code for your procedure, and finish with END procedure_name.

 

Example PL/SQL Procedure

Let's take a look at an example. We'll use our earlier code that checks values for a square or rectangle:

 1DECLARE
 2  l_width NUMBER(5) := 18;
 3  l_length NUMBER(5) := 15;
 4BEGIN
 5  IF (l_width < 0 OR l_length < 0) THEN
 6    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
 7  ELSIF (l_width = l_length AND l_length > 50 ) THEN
 8    DBMS_OUTPUT.PUT_LINE('This is a big square.');
 9  ELSIF (l_width = l_length) THEN
10    DBMS_OUTPUT.PUT_LINE('This is a square.');
11  ELSE
12    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
13  END IF;
14END;

This code is not a procedure or a function, as it has no name. This code is called an "anonymous block", because it has no name (it's anonymous) and is a block of code. So, if anyone refers to an anonymous block, it means a section of PL/SQL code that is not a function or procedure.

To convert this to a procedure, we need to give it a name. Let's call it "check_rectangle". Names of functions and procedures in PL/SQL must meet the following criteria:

  • The maximum length is 30 characters
  • The first character must be a letter
  • The characters after the first character can be letter, number, dollar sign $, number sign #, or an underscore _.

Also, PL/SQL names are case-insensitive. This means the following names are all equivalent:

  • check_rectangle
  • CHECK_RECTANGLE
  • Check_Rectangle

It's up to you what case you choose for your PL/SQL names, as it comes down to your coding standards. In this guide, I'll be using all lowercase for my procedure and function names.

Let's start our check_rectangle procedure:

1CREATE OR REPLACE PROCEDURE check_rectangle

I have added the OR REPLACE keywords here so that you can run the same statement over and over again if you want to make any changes. You don't need to drop the procedure separately.

We then have either the IS or AS keyword. I'll use the AS because it's a personal preference. There is no difference between these two keywords.

1CREATE OR REPLACE PROCEDURE check_rectangle
2AS

Then we can add in our code. We can copy and paste the code from earlier (the anonymous block) and add it here, and add the procedure_name to the END keyword.

 1CREATE OR REPLACE PROCEDURE check_rectangle
 2AS
 3DECLARE
 4  l_width NUMBER(5) := 18;
 5  l_length NUMBER(5) := 15;
 6BEGIN
 7  IF (l_width < 0 OR l_length < 0) THEN
 8    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
 9  ELSIF (l_width = l_length AND l_length > 50 ) THEN
10    DBMS_OUTPUT.PUT_LINE('This is a big square.');
11  ELSIF (l_width = l_length) THEN
12    DBMS_OUTPUT.PUT_LINE('This is a square.');
13  ELSE
14    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
15  END IF;
16END check_rectangle;

If we run this code, we get an error message:

1Errors: PROCEDURE CHECK_RECTANGLE
2Line/Col: 3/1
3PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: 
4begin function pragma procedure subtype type 
5<an identifier> <a double-quoted delimited-identifier> 
6current cursor delete exists prior external language

This error appears because we have a DECLARE statement in our code. Because we're writing a procedure, we don't need the DECLARE keyword. The variables can go just after the AS or IS keyword and before the BEGIN. Simply remove the DECLARE keyword:

 1CREATE OR REPLACE PROCEDURE check_rectangle
 2AS
 3  l_width NUMBER(5) := 18;
 4  l_length NUMBER(5) := 15;
 5BEGIN
 6  IF (l_width < 0 OR l_length < 0) THEN
 7    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
 8  ELSIF (l_width = l_length AND l_length > 50 ) THEN
 9    DBMS_OUTPUT.PUT_LINE('This is a big square.');
10  ELSIF (l_width = l_length) THEN
11    DBMS_OUTPUT.PUT_LINE('This is a square.');
12  ELSE
13    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
14  END IF;
15END check_rectangle;

You'll get this output:

1Procedure created.

When creating a procedure like this, the procedure is only created and not run. You'll only see the "Procedure created" message, not any of the output inside the procedure. You'll need to call or run the procedure to do that.

To run or call a PL/SQL stored procedure, you can do one of three things:

  • CALL procedure_name;
  • EXEC procedure_name;
  • BEGIN procedure_name END;

 

Your code and output will look like this:

1CALL check_rectangle;

1Statement processed.
2This is a rectangle.

You can use EXEC:

1EXEC check_rectangle;

1Statement processed.
2This is a rectangle.

Or you can use a BEGIN END block:

1BEGIN
2  check_rectangle;
3END;

1Statement processed.
2This is a rectangle.

It's easy to run a procedure after it's been created.

 

Using Input Parameters with PL/SQL Procedures

We have just created a procedure that checks two values and determines if a shape is a square or rectangle.

What if you wanted to run this procedure with different values?

One way you could do it is:

  • Find the code used to create the procedure
  • Modify the variables
  • Recreate the procedure
  • Call the procedure

This is a lot of steps just to see the procedure with different values.

Another way to do this is to use parameters. Parameters let you specify the values used for the variables when you run the procedure, which are then used in the calculation. It's much better to do it this way than to recompile the procedure.

In this example, we'll learn how to provide the length and width values as parameters.

Our code looks like this so far:

 1CREATE OR REPLACE PROCEDURE check_rectangle
 2AS
 3  l_width NUMBER(5) := 18;
 4  l_length NUMBER(5) := 15;
 5BEGIN
 6  IF (l_width < 0 OR l_length < 0) THEN
 7    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
 8  ELSIF (l_width = l_length AND l_length > 50 ) THEN
 9    DBMS_OUTPUT.PUT_LINE('This is a big square.');
10  ELSIF (l_width = l_length) THEN
11    DBMS_OUTPUT.PUT_LINE('This is a square.');
12  ELSE
13    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
14  END IF;
15END check_rectangle;

To add parameters, we add brackets after the procedure_name declaration on the first line:

 1CREATE OR REPLACE PROCEDURE check_rectangle ()
 2AS
 3  l_width NUMBER(5) := 18;
 4  l_length NUMBER(5) := 15;
 5BEGIN
 6  IF (l_width < 0 OR l_length < 0) THEN
 7    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
 8  ELSIF (l_width = l_length AND l_length > 50 ) THEN
 9    DBMS_OUTPUT.PUT_LINE('This is a big square.');
10  ELSIF (l_width = l_length) THEN
11    DBMS_OUTPUT.PUT_LINE('This is a square.');
12  ELSE
13    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
14  END IF;
15END check_rectangle;

Inside the brackets, we add two parameters: one for l_width, and one for l_length. We define these as IN parameters, because they are being provided to the procedure by other code. We also specify them as a NUMBER data type.

 1CREATE OR REPLACE PROCEDURE check_rectangle (
 2l_width IN NUMBER, l_length IN NUMBER
 3)
 4AS
 5  l_width NUMBER(5) := 18;
 6  l_length NUMBER(5) := 15;
 7BEGIN
 8  IF (l_width < 0 OR l_length < 0) THEN
 9    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
10  ELSIF (l_width = l_length AND l_length > 50 ) THEN
11    DBMS_OUTPUT.PUT_LINE('This is a big square.');
12  ELSIF (l_width = l_length) THEN
13    DBMS_OUTPUT.PUT_LINE('This is a square.');
14  ELSE
15    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
16  END IF;
17END check_rectangle;

I've added the parameters on a separate line to make it more readable, but it's up to you how to do this.

Now we need to remove them from the declaration section. They are already declared in the parameters.

 1CREATE OR REPLACE PROCEDURE check_rectangle (
 2l_width IN NUMBER, l_length IN NUMBER
 3)
 4AS
 5BEGIN
 6  IF (l_width < 0 OR l_length < 0) THEN
 7    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
 8  ELSIF (l_width = l_length AND l_length > 50 ) THEN
 9    DBMS_OUTPUT.PUT_LINE('This is a big square.');
10  ELSIF (l_width = l_length) THEN
11    DBMS_OUTPUT.PUT_LINE('This is a square.');
12  ELSE
13    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
14  END IF;
15END check_rectangle;

Let's run this on the database.

1Procedure created.

The procedure is now created. Let's run this procedure and use parameters. For this example, I'll use the CALL keyword but it will work with any method we've seen so far.

Let's try it with the original values of 18 and 15. To use parameters in a call to a procedure, you specify them inside brackets after the procedure name, separated by commas, like this:

1CALL check_rectangle(18, 15);

The output from this statement is:

1Statement processed.
2This is a rectangle.

This is called "positional notation", because you're passing parameters in that are getting processed based on their position inside the brackets.

The other way to do this is called "named notation", where you specify the parameter name and the value when you call it:

1CALL check_rectangle(l_width=>18, l_length=>15);

This is done by specifying the parameter name, and then the value, separated by a => symbol to assign the value of the parameter.

This shows the following output:

1Statement processed.
2This is a rectangle.

Now you've seen how to run the code with parameters, let's run a few different variations of the procedure to really see how useful parameters are:

1CALL check_rectangle(18, 15);
2CALL check_rectangle(20, 32);
3CALL check_rectangle(80, 80);
4CALL check_rectangle(-2, 12);

The output for this is:

1Statement processed.
2This is a rectangle.
3Statement processed.
4This is a rectangle.
5Statement processed.
6This is a big square.
7Statement processed.
8The width or length is negative.

This example shows how useful parameters are. You can simply change the input values to have the procedure run again using these new values.

 

Using Output Parameters with PL/SQL Procedures

We've learned how to use input parameters to specify inputs to our procedure. Let's learn how to use output parameters.

Why would you use output parameters in PL/SQL code? The main reason is so you can use the output from the procedure elsewhere in your code.

We'll update our procedure to use an output parameter, and then use that in other code.

Here's our procedure at the moment:

 1CREATE OR REPLACE PROCEDURE check_rectangle (
 2l_width IN NUMBER, l_length IN NUMBER
 3)
 4AS
 5BEGIN
 6  IF (l_width < 0 OR l_length < 0) THEN
 7    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
 8  ELSIF (l_width = l_length AND l_length > 50 ) THEN
 9    DBMS_OUTPUT.PUT_LINE('This is a big square.');
10  ELSIF (l_width = l_length) THEN
11    DBMS_OUTPUT.PUT_LINE('This is a square.');
12  ELSE
13    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
14  END IF;
15END check_rectangle;

Let's say we want to return the message to display, instead of displaying it to the screen using PUT_LINE. This is so the code that calls this procedure can decide what to do with it: either display it to the screen, add it to a database table, or something else.

First, we add an OUT parameter for the message. This is so the variable to store this is created, and it can be returned to the code that calls it.

 1CREATE OR REPLACE PROCEDURE check_rectangle (
 2l_width IN NUMBER, l_length IN NUMBER, l_message OUT VARCHAR2
 3)
 4AS
 5BEGIN
 6  IF (l_width < 0 OR l_length < 0) THEN
 7    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
 8  ELSIF (l_width = l_length AND l_length > 50 ) THEN
 9    DBMS_OUTPUT.PUT_LINE('This is a big square.');
10  ELSIF (l_width = l_length) THEN
11    DBMS_OUTPUT.PUT_LINE('This is a square.');
12  ELSE
13    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
14  END IF;
15END check_rectangle;

We can then set this variable to the message we want to show instead of using PUT_LINE:

 1CREATE OR REPLACE PROCEDURE check_rectangle (
 2l_width IN NUMBER, l_length IN NUMBER, l_message OUT VARCHAR2
 3)
 4AS
 5BEGIN
 6  IF (l_width < 0 OR l_length < 0) THEN
 7    l_message := 'The width or length is negative.';
 8  ELSIF (l_width = l_length AND l_length > 50 ) THEN
 9    l_message := 'This is a big square.';
10  ELSIF (l_width = l_length) THEN
11    l_message := 'This is a square.';
12  ELSE
13    l_message := 'This is a rectangle.';
14  END IF;
15END check_rectangle;

If we run this code, this is the output we get:

1Procedure created.

We can then output the message using other PL/SQL code, or write it to a database instead:

1DECLARE
2  l_output_message VARCHAR2(100);
3BEGIN
4  check_rectangle(10, 5, l_output_message);
5  DBMS_OUTPUT.PUT_LINE(l_output_message);
6END;

In this code, we have declared a new variable for the output message. In the BEGIN block, we run the check_rectangle function with our length and width parameters. The third parameter is the l_output_message variable, which is set inside the check_rectangle procedure. We then output this value.

Running this code shows this output:

1Statement processed.
2This is a rectangle.

We could insert this into a database table instead if we wanted, because the code is flexible.

1CREATE TABLE message_output (
2  message_val VARCHAR2(100)
3);

We can then run this code, which inserts into the table instead of displays it on the screen:

1DECLARE
2  l_output_message VARCHAR2(100);
3BEGIN
4  check_rectangle(10, 5, l_output_message);
5  INSERT INTO message_output(message_val) VALUES (l_output_message);
6END;

1 row(s) inserted.

If we SELECT from the table, we can see our value.

1SELECT * FROM message_output;
MESSAGE_VAL
This is a rectangle.

So, using output and input parameters can be very useful in PL/SQL procedures as they allow you to do what you need with the values in your code.

 

How to Create a PL/SQL Function

The other type of code object you can create with PL/SQL is a function.

A PL/SQL function runs some code and returns a value. It can be used in regular SQL as well as PL/SQL.

Creating a function is similar to creating a procedure:

1CREATE [OR REPLACE] FUNCTION function_name
2[ ( parameter_name [ IN | OUT | IN OUT ] parameter_type [, ...] ) ] 
3RETURN return_datatype
4{ IS | AS } 
5BEGIN 
6  function_body
7END function_name;

There are a few things to notice here:

  • The function_name is the name of the function to create.
  • The OR REPLACE keyword is optional. If you include it, it means that when you run this statement it will replace the function with the same name. If you don't include it and a function with this name already exists, you'll get an error.

One or more parameters can be provided:

  • Parameter_name is the name of the parameter, which is used in your function code
  • A parameter can be an IN parameter (value is provided to the function to use), an OUT parameter (value is determined by the function and returned to the code that has called it), or IN OUT (combination of IN and OUT).
  • A parameter has a parameter_type which is an SQL data type, such as NUMBER.
  • Further parameters can be added and separated by commas.

After you define the parameters:

  • Add the RETURN keyword and then the data type of the return value. This lets the database know the type of data that will be returned from the function.
  • Specify either IS or AS to start the function code.
  • BEGIN is specified just like we have learned so far.
  • You add the code for your function, and finish with END function_name.

 

Example PL/SQL Function

Let's write an example PL/SQL function. So far we've been working with code that determines what kind of shape is created when a length and width is provided. Let's write a function that calculates the area of a shape with the length and width provided.

Our function will look like this:

1CREATE OR REPLACE FUNCTION shape_area
2(l_width IN NUMBER, l_length IN NUMBER)
3RETURN NUMBER
4AS
5BEGIN
6 
7END;

We have the declaration of the function, which includes the name of shape_area. It also includes two parameters, and returns a number.

But the function doesn't do anything.

Let's write some function code.

1CREATE OR REPLACE FUNCTION shape_area
2(l_width IN NUMBER, l_length IN NUMBER)
3RETURN NUMBER
4AS
5BEGIN
6  RETURN l_width * l_length;
7END;

We've added one line, which calculates the length * width and returns it using the RETURN keyword. This means that any time this function is called, it returns the value of the length multiplied by the width.

Let's create this function. The output shows:

1Function created.

The function is now created. We can call the function from PL/SQL, like this:

1BEGIN
2  DBMS_OUTPUT.PUT_LINE(shape_area(5, 4));
3END;

This code shows the following output:

1Statement processed.
220

Because this code is a function, we can call it from SQL as well:

1SELECT shape_area(5, 4)
2FROM dual;
SHAPE_AREA(5,4)
20

Creating a function is just like creating a procedure, except the RETURN statement is needed.

 

Exceptions in PL/SQL

What is an exception in PL/SQL?

An exception is an error that happens when running your code. Some errors in your code can be found before you run the code, such as missing semicolons or other characters. However, exceptions occur when you run your code and can't be predicted before running the code, such as adding a text value to a number data type.

The good news in PL/SQL is that you can write code to work with these exceptions. This is called "exception handling", and it's where your code can see that an error has happened and take a different action. Without exception handling, your errors are reported to the program that calls it and displayed either in your IDE or in the application.

 

Syntax for Exception Handling in PL/SQL

Writing code to handle exceptions in PL/SQL looks like this:

1BEGIN
2  executable_code;
3EXCEPTION
4  WHEN exception_type THEN
5    exception_code;
6  WHEN OTHERS THEN
7    exception_code;
8END;

The BEGIN section is the same: this is where your executable code goes.

AN EXCEPTION section is added after your executable code, at the end of your code block, before the END statement. This EXCEPTION section contains code for handling any exceptions that occur.

Within the EXCEPTION section is a series of WHEN THEN statements. Each of these statements relates to a possible exception that can be found. When an exception is found when running your code, it's called "throwing an exception". I'm not sure why it's called "throwing", but that's just the programming term for it.

When an exception is found, the WHEN statements are checked. In the WHEN statements, you specify the type of exception, like an IF statement. If the type that was found matches the type in your WHEN statement, the code underneath is run.

If the type doesn't match any of your WHEN statements, you can have a WHEN OTHERS THEN statement, which runs in this situation.

Let's take a look at an example.

 

Example Exception Code in PL/SQL

We'll use a variation of our earlier code that calculates the area of a shape. Instead of using a procedure or a function, we'll just use an anonymous PL/SQL block.

The code looks like this:

1DECLARE
2  l_length NUMBER := 4;
3  l_width NUMBER := 5;
4  l_area NUMBER(3);
5BEGIN
6  l_area := l_length * l_width;
7  DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
8END;

Our l_area variable has a maximum size of 3. We can run this code and get this output:

1Statement processed.
2The area is: 20

It shows the area is 20, because 5*4 is 20.

Let's say we have a length and width that are both three digits.

1DECLARE
2  l_length NUMBER := 400;
3  l_width NUMBER := 500;
4  l_area NUMBER(3);
5BEGIN
6  l_area := l_length * l_width;
7  DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
8END;

What happens when we run the code?

We get an error message:

1ORA-06502: PL/SQL: numeric or value error: number precision too large
2ORA-06512: at line 6 ORA-06512: at "SYS.DBMS_SQL", line 1721

This error message has a code: ORA-06502. The message says the number precision is too large. This is because 400 * 500 is 200,000, which is too large for the NUMBER(3) variable.

Rather than display this error message, we can write some code that tells the program what to do. We do this using the EXCEPTION section.

 1DECLARE
 2  l_length NUMBER := 400;
 3  l_width NUMBER := 500;
 4  l_area NUMBER(3);
 5BEGIN
 6  l_area := l_length * l_width;
 7  DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
 8EXCEPTION
 9  WHEN VALUE_ERROR THEN
10    DBMS_OUTPUT.PUT_LINE('There was a problem specifying the area. ' || SQLERRM);
11END;

We have added in the EXCEPTION section. We have also added in the WHEN statement: WHEN VALUE_ERROR. This will pick up any ORA-06502 errors we may experience in our code. The reason that VALUE_ERROR is used instead of the error code is that the name is easy to identify and read.

After the WHEN statement, we have a PUT_LINE function call that writes a message, saying there was a problem with the area. We then concatenate something called SQLERRM.

SQLERRM is an SQL function that returns the error message found in the recently-executed code. This will allow us to see the details of the error message.

If we run this code, we get this output:

1Statement processed.
2There was a problem specifying the area.
3ORA-06502: PL/SQL: numeric or value error: number precision too large

This has been written as an output from PUT_LINE rather than an error message. It means the program will keep running and you can do more with this message, instead of just stopping the program.

 

Raising Exceptions in PL/SQL

We've seen what happens if we perform a calculation to set a value that's higher than the maximum size or do something that Oracle does not expect. This uses a built-in exception.

What if we find something that our business rules say is not valid, but it's OK with the Oracle database?

One example of this is what if one of the parameters is negative?

Our code could look like this:

 1DECLARE
 2  l_length NUMBER := -4;
 3  l_width NUMBER := 3;
 4  l_area NUMBER(3);
 5BEGIN
 6  l_area := l_length * l_width;
 7  DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
 8EXCEPTION
 9  WHEN VALUE_ERROR THEN
10    DBMS_OUTPUT.PUT_LINE('There was a problem specifying the area. ' || SQLERRM);
11END;

The output is:

1Statement processed.
2The area is: -12

This shows the area as -12, which is not a valid value. We don't want to have negative area values.

We can avoid this by creating or raising our own exception. To do this:

  • Declare a new variable which has a type of EXCEPTION
  • Check the criteria that cause this issue, and raise the exception if they are true

To do this in our code, we declare a new variable with a type of EXCEPTION.

 1DECLARE
 2  l_length NUMBER := -4;
 3  l_width NUMBER := 3;
 4  l_area NUMBER(3);
 5  ex_negative_area EXCEPTION;
 6BEGIN
 7  l_area := l_length * l_width;
 8  DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
 9EXCEPTION
10  WHEN VALUE_ERROR THEN
11    DBMS_OUTPUT.PUT_LINE('There was a problem specifying the area. ' || SQLERRM);
12END;

Now, we've created a new variable called ex_negative_area. This is an EXCEPTION. We now need to check if the area is negative, and if it is, raise the exception.

 1DECLARE
 2  l_length NUMBER := -4;
 3  l_width NUMBER := 3;
 4  l_area NUMBER(3);
 5  ex_negative_area EXCEPTION;
 6BEGIN
 7  l_area := l_length * l_width;
 8  IF (l_area < 0) THEN
 9    RAISE ex_negative_area;
10  END IF;
11  DBMS_OUTPUT.PUT_LINE(
12    'The area is: ' || l_area);
13EXCEPTION
14  WHEN VALUE_ERROR THEN
15    DBMS_OUTPUT.PUT_LINE(
16      'There was a problem specifying the area. ' || SQLERRM);
17END;

This code checks if the area is less than zero. If it is, then the exception is raised using the RAISE ex_negative_area statement.

Finally, we need to add this to our EXCEPTION section. If we just raise the exception without writing a WHEN statement, this is what we will see:

1User-Defined Exception
2ORA-06512: at line 9 ORA-06512: at "SYS.DBMS_SQL", line 1721

The user-defined exception error message means we raised the exception but did nothing with it.

Our new code will look like this:

 1DECLARE
 2  l_length NUMBER := -4;
 3  l_width NUMBER := 3;
 4  l_area NUMBER(3);
 5  ex_negative_area EXCEPTION;
 6BEGIN
 7  l_area := l_length * l_width;
 8  IF (l_area < 0) THEN
 9    RAISE ex_negative_area;
10  END IF;
11  DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
12EXCEPTION
13  WHEN ex_negative_area THEN
14    DBMS_OUTPUT.PUT_LINE(
15      'The area is negative. Please confirm the input values are positive.');
16  WHEN VALUE_ERROR THEN
17    DBMS_OUTPUT.PUT_LINE(
18      'There was a problem specifying the area. ' || SQLERRM);
19END;

We can now run this code.

1Statement processed.
2The area is negative. Please confirm the input values are positive.

The message shown is what we have entered in the EXCEPTION section. It says the area is negative, which is what we wanted to display.

 

Conclusion

Procedures and functions in PL/SQL are objects that contain PL/SQL code for you to run at a later stage. They have several advantages such as the ability to reuse code and to simplify your programming. There are some differences between procedures and functions.

Exceptions are errors that are encountered as your program is running. They can be handled within your PL/SQL code, otherwise they will display an error in your IDE or in your application.

< Back to Tutorial Index

Next Chapter >

comments powered by Disqus