Database Advance Topic
In this chapter you will be introduced to some advance topics in SQL that goes beyond basic database transactions. Even if this section only includes an overview of cursors, triggers and errors, such knowledge could possibly help you extend the features of your SQL implementations.
Cursors
Generally, SQL commands manipulate database objects using set-based operations. This means that transactions are performed on a group or block of data. A cursor, on the other hand, processes data from a table one row at a time. It is created using a compound a statement and destroyed upon exit. The standard syntax for declaring a cursor is (which may differ for every implementation):
DECLARE CURSORCURSOR_NAME IS {SELECT_STATEMENT}
You can perform operations on a cursor only after it has been declared or defined.
• Open a Cursor
Once declared, you perform an OPEN operation to access the cursor and then execute the specified SELECT statement. The results of the SELECT query will be saved in a certain area in the memory. The standard syntax for opening a cursor is:
OPENCURSOR_NAME;
• Fetch Data from a Cursor
The FETCH statement is performed if you want to retrieve the query results or the data from the cursor. The standard syntax for fetching data is:
FETCH NEXT FROMCURSOR_NAME[INTOFETCH_LIST]
In SQL programming, the optional statement inside the square brackets will let you assign the data retrieved into a certain variable.
• Close a Cursor
There is a corresponding CLOSE statement to be executed when you open a particular cursor. Once the cursor is closed, all the names and resources used will be deallocated. Thus, the cursor is no longer available for the program to use. The standard syntax for closing a cursor is:
CLOSECURSOR_NAME
Triggers
There are instances when you want certain SQL operations or transactions to occur after performing some specific actions. This scenario describes an SQL statement that triggers another SQL statement to take place. Essentially, a trigger is an SQL procedure that is compiled in the database that execute certain transactions based on other transactions that have previously occurred. Such triggers can be performed before or after the execution of DML statements (INSERT, DELETE and UPDATE). In addition, triggers can validate data integrity, maintain data consistency, undo transactions, log operations, modify and read data values in different databases.
• Create a Trigger
The standard syntax for creating a trigger is:
CREATE TRIGGERTRIGGER_NAME TRIGGER_ACTION_TIMETRIGGER_EVENT ONTABLE_NAME [REFERENCINGOLD_OR_NEW_VALUE_ALIAS_LIST] TRIGGERED_ACTION
TRIGGER_NAME- the unique identifying name for this object TRIGGER_ACTION_TIMETRIGGER_EVENT- the specified time that the set of triggered actions will occur (whether before or after the triggering event). TABLE_NAME– the table for which the DML statements have been specified TRIGGERED_ACTION– specifies the actions to be performed once an event is triggered
Once a trigger has been created, it cannot be altered anymore. You can just either re-create or replace it. How a trigger works depends what conditions you specify – whether it will fire at once when a DML statement is performed or it will fire multiple times for every table row affected by the DML statement. You can also include a threshold value or a Boolean condition, that when such condition is met will trigger a course of action.
• Drop a Trigger
The basic syntax for dropping a trigger is the same as dropping a table or a view:
DROP TRIGGERTRIGGER_NAME;
Errors
An error-free design or implementation is one of the ultimate goals in any programming language. You can commit errors by simply not following naming conventions, improperly writing the programming codes (syntax or typo errors like a missing apostrophe or parenthesis) or even when the data entered does not match the data type defined.
To make things easier, SQL has devised a way to return error information so that programmers will be aware of what is going on and be able to undertake the appropriate actions to correct the situation. Some of these error-handling mechanisms are the status parameter SQLSTATE and the WHENEVER clause.
• SQLSTATE
The status parameter or host variable SQLSTATE is an error-handling tool that includes a wide selection of anomalous condition. It is a string that consists of five characters (uppercase letters from A to Z and numerals from 0 to 9), where the first two characters refer to the class code while the next three is the subclass code. The class code identifies the status after an SQL statement has been completed – whether it is successful or not (if not successful, then one of the major types of error conditions are returned). Supplementary information about the execution of the SQL statement is also indicated in the subclass code.
The SQLSTATE is updated after every operation. If the value is ‘00000’ (five zeroes), it means that the execution was successful and you can proceed to the next operation. If it contains a five-character string other than ‘00000’, then you have to check your programming lines to rectify the error committed. There are numerous ways on how to handle a certain SQL error, depending on the class code and subclass code specified in the SQLSTATE.
• WHENEVER Clause
The WHENEVER clause error-handling mechanism focuses on execution exceptions. With this, an error is acknowledged and gives the programmer the option to correct it. This is better than not being able to do something if an error occurs. If you cannot rectify or reverse the error that was committed, then you can just gracefully terminate the application program.
The WHENEVER clause is written before the executable SQL code, specifically in the SQL declaration section. The basic syntax is:
WHENEVERCONDITION ACTION;
CONDITION– value can either be SQLERROR (returns TRUE if SQLSTATE class code is other than 00, 01 or 02) or NOT FOUND (returns TRUE if SQLSTATE is 02000) ACTION– value can either be CONTINUE (execution of the program is continued normally) or GOTO address (execution of a designated program address)
In this chapter you have learnt the primary role of cursors, how triggers work and the importance of handling errors in SQL programming. Learning these advance topics is one step closer in maximizing the potentials of your SQL implementations.
