(Parte 1 de 4)

SQL Functions Programmer’s Reference SQL Functions Programmer’s Reference

SQL Functions Programmer’s Reference

Arie Jones

Ryan K. Stephens Ronald R. Plew Robert F. Garrett Alex Kriegel

SQL Functions Programmer’s Reference

Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 w.wiley.com

Copyright © 2005 by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN 13 978-0-7645-6901-2 ISBN 10 0-7645-6901-5 Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 1B/RU/QU/QV/IN

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 2 Rosewood Drive, Danvers, MA01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permission

For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002.

For technical support, please visit w.wiley.com/techsupport.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

SQLfunctions programmer’s reference / Arie Jones[et al.].

Library of Congress Cataloging-in-Publication Data: p. cm.

Includes bibliographical references and index. ISBN 0-7645-6901-5 (paper/website : alk. paper) 1. SQL(Computer program language) I. Jones, Arie. QA76.73.S67S674 2005 005.13’3--dc22 2005002765

Trademarks:Wiley, the Wiley Publishing logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

About the Authors

Arie Jones

Arie Jones is a senior database administrator for Perpetual Technologies, Inc. (w.perptech.com). He holds a master’s degree in physics from Indiana State University and also works as the chief Web architect/DBA for the USPFO for Indiana. Arie’s main specialty is in developing .NET-based database solutions for the government. He and his wife and family live outside of Indianapolis, Indiana.

Ryan K.Stephens

Ryan Stephens is the president and CEO of Perpetual Technologies, Inc. (w.perptech.com), an Indianapolisbased IT firm specializing in database technologies. Ryan has been working with SQLand databases for 15 years and has held the positions of project manager, database administrator, and programmer/analyst. Ryan has been teaching database courses for local universities since 1997 and has authored several internationally published books on topics such as database design, SQL, database architecture, database administration, and Oracle. Ryan enjoys discovering new ways to optimize the use of technology to streamline business operations, as well as empowering others to do the same. Ryan and his wife live in Indianapolis with their three children.

Ronald R.Plew

Ronald R. Plew is vice president and CIO for Perpetual Technologies, Inc. (w.perptech.com) in Indianapolis, Indiana. Ron is a Certified Oracle Professional. He has coauthored several internationally published books on SQLand database technology. Ron is also an adjunct professor for Vincennes University in Indiana, where he teaches SQLand various database courses. Ron holds a bachelor of science degree in business administration/management from Indiana Institute of Technology out of Fort Wayne, Indiana. Ron recently retired from the Indiana Army National Guard, where he served as a programmer/analyst. His hobbies include automobile racing, chess, golf, and collecting Indy 500 memorabilia. Ron resides in Indianapolis with his wife Linda.

Robert F.Garrett

Bob Garrett is the software development manager at Perpetual Technologies, Inc. (w.perptech.com). Bob’s languages of preference are Java, C++, and English. He has extensive experience integrating applications with relational databases. Bob has a degree in computer science and mathematics from Purdue University, and lives with his wife and daughter near Indianapolis.

Alex Kriegel

Alex Kriegel is a professional database systems analyst with a major manufacturing firm in Oregon. He has more than 10 years of database experience working with Microsoft SQLServer, Oracle, DB2, Sybase, and PostgreSQLboth as developer and DBA. Alex has a bachelor of science degree in solid-state physics from State Polytechnic Institute of Minsk, Belarus, and has earned the Microsoft Certified Solution Developer (MCSD) accreditation. He is the author of SQLBible. Alex wrote the first draft of approximately two-thirds of this book.

Contributing Authors

Joshua Stephens

Joshua Stephens is a systems administrator/DBAfor Perpetual Technologies, Inc. (w.perptech.com). He has eight years of experience in various IT areas. As a former technical writer and trainer, he continues to enjoy helping others through writing. He holds a bachelor of arts degree in pure mathematics and physics from Franklin College. He lives in Franklin, Indiana, with his wife and daughter.

Richard Bulley

Richard is a Ferris State University graduate and received a master of arts degree from Ball State University. He has had 20 years of data processing experience with the United States Air Force and is a United States Air Force Reserves Retiree and currently has over six years of experience as a Sybase and MS SQLServer system DBA.

