[ansi-iso-iec, 1999] database language sql. part 1. sql-framework. sql99

8/9/2019 [ANSI-IsO-IEC, 1999] Database Language SQL. Part 1. SQL-Framework. SQL99

1/85

ANSI/ISO/IEC International Standard (IS)

Database Language SQL — Part 1: SQL/Framework

«Part 1»

September 1999


2/85

ISO/IEC 9075-1:1999(E)

Contents Page

F or ewor d . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v i i

Int r oduct i on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x v ii

1 Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

2 Normative references . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

3 Definitions and use of terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

3. 1 D e fi n it i on s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

3.1.1 D efini t i ons pr ov ided in t hi s s t a nda r d . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

3. 2 U s e of t e rm s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

3. 3 I n f or m a t i ve el em en t s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

4 Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

4. 1 C a v ea t . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

4. 2 S Q L -e nv ir on m en t s a n d t h e ir com pon en t s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

4.2.1 S QL-envi r onm ent s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

4. 2. 2 S Q L -a g e nt s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

4.2.3 S QL-i m pl em ent a t i ons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

4. 2. 3. 1 S Q L -cl ien t s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

4. 2. 3. 2 S Q L -s er v er s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

4.2.4 S QL-cl ient m odul es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

4.2.5 Us er i dent i fier s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 0

4.2.6 C a t a l ogs a nd s chem a s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

4. 2. 6. 1 C a t a l og s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

4. 2. 6. 2 S Q L -s ch em a s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

4. 2. 6. 3 Th e I n for m a t i on S ch em a . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

4. 2. 6. 4 Th e D efi n i t i on S ch em a . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

4.2.7 S QL-da t a . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

4.3 Ta b l es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1

4. 4 S Q L d a t a t y pe s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 2

4.4.1 G ener a l da t a t y pe infor m a t i on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 2

4.4.2 The null v a l ue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 3

4.4.3 P r edefined t y pes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

4. 4. 3. 1 N u m er ic t y pe s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 3

4. 4. 3. 2 S t r i ng t y p es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

ii Framework (SQL/Framework)


3/85

©ISO/IEC ISO/IEC 9075-1:1999 (E)

4. 4. 3. 3 B o ol ea n t y p e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

4. 4. 3. 4 D a t e t i me t y pe s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 4

4. 4. 3. 5 I n t e rv a l t y pe s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

4.4.4 C onst r uct ed a t om i c t y pes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144. 4. 4. 1 R ef er en ce t y pes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

4.4.5 C onst r uct ed com pos i t e t y pes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

4. 4. 5. 1 C ol le ct i on t y p es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

4. 4. 5. 2 R ow t y p es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 5

4. 4. 5. 3 F ie ld s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

4. 5 S i t es a n d o pe ra t i on s on s i t es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 5

4.5.1 S i t es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

4.5.2 As s ignm ent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

4.5.3 N ul la b i li t y . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 5

4.6 S QL-s chem a object s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 6

4.6.1 G ener a l S QL-s chem a object i nfor m a t i on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164.6.2 D es cr i pt or s r el a t i ng t o cha r a ct er s et s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 6

4. 6. 2. 1 C h a r a c t er s et s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

4. 6. 2. 2 C ol la t i on s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 7

4. 6. 2. 3 Tr a n s la t i on s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

4.6.3 D om a i ns a nd t hei r com ponent s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

4. 6. 3. 1 D om a i n s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

4. 6. 3. 2 D om a i n con s t r a in t s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 7

4.6.4 Us er -defined t y pes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

4. 6. 4. 1 S t r u ct u r ed t y pe s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

4. 6. 4. 2 At t r i bu t es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 8

4.6.5 D i s t inct t y pes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184.6.6 B a s e t a b les a nd t hei r com ponent s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 8

4. 6. 6. 1 B a s e t a b l es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

4. 6. 6. 2 C ol um n s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

4. 6. 6. 3 Ta b le con s t r a in t s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

4. 6. 6. 4 Tr ig g er s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 9

4.6.7 Vi ew defini t i ons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

4.6.8 As s er t i ons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 0

4.6.9 SQL-server modules (defi ned in I SO/IE C 9075-4, SQL/P SM) . . . . . . . . . . . . . . . . . . . . . . . 20

4.6.10 S c hem a r out i nes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

4.6.11 P r i v il eges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

4.6.12 R ol es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204.7 Int egr it y c ons t r a i nt s a nd c ons t r a i nt c hecking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 0

4.7.1 C onst r a i nt checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

4.7.2 D et er mi nis m a nd cons t r a i nt s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

4.8 C omm uni ca t i on bet ween a n S QL-a gent a nd a n S QL-i m pl em ent a t i on . . . . . . . . . . . . . . . . 21

4. 8. 1 H o st l a n g u a g es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1

4.8.2 P a r a m et er pa s s i ng a nd da t a t y pe cor r espondences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

4.8.2.1 G ener a l pa r a m et er pa s s ing a nd da t a t y pe cor r espondence i nfor m a t i on . . . . . . . . . . . 22

4.8.2.2 D a t a t y pe cor r espondences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Contents iii


4/85


5/85


6.2.4 D es cr i pt or s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

6.2.5 Relationships of incremental pa rts t o ISO/IEC 9075-2, Founda tion . . . . . . . . . . . . . . . . . . 42

6.2.5.1 N ew a nd m odi fied C la us es , S ub cl a us es , a nd Annexes . . . . . . . . . . . . . . . . . . . . . . . . 42

6. 2. 5. 2 N ew a n d m od ifi e d F or m a t i t em s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436. 2. 5. 3 N ew a n d m od ifi e d p a r a g r a ph s a n d r u le s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 3

6. 2. 5. 4 N ew a n d m od ifi e d t a b l es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

6.2.6 Index t y pogr a phy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

6. 3 O bje ct i d en t i fi e r for D a t a b a s e L a n g u a g e S QL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

7 Annexes to theparts of ISO/IEC 9075 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 7

7.1 Im plem ent a t i on-defined el em ent s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 7

7.2 Im plem ent a t i on-dependent el em ent s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 7

7. 3 D e pr eca t e d f ea t u r e s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 7

7.4 Incom pa t i b il it i es wi t h pr ev ious v er s ions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 7

8 Conformance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 9

8.1 R eq uir em ent s for S QL-i m pl em ent a t i ons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 9

8. 1. 1 P a r t s a n d p a ck a g es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 9

8.1.2 F unct i ona l i t y . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

8.1.3 Addit i ona l fea t ur es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 9

8. 1. 4 S Q L fl a g g er . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

8.1.5 C l a i m s of c onfor m a nc e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

8.2 R eq uir em ent s for S QL a ppli ca t i ons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 1

8.2.1 Int r oduct i on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

8.2.2 R eq uir em ent s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

8.2.3 C l a i m s of c onfor m a nc e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Annex A Maintenance and interpretation of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

Annex B SQL Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

B . 1 E n h a n ce d da t e t im e fa c il it i es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 5

B . 2 E n h a n ce d in t eg r it y m a n a g e m en t . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

B . 3 O L AP f a ci li t ie s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

B . 4 P S M . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

B . 5 C L I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

B .6 Ba s ic ob ject s uppor t . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 7

B . 7 E n h a n ce d ob je ct s u pp or t . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

B . 8 Act i ve d a t a b a s e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 8B .9 S QL/M M s uppor t . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 8

Annex C Implementation-defined elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

Annex D Implementation-dependent elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 3

Contents v


6/85

ISO/IEC 9075-1:1999 (E) ©ISO/IEC

TABLES

Tables Page

1 R el a t ion s h ip s of r ou t in e ch a r a c t er is t ics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 5

2 S QL P a ck a ges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 5

vi Framework (SQL/Framework)


7/85


Foreword

(This foreword is not a par t of American Na tional St a nda rd ANSI /IS O/IE C 9075-1:1999.)This St a nda rd (American Na tiona l S ta nda rd ANSI /IS O/IE C 9075-1:1999, Information Systems — Database L anguage — SQL — Part 1: F r amework (SQL/ F r amework) ), replaces in part AmericanNa tiona l S ta nda rd X3.135-1992.

