Cours PL/SQL

Cours PL/SQL Manipulation de données


Télécharger Cours PL/SQL Manipulation de données

★★★★★★★★★★4 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


PL/SQL

                                                             Manipulation de données

 

Lancez Oracle Net Easy Configuration.

Tapez le libellé de l'alias de connexion.

Choisissez le protocole utilisé (Tcp/Ip dans la plupart des cas).

Entrez l'adresse Tcp/Ip ainsi que le numéro du port : 1521 par défaut.

Si vous possédez un serveur de nom, entrez le nom de domaine de la machine hébergeant Oracle.

Entrez le SID de l'instance représentant la base de données à laquelle vous souhaitez vous connecter. Ce SID dépend de l'administrateur réseau.

Cliquez sur le bouton Tester le service.

Pour ce faire, entrez vos paramètres de connexion :

-           system/manager pour l'administrateur -             scott/tiger

Cette information se trouve stockée dans le fichier ORANT\NET80\ADMIN\

Connexion Access

Sous Windows 2000 ou Xp, allez dans Démarrer|Panneau de configuration|Outils d'administration|Source de données (ODBC). Contrôlez au niveau de l'onglet Pilotes ODBC la présence du pilote Oracle ODBC Driver ou Microsoft ODBC pour Oracle.

Au niveau de l'onglet Source de données système

(pour tous les utilisateurs) ou au niveau de l'onglet Source de données utilisateur (pour l'utilisateur courant), cliquez sur le bouton Ajouter.

Créez une nouvelle base de données sous Access.


Choisissez les tables.

Schéma relationnel de la base démo

Environnement SQL Plus

SQLPLUS [-S[ILENT]] [[login[/password]][@alias_sqlnet] [@fichier_plsql[.sql]]

Ce sont des scripts qui s'exécutent automatiquement à la connexion de l'utilisateur.

GLOGIN se trouve dans ORANT\DBS alors que se situe dans ORANT\PLUS80 . Il s'exécute dans cet ordre.

Au niveau du client, les paramètres pour déterminer les informations de traçage sont à entrer dans la fichier ORANT\NET80\ADMIN\ .

A partir du bureau, par un clic droit, choisissez Nouveau | Raccourci.

Choisissez ou ou à partir du répertoire \ORANT\BIN\

Le raccourci pointe sur la commande choisie au niveau de l'étape précédente.

Donnez un nom à votre connexion.

Complétez le raccourci à l'aide des paramètres représentant le logon, l'alias de Connexion et le fichier contenant le script à exécuter.

SAV[E] ou

Sauvegarde le contenu du tampon dans un nouveau fichier à l'extension .SQL

 

SAV[E] nom_fichier CREATE 

 

SAVE nom_fichier REPLACE 

Sauvegarde le contenu du buffer dans un fichier existant à l'extension .SQL. Si le fichier n'existe pas, il est créé. 

SAVE nom_fichier APPEND 

Sauvegarde le contenu du buffer en l'ajoutant à un fichier existant à l'extension .SQL. Si le fichier n'existe pas, il est créé.

GET LIST|NOLIST

Charge le contenu du tampon par le contenu de . L'option LIST OU NOLIST permet ou non de visualiser le contenu du fichier

STORE {SET} nom_fichier[.ext]

[CRE[ATE]|REP[LACE]|APP[END]]

Sauvegarde tous les paramètres SET dans un fichier

Exemple : d'un fichier sauvegardé avec les paramètres par défaut set appinfo ON set appinfo "SQL*Plus" set arraysize 15 set autocommit OFF set autoprint OFF set autotrace OFF set shiftinout invisible set blockterminator "."

set cmdsep OFF set colsep " "

set compatibility NATIVE set concat "." set copycommit 0 set copytypecheck ON set define "&" set echo OFF

set editfile "" set embedded OFF set escape OFF set feedback 6 set flagger OFF set flush ON set heading ON set headsep "|" set linesize 100 set long 80 set longchunksize 80 set newpage 1

set null "" set numformat "" set numwidth 9 set pagesize 24 set pause OFF set recsep WRAP set recsepchar " " set serveroutput OFF set showmode OFF set sqlcase MIXED set sqlcontinue "> " set sqlnumber ON set sqlprefix "#" set sqlprompt "SQL> " set sqlterminator ";" set suffix "sql" set tab ON set termout ON set time OFF set timing OFF set trimout ON

 

set trimspool OFF set underline "-" set verify ON set wrap ON

SPOOL nom_fichier

Envoi des résultats d'une requête dans un fichier à l'extension .LST, sil elle n'est pas spécifiée 

SPOOL OFF

Met fin à l'envoi de écho de la requête dans un fichier spécifié avec Spool File

SPOOL OUT

Ferme le fichier de spool et envoie à l'imprimante

R[UN] OU /

Exécute l'ordre SQL ou le bloc PL/SQL enregistré dans le tampon

EXEC[UTE] ou ;

Exécute une instruction PL/SQL

EXIT | QUIT

[SUCCESS|FAILURE|WARNING|n|variable| :BindVariable] [COMMIT|ROLLBACK]

Réalise un COMMIT par défaut sur toutes les opérations en cours et permet de sortir de l'environnement SQL Plus

START|@ [Arg1,Arg2, ]

Excécute le contenu de en passant les arguments récupérables Sous la forme &1 pour Arg1, &2 pour Arg2, etc

REM[ARK]

Commentaire

TIMI[NG] [START texte|SHOW|STOP]

Enregistre les données 

CONN[ECT]

utilisateur/mot_de_passe@service

Connexion au serveur défini par le service (au niveau de Easy Net Configuration)

DIS[CONNECT]

Déconnexion

PASSW[ORD] [utilisateur]

Changement du mot de passe

COPY {FROM

username[/password]@database_spec|

        TO

username[/password]@database_spec|

        FROM

username[/password]@database_spec

        TO

username[/password]@database_spec}

{APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column

)]

        USING query