Credits

Acquisitions Editor Jim Minatel

Development Editor Kevin Shafer

Production Editor Gabrielle Nabi

Technical Editor Wiley-Dreamtech India Pvt Ltd

Copy Editor Publication Services, Inc.

Editorial Manager Mary Beth Wakefield

Vice President & Executive Group Publisher Richard Swadley

Vice President and Publisher Joseph B. Wikert

Project Coordinator Ryan Steffen

Graphics and Production Specialists April Farling, Carrie Foster, Denny Hager, Julie Trippetti

Quality Control Technicians Joe Niesen John Greenough

Proofreading and Indexing TECHBOOKS Production Services

I would like to dedicate this book to my wife, Jacqueline, for being understanding and supportive during the long hours that it took to complete this book.

— Arie Jones For Tina, Daniel, Autumn, and Alivia. You are my inspiration. — Ryan Stephens For Linda — Ron Plew For Becky and Libby — Bob Garrett

Acknowledgments

Shortly after we accepted this project, it became clear how much of a team effort would be needed to make this book a must-have for anyone’s SQLlibrary. Fortunately, I have an incredible technical team that knows how to come together and get the job done. Most of my thanks go to Arie Jones. Arie stepped up when I needed the most help, unafraid of commitment, confidently accepting another aggressive assignment. Our author team included Arie Jones, Ron Plew, Bob Garrett, Alex Kriegel, and myself. Contributing authors were Joshua Stephens and Richard Bulley. I cannot say enough about their professionalism and technical proficiency. Thank you for being part of another successful project!

Probably as no surprise to the Wiley audience, the author team thanks the editorial staff at Wiley, which is one of the best with whom we have had the pleasure of working. Specifically, we appreciate Jim Minatel’s efforts and confidence in our team, Kevin Shafer’s strict attention to detail, and the technical editorial team’s thoroughness. Their dedication, patience, and thoroughness, we believe, reflect directly on the quality and timely delivery of this book, which would not have been possible without each of them, as well as the unmentioned Wiley staff behind the scenes.

— Ryan Stephens and the author team

Contents

Acknowledgments ix Introduction xxxv

Chapter 1: Exploring Popular SQL Implementations 1

Introduction to SQL 1 Understanding the SQL Standard 2 Overview of Vendor Implementations of SQL 2

Oracle 3 IBM DB2 UDB 3 Microsoft SQL Server and Sybase 3 MySQL 3 PostgreSQL 4

Connecting to SQL Databases 4 ANSI SQL Data Types 5 Creating SQL Databases 5 Querying SQL Databases 7 Manipulating Data in SQL Databases 9 Summary 1

Chapter 2: Functions: Concept and Architecture 13

What Is a Function? 13

Simple UNIX Shell Function Example 14 Simple SQL Function Example 15

ANSI SQL Functions 15 Built-in Functions 16

Executing Built-in Functions 17 Practical Uses of Functions 17

Creating,Compiling,and Executing a SQL Function 18 Passing Parameters by Value or by Reference 2 Scope of a Function 24

Better Security 25 Overloading 25

Classifying SQL Functions: Deterministic and Non-Deterministic Functions 27

Oracle 29 IBM DB2 UDB 29

xii

Contents

Microsoft SQL Server 30 Sybase 31 MySQL and PostgreSQL 31 Summary 32

Chapter 3: Comparison of Built-in SQL Functions by Vendor 3

Oracle 35 IBM DB2 UDB 36 Microsoft SQL Server and Sybase ASE 37 MySQL 39 PostgreSQL 39

Overview of Built-in Functions by Vendor 40 Summary 48

Chapter 4: SQL Procedural Extensions and User-Defined Functions 49

Procedural versus Declarative Languages 49 ANSI SQL Guidance for Procedural Extensions to SQL 51 SQL Procedural Extensions by Vendor 52

Oracle PL/SQL 52 Microsoft or Sybase Transact-SQL 54 IBM Procedural SQL 5 MySQL 57 PostgreSQL 57 Summary 58

Chapter 5: Common ANSI SQL Functions 59

String Functions 65 ASCII() 6 CHR() or CHAR() 67 CONCAT() 67 LOWER() and UPPER() 68 LENGTH() or LEN() 68 REPLACE() 69