This American Nat iona l S ta nda rd specifies the framew ork for th e several pa rts of ANSI/ISO/IEC9075:1999.

ANSI /IS O/IE C 9075 consists of t he following par ts, under th e genera l t itle Information Systems — Database Language — SQL :

— P ar t 1: F ra mework (SQL/Fra mework)

— P ar t 2: Foundat ion (SQL/Foundation)

— P ar t 3: Ca ll-Level Interface (SQL/CLI)

— P ar t 4: P ersistent St ored Modules (SQL/P SM)

— P a r t 5 : Host La ngua ge Bi ndi ngs (S QL/Bi ndi ngs)

This American National Standard contains four Informative Annexes that are not considered partof t he S t a nda r d:

— Annex A (informa tive): Maintena nce an d interpreta tion of SQL.

— Annex B (informa tive): SQL Pa ckages.

— Annex C (informa tive): Implementa tion-defin ed elements.

— Annex D (informat ive): Implementa tion-dependent elements.

Requests for interpretation, suggestions for improvement or addenda, or defect reports are welcome.They should be sent to t he Na tional C ommittee for I nforma tion Technology Sta nda rds (NCITS),1250 Eye St reet, NW, Suit e 200, Wa shingt on, D C 20005.

This Standard was processed and approved for submittal to ANSI by NCITS. Committee approvalof this Sta nda rd does not necessarily imply tha t al l committee members voted for a pproval . At t hetime that i t approved this Standard, NCITS had the following members:

NCITS Chairman NCITS Vice Chair NCITS Secretary

Ms. K a ren H iggen bot t om Mr. D a ve Mich a el Ms. Monica Va go

*Non-Response **Abstain

PRODUCERS=13

Foreword vii


8/85


Apple Computer Inc.Mr. David Michael [P]M/S 301-4F1 INFINI TE LOOP

CUPERTINO CA 95014+ 1.408.862.5451Fa x: + [email protected]

Mr. J erry Kellenbenz [A]M/S 301-4F1 INFINI TE LOOPCUPERTINO CA 95014+ 1.408.974.7341Fa x: + 1.408.974.2691

[email protected]

Bull HN Information Systems Inc.M r. R a nda l l Kil m a r t i n [P ]M/S B 5813430 N. B LACK CANYON H IG HWAYPHOENIX AZ 85029+ 1.602.862.4905Fa x: + [email protected]

Compaq Computer CorporationMr. Scott J am eson [P]M/S AK O2-3/D 1050 NAGOG PARKACTON MA 01720

+ 1.508.264.7468Fa x: + [email protected]

Mr. Stephen Heil [A]

M/S 11060520555 SH249HOUSTON TX 77269-2000+ 1.281.518.0781Fa x: + [email protected]

Hewlett-Packard CompanyMs. Karen Higginbottom [P]M/S 43U C

19420 HOMESTEAD ROADCUPERTINO CA 95014+ 1.408.447.3274Fa x: + [email protected]

viii Framework (SQL/Framework)


9/85


Ms. Wendy Fong [A]M/S 47U -1019447 P RUNE RIDG E AVENU E

CUPERTINO CA 95014

+ 1.408.447.4463Fa x: + 1.408.447.3995Wendy _Fong@HP -Cu pertin o-notes2.om.com

Hitachi American Ltd.Mr. J ohn Neuma nn [P]43533 G OLDEN MEADOW CIRCL EASHBURN VA 22011+ 1.703.729.4858

Fa x: + [email protected]

Mr. Ha l Miyamoto [A]MS 7302000 SIE RRA P OINT PK WYBRISBANE CA 94005-1835+ 1.650.244.7218Fa x: + [email protected]

IBM CorporationRonald F. S illett i [P]P r ogr a m D i r ect or of S t a nda r dsIntellectual Property & LicensingIBM CorporationNORTH CASTLE DRIVEARMONK, NY 10504


Mr. J oel Urman [A]IBM CorporationM/S NC 113, R oom 1B 111NORTH CASTLE DRIVEARMONK, NY 10504-1785+ 1.914.765.4392Fa x: + [email protected]

Lucent Technologies Inc.Mr. Herbert Bertine [P]ROOM 4K-316101 CRAWFORDS CORNER RDH OLMD EL NJ 07733-3030+ 1.908.949.4022Fa x: + [email protected]

Foreword ix


10/85


Mr. Tom Rut t [A]ROOM 4L-308101 CRAWFORD CORNER ROAD

H OLMD EL NJ 07733-3030


Microsoft Corp.M r. M a r k R y l a nd [P ]ONE MIC ROS OFT WAYRE DM OND WA 98052+ 1.703.757.7430


Mr. J ohn Mont gomery [A]ONE MIC ROS OFT WAYRE DM OND WA 98072+ 1.425.705.2921Fa x: + [email protected]

Panasonic Technologies Inc.Mr. J udson H ofma nn [P]3RD FLOOR2 RESEARCH WAYP RI NC ETON NJ 08540-6628+ 1.609.734.7589Fa x: + [email protected]

Mr. Terry J . Nelson [A]3RD FLOOR

2 RESEARCH WAYPRINCETON NJ 08540+ 1.609.734.7324Fa x: + 1.609.987.8827tnelson@@research.panasonic.com

Sony Electronics Inc.M r. M a s a t a k a O g a w a [P ]MD: SJ -3B23300 ZANKER ROAD

SAN J OSE CA 95134-1940+ 1.408.955.5091Fa x: + [email protected]

Mr. Micha el Deese [A]MASS STORAGE DIV4845 PE ARL EAST CIRC LEBOULDER CO 80301+ 1.303.415.5821Fa x: + 1.303.447.8198

[email protected]

x Framework (SQL/Framework)


11/85


12/85


F a x :[email protected]

AT&T

Mr. Thomas Frost [P]ROOM 1A2920 IN D E P E N D E N C E BLVDWARRE N NJ 07059+ 1.908.580.6238Fa x: + [email protected]

Mr. Paul Bartoli [A]ROOM IL-334101 C RAWFORD S CORNE R ROADH OLMD EL NJ 07733-3030+ 1.908.949.5965Fa x: + [email protected]

Omron CorporationMr. Tak Na tsume [P ]

#800160 W SANTA C LARA S TRE ETSAN J OSE CA 95113+ 1.408.271.5211Fa x: + [email protected]

PerennialM r . Ba r r y Hedq ui s t [P ]

SUITE 2104699 OLD IRONSIDES DRIVE

SANTA CLARA, CA 95054+ 1.408.748.2900Fa x: + [email protected]

Plum Hall, Inc.Mr. Thomas Plum [P]3 WAIH ONAB ox 44610KAMUELA HI 96743

+ 1.808.882.1255Fa x: + 1.808.882.1556

[email protected]

Share Inc.Mr. Da ve Thewlis [P ]2301 C STREE TE U RE KA, CA 95501-4108+ 1.707.442.0547Fa x: + 1.707.442.9342

[email protected]

xii Framework (SQL/Framework)


13/85


Sybase, Inc.Mr. Bil ly Ho [P]1650 65th STREETEME RYVILL E CA 94608

+ [email protected]

US Department of Defense/DISAMr. Russ Richards [P]10701 PARKRIDGE BLVDRESTON VA 20191-4357+ 1.703.735.3552Fa x: + [email protected]

Dr. Doris Berna rdini [A]P O B OX 2309RESTON VA 20195-0309+ 1.703.735.3566Fa x: + [email protected]

US Department of EnergyMr. B ruce White [P ]MA-43 GE RMANTOWN B U ILD ING19901 GERMANTOWN ROAD

G E RMANTOWN MD 20874-1290+ 1.301.903.6977Fa x: + [email protected]

Ms. Ca rol Blackston [A]MA-43 GE RMANTOWN B U ILD ING19901 GERMANTOWN ROADG E RMANTOWN MD 20874-1290+ 1.301.903.4294


