Module 8: Functions, Procedures, and Triggers

Opening a Cursor

Prior to opening a cursor, there are no rows available to be fetched. Every cursor has a DONE value which is set to false until the cursor have no more rows to fetch. When the value of DONE is set to true, it is an indication that the cursor has finished sifting through rows. - The OPEN command is written as follows: OPEN CUSTGROUP; - Once the cursor is opened, the associated query executes. Rows are fetched.

PL/SQL

Procedural Language/Structured Query Language, is a procedural language well-integrated with SQL, which was developed by Oracle. PL/SQL, like MySQL, and others, features Programming constructs such as conditions and loops and other programming languages structures.

Error Handling in Procedures

Procedures must be able to handle conditions that can arise when accessing the database. For example, what if you pass an invalid argument into a procedure? DELIMITER // CREATE PROCEDURE GET_NAME (IN CUST_ID_I CHAR(3)) BEGIN DECLARE FULL_NAME CHAR(40); DECLARE EXIT HANDLER FOR NOT FOUND SELECT CONCAT ('No customer with this ID was found: ', CUST_ID_I) AS MESSAGE; SELECT CONCAT (FIRST_NAME, ' ', LAST_NAME) INTO FULL_NAME FROM CUSTOMER WHERE CUST_ID = CUST_ID_I; SELECT FULL_NAME END // - In the above procedure, if someone calls the procedure and passes an invalid CUST_ID_I argument, then the line following DECLARE EXIT HANDLER... will execute. - If the DECLARE EXIT HANDLER line was not in the procedure, and someone passed an invalid argument, the NOT FOUND condition is raised. The DECLARE EXIT HANDLER line handles the NOT FOUND condition.

ROUND Function

ROUND function rounds values to a specified number of decimal places. The ROUND function has two arguments, the first argument is the value to be rounded, while the second argument indicates the number of decimal places to which to round the result. For example, ROUND(PRICE, 0) rounds the values in the PRICE column to zero decimal places (A whole number). So if PRICE was 24.95, the answer would be 25, and if PRICE was 24.25, the answer would be 24.

Cursor

A cursor is a pointer to a row in the collection of rows retrieved by an SQL command. The cursor advances one row at a time to provide sequential, one-record-at-a-time access to the retrieved rows so MySQL can process the rows. By using a cursor, MySQL can process the set of retrieved rows as though they were records in a sequential file. - The first step in using a cursor is to declare the cursor and describe the associated query in the declaration section of the procedure: DECLARE CUSTGROUP CURSOR FOR SELECT CUST_ID, CONCAT ( FIRST_NAME, ' ', LAST_NAME) AS CUST_NAME FROM CUSTOMER WHERE REP_ID = REP_ID_I; - In the above command, assuming the cursor is named CUSTGROUP, the command declares the cursor and associates it with the query that follows it. The declaration of the cursor and associating it with a query does not automatically execute the query. - Using a cursor in a procedure involves three commands: OPEN, FETCH, and CLOSE. - OPEN command opens the cursor and causes the query to be executed, making the results available to the procedure. - Executing a FETCH command advances the cursor to the next row in the set of rows retrieved by the query and places the contents of the row in the indicated variables. - Finally, the CLOSE command closes a cursor and deactivates it. Data retrieved by the execution of the query is no longer available. The cursor could be opened again later and processing could begin again.

Why Use Cursors?

Beyond coding the query inside of the cursor definition, the programmer does not need to worry about the mechanics of obtaining the necessary data, or placing it in the right order, because this happens automatically once the cursor is opened. To the programmer, it seems as if the sequential file already exists that contains the correct data, sorted in the right order. This assumption leads to three main advantages: - The coding in the procedure is greatly simplified. - In a program or procedure using embedded SQL, the optimizer determines the best way to access the data. The programmer isn't concerned with the best way to retrieve the data. In addition, when an underlying structure changes (for example, an additional index is created), the optimizer determines the best way to execute the query with the new structure. The program or procedure does not have to change at all. - When database structure changes in such a way that the necessary information is still obtainable using a different query, the only change required in the program or procedure is the cursor definition. The procedural code is not affected.

Transact-SQL