Copie partielle ou totale d'une base de données

HO[ST] CommandeSystème

Appel d'une commande système Windows

WHENEVER OSERROR {EXIT



[SUCCESS|FAILURE|n|variable|:BindVariable]

        [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

Gestion événementielle des erreurs suit e à des appels à des commandes système

WHENEVER SQLERROR {EXIT

[SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]

        [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FOR[MAT]   Saisie d'une variable format] [DEF[AULT] default]

        [PROMPT text|NOPR[OMPT]] [HIDE]

PROMPT [text]                                                                   Affichage d'un texte

PAU[SE] [text]                                                                   Pause

&                                                                                       Le caractère & dans un ordre SQl sert à passer et saisir

un paramètre. avant l'exécution de la requête ou du bloc, l'interpréteur SQL Plus Demande à L'utilisateur de saisir la valeur du paramètre :

select * from demo.&table;

SHOW DEF                                                                         permet de visualiser le caractère préfixant les

paramètres

SET DEF[INE] &|autre_caractère ON|OFF                           Définit le caractère préfixant les paramètres

DEF Paramètre=Valeur                                                       Affecte une valeur au paramètre

DEF Paramètre Visualise la valeur du paramètre UNDEF Paramètre        Détruit le paramètre

CTRL+C

La combinaison de touche vous permet d'annuler une commande en cours.

CTRL+C

Après avoir sélectionné le texte avec la souris, permet de le charger dans le presse-papiers

CTRL+V

Récupère le contenu du presse-papiers

Shift+Suppr

Vide le contenu de l'écran

L[IST] [n|n m|n *|n LAST|*|* n|*

LAST|LAST]

Liste le contenu du tampon

A[PPEND] Commande

Ajoute la commande en fin de ligne

I[NSERT] Commande

Insère une ou plusieurs lignes après la ligne courante

DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST]

Supprimer la ligne

C[HANGE]/Chaîne1/Chaîne2

Remplace l'occurrence chaîne1 par le texte chaîne2

CLEAR SCREEN

Vide le contenu de l'écran

EDIT

Edite le contenu du tampon dans un fichier sauvegardé sous dans le répertoire à partir duquel vous avez lancé l'interpréteur SQL Plus. Si vous lancez SQL Plus à l'aide d'un raccourci, le fichier sera sauvegardé dans le répertoire spécifié au niveau de

"Démarrer en"

La commande SET EDITF[ILE] nom_fichier[.ext] permet de changer le nom et l'extension de ce fichier. Ce paramètre ne sera pas sauvegardé lors d'une prochaine session.

Le contenu du fichier est effacé dès lors que vous activez l'édition du tampon d'une nouvelle session.

COLUMN nom_champ HEADING entête_de_colonne

Spécifie au niveau de l'affichage un entête de colonne associé au nom du champ. L'entête de colonne peut contenir un retour à la ligne avec le caractère |.

SET UNDERLINE Caractère

Change le caractère sous les entêtes de colonne

SET UNDERLINE =

SET UNDERLINE '-'

 

COLUMN nom_champ FORMAT modèle

Formatage numérique :

COLUMN SAL FORMAT $99 990

Formatage en affichant le contenu au champ sur des blocs De 4 caractères par ligne :

COLUMN ENAME FORMAT A4

Formatage d'une colonne à l'identique d'une autre colonne

COLUMN COMM LIKE SAL HEADING Bonus

COLUMN nom_champ

Affiche les attributs du champ

COLUMN

Affiche tous les alias mémorisés

COLUMN nom_champ CLEAR

Efface les attributs attachés au nom de Champ

CLEAR COLUMNS

Efface tous les alias de nom de champ

COLUMN nom_champ OFF|ON

Active ou désactive l'entête de colonne

BREAK ON nom_champ|ROW [SKIP n|PAGE]

[ ON ]

Evite la répétition de la valeur sur le nom du champ OU sur chaque ligne

BREAK

Visualise les définitions liées à la commande BREAK

CLEAR BREAKS

Efface toutes les définitions

BREAK ON break_column|REPORT

COMPUTE function LABEL label_name OF column column column ON break_column|REPORT

Les fonctions utilisables sont :

SUM          Somme

MINIMUM Minimum

MAXIMUM Maximum

AVG          Moyenne

STD           Ecart-type

VARIANCE Variance

COUNT      Nombre d'occurrences

NUMBER Nombre de lignes

Exemples:

REM Ne visualise pas les labels au niveau des lignes

COMPUTE

COLUMN SSTOT NOPRINT

COMPUTE SUM OF SAL ON SSTOT

BREAK ON SSTOT SKIP 1

SELECT DEPTNO SSTOT, DEPTNO, ENAME, SAL 

FROM EMP

ORDER BY DEPTNO;

COLUMN SSTOT NOPRINT

COMPUTE SUM OF SAL ON SSTOT 

BREAK ON DUMMY

SELECT NULL DUMMY, DEPTNO, ENAME, SAL

FROM EMP

ORDER BY DEPTNO;

BREAK ON DEPTNO

COMPUTE AVG SUM OF SAL ON DEPTNO

CLEAR COMPUTES

Elimine toutes les informations associées à COMPUTE



Affichage du numéro de page

TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' FORMAT

999 SKIP 2

TTITLE|BTITLE Informations sur les titres du rapport

TTITLE |BTITLE ON|OFF            Active ou désactive les titres BTITLE OFF

TTITLE|BTITLE|REPHEADER|REPFOOTER [COL n] [SKIP n]

TTITLE permet de définir un en-tête

[TAB n] [[PAGE] LEFT|RIGHT|CENTER] "texte"|variable

BTITLE définit le pied de page

LEFT produit un alignement à gauche

CENTER produit un centrage

RIGHT aligne à droite

SKIP fournit les sauts de ligne

TAB spécifie le nombre de tabulateurs COL fixe le titre au nième caractère.

REM Affichage d'un champ dans un titre

COLUMN MGR NEW_VALUE MGRVAR NOPRINT

TTITLE LEFT 'Manager: ' MGRVAR SKIP 2

BREAK ON MGR SKIP PAGE

BTITLE OFF

SELECT MGR, ENAME, SAL, DEPTNO FROM EMP

ORDER BY MGR;

Affichage de la Date courante

SET TERMOUT OFF

BREAK ON TODAY

COLUMN TODAY NEW_VALUE _DATE

SELECT TO_CHAR(SYSDATE, 'fmMonth DD, YYYY')

TODAY

FROM DUAL;

CLEAR BREAKS

SET TERMOUT ON

 

TTITLE|BTITLE ON|OFF

Active ou désactive l'affichage du titre

 

TITLE|BTITLE

Utilisées Seules, les commandes permettent de visualiser les options.

 

SET NEWPAGE number_of_lines

Fixe le nombre de ligne en terme de page

 

SET PAGESIZE number_of_lines

Taille des pages en hauteur

 

SET LINESIZE number_of_characters

Taille des pages en largeur

 

SET TERMOUT OFF|ON

Active ou désactive l’écho écran

 

SET NEWPAGE 0 SET SPACE 0

SET LINESIZE 80

SET PAGESIZE 0 SET ECHO OFF

SET FEEDBACK OFF

SET HEADING OFF

SET COLSep=;

APPI[NFO]{ON|OFF|text

ARRAY[SIZE] {20|n

AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} AUTOP[RINT] {OFF|ON} 

AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] 

BLO[CKTERMINATOR] {.|c

CMDS[EP] {;|c|OFF|ON} 

COLSEP {_|text) 

COM[PATIBILITY] {V7|V8|NATIVE]  CON[CAT] {.|c|OFF|ON}

COPYC[OMMIT] {0|n

COPYTYPECHECK {OFF|ON}  DEF[INE] {'&'|c|OFF|ON}

ECHO {OFF|ON} 

EDITF[ILE] file_name[.ext]  EMB[EDDED] {OFF|ON}

ESC[APE] {\|c|OFF|ON} 

FEED[BACK] {6|n|OFF|ON} 

FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} 

FLU[SH] {OFF|ON

HEA[DING] {OFF|ON

HEADS[EP] {||c|OFF|ON}  LIN[ESIZE] [80|n

LOBOF[FSET] {n|1}

LONG {80|n

LONGC[HUNKSIZE] [80|n

NEWP[AGE] {1|n|NONE} 

NULL text 

NUMF[ORMAT] format 

NUM[WIDTH] {10|n

PAGES[IZE] {24|n

PAU[SE] {OFF|ON|text

RECSEP {WR[APPED]|EA[CH]|OFF} 

RECSEPCHAR {|c

SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]  SHOW[MODE] {OFF|ON}

SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]

SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]} SQLCO[NTINUE] {> |text

SQLN[UMBER] {OFF|ON}  SQLPRE[FIX] {#|c}

SQLP[ROMPT] {SQL>|text

SQLT[ERMINATOR] {;|c|OFF|ON}  SUF[FIX] {SQL|text

TAB {OFF|ON

TERM[OUT] {OFF|ON

TI[ME] {OFF|ON} 

TIMI[NG] {OFF|ON} 

TRIM[OUT] {OFF|ON

TRIMS[POOL] {ON|OFF

UND[ERLINE] {-|c|ON|OFF} 

VER[IFY] {OFF|ON}  WRA[P] {OFF|ON}

SHO[W] [option]    Option désigne une variable ou l'un des éléments suivant :

ALL  APPI[NFO] 

BTI[TLE] 

ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE

BODY|TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]

LABEL 

LNO 

PNO 

REL[EASE] 

REPF[OOTER] 

REPH[EADER] 

SPOO[L] 

SQLCODE 

TTI[TLE] 

USER

DESC[RIBE] [schema.]object[@database_link_name]

SQL

ALTER  

LOCK TABLE  

ANALYZE   NOAUDIT  

AUDIT  

RENAME  

COMMENT   REVOKE  

COMMIT   ROLLBACK  

CREATE  

SAVEPOINT  

DELETE  

SELECT  

DROP  

SET ROLE  

EXPLAIN  

SET TRANSACTION  

GRANT  

TRUNCATE  

INSERT  

UPDATE  

SELECT * FROM TAB

Liste toutes les tables de l'utilisateur.

DESC Nom_Table

Description des colonnes composant la table

SELECT * FROM USER_CONS_COLUMNS

Contraintes sur les tables : clés primaires et étrangères

SELECT * FROM USER_IND_COLUMNS

Index sur les tables

Char

Caractère à longueur fixe

Varchar

Caractère à longueur variable

Long

Champ mémo de 64 Ko

Il est impossible d'utiliser ce type de champ dans une clause Where

Number

Valeurs numériques entières ou réelles

Date

Date et heure

Raw|longraw

Valeurs binaires

Rowid

Numérotation automatique

NULL

||

+,-,**,*,/

=,!= ou <>, >,<,>=,<=

Not, or, and

Like                                         Attention : les expressions régulières sont sensibles à la casse.

Between valeur1 and valeur2 Permet de fournir un intervalle au niveau d'une clause Where

In         Spécifie un ensemble de valeurs au niveau de la clause Where Is null      Permet de tester la valeur nulle (différente à la valeur vide).

MINUS   A – B représente les données de A en excluant sa partie commune à B INTERSECT           A inter B représente les données communes à A et à B.

UNION                             A union B représente les données appartenant à A ou à B.

UNION ALL                      Avec ces opérateurs, vous pouvez agréger des colonnes du même type n'ayant pas le même nom et le même nombre de caractères.

SELECT DISTINCT expression [colonne]{,expression [colonne],…}|*

FROM table [alias]{, table [alias]}

[WHERE condition]



[GROUP BY colonne{, colonne,…}]

[HAVING condition]

[ORDER BY colonne [ASC|DESC]{,colonne [ASC|DESC],…}]

ABS(nombre)

CEIL(nombre)

COS(nombre)

COSH(nombre)

EXP(nombre)

FLOOR(nombre)

LN(nombre)

LOG(nombre,base)

MOD(nombre,diviseur)

POWER(nombre,puissance)

ROUND(nombre[,décimales])

SIGN(nombre) SIN(nombre)

SINH(nombre)

SQRT(nombre)

TAN(nombre)

TANH(nombre)

TRUNC(nombre[,décimales])

Count(colonne)

Nombre d'occurrences

Sum(colonne)

Somme

Avg(colonne)

Moyenne

Max(colonne)

Maximum

Min(colonne)

Minimum

Variance(colonne)

Variance

Stddev(colonne)

Ecart-type

CREATE VIEW AS nom_vue[(colonne[,colonne,…])]

AS SELECT …

COPY 

{FROM scott [/tiger][@DSFC]}

{TO scott [/tiger][@DSFC.WORLD]} {APPEND|CREATE|INSERT|REPLACE} table_destination[(colonne[,colonne]])]

USING requete_select

Exemples

select * from tab /

select * 

from demo.employee

/

select name,city,state  from demo.customer /

select name,city,state  from demo.customer  order by name desc /

select name,city,state  from demo.customer  order by state desc,city /

select distinct city 

from demo.customer /

select name,city,state  from demo.customer where state='TX'

/

select name,city,state  from demo.customer  where name like '%BIKE%' /

select name,city,state 

from demo.customer 

where state in ('NY','TX')

/

select last_name  from demo.employee  where last_name like '_O%' /

select last_name,salary from  demo.employee 

where salary between 1000 and 1500 /

select last_name , salary  from demo.employee 

where salary>=1250 and salary<1500 /

select last_name , salary  from demo.employee  where salary <1250 or salary >1500

select last_name , salary  from demo.employee  where salary in (800,1600,3000) /

select *  from demo.sales_order 

where order_date between '1/1/1991' and '31/12/1991' /

select *  from demo.sales_order 

where order_date >='01/02/1991' and order_date<'01/03/1991' /

select count(*) total 

from demo.product /

select AVG(salary) moyenne,STDDEV(salary) ecart 

from demo.employee

/

select distinct department_id DEPT,AVG(salary) MOYENNE from demo.employee  group by department_id /

select distinct department_id DEPT,avg(salary) moyenne from demo.employee  where department_id<15  group by department_id

/

select distinct department_id DEPT,avg(salary) MOYENNE from demo.employee group by department_id having avg(salary)<2000 /

select state etat,count(name) nb from demo.customer group by state

/

select distinct product_id PRODUIT,sum(quantity) QTITE,sum(actual_price*quantity) MONTANT from group by product_id

/

select distinct customer_id "code client",sum(total) ventes from demo.sales_order group by customer_id /

select distinct customer_id CLT,sum(total) TOTAL from demo.sales_order group by customer_id having sum(total)>=20000 order by sum(total) /

select name from demo.customer where customer_id in (

            select customer_id              from demo.sales_order

            where order_date like '%91'

)

/

select customer_id from demo.sales_order in (  select customer_id      where order_date like '%90'           group by customer_id

            having sum (total)>=2500

)

/

select last_name,first_name from demo.employee

where employee_id in (              select distinct manager_id              from demo.employee

)

/

select description from demo.product where product_id in (

            select product_id              from               group by product_id

             having sum(quantity)>200

)

/

select E.last_name,J.function from demo.employee E, J

/

select E.last_name Nom,J.function Fonction from demo.employee E, J where J.job_id=E.job_id order by J.function /

select V.last_name Salarié,C.name Client from demo.employee V,demo.customer C where V.employee_id=C.salesperson_id /

select R.regional_group site,L.name service from demo.location R,demo.department L where R.location_id=L.location_id

/

select A.regional_group site,B.name service,C.last_name nom from demo.location A,demo.department B,demo.employee C where (A.location_id=B.location_id) and (B.department_id=C.department_id) order by A.regional_group,B.name,B.last_name

/

select distinct A.description,D.name

from demo.product A, B,demo.sales_order C,demo.customer D where (A.product_id=B.product_id) and (B.order_id=C.order_id) and (C.customer_id=D.customer_id) /

select A.description Produit,sum(B.quantity) Qtité,sum(B.total) montant from demo.product A, B,demo.sales_order c where (A.product_id=B.product_id) and (B.order_id=C.order_id)

and C.order_date between'01/01/1991' and '31/12/1991' group by A.description having sum(b.total)>5000 order by sum(b.total) desc /

SELECT S.ENAME SALARIE,C.ENAME CHEF 

FROM S, C 

WHERE S.MGR=C.EMPNO

GROUP BY CHEF

ORDER BY CHEF ASC /

select I.product_id,I.actual_price *I.quantity montant,I.total,

I.total-(I.actual_price*I.quantity) from I /

select AVG(J.ship_date-J.order_date) moyenne

from demo.sales_order J /

select AVG(J.ship_date-J.order_date) moyenne,K.state from demo.sales_order j,demo.customer k

where J.customer_id=k.customer_id group by k.state /

SELECT * FROM WHERE SAL >= 1000 UNION SELECT * FROM WHERE HIREDATE >= '1/1/85';

SELECT * FROM WHERE HIREDATE <= '1/1/1985'MINUS SELECT * FROM WHERE SAL >=1000;

SELECT JOB FROM UNION ALL SELECT ENAME FROM ;



853