|
|
\documentclass[a4paper,11pt]{article}
|
|
|
\usepackage[utf8x]{inputenc}
|
|
|
\usepackage[T1]{fontenc}
|
|
|
\usepackage[french]{babel}
|
|
|
\usepackage[a4paper,hmargin=20mm,vmargin=30mm]{geometry}%\usepackage{fullpage}
|
|
|
\usepackage{lastpage}
|
|
|
\usepackage{tikz,pgflibraryarrows,pgffor,pgflibrarysnakes}
|
|
|
\usetikzlibrary{decorations.pathreplacing}
|
|
|
\usepackage{url}
|
|
|
\usepackage{comment}
|
|
|
\usepackage{amsmath}
|
|
|
\usepackage{amsthm}
|
|
|
|
|
|
\usepackage{eurosym}
|
|
|
|
|
|
|
|
|
|
|
|
\theoremstyle{definition}
|
|
|
\newtheorem{exemple}{Exemple}[section]
|
|
|
\newtheorem{exercice}{Exercice}
|
|
|
\newtheorem{remarque}{Remarque}[section]
|
|
|
\newtheorem{definition}{Définition}[section]
|
|
|
|
|
|
\usepackage{makeidx}
|
|
|
\usepackage[columnsep=9pt]{idxlayout}
|
|
|
|
|
|
\usepackage{fancyvrb}
|
|
|
|
|
|
%\makeindex
|
|
|
\selectlanguage{french}
|
|
|
|
|
|
\usepackage[font=small,labelfont=bf,justification=centering]{caption}
|
|
|
\captionsetup[table]{name=Tableau}
|
|
|
|
|
|
\usepackage{latexsym}
|
|
|
\usepackage{amsfonts}
|
|
|
\usepackage[normalem]{ulem}
|
|
|
\usepackage{array}
|
|
|
\usepackage{amssymb}
|
|
|
\usepackage{graphicx}
|
|
|
|
|
|
\usepackage{subfig}
|
|
|
\usepackage{wrapfig}
|
|
|
\usepackage{wasysym}
|
|
|
\usepackage{enumitem}
|
|
|
\usepackage{adjustbox}
|
|
|
\usepackage{longtable}
|
|
|
\usepackage{changepage}
|
|
|
\usepackage{setspace}
|
|
|
\usepackage{hhline}
|
|
|
\usepackage{multicol}
|
|
|
\usepackage{float}
|
|
|
\usepackage{multirow}
|
|
|
\usepackage{slashbox}
|
|
|
|
|
|
\usepackage{color, colortbl}
|
|
|
\definecolor{Gray}{gray}{0.9}
|
|
|
|
|
|
\usepackage{fancyvrb}
|
|
|
\usepackage{fancyhdr}% fancy header
|
|
|
\usepackage{varwidth}
|
|
|
\usepackage{alltt}
|
|
|
|
|
|
|
|
|
\fancypagestyle{monstyle}{
|
|
|
%\fancyhead{}
|
|
|
\renewcommand{\headrulewidth}{1pt}
|
|
|
%% %\renewcommand{\footrulewidth}{0.4pt}
|
|
|
|
|
|
|
|
|
\newcommand{\plsql}{PL/SQL}
|
|
|
|
|
|
% \fancyhead[LE]{\slshape \thepage/ \pageref{LastPage}}
|
|
|
%% \fancyhead[RO]{\slshape \thepage/ \pageref{LastPage}}
|
|
|
|
|
|
|
|
|
%\fancyhf{}
|
|
|
%\fancyhead[LE]{\slshape LE}
|
|
|
%\fancyhead[CE]{\slshape CE}
|
|
|
%\fancyhead[RE]{\slshape RE}
|
|
|
|
|
|
\fancyhead[LO]{\bfseries\rightmark}
|
|
|
%\fancyhead[CO]{\slshape APF}
|
|
|
\fancyhead[RO]{\bfseries\leftmark}
|
|
|
|
|
|
%% %\fancyfoot{}
|
|
|
% \fancyfoot[LE,RO]{}
|
|
|
\fancyfoot[CO,CE]{\slshape\thepage/\pageref{LastPage}}
|
|
|
%% %\fancyfoot[LO,RE]{\small\slshape \ddmmyyyydate version du \today}
|
|
|
|
|
|
}
|
|
|
|
|
|
% \pagestyle{fancy}
|
|
|
|
|
|
\pagestyle{monstyle}
|
|
|
|
|
|
\newcommand{\code}[1]{\texttt{#1}}
|
|
|
|
|
|
\usepackage{boxedminipage}
|
|
|
|
|
|
\newsavebox\svbx
|
|
|
\newif\ifcache
|
|
|
\long\def\cache#1{\ \newline
|
|
|
\setbox\svbx=\vbox{\leavevmode \newline \begin{spacing}{1.5}#1\end{spacing}}
|
|
|
\smallskip\par\noindent
|
|
|
\begin{boxedminipage}{\linewidth}
|
|
|
\ifcache
|
|
|
\leavevmode\hrule height 0pt\vskip \ht\svbx\hrule height 0pt
|
|
|
\else \unvbox\svbx
|
|
|
\fi
|
|
|
\end{boxedminipage}
|
|
|
\par\smallskip}
|
|
|
|
|
|
|
|
|
\cachefalse % version prof
|
|
|
%\cachetrue % version etudiant
|
|
|
|
|
|
\makeindex
|
|
|
|
|
|
\begin{document}
|
|
|
|
|
|
|
|
|
|
|
|
\begin{titlepage}
|
|
|
\begin{center}
|
|
|
|
|
|
\textsc{\Large IUT Informatique Aubière \hfill 2018 - 2019} \\[.5cm]
|
|
|
|
|
|
\hrule
|
|
|
|
|
|
\ \\[.5cm]
|
|
|
|
|
|
\vfill
|
|
|
|
|
|
\textsc{\LARGE Bases de données}
|
|
|
|
|
|
\vfill
|
|
|
|
|
|
\textsc{\LARGE \plsql{}}
|
|
|
|
|
|
\vfill
|
|
|
|
|
|
{\Large Pascale \textsc{Brigoulet}, Franck \textsc{Glaziou}, }
|
|
|
|
|
|
{\Large Pascal \textsc{Lafourcade} et Marie-Fran\c{c}oise
|
|
|
\textsc{Servajean}}
|
|
|
|
|
|
|
|
|
\vfill
|
|
|
|
|
|
|
|
|
\begin{tikzpicture}
|
|
|
|
|
|
%%% un triangle
|
|
|
|
|
|
%% horizonatale
|
|
|
\draw[blue,line width=1pt] (-1,0) -- (1,0);
|
|
|
\draw[blue,line width=1pt] (-2.5,-.875) -- (1.5,-.875);
|
|
|
\draw[blue,line width=1pt] (-1.5,-1.75) -- (3,-1.75);
|
|
|
\draw[blue,line width=1pt] (0,3.5) -- (1,3.5);
|
|
|
|
|
|
%% Croissante
|
|
|
\draw[blue,line width=1pt] (0,0) -- (1,1.75);
|
|
|
\draw[blue,line width=1pt] (-2.5,-.875) -- (0,3.5);
|
|
|
\draw[blue,line width=1pt] (-1,0) -- (1,3.5);
|
|
|
\draw[blue,line width=1pt] (3,-1.75) -- (3.5,-0.75);
|
|
|
|
|
|
%% Decroissante
|
|
|
\draw[blue,line width=1pt] (0.5,.875) -- (1.5,-.875);
|
|
|
\draw[blue,line width=1pt] (1,1.75) -- (3,-1.75);
|
|
|
\draw[blue,line width=1pt] (1,3.5) -- (3.5,-0.75);
|
|
|
\draw[blue,line width=1pt] (-2.5,-.875) -- (-1.5,-1.75);
|
|
|
|
|
|
|
|
|
|
|
|
\end{tikzpicture}
|
|
|
|
|
|
%% \begin{tikzpicture}
|
|
|
|
|
|
%% %%% un cube
|
|
|
|
|
|
%% \draw[red,line width=1pt] (0,.5) -- (1,0);
|
|
|
%% \draw[red,line width=1pt] (2,.5) -- (1,0);
|
|
|
|
|
|
%% \draw[red,line width=1pt] (0,1.5) -- (1,1);
|
|
|
%% %\draw[red,line width=1pt] (0,1.5) -- (1,2);
|
|
|
%% \draw[red,line width=1pt] (0,1.5) -- (.5,1.75);
|
|
|
%% %\draw[red,line width=1pt] (2,1.5) -- (1,2);
|
|
|
%% \draw[red,line width=1pt] (2,1.5) -- (1.5,1.75);
|
|
|
%% \draw[red,line width=1pt] (2,1.5) -- (1,1);
|
|
|
|
|
|
%% \draw[red,line width=1pt] (1,1) -- (1,0);
|
|
|
%% \draw[red,line width=1pt] (0,1.5) -- (0,.5);
|
|
|
%% \draw[red,line width=1pt] (2,1.5) -- (2,.5);
|
|
|
|
|
|
%% %% droite
|
|
|
|
|
|
%% %\draw[red,line width=1pt] (1.5,1.25) -- (2.5,0.75);
|
|
|
%% \draw[red,line width=1pt] (2,1) -- (2.5,0.75);
|
|
|
%% \draw[red,line width=1pt] (3.5,1.25) -- (2.5,0.75);
|
|
|
|
|
|
%% \draw[red,line width=1pt] (1.5,2.25) -- (2.5,1.75);
|
|
|
%% \draw[red,line width=1pt] (1.5,2.25) -- (2.5,2.75);
|
|
|
%% \draw[red,line width=1pt] (3.5,2.25) -- (2.5,2.75);
|
|
|
%% \draw[red,line width=1pt] (3.5,2.25) -- (2.5,1.75);
|
|
|
|
|
|
%% \draw[red,line width=1pt] (2.5,1.75) -- (2.5,.75);
|
|
|
%% %\draw[red,line width=1pt] (1.5,2.25) -- (1.5,1.25);
|
|
|
%% \draw[red,line width=1pt] (1.5,2.25) -- (1.5,1.75);
|
|
|
%% \draw[red,line width=1pt] (3.5,2.25) -- (3.5,1.25);
|
|
|
|
|
|
%% %% Haut
|
|
|
%% \draw[red,line width=1pt] (0,2) -- (1,1.5);
|
|
|
%% %\draw[red,line width=1pt] (2,2) -- (1,1.5);
|
|
|
%% \draw[red,line width=1pt] (1.5,1.75) -- (1,1.5);
|
|
|
|
|
|
%% \draw[red,line width=1pt] (0,3) -- (1,2.5);
|
|
|
%% \draw[red,line width=1pt] (0,3) -- (1,3.5);
|
|
|
%% \draw[red,line width=1pt] (2,3) -- (1,3.5);
|
|
|
%% \draw[red,line width=1pt] (2,3) -- (1,2.5);
|
|
|
|
|
|
%% \draw[red,line width=1pt] (1,2.5) -- (1,1.5);
|
|
|
%% \draw[red,line width=1pt] (0,3) -- (0,2);
|
|
|
%% %\draw[red,line width=1pt] (2,3) -- (2,2);
|
|
|
%% \draw[red,line width=1pt] (2,3) -- (2,2.5);
|
|
|
|
|
|
|
|
|
%% \end{tikzpicture}
|
|
|
|
|
|
\vfill
|
|
|
|
|
|
|
|
|
\includegraphics[width=5cm]{iut-uca.png}
|
|
|
\end{center}
|
|
|
|
|
|
\vfill
|
|
|
|
|
|
{\Large Nom : \\
|
|
|
|
|
|
Prénom : \\
|
|
|
|
|
|
Groupe : \\
|
|
|
}
|
|
|
%\url{http://mocodo.wingi.net/}
|
|
|
|
|
|
%\url{http://mirror.hmc.edu/ctan/graphics/pgf/contrib/tkz-orm/tkz-orm.pdf}
|
|
|
|
|
|
|
|
|
|
|
|
\end{titlepage}
|
|
|
|
|
|
%% \section*{Avant Propos}
|
|
|
|
|
|
%% L’objectif de ce cours de base de données avancées est de
|
|
|
%% présenter
|
|
|
|
|
|
TD 1 : → Exo3
|
|
|
TD 2 : Exo 3, 5, 6 7
|
|
|
TD 3 : Reste
|
|
|
TD 4 : Partiel
|
|
|
|
|
|
%% \newpage
|
|
|
|
|
|
\tableofcontents
|
|
|
|
|
|
\newpage
|
|
|
|
|
|
\begin{center}
|
|
|
\LARGE {\bf \plsql{}}
|
|
|
\end{center}
|
|
|
|
|
|
PL/SQL (Procedural Language / Structured Query Language) est un
|
|
|
langage fondé sur les paradigmes de programmation procédurale et
|
|
|
structurée. C'est un langage propriétaire, créé par Oracle et utilisé
|
|
|
dans le cadre de bases de données relationnelles. Il permet de
|
|
|
combiner des requêtes SQL et des instructions procédurales (boucles,
|
|
|
conditions...), dans le but de créer des traitements complexes
|
|
|
destinés à être stockés sur le serveur de base de données (objets
|
|
|
serveur), comme des procédures stockées ou des déclencheurs.
|
|
|
|
|
|
\section{Fichier de commandes SQL/PLUS}
|
|
|
|
|
|
SQL/Plus est un utilitaire en ligne de commande d'Oracle qui permet
|
|
|
aux utilisateurs d'exécuter interactivement des commandes SQL et
|
|
|
PL/SQL. Il est ainsi possible de paramètrer les fichiers de commandes
|
|
|
SQL. Pour cela il est important de stocker des informations dans des
|
|
|
variable : \code{variable vnoproduit CHAR(6)}. Cette commande déclare
|
|
|
une «bind variable» utilisable dans une commande SQL. Les diff\'eretns
|
|
|
formats autorisés sont : \code{NUMBER}, \code{CHAR(n)} et
|
|
|
\code{VARCHAR2(n)}. Afin d'interagir avec l'utilisateur il faut
|
|
|
pouvoir il existe deux commandes :
|
|
|
|
|
|
\code{PROMPT taper le N° du produit}
|
|
|
|
|
|
Cette commande affiche le texte placé après prompt (pas de quote)
|
|
|
|
|
|
\code{ACCEPT vnoproduit}
|
|
|
|
|
|
Cette commande permet la saisie d'une variable au clavier (la déclare
|
|
|
si elle ne l’était pas).
|
|
|
|
|
|
\code{SELECT * FROM tproduit WHERE N°produit = ‘\&vnoproduit’;}
|
|
|
|
|
|
\&{vnoproduit} permet d'acc\'eder \`a la chaîne de caractères
|
|
|
stock\'ee dans \code{vnoproduit}.
|
|
|
|
|
|
\code{PRINT vnoproduit}
|
|
|
|
|
|
Cette commande affiche la variable.
|
|
|
|
|
|
|
|
|
|
|
|
\section{Exécution du code PL/SQL}
|
|
|
|
|
|
|
|
|
En SQL, les commandes sont transmises les unes après les autres et
|
|
|
traitées séparément par le moteur SQL, comme le montre la
|
|
|
Figure~\ref{fig:plsql1}.
|
|
|
|
|
|
\begin{figure}[htb]
|
|
|
\begin{center}
|
|
|
\includegraphics[width=12cm]{plsql2.png}
|
|
|
\end{center}
|
|
|
\caption{}\label{fig:plsql1}
|
|
|
\end{figure}
|
|
|
|
|
|
|
|
|
En extension procédurale de SQL (\plsql{}), les blocs de commandes
|
|
|
sont transmis globalement au moteur SQL, comme le montre la
|
|
|
Figure~\ref{fig:plsql2}.
|
|
|
|
|
|
\begin{figure}[htb]
|
|
|
\begin{center}
|
|
|
\includegraphics[width=12cm]{plsql1.png}
|
|
|
\end{center}
|
|
|
\caption{Interaction entre PL/SQL et SQL.}\label{fig:plsql2}
|
|
|
\end{figure}
|
|
|
|
|
|
|
|
|
\section{Exemple de programme \plsql{}}
|
|
|
|
|
|
Le fichier de commandes ci-dessous permet la saisie de la référence
|
|
|
d’un produit et l’affichage de sa désignation ou d’un message
|
|
|
d’erreur si le N° de produit n’existe pas.
|
|
|
|
|
|
\begin{verbatim}
|
|
|
set echo off
|
|
|
set verify off
|
|
|
set feed off
|
|
|
|
|
|
variable vnoproduit char(4)
|
|
|
variable vdesignation varchar2(30)
|
|
|
prompt taper la référence du produit à rechercher :
|
|
|
accept vnoproduit
|
|
|
\end{verbatim}
|
|
|
\begin{SaveVerbatim}{VerbEnv}
|
|
|
declare
|
|
|
dnoproduit char(4);
|
|
|
|
|
|
begin
|
|
|
dnoproduit := ‘&vnoproduit’;
|
|
|
select designation into :vdesignation
|
|
|
from tproduit where noproduit = dnoproduit;
|
|
|
|
|
|
exception
|
|
|
when no_data_found then
|
|
|
:vdesignation := ‘référence inconnue’;
|
|
|
end;
|
|
|
\end{SaveVerbatim}
|
|
|
\fbox{\BUseVerbatim{VerbEnv}}
|
|
|
\begin{verbatim}
|
|
|
.
|
|
|
/
|
|
|
print vdesignation
|
|
|
|
|
|
set verify on
|
|
|
set feed on
|
|
|
set echo on
|
|
|
\end{verbatim}
|
|
|
|
|
|
\begin{remarque}
|
|
|
Ce fichier comporte trois parties :
|
|
|
\begin{itemize}
|
|
|
\item des commandes SQL+ pour la saisie de la référence produit,
|
|
|
\item un bloc \plsql{} pour accéder à la base,
|
|
|
\item des commandes SQL+ pour afficher le résultat.
|
|
|
\end{itemize}
|
|
|
\end{remarque}
|
|
|
|
|
|
\plsql{} ne comprend pas d’instruction de saisie ou d’affichage. Les
|
|
|
commandes \code{SET} positionnent des variables d’environnement pour
|
|
|
éviter que le système n’affiche des informations « polluant » le
|
|
|
résultat :
|
|
|
\begin{itemize}
|
|
|
\item \code{set echo off} évite que le système n’affiche la commande
|
|
|
du bloc PL/SQL au moment où il l’exécute,
|
|
|
\item \code{set verify off} évite que le système n’affiche l’opération
|
|
|
de substitution au moment où il la fait,
|
|
|
\item \code{set feed off} évite que le système n’affiche le nombre de
|
|
|
lignes sélectionnées,
|
|
|
\item \code{.} le point indique la fin du mode PL/SQL,
|
|
|
\item \code{/} (seul sur une ligne) déclenche l’exécution du bloc PL/SQL ou de l’ ordre SQL stocké dans le buffer.
|
|
|
\end{itemize}
|
|
|
|
|
|
\section{Structure d'un bloc \plsql{}}
|
|
|
|
|
|
\plsql{} n'interprète pas une commande, mais un ensemble de commandes
|
|
|
contenu dans un "bloc" \plsql{}. Un bloc est composé de trois
|
|
|
sections.
|
|
|
\begin{itemize}
|
|
|
\item Les sections \code{DECLARE} et \code{EXCEPTION} sont facultatives.
|
|
|
\item Chaque instruction, de n'importe quelle section, est terminée
|
|
|
par un \code{;}
|
|
|
\item Possibilité de placer des commentaires : \code{/* commentaire
|
|
|
sur plusieurs lignes commençant par au moins un espace */}
|
|
|
\end{itemize}
|
|
|
|
|
|
Attention aux points virgules: en cas d’oubli, Oracle affiche un N° de
|
|
|
ligne attendant la suite de commande SQL.
|
|
|
|
|
|
\begin{figure}[htb]
|
|
|
\begin{center}
|
|
|
\includegraphics[width=12cm]{bloc.png}
|
|
|
\end{center}
|
|
|
\caption{Structure d'un programme \plsql{}.}\label{fig:bloc}
|
|
|
\end{figure}
|
|
|
|
|
|
\section{Types de variables utilisés en \plsql{}}
|
|
|
|
|
|
Variables locales déclarées dans \code{DECLARE} et il est important de
|
|
|
mettre le type adapt\'e en suivant la syntaxe suivante :
|
|
|
|
|
|
\verb+nom_variable type := valeur;+
|
|
|
|
|
|
Les diff\'erents types possibles sont : \code{CHAR, NUMBER, DATE,
|
|
|
VARCHAR2}.
|
|
|
|
|
|
Il est n\'ecessaire de choisir le type \`a l'initialisation lors de la
|
|
|
déclaration et aussi sa valeur intiale comme le montre les deux
|
|
|
exemples suivants :
|
|
|
\begin{itemize}
|
|
|
\item \code{NB NUMBER := 1;}
|
|
|
\item \verb+nom_variable nomtable.nomcol %type+
|
|
|
\end{itemize}
|
|
|
|
|
|
\begin{exemple} Cr\'eation de variable.
|
|
|
|
|
|
\code{dnoprod tproduit.noproduit \%type;}
|
|
|
|
|
|
\code{ddesignation tproduit.designation \%type ;}
|
|
|
\end{exemple}
|
|
|
|
|
|
Il est possible de cr\'eer des tableaux en \plsql{}. %
|
|
|
%% Variables de l'environnement extérieur à \plsql{}:
|
|
|
%% \begin{itemize}
|
|
|
%% \item champs d'écran en SQL*Forms,
|
|
|
%% \item variables définies en langage hôte dans PRO*. Les variables de
|
|
|
%% l’environnement sont toujours préfixées.
|
|
|
%\item
|
|
|
Les variables définies dans SQL*Plus par \code{VARIABLE} ou
|
|
|
\code{ACCEPT} sont préfixées de : pour affectation, de "\verb+&+" pour
|
|
|
substitution.
|
|
|
|
|
|
% \end{itemize}
|
|
|
|
|
|
\begin{exemple} Exemple simple d'utilisation de variable.
|
|
|
\begin{verbatim}
|
|
|
dnoproduit := '&vnoproduit';
|
|
|
select designation into :vdesignation
|
|
|
from tproduit
|
|
|
where noproduit = '&vnoproduit';
|
|
|
|
|
|
select designation into ddesignation
|
|
|
from tproduit
|
|
|
where noproduit = dnoproduit;
|
|
|
\end{verbatim}
|
|
|
|
|
|
Tableaux PL/SQL.
|
|
|
|
|
|
Déclaration :
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DECLARE
|
|
|
...
|
|
|
TYPE tchar4 IS TABLE OF CHAR(4)
|
|
|
INDEX BY BINARY_INTEGER ;
|
|
|
table_noprod tchar4 ;
|
|
|
p BINARY_INTEGER ;
|
|
|
\end{verbatim}
|
|
|
|
|
|
Utilisation :
|
|
|
|
|
|
\begin{verbatim}
|
|
|
BEGIN
|
|
|
...
|
|
|
p := 1 ;
|
|
|
table_noprod(p) := ‘p001’ ;
|
|
|
\end{verbatim}
|
|
|
\end{exemple}
|
|
|
|
|
|
\section{Les traitements du bloc \code{BEGIN}}
|
|
|
\subsection{L'ordre SELECT :}
|
|
|
\begin{itemize}
|
|
|
\item Syntaxe :
|
|
|
\begin{verbatim}
|
|
|
SELECT coll, col2
|
|
|
INTO var1, var2
|
|
|
FROM table
|
|
|
[WHERE condition];
|
|
|
\end{verbatim}
|
|
|
\item
|
|
|
Règle :
|
|
|
\begin{itemize}
|
|
|
\item La clause \code{INTO} est {\bf obligatoire}.
|
|
|
\item Le \code{SELECT} doit obligatoirement ramener une ligne
|
|
|
et une seule, sinon erreur. Pour traiter un ordre
|
|
|
\code{SELECT} qui pourrait ramener plusieurs lignes, il faut
|
|
|
utiliser un curseur.
|
|
|
\end{itemize}
|
|
|
\end{itemize}
|
|
|
Les autres ordres de manipulation sont inchangés; des
|
|
|
variables peuvent être utilisées :
|
|
|
\begin{verbatim}
|
|
|
INSERT INTO table VALUES( var1, ‘chaine’, 123, var2);
|
|
|
UPDATE table SET col2 = var1 WHERE col1 = var2;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\begin{exercice}
|
|
|
Nombre de fournisseurs d’un produit donné : le résultat sera écrit
|
|
|
dans une table \code{ligne}. Les tables utilis\'ees sont donn\'ees
|
|
|
dans la Figure~\ref{fig:bd}.
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE tligne ;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off ;
|
|
|
set verify off ;
|
|
|
set feed off ;
|
|
|
variable vnoproduit char(4)
|
|
|
prompt Entre le numero du produit:
|
|
|
accept vnoproduit
|
|
|
|
|
|
DECLARE
|
|
|
|
|
|
dnbfournisseur number ;
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
SELECT count(*) into dnbfournisseur
|
|
|
FROM Tproduitfourn
|
|
|
WHERE noprod ='& vnoproduit';
|
|
|
|
|
|
INSERT INTO tligne VALUES ('NB fournisseur='||to_char(dnbfournisseur)|
|
|
|
|' du produit '|| '&vnoproduit');
|
|
|
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne ;
|
|
|
|
|
|
set verify on ;
|
|
|
set feed on ;
|
|
|
set echo on ;
|
|
|
\end{verbatim}
|
|
|
|
|
|
|
|
|
|
|
|
\subsection{Traitements conditionnels \code{IF...THEN...END IF;}}
|
|
|
\begin{verbatim}
|
|
|
IF condition1 THEN traitement1;
|
|
|
ELSE traitement2;
|
|
|
END IF;
|
|
|
\end{verbatim}
|
|
|
|
|
|
Les opérateurs utilisés dans les conditions sont les même que dans SQL
|
|
|
:
|
|
|
|
|
|
\verb+ =, <, >, !=, >=, <=, IS NULL, IS NOT NULL, BETWEEN, LIKE, AND, OR, ...+
|
|
|
|
|
|
|
|
|
\begin{exercice}
|
|
|
Nombre de fournisseurs d’un produit donné : s’il n’y a pas de
|
|
|
fournisseur, compter le nombre de produits : il doit être 1 ou
|
|
|
0. \'Ecrire dans une table de lignes soit le nombre de fournisseurs
|
|
|
soit le message ‘\code{le produit n’existe pas}’.
|
|
|
\end{exercice}
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
variable vnoproduit char(4)
|
|
|
prompt Entre le numero du produit:
|
|
|
accept vnoproduit
|
|
|
|
|
|
DECLARE
|
|
|
|
|
|
dnbfournisseur number;
|
|
|
dnbproduit number;
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
SELECT count(*) into dnbfournisseur
|
|
|
FROM Tproduitfourn
|
|
|
WHERE noprod ='& vnoproduit';
|
|
|
|
|
|
IF dnbfournisseur = THEN
|
|
|
|
|
|
SELECT count(noproduit) into dnbproduit
|
|
|
FROM Tproduit
|
|
|
WHERE noproduit = & vnoproduit';
|
|
|
|
|
|
if dnbproduit = 0 then
|
|
|
INSERT INTO tligne VALUES ('Le produit ' || '&vnoproduit'|| ' n''existe pas');
|
|
|
else
|
|
|
INSERT INTO tligne VALUES ('NB fournisseur='||to_char(dnbfournisseur)|
|
|
|
|' du produit ' || '&vnoproduit');
|
|
|
end if;
|
|
|
END IF;
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne;
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\subsection{Traitements répétitifs \code{WHILE ...LOOP ...END LOOP;}}
|
|
|
|
|
|
La boucle \code{WHILE}: L'exécution de la boucle se fait tant
|
|
|
que la condition de la clause de \code{WHILE} est vérifiée.
|
|
|
\begin{verbatim}
|
|
|
BEGIN
|
|
|
WHILE condition
|
|
|
LOOP
|
|
|
instructions;
|
|
|
END LOOP;
|
|
|
END;
|
|
|
\end{verbatim}
|
|
|
La condition est une combinaison d'expressions au moyen d'opérateurs :
|
|
|
\verb+<, >, =, !=, AND, OR, LIKE, ...+
|
|
|
|
|
|
\begin{exemple}Calcul de factorielle.
|
|
|
\begin{verbatim}
|
|
|
CREATE TABLE tligne (ligne VARCHAR2(200));
|
|
|
VARIABLE n NUMBER
|
|
|
PROMPT taper n
|
|
|
ACCEPT n
|
|
|
|
|
|
DECLARE
|
|
|
fn NUMBER := 1;
|
|
|
i NUMBER :=1;
|
|
|
n NUMBER :='&n';
|
|
|
BEGIN
|
|
|
WHILE i<n
|
|
|
LOOP
|
|
|
i := i + 1;
|
|
|
fn := fn * i;
|
|
|
END LOOP;
|
|
|
|
|
|
INSERT INTO tligne
|
|
|
VALUES ( 'Factorielle de '|| TO_CHAR(n)||, ‘ ‘,TO_CHAR(fn));
|
|
|
END;
|
|
|
/
|
|
|
SELECT * FROM tligne;
|
|
|
DROP TABLE tligne;
|
|
|
\end{verbatim}
|
|
|
\end{exemple}
|
|
|
|
|
|
\section{Gestion des erreurs : \code{EXCEPTION ...WHEN ...THEN}}
|
|
|
\subsection{La section \code{EXCEPTION}}
|
|
|
|
|
|
La section \code{EXCEPTION} permet d'affecter un traitement approprié
|
|
|
aux erreurs survenues lors de l'exécution du bloc \plsql{}.
|
|
|
|
|
|
Les types d'erreurs sont les suivants :
|
|
|
\begin{itemize}
|
|
|
\item erreurs Oracle: elles sont prédéfinies
|
|
|
\item erreur programme utilisateur : à déclarer
|
|
|
\end{itemize}
|
|
|
|
|
|
Dans l'exemple ci-dessous, les résultats à afficher sont placés dans
|
|
|
une table \code{tligne}.
|
|
|
\begin{exemple}~
|
|
|
|
|
|
\begin{verbatim}
|
|
|
create table tligne ( ligne varchar2(150));
|
|
|
variable vnoproduit char(4)
|
|
|
prompt taper la référence du produit à rechercher:
|
|
|
accept vnoproduit
|
|
|
|
|
|
declare
|
|
|
dnoproduit char(4);
|
|
|
ddesignation varchar2(30);
|
|
|
dmessage varchar2(150);
|
|
|
|
|
|
begin
|
|
|
dnoproduit:= ‘&vnoproduit’;
|
|
|
|
|
|
dmessage := 'Référence inconnue ';
|
|
|
select designation intoddesignation From tproduit where noproduit = dnoproduit;
|
|
|
|
|
|
insert into tligne values ('Désignation: ' || ddesignation);
|
|
|
|
|
|
exception
|
|
|
when no_data_found then
|
|
|
insert into tligne values ( dmessage);
|
|
|
end;
|
|
|
.
|
|
|
/
|
|
|
select * from tligne;
|
|
|
drop table tligne;
|
|
|
\end{verbatim}
|
|
|
\end{exemple}
|
|
|
|
|
|
\begin{remarque} Le message d'erreur est initialisé juste avant la requête SQL qui risque de provoquer l'erreur; la méthode est à utiliser quand plusieurs requêtes SQL sont susceptibles de déclencher la même exception.
|
|
|
\end{remarque}
|
|
|
|
|
|
\begin{exercice}
|
|
|
Enregistrer une livraison pour un produit donné:
|
|
|
\begin{itemize}
|
|
|
\item saisie du numéro de produit et de la quantité livrée,
|
|
|
\item accès au stock du produit: exception ‘\code{référence inconnue}’,
|
|
|
\item calcul du nouveau stocket mise à jour du stock.
|
|
|
\end{itemize}
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
variable vnoproduit char(4)
|
|
|
variable vquantite number
|
|
|
|
|
|
prompt Entre le numero du produit:
|
|
|
accept vnoproduit
|
|
|
|
|
|
prompt Entre la quantite du produit:
|
|
|
accept vquantite
|
|
|
|
|
|
DECLARE
|
|
|
|
|
|
dstock number;
|
|
|
dnbproduit char(4);
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
SELECT noproduit, stock into dnoproduit, dstock
|
|
|
FROM Tproduit
|
|
|
WHERE noproduit = '&vnoproduit';
|
|
|
|
|
|
dstock := dstosck + &vquantite;
|
|
|
|
|
|
UPDATE Tproduit set stock = dstock WHERE noproduit = '&vnoproduit';
|
|
|
|
|
|
EXCEPTION
|
|
|
when no_data_found then INSERT INTO tligne VALUES('Reference inconnue')
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne;
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\begin{exercice} Enregistrer un nouveau produit d’un fournisseur:
|
|
|
\begin{itemize}
|
|
|
\item saisie des références du produit et du fournisseur, du prix
|
|
|
fournisseur,
|
|
|
\item accès au produit pour vérifier qu’il existe,
|
|
|
\item accès au fournisseur pour vérifier qu’il existe,
|
|
|
\item accès à la liaison produit-fournisseur pour vérifier qu’elle n’est pas déjà enregistrée,
|
|
|
\item enregistrement de la liaison.
|
|
|
\end{itemize}
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
variable vnoproduit char(4)
|
|
|
variable vreffourn varchar2(4)
|
|
|
variable vprixf number
|
|
|
variable vqta number
|
|
|
|
|
|
prompt Entrer le numero du produit:
|
|
|
accept vnoproduit
|
|
|
|
|
|
prompt Entrer la reference du fournisseur:
|
|
|
accept vreffourn
|
|
|
|
|
|
prompt Entrer le prix du produit:
|
|
|
accept vnprixf
|
|
|
|
|
|
prompt Entrer la quantite du produit:
|
|
|
accept vqta
|
|
|
|
|
|
DECLARE
|
|
|
dnbproduit char(4);
|
|
|
dreffourn char(4);
|
|
|
dcompteur number;
|
|
|
dmessage varchar2(80);
|
|
|
dmessage:='Produit n existe pas';
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
SELECT noproduit into dnoproduit
|
|
|
FROM Tproduit
|
|
|
WHERE noproduit ='&vnoproduit';
|
|
|
|
|
|
dmessage:= 'Fournisseur n existe pas';
|
|
|
|
|
|
SELECT reffourn into dreffourn
|
|
|
FROM Tfournisseur
|
|
|
WHERE reffourn ='&vreffourn';
|
|
|
|
|
|
dmessage:=' Liaison existe déjà';
|
|
|
|
|
|
SELECT count(*) INTO dcompteur
|
|
|
FROM Tproduitfourn
|
|
|
WHERE noproduit ='&vnoproduit' and reffourn ='&vreffourn';
|
|
|
|
|
|
if dcompteur =1 then INSERT INTO tligne VALUES(dmessage)
|
|
|
else INSERT INTO Tproduitfourn VALUES (dnoproduit, dreffourn,
|
|
|
|
|
|
&vprixf,0,&vqta);
|
|
|
EXCEPTION
|
|
|
when no_data_found then
|
|
|
INSERT INTO tligne VALUES(dmessage);
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne;
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
DEUXIEME SOLUTION
|
|
|
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
variable vnoproduit char(4)
|
|
|
variable vreffourn varchar2(4)
|
|
|
variable vprixf number
|
|
|
variable vqta number
|
|
|
|
|
|
prompt Entrer le numero du produit:
|
|
|
accept vnoproduit
|
|
|
|
|
|
prompt Entrer la reference du fournisseur:
|
|
|
accept vreffourn
|
|
|
|
|
|
prompt Entrer le prix du produit:
|
|
|
accept vnprixf
|
|
|
|
|
|
prompt Entrer la quantite du produit:
|
|
|
accept vqta
|
|
|
|
|
|
DECLARE
|
|
|
dnbproduit char(4);
|
|
|
dreffourn char(4);
|
|
|
dcompteur number;
|
|
|
dmessage varchar2(80);
|
|
|
|
|
|
dmessage:='Produit n existe pas';
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
SELECT noproduit into dnoproduit
|
|
|
FROM Tproduit
|
|
|
WHERE noproduit ='&vnoproduit';
|
|
|
|
|
|
dmessage:= 'Fournisseur n existe pas';
|
|
|
|
|
|
SELECT reffourn into dreffourn
|
|
|
FROM Tfournisseur
|
|
|
WHERE reffourn ='&vreffourn';
|
|
|
|
|
|
dmessage: =' Liaison existe déjà';
|
|
|
|
|
|
INSERT INTO Tproduitfourn VALUES (dnoproduit, dreffourn, &vprixf,0,&vqta);
|
|
|
|
|
|
EXCEPTION
|
|
|
when no_data_found then INSERT INTO tligne VALUES(dmessage);
|
|
|
when dup_val_on_index then INSERT INTO tligne
|
|
|
|
|
|
VALUES(dmessage);
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne;
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\subsection{Erreur Oracle}
|
|
|
|
|
|
Dès que l'erreur Oracle est rencontrée, passage automatique à la
|
|
|
section \code{EXCEPTION} pour réaliser le traitement approprié à
|
|
|
l'erreur. L’erreur est documentée par un code d’erreur \code{SQLCODE}
|
|
|
et un message \code{SQLERRM}.
|
|
|
|
|
|
Syntaxe:
|
|
|
\begin{verbatim}
|
|
|
EXCEPTION
|
|
|
WHEN nom erreur THEN traitement;
|
|
|
[WHEN nom erreur THEN traitement;]
|
|
|
...
|
|
|
[WHEN OTHERS THEN traitement;]
|
|
|
\end{verbatim}
|
|
|
Sortie du bloc après exécution du traitement.
|
|
|
|
|
|
Les principales erreurs Oracle prédéfinies sont d\'ecrires dans le
|
|
|
tableau ci-dessou.
|
|
|
|
|
|
\begin{tabular}{l|l|l}
|
|
|
déclenchée par : & nom de l’erreur :& valeur correspondante du
|
|
|
\code{sqlcode}\\
|
|
|
\hline
|
|
|
\code{insert}, \code{update} & \verb+DUP_VAL_ON_INDEX+ & -1\\
|
|
|
& \verb+INVALID_CURSOR+ & -1001 \\
|
|
|
& \verb+INVALID_NUMBER+ & -1722 \\
|
|
|
& \verb+LOGIN_DENIED+ & -1017 \\
|
|
|
\code{select} & \verb+NO_DATA_FOUND+ & +100 (non
|
|
|
déclenché par \code{update} ou \code{delete},\\
|
|
|
& & \phantom{+100 (}ni par \verb+select count+, \code{select sum})\\
|
|
|
& \verb+NO_LOGGED_ON+ & -1012\\
|
|
|
& \verb+PROGRAM_ERROR+ & -6501\\
|
|
|
& \verb+STORAGE_ERROR+ & -6500 \\
|
|
|
& \verb+TIMEOUT_ON_RESOURCE+ & -51 \\
|
|
|
\code{select} & \verb+TOO_MANY_ROWS+ & -1422 \\
|
|
|
&\verb+VALUE_ERROR+ & -6502\\
|
|
|
&\verb+ZERO_DIVIDE+ & -1476\\
|
|
|
& \verb+OTHERS+ & toutes les autres erreurs non
|
|
|
explicitement nommées, \\ && en particuliers celles concernant les
|
|
|
contraintes d’intégrité \\ && et les conflits d’accès. \code{Others}
|
|
|
doit être la dernière erreur \\ && de la section \code{exception}.\\
|
|
|
\end{tabular}
|
|
|
|
|
|
|
|
|
\begin{exemple}[Détection de doublons sur une clé primaire.]
|
|
|
La séquence suivante crée une ligne dans la table \code{tproduit (noproduit char(4) primary key, ...)} et stocke les erreurs dans une table \code{tligne (ligne varchar2(150));}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
BEGIN
|
|
|
INSERT INTO tproduit VALUES (dnoproduit, ddesignation ...);
|
|
|
EXCEPTION
|
|
|
WHEN DUP_VAL_ON_INDEX THEN
|
|
|
INSERT INTO tligne VALUES (dnoproduit || 'déjà inséré');
|
|
|
END;
|
|
|
\end{verbatim}
|
|
|
\end{exemple}
|
|
|
|
|
|
\begin{exemple} Récupération du message d’erreurretourné par Oracle en cas
|
|
|
d’exception.
|
|
|
|
|
|
Attention : \code{SQLERRM} et \code{SQLCODE} ne peuvent être
|
|
|
utilisés directement dans une instruction SQL. Il faut les récupérer
|
|
|
dans une variable.
|
|
|
\begin{verbatim}
|
|
|
DECLARE
|
|
|
dnom tproduit.designation%type;
|
|
|
dmess1 VARCHAR2(100);
|
|
|
dmess2 VARCHAR2(100);
|
|
|
BEGIN
|
|
|
dmess1 := 'Référence inconnue';
|
|
|
SELECT designation INTO dnom FROM tproduit
|
|
|
WHERE noproduit = ‘&vnoproduit’;
|
|
|
|
|
|
EXCEPTION
|
|
|
WHEN OTHERS THEN
|
|
|
dmess2:= SQLERRM;
|
|
|
INSERT INTO tligne VALUES (dmess1);
|
|
|
INSERT INTO tligne VALUES (dmess2);
|
|
|
COMMIT;
|
|
|
END;
|
|
|
\end{verbatim}
|
|
|
\end{exemple}
|
|
|
|
|
|
\begin{exercice} Mise en évidence des exceptions \verb+TOO_MANY_ROWS, OTHERS+
|
|
|
|
|
|
Pour mettre en évidence l’exception \verb+TOO_MANY_ROWS+, créez un
|
|
|
fichier de commandes SQL qui: demande un numéro de fournisseur, accède
|
|
|
aux N° des produits de ce fournisseur (par select into) retourne le
|
|
|
message ‘\code{le fournisseur n’a pas de produit}’ ou ‘\code{le fournisseur a plusieurs
|
|
|
produits}’, ou le N° du produit s’il n’en a qu’un.
|
|
|
|
|
|
Le résultat sera placé dans une table \code{TLIGNE (LIGNE
|
|
|
varchar2(200))} Placez aussi dans cette table le \code{SQLCODE} et
|
|
|
le message Oracle obtenus à l’issue de \code{SELECT}.
|
|
|
|
|
|
Modifiez le programme en remplaçant une des deux exceptions par \code{OTHERS}.
|
|
|
\end{exercice}
|
|
|
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
variable vreffourn char(4)
|
|
|
|
|
|
prompt Entrer la reference du fournisseur:
|
|
|
accept vreffourn
|
|
|
|
|
|
DECLARE
|
|
|
|
|
|
dnoproduit char(4);
|
|
|
BEGIN
|
|
|
|
|
|
SELECT noprod INTO dnoproduit
|
|
|
FROM Tproduitfourn
|
|
|
WHERE reffourn ='&reffourn'
|
|
|
|
|
|
INSERT INTO tligne VLAUES (dnoporduit)
|
|
|
|
|
|
EXCEPTION
|
|
|
when no_data_found then
|
|
|
INSERT INTO tligne VALUES(TO_CHAR(SQLCODE)||''||SQLERRM||'
|
|
|
Le fournisseur n''a pas le produit' );
|
|
|
when too_many_rows then
|
|
|
INSERT INTO tligne VALUES(O_CHAR(SQLCODE)||' '||SQLERRM||'
|
|
|
Le fournisseur a plusieurs produit');
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne;
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\section{Les curseurs}
|
|
|
\subsection{\code{DECLARE, OPEN, FETCH, CLOSE}}
|
|
|
|
|
|
Lorsqu’une requête \code{SELECT} est susceptible de délivrer plusieurs
|
|
|
lignes, on associe à celle ci un «curseur explicite» qui va
|
|
|
permettre d’accéder aux lignes du résultat. L’algorithme est analogue
|
|
|
au traitement d’un fichier séquentiel.
|
|
|
|
|
|
\begin{exemple}[Traitement des rayons d’un étage donné]
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DECLARE
|
|
|
detage trayon.etage%TYPE;
|
|
|
dcode_rayon trayon.code_rayon%TYPE;
|
|
|
dnom_rayon trayon.nom_rayon%TYPE;
|
|
|
|
|
|
CURSOR r IS select code_rayon, nom_rayon from trayon where etage = detage;
|
|
|
|
|
|
BEGIN
|
|
|
detage := 2;
|
|
|
OPEN r;
|
|
|
FETCH r INTOdcode_rayon, dnom_rayon;
|
|
|
|
|
|
WHILE r%FOUND
|
|
|
LOOP
|
|
|
... traitement de la ligne lue ...
|
|
|
FETCH r INTOdcode_rayon,dnom_rayon;
|
|
|
END LOOP;
|
|
|
CLOSE r;
|
|
|
END;
|
|
|
\end{verbatim}
|
|
|
\end{exemple}
|
|
|
|
|
|
\begin{exercice} Pourcentage de produits ayant un stock nul (une solution avec curseur et une sans).
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
VERSION SANS CURSEUR
|
|
|
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
declare
|
|
|
|
|
|
dtotalproduit number;
|
|
|
dnbproduitstocknull number;
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
SELECT coutn(*) INTO dtotalproduit
|
|
|
FROM Tproduit;
|
|
|
|
|
|
SELECT coutn(*) INTO dnbproduitstocknull
|
|
|
FROM Tproduit
|
|
|
WHERE stock =0 ;
|
|
|
|
|
|
INSERT INTO tligne VALUES ('Le pourcentage est '||
|
|
|
TO_CHAR(100*(dnbproduitstocknull/dtotalproduit)))
|
|
|
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne;
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
|
|
|
VERSION AVEC CURSEUR
|
|
|
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
declare
|
|
|
|
|
|
dtotal number;
|
|
|
dstocknull number;
|
|
|
dstock Tproduit.stock%TYPE;
|
|
|
|
|
|
CURSOR r IS
|
|
|
SELECT stock
|
|
|
FROM Tproduit;
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
dtotal =0;
|
|
|
dstocknull=0;
|
|
|
OPEN r;
|
|
|
FETCH r INTO dstock;
|
|
|
WHILE r%FOUND
|
|
|
LOOP
|
|
|
dtotal=dtotal +1;
|
|
|
if dstock=0 then tdstocknull= dstocknull+1;
|
|
|
FETCH r INTO dstock;
|
|
|
END LOOP;
|
|
|
|
|
|
CLOSE R;
|
|
|
|
|
|
INSERT INTO tligne VALUES ('Le pourcentage est '|| TO_CHAR(100*(dnbproduitstocknull/dtotalproduit)))
|
|
|
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne;
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\begin{exercice} Créer une table des produits classés par valeur du stock: No de produit, désignation, valeur du stock, classement.
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE classement;
|
|
|
CREATE TABLE classement (noproduit varchar2(4),designation varchar2(100),
|
|
|
stock number,classement number);
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
declare
|
|
|
|
|
|
dnoproduit Tproduit.noproduit%TYPE;
|
|
|
|
|
|
ddesignation Tproduit.designation%TYPE;
|
|
|
dstock Tproduit.stock%TYPE;
|
|
|
cpt number;
|
|
|
|
|
|
CURSOR r IS
|
|
|
SELECT noproduit, designation, stock
|
|
|
FROM Tproduit
|
|
|
ORDER by stock;
|
|
|
|
|
|
BEGIN
|
|
|
cpt: =0;
|
|
|
open r;
|
|
|
|
|
|
FETCH r INTO dnoproduit, ddesignation, dstock;
|
|
|
WHILE r%FOUND
|
|
|
LOOP
|
|
|
cpt:= cpt +1;
|
|
|
INSERT INTO classement VALUES (dnoproduit,ddesigantion,
|
|
|
TO_CHAR(dstock), TO_CHAR(cpt));
|
|
|
FETCH r INTO dnoproduit, ddesignation, dstock;
|
|
|
END LOOP;
|
|
|
CLOSE R;
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
SELECT * FROM tligne;
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\begin{exercice} créer une table des produits qui n’ont pas de fournisseur et une table des produits qui en ont avec les noms de leurs fournisseurs.
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE produitsansfourn;
|
|
|
CREATE TABLE produitsansfourn (noproduit varchar2(4));
|
|
|
|
|
|
DROP TABLE produitfourn;
|
|
|
CREATE TABLE produitfourn (noproduit varchar2(4),nomfournisseur varchar2(4));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
declare
|
|
|
|
|
|
dnoproduit Tproduit.fourn%TYPE;
|
|
|
dnomfourn Tfournisseur.nom%TYPE;
|
|
|
|
|
|
CURSOR NF IS
|
|
|
SELECT noproduit FROM Tproduit
|
|
|
WHERE noproduit not in (SELECT noproduit from Tproduitfourn);
|
|
|
|
|
|
CURSOR r IS
|
|
|
SELECT Tproduit.noproduit, Tfournisseur.nom
|
|
|
FROM Tproduit, Tproduitfourn, Tfournisseur
|
|
|
WHERE Tproduit.noproduit=Tproduitfourn.noproduit and
|
|
|
Tproduit.reffourn = Tfournisseur.reffourn;
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
open r;
|
|
|
FETCH r INTO dnoproduit;
|
|
|
WHILE r%FOUND
|
|
|
LOOP
|
|
|
INSERT INTO produitfourn VALUES (dnoproduit);
|
|
|
FETCH r INTO dnoproduit;
|
|
|
END LOOP;
|
|
|
CLOSE R;
|
|
|
|
|
|
open NF;
|
|
|
FETCH NF INTO dnoproduit;
|
|
|
WHILE NF%FOUND
|
|
|
LOOP
|
|
|
INSERT INTO produitsansfourn VALUES (dnoproduit);
|
|
|
FETCH NF INTO dnoproduit;
|
|
|
END LOOP;
|
|
|
CLOSE NF;
|
|
|
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\subsection{\code{UPDATE, DELETE ... WHERE CURRENT OF curseur}}
|
|
|
Lorsque l’on parcourt une table (ou une vue modifiable) par
|
|
|
\code{FETCH} à l’aide d’un curseur, on peut demander à mettre à jour
|
|
|
ou supprimer la ligne courante par la condition \code{WHERE CURRENT
|
|
|
OF} nom du curseur. Le curseur doit avoir été déclaré \code{FOR
|
|
|
UPDATE}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
CURSOR r IS
|
|
|
select code_rayon, nom_rayon from trayon where etage = detage FOR UPDATE;
|
|
|
\end{verbatim}
|
|
|
|
|
|
La mise à jour se fera par :
|
|
|
\begin{verbatim}
|
|
|
UPDATE trayon SET nom_rayon = dnom_rayon WHERE CURRENT OF r;
|
|
|
COMMIT;
|
|
|
\end{verbatim}
|
|
|
|
|
|
Attention, terminer par \code{COMMIT}, sinon risque d’interblocage
|
|
|
entre deux exécutions qui voudraient écrire dans la même table.
|
|
|
|
|
|
\begin{exercice}
|
|
|
augmenter de 10\% les produits de plus de 1000\euro{}, et de 5\% les autres.
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
declare
|
|
|
|
|
|
dprix Tproduit.prix%TYPE;
|
|
|
|
|
|
CURSOR r IS
|
|
|
SELECT prixv
|
|
|
FROM Tproduit
|
|
|
FOR UPDATE;
|
|
|
|
|
|
BEGIN
|
|
|
open r;
|
|
|
FETCH r INTO dprix;
|
|
|
WHILE r%FOUND
|
|
|
LOOP
|
|
|
if dprix>1000
|
|
|
then UPDATE Tproduit SET prixv=dprix*1.1 WHERE CURRENT of r; COMMIT;
|
|
|
else UPDATE Tproduit SET prixv=dprix*1.05 WHERE CURRENT of r; COMMIT;
|
|
|
end if;
|
|
|
FETCH r INTO dprixv;
|
|
|
END LOOP;
|
|
|
CLOSE R;
|
|
|
|
|
|
END;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
Erreur utilisateur : \code{EXCEPTION, RAISE}
|
|
|
|
|
|
Le traitement de l’anomalie doit être déclenché en passant dans la
|
|
|
partie \code{EXCEPTION} par \code{RAISE}.
|
|
|
\begin{verbatim}
|
|
|
DECLARE
|
|
|
...
|
|
|
nom_erreur EXCEPTION;
|
|
|
...
|
|
|
BEGIN
|
|
|
...
|
|
|
IF anomalie
|
|
|
THEN RAISE nom_erreur;
|
|
|
...
|
|
|
EXCEPTION
|
|
|
WHEN nom_erreur THEN
|
|
|
traitement à effectuer en cas d’anomalie;
|
|
|
END ;
|
|
|
\end{verbatim}
|
|
|
|
|
|
Sortie du bloc après exécution du traitement.
|
|
|
|
|
|
\begin{exemple}
|
|
|
Accèder au stock d’un produit de Numéro donné. Erreur Oracle quand le
|
|
|
produit n’existe pas Erreur utilisateur quand le stock est nul Les
|
|
|
anomalies sont placées dans une table \code{terreur(z1 varchar2(30),
|
|
|
z2 varchar2(30))}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DECLARE
|
|
|
...
|
|
|
stock_nul EXCEPTION;
|
|
|
dstock number;
|
|
|
...
|
|
|
BEGIN
|
|
|
SELECT stock INTO dstock FROM tproduit WHERE noproduit = ‘&vnoproduit’;
|
|
|
IF stock = 0
|
|
|
THEN RAISE stock_nul;
|
|
|
traitement quand le stock n’est pas nul...
|
|
|
EXCEPTION
|
|
|
WHEN NO_DATA_FOUND THEN
|
|
|
INSERT INTO terreur VALUES (‘&vnoproduit’, ‘produit inconnu’);
|
|
|
WHEN stock_nul THEN
|
|
|
INSERT INTO terreur VALUES (‘&vnoproduit’, ‘stock nul’);
|
|
|
END;
|
|
|
\end{verbatim}
|
|
|
\end{exemple}
|
|
|
|
|
|
\begin{exercice} Pour remplacer l’erreur Oracle par une erreur utilisateur, compter le nombre de produits ayant le numéro donné et déclencher une exception si ce nombre est nul.
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
variable vnoproduit varchar2(4)
|
|
|
|
|
|
prompt Entrer le numereo du produit:
|
|
|
accept vnoproduit
|
|
|
|
|
|
DECLARE
|
|
|
nbproduitnull EXCEPTION;
|
|
|
dnbproduit number;
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
SELECT count(*) INTO dnbproduit
|
|
|
FROM Tproduit
|
|
|
WHERE noproduit ='&vnoproduit'
|
|
|
|
|
|
if dnobproduit =0 the raise nbproduitnull;
|
|
|
end if;
|
|
|
|
|
|
EXCEPTION
|
|
|
when nbproduitnull then
|
|
|
INSERT INTO tligne VLAUES ('&vnoporduit)'||'n existe pas');
|
|
|
.
|
|
|
/
|
|
|
SELECT * FROM tligne;
|
|
|
set verify on;
|
|
|
set feed on;
|
|
|
set echo on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
|
|
|
\newpage
|
|
|
|
|
|
\begin{figure}[htb]
|
|
|
\begin{center}
|
|
|
\includegraphics[width=18cm]{BD.png}
|
|
|
\end{center}
|
|
|
\caption{Base de donn\'ees pour les exercices.}\label{fig:bd}
|
|
|
\end{figure}
|
|
|
|
|
|
\newpage
|
|
|
|
|
|
\begin{center}
|
|
|
{\bf Commandes utiles pour les TP}
|
|
|
\end{center}
|
|
|
|
|
|
\paragraph{Premi\`ere s\'eance :} Connexion : \verb+sqlplus <user>/<password>@KIROV+
|
|
|
|
|
|
\verb+source /etc/profile+
|
|
|
|
|
|
Lancer \verb+sqlplus+ avec cette commande \verb+rlwrap sqlplus+ vous
|
|
|
permet d'avoir l'historique.
|
|
|
|
|
|
|
|
|
Lors de votre première connexion modifier votre mot de passe avec la
|
|
|
commande SQL : \verb+PASSWORD;+
|
|
|
|
|
|
Ce qui est équivalent à :
|
|
|
\verb+ALTER USER dupond IDENTIFIED BY password;+
|
|
|
|
|
|
Si vous avez perdu votre mot de passe se connecter en \code{ssh} sur
|
|
|
\code{londres} et faire \code{oracle\_passwd}
|
|
|
|
|
|
|
|
|
Pour lancer un fichier \verb+.sql+ en sqlplus, il suffit de taper :
|
|
|
\verb+@toto.sql;+
|
|
|
|
|
|
Pour quitter sqlplus, il suffit de taper : \verb+quit;+
|
|
|
|
|
|
|
|
|
\paragraph{Mise en forme : }
|
|
|
|
|
|
Sous sqlplus :
|
|
|
\begin{verbatim}
|
|
|
Set linesize 150 -- positionne la taille d'une ligne
|
|
|
Set pagesize 300 -- positionne le nombre de lignes avant de réafficher les entêtes
|
|
|
Set pages 0 -- n'affiche pas les entêtes
|
|
|
Col <nom_colonne> for A10 -- défini que la colonne nom_colonne va être affiché sur
|
|
|
10 caractères alphanumériques, 999.99 pour les valeurs numériques.
|
|
|
\end{verbatim}
|
|
|
|
|
|
|
|
|
\paragraph{Corbeille :} Vider les tables \verb+BIN$$xxxx+ :
|
|
|
\verb+PURGE RECYCLEBIN;+
|
|
|
|
|
|
|
|
|
\paragraph{D\'ebug :} Connaître l'utilisateur connect\'e : \verb+show user;+
|
|
|
|
|
|
Liste des tables d'un \verb+user+ : \verb+SELECT table_name FROM user_tables;+
|
|
|
|
|
|
Lister les tables accessibles par l'utilisateur
|
|
|
:\verb+ SELECT table_name FROM all_tables;+
|
|
|
|
|
|
Lister toutes les tables possédées par un utilisateur
|
|
|
\verb+SELECT * FROM all_tables WHERE owner='PALAFOUR';+
|
|
|
|
|
|
Liste des vues d'un user : \verb+SELECT view_name FROM user_views;+
|
|
|
|
|
|
Liste des contraintes : \verb+SELECT * FROM user_constraints WHERE table_name=’<table>’;+
|
|
|
|
|
|
\verb+SELECT * FROM user_cons_columns WHERE table_name='<table>";+
|
|
|
|
|
|
|
|
|
\paragraph{Contraintes :} Description d'une table : \verb+describe <table>+ ou
|
|
|
\verb+desc <table>+
|
|
|
|
|
|
Liste des colonnes concernées par les contraintes :
|
|
|
\verb+SELECT * FROM user_cons_columns;+
|
|
|
|
|
|
Lire une table d'un autre schéma :
|
|
|
|
|
|
\verb+SELECT * FROM <schema>.<table_name>; -- ou schma = login de connexion de l'utilisateur+
|
|
|
|
|
|
\paragraph{Affichage :} ~ \\
|
|
|
|
|
|
\verb+SET HEADING OFF+
|
|
|
|
|
|
\verb+SET FEEDBACK OFF+
|
|
|
|
|
|
\paragraph{Acc\`es fichiers}
|
|
|
\begin{verbatim}
|
|
|
https://homeweb.iut-clermont.uca.fr/pydio/
|
|
|
\end{verbatim}
|
|
|
|
|
|
\paragraph{Aide en ligne}
|
|
|
\begin{verbatim}
|
|
|
http://cr2i.intranet.iut.local/site/
|
|
|
\end{verbatim}
|
|
|
|
|
|
%\newpage
|
|
|
|
|
|
%\printindex
|
|
|
\end{document}
|