xiii

Contents

Miscellaneous Functions 85 COALESCE() 85 NULLIF() 85 Summary 86

Chapter 6: Oracle SQL Functions 87

Oracle Query Syntax 87 Aggregate Functions 90 AVG() 91 CORR() 92 COUNT() 93 GROUPING() 94 MAX() and MIN() 95 STDDEV() 95 SUM() 96 Analytic Functions 97 xiv

Contents

Character Functions 97

CHR() and NCHR() 9 INITCAP() 100 LPAD() and RPAD() 101 TRIM(),LTRIM(),and RTRIM() 102 REPLACE() 103 SOUNDEX() 103 SUBSTR() 104 TRANSLATE() 105

Regular Expressions 106 Conversion Functions 106 CAST() 107 COMPOSE() 108 CONVERT() 108 DECOMPOSE() 110 TO_CHAR() 110 TRANSLATE...USING 113 UNISTR() 113

Date and Time Functions 114 ADD_MONTHS() 115 DBTIMEZONE and SESSIONTIMEZONE 116 EXTRACT() 117 MONTH_BETWEEN() 118 NEW_TIME() 118 ROUND() 119 SYSDATE 120 TRUNC() 121

Numeric Functions 123 ABS() 123 BITAND() 124 CEIL() and FLOOR() 124 MOD() 125 SIGN() 125 ROUND() 126 TRUNC() 127

Object Reference Functions 127 Miscellaneous Single-Row Functions 127 COALESCE() 128 DECODE() 129 DUMP() 130 GREATEST() 131 NULLIF() 132 NVL() 133

Contents

NVL2() 133 UID 134 VSIZE() 134 Summary 135

Chapter 7: IBM DB2 Universal Database (UDB) SQL Functions 137

DB2 UDB Query Syntax 138 String Functions 140 CONCAT() 142 INSERT() 142 LEFT() and RIGHT() 143 LENGTH() 143 LOCATE() and POSSTR() 144 LTRIM() and RTRIM() 144 REPEAT() 145 REPLACE() 145 SOUNDEX() 146 SPACE() 146 SUBSTR() 147 TRUNC() or TRUNCATE() 147

Date and Time Functions 148 DATE() 150 DAY() 151 DAYNAME() 151 DAYOFWEEK() 152 DAYOFWEEK_ISO() 152 DAYOFYEAR() 153 DAYS() 153 HOUR() 153 JULIAN_DAY() 154 MICROSECOND() 155 MIDNIGHT_SECONDS() 155 MINUTE() 156 MONTH() 156 MONTHNAME() 157 SECOND() 157 TIME() 157 TIMESTAMP() 158 TIMESTAMPDIFF() 159 TIMESTAMP_FORMAT() 160 TIMESTAMP_ISO() 160 WEEK() 160 xvi

Contents

WEEK_ISO() 161 YEAR() 161

Conversion Functions 162

DEC or DECIMAL 163 HEX() 164 DOUBLE or DOUBLE_PRECISION 164 INT() or INTEGER() and SMALLINT() 165 TRANSLATE() 165 VARCHAR() 166

Security Functions 167 DECRYPT_BIN() 167 DECRYPT_CHAR() 168 ENCRYPT() 168 GETHINT() 169

IBM DB2 UDB Special Registers 169 CURRENT DATE 170 CURRENT DEFAULT TRANSFORM GROUP 171 CURRENT DEGREE 171 CURRENT EXPLAIN MODE 171 CURRENT EXPLAIN SNAPSHOT 172 CURRENT ISOLATION 172 CURRENT NODE 173 CURRENT PATH 173 CURRENT QUERY OPTIMIZATION 174 CURRENT REFRESH AGE 174 CURRENT SCHEMA 174 CURRENT SERVER 175 CURRENT TIME 175 CURRENT TIMESTAMP 175 CURRENT TIMEZONE 176 SESSION_USER 176 USER 177

Miscellaneous Functions 177

COALESCE() and VALUE() 178 DIGITS() 179 GENERATE_UNIQUE() 179 NULLIF() 180 RAND() 180 TABLE_NAME() 181 TYPE_ID() 182 TYPE_NAME() 182 Summary 182 xvii

