Overblog Suivre ce blog
Administration Créer mon blog
20 juillet 2012 5 20 /07 /juillet /2012 22:03

 

TRANSLATE Fonction.

Comment utiliser la fonction PL SQL TRANSLATE dans Oracle.

La fonction TRANSLATE transforme, remplace et retourne une expression avec toutes les occurrences de la chaîne1 remplacées par les caractères en position correspondantes de la chaîne2.

TRANSLATE syntaxe.

 
 TRANSLATE(expression, chaîne1, chaîne2) 

Fonction sql Translate

 

Fonction sql TRANSLATE par l'exemple.

- Si la chaîne2 comporte moins de caractères que la chaîne1 alors les caractères non compensées de chaîne1 sont retirés de expression.

 SQL> SELECT TRANSLATE('DBA ORxxACLE','#x', ' ') AS TRANS FROM dual; TRANS ---------- DBA ORACLE 

Donc ici pour remplacer les deux x mais surtout pour tronquer les 2 espaces blanc, on place volontairement un caractère # (caractères non présent dans DBA ORxxACLE) afin que le caractère x non compensées de chaîne1 soit retirés de expression, sinon le résultat serait le suivant :

 SQL> SELECT TRANSLATE('DBA ORxxACLE','x', ' ') AS TRANS FROM dual; TRANS ------------ DBA OR ACLE 

 


- Si la chaîne1 comporte moins de caractères que la chaîne2 alors les caractères non compensées de chaîne2 sont ignorés.

 SQL> SELECT TRANSLATE('DBA ORACLE','DBA', 'dba oracle') AS TRANS FROM dual; TRANS ---------- dba ORaCLE SQL> 

Ici la fonction translate remplace :
- les D par des d
- les B par des b
- les A par des a
- puis s'arrête, les caractères " oracle" de la chaîne2 sont ignorés.

 


- Exemple basic avec TRANSLATE.

 SQL> SELECT TRANSLATE('orAcle_dAtAbAse','Aod_', 'aOD ') AS TRANS FROM dual; TRANS --------------- Oracle Database SQL> 

Haut de Page www.dba-ora.fr

Repost 0
Published by Oracle SQL - dans FUNCTIONS - SQL
commenter cet article
18 juillet 2012 3 18 /07 /juillet /2012 21:40

 

RPAD - LPAD Fonction.

Comment utiliser la fonction SQL RPAD oracle.
Comment utiliser la fonction SQL LPAD oracle.

La fonction RPAD (Right PAD) réplique à la droite d'une valeur, autant de fois que nécessaire une chaîne d'une longueur définie en utilisant un caractère spécifié.
De même pour la fonction LPAD (Left PAD) qui elle réplique à gauche une chaîne d'une longueur définie en utilisant un caractère spécifié.

RPAD - LPAD syntaxe.

 
 RPAD(expr1 , i [, expr2 ]) LPAD(expr1 , i [, expr2 ]) 

Fonction RPAD et LPAD

 

Les fonctions chaînes de caractères RPAD et LPAD prennent 3 arguments, où expr1 et expr2 sont des chaines de caractères et i un nombre entier.

La fonction RPAD retourne la chaîne de caractères expr1 augmentée en longueur de i caractères, en utilisant expr2 pour remplir l'espace à droite de expr1.
La fonction LPAD retourne la chaîne de caractères expr1 augmentée en longueur de i caractères, en utilisant expr2 pour remplir l'espace à gauche de expr1.

Si la longueur de expr1 est plus grande que i alors la fonction RPAD et LPAD retourne la partie de expr1 qui correspond a une longueur égale à i, la chaîne est donc tronquée à i caractères.
Si vous ne spécifiez pas expr2 alors le caractère de remplissage est un espace.

RPAD dans les requêtes sql.

 
 SQL> SELECT RPAD('www.dba-ora.fr',20,'#') AS Droite FROM dual; DROITE -------------------- www.dba-ora.fr###### SQL> SELECT RPAD('www.dba-ora.fr',20) AS Droite FROM dual; DROITE -------------------- www.dba-ora.fr Ici RPAD a placé 6 espaces à droite de expr1. SQL> SELECT RPAD('www.dba-ora.fr',11,'#') AS Droite FROM dual; DROITE ----------- www.dba-ora Ici expr1 est tronquée à 11 caractères. 

 


LPAD dans les requêtes sql.

 
 SQL> SELECT LPAD('www.dba-ora.fr',20,'#') AS Gauche FROM dual; GAUCHE -------------------- ######www.dba-ora.fr SQL> SELECT LPAD('www.dba-ora.fr',20) AS Gauche FROM dual; GAUCHE -------------------- www.dba-ora.fr Ici LPAD a placé 6 espaces à gauche de expr1. SQL> SELECT LPAD('www.dba-ora.fr',11,'#') AS Gauche FROM dual; GAUCHE ----------- www.dba-ora Ici expr1 est tronquée à 11 caractères. 

Haut de Page www.dba-ora.fr

Repost 0
Published by Oracle SQL - dans FUNCTIONS - SQL
commenter cet article
14 juillet 2012 6 14 /07 /juillet /2012 18:29

 

SQL RANK.

Comment utiliser la fonction analytique RANK.
Comment utiliser la fonction d'agrégation RANK.

La fonction RANK renvoie le rang d'une valeur dans un groupe de valeurs.
La fonction RANK peut être utilisée en tant que fonction d'agrégation ou fonction analytique.

Fonction RANK en fonction d'Agrégation.

 
 RANK(list) WITHIN GROUP (ORDER BY col_list[ASC|DESC] [NULLS {first|last}]) 

Jeu d'essai pour la fonction RANK.

 ENAME SAL DEPTNO ---------- ---------- ---------- SMITH 800,5 10 ALLEN 1600 30 WARD 1250 30 JONES 2975 30 MARTIN 1250 40 DANIEL 1000 10 JOHN 1600 30 

 

 
 SQL> SELECT RANK(2000) WITHIN GROUP (ORDER BY sal DESC) "Rank of 2000" FROM emp; Rank of 2000 ------------ 2 SQL> 

Ici la fonction RANK calcule le rang d'un salaire hypothétique identifié par l'argument de la fonction RANK.
Un salaire de 2000 en cas d'embauche par exemple serait le deuxième plus gros salaire de ce jeu d'essai.

 


Fonction RANK en fonction Analytique.

 
 RANK() OVER ( [ query_partition_clause] ORDER BY clause ); 

OVER indique qu'on utilise une fonction analytique.

 
 SQL> SELECT deptno, ename, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) "Rank" FROM emp; DEPTNO ENAME SAL Rank ---------- ---------- ---------- ---------- 10 DANIEL 1000 1 10 SMITH 800,5 2 30 JONES 2975 1 30 JOHN 1600 2 30 ALLEN 1600 2 30 WARD 1250 4 40 MARTIN 1250 1 7 ligne(s) sélectionnée(s). SQL> 

RANK par département, si au moins deux lignes sont liées pour un rang, chacune d'entre elles reçoit le même RANK (JOHN et ALLEN).
La fonction RANK ne retourne pas toujours des entiers consécutifs contrairement à la fonction DENSE_RANK (WARD devrait avoir le RANK 3 et non pas le RANK 4 dans le département 30).