GENERAL INTEREST=2

Institute for Certification of Computer ProfessionalsMr. Kenneth M. Zemrowski [P ]C /O I S N/TRW S U I TE C -651280 MARYLAND AVENUE SW

WASHINGTON DC 20024+ 1.202.479.0085 X225Fa x: + 1.202.479.4187

[email protected]

Mr. Tom Kur iha ra [A]TKS TDS & ASS OCI ATES , INC .5713 6TH STRE E T, NOR THARL I NG TON , VA 22205-1013+ 1.703.516.9650Fa x: + 1.703.516.4688

Foreword xiii


14/85


[email protected]

National Institute of Standards & TechnologyMr. Michael Hogan [P]

BUILD IN G 8 2 0ROOM 634GAITHERSBURG MD 20899-0999+ 1.301.975.2926Fa x: + [email protected]

Mr. Bruce K. Rosen [A]BLD G 8 2 0

ROOM 562GAITHERSBURG MD 20899+ 1.301.975.3345Fa x: + [email protected]

Mr. William LaPlant J r. [A]4312 BIRCHLAKE COURTALEXANDRIA VA 22309+ 1.301.457.4887Fa x: + 1.301.457.2299

[email protected]

American Na tiona l S ta nda rd ANSI /IS O/IE C 9075-1:1999 w a s prepared by Technical Committ eeGroup NCITS H2, Database, working under the auspices of Accredited National StandardsCommitt ee NCITS (Nat iona l Committ ee for Informa tion Technology St a nda rds). TechnicalCommittee H2 on Database, which developed this Standard, had the following members:

Donald R. Deutsch, Chair

Bruce M. Horowitz, Vice-Chair

Krishna Kulkarni , International Representative

B a rry D. Vickers, Treasu rer

Michael M. Gorman, Secretary

J im Melton, Edit or

C h uck C a mpbell An drew E isenber g C h ris F a rr a rK eit h H a re D oug I n kst er Tom J ulien

R. Micha el Lefl er Mich a el P a n t a leo F ra nk P ellow

J a n et P richa r d Willia m E . Ra a b B r uce K . Rosen

P a ul S ca r poncini H erb S ut t er B ie Tie

Fred Zemke

xiv Framework (SQL/Framework)


15/85


Others holding Technical Committee H 2 membership wh ile the committee wa developing t hisstandard were the following:

D ea n A. Ander son Ma rk Ash w ort h J a mes B a rn et t e

J oh n B a r ney D a niel B a r t on Aime B a yleD a vid B eech Richa rd B oyn e An dra s B udin szky

Amelia C a rlson J oe C elko Ar t hur C ulber t son

J udy D illma n T. N. D or a isw a my S h el F inkelst ein

D on na F isher J effrey F r ied B a r ry F rit ch ma n

L eon a r d J . G a lla gh er K yle W. G eiger J im G ra h a m

Tom H a rw ood Wei H on g K en J a cobs

P hil J on es B ill K elley Willia m K en t

Willia m J . K ost er Vin ce K ow a lski Melissa LoB ia n do

F ra n Ly nch Nelson Ma t t os J eff Misch kin sky

M. Reza Mon a jjemi S a n t a n u Mukhopa dhya y P hil NelsonK ee Ong E mma n uel Onuegbe D ipa k P a t el

Richa r d P edigo E d P eeler P a ul P er kovic

Tom P er ry S h err y P et ersen Willia m P hillips

Mich a el P izzo Ma hesh Ra o G eor ge Ra uda ba ugh

E d Reyn olds J eff Richey J oh n S a dd

C h a nder S a rn a Robert S a un ders D a vid S chn epper

S cot t S ch nier C h rist ine S emen iuk La rr y S et t le

P hil S ha w Ma ur ice L . S mit h Ma dhuka r Tha kur

Ya ng Tsouya Mich a el U bell Mura li Venka t r a o

An drew Wa de K en Wiln er D a vid Ya ch

Robert Zeek H a ns Zeller

Foreword xv


16/85


17/85


Introduction

The orga niza tion of th is par t of ISO/IE C 9075 is as follow s:

1) Cla use 1, ‘‘Scope’’, specifi es th e scope of this par t of IS O/IE C 9075.

2) Clause 2, ‘‘Normative references’’, identifies a dditiona l sta nda rds t ha t , t hrough reference in thispart of I nterna tiona l S ta nda rd, consti tute provisions of ISO/IE C 9075.

3) Clause 3, ‘‘Defi nitions a nd use of terms’’, defines t erms used in this an d other part s of ISO/IE C9075.

4) Cla use 4, ‘‘Concepts’’, d escribes th e concepts used in IS O/IE C 9075.

5) Clause 5, summa rises th e content of each of the pa rts of ISO/IEC 9075, in t erms of the conceptsdescribed in Clause 4, ‘‘Concepts’’.

6) Clause 6, defin es notation a nd conventions used in other pa rts of ISO/IEC 9075.

7) Cla use 7, describes the cont ent of an nexes of other par ts of ISO/IE C 9075.

8) Clause 8, specifies requirements t ha t a pply to claims of conformance to al l or some of the pa rtsof I S O/IE C 9075.

9) Annex A, is an informa tive Annex. It describes the formal procedures for maintena nce andint erpret a tion of ISO/IE C 9075.

10) Annex B , ‘‘SQL P ackages’’, is a n informative Annex. It specifi es several packages of SQLl a ngua ge fea t ur es a s i dent i fied i n:

— Appendix F, " SQL feature an d pa ckage t axonomy" , in I SO/IEC 9075-2

— Appendix F, " SQL Fea tur e Ta xonomy" , in I SO/IE C 9075-4

— Appendix F, " SQL feature an d pa ckage t axonomy" , in I SO/IEC 9075-5

to which SQL-implementations may claim conformance.

11) Annex C, ‘‘Implementa tion-defined elements’’, is a n informa tive Annex. It l ists those featuresfor w hich t he body of this part of ISO/IEC 9075 sta tes tha t t he synta x, the meaning, t hereturned results, t he effect on S QL-dat a an d/or schemas, or a ny other behavior is part ly or

wholly implementation-defined.

12) Annex D, ‘‘Implementa tion-dependent element s’’, is a n informat ive Annex. It lists t hose feat uresfor w hich t he body of this part of ISO/IEC 9075 sta tes tha t t he synta x, the meaning, t hereturned results, t he effect on S QL-dat a an d/or schemas, or a ny other behavior is part ly orwholly implementation-dependent.

In the t ext of this part of ISO/IEC 9075, Cla uses begin a new odd-numbered pa ge. Any r esultingblank space is not significant.

Introduction xvii


18/85


19/85

INTERNATIONAL STANDARD ©ISO/IEC ISO/IEC 9075-1:1999 (E)

Informationtechnology — Databaselanguages— SQL —

Part 1:

Fr a mew ork (S QL/Fr a mew ork)

1 Scope

This pa rt of IS O/IE C 9075 describes th e conceptua l fra mework used in other pa rts of ISO/IE C 9075t o s peci fy t he gr a m m a r of S QL a nd t he r es ult of pr ocess i ng s t a t em ent s i n t ha t l a ngua ge b y a nSQL-implementation.

This pa rt of ISO/IE C 9075 also defi nes term s a nd n otat ion used in th e other pa rt s of ISO/IE C 9075.

NOTE 1 – The c oordinat ion of the development of exist ing a nd future sta ndards for the ma nagement of

persistent dat a in informat ion systems is described by the Reference Model of Dat a Man agement (ISO/IE C10032:1995).

Scope 1


20/85


2 Framework (SQL/Framework)


21/85


2 Normativereferences

The following standards contain provisions that, through reference in this text, constitute provisionsof this pa rt of ISO/IE C 9075. At the t ime of publicat ion, t he editions indicated were va lid. Allsta nda rds a re subject to revision, a nd pa rties to a greements ba sed on t his part of ISO/IEC 9075ar e encouraged to investigate the possibili ty of a pplying th e most recent editions of the sta nda rdsindicat ed below. Members of IEC an d ISO ma inta in registers of currently valid Interna tiona lS t a n d a r d s .

