Explication détaillée des fonctions PL/SQL à une seule ligne et des fonctions de groupe Une fonction est un programme qui a zéro ou plusieurs paramètres et une valeur de retour. Oracle dispose d'une série de fonctions intégrées dans SQL,
Ces fonctions peuvent être appelées instructions SQL ou PL/SQL. Les fonctions sont principalement divisées en deux catégories :
Fonctions de groupe de fonctions à une seule ligne Cet article explique comment utiliser les fonctions à une seule ligne et les règles de leur utilisation.
Fonction à une seule ligne en SQL
SQL et PL/SQL sont livrés avec de nombreux types de fonctions, notamment des fonctions de caractères, numériques, de date, de conversion et mixtes pour le traitement de lignes uniques de données.
Par conséquent, celles-ci peuvent être collectivement appelées fonctions à une seule ligne. Ces fonctions peuvent être utilisées dans SELECT, WHERE, ORDER BY et d'autres clauses,
Par exemple, l'exemple suivant contient des fonctions sur une seule ligne telles que TO_CHAR, UPPER et SOUNDEX.
SELECT nom, TO_CHAR (date d'embauche, 'jour, JJ-Lun-AAAA')
DE emp
Où UPPER(ename) Comme 'AL%'ORDER BY SOUNDEX(ename)
Les fonctions à une seule ligne peuvent également être utilisées dans d'autres instructions, telles que la clause SET de update, la clause VALUES de INSERT et la clause WHERE de DELET.
L'examen de certification accorde une attention particulière à l'utilisation de ces fonctions dans l'instruction SELECT, notre attention se porte donc également sur l'instruction SELECT.
NULL et fonctions à une seule ligne Comprendre NULL peut être difficile au début, et même une personne très expérimentée peut encore être confuse.
La valeur NULL représente une donnée inconnue ou une valeur NULL. Tout opérande d'un opérateur arithmétique est une valeur NULL et le résultat est une valeur NULL.
Cette règle s'applique également à de nombreuses fonctions. Seuls CONCAT, DECODE, DUMP, NVL et REPLACE peuvent renvoyer des valeurs non NULL lorsqu'ils sont appelés avec des paramètres NULL.
Parmi celles-ci, la fonction NVL est la plus importante car elle peut gérer directement les valeurs NULL.
NVL a deux paramètres : NVL(x1,x2), x1 et x2 sont des expressions. Lorsque x1 est nul, X2 est renvoyé, sinon x1 est renvoyé.
Jetons un coup d'œil au tableau de données emp. Il contient le salaire et les primes. Nous devons calculer la rémunération totale.
nom de la colonne
emp_id type de clé de bonus de salaire
pk nulls/unique nn,u nnfk table type de données numéro numéro nombrelongueur 11.2 11.2
Au lieu de simplement additionner le salaire et la prime, si une certaine ligne a une valeur nulle, le résultat sera nul, comme dans l'exemple suivant :
mettre à jour empset salaire = (salaire + bonus) * 1.1
Dans ce relevé, le salaire et la prime de l'employé seront mis à jour à une nouvelle valeur, mais s'il n'y a pas de prime,
C'est-à-dire salaire + nul, alors une conclusion erronée sera tirée. À ce stade, la fonction nvl doit être utilisée pour éliminer l'influence des valeurs nulles.
La déclaration correcte est donc :
mettre à jour empset salaire=(salaire+nvl(bonus,0)*1.1
Fonctions de chaîne sur une seule ligne Les fonctions de chaîne sur une seule ligne sont utilisées pour manipuler des données de chaîne. La plupart d'entre elles ont un ou plusieurs paramètres et la plupart renvoient des chaînes.
ASCII()
c1 est une chaîne, renvoie le code ASCII de la première lettre de c1 et sa fonction inverse est CHR()
SELECT ASCII('A') BIG_A,ASCII('z') BIG_z
DE empBIG_A BIG_z65 122
CHR(<i>)[NCHAR_CS]
i est un nombre et la fonction renvoie la représentation décimale du caractère
sélectionnez CHR(65),CHR(122),CHR(223)
DE empCHR65 CHR122 CHR223A z B
CONCAT(,)
C1 et c2 sont des chaînes. La fonction connecte c2 à l'arrière de c1. Si c1 est nul, c2 sera renvoyé. Si c2 est nul, c1 sera renvoyé.
Si c1 et c2 sont nuls, renvoyez null. Il renvoie le même résultat que l'opérateur ||
sélectionnez le nom d'utilisateur concat('slobo ','Svoboda')
de dualusernameslobo Syoboda
INITCAP()
c1 est une chaîne. La fonction renvoie la première lettre de chaque mot en majuscule et les autres lettres en minuscules. Les mots sont composés d'espaces, de caractères de contrôle,
Restrictions de ponctuation.
sélectionnez INITCAP('veni,vedi,vici') César
de dualCeasarVeni, Vedi, Vici
INSTR(,[,＜i＞[,]])
c1 et c2 sont tous deux des chaînes, i et j sont des entiers. La fonction renvoie la position de la j-ème occurrence de c2 dans c1 et la recherche commence à partir du i-ème caractère de c1.
Lorsque le caractère recherché n'est pas trouvé, 0 est renvoyé. Si i est un nombre négatif, la recherche sera effectuée de droite à gauche, mais la position sera toujours calculée de gauche à droite.
La valeur par défaut pour i et j est 1.
SELECT INSTR('Mississippi','i',3,3)
DE double INSTR('MISSISSIPPI','I',3,3)
11
sélectionnez INSTR('Mississippi','i',-2,3)
à partir du double INSTR('MISSISSIPPI','I',3,3)
2
INSTRB(,[,i[,j])
Identique à la fonction INSTR(), sauf qu'elle renvoie des octets pour un seul octet, INSTRB() est égal à INSTR().
LONGUEUR()
c1 est une chaîne et la longueur de c1 est renvoyée. Si c1 est nulle, une valeur nulle sera renvoyée.
sélectionnez LONGUEUR('Ipso Facto') ergo
de dualergo10
LONGUEURb()
Comme LENGTH(), renvoie des octets.
inférieur()
Renvoie le caractère minuscule de c, qui apparaît souvent dans la sous-chaîne Where.
sélectionnez INFÉRIEUR (nom de la couleur)
à partir du détail de l'article
OÙ INFÉRIEUR(nom de la couleur) COMME '%white%'COLORNAMEWinterwhite
LPAD(,＜i＞[,])
c1 et c2 sont tous deux des chaînes et i est un entier. Utilisez la chaîne c2 pour compléter la longueur i sur le côté gauche de c1, qui peut être répétée plusieurs fois si i est inférieure à la longueur de c1,
Ensuite seuls les caractères c1 tant que i seront renvoyés, et les autres seront tronqués. La valeur par défaut de c2 est un espace simple, voir RPAD.
sélectionnez LPAD (réponse, 7, '') complété, réponse non complétée
de la question ;
REMBOURRÉ NON REMBOURRÉ Oui OuiNON NONPeut-être peut-être
LTRIM(,)
Supprimez le caractère le plus à gauche de c1 afin que le premier caractère ne soit pas dans c2. S'il n'y a pas de c2, alors c1 ne changera pas.
sélectionnez LTRIM('Mississippi','Mis') dans dualLTRppi
RPAD(,＜i＞[,])
Utilisez la chaîne c2 pour compléter la longueur i sur le côté droit de c1, qui peut être répétée plusieurs fois si i est inférieure à la longueur de c1, alors seuls les caractères c1 tant que i seront renvoyés.
D'autres seront tronqués. La valeur par défaut de c2 est un espace unique et les autres sont similaires à LPAD.
RTRIM(,)
Supprimez le caractère le plus à droite de c1 afin que le dernier caractère ne soit pas dans c2. S'il n'y a pas de c2, alors c1 ne changera pas.
REMPLACER(,[,])
c1, c2 et c3 sont toutes des chaînes. La fonction remplace c2 qui apparaît dans c1 par c3 et renvoie.
sélectionnez REMPLACER('uptown','up','down')
de dualREPLACEdowntown
STBSTR(,＜i＞[,])
c1 est une chaîne, i et j sont des entiers et une sous-chaîne de longueur j est renvoyée à partir de la i-ème position de c1, ou jusqu'à la fin de la chaîne si j est vide.
sélectionnez SUBSTR('Message',1,4)
de dualSUBSMess
SUBSTRB(,<i>[,])
C'est à peu près la même chose que SUBSTR, sauf que I et J sont calculés en octets.
SOUNDEX()
Renvoie les mots qui ressemblent à c1
sélectionnez SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson')
de dualDawes Daws DawsonD200 D200 D250
TRADUIRE(,,)
Remplacez les mêmes caractères dans c1 que c2 par c3
sélectionnez le test TRANSLATE('fumble','uf','ar') depuis dualTEXTrambe
TRIM([[]] de c3)
Supprimez le premier, le dernier ou les deux dans la chaîne c3.
sélectionnez la garniture TRIM ('espace rembourré') à partir du double TRIMespace rembourré
SUPÉRIEUR()
Renvoie la version majuscule de c1, qui apparaît souvent dans la sous-chaîne Where.
sélectionnez le nom à partir de dual où UPPER(name) LIKE 'KI%'NAMEKING
Fonctions numériques à une seule ligne Les fonctions numériques à une seule ligne opèrent sur des données numériques et effectuent des opérations mathématiques et arithmétiques. Toutes les fonctions prennent des paramètres numériques et renvoient des valeurs numériques.
Les opérandes et les valeurs de toutes les fonctions trigonométriques sont des radians plutôt que des angles. Oracle ne fournit pas de fonction de conversion intégrée pour les radians et les angles.
ABS()
Renvoie la valeur absolue de n
ACOS()
La fonction cofacteur inverse renvoie un nombre compris entre -1 et 1. n représente les radians
sélectionnez ACOS(-1) pi,ACOS(1) ZÉRO
DE dualPI ZÉRO3.14159265 0
ASIN()
Quoi qu'il en soit, la fonction mystérieuse renvoie -1 à 1, n représente les radians
ATAN()
Fonction Arctangente, renvoie la valeur arctangente de n, où n représente les radians.
CEIL()
Renvoie le plus petit entier supérieur ou égal à n.
COS()
Renvoie la co-valeur de n, où n est le radian
MATRAQUE()
Renvoie le cofacteur hyperbolique de n, où n est un nombre.
sélectionnez COSH(<1.4>)
DE dualCOSH(1.4)2.15089847
EXP()
Renvoie la nième puissance de e, e=2,71828183.
SOL()
Renvoie le plus grand entier inférieur ou égal à N.
LN()
Renvoie le logarithme népérien de N, qui doit être supérieur à 0
ENREGISTRER(,)
Renvoie le logarithme de n1 en base n2
MOD()
Renvoie le reste de n1 divisé par n2,
POUVOIR(,)
Renvoie n1 élevé à la puissance n2
ROND(,)
Renvoie la valeur de n1 arrondie à n2 places à droite de la virgule décimale. La valeur par défaut de n2 est 0. Cette fois, l'entier le plus proche de la virgule décimale est arrondi.
Si n2 est un nombre négatif, il est arrondi au chiffre correspondant à gauche de la virgule décimale. n2 doit être un nombre entier.
sélectionnez ROND (12345,-2), ROND (12345.54321,2)
DE dualROUND(12345,-2) ROND(12345.54321,2)12300 12345.54
SIGNE()
Si n est un nombre négatif, -1 est renvoyé, si n est un nombre positif, 1 est renvoyé et si n=0, 0 est renvoyé.
PÉCHÉ()
Renvoie la valeur positive de n, où n correspond aux radians.
SINH()
Renvoie la valeur hyperbolique positive de n, où n correspond aux radians.
SQRT()
Renvoie la racine carrée de n, où n représente les radians
TANNÉ()
Renvoie la tangente de n, où n est le radian
TANH()
Renvoie la tangente hyperbolique de n, où n est le radian
TRONC(,)
Renvoie la valeur de n1 tronquée à n2 décimales. Le paramètre par défaut de n2 est 0. Lorsque n2 est le paramètre par défaut, n1 sera tronqué en un nombre entier.
Si n2 est une valeur négative, elle est tronquée à la position correspondante à gauche de la virgule décimale.
Fonction de date à une seule ligne
La fonction de date sur une seule ligne fonctionne sur le type de données DATA, et la plupart d'entre elles ont des paramètres du type de données DATA.
La plupart des valeurs renvoyées sont également des valeurs de type de données DATA.
ADD_MONTHS(,＜i＞)
Renvoie le résultat de la date d plus i mois. je peux être n'importe quel entier. Si je suis un nombre décimal,
Ensuite, la base de données le convertira implicitement en entier et tronquera la partie après la virgule décimale.
LAST_DAY()
La fonction renvoie le dernier jour du mois contenant la date d
MOIS_BETWEEN(,)
Renvoie le nombre de mois entre d1 et d2, si les dates de d1 et d2 sont identiques, ou si les deux sont le dernier jour du mois,
Ensuite, un entier sera renvoyé, sinon le résultat renvoyé contiendra une fraction.
NEW_TIME(,,)
d1 est un type de données date Lorsque la date et l’heure du fuseau horaire tz1 sont d, renvoie la date et l’heure du fuseau horaire tz2.
tz1 et tz2 sont des chaînes.
NEXT_DAY(,)
Renvoie le premier jour suivant la date d donnée par dow, qui précise le jour de la semaine en utilisant la langue donnée dans la session en cours,
La composante temporelle renvoyée est la même que la composante temporelle de d.
sélectionnez NEXT_DAY('01-Jan-2000','Monday') "1er lundi",
NEXT_DAY('01-Nov-2004','Mardi')+7 "2ème mardi")
de double ;
1er lundi 2e mardi03-jan-2000 09-nov-2004
ROND([,])
Arrondissez la date d selon le format spécifié par fmt, qui est une chaîne.
SYADATE
La fonction ne prend aucun paramètre et renvoie la date et l'heure actuelles.
TRONC([,])
Renvoie la date d dans les unités spécifiées par fmt.
Fonction de conversion à une seule ligne La fonction de conversion à une seule ligne est utilisée pour exploiter plusieurs types de données et convertir entre les types de données.
CHARTORWID()
c crée une chaîne et la fonction convertit c en type de données RWID.
SELECT test_id
de test_case
où rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
CONVERTIR(,[,])
c tail string, dset et sset sont deux jeux de caractères. La fonction convertit la chaîne c du jeu de caractères sset en jeu de caractères dset.
Le paramètre par défaut de sset est le jeu de caractères de la base de données.
HEXTORAW()
x est une chaîne hexadécimale et la fonction convertit le x hexadécimal en un type de données RAW.
RAWTOHEX()
x est une chaîne de type de données RAW et la fonction convertit le type de données RAW en type de données hexadécimal.
ROWIDTOCHAR()
La fonction convertit le type de données ROWID en type de données CHAR.
À_CHAR([[,)
x est un type de données data ou number. La fonction convertit x en un type de données char au format spécifié par fmt.
Si x est une date, nlsparm= NLS_DATE_LANGUAGE contrôle la langue utilisée pour le mois et le jour renvoyés.
Si x est un nombre, nlsparm=NLS_NUMERIC_CHARACTERS est utilisé pour spécifier les séparateurs décimaux et de milliers, ainsi que les symboles monétaires.
NLS_NUMERIC_CHARACTERS="dg", NLS_CURRENCY="chaîne"
À CE JOUR([,[,)
c représente une chaîne et fmt représente une chaîne dans un format spécial. Renvoie c affiché au format fmt et nlsparm indique la langue utilisée.
La fonction convertit la chaîne c en type de données date.
TO_MULTI_BYTE()
c représente une chaîne et la fonction convertit le caractère de troncature de c en un caractère multi-octets.
TO_NUMBER([,[,)
c représente une chaîne, fmt représente une chaîne dans un format spécial et la valeur de retour de la fonction est affichée dans le format spécifié par fmt.
nlsparm représente la langue et la fonction renverra le nombre représenté par c.
TO_SINGLE_BYTE()
Convertissez les caractères multi-octets de la chaîne c en caractères équivalents à un octet.
Cette fonction est utilisée uniquement lorsque le jeu de caractères de la base de données contient à la fois des caractères à un octet et plusieurs octets.
Autres fonctions sur une seule ligne
BFILENAME(,)
dir est un objet de type répertoire et file est un nom de fichier. La fonction renvoie un indicateur de valeur de position BFILE vide,
La fonction est utilisée pour initialiser les variables BFILE ou les colonnes BFILE.
DÉCODER(,,[,,,[])
x est une expression, m1 est une expression correspondante, x est comparé à m1, si m1 est égal à x, alors r1 est renvoyé, sinon x est comparé à m2,
Et ainsi de suite m3, m4, m5.... jusqu'à ce que le résultat soit renvoyé.
DÉCHARGE(,[,[,[,]]])
x est une expression ou un caractère et fmt représente un caractère octal, décimal, hexadécimal ou un seul caractère.
La fonction renvoie une valeur de type VARCHAR2 qui contient des informations sur la représentation interne de x.
Si n1,n2 sont spécifiés, alors les octets de longueur n2 à partir de n1 seront renvoyés.
EMPTY_BLOB()
Cette fonction n'a aucun paramètre et renvoie un indicateur de position BLOB vide. Fonction utilisée pour initialiser une variable BLOB ou une colonne BLOB.
EMPTY_CLOB()
Cette fonction n'a aucun paramètre et renvoie un indicateur de position CLOB vide. La fonction est utilisée pour initialiser une variable CLOB ou une colonne CLOB.
LE PLUS GRAND()
exp_list est une liste d'expressions, renvoyant la plus grande expression. Chaque expression est implicitement convertie dans le type de données de la première expression.
Si la première expression appartient à l’un des types de données chaîne, alors le résultat renvoyé est du type de données varchar2,
La comparaison utilisée en même temps est un type de comparaison sans espace.
MOINS()
exp_list est une liste d'expressions, renvoyant la plus petite expression parmi elles. Chaque expression est implicitement convertie dans le type de données de la première expression.
Si la première expression appartient à l'un des types de données chaîne, le résultat renvoyé est du type de données varchar2,
La comparaison utilisée en même temps est un type de comparaison sans espace.
UID
Cette fonction n'a aucun paramètre et renvoie un entier qui identifie de manière unique l'utilisateur actuel de la base de données.
UTILISATEUR
Renvoie le nom d'utilisateur de l'utilisateur actuel
USERENV()
Basé sur le retour opt, il contient des informations sur la session en cours. Les valeurs facultatives pour opt sont :
Le rôle SYSDBA répond dans la session ISDBA et renvoie TRUE.
SESSIONID renvoie l'identifiant de la session d'audit
ENTRYID renvoie les identifiants d'entrée d'audit disponibles
INSTANCE Renvoie l'identifiant de l'instance une fois la session connectée.
Cette valeur n'est utilisée que si vous exécutez un serveur parallèle et disposez de plusieurs instances.
LANGUE renvoie le jeu de caractères des paramètres de langue, de région et de base de données.
LANG renvoie l'abréviation ISO du nom de la langue.
TERMINAL Renvoie l'identifiant du système d'exploitation du terminal ou de l'ordinateur utilisé par la session en cours.
TAILLE()
x est une expression. Renvoie le nombre d'octets représentés en interne par x.
Les fonctions de groupe en SQL sont également appelées fonctions d'agrégation. Elles renvoient un résultat unique basé sur plusieurs lignes. Le nombre exact de lignes ne peut pas être déterminé.
Sauf si la requête est exécutée et que tous les résultats sont inclus. Contrairement aux fonctions à une seule ligne, toutes les lignes sont connues au moment de l'analyse.
En raison de cette différence, les fonctions de groupe ont des exigences et un comportement légèrement différents de ceux des fonctions à une seule ligne.
Fonctions de groupe (à plusieurs lignes) Par rapport aux fonctions à une seule ligne, Oracle fournit un riche ensemble de fonctions à plusieurs lignes basées sur des groupes.
Ces fonctions peuvent être utilisées dans select ou dans la clause have de select, et sont souvent utilisées avec GROUP BY lorsqu'elles sont utilisées pour sélectionner des sous-chaînes.
MOYENNE([{DISYINCT|TOUS}])
Renvoie la moyenne des valeurs numériques. Le paramètre par défaut est TOUS.
SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal)
DE scott.empAVG(SAL) AVG(TOUS SAL) AVG(DISTINCT SAL)1877.94118 1877.94118 1916.071413
COMPTE({*|DISTINCT|TOUS} )
Renvoie le nombre de lignes dans la requête. Le paramètre par défaut est ALL, * signifie renvoyer toutes les lignes.
MAX([{DISTINCT|TOUS}])
Renvoie la valeur maximale de l'élément de liste de sélection. Si x est un type de données chaîne, il renvoie un type de données VARCHAR2.
Si X est un type de données DATA, renvoie une date, si X est un type de données numérique, renvoie un nombre.
Notez que distinct et tous n'ont aucun effet, la valeur maximale doit être la même pour les deux paramètres.
MIN([{DISTINCT|TOUS}])
Renvoie la valeur minimale d'un élément de la liste de sélection.
STDDEV([{DISTINCT|TOUS}])
Renvoie l'écart type des éléments de la liste du sélecteur, où l'écart type est la racine carrée de la variance.
SOMME([{DISTINCT|TOUS}])
Renvoie la somme des valeurs numériques des éléments de la liste de sélection.
VARIANCE([{DISTINCT|TOUS}])
Renvoie la variance statistique d'un élément de liste de sélection.
Utilisez GROUP BY pour regrouper les données Comme le titre l'indique, la fonction de groupe opère sur les données qui ont été regroupées.
Nous indiquons à la base de données comment regrouper ou classer les données à l'aide de GROUP BY. Lorsque nous utilisons la fonction group dans la clause SELECT de l'instruction SELECT,
Il faut placer des colonnes groupées ou non constantes dans la clause GROUP BY Si group by n'est pas utilisé pour un traitement particulier,
Ensuite, la classification par défaut consiste à regrouper l’intégralité du résultat dans une seule catégorie.
sélectionnez stat, compteur (*) zip_count
à partir de l'état zip_codes GROUP BY ;
ST ZIP_COUNT----------AK 360AL 1212AR 1309AZ 768CA 3982
Dans cet exemple, nous utilisons le champ state pour classer ; si nous voulons trier les résultats selon les codes postaux, nous pouvons utiliser l'instruction ORDER BY,
La clause ORDER BY peut utiliser des fonctions de colonne ou de groupe.
sélectionnez stat, compteur (*) zip_count
à partir des codes_postaux
GROUP BY état ORDER BY COUNT(*) DESC;
ST COMPTE(*)---------------NY 4312PA 4297TX 4123CA 3982
Limiter les données groupées avec la clause HAVING
Maintenant que vous savez comment utiliser la fonction main dans l'instruction SELECT et la clause ORDER BY d'une requête, la fonction group ne peut être utilisée que sur deux sous-chaînes.
Les fonctions de groupe ne peuvent pas être utilisées dans les sous-chaînes WHERE. Par exemple, la requête suivante est erronée :
erreur
SELECT vendeur_clerk,SUN(sale_amount)
DE ventes_grossières
OÙ sales_dept='EXTÉRIEUR' ET SUM(sale_amount)>10000
GROUPER PAR commis_vendeur
Dans cette instruction, la base de données ne sait pas ce qu'est SUM(). Lorsque nous devons demander à la base de données de regrouper les lignes, puis limiter la sortie des lignes groupées,
La bonne méthode consiste à utiliser l'instruction HAVING :
SELECT vendeur_clerk,SUN(sale_amount)
DE ventes_grossières
OÙ sales_dept='EXTÉRIEUR'
GROUPER PAR commis_vendeur
AVOIR SUM(sale_amount)>10000 ;
Fonctions imbriquées Les fonctions peuvent être imbriquées. La sortie d’une fonction peut être l’entrée d’une autre fonction. Les opérandes ont un processus d'exécution héritable.
Mais la priorité des fonctions est uniquement basée sur la position, et les fonctions suivent le principe de l'intérieur vers l'extérieur et de gauche à droite.
La technologie d'imbrication est généralement utilisée pour des fonctions telles que DECODE qui peuvent être utilisées dans des instructions de jugement logique IF...THEN...ELSE.