Oracle Pour Les Developpeurs I

7/22/2019 Oracle Pour Les Developpeurs I

1/461

Copyright 2007, Oracle. All rights reserved.

Rcupration des donnes l'aide del'instruction SQL SELECT


2/461Oracle Database 11g: SQL Fundamentals I 1 - 2

Copyright 2007, Oracle. All rights reserved.1 - 2

Objectif

Aprs avoir termin cette leon, vous devriez tre en mesurede faire ce qui suit: Liste des capacits des instructions SELECT SQL Excuter une instruction SELECT de base

objectifs

Pour extraire des donnes de la base de donnes, vous devez utiliser l'instruction SQLSELECT. Cependant, vous devrez peut-tre limiter les colonnes qui sont affiches. Cetteleon dcrit toutes les instructions SQL qui sont ncessaires pour effectuer ces actions. Enoutre, vous pouvez crer des instructions SELECT qui peuvent tre utiliss plus d'une fois.




Agenda de leon

base instruction SELECT Les expressions arithmtiques et des valeurs NULL dansl'instruction SELECT

alias de colonnes L'utilisation de l'oprateur de concatnation des chanes de

caractres littrales, citant l'oprateur alternatif, et le mot-clDISTINCT

commande DESCRIBE




Capacits de requtes SQL SELECT

SelectionProjection

Table 1 Table 2

Table 1Table 1

Jointure

Capacits de requtes SQL SELECT

Une instruction SELECT rcupre les informations de la base de donnes. Avec uneinstruction SELECT, vous pouvez utiliser les fonctionnalits suivantes:

Projection: Slectionner les colonnes d'une table qui sont retournes par une requte.Slectionnez aussi peu ou autant de colonnes que ncessaire.

Slection: Slectionnez les lignes d'une table qui sont retournes par une requte. Plusieurscritres peuvent tre utiliss pour restreindre les lignes qui sont rcupres.

Jointure: Runir les donnes qui sont stockes dans des tables diffrentes en spcifiant lelien entre eux. Jointures SQL sont traits plus en dtail dans la leon intitule Affichage desdonnes dans plusieurs tables."




Base instruction SELECT

SELECT identifie les colonnes afficher. FROM identifie la table contenant les colonnes

SELECT *|{[DISTINCT] column|expression [alias],...}FROM table;

base instruction SELECT

Dans sa forme la plus simple, une instruction SELECT doit comprendre les lments suivants:

Une clause SELECT, qui spcifie les colonnes afficher

Une clause FROM, qui identifie la table contenant les colonnes rpertories dans la clauseSELECT

Dans la syntaxe:

SELECT une liste d'une ou plusieurs colonnes* slectionne toutes les colonnesDISTINCT supprime les doublonscolumn|expression slectionne la colonne nomme ou l'expressionalias donne les colonnes slectionnes diffrentes rubriquesFROM table spcifie le tableau contenant les colonnes

Remarque: Tout au long de ce cours, le mot cl mots, clause, et la dclaration sont utilisscomme suit:

Un mot cl renvoie un lment individuel SQL.? Par exemple, SELECT et FROM sontdes mots-cls.

Une clause est une partie d'une instruction SQL.? Par exemple, SELECT employe_id,last_name, et ainsi de suite est une clause. Une instruction est une combinaison de deux ou plusieurs clauses.? Par exemple,

SELECT * FROM employs est une instruction SQL.




Selection de toutes les Collonnes

SELECT *

FROM departments;

Slection de toutes les colonnes

Vous pouvez afficher toutes les colonnes de donnes dans une table en suivant le mot-clSELECT avec un astrisque (*). Dans l'exemple de la diapositive, dans la table departmentcontient quatre colonnes: departement_id, DEPARTMENT_NAME, manager_id etLOCATION_ID. Le tableau contient huit lignes, une pour chaque dpartement.

Vous pouvez galement afficher toutes les colonnes de la table en numrant toutes les

colonnes aprs le mot-cl SELECT. Par exemple, l'instruction SQL suivante (commel'exemple de la diapositive) affiche toutes les colonnes et toutes les lignes de la tableDPARTEMENTS:

SELECT departement_id, department_name, manager_id, LOCATION_ID FROMDEPARTEMENT;

Remarque: Dans SQL Developer, vous pouvez entrer votre requte SQL dans une feuillede calcul SQL et cliquez sur "Excuter l'instruction" icne ou appuyez sur [F9] pourexcuter l'instruction. La sortie affiche dans la page de rsultats onglets apparat commeindiqu dans la diapositive.




Slectionner des colonnes spcifiques

SELECT department_id, location_id

FROM departments;

Slectionner des colonnes particulires

Vous pouvez utiliser l'instruction SELECT pour afficher des colonnes spcifiques de latable en spcifiant les noms de colonnes, spars par des virgules. L'exemple de ladiapositive affiche tous les numros de dpartement et les numros de localisation partirde la table Departments.

Dans la clause SELECT, spcifiez les colonnes ce que que vous voulez dans l'ordre dans

lequel que vous voulez qu'ils apparatre dans le de sortie. Par exemple, pour afficherl'emplacement avant le numro de dpartement (de gauche droite), vous utilisezl'instruction suivante :

SELECT location_id, department_idFROM departments;




criture d'instructions SQL

Instructions SQL ne sont pas sensibles la casse. SQL peut tre inscrit sur une ou plusieurs lignes. Mots-cls ne peuvent pas tre abrge ou sur plusieurs

lignes. Clauses sont gnralement placs sur des lignes spares. Tirets sont utiliss pour amliorer la lisibilit. Dans SQL Developer, SQL peuvent ventuellement tre

termine par un point-virgule (;). Points-virgules sontncessaires lorsque vous excutez plusieurs instructionsSQL.

Dans SQL * Plus, vous devez terminer chaque instructionSQL avec un point-virgule (;).

Rdaction d'instructions SQLEn utilisant les rgles simples suivantes et les lignes directrices, vous pouvez construire desnoncs valides qui sont la fois facile lire et modifier:

Instructions SQL ne sont pas sensibles la casse (sauf indication contraire). Les instructions SQL peuvent tre inscrits sur une ligne ou d'autres. Mots-cls ne peuvent pas tre rpartis sur plusieurs lignes ou abrgs. Clauses sont gnralement placs sur des lignes spares pour la lisibilit et la facilit de

montage. Tirets doit tre utilis pour rendre le code plus lisible. Mots-cls sont gnralement en majuscules, tous les autres mots, tels que les noms de

tables et colonnes sont entrs en minuscules.Excution d'instructions SQL

Dans SQL Developer, cliquez sur l'icne Excuter le script ou appuyez sur [F5] pour excuter la oules commandes dans la feuille de calcul SQL. Vous pouvez galement cliquer sur l'icne DclarationExcuter ou appuyez sur [F9] pour excuter une instruction SQL dans la feuille de calcul SQL. LaDclaration icne Excuter excute l'instruction au niveau du pointeur de la souris dans la zoneEntrez l'instruction SQL lorsque l'icne Excuter le script excute toutes les instructions de la bote

de dialogue Entrer une instruction SQL. L'icne Dclaration Excuter affiche le rsultat de larequte sur la page des rsultats onglets lorsque l'icne Excuter le script mule SQL * Plusd'affichage et affiche le rsultat sur la page de sortie Script onglets.Dans SQL * Plus, mettre fin l'instruction SQL avec un point-virgule, puis appuyez sur [Entre] pourexcuter la commande.




entte de colonne par dfaut

SQL Developer: Par dfaut alignement de rubrique: gauche Par dfaut affichage rubrique : Majuscules

SQL * Plus: enttes de colonne de caractres et la date sont aligns

gauche. Enttes de colonne Nombre sont aligns droite.

Par dfaut rubrique affichage: Majuscules

Tte de colonne par dfaut

Dans SQL Developer, ttes de colonne sont affichs en majuscules et sont alignes gauche.

SELECT last_name, hire_date, le salaire

FROM employs;

Vous pouvez remplacer l'affichage tte de colonne avec un alias. Alias de colonnes sonttraits plus loin dans cette leon.




Agenda de leon




commande DESCRIBE




Expressions Arithmtiques

Crer des expressions avec de nombre et de date l'aide desoprateurs arithmtiques.

Multiply*

Divide/

Subtract-

Add+

DescriptionOperator

Expressions arithmtiques

Vous devrez peut-tre modifier la faon dont les donnes sont affiches, ou vous pouvezeffectuer des calculs, ou de regarder des scnarios de simulation. Tout cela est possibleen utilisant des expressions arithmtiques. Une expression arithmtique peut contenir desnoms de colonnes, constantes et les valeurs numriques, les oprateurs arithmtiques.

oprateurs arithmtiques

La diapositive prsente les oprateurs arithmtiques qui sont disponibles dans SQL. Vouspouvez utiliser des oprateurs arithmtiques dans toute clause d'une instruction SQL (saufla clause FROM).

Remarque: la date et types de donnes timestamp, vous pouvez utiliser l'addition et lasoustraction oprateurs seulement.




