Xem mẫu
52 CHAPTER 1: DATABASE DESIGN
::= []
[]
::=
ALTER DOMAIN
::=
|
| |
It is important to note that a DOMAIN has to be defined with a basic data type and not with other DOMAINs. Once declared, a DOMAIN can be used in place of a data type declaration on a column.
The CHECK() clause is where you can put the code for validating data items with check digits, ranges, lists, and other conditions. Since the DOMAIN is in one place, you can make a good argument for writing the following:
CREATE DOMAIN StateCode AS CHAR(2) DEFAULT `??`
CONSTRAINT valid_state_code
CHECK (VALUE IN (`AL`, `AK`, `AZ`, ...));
instead of:
CREATE DOMAIN StateCode AS CHAR(2) DEFAULT ‘??’
CONSTRAINT valid_state_code
CHECK (VALUE IN (SELECT state FROM StateCodeTable));
The second method would have been better if you did not have a DOMAIN and had to replicate the CHECK() clause in multiple tables in the database. This would collect the values and their changes in one place instead of many.
1.4.4 CREATE TRIGGER Statement
A TRIGGER is a feature in many versions of SQL that will execute a block of procedural code against the database when a table event occurs. This
1.4 Other Schema Objects 53
is not part of Standard SQL, but has been proposed in the SQL3 working document. You can think of a TRIGGER as a generalization of the referential actions.
The procedural code is usually written in a proprietary language, but some products let you attach programs in standard procedural languages. A TRIGGER could be used to automatically handle discontinued merchandise, for example, by creating a credit slip in place of the original order item data.
There is a Standard syntax for TRIGGERs, based on the SQL/PSM Standard, but it is not widely implemented. You should look at what your particular vendor has given you if you want to work with TRIGGERs.
The advantages of TRIGGERs over declarative referential integrity are that you can do everything that DRI can, and almost anything else, too. The disadvantages are that the optimizer cannot get any data from the procedural code, the TRIGGERs take longer to execute, and they are not portable from product to product.
My advice would be to avoid TRIGGERs when you can use declarative referential integrity instead. If you do use them, check the code very carefully and keep it simple so that you will not hurt performance.
1.4.5 CREATE PROCEDURE Statement
CREATE PROCEDURE allows you to declare and name a module of procedural code written in SQL/PSM or another ANSI X3J programming language. The two major differences in a TRIGGER and a PROCEDURE are that a procedure can accept parameters and return values, and that is it is explicitly invoked by a CALL from a user session and not a database event.
Again, many SQL products have had their own versions of procedure, so you should look at what your particular vendor has given you, check the code very carefully, and keep it simple so you will not hurt performance.
The SQL/PSM (see Understanding SQL’s Stored Procedures by Jim Melton) for procedural code is an ISO Standard. Still, even with the move to the ISO Standard, existing implementations will still have their own proprietary syntax in many places.
1.4.6 DECLARE CURSOR Statement
I will not spend much time with cursors in this book, but you should understand them at a high level, since you will see them in actual code. Despite a standard syntax, every product has a proprietary version of
54 CHAPTER 1: DATABASE DESIGN
cursors, because cursors are a low-level construct that works close to the physical implementation in the product.
A CURSOR is a way of converting an SQL result set into a sequential data structure that looks like a simple sequential file. This structure can be handled by the procedural host language, which contains the very statement that executes and creates a structure that looks like a sequential file. In fact, the whole cursor process looks like an old-fashioned magnetic tape system!
You might have noticed that in SQL, the keyword CREATE builds persistent schema objects. The keyword DECLARE builds transient objects that disappear with the end of the session in which they were build. For this reason, you say DECLARE CURSOR, not CREATE CURSOR.
First, you allocate working storage in the host program with a BEGIN DECLARE ... END DECLARE section. This allocation sets up an area where SQL variables can be converted into host language data types, and vice versa. NULLs are handled by declaring INDICATORvariables in the host language BEGIN DECLARE section. The INDICATOR variables are paired with the appropriate host variables. An INDICATOR is an exact numeric data type with a scale of zero, that is, some kind of integer in the host language.
DECLARE CURSOR Statement
The DECLARE CURSOR statement must appear next. The SQL-92 syntax is fairly representative of actual products, but you must read your manual.
::=
DECLARE [INSENSITIVE] [SCROLL] CURSOR FOR
::=
[] []
::= FOR {READ ONLY | UPDATE [OF ]}
::= ORDER BY
::=
1.4 Other Schema Objects 55
[{ }...]
::= [] []
::=
::= ASC | DESC
A few things need explaining. First of all, the ORDER BY clause is part of a cursor, not part of a SELECT statement. Because some SQL products, such as SQL Server and Sybase, allow the user to create implicit cursors, many newbies get this wrong. This is easy to implement in products that evolved from sequential file systems and still expose this architecture to the user, in violation of Dr. Codd’s rules. Oracle is probably the worst offender as of this writing, but some of the “micro-SQLs” are just as bad.
If either INSENSITIVE, SCROLL, or ORDER BY is specified, or if the working table is a read-only, then an of READ ONLY is implicit. Otherwise, an of FOR UPDATE without a is implicit.
OPEN Statement
The OPEN statement positions an imaginary read/ write head before the first record in the cursor. FETCH statements can then move this imaginary read/write head from record to record. When the read/write head moves past the last record, an exception is raised, like an EOF (end of file) flag in a magnetic tape file system.
Watch out for this model! In some file systems, the read/write head starts on the first record and the EOF flag is set to TRUEwhen it reads the last record. Simply copying the algorithms from your procedural code into SQL/PSM might not work.
FETCH Statement
::= FETCH [[] FROM] INTO
::= NEXT | PRIOR | FIRST | LAST
| {ABSOLUTE | RELATIVE}
The FETCH statement takes one row from the cursor, then converts each SQL data type into a host-language data type and puts result into
56 CHAPTER 1: DATABASE DESIGN
the appropriate host variable. If the SQL value was a NULL, the INDICATOR is set to -1; if no indicator was specified, an exception condition is raised.
As you can see, the host program must be sure to check the INDICATORs, because otherwise the value of the parameter will be garbage. If the parameter is passed to the host language without any problems, the INDICATOR is set to zero. If the value being passed to the host program is a non-NULL character string and it has an indicator, the indicator is set to the length of the SQL string and can be used to detect string overflows or to set the length of the parameter.
The tells the read/write head which way to move. NEXT and PRIOR read one record forward or backward from the current position. FIRST and LAST put the read/write on the first or last records respectively. The ABSOLUTE fetch moves to a given record number. The RELATIVE fetch moves the read/write head forward or backward (n) records from the current position. Again, this is a straight imitation of a sequential file system.
CLOSE Statement
The CLOSE statement resets the cursor read/write head to a position before the first row in the cursor. The cursor still exists, but must be reopened before it can be used. This is similar to the CLOSE FILE operations in FORTRAN or COBOL, but with an important difference, the cursor can be recomputed when it is reopened!
DEALLOCATE Statement
The DEALLOCATE CURSOR statement frees up the working storage in the host program. Think of it as dismounting a tape from the tape drive in a sequential file system.
How to Use a CURSOR
The best performance improvement technique for cursors inside the database is not to use them. SQL engines are designed for set processing, and they work better with sets of data than with individual rows. The times when using cursor is unavoidable usually deal with corrections to the database caused by an improper design, or when speed of a cursor is faster because of the physical implementation in the product. For example, a cursor can be used to take redundant duplicates out of a table that does not have a key.
The old argument for cursors in the original Sybase SQL Server training course was this example. You own a bookstore and you want to
...
- tailieumienphi.vn
nguon tai.lieu . vn