CETM16 StoredProceduresTutorialV1.pdf - Oracle PL\/SQL A Guide to Stored Procedures Stored Functions Cursors and Triggers Contents 1 Introduction 2 2

CETM16 StoredProceduresTutorialV1.pdf - Oracle PL/SQL A...

This preview shows page 1 out of 23 pages.

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

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture