Quick Reference SQL en SQL*Plus

Leerboek Oracle SQL 11g/12c

Appendix B

Quick Reference SQL en SQL*Plus In deze bijlage is een quick reference opgenomen van SQL*Plus en SQL. De Oracledocumentatie bevat (naast de uitgebreide en volledige naslagwerken) ook quick references, zowel voor SQL als voor SQL*Plus, maar deze bijlage A is nog veel beknopter. Volledigheid is dan ook niet nagestreefd. Anderzijds komen in deze bijlage ook constructies en commando’s voor die niet in dit boek zijn behandeld. Alle SQL*Plus-commando’s, en onderdelen daarvan, mogen worden afgekort tot iedere lengte, zolang ondubbelzinnigheid gewaarborgd is. Omwille van de leesbaarheid zijn deze mogelijkheden niet steeds expliciet weergegeven met blokhaken. Er staat bijvoorbeeld COMPUTE, terwijl het eigenlijk COMP[UTE] zou moeten zijn, omdat we het SQL*Plus COMPUTE commando mogen afkorten tot COMP. SQL-commando’s moeten we daarentegen wèl altijd volledig ingeven.

Syntax-conventies Element:

Betekenis:

CREATE

Voer deze commando-onderdelen letterlijk in (vetgedrukte hoofdletters)

char

Alfanumerieke constante tussen enkele aanhalingstekens, of een expressie van het datatype CHAR

txt

Alfanumerieke constante, met of zonder quotes

d

Datum, of een expressie van het datatype DATE

expr

Willekeurige expressie

m,n

Willekeurige getallen of numerieke expressies

cond

Expressie die TRUE of FALSE oplevert (conditie)

query

Query binnen een ander statement (subquery)

Element:

Voorbeeld:

Betekenis:

Blokhaken

[NOWAIT]

Optioneel onderdeel; voer de haken zelf niet in

Underline

[ASC|DESC]

Geeft een default-waarde aan die wordt gebruikt als we zelf geen keuze maken

Accolades

{expr|expr}

Lijst van keuzemogelijkheden, gescheiden door verticale strepen; kies één van de items (voer de accolades en/of streep zelf niet in).

Drie puntjes

expr[,...]

Voorafgaande item mag een willekeurig aantal malen worden herhaald

Gewone haakjes (deze dus), komma’s, punten, aanhalingstekens ('), apenstaartjes (@), en eventuele andere exotische karakters hebben geen bijzondere betekenis in deze syntax-beschrijvingen; ze moeten dan ook letterlijk worden ingevoerd.

© Academic Service, Den Haag

1


Appendix B

Starten en stoppen van SQL*Plus SQLPLUS -HELP SQLPLUS [-SILENT] [/NOLOG|naam[/wachtwoord]] [@script] script::= bestand[.ext] [arg1 arg2...] CONNECT naam[/wachtwoord][@database_spec] Log vanuit SQL*Plus aan onder gegeven naam/wachtwoord. PASSWORD

Verander je wachtwoord (voordeel: geen echo naar scherm). DISCONNECT

Commit transactie, verlaat database, maar blijf in SQL*Plus. {EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n][COMMIT|ROLLBACK] Verlaat de database en SQL*Plus; default met een COMMIT.

Commando’s ingeven en uitvoeren / Voer het SQL-commando uit dat in de SQL-buffer staat. RUN Idem; laat tevens het commando zelf zien. HOST [commando]

Voer één operating system commando uit zonder SQL*Plus te verlaten; zonder argument wordt een subsessie gestart.

De SQL*Plus-editor (niet beschikbaar in iSQL*Plus) A[PPEND] txt Voeg txt toe aan het eind van de actuele regel in de buffer; gebruik twee spaties om txt van het voorgaande te scheiden. C[HANGE] /oud[/[nieuw[/]]] Verander oud op de actuele regel in nieuw; ieder niet-alfabetisch karakter is toegestaan als separator in plaats van /. DEL [n[m]] Verwijder één of meer bufferregels; default alleen de actuele regel. I[NPUT] [tekst] Voeg nieuwe regel(s) toe onder de huidige regel in de SQL-buffer. L[IST] [n[m]] Geef één of meer bufferregels weer; voor n of m kan ook worden gebruikt: LAST (laatste regel) * (huidige regel) LIST zonder argumenten geeft alle regels in de buffer weer.


2


Appendix B

Manipuleren met SQL*Plus-scripts ED[IT] [bestand[.ext]] Start een externe editor op de inhoud van bestand.ext, of op de inhoud van de SQL-buffer (zonder argument). GET bestand[.ext] [LIST|NOLIST] Haal de inhoud van een bestand in de SQL-buffer. REM[ARK] [txt] Commentaar; txt (tot het eind van de regel) wordt genegeerd. SAVE bestand[.ext] [CREATE|REPLACE|APPEND] Bewaar de inhoud van de SQL-buffer in bestand.ext. STORE SET bestand[.ext] [CREATE|REPLACE|APPEND] Bewaar de huidige SQL*Plus-settings in bestand.ext. {START|@|@@} {url|script[.ext]} [arg1 arg2 ...] Voer het script uit (default-extensie is SQL); eventuele argumenten worden gesubstitueerd voor &1, &2, enzovoort. WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n] [COMMIT|ROLLBACK] |CONTINUE [COMMIT|ROLLBACK|NONE]} Geef aan wat SQL*Plus moet doen als er een foutsituatie in het operating systeem ontstaat. WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n] [COMMIT|ROLLBACK] |CONTINUE [COMMIT|ROLLBACK|NONE]} Geef aan wat SQL*Plus moet doen als een SQL-commando een foutsituatie creëert.

Commando’s om interactie te regelen ACCEPT x [NUMBER|CHAR|DATE] [DEFAULT default][FORMAT format] [PROMPT txt|NOPROMPT][HIDE] Vraag om invoer, en wijs gegeven waarde toe aan variabele x. DEFINE [x[=txt]] Wijs de waarde txt toe aan variabele x; geef de waarde van x of van alle gedefinieerde variabelen. PAUSE [txt] Geef een lege regel of een regel met txt, en wacht op [Enter]. PROMPT [msg] Schrijf de boodschap msg (of een lege regel) naar het scherm. UNDEFINE x Verwijder de gedefinieerde variabele x.


3


Appendix B

Bindvariabelen, substitutievariabelen, en parameters PRINT [var] Geef de waarde van bindvariable var weer, of alle bindvariabelen. VARIABLE [var {NUMBER|CHAR|...} Declareer een bindvariabele var. &n Refereer naar een parameter in een SQL*Plus-script; &1 naar het eerste argument, &2 naar het tweede, etc. &var, &&var Refereer naar de waarde van de variabele var; als var ongedefinieerd is vraagt SQL*Plus om een waarde; bij & gebeurt dat iedere keer opnieuw; bij && slechts éénmaal (&& is een impliciete DEFINE). .

(punt) scheidingsteken tussen de naam van een variabele en onmiddellijk daaropvolgende tekst (het concat-karakter).

Formatteren van query-resultaten BREAK [ON element [actie [actie]]]... element::= {kolom|expr|ROW|REPORT} actie::= [SKIP n|PAGE] [NODUPLICATES|DUPLICATES] Specificeer waar en hoe de formattering dient te veranderen (bijvoorbeeld: regel overslaan bij verandering van kolomwaarden); BREAK zonder argumenten geeft de huidige break-definitie; BREAK overschrijft impliciet een bestaande break-definitie. BTITLE [spec [txt|var]...] | [OFF|ON] Definieer een voetregel voor iedere pagina, of geef de huidige BTITLE-instelling; zie verder TTITLE. CLEAR {BREAKS|COMPUTES|TIMING|BUFFER|COLUMNS|SCREEN|SQL} Reset de gegeven optie. COLUMN [{kolom|expr} [optie...]] Raadpleeg of wijzig kolom-instellingen wat betreft de schermweergave. Zonder argumenten: kolom (of expr): kolom optie:

Informatie over alle kolommen; Instellingen van die kolom; Kolom-instellingen veranderen.

Dit zijn de belangrijkste COLUMN opties: ALIAS alias

LIKE {expr|alias}

CLEAR

NEWLINE

ENTMAP (ON|OFF}

NEW_VALUE var

FOLD_AFTER

NOPRINT|PRINT

FOLD_BEFORE

NULL char

FORMAT fmt

OLD_VALUE var


4


Appendix B

HEADING txt

ON|OFF

JUSTIFY {L|C|R}

WRAP|WORD_WRAP|TRUNC

Met FORMAT fmt kan het volgende worden geregeld: Element:

Voorbeeld:

Omschrijving:

An

A10

Beeld alfanumerieke kolom af op breedte n

9

9999

Getalbreedte zonder voorloopnullen

0

0999

Getalbreedte met voorloopnullen

9990

Nul i.p.v. een spatie als de waarde nul is

$

$9999

Dollarteken voor een getal

B

B9999

Geef nul weer met een spatie

MI

9999MI

Minteken achter negatieve getallen

PR

9999PR

Negatieve getallen tussen

S

S9999

Een + voor positieve, - voor negatieve getallen

D

99D99

Decimale karakter op deze positie

G

9G999

Duizendtallen-separator op deze positie

C

C999

Het ISO currency-symbool

L

L999

Het lokale currency-symbool (NLS)

,

9,999

Komma op de aangegeven positie

.

99.99

Decimale punt op de aangegeven positie

V

999V99

Vermenigvuldig getal met 10n, waarbij n het aantal negens is na de V

EEEE

9.999EEEE

Wetenschappelijke notatie

RN,rn

RN

Romeinse cijfers (in hoofd- of kleine letters)

DATE

DATE

Datum in MM/DD/YY formaat

COMPUTE [functie... [LABEL txt] OF kolomspec [kolomspec]... ON locatie [locatie]...] kolomspec::= {expr|kolom|alias} locatie::= {kolomspec|REPORT|ROW} Voer berekeningen uit, of geef huidige compute-instellingen; behalve door NUMBER worden null-waarden genegeerd.

Functie:

Berekent:

Op datatype:

AVG

Gemiddelde

Numeriek

COUNT

Aantal waarden

Alle types

MAXIMUM

Maximumwaarde

Numeriek, alfanumeriek


5


Appendix B

MINIMUM

Minimumwaarde

Numeriek, alfanumeriek

NUMBER

Aantal rijen (alle rijen!)

Alle types

STD

Standaarddeviatie

Numeriek

SUM

Som van de waarden

Numeriek

VARIANCE

Variantie

Numeriek

REPFOOTER [PAGE] [printspec [txt|var]...]|[OFF|ON] REPHEADER [PAGE] [printspec [txt|var]...]|[OFF|ON] printspec:== {COL n|SKIP [n]|TAB n |FORMAT tekst|BOLD|LEFT|CENTER|RIGHT} SPOOL [bestand[.ext][CREATE|REPLACE|APPEND]|OFF|OUT] Kopieer schermuitvoer naar een bestand. OFF OUT SPOOL

Stop spooling; Stuur het bestand tevens naar de (default) printer; Zonder argumenten: geef de huidige status.

TTITLE [spec [txt|var]...] | [OFF|ON] spec::= {COL n|BOLD|TAB n|SKIP [n]|LEFT|RIGHT|CENTER|FORMAT txt} Specificeer een kopregel, of geeft de huidige instelling.

Overige SQL*Plus-commando’s DESCRIBE [naam.]object Geef de kolomdefinities van het gespecificeerde object. SET systeemvariabele waarde Geef een waarde aan een SQL*Plus-systeemvariabele; de belangrijkste variabelen zijn: APPINFO {ON|OFF|txt]

RECSEP {WRAPPED|EACH|OFF}

AUTOCOMMIT {OFF|ON|IMM|n}

RECSEPCHAR c

COLSEP txt

SCAN {OFF|ON}

CONCAT {.|c|OFF|ON}

SHOWMODE {OFF|ON}

DEFINE {&|c|OFF|ON}

SPACE {1|n}

DESCRIBE [DEPTH{1|n|ALL}]...

SQLBLANKLINES {ON|OFF}

ECHO {OFF|ON}

SQLCASE {MIXED|LOWER|UPPER}

EDITFILE naam[.ext]

SQLCONTINUE {>|txt}

ESCAPE {\|c|OFF|ON}

SQLNUMBER {OFF|ON}

FEEDBACK {6|n|OFF|ON}

SQLPREFIX {#|c}

HEADING {OFF|ON}

SQLPROMPT {SQL>|txt}

INSTANCE [pad|LOCAL]

SQLTERMINATOR {;|c|OFF|ON}


6


Appendix B

LINESIZE {80|n}

SUFFIX {SQL|txt}

LONG {80|n}

TAB {OFF|ON}

MARKUP HTML {ON|OFF} ...

TERMOUT {OFF|ON}

NEWPAGE {1|n|NONE}

TIME {OFF|ON}

NULL txt

TIMING {OFF|ON}

NUMFORMAT fmt

TRIMSPOOL {OFF|ON}

NUMWIDTH {10|n}

UNDERLINE {-|c|ON|OFF}

PAGESIZE {14|n}

VERIFY {OFF|ON}

PAUSE {OFF|ON|txt}

WRAP {OFF|ON}

AUTOTRACE {OFF|ON|TRACEONLY}[EXPLAIN][STATISTICS] SHOW [systeemvariabele|USER|TTITLE|BTITLE|ERRORS|SPOOL|ALL] Geef de gevraagde waarde of instelling, of alle instellingen (ALL).

SQL: datamanipulatie (DML), transacties en queries COMMIT [WORK] Voer wijzigingen door en sluit de transactie af. DELETE FROM {tabelnaam|(tabelexpr)} [WHERE conditie] Verwijder rijen die voldoen aan conditie; verwijder alle rijen als geen conditie is gespecificeerd. INSERT INTO {tabelnaam|(tabelexpr)}[(kolom,...)] {VALUES(expr,...)|query} Voeg nieuwe rijen toe met behulp van waarden of een query. ROLLBACK [TO SAVEPOINT sp_naam] Annuleer alle mutaties die zijn uitgevoerd sinds savepoint sp_naam, of annuleer de huidige transactie en sluit hem af. SAVEPOINT sp_naam Markeer een savepoint in een transactie; dit is een punt waarnaar een ROLLBACK (zie hierboven) kan worden uitgevoerd. [WITH naam AS subquery [, naam AS subquery]... SELECT [DISTINCT] select_expr[,...] FROM tabel_expr[,...] [WHERE conditie] [GROUP BY [CUBE|ROLLUP|GROUPING SETS] expr[,...] [HAVING conditie]] [{UNION [ALL]|INTERSECT|MINUS} query] [ORDER BY {expr|c_alias} [ASC|DESC][NULLS {FIRST|LAST}][,...] select_expr::= {*|t_alias.*|expr|scalar_subquery [[AS] c_alias]} tabel_expr::= {[user.]tabel|(subquery)|join_expr} [t_alias] join_expr::= [t1 JOIN t2 {USING (kolomlijst)|ON conditie} |t1 {NATURAL|CROSS} JOIN t2


7


Appendix B

|t1 [LEFT|FULL|RIGHT] OUTER JOIN t2 {USING...|ON...} ] Raadpleeg een of meer tabellen of views; retourneert rijen en kolommen; kan ook als onderdeel (subquery) binnen andere commando’s voorkomen. TRUNCATE tabelnaam Verwijder op efficiënte wijze alle rijen van een tabel, en geef gealloceerde ruimte terug; ROLLBACK hiervan is niet mogelijk (TRUNCATE is een DDL-commando) UPDATE {tabelnaam|(tabelexpr)} [t_alias] SET {col=expr[,...] | (col[,...])=(subquery)} [WHERE conditie] Wijzig kolomwaarden in rijen die voldoen aan conditie; wijzigt alle rijen als geen WHERE-clausule wordt gespecificeerd.

SQL: datadefinitie (DDL) ALTER SEQUENCE sequence_naam [INCREMENT BY n] [MAXVALUE n|NOMAXVALUE] [MINVALUE n|NOMINVALUE] [ORDER|NOORDER][CYCLE|NOCYCLE] Wijzig de definitie van een sequence. ALTER TABLE [user.]tabelnaam [RENAME TO nieuwe_naam] [RENAME COLUMN oude_naam TO nieuwe_naam] [ADD ({col_element|tab_constr}[,...])] [MODIFY (col_element[,...])] [DROP COLUMN col [CASCADE CONSTRAINTS]] [{DROP|DISABLE|ENABLE} constr]... [SHRINK SPACE [CASCADE]] Herdefinieer de structuur van een tabel. COMMENT ON {TABLE t_naam|COLUMN c_naam} IS txt Zet toelichting bij een tabel, view of kolom in de datadictionary. CREATE [UNIQUE|BITMAP] INDEX i_naam ON t_naam(c_naam {ASC|DESC}[,...]) [NOSORT] Creëer een index op de genoemde kolom(men) van een tabel. CREATE [PUBLIC] SYNONYM s_naam FOR [user.]object[@database] Creëer een synoniem voor een tabel of view; publieke synoniemen zijn beschikbaar voor alle gebruikers. CREATE SEQUENCE sequence_naam [START WITH n] [INCREMENT BY n] [MAXVALUE n|NOMAXVALUE] [MINVALUE n|NOMINVALUE] [ORDER|NOORDER][CYCLE|NOCYCLE] Creëer een sequence om volgnummers te genereren. CREATE TABLE t_naam ({col_def|tab_constr}[,...]) [AS query] Creëer een tabel; definieert kolommen en constraints.


8


Appendix B

col_def::= kolomnaam datatype [DEFAULT expr] [inl_constr] inl_constr: tab_constr:

Declaratie van een inline constraint Declaratie van een tabel-constraint

DEFAULT expr

Default-waarde voor deze kolom

Oracle ondersteunt de volgende datatypes: CHAR[(n)]

Vaste lengte string (1<=n<=2000; default n=1)

VARCHAR[2](n[BYTE|CHAR]) Variabele lengte string (n<=4000) uitgedrukt in bytes of in karakters CLOB

Tekst “large object”, max 4GB * database block size

BLOB

Binair “large object”, max 4GB * database block size

BFILE

Locator naar een extern binair bestand, max 4GB

DATE

Datum tussen 01-jan-4712 BC en 31-dec-9999 AD

TIMESTAMP(sp) [WITH [LOCAL] TIMEZONE]

Tijdstip met sp cijfers achter de (seconden)komma

INTERVAL YEAR(jp) TO MONTH

Tijdsinterval, met jp cijfers voor het aantal jaren

INTERVAL DAY(dp) TO SECOND(sp)

Tijdsinterval, met dp cijfers voor het aantal dagen en sp cijfers achter de (seconden)komma

LONG

Variabele lengte string (maximaal 2GB) (Verouderd datatype; niet meer gebruiken)

RAW(n)

Binaire data tot maximaal n bytes (n<=2000)

LONG RAW

Binaire data; verder als LONG (maximaal 2GB)

NUMBER(p[,s])

Getal met maximaal p cijfers, waarvan maximaal s rechts van de decimale punt (1<=p<=38; -84<=s<=127; default s=0)

NUMBER(*,s)

Hetzelfde als NUMBER(38,S)

BINARY_FLOAT

32-bits drijvende komma getal

BINARY_DOUBLE

64-bits drijvende komma getal

CREATE [OR REPLACE][FORCE] VIEW v_naam [(c_alias[,...])] AS query [WITH CHECK OPTION|WITH READ ONLY] DROP INDEX index_naam DROP SEQUENCE seq_naam DROP [PUBLIC] SYNONYM synoniem_naam DROP TABLE tabel_naam [PURGE][CASCADE CONSTRAINTS] DROP VIEW view_naam GRANT {obj_priv[,...]|ALL} ON object TO {user[,...]|rol[,...]|PUBLIC} [WITH GRANT OPTION]


9


Appendix B

Geef een gebruiker of rol privileges op een database-object. RENAME oud TO nieuw Geef een tabel, view of synoniem een nieuwe naam. REVOKE {obj_priv[,...]|ALL} ON object FROM {user[,...]|rol[,...]|PUBLIC} Herroep object-privileges van een gebruiker of rol.

SQL: overige commando’s ALTER SESSION SET parameter=waarde Verander bepaalde sessieparameters, zoals de NLS-instellingen. SET CONSTRAINTS {ALL|constraint[,…]} {IMMEDIATE|DEFERRED} Geef aan wanneer constraintsbewaking moet plaatsvinden. SET TRANSACTION {READ ONLY|READ WRITE| ISOLATION LEVEL {SERIALIZABLE|READ COMMITTED}} Dwing read-consistency op transactieniveau af. /* vrije tekst */ -- vrije tekst Commentaar; mag overal in een SQL-commando voorkomen. (-- werkt tot het einde van de regel)

SQL: operatoren De volgende tabellen geven de Oracle-operatoren weer in afnemende precedentie; gebruik van haakjes ( ) in expressies kan de precedentie beïnvloeden. Rekenkundige operatoren: +

-

Prefix positieve/negatieve expressie (unair: met één operand)

*

/

Vermenigvuldigen en delen

+

-

Optellen en aftrekken

Alfanumerieke operatoren: ||

Concateneren (aan elkaar plakken) van strings

Vergelijkingsoperatoren: =

Is gelijk aan

!=

^=

<>

>

>=

Is groter dan, is groter dan of gelijk aan

<

<=

Is kleiner dan, is kleiner dan of gelijk aan

Is ongelijk aan

IN

Is gelijk aan een waarde uit een verzameling (of subquery)

NOT IN

Komt niet voor in ...

ANY

Geldt voor minstens een waarde uit ...


10


Appendix B

ALL

Geldt voor alle waarden uit ...

BETWEEN x AND y

Groter dan of gelijk aan x en kleiner dan of gelijk aan y

EXISTS

Waar, als de subquery minstens één rij oplevert

LIKE [ESCAPE 'x']

Voldoet aan een zoekpatroon met wildcards: '%' staat voor 0, 1 of meer willekeurige karakters; '_' staat voor precies één willekeurig karakter; x schakelt de betekenis van een wildcard uit

IS NULL

Bevat een null-waarde

Logische operatoren: NOT

Ontkenning van een logische expressie

AND

Combineert twee logische expressies; het geheel is waar als beide expressies waar zijn

OR

Combineert twee logische expressies; het geheel is waar als minstens één expressie waar is

Verzamelings-operatoren: UNION[ALL]

(Vereniging) combineert twee queries; geeft de rijen die uit de ene of uit de andere query voortkomen; ALL behoudt duplicaatrijen

INTERSECT

(Doorsnede) geeft alle rijen die uit de ene èn uit de andere query voortkomen

MINUS

(Verschilverzameling) geeft alle rijen die wèl uit de eerste, maar niet uit de tweede query voortkomen

Overige operatoren: (+) *

Indicator van een outerjoin-kolom (verouderde syntax) Selecteer alle kolommen van een tabel of view

DISTINCT

Elimineer duplikaatrijen uit een query-resultaat

CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ... [ELSE rn] END

Als expr gelijk is aan v1 dan r1, ..., en anders rn

CASE WHEN c1 THEN r1 WHEN c2 THEN r2 ... [ELSE rn] END

Als c1 waar is dan r1, ..., en anders rn

SQL: functies Numerieke functies: ABS(n)

Absolute waarde van n

BITAND(n,m)

Logische AND over de binaire representatie van n en m

CEIL(n)

Kleinste gehele getal groter dan of gelijk aan n

COS(n),COSH(n),ACOS(n) Cosinus, cosinus hyperbolicus, arc cosinus EXP(n)


e tot de n-de macht

11


Appendix B

FLOOR(n)

Grootste gehele getal kleiner dan of gelijk aan n

LN(n),LOG(m,n)

(Natuurlijke) logaritme uit n

MOD(m,n)

Rest na deling van m door n (afgekapt met TRUNC)

POWER(m,n)

m tot de n-de macht

REMAINDER(m,n)

Rest na deling van m door n (afgerond met ROUND)

ROUND(n[,m])

n afgerond op m posities (default m = 0)

SIGN(n)

Teken; als n < 0, n = 0, n > 0: respectievelijk -1, 0, 1

SIN(n),SINH(n),ASIN(n) Sinus, sinus hyperbolicus, arc sinus SQRT(n)

Wortel uit n; als n < 0: NULL

TAN(n),TANH(n), ATAN(n),ATAN2(n,m)

Tangens, tangens hyperbolicus, arc tangens

TRUNC(n[,m])

n afgekapt op m posities (default m = 0)

WIDTH_BUCKET (e,min,max,nb)

Histogram: bucket waarin e valt als we het interval van min tot max verdelen in nb buckets

Alfanumerieke functies die een alfanumerieke waarde retourneren: CHR(n)

Karakter met ASCII-waarde n

CONCAT(c1,c2)

Concateneer c1 en c2

INITCAP(char)

Ieder woord beginnend met een hoofdletter

LOWER(char)

Alle letters omgezet naar kleine letters

LPAD(c1,n,c2)

c1 wordt links aangevuld tot lengte n met karakters van c2 (default c2 is een spatie)

LTRIM(char[,set])

Karakters worden verwijderd tot het eerste karakter dat niet in set voorkomt (default set is een spatie)

REGEXP_REPLACE (c1,p[,...])

Zoek in c1 naar de reguliere expressie p; retourneer de positie in c1

REGEXP_SUBSTR (c1,p[,...])

Zoek in c1 naar de reguliere expressie p; retourneer de substring zelf

REPLACE(char,s,r)

Ieder vóórkomen van s in char vervangen door r; zonder r worden alle voorkomens van s verwijderd

RPAD(c1,n,c2)

c1, rechts aangevuld tot lengte n met de karakters van c2 (default c2 is een spatie)

RTRIM(char[,set])

Karakters na het laatste karakter dat niet in set voorkomt worden verwijderd (default is een spatie)

SUBSTR(char,m,n)

Substring van char vanaf positie m, n karakters lang (zonder n: tot het einde)

TRANSLATE(char,f,t)

char, vertaald van karakterset f naar karakterset t

TRIM([... FROM] src)

Verwijder karakters aan het begin en/of het eind van src


12


Appendix B

UPPER(char)

Letters in char omgezet naar hoofdletters

Alfanumerieke functies die een numerieke waarde retourneren: ASCII(char)

ASCII-waarde eerste karakter van char

INSTR (c1,c2,[,n[,m]])

Positie m-de vóórkomen van c2 in c1; zoeken vanaf positie n (default voor m en n is 1)

LENGTH(char)

Lengte van char in karakters

REGEXP_INSTR (c1,p,[,...])

Positie van de reguliere expressie p in c1

Groepsfuncties: AVG(n)

Gemiddelde

CORR(expr1,expr2) Correlatiecoëfficient COUNT({*|expr})

Aantal rijen waarin expr niet NULL is (* telt alle rijen)

MAX(expr)

Maximum waarde

MEDIAN(expr)

Middelste waarde (mediaan)

MIN(expr)

Minimum waarde

SUM(n)

Som

STATS_MODE(expr)

Waarde die het meeste voorkomt

STDDEV(n)

Standaarddeviatie

VARIANCE(n)

Variantie

[DISTINCT]

Alle groepsfuncties kennen deze toevoeging; verschillende waarden worden éénmaal meegenomen

Datumfuncties: ADD_MONTHS(d,n)

Datum d plus n maanden

CURRENT_DATE

Huidige datum, met tijdzone informatie

CURRENT_TIMESTAMP[(p)] Huidige tijdstip (met precisie p) met tijdzone informatie EXTRACT(c FROM d)

Extraheer de component c uit een datum of tijdsinterval d

LAST_DAY(d)

De laatste dag van de maand waarin d valt

LOCALTIMESTAMP[(p)]

Huidige tijdstip (met precisie p) zonder tijdzone informatie

MONTHS_BETWEEN(d,e)

Verschil in maanden tussen de datums d en e

NEXT_DAY(d,char)

Datum van de eerste weekdag gespecificeerd door char, die gelijk is aan of later valt dan datum d

NUMTODSINTERVAL(n,u)

Zet n om naar INTERVAL DAY TO SECOND

ROUND(d[,fmt])

Datum d, afgerond op de eenheid fmt

SESSIONTIMEZONE

Tijdzone van de huidige sessie

SYSDATE

Huidige systeemdatum/tijd

SYSTIMESTAMP

Systeemdatum, met exact tijdstip en tijdzone

TRUNC(d[,fmt])

Datum d afgekapt op de eenheid fmt


13


Appendix B

(default ROUND en TRUNC fmt = 'DD') Conversiefuncties: ASCIISTR(txt)

Zet niet-ascii karakters in txt om naar \xxxx formaat

BIN_TO_NUM(e1[,e2,...])

Conversie van binair naar decimaal

CAST(a AS b)

Conversie van a naar datatype b

COMPOSE('txt')

Converteer txt naar Unicode, bijvoorbeeld om samengestelde tekens te produceren

CONVERT(c1,dks[,bks])

Zet c1 om van karakterset bks naar dks

DECOMPOSE('txt')

Converteer txt (in Unicode) om samengestelde tekens te ontleden

TO_BINARY_DOUBLE(e[,fmt])

Conversie van e naar dubbele precisie drijvende komma getal

TO_BINARY_FLOAT(e[,fmt])

Conversie naar drijvende komma getal

TO_CHAR(expr[,fmt])

Conversie van expr naar CHAR in het formaat gespecificeerd door fmt *)

TO_CLOB(c1)

Conversie naar CLOB (Character Large Object)

TO_DATE(char[,fmt])

Conversie van CHAR naar een datum volgens formaat fmt; als char in het default-datumformaat is mag fmt weggelaten worden

TO_DSINTERVAL(c1)

Converteer c1 naar INTERVAL DAY TO SECOND

TO_LOB(lc)

Converteer lc van LONG [RAW] naar een LOB type

TO_NUMBER(char)

Conversie van CHAR naar NUMBER

TO_TIMESTAMP(char[,fmt])

Conversie naar een timestamp

TO_TIMESTAMP_TZ(char[,fmt]) Conversie naar een timestamp met tijdzone informatie TO_YMINTERVAL(char)

Conversie naar INTERVAL YEAR TO MONTH

*) Er is een default datumformaat, en getallen worden standaard precies breed genoeg om alle significante cijfers weer te kunnen geven. Overige functies, met in de tweede kolom wat ze retourneren: COALESCE(a,b,c,...)

Het eerste niet-null argument

DECODE(x,s1,r1 [,s2,r2,]... [default])

r1 als x=s1, r2 als x=s2, ... en anders default

DUMP(expr[,...])

Dump interne informatie: data type code, lengte in bytes, en interne representatie

GREATEST(expr[,...])

expr met de grootste waarde

LEAST(expr[,...])

expr met de kleinste waarde

LNNVL(conditie)

Retourneert TRUE als de conditie FALSE of UNKNOWN oplevert, en anders FALSE

NULLIF(expr1,expr2)

NULL als expr1=expr2 en anders expr1


14


Appendix B

NVL(expr1,expr2)

expr2 als expr1 NULL is, en anders expr1

NVL2(expr1,expr2,expr3)

expr2 als expr1 niet NULL is, en anders expr3

ORA_HASH(expr[,m[,s]])

Bereken een hash-waarde voor expr, gebruik makend van m buckets en een seed-waarde s

SYS_CONNECT_BY_PATH(...) Pad tot de “root” (alleen voor hiërarchische queries) SYS_CONTEXT('ns','p')

Waarde van de systeemparameter p van de ns (name space) context. Een bekende name space is 'USERENV'

UID

Nummer van de (huidige) gebruiker

USER

Naam van de (huidige) gebruiker

VSIZE(expr)

Aantal bytes dat expr intern in beslag neemt

Datumformaatmodellen voor TO_CHAR en TO_DATE Elke combinatie van de volgende elementen is toegestaan als fmt-argument voor de functies TO_CHAR en TO_DATE: [S]CC

Eeuw; 'S' geeft een BC-datum een minteken

[S]YYYY

Jaar; 'S' als hierboven

YYY,YY,Y

Laatste 3, 2, of 1 cijfers van het jaartal

RR

Laatste twee cijfers van het jaartal (met interpretatie voor jaartallen in de 20ste eeuw)

IYYY,IYY,IY,I

ISO jaartal; laatste 3, 2, of 1 cijfers van het ISO jaartal

[S]YEAR

Jaar, voluit gespeld; 'S' als hierboven

BC,AD

BC/AD indicator (B.C. en A.D. mag ook)

Q

Kwartaal (1, 2, 3, 4; Januari - Maart = 1)

MM

Maand (01-12; Januari = 01)

MONTH

Maandnaam, met spaties aangevuld tot lengte 9

MON

Maandnaam, drieletterige afkorting

RM

Maand in romeinse cijfers

WW,W

Jaarweeknummer (1-52); maandweeknummer (1-5)

DDD,DD,D

Dag van het jaar (1-366); van de maand (1-31); van de week (1-7)

DAY

Dagnaam, met spaties aangevuld tot lengte 9

DY

Dagnaam, drieletterige afkorting

DS,DL

Datum in korte (S) of lange (L) representatie; reageert op de parameters NLS_TERRITORY en NLS_LANGUAGE

J

Juliaanse datum (dag sinds 31-12-4713 BC)

TS

Tijdstip van de dag; reageert op instellingen van NLS_TERRITORY en NLS_LANGUAGE

AM,PM

AM/PM indicator (A.M. en P.M. mag ook)

HH[12],HH24

Uur van de dag (1-12 of 0-23)


15


Appendix B

MI

Minuten (0-59)

SS,SSSSS

Seconden (0-59); seconden sinds middernacht (0-86399)

FF[n]

Fractie van seconden, met n cijfers achter de komma

TZD,TZH,TZM,TZR

Tijdzone informatie: zomertijd, uren, minuten, en tijdzone

/ . , ; :

Leestekens worden afgedrukt zoals gespecificeerd

"tekst"

tekst wordt precies afgedrukt zoals gespecificeerd

De volgende prefix (FM) en suffixes (TH, SP) kunnen worden toegevoegd: FM

(Fill-Mode) Onderdruk aanvulling met spaties; gebruik FM nogmaals om terug te switchen (aan/uit)

TH

Ordinaalgetal (4e)

SP

Uitgespeld getal (vier)

SPTH, THSP

Uitgespeld ordinaalgetal (vierde)

Getalformaatmodellen voor TO_CHAR Elke combinatie van de volgende elementen is toegestaan als fmt-argument voor de functie TO_CHAR: Element:

Voorbeeld:

Betekenis:

9

'9999'

Aantal negens bepaalt de lengte

0

'0999'

Getal wordt met voorloopnullen afgebeeld

RN, rn

'RN'

Getal in romeinse cijfers (hoofd- of kleine letters)

$

'$9999'

Zet een dollarteken voor het getal

C

'C9999'

Zet ISO currency symbool voor het getal

L

'L9999'

Zet het lokale currency symbool voor het getal

U

'U9999'

Zet Euro (of ander dual currency) symbool voor het getal

B

'B9999'

Geef het getal nul met een spatie weer

MI

'9999MI'

Zet een minteken achter negatieve waarden

PR

'9999PR'

Zet negatieve waarden tussen de haken < en >

S

'S999' '999S'

Zet plusteken of minteken voor of achter het getal (alleen toegestaan aan het begin of eind van een getal)

,

'9,999'

Geef een komma op deze positie

G

'9G999'

Geef het groepscheidingsteken op deze positie (afhankelijk van NLS_NUMERIC_CHARACTER)

.

'99.99'

Geef een decimale punt op deze positie

D

'99D99'

Geef het decimale karakter op deze positie (afhankelijk van NLS_NUMERIC_CHARACTER)

V

'999V99'

Maal 10n; n is het aantal negens na de V

X

'XXXX'

Geef het getal hexadecimaal weer


16


Appendix B

EEEE

'9.99EEEE'

Wetenschappelijke notatie

TM

'TM'

Kortst mogelijke notatie; schakelt bij 64 karakters over naar wetenschappelijke notatie

DATE

'DATE'

Conversie Juliaanse datum naar mm/dd/yy


17

Quick Reference SQL en SQL*Plus

Recommend Documents