1) IS O 8824-1:1995, Information technology — Specification of Abstract Syntax Notation One

(ASN.1) — Part 1: Specification of basic notation

2) IS O/IE C 9075-2:1999, I nformat ion technology — Database languages — SQL — Part 2: F oundation (SQL/ F oundation) .

3) IS O/IE C FD IS 9075-3:1999, I nform ation technology — Database languages — SQL — Part 3: Cal l-Level Interface (SQL/ CLI) .

4) IS O/IE C 9075-4:1999, I nformat ion technology — Database languages — SQL — Part 4: Persistent Stored M odul es (SQL / PSM ) .

5) IS O/IE C 9075-5:1999, I nform ation technology — Database languages — SQL — Part 5: H ost L a n g u a g e B i n d i n g s ( S QL / B i n d i n g s) .

6) IS O/IE C 10646-1:1993, I nformat ion technology — Uni versal M ul t i -Octet Coded Character S et ( U C S ) — P a r t 1 : A r c h i t ect u r e a n d M u l t i l i n g u a l P l a n e .

7) IS O/IE C CD 14651, In formati on t echnology — I ntern ational Str ing Or deri ng — Method for comparing Character Str ings .

8) The Unicode Consortium, T he Un icode Standar d, Version 2.0 , 1996. IS B N 0-201-48345-9.

Normative references 3


22/85




23/85


3 Definitionsanduseof terms

3.1 Definitions

For t he purposes of t his pa rt of ISO/IE C 9075, th e follow ing defi nitions a pply.

3.1.1 Definitions provided in this standard

In this pa rt of ISO/IEC 9075, t he defi nition of a verb defines every voice, mood, a nd tense of tha tverb.

This pa rt of ISO/IE C 9075 defin es th e follow ing t erms, w hich a re a lso used in oth er par ts of IS O/IE C

9075:

a ) atomic: Incapable of being subdivided.

b) compilation unit: A segment of executa ble code, possibly consisting of one or m ore subpro-gr a m s .

c) data type: A set of representa ble values.

d) descriptor: A coded description of a n S QL object. It includes a l l of th e informa tion about t heobject that a conforming SQL-implementation requires.

e) identifier: A means by which something is identified.

f) identify: To properly reference somethin g w ithout a mbiguity.

g) implementation-defined: P ossibly differing between SQL-implementa tions, but specifi ed bythe implementor for each particular SQL-implementation.

h) implementation-dependent: Possibly differing between SQL-implementations, but not spec-ified by IS O/IE C 9075, a nd not required t o be specifi ed by t he implementor for an y particularSQL-implementations.

i) instance (of a value): A physical representa tion of a va lue. Ea ch insta nce is at exactly ones it e . An i ns t a nc e ha s a da t a t y pe t ha t i s t he da t a t y pe of i t s v a l ue.

j) null value: A special value that is used to indicate the absence of any data value.

k) object (as in ‘‘x object’’): A ny t h i n g . An x object is a component of, or is otherwise associatedwith, some x , and cannot exist independently of that x . For example, an SQL object is an objectthat exists only in the context of SQL; an SQL-schema object is an object that exists in someSQL-schema.

l) persistent: Continuing to exist indefin itely, unt i l destroyed deliberat ely. Referential a ndcascaded actions are regarded as deliberate. Actions incidental to the termination of an SQL-transaction or an SQL-session are not regarded as deliberate.

m) property (of an object): An at tribute, qua lity, or cha ra cteristic of t he object.

Definitions and use of terms 5


24/85

ISO/IEC 9075-1:1999 (E) ©ISO/IEC3.1 Definitions

n) row: A sequence of (fi eld na me, value) pairs, the da ta type of each va lue being specified by therow type.

o) scope (of a standard): The cl a us e in t he s t a nda r d t ha t defines t he s ub ject of t he s t a nda r d a nd

the a spects covered, t hereby indicat ing t he l imits of applicabil i ty of the sta nda rd or of particularparts of i t .

p) scope (of a declaration): That part of an SQL-client module, SQL-server module, , SQL routine, or SQL-sta tement in which t he object declared can bereferenced.

q ) sequence: An ordered collection of objects that are not necessarily distinct.

r) site: A place occupied by an instance of a value of some specified data type (or subtype of it).

s) SQL-connection: An association between an SQL-client and an SQL-server.

t ) SQL-environment: The context in which SQL-dat a exists a nd S QL-sta tements a re executed.

u) SQL-implementation: A processor th at processes SQL-sta tements. A conforming SQL- implementation is a n S QL-implementa tion tha t sat isfies th e requirements for S QL-implementa tionsas defined in Clause 8, ‘‘Conformance’’.

v) SQL-session: The context within wh ich a single user, from a single SQL-agent, executes asequence of consecutive SQL -sta tement s over a single SQL -connection.

w ) SQL-statement: A string of characters that conforms, or purports to conform, to the Formatan d S ynta x Rules specifi ed in t he par ts of ISO/IEC 9075.

x) table: A table ha s a n ordered collection of one or more columns a nd a n un ordered collection ofzer o or m or e r ows . E a ch c ol um n ha s a na m e a nd a da t a t y pe. E a ch r ow ha s , for ea c h col umn,exactly one value in the data type of that column.

3.2 Useof terms

The concepts on w hich IS O/IE C 9075 is ba sed a re described in t erms of objects, in t he usua l senseof the word.

Every object has properties , in the usual sense of the word (sometimes called characteristics orat tributes), usua lly including a na me tha t is unique with in some class of object. Some objects aredependent on other objects. If x is an object, then the objects dependent on i t are known as ‘ ‘x objects’’. Thus th e term ‘‘SQL object’’ denotes some object tha t exists only in th e cont ext of S QL.

M a n y x objects might be considered to be components of the x on w hich t hey depend.

I f a n x ceases to exist, then every x object dependent on that x also ceases to exist.

The representation of an x i s known a s a n x descriptor or an x s tate, depending on the nature of x ’s.The descriptor or state of an x represents everything that needs to be known about the x . S ee a l s oSubclause 6.2.4, ‘‘Descriptors’’.



25/85

©ISO/IEC ISO/IEC 9075-1:1999 (E)3.3Informative elements

3.3 Informativeelements

In several places in t he body of ISO/IE C 9075, informa tive notes a ppea r. For example:

NOTE 2 – This is an example of a note.Those notes do not belong t o the n ormat ive part IS O/IE C 9075 a nd conforma nce to ma teria l speci-

fi ed in th ose notes shall not be claimed.

Definitions and use of terms 7


26/85




27/85


4 Concepts

4.1 Caveat

This Clause describes concepts that are, for the most part , specified precisely in other parts ofISO/IEC 9075. In an y case of discrepancy, t he specificat ion in t he other pa rt is to be presumedcorrect.

4.2 SQL-environmentsand their components

4.2.1 SQL-environments

An SQL-environment comprises:

— One S QL-a gent .

— One SQL-implementa tion.

— Zero or more SQL-client modules, cont a ining externa lly-invoked procedures a va ilable to theSQL-agent.

— Zero or more aut horizat ion identifiers.

— Zero or more cat alogs, each of which conta ins one or more SQL-schemas.

— The sites, principally ba se ta bles, tha t conta in SQL-dat a, as described by t he contents of theschemas. This da ta may be thought of as ‘ ‘the da ta base’’, but t he term is not used in I SO/IEC9075, because i t ha s different meanings in the genera l context.

4.2.2 SQL-agents

An SQL-agent i s t h a t whi ch ca us es t he execut i on of S QL-s t a t em ent s . In t he c a s e of t he di -rect invocation of SQL (see Subclause 5.6.4, ‘‘Direct invocation of SQL’’), it is implementation-defin ed. Alternat ively, i t may consist of one or more compilation units tha t , when executed, invokeexterna lly-invoked procedures in a n SQL-client m odule.

4.2.3 SQL-implementations

An SQL-implementation is a processor that executes SQL-statements, as required by the SQL-

