You've reached the end of your free preview.
Want to read all 23 pages?
Unformatted text preview: Oracle PL/SQL
A Guide to
Stored Procedures, Stored Functions,
Cursors and Triggers
Contents
1. Introduction .................................................................................................................................... 2 2. PL/SQL ............................................................................................................................................. 3
2.1 PL/SQL Blocks ................................................................................................................................ 5
2.2 Exception Handling ....................................................................................................................... 7
2.3 Exercises ........................................................................................................................................ 9 3. Stored Procedures in Oracle ......................................................................................................... 10
3.1 IF statements .............................................................................................................................. 11
3.2 Exercises ...................................................................................................................................... 11
3.3 Insert, Update and Delete Statements ....................................................................................... 12 4. Using Cursors within PL/SQL ......................................................................................................... 13
4.1 Loops ........................................................................................................................................... 13
4.2 Exercises ...................................................................................................................................... 13 5. Stored Functions in Oracle ............................................................................................................ 15
5.1 Exercises ...................................................................................................................................... 16 6. Developing Triggers in PL/SQL ...................................................................................................... 17
6.1 BEFORE OR AFTER? ..................................................................................................................... 19
6.2 :OLD OR :NEW? ........................................................................................................................... 20
6.3 Row and Statement Level Triggers ............................................................................................. 20
6.4 Exercises ...................................................................................................................................... 22 7. Summary ....................................................................................................................................... 23 8. References .................................................................................................................................... 23 © David Nelson: Version 1.5 (24th February 2017)
1 1. Introduction
PL/SQL stands for Procedural Language/SQL and is the language used by Oracle within its DBMS for
database programming. It is an extension of the SQL query language and is designed to enhance SQL
with computational capabilities. It can be used in a variety of ways, including:
- to enhance the data manipulation (i.e. query) capabilities in tools such as SQL Developer and
SQL Plus;
to enhance the constraint mechanisms of Oracle databases via database triggers;
to allow event driven programming through graphical forms applications such as Oracle
Forms Developer and Oracle Application Express (APEX). In this tutorial you will develop PL/SQL code to enhance SQL’s data manipulation capabilities
through definitions of PL/SQL code blocks as well as developing stored procedures and stored
functions. You will then continue to look at the use of PL/SQL for developing database triggers to
manage complex constraints on your data.
In a later tutorial you will see how to use PL/SQL to develop graphical forms applications such as
through PHP or Oracle APEX.
Before you can run this tutorial you will need to log in to SQL Developer. Your username and
password are the same as the ones you had previously used during this module. If you are using
University of Sunderland computers then the database that you will be connecting to is FASDBPRD.
In this tutorial you will use the Property for Rent database (property2015.sql) that you have used
throughout the module. You may need to check the instructions in the SQL booklet (i.e. Unit 1) if
you are unsure how to load this database into your Oracle space. It is advisable that you recreate
the entire database by running the property.sql file in SQL Developer. This is to ensure that the
tables and data are correct before starting this tutorial.
You should run all examples in this tutorial, and attempt all of the exercises. 2 2. PL/SQL
A PL/SQL code block can be written in SQL Developer to run a set of instructions. For example, the
block of code below will ask you to type in a tenant number and then will display the surname of
that tenant.
However, before you run any PL/SQL code in SQL Developer you need to set the SERVEROUTPUT
environment variable to on. To do this, in the SQL Worksheet Window type the following code, and run it using the Run Script button (F5). Now type the following code into SQL Developer and then run it using the Run Script button (F5).
DECLARE
my_surname TENANT.T_SURNAME%TYPE;
BEGIN
SELECT t_surname
INTO my_surname
FROM tenant
WHERE tenant_no = '&enter_tenant_no';
DBMS_OUTPUT.PUT_LINE('Tenant''s surname: '||my_surname);
END;
/ 3 It is important to include the ‘/’ on a separate line at the end of your PL/SQL code block. This is
because each statement within your PL/SQL code block is terminated by a semicolon ‘;’ so this can
no longer indicate the end of the complete statement that you want to run. You therefore use a ‘/’
at the end to tell Oracle that the PL/SQL code block is finished.
When you run the code, you should get a window which prompts you to type in a tenant number.
Choose one which you know exists in the tenant table (e.g. 3003), type it into the prompt and press
the OK button. Upon running, in the Script Output window you should see a copy of the code you have entered,
with a message stating ‘anonymous block completed’, and then the message showing the Tenant’s
surname. 4 Remember if it does not print the tenant’s surname then you probably forgot to set serveroutput
on.
Now let’s have a look at the code. 2.1 PL/SQL Blocks
The structure of a PL/SQL block is normally as follows:
DECLARE
<variable and constant declarations to here>
BEGIN
<your PL/SQL code goes here>
EXCEPTION
<exception handling code goes here, this section is
optional>
END;
/
Within the DECLARE section you define any variables and constants that are going to be used within
your PL/SQL code block. They can use any type in Oracle, e.g. NUMBER, VARCHAR2, DATE – the only
difference is that it is not a requirement that you specify a size for data types such as VARCHAR2,
although it is still good practice.
Examples of valid variable declarations are: 5 DECLARE
my_age
my_name
my_sex NUMBER;
VARCHAR := 'Fred'; /* this is a variable with
a default value */
CONSTANT CHAR := 'M'; / this is a constant,
i.e. the value can NEVER change */ You can also declare variables that have the same type as an attribute within one of your tables.
This is very useful and is used in the example block that we have already run.
To do this, for example, you would specify:
my_surname TENANT.T_SURNAME%TYPE;
This means that the my_surname variable takes the same type as the T_SURNAME column within
the TENANT table. The reason for doing this will become clearer when we explain the INTO
statement in the SQL query in the PL/SQL code block.
The next block starts with the keyword BEGIN. In this section we include the PL/SQL code to be
executed by Oracle. This can include SQL data manipulation statements, i.e. SELECT, INSERT,
UPDATE and DELETE statements.
The example you have run includes the following code:
BEGIN
SELECT t_surname
INTO my_surname
FROM tenant
WHERE tenant_no = '&enter_tenant_no';
DBMS_OUTPUT.PUT_LINE('Tenant''s surname: '||my_surname);
END;
It includes an SQL SELECT statement to retrieve tenant surname from the tenant table, followed by a
PL/SQL statement which prints the tenant’s surname. The only difference to the SQL you have seen
previously is the inclusion of the INTO part of the SELECT statement. This basically ensures that the
surname attribute value retrieved from the tenant table is stored into a variable my_surname, which
was previously declared within the DECLARE section of the PL/SQL block.
This variable is then used in the DBMS_OUTPUT.PUT_LINE statement, which simply outputs the
string in brackets to the output window in SQL Developer. The string is simply a concatenation of
the string Tenant’s surname, with the surname in the my_surname variable.
The PL/SQL block of code then ends with the keyword END; followed by a forward slash to signify to
Oracle the end of the PL/SQL block.
6 Now try a tenant number which does not exist, e.g. 1001. You should get an error as follows: The problem is that the code runs the query, and the query will return no data as no rows in the
table contain that tenant number. So, the query fails and the subsequent PUT_LINE command will
fail as there is no surname to print.
To resolve this problem we can include an exception handler. 2.2 Exception Handling
Alter the code that you had previously entered so that it matches the code below.
DECLARE
my_surname TENANT.T_SURNAME%TYPE;
BEGIN
SELECT t_surname
INTO my_surname
FROM tenant
WHERE tenant_no = '&enter_tenant_no';
DBMS_OUTPUT.PUT_LINE('Tenant''s surname: '||my_surname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No tenant with that number');
END;
/ Now run again, enter an incorrect tenant number (e.g. 1001) and you should get the following result:
7 Oracle now prints the error message.
This is an example of a pre-defined exception. There are other pre-defined exceptions, e.g.
TOO_MANY_ROWS and OTHERS. You should try to find out what these exceptions do by reading the
Oracle documentation.
The following is an example of a user-defined exception, run it and see what happens.
DECLARE
too_old EXCEPTION;
BEGIN
IF &what_is_your_age > 35 THEN
RAISE too_old;
END IF;
EXCEPTION
WHEN too_old THEN
DBMS_OUTPUT.PUT_LINE('You are past it!');
END;
/
8 Notice the syntax of the IF statement, whereby we indicate the end of the IF statement by the
keywords END IF. 2.3 Exercises
1. Write a PL/SQL block of code to prompt the user to enter a lease number from the lease
table, and print, for example for lease 6001, the message:
Property 1001 was leased from 12-JUN-95 to 07-JUN-97
2. Adapt the PL/SQL block of code above to print the message ‘That lease does not
exist’ if an invalid lease number is entered
3. Adapt your PL/SQL block of code to also display the forename and surname of the tenant
that takes the lease.
4. Adapt your block of code so that it asks for a property number rather than a lease number.
Test that this works by running the code for 1001. Now run the PL/SQL block of code for
property number 1005. You should see that this raises an exception, because more than
one lease exists for property number 1005.
Adapt your code by using a
WHEN_TOO_MANY_ROWS exception to print an error message if there is more than one
lease for the property. 9 3. Stored Procedures in Oracle
The example we have seen so far allows us to write code which will run a collection of PL/SQL
commands. However we need to keep running the entire block of code to execute it, and if we close
the SQL Developer session and re-open later, the block of code we have created is not stored in the
database.
Stored procedures allow us to write blocks of code and assign these a name, similar to the concept
of functions in C#. The only difference is that we no longer need the DECLARE keyword at the top of
the block of code, and instead replace it with a PROCEDURE definition. The example below is the
same as the example we used previously, but now defined as a stored procedure.
CREATE OR REPLACE PROCEDURE print_tenant_surname (my_tenant_no
TENANT.TENANT_NO%TYPE) AS
my_surname TENANT.T_SURNAME%TYPE;
BEGIN
SELECT t_surname
INTO my_surname
FROM tenant
WHERE tenant_no = my_tenant_no;
DBMS_OUTPUT.PUT_LINE('Tenant''s surname: '||my_surname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No tenant with that number');
END;
/ The only other difference is that now we can pass values into the procedure. So, instead of the SQL
code having to prompt the user to enter a tenant number, we can pass this as a value to the
procedure.
If you have correctly entered the code above, when you press the Run Script button you should see
the following message: If you see the following: 10 This means that there were errors in the code you entered. To see the errors you type and run the
command:
SHOW ERRORS
Fix the errors, and then run the code again until there are no remaining errors.
Once there are no errors, to run the procedure we then use the execute command. Type, for
example, the following command to retrieve the surname for tenant 3003.
EXECUTE print_tenant_surname(3003);
PL/SQL blocks of code can include selection as well as looping statements. You have already seen an
example of an IF statement in the previous example of a user-defined exception. 3.1 IF statements
Consider the following example where we want to test the monthly rent and print a message
depending on its value
CREATE OR REPLACE PROCEDURE test_monthly_rent
(my_prop_no PROP_FOR_RENT.PROPERTY_NO%TYPE) AS
my_monthly_rent PROP_FOR_RENT.PROP_RENT_PM%TYPE;
BEGIN
SELECT prop_rent_pm
INTO my_monthly_rent
FROM prop_for_rent
WHERE property_no = my_prop_no;
IF my_monthly_rent < 500 THEN
DBMS_OUTPUT.PUT_LINE('Rent is less than £500 per month');
ELSIF my_monthly_rent <= 600 THEN
DBMS_OUTPUT.PUT_LINE('Rent is between £500 and £600 per month');
ELSE
DBMS_OUTPUT.PUT_LINE('Rent is more than £600 per month');
END IF;
END;
/ Run your procedure for properties 1001, 1002 and 1003 and note the result.
Inspect the code above carefully and ensure you understand it, ensure you pay particular attention
to the ELSIF statement. 3.2 Exercises
1. Add an exception statement to the above code to deal with an invalid property number
being entered.
2. Write a stored procedure to calculate the annual rent (including the poll tax amount which
you can assume is already an annual amount) for any property.
11 We will look at LOOP statements when we look at the use of Cursors. 3.3 Insert, Update and Delete Statements
As well as including SELECT statements in your PL/SQL code, you can also include INSERT, UPDATE
and DELETE statements. If, for example, we wanted to update all monthly rents by £50 per month,
we could write the following stored procedure.
CREATE OR REPLACE PROCEDURE increase_monthly_rent(monthly_increment
NUMBER) AS
BEGIN
UPDATE LEASE
SET rent_pm = rent_pm + monthly_increment;
END;
/ 12 4. Using Cursors within PL/SQL
In the PL/SQL code which we have written so far, we have expected out SQL query to return one row
as the result which we then process, giving an error if our query returns no rows or if it returns
multiple row. However, there are many instances where we may wish our procedure to process
multiple rows of data. Cursors allow us to execute a statement and then process the results of that
statement on a line by line basis.
CREATE OR REPLACE show_tenants AS
BEGIN
FOR tenant_cursor IN
(SELECT t_surname, t_forenames FROM tenant)
LOOP
DBMS_OUTPUT.PUT_LINE(tenant_cursor.t_forenames||
' '||tenant_cursor.t_surname);
END LOOP;
END;
/ 4.1 Loops
The example above shows how to use a cursor to display the forename and surname of all tenants.
Notice that we have introduced the FOR LOOP construct for this example. This is an example of a
simple looping operation, i.e. the code within the LOOP … END LOOP block will iterate for each result
returned by the cursor, i.e. once for each row within the tenant table.
This is one of the types of LOOP that PL/SQL supports. You should look at the Oracle documentation
to see the other types of LOOP that are supported, such as basic LOOPS, WHILE statements, and how
to use counters in FOR loops. 4.2 Exercises
1. Write a stored procedure to use a cursor, which for each property that is available for rent, it
displays the street, area, town and postcode as one column (i.e. using the concatenate operator
that you used in the SQL booklet).
2. Adapt your cursor from question 1 above so that it also prints the owner’s forename and
surname, again as one column of data but on a separate line from the address details. For
example, the output should be as follows: 13 14 5. Stored Functions in Oracle
Stored procedures are excellent for defining complex operations on data. However, we want
sometimes to be able to produce a PL/SQL function which we can then directly use inside an SQL
query.
As an example, the following stored procedure calculates the length in months of a rental for a
single property being leased:
CREATE
OR
REPLACE
PROCEDURE
show_lease_length(my_lease_no
LEASE.LEASE_NO%TYPE) AS
num_months NUMBER;
BEGIN
SELECT round(months_between(enddate, stdate))
INTO num_months
FROM lease
WHERE lease_no = my_lease_no;
DBMS_OUTPUT.PUT_LINE('Lease ' || my_lease_no || ' lasted ' ||
num_months || ' months ');
END;
/ The problem with this procedure is firstly that we need to execute it once for each month. We can
fix this problem within a stored procedure by use of Cursors and Loops, which we will see in the next
session, however ideally we want to use it within an SQL query.
To illustrate, here is an example function.
CREATE
OR
REPLACE
FUNCTION
calculate_lease_length(my_lease_no
LEASE.LEASE_NO%TYPE) RETURN NUMBER AS
num_months NUMBER;
BEGIN
SELECT round(months_between(enddate, stdate))
INTO num_months
FROM lease
WHERE lease_no = my_lease_no;
RETURN num_months;
END;
/ The syntax is very similar to the PROCEDURE apart from a few small differences.
1. Firstly a function needs to return a value, in this case we are calculating the number of
months which is a NUMBER so we specify the return type as NUMBER
2. We no longer need the DBMS_OUTPUT.PUT_LINE statement as we will be using this function
within an SQL statement
3. We have a new statement at the end, RETURN, which specifies the value to return to SQL.
15 To run this function, we could write an SQL query as follows:
SELECT lease_no, calculate_lease_length(lease_no) FROM lease;
You should see the following output: 5.1 Exercises
1. Adapt the stored procedure from exercise 2.3 question 1 to use a function rather than a
stored procedure, taking the lease number as a parameter to the function.
2. Create a stored function to calculate the annual rent for any property.
3. Adapt your function from question 2 above so that a percentage can be supplied as a whole
number (i.e. 10 for 10%) to calculate the annual rent increased by the given percentage. 16 6. Developing Triggers in PL/SQL
Triggers allow us to add complex data validation or integrity checks to our database. Although we
can normally add data validation and integrity checks as constraints on tables, this constraint system
normally only allows very simple checks to be performed. We use triggers when we want to manage
more complex constraints on our data.
This first example defines a trigger which tests, when inserting a new row of data, that the end date
cannot be before the start date.
CREATE OR REPLACE TRIGGER check_lease
BEFORE INSERT ON lease
FOR EACH ROW
DECLARE
invalid_entry EXCEPTION;
BEGIN
IF :new.enddate < :new.stdate THEN
RAISE invalid_entry;
END IF...
View
Full Document
- Spring '19
- Jan Smith
- MYSQL , Database trigger
