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:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ ( parameter_name [ IN | OUT | IN OUT ] parameter_type [, ...] ) ]
{ IS | AS }
BEGIN
procedure_body
END 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:
DECLARE
l_width NUMBER(5) := 18;
l_length NUMBER(5) := 15;
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END;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:
CREATE OR REPLACE PROCEDURE check_rectangleI 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.
CREATE OR REPLACE PROCEDURE check_rectangle
ASThen 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.
CREATE OR REPLACE PROCEDURE check_rectangle
AS
DECLARE
l_width NUMBER(5) := 18;
l_length NUMBER(5) := 15;
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END check_rectangle;If we run this code, we get an error message:
Errors: PROCEDURE CHECK_RECTANGLE Line/Col: 3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current 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:
CREATE OR REPLACE PROCEDURE check_rectangle
AS
l_width NUMBER(5) := 18;
l_length NUMBER(5) := 15;
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END check_rectangle;You’ll get this output:
Procedure 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:
CALL check_rectangle;Statement processed. This is a rectangle.
You can use EXEC:
EXEC check_rectangle;Statement processed. This is a rectangle.
Or you can use a BEGIN END block:
BEGIN
check_rectangle;
END;Statement processed. This 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:
CREATE OR REPLACE PROCEDURE check_rectangle
AS
l_width NUMBER(5) := 18;
l_length NUMBER(5) := 15;
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END check_rectangle;To add parameters, we add brackets after the procedure_name declaration on the first line:
CREATE OR REPLACE PROCEDURE check_rectangle ()
AS
l_width NUMBER(5) := 18;
l_length NUMBER(5) := 15;
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END 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.
CREATE OR REPLACE PROCEDURE check_rectangle (
l_width IN NUMBER, l_length IN NUMBER
)
AS
l_width NUMBER(5) := 18;
l_length NUMBER(5) := 15;
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END 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.
CREATE OR REPLACE PROCEDURE check_rectangle (
l_width IN NUMBER, l_length IN NUMBER
)
AS
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END check_rectangle;Let’s run this on the database.
Procedure 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:
CALL check_rectangle(18, 15);The output from this statement is:
Statement processed. This 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:
CALL 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:
Statement processed. This 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:
CALL check_rectangle(18, 15);
CALL check_rectangle(20, 32);
CALL check_rectangle(80, 80);
CALL check_rectangle(-2, 12);The output for this is:
Statement processed. This is a rectangle. Statement processed. This is a rectangle. Statement processed. This is a big square. Statement processed. The 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:
CREATE OR REPLACE PROCEDURE check_rectangle (
l_width IN NUMBER, l_length IN NUMBER
)
AS
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END 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.
CREATE OR REPLACE PROCEDURE check_rectangle (
l_width IN NUMBER, l_length IN NUMBER, l_message OUT VARCHAR2
)
AS
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
ELSIF (l_width = l_length AND l_length > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('This is a big square.');
ELSIF (l_width = l_length) THEN
DBMS_OUTPUT.PUT_LINE('This is a square.');
ELSE
DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
END IF;
END check_rectangle;We can then set this variable to the message we want to show instead of using PUT_LINE:
CREATE OR REPLACE PROCEDURE check_rectangle (
l_width IN NUMBER, l_length IN NUMBER, l_message OUT VARCHAR2
)
AS
BEGIN
IF (l_width < 0 OR l_length < 0) THEN
l_message := 'The width or length is negative.';
ELSIF (l_width = l_length AND l_length > 50 ) THEN
l_message := 'This is a big square.';
ELSIF (l_width = l_length) THEN
l_message := 'This is a square.';
ELSE
l_message := 'This is a rectangle.';
END IF;
END check_rectangle;If we run this code, this is the output we get:
Procedure created.
We can then output the message using other PL/SQL code, or write it to a database instead:
DECLARE
l_output_message VARCHAR2(100);
BEGIN
check_rectangle(10, 5, l_output_message);
DBMS_OUTPUT.PUT_LINE(l_output_message);
END;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:
Statement processed. This is a rectangle.
We could insert this into a database table instead if we wanted, because the code is flexible.
CREATE TABLE message_output (
message_val VARCHAR2(100)
);We can then run this code, which inserts into the table instead of displays it on the screen:
DECLARE
l_output_message VARCHAR2(100);
BEGIN
check_rectangle(10, 5, l_output_message);
INSERT INTO message_output(message_val) VALUES (l_output_message);
END;1 row(s) inserted.
If we SELECT from the table, we can see our value.
SELECT * 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:
CREATE [OR REPLACE] FUNCTION function_name
[ ( parameter_name [ IN | OUT | IN OUT ] parameter_type [, ...] ) ]
RETURN return_datatype
{ IS | AS }
BEGIN
function_body
END 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:
CREATE OR REPLACE FUNCTION shape_area
(l_width IN NUMBER, l_length IN NUMBER)
RETURN NUMBER
AS
BEGIN
END;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.
CREATE OR REPLACE FUNCTION shape_area
(l_width IN NUMBER, l_length IN NUMBER)
RETURN NUMBER
AS
BEGIN
RETURN l_width * l_length;
END;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:
Function created.
The function is now created. We can call the function from PL/SQL, like this:
BEGIN
DBMS_OUTPUT.PUT_LINE(shape_area(5, 4));
END;This code shows the following output:
Statement processed. 20
Because this code is a function, we can call it from SQL as well:
SELECT shape_area(5, 4)
FROM 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:
BEGIN
executable_code;
EXCEPTION
WHEN exception_type THEN
exception_code;
WHEN OTHERS THEN
exception_code;
END;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:
DECLARE
l_length NUMBER := 4;
l_width NUMBER := 5;
l_area NUMBER(3);
BEGIN
l_area := l_length * l_width;
DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
END;Our l_area variable has a maximum size of 3. We can run this code and get this output:
Statement processed. The 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.
DECLARE
l_length NUMBER := 400;
l_width NUMBER := 500;
l_area NUMBER(3);
BEGIN
l_area := l_length * l_width;
DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
END;What happens when we run the code?
We get an error message:
ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-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.
DECLARE
l_length NUMBER := 400;
l_width NUMBER := 500;
l_area NUMBER(3);
BEGIN
l_area := l_length * l_width;
DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('There was a problem specifying the area. ' || SQLERRM);
END;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:
Statement processed. There was a problem specifying the area. ORA-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:
DECLARE
l_length NUMBER := -4;
l_width NUMBER := 3;
l_area NUMBER(3);
BEGIN
l_area := l_length * l_width;
DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('There was a problem specifying the area. ' || SQLERRM);
END;The output is:
Statement processed. The 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.
DECLARE
l_length NUMBER := -4;
l_width NUMBER := 3;
l_area NUMBER(3);
ex_negative_area EXCEPTION;
BEGIN
l_area := l_length * l_width;
DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('There was a problem specifying the area. ' || SQLERRM);
END;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.
DECLARE
l_length NUMBER := -4;
l_width NUMBER := 3;
l_area NUMBER(3);
ex_negative_area EXCEPTION;
BEGIN
l_area := l_length * l_width;
IF (l_area < 0) THEN
RAISE ex_negative_area;
END IF;
DBMS_OUTPUT.PUT_LINE(
'The area is: ' || l_area);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(
'There was a problem specifying the area. ' || SQLERRM);
END;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:
User-Defined Exception ORA-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:
DECLARE
l_length NUMBER := -4;
l_width NUMBER := 3;
l_area NUMBER(3);
ex_negative_area EXCEPTION;
BEGIN
l_area := l_length * l_width;
IF (l_area < 0) THEN
RAISE ex_negative_area;
END IF;
DBMS_OUTPUT.PUT_LINE('The area is: ' || l_area);
EXCEPTION
WHEN ex_negative_area THEN
DBMS_OUTPUT.PUT_LINE(
'The area is negative. Please confirm the input values are positive.');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(
'There was a problem specifying the area. ' || SQLERRM);
END;We can now run this code.
Statement processed. The 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.
Chapter Quiz
This tutorial has the clearest explanations of PL/SQL concepts that I’ve ever seen.
Using Live SQL is great for reinforcing the concepts.
Glad you like it!
great examples