SELECT last_name, salary, salary + 300

FROM employees;

Utilisation des oprateurs arithmtiques

Utilisation des oprateurs arithmtiquesL'exemple de la diapositive utilise l'oprateur d'addition pour calculer une augmentation de salairede 300 $ pour tous les employs. La diapositive affiche galement une SALAIRE 300 colonne dansla sortie.Notez que la colonne rsultante calcule, SALAIRE +300, n'est pas une nouvelle colonne dans latable EMPLOYEES, c'est uniquement pour l'affichage. Par dfaut, le nom d'une nouvelle colonneprovient du calcul qui l'a gnr, dans ce cas, le salaire de +300.Remarque: Le serveur Oracle ignore les espaces vides avant et aprs l'oprateur arithmtique.priorit des oprateurs

Si une expression arithmtique contient plus d'un oprateur, la multiplication et la division sontvalues en premier. Si les oprateurs dans une expression sont de la mme priorit, l'valuationse fait de gauche droite.Vous pouvez utiliser des parenthses pour forcer l'expression qui est dlimite par les parenthsestre values en premier.Rgles de priorit:

Multiplication et la division se produire avant l'addition et la soustraction.Les oprateurs de mme priorit sont valus de gauche droite.

Les parenthses sont utilises pour remplacer la priorit par dfaut ou clarifier la dclaration.




SELECT last_name, salary, 12*salary+100

FROM employees;

priorit des oprateurs

SELECT last_name, salary, 12*(salary+100)

FROM employees;

1

2

Priorits des oprateurs (suite)

Le premier exemple de la diapositive affiche le nom de famille, le salaire et la rmunrationannuelle des employs. Il calcule la rmunration annuelle en multipliant le salaire mensuelde 12, plus un bonus unique de 100 $. Notez que la multiplication est effectue avantl'addition.

Remarque: Utilisez les parenthses pour renforcer l'ordre standard de priorit et

d'amliorer la clart. Par exemple, l'expression de la lame peut tre crite comme (12 *salaire) +100 sans aucun changement dans le rsultat.

Utilisation des parenthses

Vous pouvez remplacer les rgles de priorit en utilisant des parenthses pour prciserl'ordre souhait dans lequel les oprateurs doivent tre excutes.

Le deuxime exemple de la diapositive affiche le nom de famille, le salaire et larmunration annuelle des employs. Il calcule la rmunration annuelle comme suit:l'ajout d'une prime mensuelle de 100 $ au salaire mensuel, puis en multipliant ce montantpar 12. cause des parenthses, on ajoute la priorit sur la multiplication.


14/461




SELECT last_name, 12*salary*commission_pct

FROM employees;

Valeurs NULL dans les expressions arithmtiques

Les expressions arithmtiques contenant une valeur NULL est

valus NULL.

Valeurs NULL dans les expressions arithmtiques

Si aucune valeur de colonne dans une expression arithmtique est nulle, le rsultat est nul.Par exemple, si vous tentez d'effectuer une division par zro, vous obtenez une erreur.Toutefois, si vous divisez un nombre par NULL, le rsultat est une valeur nulle ouinconnue.

Dans l'exemple de la diapositive, le roi employ ne reoit pas de commission. Parce que la

colonne COMMISSION_PCT dans l'expression arithmtique est NULL, le rsultat est nul.Pour plus d'informations, reportez-vous la section lments de base d'Oracle SQL"dans Oracle Database 11g SQL Langage de rfrence, Release 1 (11.1).




Agenda de leon




commande DESCRIBE


17/461




Utilisation des alias de colonnes

SELECT last_name "Name" , salary*12 "Annual Salary"FROM employees;

SELECT last_name AS name, commission_pct comm

FROM employees;

Utilisation des alias de colonnes

Le premier exemple affiche les noms et les pourcentages des commissions de tous lesemploys. Notez que le mot-cl optionnel AS a t utilise avant l'alias de la colonne. Lersultat de la requte est le mme que le mot-cl AS est utilis ou non. En outre, notez quel'instruction SQL possde les alias de colonne, le nom et comm, en minuscules, tandis quele rsultat de la requte affiche les titres des colonnes en majuscules. Comme mentionndans la diapositive prcdente, ttes de colonnes apparaissent en majuscules par dfaut.

Le second exemple affiche les noms de famille et les salaires annuels de tous lesemploys. Parce Salaire annuel contient un espace, il a t plac entre guillemets doubles.Notez que le titre de la colonne dans la sortie est exactement le mme que l'alias decolonne.




Agenda de leon




commande DESCRIBE




Oprateur de concatnation

Un oprateur de concatnation: Liens colonnes ou des chanes de caractres aux autrescolonnes

Est reprsent par deux barres verticales (| |) Cre une colonne rsultante qui est une expression de

caractres

SELECT last_name||job_id AS "Employees"

FROM employees;

Oprateur de concatnation

Vous pouvez lier des colonnes d'autres colonnes, expressions arithmtiques ou desvaleurs constantes pour crer une expression de caractres en utilisant l'oprateur deconcatnation (| |). Colonnes de chaque ct de l'oprateur sont combins pour former unecolonne de sortie unique.

Dans l'exemple, LAST_NAME et JOB_ID sont concatnes, et tant donn les employs

pseudonyme. Notez que le nom de famille de l'employ et le code de travail sont combinspour crer une colonne de sortie unique.

Le mot cl AS avant le nom de l'alias fait la clause SELECT facile lire.

Valeurs NULL avec l'oprateur de concatnation

Si vous concatner une valeur NULL avec une chane de caractres, le rsultat est unechane de caractres. LAST_NAME | | NULL dans les rsultats LAST_NAME.

Remarque: Vous pouvez galement concatner des expressions de date avec d'autresexpressions ou des colonnes.




Chanes de caractres littrales

Un littral est un personnage, un nombre ou une date qui estinclus dans l'instruction SELECT. Les valeurs de date et le caractre littral doit tre place

entre guillemets simples. Chaque chane de caractres est sortie une seule fois pour

chaque ligne renvoye.

Chanes de caractres littrales

Un littral est un personnage, un nombre ou une date qui est inclus dans la liste SELECT.Ce n'est pas un nom de colonne ou un alias de colonne. Il est imprim pour chaque lignerenvoye. Les chanes littrales de libre-format texte peut tre inclus dans le rsultat de larequte et sont traits comme une colonne dans la liste SELECT.

Les littraux de date et de caractre doit tre plac entre guillemets simples (''); littraux

numriques ne doivent pas tre enferm dans une manire similaire.




Utilisation de chanes de caractres littrales

SELECT last_name ||' is a '||job_id

AS "Employee Details"

FROM employees;

Utilisation de chanes de caractres littralesL'exemple de la diapositive affiche les noms de famille et les codes d'emploi de tous lesemploys. La colonne prsente les dtails des employs rubrique. Notez les espaces entreles guillemets simples dans l'instruction SELECT. Les espaces amliorer la lisibilit de lasortie.Dans l'exemple suivant, le nom et le salaire de chaque employ sont concatns avec unlittral, de donner les lignes retournes plus de sens :

SELECT last_name ||': 1 Month salary = '||salary MonthlyFROM employees;




Alternative Citation de l'oprateur (q)

Spcifiez votre propre guillemet dlimiteur. Slectionnez n'importe quel dlimiteur. Augmenter la lisibilit et la convivialit.

SELECT department_name || ' Department' ||

q'['s Manager Id: ]'

|| manager_id

AS "Department and Manager"

FROM departments;

Alternative Citation (q) de l'oprateur

De nombreuses instructions SQL utiliser des littraux caractres dans les expressions ouconditions. Si le littral lui-mme contient un guillemet simple, vous pouvez utiliser lacitation (q) l'oprateur et choisir votre propre guillemet dlimiteur.

Vous pouvez choisir n'importe quel dlimiteur pratique, un octet ou multi-octets, oun'importe lequel des paires de caractres suivants: [], {}, (), ou .

Dans l'exemple illustr, la chane contient un guillemet simple, qui est normalementinterprt comme un dlimiteur de chane de caractres. l'aide de l'oprateur q,cependant, les crochets [] sont utiliss comme dlimiteurs guillemet. La chane entre lesdlimiteurs supports est interprte comme une chane de caractres littrale.




duplication les lignes

L'affichage par dfaut des requtes est que toutes les lignes, y

compris les lignes dupliques.SELECT department_id

FROM employees;

SELECT DISTINCT department_id

FROM employees;

1

2

dupliquer les lignes moins d'indication contraire, SQL affiche les rsultats d'une requte sans liminer lesdoublons. Le premier exemple de la diapositive affiche tous les numros de dpartementde la table Employees. Notez que les numros de dpartement sont rptes.Pour liminer les doublons dans le rsultat, inclure le mot cl DISTINCT dans la clauseSELECT immdiatement aprs le mot-cl SELECT. Dans le deuxime exemple de ladiapositive, la table Employees contient en fait 20 lignes, mais il ya seulement sept