T-SQL is an extension of SQL. T-SQL is the procedural language that SQL Server uses. You can perform tasks, such as retrieving a single row, inserting new rows, and retrieving multiple rows, using T-SQL in SQL Server. Although the language syntax is slightly different in T-SQL when compared to MySQL and PL/SQL, the functionality and the results are the same.

Adding Days

To add a specific number of days to a date, you do not need a function. You can add the number of days directly to the invoice date as in the following: SELECT INVOICE_DATE + 7 AS NEXT_WEEK FROM INVOICES; - In the above query, the results will be the dates with 7 days added to them. - Note that days can also be subtracted from dates in the same manner.

LOWER Function

To display a value in lowercase letters, you would use the LOWER function. For example, the function LOWER(LAST_NAME) displays the last name 'Mullis' as 'mullis'. You can use functions in the WHERE clause as well. For example, the condition LOWER(LAST_NAME) = 'mullis' would be true for names like mullis, MULLIS, and MuLlIs, because the result of applying the Lower function to any of these values would result in the value mullis.

Fetching Rows From a Cursor

To fetch (get) the next row from a cursor, use the FETCH command. The FETCH command is written as follows: FETCH CUSTGROUP INTO CUST_ID_I, CUST_NAME_I; - Note that the INTO clause is associated with the FETCH command itself and not with the query used in the cursor definition. The execution of this query could produce multiple rows. The execution of the FETCH command produces only a single row, so it is appropriate that the FETCH command causes data to be placed in the indicated variables.

Cursor Procedure

A cursor procedure is just like any other procedure, except that it uses a cursor within it. DELIMITER // CREATE PROCEDURE DISP_REP_CUST (IN REP_ID_I CHAR(2)) BEGIN DECLARE DONE INT DEFAULT FALSE; DECLARE CUST_ID_I CHAR(3); DECLARE CUST_NAME_I CHAR(41); DECLARE CUSTGROUP CURSOR FOR SELECT CUST_ID, CONCAT (FIRST_NAME, ' ', LAST_NAME) AS CUST_NAME FROM CUSTOMER WHERE REP_ID = REP_ID_I; DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE; OPEN CUSTGROUP; READ_LOOP: LOOP FETCH CUSTGROUP INTO CUST_ID_I, CUST_NAME_I; IF DONE THEN LEAVE READ_LOOP; END IF; SELECT CUST_ID_I, CUST_NAME_I; END LOOP; CLOSE CUSTGROUP; END // DELIMITER ; - In the line that contains 'DECLARE CUSTGROUP CURSOR FOR' the CURSOR is declared and its associated query is defined. - After the block of code that defines the cursor, a continue handler is declared which handles the error that raises NOT FOUND, should no customers be found with the sales rep ID that is passed. - The loop portion of the procedure displays the customer ID, and the full name of every customer associated with the rep ID that was passed. The loop only stops once the cursor has no more rows to fetch. - Notice that the cursor is closed at the end of the procedure.

Procedural Language

A procedural language is one in which you must give the computer the systematic process for accomplishing a task. MySQL procedural language was introduced in version 5 as an extension of SQL. MySQL procedural language allows developers to embed SQL statements to perform complex tasks that could no be done by SQL alone. These tasks can be saved within the database as stored procedures to be executed at any time.

Stored Procedure

A stored query, that you anticipate to be running often, and to avoid retyping you store it for quick access. The stored procedure is placed on the server.

Trigger