agent. An SQL-implementation, as perceived by the SQL-agent, includes one SQL-client, to whichtha t SQL-agent is bound, an d one or more SQL-servers. An SQL-implementa tion can conform toIS O/IE C 9075 with out a llowin g more tha n one SQL-server to exist in a n S QL-environment.

B ecause an SQL-implementa tion can be specifi ed only in t erms of how it executes S QL-sta tements,the concept denotes an instal led insta nce of some softwa re (dat aba se man agement system). ISO/IE C9075 does not distinguish betw een features of t he S QL-implementa tion tha t are determined by t hesoftw ar e vendor a nd t hose determined by the insta l ler.

Concepts 9


28/85

ISO/IEC 9075-1:1999 (E) ©ISO/IEC4.2 SQL-environments and their components

IS O/IE C 9075 recognizes tha t S QL-client a nd S QL-server softwa re ma y ha ve been obta ined fromdifferent vendors; it does not specify the method of communication between SQL-client and SQL-server.

4.2.3.1 SQL-clients

An SQL-client is a processor, perceived by the SQL-agent as part of the S QL-implementa tion, tha testablishes S QL-connections betw een i tself a nd S QL-servers a nd maint ains a diagnostics a rea an dother state data relating to interactions between i tself, the SQL-agent, and the SQL-servers.

4.2.3.2 SQL-servers

E a c h SQ L -ser ver is a processor, perceived by t he S QL-agent as part of the SQL-implementa tion,t ha t m a na ges S QL-da t a .

Each SQL-server:

— Mana ges the SQL-session ta king place over the S QL-connection betw een i tself and the S QL-client.

— Executes SQL-sta tements received from the SQL-client, receiving an d sending da ta as required.

— M a i nt a i ns t he s t a t e of t he S QL-s es s ion, i nc ludi ng t he a ut hor i za t i on i dent i fier a nd cer t a i n

session defaults.

4.2.4 SQL-client modules

An SQL-cl ient module is a module th at is explicit ly created an d dropped by implementa tion-defined

mechanisms.An SQL-client module does not necessarily have a name; i f i t does, the permitted names areimplementation-defined.

An SQL-client module conta ins zero or m ore externa lly-invoked procedures.

Exactly one SQL-client module is associated with an SQL-agent at any t ime. However, in the case

of either direct binding st yle or S QL/CLI , th is ma y be a defau lt S QL-client module wh ose existenceis not apparent to the user.

4.2.5 User identifiers

A user i denti fier represents a user. The means of creating a nd destroying user identifi ers, and their

ma pping to rea l users, is n ot specifi ed by I SO/IE C 9075.

4.2.6 Catalogs and schemas



29/85

©ISO/IEC ISO/IEC 9075-1:1999 (E)4.2SQL-environments and their components

4.2.6.1 Catalogs

A catalog is a n a med collection of SQL-schemas in a n S QL-environment. The mechan isms for

creat ing a nd destroying cata logs a re implementa tion-defined.

4.2.6.2 SQL-schemas

An SQL-schema , often referred t o simply a s a schema , is a persistent, named collection of descrip-tors tha t describe SQL-dat a. Any object wh ose descriptor is in some SQL-schema is known a s a nSQL-schema object.

A schema, the schema objects in i t , and the SQL-data described by them are said to be owned bythe authorization identifier associated with the schema.

SQL-schemas are created and destroyed by execution of SQL-schema statements (or by implementation-defin ed mecha nisms).

4.2.6.3 The Information Schema

Every catalog contains an SQL-schema with the name INFORMATION_SCHEMA that includesthe descriptors of a number of schema objects, mostly view defin itions, t ha t together al low everydescriptor in that catalog to be accessed, but not changed, as though i t was SQL-data.

The data available through the views in an Information Schema includes the descriptors of theInformat ion S chema i tself. It does not include the schema objects or base ta bles of the D efinitionSchema (see Subclause 4.2.6.4, ‘‘The Definition Schema’’).

Each Information Schema view is so specified that a given user can access only those rows of theview tha t represent descriptors on w hich t ha t user ha s privileges.

4.2.6.4 The Definition Schema

The definition schema is a ficti t ious schema with the name DEFINITION_SCHEMA; i f i t were toexist , the SQL-data in i ts base tables would describe al l the SQL-data available to an SQL-server.ISO/IEC 9075 defines i t only in order to use i t as the ba sis for t he views of the I nforma tion Schema.

The structure of the Defi nition S chema is a representat ion of the da ta model of SQL.

4.2.7 SQL-data

S Q L - d a t a i s da t a descr i bed b y S QL-s chem a s — da t a t ha t i s under t he c ont r ol of a n S QL-implementa tion in a n SQL-environment.

4.3 Tables

A t a b l e has a n ordered collection of one or more columns a nd a n unordered collection of zero or morer ows . E a ch c ol um n ha s a na m e a nd a da t a t y pe. E a ch r ow ha s , for ea c h col um n, ex a ct l y one v a l uei n t he da t a t y pe of t ha t col umn.

Concepts 11


30/85

ISO/IEC 9075-1:1999 (E) ©ISO/IEC4.3 Tables

SQL-dat a consists entirely of t able va riables, called base tables . An opera tion t ha t references zeroor more base tables and returns a table is called a query . The result of a query is called a derived t a b l e .

The rows of a table have a type, called ‘ ‘the row type’’; every row of a table has the same row type,which is also the row type of the table. A table that is declared to be based on some structuredtype is called a ‘‘typed t able’’; i ts columns correspond in na me a nd declared type t o the at tributes ofth e str uctured t ype. Typed ta bles ha ve one add itional column, called th e ‘‘self-referencing column’’wh ose type is a reference type a ssociat ed with the st ructured type of the ta ble.

I f a t y ped t a b l e T B 1 ha s a n a s s oci a t ed s t r uc t ur ed t y pe T P1 that is a subtype of some other struc-t ur ed t y pe T P2 , t hen T B 1 can be defined to be a ‘‘subtable’’ of a typed ta ble T B 2 w hose associatedtype is T P2 ; T B 2 is, in this case, a ‘‘supertable’’ of T B 1 .

A view is a named query, which can be invoked by use of this name. The result of such an invocationis called a viewed table .

Some queries, a nd hence some views, a re u p d a t a b l e , m ea ni ng t hey ca n a ppea r a s t a r get s of s t a t e-ments that change SQL-data. The results of changes expressed in this way are defined in terms ofcorresponding changes t o base ta bles.

No two columns of a base table or a viewed table can have the same name. Derived tables, other

than viewed tables, may contain more than one column with the same name.

A base table is either a schema object (its descriptor is in a schema; see Subclause 4.6.6, ‘‘Basetables and their components’’) or a module object (its descriptor is in a module; see Subclause 4.9,‘‘Modules’’). A base table whose descriptor is in a schema is called a created base table , a n d m a y b eeither persistent or temporary (though its descriptor is persistent in either case). A persistent base t a b l e contains 0 (zero) or m ore rows of persistent S QL-da ta . A base ta ble declared in a module ma yonly be tempora ry, a nd is called a declared temporary table .

A temporary table is an SQL-session object tha t ca nnot be a ccessed from a ny other S QL-session. Aglobal temporary table can be a ccessed from a ny a ssociat ed SQL-client module. A local temporary

t a b l e can be accessed only from the module to which it is local.

A temporary table is empty when an SQL-session is init iated and i t is emptied (that is , al l i ts rowsare deleted) either when an SQL-transaction is terminated or when an SQL-session is terminated,depending on its descriptor.

4.4 SQL datatypes

4.4.1 General data type information

Every data value belongs to some data type.

Every data type is either p r ed efin ed , constructed , or u ser -d efin ed . E v er y da t a t y pe ha s a na m e. The

na me of a predefined or constructed da ta type is a r eserved word specified by t ha t pa rt of ISO/IEC9075 that specifies the data type. The name of a user-defined type is provided in i ts definition. Auser-defined data type is a schema object; see Subclause 4.6.4, ‘‘User-defined types’’.

A predefi ned da ta type is a dat a type specified by ISO/IEC 9075, a nd is therefore provided by theSQL-implementa tion. A data type is predefined even th ough the user is required (or al lowed) toprovide certain parameters when specifying it (for example the precision of a number).

A predefined data type is atomic. An atomic type is a data type whose values are not composed ofvalues of other da ta types. The existence of a n operation (SUB STRING , E XTRACT) that is capable

of selecting part of a string or datetime value does not imply that a string or datetime is not atomic.



31/85

©ISO/IEC ISO/IEC 9075-1:1999 (E)4.4 SQL data types

A constructed t ype is either a tomic or composite. A composite type is a dat a type each of w hosevalues is composed of zero or more values, each of a declared data type.

4.4.2 The null value

Every data type includes a special value, called the n u l l v a l u e , sometimes denoted by the keywordNULL. This value differs from other values in the following respects:

— S i nce t he nul l v a l ue i s i n ever y da t a t y pe, t he da t a t y pe of t he nul l v a l ue im pli ed b y t hekeyword NULL cannot be inferred; hence NULL can be used to denote the null value only incertain contexts, rather than everywhere that a l i teral is permitted.

— Although the null value is neither equa l to any other value nor not equa l to any other value —it is u n k n o w n whether or not i t is equal to any given value — in some contexts, multiple nullvalues a re trea ted t ogether; for example, t he treats al l null va lues t ogether.

4.4.3 Predefined types

4.4.3.1 Numeric types

There a re t wo classes of numeric type: exact numeric , which includes integer types and types withspecified precision and scale; and approximate numeric , which is essential ly floating point, and for

which a precision may optionally be specified.

E v er y num b er ha s a precision (number of digits), a nd exact numeric types a lso have a scale (digitsaft er the ra dix point). Arithmetic opera tions ma y be performed on opera nds of different or the sa menumeric type, and the result is of a numeric type that depends only on the numeric type of theoperands. If th e result can not be represented exactly in th e result type, then w hether i t is roundedor trun cated is implementa tion-defined. An exception condition is ra ised i f th e result is outside

the range of numeric values of the result type, or i f the ari thmetic operation is not defined for theoperands.

4.4.3.2 String types

A value of character type is a string (sequence) of chara cters dra wn from some cha ra cter repertoire.The characters in a character string S a r e a l l dr a wn fr om t he s a m e cha r a ct er s et CS . I f S i s t hevalue of some expression E , t hen CS is the character set specified for the declared type of E . Acharacter string type is either of fixed length, or of variable length up to some implementation-defined maximum. A value of character large object (CLOB) type is a string of cha ra cters from somechara cter repertoire and is alw ays a ssociat ed with exactly one cha ra cter set . A cha ra cter largeobject is of va riable length, up to some implementa tion-defined ma ximum tha t is probably grea ter

t ha n t ha t of ot her c ha r a c t er s t r i ngs.

Either a character string or character large object may be specified as being based on a spe-cific character set by specifying CHARACTER SET in the data type; a particular character setchosen by the implementation to be the national character set ma y be specified by specifyingNATIONAL CHARACTER, NATIONAL CHARACTER VARYING, or NATIONAL CHARACTERLARGE OBJ ECT (or one of severa l synta ctic equivalents) as the da ta type.

A value of b i t s t r i n g t y p e is a string of bits (binary digits). A bit str ing type is either of fixed length,or of varia ble length up t o some implementa tion-defined ma ximum.

Concepts 13


32/85

ISO/IEC 9075-1:1999 (E) ©ISO/IEC4.4 SQL data types

A value of b i n a r y s t r i n g t y p e (known a s a binary large object , or BL OB) is a va riable length sequenceof octets, up t o an implementa tion-defined maximum.

4.4.3.3 Boolean type

A value of t he Boolean data type is either t r u e or f a l se . The truth value of u n k n ow n is sometimesrepresented by t he null va lue.

4.4.3.4 Datetime types

There a re t hree datetime types , ea ch of wh ich specifi es values comprising da tetime fi elds.

A value of dat a ty pe TIME STAMP comprises values of th e da tet ime fi elds YEAR (betw een 0001 a nd9999), MONTH, DAY, HOUR, MINUTE and SECOND.

A value of data type TIME comprises values of the datetime fields HOUR, MINUTE and SECOND.

A value of data type DATE comprises values of the datetime fields YEAR (between 0001 and 9999),MONTH and DAY.

A value of DATE is a valid G regorian dat e. A value of TIME is a valid t ime of day.

TIME STAMP a nd TIME ma y be specifi ed w ith a number of (decima l) digits of fra ctiona l secondsprecision.

TIME STAMP a nd TIME ma y a lso be specifi ed a s being WITH TIME ZONE, in w hich case everyvalue has associated with i t a t ime zone displacement. In comparing values of a data type WITHTIME ZONE, the value of th e t ime zone displacement is disregarded.

4.4.3.5 Interval types

A v a l ue of a n interval type represents t he dura tion of a period of t ime. There ar e tw o classes ofintervals. One class, called year-month intervals , has a datetime precision that includes a YEARfi eld or a MONTH fi eld, or both. The other class, called d a y -t i m e i n t er v a l s , ha s a n expr es s orimplied interval precision that can include any set of contiguous fields other than YEAR or MONTH.

4.4.4 Constructed atomic types

4.4.4.1 Referencetypes

A reference type is a predefined data type, a value of which references (or points to) some site

holding a value of the referenced type. The only si tes tha t may be so referenced a re the r ows oftyped ta bles. It follows tha t every referenced type is a structured type.

4.4.5 Constructed composite types



33/85

©ISO/IEC ISO/IEC 9075-1:1999 (E)4.4 SQL data types

4.4.5.1 Collection types

A collection comprises zero or more elements of a specified d at a type known a s th e element type .

An a r r a y is an ordered collection of not necessarily distinct values, whose elements may be refer-enced by t heir ordina l position in the a rra y.

An array type is specified by an array type constructor.

4.4.5.2 Row types

A row type is a sequence of one or more (fi eld na me, dat a type) pairs, known a s fi elds. A value of arow type consists of one value for each of its fields.

4.4.5.3 Fields

A field is a (field name, data type) pair . A value of the field is a value of i ts data type.

4.5 Sitesandoperationson sites

4.5.1 Sites

A si t e is a pla c e t ha t hol ds a n i nst a nce of a v a l ue of a s peci fied da t a t y pe. E v er y s it e ha s a de-fi ned degree of persistence, independent of i ts dat a type. A site th at exists unt i l deliberatelydestroyed is said to be persistent . A site t ha t necessarily ceases t o exist on completion of a com-pound SQL-statement, at the end of an SQL-transaction, or at the end of an SQL-session is said tobe temporar y . A si te tha t exists only for a s long a s necessary t o hold an argument or returned valueis said to be tr ansient .

As indicat ed a bove, the principal kind of persistent or t empora ry si te is the ba se ta ble. A basetable is a special kind of si te, in that constraints can be specified on i ts values, which the SQL-implement a tion is req uired t o enforce (see Subcla use 4.6.6.3, ‘‘Ta ble const ra int s’’).

Some sites may be referenced by their names — for example, base tables and SQL variables (seeIS O/IE C 9075-4). Some sites ma y be referenced by a REF value. A site occupied by a n element ofan array may be referenced by i ts element number.

4.5.2 Assignment

The instance at a si te can be changed by the operation of assignment . Assignment replaces thei ns t a nce a t a s it e (known a s t he target ) with a new instan ce of a (possibly different) value (known

a s t h e source value). Assignment has no effect on the reference value of a site, if any.

4.5.3 Nullability

E v er y s it e ha s a n u l l a b i l i t y ch a r a ct er i st i c , which indicates whether i t may contain the null value(is possibly nul lable ) or not (is k n o w n n o t n u l l a b l e ). Only t he c ol um ns of b a s e t a b l es m a y b econstrained to be known not nullable, but columns derived from such columns may inherit thecharacteristic.

A base table cannot be null , though i t may have zero rows.

Concepts 15


34/85

ISO/IEC 9075-1:1999 (E) ©ISO/IEC4.6 SQL-schema objects

4.6 SQL-schema objects

4.6.1 General SQL-schemaobject information

An S QL-schema object ha s a descriptor. The descriptor of a persistent ba se ta ble describes apersistent object that has a separate, though dependent, existence as SQL-data. Other descriptorsdescribe SQL objects that have no existence distinct from their descriptors (at least as far asIS O/IE C 9075 is concerned). Hence t here is no loss of precision if, for exa mple, th e t erm ‘‘a ssertion’’is used when ‘‘assertion descriptor’’ would be more strictly correct.

Every schema object has a name that is unique within the schema among objects of the name classto w hich i t belongs. The na me classes a re:

— B a s e t a b l es a n d v ie w s.

— Domains and user-defined types.

— Table constraints, domain constra ints, and assertions.

— SQL-server modules.

— Triggers.

— SQL-invoked routines (specifi c nam es only, w hich ar e not required t o be specifi ed explicitly, but

if not are implementation-dependent).

— C h a r a c t er se t s.

— C oll a t i ons .

— Tran slations.

Certain schema objects have named components whose names are required to be unique within theobject to which they belong. Thus columns are uniquely named components of base tables or views,attributes are uniquely named components of structured types, and fields are uniquely namedcomponents of row types.

Some schema objects may be provided by t he S QL-implementa tion an d can be neither creat ed nordropped by a user.

4.6.2 Descriptors relating tocharacter sets

4.6.2.1 Character sets

A char acter set i s a na m ed s et of cha r a ct er s (character repertoire ) that may be used for formingv a l ues of t he cha r a ct er da t a t y pe. E v er y cha r a ct er s et ha s a default col lation . C h a r a c t er s et sprovided by t he SQL-implementat ion, w hether defi ned by other sta nda rds or by t he implementa tion,are represented in the Information Schema.

When char acters a re conta ined entirely within an SQL-implementa tion, the methods for encodingthem an d for collecting t hem into strings ar e implementa tion-dependent. When chara cters are

excha nged w ith host progra ms or other ent i t ies outside of t he SQL-implementa tion, the char actersets a lso have an encoding, w hich specifies t he bits used to represent each chara cter, a nd a form-of- u se , which specifies the scheme used to collect characters into strings.



35/85

©ISO/IEC ISO/IEC 9075-1:1999 (E)4.6 SQL-schema objects

This In terna tiona l S ta nda rd uses the phrases ‘ ‘chara cter set ’’ and ‘‘chara cter repertoire’’ inter-changeably except when referring to data exchanged outside the SQL-implementation, when‘‘character set’’ is understood to include an encoding and a form-of-use in addition to a character

repertoire.

Every character set supported by an SQL-implementation comprises only characters that arerepresented a nd expressible using I SO/IE C 10646 U TF-16, t he ‘‘U CS Tra nsforma tion F ormat forP lanes of G roup 00’’. This representat ion is the canonical representat ion of cha ra cters a nd chara cters t r i ngs in t hi s Int er na t i ona l S t a nda r d.

NOTE 3 – ISO/IE C 10646 supports ‘‘private use’’ cha ra cters, wh ich ar e expressible using U TF-16. Fut ureeditions of ISO/IE C 10646 ar e likely to add more chara cters tha t ar e expressible using U TF-16. Such

c harac ters a re na tura l ly permit ted in chara c ter sets supported by an SQL-implementat ion.

4.6.2.2 Collations

A collation , a l so known a s a collating sequence , is a named operation for ordering character strings

in a particular cha ra cter repertoire. Ea ch collation is defined for exactly one chara cter set .A site declared with a character data type may be specified as having a collation, which is treateda s pa r t of i t s da t a t y pe.

Every collation shall be derived from a collation defined by an International Standard such asISO/IEC 14561 or by a Nat iona l St an dar d, or sha ll be an implementa tion-defin ed collat ion.

4.6.2.3 Translations

A translation is a named operation for mapping from a character string of some character set intoa chara cter st ring of a given, not necessarily distinct, cha ra cter set . The operat ion is performed byinvocation of an external function identified by the name of the translation. Since an entire string

is passed to this function and a string returned, the mapping is not necessarily from one characterto one chara cter, but ma y be from a sequence of one or more cha racters t o an other sequence of oneor more chara cters.

4.6.3 Domains and their components

4.6.3.1 Domains

A d o m a i n i s a na m ed us er -defined object t ha t ca n b e s peci fied a s a n a l t er na t i ve t o a da t a t y pe,wherever a data type can be specified. A domain consists of a data type, possibly a default option,an d zero or more (domain) constraints.

4.6.3.2 Domain constraints

A domain constraint applies to every column that is based on that domain, by operating as a tableconstraint for each such column.

Domain constra ints a pply only t o columns ba sed on the associated domain.

A domain constraint is applied to any value resulting from a cast operation to the domain.

Concepts 17


36/85


4.6.4 User-definedtypes

4.6.4.1 Structured types

A structured type is a na med, user-defin ed dat a type. A value of a structured type comprises anumber of attr ibute values . E a c h a t t r i b ut e of a s t r uc t ur ed t y pe ha s a da t a t y pe, s pec i fied b y a na t t r i b u t e t y pe tha t is included in the descriptor of the structured t ype.

Attribute values are said to be encapsul ated ; that is to say, they are not directly accessible to theuser, i f at al l . An a ttr ibute value is a ccessible only by invoking a function known as an observ er function that returns that value. An instance of a structured type can also be accessed by a locator .

A s t r uct ur ed t y pe m a y b e defi ned t o b e a subtype of another structured type, known as i ts direct

supertype . A s ubt y pe i n h e r i t s every attribute of i ts direct supertype, and may have additionalattributes of i ts own. An expression of a subtype may appear anywhere that an expression of anyof its supertypes is allowed (this concept is known as su b st i t u t a b i l i t y ). Moreover, t he va lue of a n

expression ma y be a value of an y subtype of th e declared t ype of t he expression.One or m or e b a s e t a b les ca n b e cr ea t ed b a s ed on a s t r uct ur ed t y pe. A ba s e t a b l e b a s ed on astructured type ST can be a subtable of a base ta ble based on a supertype of ST .

4.6.4.2 Attributes

An a t t r i b u t e is a na m ed c om ponent of a s t r uc t ur ed t y pe. I t ha s a da t a t y pe a nd a defa ul t v a l ue.

4.6.5 Distinct types

A d ist inct type is a us er -defined da t a t y pe t ha t i s b a s ed on s om e da t a t y pe ot her t ha n a di st i nct

type. The values of a distinct type a re represented by th e values of the type on w hich i t is based.

An argument of a distinct type can be passed only to a parameter of the same distinct type. Thisallows precise control of what routines can be invoked on arguments of that data type.

4.6.6 Basetablesand their components

4.6.6.1 Basetables

A base tabl e is a site th a t holds a ta ble value (see Subclause 4.3, ‘‘Ta bles’’). All SQL-da ta is held in

base tables.

I f a b a s e t a b l e is b a s ed on a s t r uct ur ed t y pe, i t m a y b e a subtable of one or m ore other base t ables

t h a t a r e it s supertables .

4.6.6.2 Columns

A colu mn i s a na m ed com ponent of a t a b l e. I t ha s a da t a t y pe, a defa ul t , a nd a nul la b i li t y cha r a c-teristic.



37/85

©ISO/IEC ISO/IEC 9075-1:1999 (E)4.6 SQL-schema objects

4.6.6.3 Tableconstraints

A tabl e constrain t is an integrity constraint associated with a single base table.

A table constraint is either a unique constraint , a pr imary key constraint , a referential constraint , ora check constraint .

A unique constraint specifies one or more columns of the table as u n i q u e col u m n s . A u n iq u e

cons t r a i nt i s s a t i s fied i f a nd onl y i f no t wo r ows i n a t a b le ha v e t he s a m e non-nul l va l ues i n t heunique columns.