Contents

Chapter 8: Microsoft SQL Server Functions 183

SQL Server Query Syntax 183 String Functions 185 ASCII() 187 CHAR() 187 CHARINDEX() 188 DIFFERENCE() 188 LEFT() and RIGHT() 189 LEN() 189 LOWER() 190 LTRIM() and RTRIM() 190 NCHAR() 191 PATINDEX() 191 REPLACE() 192 QUOTENAME() 192 REPLICATE() 192 REVERSE() 193 SOUNDEX() 193 SPACE() 194 STR() 194 STUFF() 195 SUBSTRING() 196 UNICODE() 196 UPPER() 196

Date and Time Functions 197 DATEADD() 198 DATEDIFF() 199 @@DATEFIRST() 199 DATENAME() 200 DATEPART() 200 DAY() 201 GETDATE() and GETUTCDATE() 202 MONTH() 202 YEAR() 203

Metadata Functions 203 COL_LENGTH() 204 DB_ID() 205 DB_NAME() 205 FILE_ID() 206 FILE_NAME() 207 xviii

Contents

Configuration Functions 207 @@CONNECTION 207

@@LANGID 208

@@LANGUAGE 208

@@LOCK_TIMEOUT 210

@@MAX_CONNECTIONS 210

@@NESTLEVEL 211

@@OPTIONS 211

@@SPID 211

@@VERSION 212

Security Functions 212 HAS_DBACCESS() 213 SUSER_SID() 214 SUSER_SNAME() 214 USER 214 USER_ID() 215 USER_NAME() 215

System Functions 216 APP_NAME() 219 CASE 219 CAST() and CONVERT() 220 COALESCE() 224 CURRENT_TIMESTAMP 225 CURRENT_USER 225 DATALENGTH() 226 @@ERROR 226 HOST_ID() 227 HOST_NAME() 227 @@IDENTITY 228 IDENTITY() 228 ISDATE() 229 ISNULL() 229 ISNUMERIC() 230 NEWID() 230 PERMISSIONS() 231 ROWCOUNT_BIG and @@ROWCOUNT 231 @@TRANCOUNT 232 COLLATIONPROPERTY() 233 SCOPE_IDENTITY() 233

System Statistical Functions 234 @@CPU_BUSY 235

@@IDLE 235 xix

Contents

@@IO_BUSY 236 @@TIMETICKS 236

@@TOTAL_ERRORS 236

@@TOTAL_READ 237

@@TOTAL_WRITE 237 fn_virtualfilestats() 238

Undocumented Functions 239 ENCRYPT() 240 FN_GET_SQL() 240 @@MICROSOFTVERSION 241 PWDCOMPARE() 241 PWDENCRYPT() 242 TSEQUAL() 242 Summary 243

Chapter 9: Sybase ASE SQL Built-In Functions 245

Sybase Query Syntax 246 String Functions 247 CHARINDEX() 249 CHAR_LENGTH() 250 COMPARE() 250 DIFFERENCE() 252 LTRIM() and RTRIM() 252 PATINDEX() 253 REPLICATE() 254 REVERSE() 254 RIGHT() and LEFT() 255 SORTKEY() 255 SOUNDEX() 257 SPACE() 257 STR() 257 STUFF() 258 SUBSTRING() 259 USCALAR() 259

Date and Time Functions 260 DATEADD() 261 DATEDIFF() 262 DATENAME() 262 DATEPART() 263 GETDATE() 264

Contents

Conversion Functions 264 CONVERT() 266 INTTOHEX() 270 HEXTOINT() 271

Security Functions 271 IS_SEC_SERVICE_ON() 272 SHOW_SEC_SERVICES() 272

Text and Image Functions 286 TEXTPTR() 287 TEXTVALID() 287

System Functions 288 COL_LENGTH() 289 COL_NAME() 290

xxi

Contents

Unary System Functions 299 @@BOOTTIME 301

@@CLIENT_CSID 301

@@CLIENT_CSNAME 302

@@CONNECTIONS 302

@@CPU_BUSY 303

@@ERROR 303

@@ERRORLOG 303

@@IDENTITY 304

@@IDLE 305

@@IO_BUSY 305

(Parte 1 de 4)

Comentários