A trigger is a procedure that is executed automatically in response to an associated database operation, such as an INSERT, UPDATE, or DELETE command. - Unlike a stored procedure, which is executed in response to a user request, a trigger is executed in response to a command that causes the associated database operation to occur. - Assume that inside the ITEM table there is a new column named ON_ORDER. This column represents the number of units of an item currently on order. For example, if there are two separate invoice lines for an item and the number ordered on one invoice line is 3 and the number ordered on the other invoice line is 2, the ON_ORDER value for that item will be 5. Adding, changing, or deleting invoice lines, inside the INVOICE_LINE table, affects the value in the ON_ORDER column for the item, inside the ITEM table. To ensure that the ON_ORDER column is updated appropriately, you can use a trigger. - The following trigger would be executed when a user adds an invoice line. The trigger updates the ON_ORDER value for the corresponding item to reflect the invoice line: DELIMITER // CREATE TRIGGER ADD_INVOICE_LINE AFTER INSERT ON INVOICE_LINE FOR EACH ROW BEGIN UPDATE ITEM SET ON_ORDER = ON_ORDER + NEW.QUANTITY WHERE ITEM_ID = NEW.ITEM_ID; END // DELIMITER ; - The above trigger is executed when a user adds an invoice line. The trigger updates the ON_ORDER value for the corresponding item to reflect the invoice line. For example, if the value in the ON_ORDER column for item AD72 is 3 and the user adds an invoice line on which the item number is AD72 and the number of units ordered is 2, then 5 units of item AD72 will be on order. - The NEW qualifier refers to the row that is added to the INVOICE_LINE table. If an invoice line is added on which the item number is AD72 and the number ordered is 2, for example, NEW.ITEM_ID will be AD72 and NEW.QUANTITY will be 2. DELIMITER // CREATE TRIGGER UPDATE_INVOICE_LINE AFTER UPDATE ON INVOICE_LINE FOR EACH ROW BEGIN UPDATE ITEM SET ON_ORDER = ON_ORDER + NEW.QUANTITY - OLD.QUANTITY WHERE ITEM_ID = NEW.ITEM_ID; END // DELIMITER ; - The above trigger is similar to ADD_INVOICE_LINE, except that this trigger, UPDATE_INVOICE_LINE, is executed when a user updates the INVOICE_LINE table, where as ADD_INVOICE_LINE executes after a user adds a row to the INVOICE_LINE table. - The computation to update the ON_ORDER column includes both NEW.QUANTITY and OLD.QUANTITY. The NEW qualifier refers to the new value, the OLD qualifier refers to the value before the update takes place. So, if an update changes the value for quantity from 2 to 3, OLD.QUANTITY is 2 and NEW.QUANTITY is 3. DELIMITER // CREATE TRIGGER DELETE_INVOICE_LINE AFTER INSERT ON INVOICE_LINE FOR EACH ROW BEGIN UPDATE ITEM SET ON_ORDER = ON_ORDER - OLD.QUANTITY WHERE ITEM_ID = NEW.ITEM_ID; END // DELIMITER ; - The above trigger is similar to the other two triggers. When an invoice line is deleted, the ON_ORDER value for the corresponding item is updated by subtracting OLD.QUANTITY from the current ON_ORDER value. (In a delete operation, there is no NEW.QUANTITY.)

Complex Cursor Procedure

DELIMITER // CREATE PROCEDURE DISP_ITEM_INVOICES (IN ITEM_ID_I CHAR(4)) BEGIN DECLARE DONE INT DEFAULT FALSE; DECLARE INVOICE_NUM_V CHAR(5); DECLARE INVOICE_DATE_V DATE; DECLARE CUST_ID_V CHAR(3); DECLARE REP_ID_V CHAR(2); DECLARE REP_LAST_V CHAR(2); DECLARE REP_FIRST_V CHAR(20); DECLARE CUSTGROUP CURSOR FOR SELECT INVOICES.INVOICE_NUM, INVOICES.INVOICE_DATE, INVOICES.CUST_ID, CUSTOMER.REP_ID, SALES_REP.FIRST_NAME, SALES_REP.LAST_NAME FROM INVOICES, INVOICE_LINE, CUSTOMER, SALES_REP WHERE INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM AND INVOICES.CUST_ID = CUSTOMER.CUST_ID AND CUSTOMER.REP_ID = SALES_REP.REP_ID AND INVOICE_LINE.ITEM_ID = ITEM_ID_I; DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE; OPEN CUSTGROUP; READ LOOP: LOOP FETCH CUSTGROUP INTO INVOICE_NUM_V, INVOICE_DATE_V, CUST_ID_V, REP_ID_V, REP_LAST_V, REP_FIRST_V; IF DONE THEN LEAVE READ LOOP; END IF; SELECT INVOICE_NUM_V, INVOICE_DATE_V, CUST_ID_V, REP_ID_V, REP_LAST_V, REP_FIRST_V; END LOOP; CLOSE CUSTGROUP; END // DELIMITER ; - The above procedure takes a single argument of item ID value and displays the invoice number, invoice date, customer ID, representative ID, representative first name, and representative last name of every single row in INVOICE_LINE table that matches the value of item ID passed to the procedure. - The select statement associated with the cursor inside of this procedure joins four tables together, to grab all of the data in the select statement of the cursor query.