A primary key constraint is a unique constraint that specifies PRIMARY KEY. A primary keycons t r a i nt i s s a t i s fied i f a nd onl y i f no t wo r ows i n a t a b le ha v e t he s a m e non-nul l va l ues i n t heunique columns a nd none of t he va lues in the specified column or columns a re th e null value.

A referential constra int specifies one or more columns as referencing columns and correspondingreferenced columns in some (not necessarily distinct) base ta ble, referred to a s t he referenced table .Such referenced columns are the unique columns of some unique constraint of the referenced table.

A referential constraint is always satisfied i f , for every row in the referencing table, the values ofthe referencing columns are equal to those of the corresponding referenced columns of some row inthe r eferenced ta ble. If n ull values are present, however, sa tisfaction of the referential constra intdepends on t he trea tment specified for nulls (known as the match type ).

Referential actions may be specified to determine what changes are to be made to the referencingtable i f a change to the referenced table would otherwise cause the referential constraint to beviolated.

A table check constraint specifies a search condition . The constra int is violat ed i f the result of thesearch condition is false for an y r ow of the ta ble (but n ot i f i t is unknown).

4.6.6.4 Triggers

A trigger , though not defined to be a component of a base table, is an object associated with asingle base t able. A trigger specifies a trigger event , a tr igger action t ime , and one or more t r iggered actions .

A trigger event specifies what action on the base table shall cause the triggered actions. A triggerevents is either I NSE RT, DE LE TE, or U P DATE.

A trigger action t ime specifies whether the triggered action is to be taken BEFORE or AFTER thetrigger event.

A triggered a ction is either a n SQL procedure sta tement or B EG IN ATOMIC, followed by one ormore s terminated with s, followed by END.

4.6.7 View definitions

A view (strictly, a view definition ) is a na m ed q uer y, t ha t m a y for m a ny pur pos es b e us ed i n t hes a m e w a y a s a b a s e t a b l e. I t s v a l ue is t he r es ult of ev a l ua t i ng t he q uer y. S ee a l s o S ubcl a us e 4.3,‘‘Ta ble s’’.

Concepts 19


38/85


4.6.8 Assertions

An assertion is a check constraint. The constra int is violated i f the result of the search condition isfalse (but not if it is unknown).

4.6.9 SQL-server modules(defined in ISO/IEC 9075-4,SQL/PSM)

An SQL-server module is a module that is a schema object. See Subclause 4.9, ‘‘Modules’’.

4.6.10 Schemaroutines

A schema routine is an SQL-invoked routine th a t is a schema object. See Su bclaus e 4.10, ‘‘Routines’’.

4.6.11 PrivilegesA pr i vi l ege represents a grant, by some grantor, to a specified grantee (which is either an authoriza-

tion identifier, a role, or PUBLIC), of the authority required to use, or to perform a specified actionon, a specifi ed schema object. The specifi a ble actions a re: SE LE CT, INS ERT, UP DATE, D EL ETE,R E F E R E N C E S , US A G E , UN D E R , TR IG G E R , a nd E XE C UTE .

A privilege w i t h g r a n t o p t i on a ut hori zes t he gr a nt ee t o gr a nt t ha t pr iv il ege t o ot her gr a nt ees, w i t hor without the grant option.

A S ELE CT privilege w i t h h i er a r ch y op t i o n a ut oma t i ca l l y pr ov ides t he gr a nt ee wi t h S E LE C Tprivileges on al l subtables, both existing and any that may be added in the future, on the table onwh ich t he privilege is gra nted.

Every possible grantee is authorized by privileges granted to PUBLIC. SELECT with grant option

is granted to PUBLIC for every schema object in the Information Schema.

An authorization identifier who creates a schema object is automatically granted al l possibleprivileges on i t , w ith gran t option.

Only a n aut horizat ion identifi er w ho ha s some privilege on a schema object is a ble to discover i tsexistence.

4.6.12 Roles

A r o l e is a collection of zero or more role authorizations.

A role authori zati on permits a grantee (see Subclause 4.6.11, ‘‘Privileges’’) to use every privilege

gra nted t o the role. It also indicat es whether the role auth orizat ion is WITH ADMIN OPTION, inwhich case the grantee is authorized to grant the role.

4.7 Integrityconstraintsandconstraint checking



39/85

©ISO/IEC ISO/IEC 9075-1:1999 (E)4.7Integrity constraints and constraint checking

4.7.1 Constraint checking

There are t wo kinds of schema object tha t describe constraints: assertions and ta ble constra ints(including domain constraints of any domains on which columns of that table may be based), andthey are checked in the same way.

Every constra int is ei ther deferrable or not deferrable .

In every SQL-session, every constra int ha s a constraint mode that is a property of that SQL-session.Each constraint has a (persistent) default constraint mode, with which the constraint starts each

SQL-transaction in each SQL-session.

A constraint mode is either deferred or i m m e d i a t e , and can be set by an SQL-statement, providedthe constraint is deferra ble.

When a transaction is init iated, the constraint mode of each constraint is set to i ts default .

On completion of execution of every SQL-statement, every constraint is checked whose constraintmode is immediate.

Before termination of a transaction, every constraint mode is set to immediate (and thereforechecked).

4.7.2 Determinismandconstraints

Expression evaluation results ma y be non-deterministic. For example, in the case of a columnwhose data type is varying character string, the value remaining after the elimination of duplicatesma y be different on different occasions, even though t he da ta is the same. This can occur becausethe number of trai l ing spaces may vary from one duplicate to another, and the value to be retained,aft er th e duplicat es ha ve been eliminated, is n ot specified by IS O/IEC 9075. Hence, t he length ofthat value is non-deterministic. In such a case, the expression, and any expression whose value

is derived from it , is said to be possibly non-deterministic (‘‘possibly’’, because it may be that allSQL-agents that ever update that column may remove trai l ing spaces; but this cannot be known tothe SQL-implementation).

B ecause a constra int t ha t contains a possibly non-deterministic expression might be satisfi ed at onetime, yet fa i l at some later t ime, no constra int is permitted to conta in such an expression.

A routine may claim to be deterministic; i f i t isn’t , then the effect of invoking the routine isimplementation-dependent.

4.8 Communication between an SQL-agent and anSQL-implementation

4.8.1 Host languages

An SQL-implementa tion can communicat e successfully with an SQL-agent only i f th e lat ter con-forms to t he sta nda rd for some progra mming langua ge specified by ISO/IEC 9075. Such a langua geis known generically as a host language , and a conforming SQL-implementation is required tosupport at least one host language.

Concepts 21


40/85

ISO/IEC 9075-1:1999 (E) ©ISO/IEC4.8 Communication between an SQL-agent and an SQL-implementation

There a re severa l methods of communicat ing, known as b inding styles .

— The SQL -client module binding st yle (specifi ed in I SO/IE C 9075-2). In th is binding st yle,

the user, using an implementation-defined mechanism, specifies a module to be used as an

SQL-client module.

— The Ca ll-Level Int erface (specifi ed in I SO/IE C 9075-3). In th is case, th e SQL-agent invokes one

of a number of standard routines, passing appropriate arguments, such as a character stringwhose value is some SQL-sta tement.

— Embedded SQL (specifi ed in I SO/IEC 9075-5). In this case, S QL-sta tements a re coded into theapplicat ion progra m; a n implementa tion-dependent mecha nism is then used to:

• Genera te from each SQL-sta tement an externally-invoked procedure. These procedures arecollected together into a module, for subsequent use as an SQL-client module.

• Replace each SQL-sta tement with a n invocation of the externa lly-invoked procedure gener-

ated from it .

— Direct invoca tion of SQL (specifi ed in I SO/IE C 9075-5). Direct invoca tion is a method of ex-

ecuting SQL-statements directly, through a front-end that communicates directly with theuser.

IS O/IE C 9075-2 specifi es t he a ctions of a n externa lly-invoked procedure in a n SQL-clien

[ansi-iso-iec, 1999] database language sql. part 1. sql-framework. sql99

Documents