SQL Anywhere® Server - SQL Usage

928 Pages·2012·6.62 MB·English

SQL Anywhere® Server
SQL Usage
Version 12.0.1
January 2012
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1
Version 12.0.1
January 2012
Copyright © 2012 iAnywhere Solutions, Inc. Portions copyright © 2012 Sybase, Inc. All rights reserved.
This documentation is provided AS IS, without warranty or liability of any kind (unless provided by a separate written agreement between
you and iAnywhere).
You may use, print, reproduce, and distribute this documentation (in whole or in part) subject to the following conditions: 1) you must
retain this and all other proprietary notices, on all copies of the documentation or portions thereof, 2) you may not modify the
documentation, 3) you may not do anything to indicate that you or anyone other than iAnywhere is the author or source of the
documentation.
iAnywhere®, Sybase®, and the marks listed at http://www.sybase.com/detail?id=1011207 are trademarks of Sybase, Inc. or its subsidiaries.
® indicates registration in the United States of America.
All other company and product names mentioned may be trademarks of the respective companies with which they are associated.
Contents
About this book ................................................................................... vii
Tables, views, and indexes ................................................................... 1
Setting properties for database objects ............................................................ 1
Viewing lists of system objects (Sybase Central) ............................................ 2
Viewing lists of system objects (SQL) ............................................................... 3
Tables ................................................................................................................... 4
Temporary tables ................................................................................................. 9
Computed columns ........................................................................................... 11
Primary keys ...................................................................................................... 15
Foreign keys ....................................................................................................... 18
Indexes ............................................................................................................... 23
Views ................................................................................................................... 35
Materialized views ............................................................................................. 49
Stored procedures, triggers, batches, and user defined functions . .71
Benefits of procedures, triggers, and user-defined functions ...................... 71
Procedures ......................................................................................................... 72
User-defined functions ...................................................................................... 76
Triggers .............................................................................................................. 80
Batches ............................................................................................................... 91
The structure of procedures, triggers, and user-defined functions ............. 94
Control statements ............................................................................................ 97
Result sets ........................................................................................................ 100
Cursors in procedures, triggers, user-defined functions, and batches ..... 106
Error and warning handling ............................................................................ 109
EXECUTE IMMEDIATE used in procedures, triggers, user-defined
functions, and batches .................................................................................... 117
Transactions and savepoints in procedures, triggers, and user-defined
functions ........................................................................................................... 119
Tips for writing procedures, triggers, user-defined functions, and
batches ............................................................................................................. 120
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 iii
SQL Anywhere® Server - SQL Usage
Statements allowed in procedures, triggers, events, and batches ............. 122
Hiding the contents of procedures, functions, triggers, events, and
views ................................................................................................................. 123
Performance improvements, diagnostics, and monitoring ........... 125
Performance monitoring and diagnostic tools ............................................. 125
Tips for improving performance .................................................................... 185
Application profiling tutorials ......................................................................... 226
Query and modify data ...................................................................... 247
Queries ............................................................................................................. 247
Full text search ................................................................................................ 324
Summarizing, grouping, and sorting query results ..................................... 406
Joins: Retrieving data from several tables ................................................... 428
Common table expressions ............................................................................ 472
OLAP support .................................................................................................. 487
Use of subqueries ............................................................................................ 534
Data manipulation statements ........................................................................ 556
SQL dialects and compatibility ........................................................ 573
SQL compliance testing using the SQL Flagger .......................................... 573
SQL Anywhere features that differ from other SQL implementations ........ 575
Watcom SQL .................................................................................................... 579
Transact-SQL compatibility ............................................................................ 580
Adaptive Server Enterprise architectures ..................................................... 582
Transact-SQL-compatible databases ............................................................ 587
Compatible SQL statements ........................................................................... 593
Transact-SQL procedure language ................................................................ 598
Automatic translation of stored procedures ................................................. 600
Returning result sets from Transact-SQL procedures ................................. 601
Variables in Transact-SQL procedures ......................................................... 602
Error handling in Transact-SQL procedures ................................................. 602
iv Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1
SQL Anywhere® Server - SQL Usage
Use of XML in the database .............................................................. 605
Storage of XML documents in relational databases .................................... 605
Exporting relational data as XML ................................................................... 606
Importing XML documents as relational data ............................................... 607
Query results as XML ...................................................................................... 614
Use of Interactive SQL to view results .......................................................... 632
Use of SQL/XML to obtain query results as XML ......................................... 633
Remote data and bulk operations .................................................... 641
Data import and export ................................................................................... 641
Remote data access ........................................................................................ 693
Server classes for remote data access ......................................................... 729
Data integrity ...................................................................................... 749
How your data can become invalid ................................................................ 749
Integrity constraints ........................................................................................ 749
How the contents of your database change ................................................. 750
Tools for maintaining data integrity ............................................................... 750
SQL statements for implementing integrity constraints .............................. 751
Column defaults ............................................................................................... 752
Table and column constraints ........................................................................ 759
Domains ............................................................................................................ 764
Entity and referential integrity ........................................................................ 767
Integrity rules in the system tables ................................................................ 776
Transactions and isolation levels .................................................... 779
Transactions .................................................................................................... 779
Concurrency ..................................................................................................... 781
Savepoints within transactions ...................................................................... 782
Isolation levels and consistency .................................................................... 782
Transaction blocking and deadlock ............................................................... 797
How locking works .......................................................................................... 802
Guidelines for choosing isolation levels ....................................................... 817
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 v
SQL Anywhere® Server - SQL Usage
Isolation level tutorials .................................................................................... 820
Primary key generation and concurrency ..................................................... 840
Data definition statements and concurrency ................................................ 845
Summary .......................................................................................................... 845
The SQL Anywhere debugger .......................................................... 847
Requirements for using the debugger ........................................................... 847
Tutorial: Getting started with the debugger .................................................. 848
Breakpoints ...................................................................................................... 852
Variables ........................................................................................................... 855
Connection debugging .................................................................................... 858
Index ................................................................................................... 859
vi Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1
About this book
This book describes how to add objects to a database; how to import, export, and modify data; how to
retrieve data; and how to build stored procedures and triggers.
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 vii
viii Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1
Tables, views, and indexes
This section provides procedures for adding database objects and setting database properties.
The SQL statements for creating, changing, and dropping database objects are called the data definition
language (DDL). The definitions of the database objects form the database schema. A schema is the
logical framework of the database.
See also
● “Stored procedures, triggers, batches, and user defined functions” on page 71
● “Data integrity” on page 749
● “SQL Anywhere database creation” [SQL Anywhere Server - Database Administration]
● “Sybase Central” [SQL Anywhere Server - Database Administration]
● “Interactive SQL” [SQL Anywhere Server - Database Administration]
Setting properties for database objects
You can view or set the properties of a database and of most database objects.
Prerequisites
DBA authority.
Context and remarks
Use the properties windows in Sybase Central to view and set properties. If you do not use Sybase
Central, specify the properties when you create the object with a CREATE statement. If the object already
exists, use an ALTER statement to modify the properties.
Set properties for database objects using Sybase Central
1. Connect to the database using the SQL Anywhere 12 plug-in.
2. Open the folder that the object is in.
3. Select the object.
4. In the right pane, click the appropriate tabs to edit the properties.
Results
The object properties appear in the right pane of Sybase Central and are editable.
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 1
Tables, views, and indexes
Next
You can also view and edit properties on the object's properties window. To view the properties window,
right-click the object, and then click Properties. Some properties that were set when a database was
created are non-configurable.
See also
● “Connection, database, and database server properties” [SQL Anywhere Server - Database
Administration]
Viewing lists of system objects (Sybase Central)
Use Sybase Central to display information about system objects such as system tables, system views,
stored procedures, and domains. You perform this task when you want see the list of system objects in the
database, and their definitions, or when you want to use their definition to create other similar objects.
Prerequisites
DBA authority, or privileges to view system objects.
Context and remarks
Many.
Display system objects in a database using Sybase Central
1. Connect to the database using the SQL Anywhere 12 plug-in
2. Select the database and click File » Configure Owner Filter.
3. Select SYS, and dbo, and then click OK.
Results
The list of system objects displays in Sybase Central.
Next
None.
See also
● “SYSOBJECT system view” [SQL Anywhere Server - SQL Reference]
● “SYSTAB system view” [SQL Anywhere Server - SQL Reference]
● “SYSUSER system view” [SQL Anywhere Server - SQL Reference]
2 Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1