CREATE PROCEDURE Command

DELIMITER // CREATE PROCEDURE GET_NAME (IN CUST_ID_I CHAR(3)) BEGIN DECLARE FULL_NAME CHAR(40); SELECT CONCAT (FIRST_NAME, ' ', LAST_NAME) INTO FULL_NAME FROM CUSTOMER WHERE CUST_ID = CUST_ID_I; SELECT FULL_NAME END // - This CREATE PROCEDURE command contains a single argument, CUST_ID. The word IN before the argument indicates that the argument is used for input. That is, the user must pass a value for CUST_ID to use the procedure. - Alternative possibilities to IN are OUT, which indicates that the procedure sets a value for the argument, and INOUT, which indicates that the user enters a value that the procedure can later change. - The procedural code, which contains the commands (between BEGIN and END) that specify the procedures function, appears between the BEGIN and END commands. - The DECLARE statement within the commands defined a variable to store values to be used at a later stage. In addition, part of declaration of a variable you must assign a data type, just as you do in the CREATE TABLE command. Data types are the same as in the CREATE TABLE command (INT, SMALLINT, VAR, VARCHAR.. etc). - The SQL command uses the INTO clause to assign the result of the concatenated FIRST_NAME, ' ', and LAST_NAME into the previously declared variable of FULL_NAME. - The final statement of SELECT FULL_NAME; is used to display the stored value of the variable FULL_NAME. • To execute a procedure from the SQL commands page, type the word CALL, followed by the name of the procedure including the desired value for the argument in parenthesis, following by a semicolon: CALL GET_NAME('125');

Error 1172

Error 1172 is an error that is raise when too many rows exist for the result. So if your procedure is trying to narrow down a single row, but based on the statements within the procedure, too many rows are produced, error 1172 is raised: DELIMITER // CREATE PROCEDURE GET_REP_NAME(IN RATE_I DECIMAL (4, 2)) BEGIN DECLARE FULL_NAME VARCHAR(40); DECLARE EXIT HANDLER FOR 1172 SELECT CONCAT('There is more than one REP with RATE: ', RATE_I) AS MESSAGE; SELECT CONCAT (FIRST_NAME, ' ', LAST_NAME) INTO FULL_NAME FROM SALES_REP WHERE RATE = RATE_I; SELECT FULL_NAME; END // - In the above procedure, when the user enters rate 0.04, the procedure raises error 1172 because Susan, Donna, and Daniel all have this same commission rate—the procedure finds three rows instead of one.

Client / Server Databases

In a client/server, the database is stored on a computer called the server and users can access the database through clients. A client is a computer that is connected to a network and has access through the server to the database.

Nonprocedural Language

SQL is a powerful nonprocedural langauge in which you submit requests to the computer using single, english like, commands.

CONCAT Function

The CONCAT function will concatenate (Merge) two or more character columns into a single expression. To use it, you would do the following: SELECT CONCAT (FIRST_NAME, ' ', LAST_NAME) AS FULL_NAME FROM CUSTOMER; - The above command merges together the character values of FIRST_NAME, ' ', and LAST_NAME, and stores the result in a column called FULL_NAME. - So, if FIRST_NAME is 'Brandon' and LAST_NAME is 'Mullis', then the result would be: 'Brandon Mullis' - When the first name doesn't include enough characters to fill the width of the column (as determined by the number of characters specified in the CREATE TABLE command), SQL inserts the extra spaces. For example, when the FIRST_NAME column is 12 characters in length, the first name is Joey, and the last name is Smith, the concatenated expression FIRST_NAME, and LAST_NAME is displayed as Joey, followed by eight spaces, and then Smith. - To remove the extra spaces following the first name value, you use the RTRIM (right trim) function. When you apply this function to the value in a column, SQL displays the original value and removes any spaces inserted at the end of the value. - Here is RTRIM being used in combination with CONCAT: CONCAT ( RTRIM(FIRST_NAME), ' ', RTRIM(LAST_NAME) )

CURDATE Function