numros de dpartement uniques dans la table.Vous pouvez spcifier plusieurs colonnes aprs le qualificateur DISTINCT. Le qualificatifDISTINCT affecte toutes les colonnes slectionnes, et le rsultat est chaquecombinaison distincte des colonnes.

SELECT DISTINCT department_id, job_idFROM employees;




Agenda de leon




commande DESCRIBE




Affichage de la structure dune table

Use the DESCRIBE command to display the structure of atable.

Or, select the table in the Connections tree and use theColumns tab to view the table structure.

DESC[RIBE] tablename

Affichage de la structure du tableau

Dans SQL Developer, vous pouvez afficher la structure d'une table l'aide de lacommande DESCRIBE. La commande affiche les noms de colonnes et les types dedonnes, et il vous indique si une colonne doit contenir des donnes (c'est--dire, si lacolonne a une contrainte NOT NULL).

Dans la syntaxe, le nom de table est le nom d'une table existante, voir, ou un synonyme

qui est accessible l'utilisateur.Utilisation de l'interface GUI SQL Developer, vous pouvez slectionner la table dans l'arbrede connexions et utilisez l'onglet Colonnes pour afficher la structure de la table.

Remarque: La commande DESCRIBE est soutenu la fois par SQL * Plus et SQLDeveloper.




Utilisation de la commande DESCRIBE

DESCRIBE employees

Utilisation de la commande DESCRIBEL'exemple de la diapositive affiche des informations sur la structure de la table EMPLOYESl'aide de la commande DESCRIBE.Dans l'affichage qui en rsulte, Null indique que les valeurs de cette colonne peut treinconnue. NOT NULL indique qu'une colonne doit contenir des donnes. Type affiche letype de donnes pour une colonne.Les types de donnes sont dcrits dans le tableau suivant::

Data Type DescriptionNUMBER(p,s)

Number value having a maximum number of digitsp, withsdigits to the right of the decimal point

VARCHAR2(s) Variable-length character value of maximum size sDATE Date and time value between January 1, 4712 B.C. and

December 31, A.D. 9999.CHAR(s) Fixed-length character value of size s




rsum

Dans cette leon, vous devriez avoir appris : Rdiger une instruction SELECT: Renvoie toutes les lignes et colonnes d'un tableau Retourne les colonnes spcifies d'une table Utilise des alias de colonne pour afficher ttes de colonne

plus descriptifs

SELECT *|{[DISTINCT] column|expression [alias],...}

FROMtable;

Instruction SELECT

Dans cette leon, vous devriez avoir appris comment rcuprer des

donnes d'une table de base de donnes avec l'instruction SELECT

.

SELECT *|{[DISTINCT] column [alias],...}FROM table;

Dans la syntaxe:SELECT est une liste d'un ou de plusieurs colonnes

* Slectionne toutes les colonnes

DISTINCT supprime les doublons

colonne | expression slectionne la colonne nomme ou l'expression

alias donne les colonnes slectionnes diffrentes rubriques

FROM table spcifie la table contenant les colonnes




Exercice 1: Vue d'ensemble

Cette pratique couvre les sujets suivants: Slection de toutes les donnes de diffrentes tables Dcrivant la structure des tables Effectuer des calculs arithmtiques et en spcifiant les noms

de colonnes

Exercice 1: Vue d'ensemble

Dans cette pratique, vous crivez des requtes SELECT simples. Les requtes couvrent laplupart des clauses SELECT et les oprations que vous avez appris dans cette leon.



pratique 1partie 1

Testez vos connaissances:1. L'instruction SELECT suivante s'excute avec succs:

SELECT last_name, job_id, salary AS Sal FROM employs;?Vrai / Faux2. L'instruction SELECT suivante s'excute avec succs:

SELECT *DE job_grades;?

Vrai / Faux3. Il ya quatre erreurs de codage dans la dclaration suivante. Pouvez-vous les identifier?

SELECT employee_id, last_namesal * 12 "SALAIRE ANNUEL"FROM employees;

Partie 2

Notez les points suivants avant de commencer avec les pratiques:Sauvegardez tous vos fichiers laboratoire l'emplacement suivant: D: \ \ \ SQL1 laboratoireslaboratoires

Entrez vos instructions SQL dans une feuille de calcul SQL. Pour enregistrer un script dans SQLDeveloper, assurez-vous que la feuille de calcul requise SQL est actif, puis dans le menu Fichier,slectionnez Enregistrer sous ou faites un clic droit dans la feuille de calcul SQL et slectionnezEnregistrer le fichier pour enregistrer votre instruction SQL comme lab_ _ .sql. Lorsque vous modifiez un script existant, assurez-vous que vous utilisez la commandeEnregistrer sous pour l'enregistrer sous un autre nom.

Pour excuter la requte, cliquez sur Excuter l'icne Dclaration la feuille de calcul SQL.

Alternativement, vous pouvez appuyer sur [F9]. Pour les instructions DML et DDL, utilisez le scriptRun icne ou appuyez sur [F5].Aprs avoir excut la requte, assurez-vous que vous ne saisissez pas votre requte suivante

dans la mme feuille de calcul. Ouvrez une nouvelle feuille de calcul.Vous avez t embauch comme programmeur SQL pour Acme Corporation. Votre premire tcheconsiste crer des rapports bass sur les donnes des tableaux des ressources humaines.4. Votre premire tche consiste dterminer la structure de la table Departments et son contenu.



La pratique 1 (suite)

5. Vous devez dterminer la structure de la table EMPLOYEES.

Le dpartement des ressources humaines veut une requte pour afficher le nom de famille,le code du travail, la date d'embauche, et le numro d'employ pour chaque employ, avecle numro d'employ apparaissant en premier. Fournir un STARTDATE alias pour lacolonne hire_date. Enregistrer votre instruction SQL dans un fichier nomm lab_01_05.sqlde sorte que vous pouvez distribuer ce fichier dans le dpartement des ressourceshumaines.




6. Testez votre requte dans le fichier lab_01_05.sql pour s'assurer qu'il fonctionnecorrectement.

Remarque: Aprs avoir excut la requte, assurez-vous que vous ne saisissez pas votrerequte suivante dans la mme feuille de calcul. Ouvrez une nouvelle feuille de calcul.

7. Le dpartement des ressources humaines veut une requte pour afficher tous les codesd'emploi uniques de la table EMPLOYS.




partie 3

Si vous avez le temps, faites les exercices suivants:

8. Le dpartement RH veut-ttes de colonne plus descriptifs pour son rapport sur lesemploys. Copiez le communiqu de lab_01_05.sql une nouvelle feuille de calcul SQL.

Nommez la colonne de rubriques Emp #, employs, d'emploi, et date d'embauche,respectivement. Ensuite, excutez votre requte nouveau.

9. Le dpartement RH a demand un rapport de tous les employs et leurs identificateursd'emploi. Afficher le nom concatn avec l'ID du travail (spars par une virgule etl'espace) et le nom de l'employ et de la colonne Titre.


34/461


35/461


Restriction et tri des donnes




Objectif

Aprs avoir termin cette leon, vous devriez tre en mesurede faire ce qui suit: Limitez les lignes qui sont rcupres par une requte Trier les lignes qui sont rcupres par une requte Utiliser la substitution et commercial de restreindre et trier la

sortie au moment de l'excution

objectifs

Lors de la rcupration des donnes de la base de donnes, vous devrez peut-treeffectuer les oprations suivantes:

Restreindre les lignes de donnes qui sont affiches

Spcifier l'ordre dans lequel les lignes sont affiches

Cette leon explique les instructions SQL que vous utilisez pour effectuer les actions

rpertories ci-dessus.




Lesson Agenda

Limiter les lignes avec: La clause WHERE Les conditions de comparaison avec =,




Limite des lignes en utilistant Selection

rcuprer tous lesemploys du service90

EMPLOYEES

Limite des ligne en utilisant Selection

Dans l'exemple de la diapositive, supposons que vous souhaitez afficher tous les employs duservice 90. Les lignes avec une valeur de 90 dans la colonne DEPARTMENT_ID sont les seulsqui sont retournes. Cette mthode de restriction est la base de la clause WHERE en SQL.




Limite des lignes en utilistant Selection

Restreindre les lignes qui sont retournes en utilisant laclause WHERE:

La clause WHERE suit la clause FROM.

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table

[WHERE condition(s)];

Limiter les lignes slectionnes

Vous pouvez restreindre les lignes qui sont retournes par la requte en utilisant la clauseWHERE. Une clause WHERE contient une condition qui doit tre remplie et il dcouledirectement de la clause FROM. Si la condition est vraie, la ligne remplisse la condition estretourn.

Dans la syntaxe:

WHERE restreint la requte de lignes qui rpondent une condition condition est compose de noms de colonnes, des expressions, des constantes et un oprateur

de comparaison. Une condition spcifie un? combinaison d'une ou plusieurs expressionslogiques et (boolen)? oprateurs, et renvoie la valeur TRUE, FALSE ou UNKNOWN.

La clause WHERE peut comparer les valeurs dans les colonnes, littrales des expressionsarithmtiques, ou des fonctions. Il se compose de trois lments:

nom de la colonne

condition de comparaison

Nom de la colonne, une constante ou liste de valeurs




SELECT employee_id, last_name, job_id, department_id

FROM employees

WHERE department_id = 90 ;

Utilisation de la clause WHERE

Utilisation de la clause WHERE

Dans l'exemple, l'instruction SELECT rcupre l'ID employ, nom, ID du travail, et lenumro de dpartement de tous les employs qui sont dans le dpartement 90.

Remarque: Vous ne pouvez pas utiliser les alias de colonne dans la clause WHERE.




SELECT last_name, job_id, department_id

FROM employees

WHERE last_name = 'Whalen' ;

Chanes de caractres et dates

Les chanes de caractres et les valeurs de date sontentours par des guillemets simples. Les valeurs de caractre sont sensibles la casse et les

valeurs de date sont au format sensible. Le format d'affichage par dfaut la date est DD-MON-RR.

SELECT last_name

FROM employees

WHERE hire_date = '17-FEB-96' ;

Chanes de caractres et dates

Les chanes de caractres et des dates dans la clause WHERE doit tre encadre par desguillemets simples (''). Nombre constantes, toutefois, ne doit pas tre encadre par desguillemets simples.

Toutes les recherches de caractres sont sensibles la casse. Dans l'exemple suivant,aucune ligne n'est renvoye parce que la table EMPLOYES stocke tous les noms de

famille dans le cas mixte:SELECT last_name, job_id, department_idFROM employeesWHERE last_name = 'WHALEN';

Bases de donnes Oracle stocker les dates dans un format numrique interne, ce qui reprsentesicle, anne, mois, jour, heures, minutes et secondes. L'affichage de la date par dfaut est auformat DD-MON-RR.

Remarque: Pour plus de dtails sur le format de RR et de changer le format de date par dfaut,voir la leon intitule Utilisation simple range fonctions de personnalisation de sortie." Enoutre, vous renseigner sur l'utilisation d'une seule range de fonctions telles que la suprieure et

infrieure pour remplacer la sensibilit la casse dans la mme leon.




oprateurs de comparaison

Not equal to

Between two values (inclusive)BETWEEN...AND...

Match any of a list of valuesIN(set)

Match a character patternLIKE

Is a null valueIS NULL

Less than

Equal to=MeaningOperator

oprateurs de comparaison

Les oprateurs de comparaison sont utiliss dans des conditions qui permettent decomparer une expression une autre valeur ou une expression. Ils sont utiliss dans laclause WHERE dans le format suivant:

Syntaxe

... WHERE expr operator value

Example... WHERE hire_date = '01-JAN-95'

... WHERE salary >= 6000

... WHERE last_name = 'Smith'

Un alias ne peut pas tre utilise dans la clause WHERE.

Remarque: Les symboles = et ^ = peut aussi reprsenter le pas gale condition!.




SELECT last_name, salary

FROM employees

WHERE salary




SELECT last_name, salary

FROM employees

WHERE salary BETWEEN 2500 AND 3500 ;

Conditions d'intervalle avec loprateur BETWEEN

Utilisez l'oprateur BETWEEN pour afficher des lignes enfonction d'une plage de valeurs:

Lower limit Upper limit

Conditions d'intervalle avec loprateur BETWEEN

Vous pouvez afficher des lignes en fonction d'une srie de valeurs l'aide de l'oprateurBETWEEN. La plage que vous spcifiez contient une limite infrieure et une limite suprieure.

L'instruction SELECT dans la diapositive renvoie des lignes de la table Employees pour toutemploy dont le salaire se situe entre $ 2.500 et $ 3.500.

Valeurs qui sont spcifies l'oprateur BETWEEN sont inclus. Toutefois, vous devez spcifier lalimite infrieure d'abord.

Vous pouvez galement utiliser l'oprateur BETWEEN sur les valeurs de caractres :SELECT last_nameFROM employeesWHERE last_name BETWEEN 'King' AND 'Smith';




SELECT employee_id, last_name, salary, manager_id

FROM employees

WHERE manager_id IN (100, 101, 201) ;

Condition d'appartenance Utilisation del'oprateur IN

Utilisez l'oprateur IN pour tester les valeurs dans une liste:

Condition d'appartenance Utilisation de l'oprateur IN

Pour tester les valeurs dans un ensemble spcifi de valeurs, utilisez l'oprateur IN. Lacondition dfinie l'aide de l'oprateur IN est galement connu comme la conditiond'appartenance.

L'exemple diaporama affiche le nombre d'employs, noms de famille, les salaires, et lesnumros des employs des gestionnaires de tous les employs dont le responsable est le

numro d'employ est de 100, 101 ou 201.L'oprateur peut tre utilis avec n'importe quel type de donnes. L'exemple suivantretourne une ligne de la table EMPLOYES, pour tout employ dont le nom figure dans laliste des noms dans la clause WHERE :

SELECT employee_id, manager_id, department_idFROM employeesWHERE last_name IN ('Hartstein', 'Vargas');

Si les caractres ou les dates sont utilises dans la liste, elles doivent tre encadres par desguillemets simples ('').

Remarque: L'oprateur se fait en interne par le serveur Oracle comme un ensemble de conditionsOR, tels que a = valeur1 = valeur2 ou une ou un = valeur3. Par consquent, en utilisant l'oprateurIN n'a pas d'avantages de performance et est utilis seulement pour la simplicit logique.


46/461




En combinant les caractres gnriques

Vous pouvez combiner les deux caractres gnriques (%,_) avec des caractres littrales de filtrage:

Vous pouvez utiliser l'identificateur ESCAPE pour rechercherl'% rel et symboles _.

SELECT last_name

FROM employees

WHERE last_name LIKE '_o%' ;

En combinant les caractres gnriques

Les symboles% et _ peuvent tre utiliss dans n'importe quelle combinaison de caractreslittraux. L'exemple de la diapositive affiche les noms de tous les employs dont les noms ont lalettre o comme deuxime caractre.

ESCAPE Identificateur

Lorsque vous avez besoin d'avoir une correspondance exacte pour les caractres% et _ relles,

utilisez l'identificateur ESCAPE. Cette option spcifie ce que le caractre d'chappement est. Sivous souhaitez rechercher des chanes qui contiennent SA_, vous pouvez utiliser l'instruction SQLsuivante :

SELECT employee_id, last_name, job_idFROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';

L'identificateur ESCAPE identifie la barre oblique inverse (\) comme caractre d'chappement.Dans l'instruction SQL, le caractre d'chappement prcde le trait de soulignement (_). Celaprovoque le serveur Oracle pour interprter littralement le trait de soulignement.




SELECT last_name, manager_id

FROM employees

WHERE manager_id IS NULL ;

Utilisation des Conditions NULL

Test de null avec l'oprateur IS NULL.

Utilisation des Conditions NULL

Les conditions NULL inclure la condition IS NULL et IS NOT NULL tat .

L'IS NULL pour les tests de condition nuls. Une valeur nulle signifie que la valeur n'est pasdisponible, non affecte, inconnu ou inapplicable. Par consquent, vous ne pouvez pas tester avec=, parce que nul ne peut tre gal ou ingal n'importe quelle valeur. L'exemple diaporamarcupre les noms de famille et les gestionnaires de tous les employs qui n'ont pas ungestionnaire.

Voici un autre exemple: Pour afficher le dernier nom, ID du travail, et la commission pour tous lesemploys qui n'ont pas droit de recevoir une commission, utilisez l'instruction SQL suivante :

SELECT last_name, job_id, commission_pctFROM employeesWHERE commission_pct IS NULL;




Dfinition des conditions utilisant les oprateurslogiques

Returns TRUE if the condition is falseNOT

Returns TRUE if eithercomponent conditionis true

OR

Returns TRUE if bothcomponent conditionsare true

AND

MeaningOperator

Dfinition des conditions utilisant les oprateurs logiques

A condition logique combine le rsultat de deux dfauts des composants pour produire unrsultat unique sur la base de ces conditions ou inverse le rsultat d'un tat unique. Uneligne est retourne seulement si le rsultat global de la condition est vraie.

Trois oprateurs logiques sont disponibles en SQL:

AND

ORNOT

Tous les exemples jusqu'ici ont indiqu une seule condition dans la clause WHERE. Vouspouvez utiliser plusieurs conditions dans une seule clause WHERE utilisant le oprateursAND et OR.




SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >= 10000

AND job_id LIKE '%MAN%' ;

Utilisation de l'oprateur AND

AND ncessite la fois les conditions composants pour tre vrai:

Utilisation de l'oprateur ET

Dans l'exemple, les deux composantes les conditions doivent tre remplies pour n'importe quelenregistrement pour tre slectionn. Par consquent, seuls les employs qui ont un titre d'emploiqui contient "MAN" la chane et gagner 10.000 $ ou plus sont slectionnes.

Toutes les recherches de caractres sont sensibles la casse, c'est-aucune ligne n'est renvoye si"MAN" n'est pas en majuscules. En outre, les chanes de caractres doivent tre placs entreguillemets.

ET Table de vrit

Le tableau suivant montre les rsultats de la combinaison de deux expressions avec AND :

AND TRUE FALSE NULL

TRUE TRUE FALSE NULL

FALSE FALSE FALSE FALSENULL NULL FALSE NULL




SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >= 10000

OR job_id LIKE '%MAN%' ;

Utilisation de l'oprateur OR

OR ncessite soit l'tat des composants pour tre vrai:

Utilisation de l'oprateur OR

Dans l'exemple, la condition composant supporte peut tre le cas pour un enregistrement slectionner. Par consquent, tout employ qui a un ID de travail qui contient "MAN" lachane ou gagne 10.000 $ ou plus est slectionn.

Table de vrit OR

Le tableau suivant montre les rsultats de la combinaison de deux expressions avec OR:

OR TRUE FALSE NULL

TRUE TRUE TRUE TRUE

FALSE TRUE FALSE NULLNULL TRUE NULL NULL




SELECT last_name, job_id

FROM employees

WHERE job_id

NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;

Utilisation de l'oprateur NOT

Utilisation de l'oprateur NOT

L'exemple diaporama affiche le nom et l'ID du travail de tous les employs dont le travaild'identification n'est pas IT_PROG, ST_CLERK ou SA_REP.

Table de vrit NOT

Le tableau suivant montre le rsultat de l'application de l'oprateur NOT une condition:

Remarque: L'oprateur NOT peut galement tre utilis avec d'autres oprateurs SQL, commeBETWEEN, LIKE et NULL.

... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP')

... WHERE salary NOT BETWEEN 10000 AND 15000

... WHERE last_name NOT LIKE '%A%'

... WHERE commission_pct IS NOT NULL

NOT TRUE FALSE NULLFALSE TRUE NULL




Lesson Agenda





Rgles de priorit

Vous pouvez utiliser des parenthses pour modifier les rgles deprsance.

Not equal to6NOT logical condition7

AND logical condition8

OR logical condition9

IS [NOT] NULL, LIKE, [NOT] IN4

[NOT] BETWEEN5

Comparison conditions3

Concatenation operator2

Arithmetic operators1

MeaningOperator

Rgles de priorit

Les rgles de priorit dtermine l'ordre dans lequel les expressions sont values etcalcules. Le tableau de la diapositive indique l'ordre de priorit par dfaut. Cependant,vous pouvez modifier l'ordre par dfaut en utilisant des parenthses autour desexpressions que vous voulez calculer d'abord.




SELECT last_name, job_id, salary

FROM employeesWHERE job_id = 'SA_REP'

OR job_id = 'AD_PRES'

AND salary > 15000;

Rgles de priorit

SELECT last_name, job_id, salary

FROM employees

WHERE (job_id = 'SA_REP'

OR job_id = 'AD_PRES')

AND salary > 15000;

1

2

Rgles de priorit (suite)

1. Primaut de l'oprateur AND: Exemple

Dans cet exemple, il ya deux conditions:

La premire condition est que l'ID du travail est AD_PRES et le salaire est suprieur 15.000 $.

La deuxime condition est que l'ID du travail est SA_REP.

Par consquent, l'instruction SELECT se lit comme suit:

"Slectionnez la ligne si un employ est un prsident et gagne plus de $ 15.000, ou si le salariest un reprsentant des ventes."

2. Utilisation de parenthses: Exemple

Dans cet exemple, il ya deux conditions:

La premire condition est que l'ID du travail est AD_PRES ou SA_REP.

La deuxime condition est que le salaire est suprieur 15.000 $.

Par consquent, l'instruction SELECT se lit comme suit:

"Slectionnez la ligne si un employ est un prsident ou un reprsentant des ventes, et sil'employ gagne plus de $ 15.000."




Lesson Agenda





Utilisation de la clause ORDER BY

Trier rcuprer les lignes avec la clause ORDER BY:

ASC: ordre croissant par dfaut DESC: Par ordre dcroissant

La clause ORDER BY vient en dernier dans l'instructionSELECT:SELECT last_name, job_id, department_id, hire_date

FROM employees

ORDER BY hire_date ;

Utilisation de la clause ORDER BY

L'ordre des lignes qui sont retournes dans un rsultat de requte est indfini. La clauseORDER BY peut tre utilise pour trier les lignes. Toutefois, si vous utilisez la clauseORDER BY, elle doit tre la dernire clause de l'instruction SQL. En outre, vous pouvezspcifier une expression, un alias ou une position de colonne que la condition de tri.

syntaxeSELECT expr

FROM table[WHERE condition(s)][ORDER BY {column, expr, numeric_position} [ASC|DESC]];

Dans la syntaxe: ORDER BY spcifie l'ordre dans lequel les lignes rcupres sont affichs ASC les lignes en ordre croissant (ce qui est l'ordre par dfaut) DESC ordonne les lignes dans l'ordre dcroissant

Si la clause ORDER BY n'est pas utilis, l'ordre de tri n'est pas dfini, et le serveur Oracle ne peut pasrcuprer les lignes dans le mme ordre pour la mme requte deux reprises. Utiliser la clause ORDERBY pour afficher les lignes dans un ordre spcifique.

Remarque: Utilisez les valeurs NULL premiers mots cls ou NULLS DERNIERS de spcifier si les lignesrenvoyes contenant des valeurs NULL doit apparatre en premier ou en dernier dans la squence de lacommande.




Tri

Le tri dans l'ordre dcroissant:

Tri par alias de colonne:

SELECT last_name, job_id, department_id, hire_date

FROM employees

ORDER BY hire_date DESC ; 1

SELECT employee_id, last_name, salary*12 annsal

FROM employees

ORDER BY annsal ;

2

triL'ordre de tri par dfaut est croissant:Les valeurs numriques sont affiches avec les valeurs les plus basses en premier (par exemple, 1 999).Les valeurs de date sont affichs avec la premire premire valeur (par exemple, 01-JAN-92 avant?01-JAN-95).Les valeurs de caractre sont affiches dans l'ordre alphabtique (par exemple, "A" d'abord et "Z"dernire).Les valeurs nulles sont affiches la fin pour les squences ascendantes et le premier pour ladescente des squences.Vous pouvez galement trier selon une colonne qui n'est pas dans la liste SELECT.Exemples:1. Pour inverser l'ordre dans lequel les lignes sont affiches, spcifiez le mot-cl DESC aprs le nomde la colonne dans la clause ORDER BY. Les sortes de diapositives par exemple le rsultat del'employ plus rcemment embauch.2. Vous pouvez galement utiliser un alias de colonne dans la clause ORDER BY. L'exemple dediapositive trie les donnes par salaire annuel.




Tri

Le tri en utilisant la position numrique de la colonne:

Le tri par colonnes multiples:

SELECT last_name, job_id, department_id, hire_date

FROM employees

ORDER BY 3; 3

SELECT last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC;4

Tri (suite)

Exemples:

3. Vous pouvez trier les rsultats de la requte en spcifiant la position numrique de lacolonne dans la clause SELECT. Les sortes de diapositives par exemple le rsultat parle departement_id que cette colonne est la troisime position dans la clause SELECT.

4. Vous pouvez trier les rsultats de la requte par plus d'une colonne. La limite de tri est le

nombre de colonnes dans la table donne. Dans la clause ORDER BY, spcifiez lescolonnes et sparer les noms de colonnes par des virgules. Si vous souhaitez inverserl'ordre d'une colonne, spcifiez DESC aprs le nom.


60/461




variables de substitution

... salary = ? department_id = ? ... last_name = ? ...

je veuxinterrogerdiffrentvaleurs.


Jusqu' prsent, toutes les instructions SQL ont t excutes avec des colonnesprdfinies, des conditions et de leurs valeurs. Supposons que vous vouliez une requtequi rpertorie les employs avec des emplois diffrents, et pas seulement ceux dont job_idest SA_REP. Vous pouvez modifier la clause WHERE pour fournir une valeur diffrente chaque fois que vous excutez la commande, mais il est aussi un moyen plus facile.

En utilisant une variable de substitution la place des valeurs exactes dans la clauseWHERE, vous pouvez excuter la mme requte pour diffrentes valeurs.

Vous pouvez crer des rapports qui invitent les utilisateurs fournir leurs propres valeurs restreindre l'ventail des donnes retournes, l'aide de variables de substitution. Vouspouvez intgrer des variables de substitution dans un fichier de commandes ou dans uneinstruction SQL unique. Une variable peut tre considr comme un conteneur dans lequelles valeurs sont stockes temporairement. Lorsque l'instruction est excute, la valeurstocke est substitu.





Utilisez des variables de substitution pour: Stocker temporairement des valeurs avec un seul esperluette(&) et double-esperluette (&&) la substitution

Utiliser les variables de substitution pour complter ce quisuit:

conditions WHERE Clauses ORDER BY

expressions de colonne les noms de table Ensemble des instructions SELECT

Variables de substitution (suite)Vous pouvez utiliser des variables de substitution mono-esperluette (&) pour stockertemporairement des valeurs.Vous pouvez galement prdfinir des variables l'aide de la commande DEFINE.DFINIR cre et assigne une valeur une variable.Gammes restreint de donnes: Exemples

Rapports chiffres seulement pour le trimestre en cours ou plage de dates spcifie

Les rapports sur les donnes pertinentes uniquement pour l'utilisateur qui demande le rapportAffichage de personnel que dans un dpartement donnAutres effets interactifsLes effets interactifs ne sont pas limits l'interaction directe de l'utilisateur avec la clauseWHERE. Les mmes principes peuvent galement tre utiliss pour atteindre d'autresobjectifs, tels que:

Obtention des valeurs d'entre partir d'un fichier plutt que d'une personnePassage de valeurs partir d'une instruction SQL l'autre

Remarque: Les deux SQL Developer et SQL * Plus de soutien des variables desubstitution et les commandes DEFINE / undefine. Bien que SQL Developer ou SQL * Plus

ne prend pas en charge les contrles de validation (sauf pour le type de donnes) surl'entre utilisateur.




SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = &employee_num ;

Utilisation de la variable de substitution mono-Ampersand

Utilisez une variable prfixe par une esperluette (&) pourinviter l'utilisateur une valeur:


Lorsque vous excutez un rapport, les utilisateurs veulent souvent de limiter les donnesrenvoyes dynamiquement. SQL * Plus ou SQL Developer offre cette flexibilit avec des variablesutilisateur. Utilisez une esperluette (&) pour identifier chaque variable dans l'instruction SQL.Cependant, vous n'avez pas besoin de dfinir la valeur de chaque variable.

L'exemple de la diapositive cre une variable SQL Developer substitution d'un numro d'employ.Lorsque l'instruction est excute, SQL Developer invite l'utilisateur un numro d'employ etaffiche ensuite le numro d'employ, nom de famille, le salaire et le numro de dpartement pourcet employ.

Avec l'esperluette unique, l'utilisateur est invit chaque fois que la commande est excute si lavariable n'existe pas.

Notation Description&user_variable Indique une variable dans une instruction SQL, si la

variable n'existe pas, SQL * Plus ou SQL Developerinvite l'utilisateur une valeur (la nouvelle variable estjet aprs usage.)





Utilisation de la variable de substitution mono-esperluette (suite)

Lorsque SQL Developer dtecte que l'instruction SQL contient une esperluette, vous tes invit entrer une valeur pour la variable de substitution qui est nomm dans l'instruction SQL.

Une fois que vous entrez une valeur, puis cliquez sur le bouton OK, les rsultats sont affichs dansl'onglet Rsultats de votre session SQL Developer.




SELECT last_name, department_id, salary*12

FROM employees

WHERE job_id = '&job_title' ;

Les valeurs de caractre et date avec lesvariables de substitution

Utilisez des guillemets simples pour la date et les valeurs descaractres:

Les valeurs de caractre et date avec des variables de substitution

Dans une clause WHERE, les valeurs de date et de caractre doit tre plac entreguillemets simples. La mme rgle s'applique aux variables de substitution.

Entourez la variable entre guillemets simples dans l'instruction SQL elle-mme.

Cette diapositive montre une requte pour rcuprer les noms des employs, des numrosde dpartement et les salaires annuels de tous les employs sur la base de la valeur du

titre d'emploi de la variable de substitution SQL Developer.




Spcification de noms de colonnes,? Expressionset de texte

SELECT employee_id, last_name, job_id,&column_name

FROM employees

WHERE &condition

ORDER BY &order_column ;

Spcification des noms de colonnes, expressions et de texte

Vous pouvez utiliser les variables de substitution non seulement dans la clause WHERE d'uneinstruction SQL, mais aussi que la substitution de noms de colonnes, expressions, ou du texte.

exemple:

L'exemple diaporama affiche le numro d'employ, nom, titre du poste, et une autre colonne quiest spcifi par l'utilisateur au moment de l'excution, de la table Employees. Pour chaque variable

de substitution dans l'instruction SELECT, vous tes invit entrer une valeur, puis cliquez sur OKpour continuer.

Si vous n'entrez pas une valeur pour la variable de substitution, vous obtenez une erreur lorsquevous excutez l'instruction prcdente.

Remarque: Une variable de substitution peut tre utilis partout dans l'instruction SELECT, saufque le premier mot est entr l'invite de commande.




SELECT employee_id, last_name, job_id, &&column_name

FROM employees

ORDER BY &column_name ;

Utilisation de la variable double substitution

Utilisez esperluette double (&&) si vous souhaitez rutiliser lavaleur de la variable sans inviter l'utilisateur chaque fois:

Utilisation de la variable de substitution Double-Ampersand

Vous pouvez utiliser le double esperluette (&&) variable de substitution si vous souhaitez rutiliserla valeur de la variable sans inviter l'utilisateur chaque fois. L'utilisateur voit le message de lavaleur qu'une seule fois. Dans l'exemple de la diapositive, l'utilisateur est invit donner la valeurde la variable, nom_colonne, une seule fois. La valeur qui est fournie par l'utilisateur(departement_id) est utilis pour l'affichage et la commande de donnes. Si vous excutez larequte nouveau, vous ne serez pas invit entrer la valeur de la variable.

SQL Developer stocke la valeur qui est fournie l'aide de la commande DEFINE, il l'utilise chaquefois que vous rfrencez le nom de la variable. Aprs une variable utilisateur est en place, vousdevez utiliser la commande undefine de le supprimer:

UNDEFINE column_name




Lesson Agenda





Utilisation de la commande DEFINE

Utilisez la commande DEFINE pour crer et affecter unevaleur une variable. Utilisez la commande UNDEFINE pour supprimer une

variable.DEFINE employee_num = 200

SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = &employee_num ;

UNDEFINE employee_num

Utilisation de la commande DEFINE

L'exemple ci cre une variable de substitution pour un numro d'employ l'aide de la commandeDEFINE. Au moment de l'excution, affiche le numro d'employ, le nom, le salaire et le numrode dpartement pour cet employ.

Parce que la variable est cre en utilisant l'SQL Developer commande DEFINE, l'utilisateur n'estpas invit entrer une valeur pour le numro d'employ. Au lieu de cela, la valeur de la variabledfinie est automatiquement remplac dans l'instruction SELECT.

La variable de substitution EMPLOYEE_NUM est prsent dans la session jusqu' ce que l'utilisateurannule la dfinition ou il quitte la session SQL Developer.




SET VERIFY ON

SELECT employee_id, last_name, salary

FROM employees

WHERE employee_id = &employee_num;

Utilisation de la commande VERIFY

Utilisez la commande VERIFY pour basculer l'affichage de lavariable de substitution, la fois avant et aprs SQL Developerremplace les variables de substitution avec les valeurs:

Utilisation de la commande VERIFY

Pour confirmer les changements dans l'instruction SQL, utilisez la commande VERIFY. Rglage SETVERIFY ON forces SQL Developer pour afficher le texte d'une commande aprs qu'elle remplace lesvariables de substitution avec des valeurs. Pour voir la sortie VERIFY, vous devez utiliser Excuter lescript (F5) icne dans la feuille de calcul SQL. SQL Developer affiche le texte d'une commandeaprs qu'elle remplace les variables de substitution avec les valeurs, dans l'onglet Sortie de scriptcomme indiqu dans la diapositive.

L'exemple de la diapositive affiche la nouvelle valeur de la colonne employe_id dans l'instructionSQL suivie par la sortie.

SQL plus Variables systme

SQL * Plus utilise des variables de divers systmes qui contrlent l'environnement de travail. L'unedes variables est VERIFY. Pour obtenir une liste complte de toutes les variables du systme, vouspouvez excuter la commande show all sur la commande SQL * Plus rapide.




Dans cette leon, vous devriez avoir appris : Utilisez la clause WHERE pour limiter les lignes deproduction:

Utiliser les conditions de comparaison Utilisez le BETWEEN, IN, LIKE, NULL et les oprateurs Appliquer les oprateurs logiques AND, OR et NOT Utiliser la clause ORDER BY pour trier les lignes de

production:

Utiliser la substitution et commercial de restreindre et trier lasortie au? Temps d'excution

SELECT *|{[DISTINCT] column|expression [alias],...}FROM table

[WHERE condition(s)]

[ORDER BY {column, expr, alias} [ASC|DESC]] ;

Summary

rsum

Dans cette leon, vous devriez avoir appris propos de la restriction et tri des lignes quisont retournes par l'instruction SELECT. Vous devez galement avoir appris mettre enuvre les diffrents oprateurs et les conditions.

En utilisant les variables de substitution, vous pouvez ajouter de la souplesse vosinstructions SQL. Cela permet aux requtes pour demander l'tat du filtre pour les lignes

lors de l'excution.




Pratique 2

Cette pratique couvre les sujets suivants: La slection de donnes et de modifier l'ordre des rangs?Qui sont affichs

Restriction de lignes l'aide de la clause WHERE Tri des lignes avec la clause ORDER BY Utilisation des variables de substitution pour ajouter de la

flexibilit votre? SQL SELECT

Pratique 2: Vue d'ensemble

Dans cette pratique, vous construisez plus de rapports, y compris les tats qui utilisent laclause WHERE et la clause ORDER BY. Vous faites les instructions SQL plus rutilisableset gnriques en incluant la substitution esperluette.



Pratique 2

Le dpartement RH a besoin de votre aide pour crer des requtes.

1. En raison de problmes budgtaires, le dpartement RH a besoin d'un rapport qui affiche lenom et le salaire des employs qui gagnent plus de $ 12,000. Enregistrer votre instruction SQLdans un fichier nomm lab_02_01.sql. Excutez votre requte.

2. Ouvrez une nouvelle feuille de calcul SQL. Crer un rapport qui affiche le nom et le numro dedpartement pour d'employ numro 176. Excutez la requte.

3. Le dpartement RH a besoin de trouver des employs haut salaire et faible salaire. Modifierlab_02_01.sql pour afficher le nom de famille et le salaire de tout employ dont le salaire n'est pasde l'ordre de $ 5.000 $ 12.000. Sauvegardez votre instruction SQL comme lab_02_03.sql.



Practice 2 (suite)

4. Crer un rapport pour afficher le dernier nom, ID du travail, et la date de dbut pour lesemploys avec les noms de famille de Matos et Taylor. Commandez la requte par ordrecroissant de la date de dbut.

5. Afficher le nom et le numro de dpartement de tous les employs dans les ministres20 ou 50 dans l'ordre alphabtique par nom.?

6. Modifier lab_02_03.sql pour afficher le nom et le salaire des employs qui gagnent entre$ 5.000 et $ 12.000, et sont dans le dpartement 20 ou 50. Marquez l'employ colonnes etSalaire mensuel, respectivement. Renregistrer lab_02_03.sql que lab_02_06.sql.Excutez l'instruction dans lab_02_06.sql.



Pratique 2 (suite)

7. Le dpartement RH a besoin d'un rapport qui affiche le nom et la date d'embauche pourtous les employs qui ont t embauchs en 1994.

8. Crer un rapport pour afficher le nom et le titre du poste de tous les employs qui n'ontpas un gestionnaire.

9. Crer un rapport pour afficher le nom, le salaire et la commission de tous les employsqui gagnent des commissions. Trier des donnes dans l'ordre dcroissant du salaire et

des commissions.? Utilisez la position numrique de la colonne dans la clause ORDERBY.

10. Les membres du dpartement des ressources humaines voulez avoir plus de flexibilitavec les requtes que vous crivez. Ils aimeraient un rapport qui affiche le nom et lesalaire des employs qui gagnent plus qu'un montant spcifi par l'utilisateur aprsl'invite. Enregistrez cette requte dans un fichier nomm lab_02_10.sql. Si vous entrez l'invite 12000, le rapport affiche les rsultats suivants:



Pratique 2 (suite)

11. Le dpartement des ressources humaines veut excuter des rapports bass sur ungestionnaire. Crez une requte qui demande l'utilisateur un ID manager et gnrel'ID d'employ, nom de famille, le salaire et service pour les employs que gestionnaire.Le dpartement des ressources humaines veut la possibilit de trier le rapport sur unecolonne slectionne. Vous pouvez tester les donnes avec les valeurs suivantes:

Manager_id = 103, tries par last_name:

Manager_id = 201, tries par salaire:

Manager_id = 124, tries par employe_id:



Pratique 2 (suite)

Si vous avez le temps, faites les exercices suivants:

12. Afficher tous les noms des employs dernires dans lequel la troisime lettre du nomest a.

13. Afficher les noms de famille de tous les employs qui ont la fois un a et e dansleur nom de famille.

Si vous voulez un dfi supplmentaire, faites les exercices suivants:

14. Afficher le nom de famille, le travail, et le salaire de tous les employs dont les emploissont soit ceux d'un reprsentant des ventes ou d'un commis aux stocks, et dont lessalaires ne sont pas gaux 2.500 $, 3.500 $, ou 7,000 $.

15. Modify lab_02_06.sql to display the last name, salary, and commission for allemployees whose commission is 20%. Resave lab_02_06.sql as lab_02_15.sql.Rerun the statement in lab_02_15.sql.


78/461


79/461


En utilisant une seule range de fonctionsSortie Personnaliser




Objectif

Aprs avoir termin cette leon, vous devriez tre en mesurede faire ce qui suit: Dcrire les divers types de fonctions disponibles dans SQL Utilisez caractre, le nombre et les fonctions de date dans

une instruction SELECT

objectifs

Fonctions rendre le bloc de requte de base plus puissant, et ils sont utiliss pour manipuler lesvaleurs des donnes. Il s'agit du premier de deux leons qui explorent des fonctions. Il seconcentre sur une seule range de caractres, le nombre et les fonctions de date.




Lesson Agenda

Fonctions SQL une ligne fonctions de caractres fonctions numriques Travailler avec les dates Les fonctions de date




SQL Functions

Function

Input

arg 1

arg 2

arg n

Fonction de l'action

Output

Resultvalue

Fonctions SQL

Les fonctions sont une fonctionnalit trs puissante de SQL. Ils peuvent tre utiliss pour effectuerles oprations suivantes:

Effectuer des calculs sur des donnes

Modifier des donnes individuelles

Manipuler sortie pour des groupes de lignes

Format des dates et des nombres pour l'affichageAutre types de donnes des colonnes

Les fonctions SQL prennent parfois des arguments et renvoient toujours une valeur.

Remarque: Si vous voulez savoir si une fonction est une instruction SQL: 2003 conformes fonction,reportez-vous la conformit de base Oracle Pour SQL: 2003 section dans Oracle Database 11gSQL Langage de rfrence, Release 1 (11.1).




Deux types de fonctions SQL

Fonctions une ligne

FunctionsLigne multiple

Retourner un rsultatpar ligne

Retourner un rsultatpar ensemble de lignes

Functions

Deux types de fonctions SQLIl existe deux types de fonctions:

Fonctions une ligneFonctions Plusieurs ligne

Fonctions une ligneCes fonctions oprent sur des lignes simples seulement et retourner un rsultat par ligne. Il existediffrents types de fonctions une range. Cette leon couvre les suivantes:

CharacterNumberDateConversionGeneral

Fonctions Plusieurs ligneLes fonctions peuvent manipuler les groupes de lignes pour donner un rsultat par groupe delignes. Ces fonctions sont galement connus comme les fonctions de groupe (couvert dans la leon5 intitul la communication des donnes agrges l'aide des fonctions de groupe").Remarque: Pour plus d'informations et une liste complte des fonctions disponibles et leur

syntaxe, consultez la rubrique Fonctions d'Oracle Database 11g SQL Langage de rfrence, Release1 (11.1).




fonctions une ligne

fonctions une ligne: Manipuler des lments de donnes Accepter les arguments et retourne une valeur Loi sur chaque ligne renvoye Retourner un rsultat par ligne Peut modifier le type de donnes Peuvent tre imbriques Accepter les arguments qui peuvent tre une colonne ou

une expression

function_name [(arg1, arg2,...)]

Single-Row Fonctions une range de fonctions sont utilises pour manipuler les lments de donnes. Ils acceptent unou plusieurs arguments et retourne une valeur pour chaque ligne renvoye par la requte. Unargument peut tre l'un des suivants:

Fourni par l'utilisateur constante valeur de la variable nom de la colonne expression

Caractristiques de simple-range fonctions comprennent: Agissant sur chaque ligne qui est retourne dans la requte Revenant un rsultat par ligne ventuellement renvoyer une valeur de donnes d'un type diffrent de celui qui est

rfrenc Peut-tre attend un ou plusieurs arguments Peut tre utilis dans SELECT, WHERE et ORDER BY; peuvent tre imbriques

Dans la syntaxe:

nom_fonction est le nom de la fonction

arg1, arg2 est un argument pour tre utilis par la fonction. Cela peut tre reprsent par un nomde colonne ou expression.




fonctions une ligne

Conversion

Character

Number

Date

GeneralSingle-rowfunctions

Single-Row Fonctions (suite)Cette leon porte sur les lments suivants une range de fonctions:Fonctions de caractres: Accepter la saisie de caractres et peut retourner des caractreset des valeurs numriquesFonctions numriques: Accepter l'entre numrique et renvoient des valeurs numriquesLes fonctions de date: Agir sur les valeurs du type de donnes DATE (Toutes lesfonctions de date renvoyer une valeur de type de donnes DATE exception de la fonction

MONTHS_BETWEEN, qui renvoie un nombre.)Les suivants une range de fonctions sont discutes dans la prochaine leon intituleUtilisation des fonctions de conversion et des expressions conditionnelles:Les fonctions de conversion: Convertir une valeur d'un type de donnes un autreFonctions gnrales:

- NVL- NVL2- NULLIF- COALESCE- CASE

- DECODE




Lesson Agenda

Fonctions SQL simple ligne fonctions de caractres fonctions numriques Travailler avec les dates Les fonctions de date



Function Purpose

LOWER(column|expression) Converts alpha character values to lowercaseUPPER(column|expression) Converts alpha character values to uppercase

INITCAP(column|expression) Converts alpha character values to uppercase for the firstletter of each word; all other letters in lowercase

CONCAT(column1|expression1,column2|expression2)

Concatenates the first character value to the secondcharacter value; equivalent to concatenation operator (||)

SUBSTR(column|expression,m[,n])

Returns specified characters from character value starting atcharacter position m, n characters long (Ifm is negative, thecount starts from the end of the character value. Ifn is

omitted, all characters to the end of the string are returned.)


fonctions de caractres

fonctions de caractres

LOWER

UPPER

INITCAP

CONCAT

SUBSTR

LENGTH

INSTR

LPAD | RPAD

TRIM

REPLACE

Fonctions de conversionde cas

Caractre de manipulationfonctions

Fonctions de caractres

Fonctions de caractres une range accepter des donnes de caractres en entre et peutretourner des caractres et des valeurs numriques. Fonctions de caractres peut tre diviscomme suit:

Case fonctions de conversion

Caractre fonctions de manipulation

Remarque: Les fonctions dcrites dans cette leon ne sont que quelques-unes des fonctionsdisponibles.



Fonctions de caractres(suite)

Remarque: Certaines des fonctions qui sont entirement ou partiellement SQL: 2003 sontconformes :

UPPERLOWERTRIM

LENGTHSUBSTRINSTR

Function Purpose

LENGTH(column|expression) Returns the number of characters in the expression

INSTR(column|expression,string, [,m], [n] )

Returns the numeric position of a named string.

Optionally, you can provide a position m to startsearching, and the occurrence n of the string. m and ndefault to 1, meaning start the search at the beginningof the string and report the first occurrence.

LPAD(column|expression, n,'string')

RPAD(column|expression, n,'string')

Returns an expression left-padded to length ofncharacters with a character expression.Returns an expression right-padded to length ofncharacters with a character expression.

TRIM(leading|trailing|both,trim_character FROM

trim_source)

Enables you to trim leading or trailing characters (or

both) from a character string. Iftrim_characterortrim_source is a character literal, you must enclose it insingle quotation marks.This is a feature that is available in Oracle8i and laterversions.

REPLACE(text,search_string,

replacement_string)

Searches a text expression for a character string and, iffound, replaces it with a specified replacement string




Fonctions de conversion de cas

Ces fonctions convertissent le cas des chanes de caractres:

sql courseLOWER('SQL Course')

Sql CourseINITCAP('SQL Course')

SQL COURSEUPPER('SQL Course')

ResultFunction

fonctions de conversion de casLOWER, UPPER, and INITCAP sont les trois cas de conversion fonctions.

LOWER: Convertit une chane de caractres majuscules et des minuscules ou desmajuscules en minuscules

UPPER: Convertit une chane de caractres majuscules et des minuscules en majusculesou en minuscules

INITCAP: Convertit la premire lettre de chaque mot en majuscule et les autres lettres

en minusculesSELECT 'The job id for '||UPPER(last_name)||' is '||LOWER(job_id) AS "EMPLOYEE DETAILS"FROM employees;




SELECT employee_id, last_name, department_id

FROM employeesWHERE LOWER(last_name) = 'higgins';

Using Case-Conversion Functions

Afficher le numro d'employ, le nom et le numro dedpartement pour l'employ higgins:

SELECT employee_id, last_name, department_id

FROM employees

WHERE last_name = 'higgins';

Utilisation de cas de conversion des fonctions

L'exemple diaporama affiche le numro d'employ, le nom et le numro de dpartement del'employ Higgins.

La clause WHERE de la premire instruction SQL spcifie le nom de l'employ comme higgins.Parce que toutes les donnes dans la table EMPLOYEES est stock dans les cas appropris, le nomhiggins ne trouve pas de correspondance dans la table, et aucune ligne n'est slectionne.

La clause WHERE de la deuxime instruction SQL spcifie que le nom employ dans la tableEMPLOYES est compare higgins, la conversion de la colonne LAST_NAME en minuscules des finsde comparaison. Parce que les deux noms sont maintenant en minuscules, une correspondance esttrouve et une ligne est slectionne. La clause WHERE peut tre rcrite de la faon suivantepour produire le mme rsultat:

... WHERE nom = 'Higgins

Le nom dans le rsultat apparat tel qu'il a t stock dans la base de donnes. Pour afficher lenom en majuscules, utilisez la fonction UPPER dans l'instruction SELECT.

SELECT employee_id, UPPER(last_name), department_id

FROM employeesWHERE INITCAP(last_name) = 'Higgins';




fonctions de manipulation de Caractre

Ces fonctions de manipuler des chanes de caractres:

BLACK and BLUEREPLACE('JACK and JUE','J','BL')

10LENGTH('HelloWorld')

6INSTR('HelloWorld', 'W')

*****24000LPAD(salary,10,'*')

24000*****RPAD(salary, 10, '*')

HelloWorldCONCAT('Hello', 'World')

elloWorldTRIM('H' FROM 'HelloWorld')

HelloSUBSTR('HelloWorld',1,5)

ResultFunction

fonctions de manipulation de CaractreCONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD etTRIM sont les fonctions de manipulationde caractre qui sont couverts dans cette leon.

CONCAT: Relie ensemble des valeurs (Vous tes limit l'aide de deux paramtres avecCONCAT.)

SUBSTR: Extrait une chane de longueur dtermine

LONGUEUR: Affiche la longueur d'une chane comme une valeur numriqueINSTR: Trouve la position numrique d'un personnage nomm

LPAD: Renvoie une expression de gauche rembourr la longueur de n caractres avec uneexpression de caractres

RPAD: Renvoie une expression complte droite la longueur de n caractres avec uneexpression de caractres

TRIM: Trims attaque ou de fuite caractres (ou les deux) d'une chane de caractres (Sitrim_character ou trim_source est un caractre littral, vous devez le placer entre guillemetssimples.)

Remarque: Vous pouvez utiliser des fonctions telles que UPPER et LOWER avec la substitutionesperluette. Par exemple, utilisez UPPER ('& job_title') de sorte que l'utilisateur n'a pas besoind'entrer le titre du poste dans un cas particulier.




SELECT employee_id, CONCAT(first_name, last_name) NAME,

job_id, LENGTH (last_name),

INSTR(last_name, 'a') "Contains 'a'?"

FROM employees

WHERE SUBSTR(job_id, 4) = 'REP';

Utilisation des fonctions de manipulation decaractres

2

31 2

1

3

Utilisation de fonctions de manipulation de Caractre

L'exemple diaporama affiche employs prnoms et noms de famille runis, la longueur du nom del'employ dernier, et la position numrique de la lettre a dans le nom de l'employ dernire pourtous les employs qui ont la chane, REP, contenue dans le travail ID partir de la quatrimeposition de l'ID du travail.

exemple:

Modifiez l'instruction SQL dans la diapositive pour afficher les donnes de ces employs dont le

nom se termine par la lettre n.SELECT employee_id, CONCAT(first_name, last_name) NAME,LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?"FROM employeesWHERE SUBSTR(last_name, -1, 1) = 'n';




Lesson Agenda

Fonctions SQL simple ligne fonctions de caractres fonctions numriques Travailler avec les dates Les fonctions de date




Fonctions nombre

ROUND: Rondes valeur une dcimale spcifie TRUNC: Tronque la valeur une dcimalespcifie

MOD: reste de la division de retour

100MOD(1600, 300)

45.93ROUND(45.926, 2)

45.92TRUNC(45.926, 2)

ResultFunction

Functions nombre

Fonctions numriques accepter une entre numrique et renvoient des valeurs numriques. Cettesection dcrit certaines des fonctions numriques.

Note: Cette liste ne contient que quelques-unes des fonctions disponibles nombre.

Pour plus d'informations, voir la section sur les fonctions numriques dans Oracle Database 11gSQL Langage de rfrence, Release 1 (11.1).

Function Purpose

ROUND(column|expression, n) Rounds the column, expression, or value to n decimalplaces or, ifn is omitted, no decimal places (Ifn is

negative, numbers to the left of decimal point are rounded.)TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal

places or, ifn is omitted, n defaults to zeroMOD(m,n) Returns the remainder ofm divided by n




SELECT ROUND(45.923,2), ROUND(45.923,0),

ROUND(45.923,-1)

FROM DUAL;

Utilisation de la fonction ROUND

DUAL est une table virtuelle que vous pouvez utiliser pour afficher lesrsultats des fonctions et des calculs.

3

31 2

1 2

Utilisation de la fonction ROUND

La fonction ROUND arrondit la colonne, d'expression ou de la valeur n dcimales. Si lesecond argument est 0 ou est absent, la valeur est arrondie zro dcimale. Si le secondargument est 2, la valeur est arrondie deux dcimales prs. A l'inverse, si le secondargument est de -2, la valeur est arrond

Documents

Oracle Pour Les Developpeurs I