Fonction DENSE_RANK en fonction Analytique.

 
 SQL> SELECT deptno, ename, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) "Rank" FROM emp; DEPTNO ENAME SAL Rank ---------- ---------- ---------- ---------- 10 DANIEL 1000 1 10 SMITH 800,5 2 30 JONES 2975 1 30 JOHN 1600 2 30 ALLEN 1600 2 30 WARD 1250 3 40 MARTIN 1250 1 7 ligne(s) sélectionnée(s). SQL> 

La fonction DENSE_RANK retourne des entiers consécutifs maintenant.

La pseudo-colonne ROWNUM, si elle est utilisée dans une vue, peut fournir un classement, mais ne donnerait pas d'égalité sur le RANK des valeurs dupliquées et ne sautera pas les valeurs en double.

Haut de Page www.dba-ora.fr

Repost 0
Published by Oracle SQL - dans FUNCTIONS - SQL
commenter cet article
8 juin 2012 5 08 /06 /juin /2012 21:19

 

fonction sql oracleSQL ORACLE FUNCTION.

Les fonctions sql oracle listées sont toutes accompagnées d'une description et d'exemple testés sous oracle 10G.

 

Oracle Numeric Function.

  1. CEIL.
  2. FLOOR.
  3. ROUND.
  4. SIGN.
  5. TRUNC.

Oracle Date Function.

  1. ADD_MONTHS.
  2. EXTRACT.
  3. LAST_DAY.
  4. MONTHS_BETWEEN.
  5. NEXT_DAY.
  6. SYSDATE.

Oracle Conversion Function.

  1. TO_CHAR.
  2. TO_DATE.
  3. TO_NUMBER.

Oracle Character Function.

  1. INSTR - INSTRB.
  2. LENGTH.
  3. LTRIM.
  4. REPLACE.
  5. TRANSLATE.
  6. RTRIM.
  7. SUBSTR - SUBSTRB.
  8. TRIM.
  9. CONCAT.
  10. RPAD - LPAD.

Oracle Function.

  1. DECODE.
  2. GREATEST.
  3. LEAST.
  4. NVL - NVL2.
  5. COALESCE.

Oracle Group Function.

  1. COUNT.
  2. MAX.
  3. MIN.
  4. SUM.
  5. RANK.

 

 

 

 


 

Priorité des Opérateurs Arithmétiques dans Oracle.

* / + -
Dans une expression arithmétique contenant plusieurs Opérateurs, la multiplication ( * ) et la division ( / ) ont la priorité et sont évaluées en premier.
Si deux opérateurs de même priorité sont dans une expression arithmétique, alors ils seront évalués de la gauche vers la droite.
L'utilisation de parenthèses force l'ordre des priorités dans l'évaluation de l'expression arithmétique.

10*2+10 = 30;
2+10*10 = 102;
(10*2)+10 = 30;
10*(2+10) = 120;

 


Single Row Number Functions

Function

Description Function

ABS(n) ABS renvoie la valeur absolue de n
ACOS(n) ACOS renvoie l'arc cosinus de n en radians
ASIN(n) ASIN renvoie l'arc sinus de n en radians
ATAN(n) ATAN renvoie la tangente à l'arc de n en radians
ATAN2(n,m) ATAN2 renvoie l'arc tangente de n et m en radians
BITAND(n,m) BITAND calcule une opération bit à bit AND sur les bits de n et m , qui tous deux doivent être un nombre entier positif ou nul , et retourne un entier
CEIL(n) CEIL renvoie le plus petit entier supérieur ou égal à n
COS(n) COS renvoie le cosinus de n ( ou n est exprimé en radians )
COSH(n) COSH renvoie le cosinus hyperbolique de n
EXP(n) EXP renvoie une valeur élevée à la puissance n
FLOOR(n) FLOOR retourne le plus grand nombre entier égal ou inférieur à n .
LN(n) LN renvoie le logarithme naturel de n , où n est supérieur à 0
LOG(m,n) LOG retourne le logarithme base m de n
MOD(m,n) MOD renvoie le reste de m divisé par n
POWER(m,n) POWER retourne m élevé à la puissance n
ROUND (m[,n]) ROUND returns m rounded to n places to the right of the decimal point
SIGN(n) SIGN renvoie le signe de n ( –1 si négatif, 1 si positif, 0 si 0)
SIN(n) SIN renvoie le sinus de n ( ou n est exprimé en radians )
SINH(n) SINH renvoie le sinus hyperbolique de n
SQRT(n) SQRT renvoie la racine carrée de n
TAN(n) TAN renvoie la tangente de n ( ou n est exprimé en radians )
TANH(n) TANH renvoie la tangente hyperbolique de n
TRUNC (m[,n]) Truncate. Returns m truncated to n places. Where n is omitted, it returns the integer value of m.
WIDTH_BUCKET (exp,min,max,num) WIDTH_BUCKET construit des histogrammes, dans lequel le range histogramme est divisé en intervalles de taille identiques

 


Character Single Row Functions

Function

Description Function

CHR (n) Returns the character whose binary value is n. Accepts USING NCHAR_CS clause
CONCAT (char1,char2) Combines two strings, char1 and char2
INITCAP(char) Returns char with the first character of each word in char capitalized
LOWER(char) Returns char with all characters converted to lowercase
LPAD(char1,n[,char2]) Returns char1 padded on the left to width n with character sequence in char2. Default padding is a single blank (space).
LTRIM(char[,set]) Returns char with initial characters in set removed from the left. Default set is a blank character (space).
NLS_INITCAP(char[,nlsparam]) Returns char with the first character of each word in char capitalized. Accepts an NLS parameter.
NLS_LOWER(char[,nlsparam]) Returns char with all characters converted to lowercase. Accepts an NLS parameter.
NLSSORT(char[,nlsparam]) Returns language specific sort of char. Accepts an NLS parameter.
NLS_UPPER(char[,nlsparam]) Returns char with all characters converted to uppercase. Accepts an NLS parameter.
REPLACE(char[,searchstring[,replacestring]]) Returns char with searchstring replaced by replacestring. Where replacestring is omitted or null, all instances of searchstring are removed. Where searchstring is omitted or null, char is returned.
RPAD(char1,n[,char2]) Returns char1 padded on the right to width n with character sequence in char2. Default padding is a single blank (space).
RTRIM(char[,set]) Returns char with initial characters in set removed from the right. Default set is a blank character (space).
SOUNDEX(char) Returns the phonetic equivalent of char. Allows for searches for words that sound alike but are spelled differently.
SUBSTR(string,n[,m])]])
SUBSTRB - bytes]])
SUBSTRC - unicode]])
SUBSTR2 - UCS2 codepoints]])
SUBSTR4 - UCS4 codepoints
Returns the substring of string, starting at position n, for a length of m (or to the end of string if m is not present)
TRANSLATE(char,from,to) Returns char, with all occurrences of characters in the from string replaced with the corresponding character in the to string. If to is shorter than from, then from characters without a corresponding to character will be removed. Empty to returns NULL, not an empty string.
TREAT(exp AS [[REF] [schema.]] type) Changes the declared type of exp to type
TRIM([[LEADING|TRAILING|BOTH] [trimchar]FROM]source) Returns source with leading and/or trailing trimchars removed. Default trimchar is a blank space, default action is to remove both leading and trailing blank spaces.
UPPER (char) Returns char with all characters converted to uppercase
ASCII (char) Returns the number value of the first character of char
INSTR(str,substr[,pos[,occur]])
INSTRB - bytes]])
INSTRC - unicode]])
INSTR2 - UCS2 codepoints]])
INSTR4 - UCS4 codepoints
In string function. Returns the position of the occurrence occur of substr in str, starting at pos. Default for pos and occur is 1. If pos is negative, search works backwards from the end of str.
LENGTH (char)]])
LENGTHB - bytes]])
LENGTHC - unicode]])
LENGTH2 - UCS2 codepoints]])
LENGTH4 - UCS4 codepoints
Returns the length of char

 


Date Single Row Functions

Function

Description Function

ADD_MONTHS(d,n) Returns the date d plus n months. If d is the last day of the month, or d+n would be past the end of the month, returns the last day of the month.
CURRENT_DATE Returns the current Gregorian date as datatype DATE, in the session specific time zone
CURRENT_TIMESTAMP [(precision)] Returns the current date and time as datatype TIMESTAMP WITH TIME ZONE, in the session specific time zone. Precision defaults to 6 places.
DBTIMEZONE Returns the time zone of the database
EXTRACT (datetime FROM expr) datetime can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, or TIMEZONE_ABBR, and expr can be either an internal value or datetime value expression
FROM_TZ(timestamp, time_zone) Returns timestamp converted to a TIMESTAMP WITH TIME ZONE value, using time_zone
LAST_DAY(date) Returns the date of the last day of the month containing date
LOCALTIMESTAMP [(precision)] Returns the current date and time of the session in datatype TIMESTAMP of precision
MONTHS_BETWEEN(date1, date2) Returns the number of months between date1 and date2
NEW_TIME(date,zone1,zone2) Returns date converted from time zone zone1 to zone2. NLS_DATE_FORMAT must be set to a 24-hour format.
NEXT_DAY(date,weekday) Returns the next weekday later than date where weekday is the day of the week or its abbreviation
NUMTODSINTERVAL (n, char) Returns n converted to an INTERVAL DAY TO SECOND literal. char can be 'DAY,' 'HOUR,' 'MINUTE,' or 'SECOND,' or an expression that resolves to one of those
NUMTOYMINTERVAL (n, char) Returns n converted to an INTERVAL YEAR TO MONTH literal. char can be 'MONTH' or 'YEAR' or an expression that resolves to one of those
ROUND (date[,fmt]) Returns date rounded to the nearest unit specified by the format model fmt. Defaults to the nearest day.
SESSIONTIMEZONE Returns the time zone of the current session, either as a time zone offset or a time zone region name, depending on the format used for the most recent ALTER SESSION statement
SYS_EXTRACT_UTC (datetz) Extracts the UTC value of datetz where datetz is a datetime with time zone displacement
SYSDATE Returns the current date and time
SYSTIMESTAMP Returns the system timestamp in TIMESTAMP WITH TIME ZONE datatype
TO_DSINTERVAL(char [nlsparm]) Converts char to an INTERVAL DAY TO SECOND type
TO_TIMESTAMP (char[,fmt[nlsparm]]) Converts char to datatype of TIMESTAMP. fmt specifies the format of char if other than the default for datatype TIMESTAMP
TO_TIMESTAMP_TZ (char[,fmt[nlsparm]]) Converts char to datatype of TIMESTAMP WITH TIME ZONE. fmt specifies the format of char if other than the default for datatype TIMESTAMP WITH TIME ZONE.
TO_YMINTERVA(char) Converts char to an INTERVAL YEAR TO MONTH type
TRUNC (date[,fmt]) Returns date truncated to the time unit specified by fmt. If fmt is omitted, date is truncated to the nearest day.
TZ_OFFSET(tzname | SESSIONTIMEZONE | DBTIMEZONE | '+|-hh:mi') Returns the timezone offset

 


Conversion Single Row Functions

Function

Description Function

ASCIISTR(string) Returns the ASCII string in the database language of string which can be in any character set. Non-ASCII characters are converted to their UTF-16 binary values.
BIN_TO_NUM(expr[,expr…]) Converts the binary bits of expr,expr,… to a number. Example: BIN_TO_NUM(1,1,0,1) returns 13.
CAST(expr | [MULTISET] (subquery) AS type) Converts from one built in datatype or collection type to another
CHARTOROWID(char) Converts char to type ROWID
COMPOSE('string') Converts string to its Unicode string equivalent in the same character set
CONVERT(char, dest_set [,source_set]) Returns char converted from source_set character set to dest_set character set. If source_set is not specified, the database character set is assumed.
DECOMPOSE(string [CANONICAL | COMPATIBILITY]) Returns a unicode string decomposed from its fully normalized form. If CANONICAL(the default) is used, the result can be recomposed with COMPOSE.
HEXTORAW (char) Returns hexadecimal digits of char as RAW
NUMTODSINTERVAL (n, char) Converts number n to an INTERVAL DAY TO SECOND literal. char can be 'DAY,' 'HOUR,' 'MINUTE,' or 'SECOND'
NUMTOYMINTERVAL (n, char) Converts number n to an INTERVAL YEAR TO MONTH literal. char can be 'YEAR or 'MONTH'
RAWTOHEX(raw) Converts raw to its hexadecimal equivalent character value
RAWTONHEX(raw) Converts raw to its hexadecimal equivalent NVARCHAR2 character value
ROWIDTOCHAR(rowid) Converts rowid to a VARCHAR2 18 characters long
ROWIDTONCHAR(rowid) Converts rowid to a NVARCHAR2 18 characters long
TO_CHAR (nchar | clob | nclob) Converts an NCHAR, NVARCHAR2, CLOB or NCLOB value to the underlying database character set
TO_CHAR (date [,fmt[nlsparm]]) Converts date to VARCHAR2, using format fmt and any nlsparm
TO_CHAR (num [,fmt[nlsparm]]) Converts num to VARCHAR2, using format fmt and any nlsparm
TO_CLOB (lob_col|char) Converts lob_col or char to CLOB value
TO_DATE char [,fmt[nlsparm]] Converts char to a date, using the format fmt and any nlsparm. If fmt is not specified, then the default date format is used.
TO_DSINTERVAL (char [nlsparm]) Converts char to an INTERVAL DAY TO SECOND literal
TO_LOB(long_col) Converts the LONG or LONG RAW value of long_col to LOB values
TO_MULTI_BYTE(char) Converts single byte char to multibyte characters
TO_NCHAR(char [,fmt[nlsparm]]) Converts a string from the database character set to the national character set
TO_NCHAR (datetime | interval[,fmt[nlsparm]]) Converts a date, time, or interval value from the database character set to the national character set
TO_NCHAR (n [,fmt[nlsparm]]) Converts a number to a string in the NVARCHAR2 character set
TO_NCLOB (lob_column | char) Converts char or lob_column to NCLOB data, using the national character set
TO_NUMBER(char[,fmt[nlsparm]]) Converts char to a number, using fmt as the format specifier
TO_SINGLE_BYTE(char) Returns char with any multibyte characters converted to the corresponding single byte characters
TO_YMINTERVAL(char [nlsparm]) Converts char to an INTERVAL YEAR TO MONTH literal
TRANSLATE (text USING CHAR_CS | NCHAR_CS) Returns text translated into the database character set (USING CHAR_CS) or the national character set (USING NCHAR_CS)
UNISTR(string) Returns string in Unicode using the database Unicode character set

 


Miscellaneous Single Row Functions

Function

Description Function

BFILENAME('dir','fname') Returns a locator for an LOB binary file on the filesystem. dir is the database object that is an alias for the full pathname of the file directory, fname is the actual file name.
COALESCE(expr[,expr,...]) Returns the first nonnull expression in a list of expressions
DECODE(expr,search ,result [ ,search,result...][,default]) Searches expr for search, returning the specific result for each search. Returns default if search is not found.
DEPTH(correlation_int) Returns the number of levels in the path specified by an UNDER_PATH condition
DUMP(expr[,return_fmt [,start[,length]]]) Returns a VARCHAR2 value with the datatype, length, and internal representation of expr, using the format of return_fmt. Returns entire internal representation unless start and optionally length are specified.
EMPTY_BLOB() Returns a locator for a BLOB, allowing you to initialize the BLOB
EMPTY_CLOB() Returns a locator for a CLOB, allowing you to initialize the CLOB
EXISTSNODE(XML_Instance, path [expr]) Walks the XML tree and returns success if a node is found that matches the specified path
EXTRACT (XML_Instance, path [expr]) Walks the XML tree and, if nodes are found which match the specified path, returns those nodes
EXTRACTVALUE(XML_Instance, path [expr]) Walks the XML tree and, if nodes are found that match the specified path, returns the scalar value of those nodes
GREATEST(expr[,expr,...]) Returns the expression in the list with greatest value. All data types are implicitly converted to the data type of the first expression. Character comparisons use the database character set.
LEAST(expr[,expr,...]) Returns the expression in the list with least value. All data types are implicitly converted to the data type of the first expression. Character comparisons use the database character set.
NLS_CHARSET_DECL_LEN (bytes,set_id) Returns the declaration width of the NCHAR column of width bytes and a character set ID of set_id
NLS_CHARSET_ID(text) Returns the number of a character set ID with a character set name of text
NLS_CHARSET_NAME(num) Returns the character set name of the character set with ID num
NULLIF(expr1,expr2) Returns null if expr1and expr2 are equal, else returns expr1
NVL(expr1,expr2) Returns expr2 if expr1 is NULL, else returns expr1
NVL2(expr1,expr2,expr3) Returns expr2 if expr1 is NOT NULL, else returns expr3
PATH (correlation_int) Returns the relative path to the resource specified in an UNDER_PATH or EQUALS_PATH condition
SYS_CONNECT_BY_PATH (column,char) Returns the path of a column value from root to node in an hierarchical query. Column values are separated by char.
SYS_CONTEXT('namespace', 'param'[,len]) Returns a VARCHAR2 with the value of param of namespace. Return is 256 bytes unless overridden by len.
SYS_DBURIGEN(col|attr [rowid][,col|attr [rowid],...] [,'text()']) Generates a URL that can be used to retrieve an XML document from one or more columns col or attributes attr with or without a rowid
SYS_EXTRACT_UTC(time) Returns the UTC from time where time is a datetime with time zone displacement
SYS_GUID() Generates and then returns a Globally Unique IDentifier (GUID) of 16 RAW bytes
SYS_TYPEID(obj_val) Returns the typeid of an object type operand
SYS_XMLAGG(expr [fmt]) Creates a single well-formed XML document from multiple documents
SYS_XMLGEN(expr [fmt]) Creates a well-formed XML document from a database row/column expression
UID Returns the UID of the current session user
UPDATEXML(XML_instance, path, expr) Updates an XML document by searching for the node specified in the path, then replaces either the node or the scalar value of the node, depending on argument types
USER Returns the username of the current session user
USERENV(param) Returns a variety of information about the current session. While deprecated in favor of SYS_CONTEXT, this is retained for backward compatibility.
VSIZE(expr) Returns the number of bytes used by the value represented by expr
XMLAGG(XML_instance [ORDER BY sortlist]) Returns a well-formed XML document by aggregating a series of XML fragments. The returned document is a simple aggregate and no formatting is supported.
XMLCOLATTVAL Creates an XML fragment for one or more columns of a single row. The format of the fragment is fixed as column value.
XMLCONCAT(XML_instance [, XML_instance,...]) Returns an XML fragment created by concatenating a series of XML fragments or elements
XMLFOREST Creates an XML fragment for one or more columns of a single row. The format of the fragment is fixed as column value.
XMLSEQUENCE Used to "unroll" a stored XMLType into multiple rows for further processing as individual elements
XMLTRANSFORM Applies an XSL style sheet to an XML document and returns the resulting new XML document

 


Aggregate Functions

Function

Description Function

AVG([DISTINCT|ALL] expr) Computes the average of the rows returned by expr. If the DISTINCT keyword is used, duplicate rows will be excluded from the calculation.
CORR( expr1 , expr2 ) Calculates the coefficient of correlation between expr1 and expr2
COUNT(* | [DISTINCT|ALL] expr) Returns the number of [DISTINCT] rows in the expr that are not null, or if * is specified, the total number of rows, including duplicates and nulls
COVAR_POP( expr1, expr2 ) Given a set of pairs, expr1 and expr2, where nulls are excluded, returns the population covariance
COVAR_SAMP( expr1, expr2 ) Given a set of pairs, expr1 and expr2, where nulls are excluded, returns the sample covariance
CUME_DIST(expr[,expr...]) WITHIN GROUP (ORDER BY expr [DESC|ASC] [NULLS [FIRST|LAST]) Given a list of values, finds and returns the cumulative distribution of a single value within that list
DENSE_RANK(expr[,expr...]) WITHIN GROUP (ORDER BY expr) Given an ordered group of rows, finds and returns the rank of a single value within that group
FIRST ORDER BY expr [DESC|ASC] [NULLS [FIRST|LAST]) Returns the first row or rows from a set based on the specified sort order. If multiple rows tie as "first" then all tied rows will be returned. Used in an aggregate function.
GROUP_ID() Used in GROUP BY specification to distinguish duplicate groups
GROUPING(expr) Used to distinguish superaggregate rows from regular grouped rows when ROLLUP and CUBE are used
GROUPING_ID(expr[,expr...]) Returns the number of the GROUPING bit vector for a row
LAST ORDER BY expr [DESC|ASC] [NULLS [FIRST|LAST]) Returns the last row or rows from a set based on the specified sort order. If multiple rows tie as "last" then all tied rows will be returned. Used in an aggregate function.
MAX([DISTINCT|ALL] expr) Returns the maximum value of expr. If the DISTINCT keyword is used, duplicate rows will be excluded from the calculation.
MIN([DISTINCT|ALL] expr) Returns the minimum value of expr. If the DISTINCT keyword is used, duplicate rows will be excluded from the calculation.
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [DESC|ASC]) Given a list of values and a specified percentile ranking, returns the interpolated value of that percentile by assuming a continuous distribution of data in the list
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [DESC|ASC]) Given a list of values and a specified percentile ranking, returns the smallest value that meets or exceeds that percentile rank by assuming a discrete distribution of data in the list
PERCENT_RANK(expr) WITHIN GROUP (ORDER BY expr [DESC|ASC][NULLS FIRST|LAST]) Given a list of values, calculates the hypothetical rank of a single value within that list
RANK(expr) WITHIN GROUP (ORDER BY expr [DESC|ASC][NULLS FIRST|LAST]) Returns the rank (ordering) of expr in the group of values returned by the order by expression
STDDEV([DISTINCT|ALL] expr) Returns the standard deviation of expr
STDDEV_POP([DISTINCT|ALL] expr) Returns the square root of the population variance from computing the standard deviation of expr
STDDEV_SAMP([DISTINCT|ALL] expr) Returns the square root of the cumulative sample standard deviation of expr
SUM([DISTINCT|ALL] expr) Returns the sum of expr. Distinct eliminates duplicates from the set of values being summed.
VAR_POP(expr) Returns the population variance of expr. Nulls are removed from the calculation.
VAR_SAMP(expr) Returns the sample variance of expr. Nulls are removed from the calculation.
VARIANCE([DISTINCT|ALL] expr) The variance of expr, with duplicates removed if DISTINCT is specified

 


Regression Functions

Function

Description Function

REGR_SLOPE(expr,expr2) Returns the slope of a least squares regression line of the set of number pairs defined by (expr,expr2)
REGR_INTERCEPT(expr,expr2) Returns the Y intercept of a least squares regression line of the set of number pairs defined by (expr,expr2)
REGR_COUNT(expr,expr2) Returns the number of NOT NULL pairs used to fit the least squares regression line of the set of number pairs defined by (expr,expr2)
REGR_R2(expr,expr2) Returns the R2 value (coefficient of determination) of a least squares regression line of the set of number pairs defined by (expr,expr2)
REGR_AVGX(expr,expr2) Returns the average value of expr2 of a least squares regression line of the set of number pairs defined by (expr,expr2) after removing nulls from the calculation
REGR_AVGY(expr,expr2) Returns the average value of expr of a least squares regression line of the set of number pairs defined by (expr,expr2) after removing nulls from the calculation
REGR_SXX(expr,expr2) Returns the value of calculating REGR_COUNT(expr, expr2) * VAR_POP(expr2) with nulls removed from the calculation
REGR_SYY(expr,expr2) Returns the value of calculating REGR_COUNT(expr, expr2) * VAR_POP(expr) with nulls removed from the calculation
REGR_SXY(expr,expr2) Returns the value of calculating REGR_COUNT(expr, expr2) * COVAR_POP(expr,expr2) with nulls removed from the calculation

 


Analytical Functions

Function

Description Function

FIRST_VALUE(expr) OVER (analytical_clause) Returns the first in the ordered set of expr
LAG(expr[,offset][,default]) OVER (analytical_clause) Provides access at a point offset prior to the cursor in a series of rows returned by expr
LAST_VALUE(expr) OVER (analytical_clause) Returns the last in the ordered set of expr
LEAD(expr[,offset][,default]) OVER (analytical_clause) Provides access at a point offset beyond the cursor in a series of rows returned by expr
NTILE(expr) OVER (analytical_clause) Divides the ordered dataset into expr number of buckets
RATIO_TO_REPORT(expr) OVER (analytical_clause) Returns the ratio of expr to the sum returned by analytical_clause
ROW_NUMBER(expr) OVER ([partition_clause]order_by_clause) Assigns a unique number to each row
Repost 0
Published by Oracle SQL - dans FUNCTIONS - SQL
commenter cet article
21 avril 2012 6 21 /04 /avril /2012 13:22

 

SQL COALESCE.

Comment retourner la première valeur NOT NULL dans une liste avec la fonction sql COALESCE dans Oracle.
Comment utiliser COALESCE pour tester des valeurs NULL dans une liste de colonnes.

 

Fonction Oracle SQL COALESCE.

La fonction oracle SQL COALESCE renvoie la première expression NOT NULL parmi une liste d'expressions en évaluant chaque valeur et en déterminant si celle ci est NULL.
COALESCE est une extension de la Fonction NVL / NVL2. ( Gestion des valeurs NULL ).

 

SYNTAXE SQL COALESCE.

 
 COALESCE(expr [, expr ]...)  

Si toutes les expressions de la liste sont NULL, COALESCE retourne une valeur NULL.
Toutes les expressions de la liste doivent être du même type de données.

 

 
 TABLE DEMO COALESCE SQL> SELECT ename, sal, comm FROM emp; ENAME SAL COMM ---------- ---------- ---------- SMITH 800,5 ALLEN 1800 WARD 1250 500,56 JONES 2975 MARTIN SQL> 

 


TESTER LES VALEURS NULL DES COLONNES AVEC COALESCE.

Dans cet exemple, COALESCE va évaluer les valeurs des colonnes Salaire et Com.
La fonction affichera en premier la colonne Salaire si celle-ci est NOT NULL,
sinon elle affichera la colonne Com si celle-ci est NOT NULL, sinon si toutes les colonnes sont NULL alors une valeur par défaut ( ici 10 ) sera retournée.

 
 SQL> SELECT ename, COALESCE (sal, comm, 10) AS RESULT FROM emp; ENAME RESULT ---------- ---------- SMITH 800,5 ALLEN 1800 WARD 1250 JONES 2975 MARTIN 10 SQL> 

 


CALCULER AVEC DES VALEURS NULL AVEC COALESCE.

Dans cet exemple, COALESCE va évaluer les valeurs des colonnes Salaire et Com afin de retourner un nouveau salaire unique.
Si Salaire et Com sont NOT NULL alors augmentation de 10 % de la Com.
Sinon si Com est NOT NULL alors augmentation de 30% de la Com.
Sinon si Salaire est NOT NULL alors augmentation de 35% du Salaire.
Sinon un salaire de 2000 par défaut si Salaire et Com sont NULL.

 
 SQL> SELECT ename, sal, comm, COALESCE (sal + comm * 1.1, comm * 1.3, sal * 1.35, 2000) AS "Nvx Salaire Uniq" FROM emp; ENAME SAL COMM Nvx Salaire Uniq ---------- ---------- ---------- ---------------- SMITH 800,5 1080,675 ALLEN 1800 2340 WARD 1250 500,56 1800,616 JONES 2975 4016,25 MARTIN 2000 SQL> 

 


GESTION SIMPLE DE VALEURS NULL AVEC SQL COALESCE.

 
 SQL> SELECT COALESCE(NULL,NULL,NULL,4,NULL,2) AS RESULT FROM dual; RESULT ---------- 4 SQL> 

 

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Fonction NVL / NVL2
Repost 0
Published by Daniel Roesch - dans FUNCTIONS - SQL
commenter cet article
11 avril 2012 3 11 /04 /avril /2012 19:32

 

SQL - CONCAT.

Comment utiliser la fonction Oracle CONCAT.
Comment concaténer des chaînes de caractères dans Oracle.
Comment faire une concaténation de champs / colonnes dans une requête SQL.

LA CONCATÉNATION ORACLE AVEC LA FONCTION SQL CONCAT.

La fonction Oracle SQL CONCAT permet de concaténer ou de fusionner plusieurs chaînes de caractères, ainsi le résultat de la concaténation de deux chaînes de caractères sera une autre chaîne de caractères.
Si les deux chaînes de caractères sont de type de données CHAR, la fonction CONCAT retrournera une chaîne de type CHAR et donc sera limité à 2000 caractères.
Si les chaîne de caractères sont type de données VARCHAR2 alors la fonction CONCAT retournera une chaîne de type VARCHAR2 et donc sera limité à 4000 caractères.

La concaténation d'une chaîne '' (Vide) ou NULL avec une chaîne NON NULL retourne une chaîne et non pas une valeur NULL.

 

Il existe aussi dans Oracle un opérateur de concaténation, le || ( Double Pipe ) ( Barres verticales ).
Lorsque l'on doit concaténer des chaînes de caractères dans des scripts, ou Batch, Oracle conseille l'utilisation de la fonction CONCAT pour une raison de portabilité, car le || ( Double Pipe ) est mal ou pas interprété sur d'autres Systèmes.

SYNTAXE SQL CONCAT.

 
 CONCAT(char1, char2) 

Les expressions char1 et char2 de la fonction CONCAT peuvent être de type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB et NCLOB.

 

EXEMPLE CONCAT DANS UN SELECT.

Concaténation simple de deux chaînes de caractères.

 
 SQL> SELECT CONCAT ('Bienvenue ', 'sur l''article CONCAT') AS "CONCAT = String" FROM dual; CONCAT = String ------------------------------ Bienvenue sur l'article CONCAT SQL> 

 

EXEMPLE CONCAT ET DOUBLE PIPE DANS UN SELECT.

Une double utilisation de la fonction CONCAT concaténée par un double pipe ( || ) à partir de champs de table.

 
 SQL> SELECT CONCAT ('Le Salarié ', ename) || CONCAT (' a pour salaire ', sal) AS "CONCAT + DOUBLE PIPE" FROM bonus; CONCAT + DOUBLE PIPE ----------------------------------------------- Le Salarié SMITH a pour salaire 800,5 Le Salarié ALLEN a pour salaire 1600 Le Salarié WARD a pour salaire 1250 Le Salarié JONES a pour salaire 2975 Le Salarié MARTIN a pour salaire 1250 SQL> 

 

EXEMPLE CONVERSION NUMÉRIQUE D'UN CONCAT.

Dans cet exemple, concaténation de deux valeurs chaînes puis conversion numérique avec TO_NUMBER du résultat de la fonction CONCAT.

 
 SQL> SELECT CONCAT ('2', '22') AS "CONCAT = String" FROM dual; CONCAT = String --------------- 222 SQL> SQL> SELECT TO_NUMBER(CONCAT ('2', '22')) AS "TO_NUMBER - CONCAT" FROM dual; TO_NUMBER - CONCAT ------------------ 222 SQL> 

 

EXEMPLE D'UTILISATION DE LA FONCTION SQL CONCAT.

Pour le fun, et pour bien comprendre la concaténation dans Oracle, cet exemple qui mixe conversion TO_NUMBER + CONCAT + DOUBLE PIPE.

 
 SQL> SELECT 'J''ai transformé ' || TO_NUMBER (CONCAT ('2', '22')) || ' en valeur numérique.' AS "TO_NUMBER - CONCAT" FROM dual; TO_NUMBER - CONCAT ---------------------------------------- J'ai transformé 222 en valeur numérique. SQL> 

 

UPDATE D'UNE COLONNE AVEC DES VALEURS CONCATÉNÉES VIA LA FONCTION SQL CONCAT.

Dans cet exemple de concaténation, on met à jour un champs de table avec son contenu concaténé à une nouvelle chaîne de caractères.

 
 SQL> UPDATE bonus SET ename = CONCAT ('Salarié : ', ename); 5 ligne(s) mise(s) à jour. SQL> SELECT * FROM bonus; ENAME JOB SAL COMM -------------------- ---------- ---------- ---------- Salarié : SMITH CLERK 800,5 Salarié : ALLEN SALESMAN 1600 300 Salarié : WARD SALESMAN 1250 500,56 Salarié : JONES MANAGER 2975 Salarié : MARTIN SALESMAN 1250 1400 SQL> 

Même résultat si nous avions concaténé avec le Double pipe : UPDATE bonus SET ename = 'Salarié : ' || ename;

 

 

Haut de Page www.dba-ora.fr

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Fonction SQL Oracle LTRIM
• Fonction SQL Oracle RTRIM
• Fonction SQL Oracle REPLACE
• Fonction SQL Oracle TRIM
Repost 0
Published by Daniel Roesch - dans FUNCTIONS - SQL
commenter cet article
10 avril 2012 2 10 /04 /avril /2012 22:21

 

SELECT MIN.

Comment trouver la valeur MINIMUM d'une colonne avec la fonction de groupe SQL MIN.
Comment sélectionner la plus petite valeur MIN d'une table avec SELECT MIN ().

 

 

Fonction SQL MIN.

La fonction de groupe PL-SQL MIN est utilisée pour obtenir la valeur minimale d'une colonne.

 

SYNTAXE FONCTION MIN.

 
 MIN( [DISTINCT | ALL] expr ) 

La fonction MIN renvoie une date si l'expression est de type date / heure.
La fonction MIN renvoie un nombre si l'expression est de type numérique.
La fonction MIN retourne un VARCHAR2 (suivant le Character set défini) si l'expression est de type caractère.
Il n'y a pas d'incidence sur le calcul d'un MIN avec l'utilisation de DISTINCT ou ALL.

Les fonctions d'agrégation MIN et MAX ignorent les valeurs NULL présentes dans le jeu de données sur lequel porte leur calcul.

 

FONCTION DE GROUPE MIN.

 
 TABLE DEMO MIN SQL> SELECT * FROM SCOTT.EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------------------------------------------------------------------- 7369 SMITH CLERK 7902 800.5 30 7499 ALLEN SALESMAN 20/02/1981 1600 300 30 7521 WARD SALESMAN 7698 22/02/1981 1250 500.56 30 7566 JONES MANAGER 7839 02/04/1981 2975 30 7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 40 

 

FONCTION ORACLE MIN SANS REGROUPEMENT.

 
 SQL> SELECT MIN(ENAME), MIN(ALL HIREDATE), MIN(SAL), MIN(COMM), MIN(DISTINCT DEPTNO) FROM SCOTT.EMP; MIN(ENAME) MIN(ALLHIR MIN(SAL) MIN(COMM) MIN(DISTINCTDEPTNO) ---------- ---------- ---------- ---------- ------------------- ALLEN 20/02/1981 800,5 30 30 SQL> 

La fonction MIN renvoie ici, un type VARCHAR2, un type DATE et des types NUMBER.
Ici l'utilisation de MIN nous retourne pour chacune des colonnes la valeur MINIMALE.

 

FONCTION ORACLE MIN AVEC CLAUSE GROUP BY.

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, MIN(HIREDATE), MIN(SAL), MIN(COMM), MIN(DEPTNO) FROM SCOTT.EMP GROUP BY (JOB); JOB MIN(HIREDA MIN(SAL) MIN(COMM) MIN(DEPTNO) --------------- ---------- ---------- ---------- ----------- CLERK 800,5 30 SALESMAN 20/02/1981 1250 300 30 MANAGER 02/04/1981 2975 30 SQL> 

Pour le regroupement de données explicite, il faut utiliser la clause GROUP BY.
La clause GROUP BY liste les colonnes de regroupement, ici il se fait sur le champ JOB. Il doit y avoir une cohérence entre les colonnes du SELECT MIN et du GROUP BY.

 

FONCTION ORACLE MIN AVEC CONDITION HAVING.

La condition lors d’un regroupement se fait en utilisant la clause HAVING, elle permet de poser des conditions sur chaque regroupement MIN.

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, MIN(HIREDATE), MIN(SAL), MIN(COMM), MIN(DEPTNO) FROM SCOTT.EMP GROUP BY (JOB) HAVING MIN(SAL)>1249 AND MIN(COMM) > 200; JOB MIN(HIREDA MIN(SAL) MIN(COMM) MIN(DEPTNO) --------------- ---------- ---------- ---------- ----------- SALESMAN 20/02/1981 1250 300 30 SQL> 

 

 

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Fonction SQL MAX
• Fonction SQL COUNT
• Instruction SQL GROUP BY
• SQL SELECT FROM WHERE
Repost 0
Published by Daniel Roesch - dans FUNCTIONS - SQL
commenter cet article
9 avril 2012 1 09 /04 /avril /2012 00:40

 

SQL SUM.

Comment faire une somme en regroupant des lignes avec la FONCTION SUM.
Comment calculer une somme dans une table Oracle avec SELECT SUM.

 

Fonction SQL SUM.

La fonction de groupe ( Fonction d'agrégation ) SQL SUM regroupe des lignes et retourne une somme unique de toutes les expressions numériques d'une colonne de table dans Oracle.

 

SYNTAXE FONCTION SUM.

 
 SUM([ DISTINCT | ALL ] expr)  

La fonction SUM prend comme argument un type de données numérique ou n'importe quel type non numérique qui peut être implicitement converti en un type numérique.
La fonction SUM retournera le même type de données que le type de données numérique de l'argument.

La fonction d'agrégation SUM ignorent les valeurs NULL présentes dans le jeu de données sur lequel porte leur calcul. La fonction SUM ne traite pas les valeurs NULL, ainsi si les seules valeurs évaluées sont NULL alors la fonction SUM retournera NULL.

Lorsque ALL est spécifiée, toutes le valeurs non NULL sont appliquées à la fonction de groupe SUM.
Lorsque DISTINCT est spécifiée, seul une des valeurs non NULL est appliquée à la fonction de regroupement SUM.

LES VALEURS NULL AVEC LES OPERATEURS ARITHMETIQUES.

 
  SQL> SELECT 1+2+0+3 FROM dual; 1+2+0+3 ---------- 6 SQL> SELECT 1+2+NULL+3 FROM dual; 1+2+NULL+3 ---------- SQL>  

On constate ici, qu'une expression NULL utilisée avec les opérateurs arithmétiques retournera NULL.

 

FONCTION DE REGROUPEMENT SUM.

 
 TABLE DEMO UTILISE POUR SQL SUM SQL> SELECT * FROM SCOTT.EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------------------------------------------------------------------- 7369 SMITH CLERK 7902 800.5 30 7499 ALLEN SALESMAN 20/02/1981 1600 300 30 7521 WARD SALESMAN 7698 22/02/1981 1250 500.56 30 7566 JONES MANAGER 7839 02/04/1981 2975 30 7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 40 

 

FONCTION ORACLE SUM SANS REGROUPEMENT.

 
 SQL> SELECT SUM(SAL), SUM(DISTINCT SAL), SUM(COMM), SUM(DISTINCT COMM) FROM SCOTT.EMP; SUM(SAL) SUM(DISTINCTSAL) SUM(COMM) SUM(DISTINCTCOMM) ---------- ---------------- ---------- ----------------- 7875,5 6625,5 2230,56 2230,56 SQL> 

On voit ici que la fonction sql sum retourne la somme totale des salaires avec l'option par défaut ALL, et une somme totale des salaires distincts avec l'utilisation de DISTINCT. Le salaire 1250 est sommé une seule fois.

 

FONCTION ORACLE SUM AVEC CLAUSE GROUP BY.

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, SUM(SAL), SUM(DISTINCT SAL), SUM(COMM), SUM(DISTINCT COMM) FROM SCOTT.EMP GROUP BY (JOB); JOB SUM(SAL) SUM(DISTINCTSAL) SUM(COMM) SUM(DISTINCTCOMM) --------------- ---------- ---------------- ---------- ----------------- CLERK 800,5 800,5 MANAGER 2975 2975 30 30 SALESMAN 4100 2850 2200,56 2200,56 SQL> 

Pour chaque groupe indiqué dans la clause group by (ici JOB) la fonction SUM retourne la somme des expressions numériques. Pour le regroupement de données explicite, il faut utiliser la clause GROUP BY.
La clause GROUP BY liste les colonnes de regroupement, ici il se fait sur le champ JOB. Il doit y avoir une cohérence entre les colonnes du SELECT SUM et du GROUP BY.

 

FONCTION ORACLE SUM AVEC CONDITION HAVING.

La condition lors d’un regroupement se fait en utilisant la clause HAVING, elle permet de poser des conditions sur chaque regroupement SUM.

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, SUM(SAL), SUM(DISTINCT SAL), SUM(COMM), SUM(DISTINCT COMM) FROM SCOTT.EMP GROUP BY (JOB) HAVING SUM(SAL)> SUM(DISTINCT SAL); JOB SUM(SAL) SUM(DISTINCTSAL) SUM(COMM) SUM(DISTINCTCOMM) --------------- ---------- ---------------- ---------- ----------------- SALESMAN 4100 2850 2200,56 2200,56 SQL> 

 

 

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Fonction SQL MIN
• Fonction SQL MAX
• Fonction SQL COUNT
• Instruction SQL GROUP BY
• SQL SELECT FROM WHERE
Repost 0
Published by Daniel Roesch - dans FUNCTIONS - SQL
commenter cet article
3 avril 2012 2 03 /04 /avril /2012 22:49

 

Oracle MAX.

Comment trouver la valeur MAXIMUM d'une expression avec la fonction de groupe sql MAX.
Comment sélectionner la plus grande valeur MAXIMUM d'une table.

 

 

Fonction SQL MAX.

La fonction de groupe PL-SQL MAX est utilisée pour obtenir la valeur maximale d'une colonne

 

SYNTAXE FONCTION MAX.

 
 MAX( [DISTINCT | ALL] expr ) 

Si expression est un type de données date / heure alors la fonction MAX renvoie une date.
Si expression est un type de données numérique alors la fonction MAX renvoie un nombre.
Si expression est un type de données caractère alors la fonction MAX retourne un VARCHAR2 (suivant le Character set défini).
L'utilisation de DISTINCT ou ALL est syntaxiquement acceptable, mais son utilisation n'a pas d'incidence sur le calcul d'un MAX.

 

FONCTION DE GROUPE MAX.

 
 TABLE DEMO SQL> SELECT * FROM SCOTT.EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------------------------------------------------------------------- 7369 SMITH CLERK 7902 800.5 30 7499 ALLEN SALESMAN 20/02/1981 1600 300 30 7521 WARD SALESMAN 7698 22/02/1981 1250 500.56 30 7566 JONES MANAGER 7839 02/04/1981 2975 30 7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 40 

 

FONCTION MAX SANS REGROUPEMENT.

 
 SQL> SELECT MAX(ENAME), MAX(DISTINCT HIREDATE), MAX(SAL), MAX(COMM), MAX(ALL DEPTNO) FROM SCOTT.EMP; MAX(ENAME) MAX(DISTIN MAX(SAL) MAX(COMM) MAX(ALLDEPTNO) ---------- ---------- ---------- ---------- -------------- WARD 28/09/1981 2975 1400 40 SQL> 

La fonction MAX renvoie ici, un type VARCHAR2, un type DATE et du type NUMBER.
Ici l'utilisation de MAX nous retourne pour chacune des colonnes la valeur MAXIMUM.

 

FONCTION MAX AVEC CLAUSE GROUP BY.

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, MAX(HIREDATE), MAX(SAL), MAX(COMM), MAX(DEPTNO) FROM SCOTT.EMP GROUP BY (JOB); JOB MAX(HIREDA MAX(SAL) MAX(COMM) MAX(DEPTNO) ---------- ---------- ---------- ---------- ----------- CLERK 800,5 30 SALESMAN 28/09/1981 1600 1400 40 MANAGER 02/04/1981 2975 30 SQL> 

Pour le regroupement de données explicite, il faut utiliser la clause GROUP BY
La clause GROUP BY liste les colonnes du groupement, ici il se fait sur le champ JOB. Il doit y avoir une cohérence entre les colonnes du SELECT et du GROUP BY.

 

FONCTION MAX AVEC CONDITION HAVING.

La condition lors d’un regroupement se fait en utilisant la clause HAVING, elle permet de poser des conditions sur chaque regroupement MAX.

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, MAX(HIREDATE), MAX(SAL), MAX(COMM), MAX(DEPTNO) FROM SCOTT.EMP GROUP BY (JOB) HAVING MAX(SAL)>801 AND MAX(COMM) > 1; JOB MAX(HIREDA MAX(SAL) MAX(COMM) MAX(DEPTNO) ---------- ---------- ---------- ---------- ----------- SALESMAN 28/09/1981 1600 1400 40 SQL> 

 

 

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Fonction SQL MIN
• Fonction SQL COUNT
• Instruction SQL GROUP BY
• Instruction SQL SELECT FROM WHERE
Repost 0
Published by Daniel Roesch - dans FUNCTIONS - SQL
commenter cet article
2 avril 2012 1 02 /04 /avril /2012 21:34

 

Oracle COUNT.

Comment regrouper et compter le nombre de lignes avec la fonction de groupe COUNT ?

 

Fonction COUNT.

La fonction de groupe ou fonction d’agrégat COUNT retourne un nombre de lignes issue d'une requête ou sous-interrogation.

 

SYNTAXE COUNT.

 
 COUNT({* | [DISTINCT | ALL] }) 

* (astérisque) compte toute les lignes quel que soit les valeurs NULL, la fonction de groupe COUNT(*) prend en compte les valeurs NULL.
L’option DISTINCT évite les duplicatas (doublons) alors que ALL les prend en compte (Option par défaut de la fonction).

 

FONCTION DE GROUPE COUNT

 
 TABLE DEMO SQL> SELECT * FROM SCOTT.EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------------------------------------------------------------- 7369 SMITH CLERK 7902 800.5 30 7499 ALLEN SALESMAN 20/02/1981 1600 300 30 7521 WARD SALESMAN 7698 22/02/1981 1250 500.56 30 7566 JONES MANAGER 7839 02/04/1981 2975 30 7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 40 

 

FONCTION COUNT SANS REGROUPEMENT

 
 SQL> SELECT COUNT(*), COUNT(DISTINCT JOB), COUNT(ALL DEPTNO), COUNT(DISTINCT DEPTNO) FROM SCOTT.EMP; COUNT(*) COUNT(DISTINCTJOB) COUNT(ALLDEPTNO) COUNT(DISTINCTDEPTNO) ---------- ------------------ ---------------- --------------------- 5 3 5 2 SQL> 

3 job distinct, 2 Deptno distinct (valeur NULL non retournée avec DISTINCT).

 

ORA-00937: la fonction de groupe ne porte pas sur un groupe simple

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, COUNT(*), COUNT(DISTINCT JOB), COUNT(ALL DEPTNO), COUNT(DISTINCT DEPTNO) FROM SCOTT.EMP; SELECT JOB, * ERREUR à la ligne 1 : ORA-00937: la fonction de groupe ne porte pas sur un groupe simple SQL> SQL> 

Pour le regroupement de données explicite, il faut utiliser la clause GROUP BY

 

Clause GROUP BY avec fonction de groupe COUNT.

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, COUNT(*), COUNT(DISTINCT JOB), COUNT(ALL DEPTNO), COUNT(DISTINCT DEPTNO) FROM SCOTT.EMP GROUP BY (JOB); JOB COUNT(*) COUNT(DISTINCTJOB) COUNT(ALLDEPTNO) COUNT(DISTINCTDEPTNO) ---------- ---------- ------------------ ---------------- --------------------- CLERK 1 1 1 1 MANAGER 1 1 1 1 SALESMAN 3 1 3 2 SQL> 

La clause GROUP BY liste les colonnes du groupement, ici il se fait sur le champ JOB. Il doit y avoir une cohérence entre les colonnes du SELECT et du GROUP BY.

 

Condition HAVING avec fonction de groupe COUNT.

 
 SQL> COL JOB FORMAT A10 SQL> SELECT JOB, COUNT(*), COUNT(DISTINCT JOB), COUNT(ALL DEPTNO), COUNT(DISTINCT DEPTNO) FROM SCOTT.EMP GROUP BY (JOB) HAVING COUNT(DISTINCT DEPTNO)=2; JOB COUNT(*) COUNT(DISTINCTJOB) COUNT(ALLDEPTNO) COUNT(DISTINCTDEPTNO) ---------- ---------- ------------------ ---------------- --------------------- SALESMAN 3 1 3 2 SQL> 

La condition lors d’un regroupement se fait en utilisant la clause HAVING, elle permet de poser des conditions sur chaque regroupement.

 

 

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Instruction SQL GROUP BY
• Instruction SQL SELECT FROM WHERE
Repost 0
Published by Daniel Roesch - dans FUNCTIONS - SQL
commenter cet article