The CURDATE function is used to obtain today's date. SELECT INVOICE_NUM, CURDATE() AS TODAYS_DATE, INVOICE_DATE FROM INVOICES; - The above command will get today's date and store it in the column as "TODAYS_DATE".

FLOOR Function

The FLOOR function rounds values down to the nearest whole number. FLOOR only requires one argument as it's only rounding down to the nearest whole number. FLOOR(24.99) would result in 24.

UPPER Function

The UPPER function displays a value in uppercase letters; for example, the function UPPER(LAST_NAME) displays the last name 'Mullis' as 'MULLIS'. You can use functions in the WHERE clause as well. For example, the condition UPPER(LAST_NAME) = 'MULLIS' would be true for names like mullis, MULLIS, and MuLlIs, because the result of applying the UPPER function to any of these values would result in the value MULLIS.

Closing a Cursor

The close command is written as follows: CLOSE CUSTGROUP; The cursor is closed and deactivated, however, once closed, the cursor can be reopened and used again, assuming there are more rows to fetch.

EXIT and CONTINUE Error Handling

There are two options for error handling: EXIT and CONTINUE. EXIT stops the execution of the code enclosed between BEGIN and END, while CONTINUE keeps executing the code proceeding the statement that caused the error.

CONVERT Function

This function converts a value to a specific format. So, if you wanted to put raw numbers in a date format, you would do the following: CONVERT ( DATA, DATE) The first argument is the numbers that you want to convert, and the second argument is the format that you want them in.

DATE_ADD Function

This function is used to add a specific number of days, months, or years to a date. DATE_ADD function requires two arguments: the first argument is the date to which you want to add a specific interval to, and the second argument is the interval value and specific component (days, months, or years). DATE_ADD( INVOICE_DATE, INTERVAL 1 MONTH ) - Note, negative intervals can be added to dates, in which case the date would decrease by whatever the interval is.

RTRIM Function

This function removes the extra spaces surrounding character values. So, if the character value 'Brandon ' , which has several empty spaces at the end of the value, is used with RTRIM, then the resulting value would be 'Brandon', with the empty spaces removed: RTRIM( FIRST_NAME )

DATEDIFF Function

This function returns the amount of days between the two dates passed into the function. So if you wanted to find the amount of days between the value INVOICE_DATE and today's date, you would do the following: DATEDIFF ( INVOICE_DATE, CURDATE() ) AS DAYS_BETWEEN

Deleting Data in Procedure

You can delete values inside of a procedure. Deleting procedures work as any normal procedure, but instead of SELECT or UPDATE statements there is a DELETE statement. DELIMITER // CREATE PROCEDURE DEL_INVOICE( IN INVOICE_NUM_I CHAR(5) ) BEGIN DELETE FROM INVOICE_LINES WHERE INVOICE_NUM = INVOICE_NUM_I; DELETE FROM INVOICES WHERE INVOICE_NUM = INVOICE_NUM_I; END // DELIMITER ; - The above procedure takes a single argument which is the invoice number for the invoice that is being deleted. The procedure includes two statements, one to delete the row which matches the invoice number passed in both the INVOICES and INVOICE_LINES tables. - If you attempt to delete the invoice in the INVOICES table first, referential integrity will prevent the deletion because matching rows would still exist in the INVOICE_LINES table, so it is a good idea to delete the orders from the INVOICE_LINES table first.

Update Procedure

You can update values in a procedure. Update procedures aren't any more special than regular procedures, they just use the UPDATE command instead of the SELECT command DELIMITER // CREATE PROCEDURE CHANGE_NAME(IN CUST_ID_I CHAR(3), IN NEW_NAME_I VARCHAR(20)) BEGIN DECLARE EXIT HANDLER FOR NOT FOUND SELECT CONCAT ('No customer with this ID found: ', CUST_ID_I) AS MESSAGE; UPDATE CUSTOMER SET LAST_NAME = NEW_NAME_I WHERE CUST_ID = CUST_ID_I; END // DELIMITER ; - The above procedure takes two arguments, ID of the customer whose name is being changed, and the new name that will be assigned to the customer. There is an exit handler stated, which displays a message if the ID passed is not found. The update statement assigns the new name to the customer's last name, should the ID passed match with any of the IDs in the table.