Đang chuẩn bị liên kết để tải về tài liệu:
Joe Celko s SQL for Smarties - Advanced SQL Programming P24

Joe Celko s SQL for Smarties - Advanced SQL Programming P24. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 202 CHAPTER 7 MULTIPLE COLUMN DATA ELEMENTS CREATE FUNCTION Distance IN latitudel REAL IN longitudel REAL IN latitude2 REAL IN longitude2 REAL RETURNS REAL BEGIN DECLARE r REAL DECLARE lat REAL DECLARE lon REAL DECLARE a REAL DECLARE c REAL SET r 6367.00 0.6214 calculate the Deltas. SET lon longitude2 - longitudel SET lat latitude2 - latitudel Intermediate values. SET a SIN lat 2 COS latitudel COS latitude2 SIN lon 2 --Intermediate result c is the great circle distance in radians. SET c 2 ARCSIN LEAST l.00 SQRT a Multiply the radians by the radius to get the distance RETURN r c END least function protects against possible round-off errors that could sabotage computation of the arcsin if the two points are very nearly antipodal. It exists as a vendor extension in Oracle and MySQL but can be written with a CASE expression in Standard SQL. 7.2 Storing an IP Address in SQL While not exactly a data type IP addresses are being used as unique identifiers for people or companies. If you need to verify them you can send an e-mail or ping them. There are three popular ways to store an IP address a string an integer and a set of four octets. In a test conducted in SQL Server all three methods required about the same amount of time work and I O to return data as a string. The 7.2 Storing an IP Address in SQL 203 latter two have some additional computations but the overhead was not enough to affect performance very much. The conclusion was that the octet model with four tinyint columns had two advantages simpler programming indexes on individual octets and human readability. But you should look at what happens in your own environment. tinyint is a one-bit integer data type found in SQL Server and other products smallint is the closest thing to it in Standard SQL. 7.2.1 A Single VARCHAR 15 Column The most obvious way to store IP addresses for example 63.246.173.210 is a VARCHAR 15 column with a CHECK constraint that uses a similar to predicate to be sure that it has the dots and