Procédures et fonctions stockées

Version imprimable

Je travaille en ce moment sur un cours portant sur les procédures et les fonctions stockées pl/sql.

J'ai quelques questions à ce sujet.
Je vous remercie beaucoup d'avoir la gentillesse de bien vouloir y répondre.

procedures stockées

Quand, le mode de paramètre est OUT, il est indiqué dans le cours dont je dispose, que le paramètre formel agit en tant que
variable non initialisée et ne peut pas être affecté à une autre variable ou à lui-même.
En outre, le paramètre réel dans le mode paramètre OUT doit être une variable.
Voici un exemple de procedure stockée avec des paramètre OUT
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE OR REPLACE PROCEDURE query_emp ( p_id IN emp.empno%TYPE, p_name OUT emp_ename%TYPE, p_salary OUT emp.sal%TYPE, p_comm OUT emp.comm%TYPE) IS BEGIN SELECT ename, sal, comm INTO p_name, p_salary,p_comm WHERE empno =p_id; END query_emp; /
Dans cette procédure, un des paramètre OUT est, par exemple p_name.
C'est bien une variable non initialisée, et que je ne peux pas initialiser.

Lorqu'il est dit que l'on ne peut affecter cette variable à lui-même, cela signifie t-il que je ne peux pas avoir,dans le code, une expresion telle que p_name= p_name;
Dans quel cas pourrais-je avoir une telle instruction?

Dans la syntaxe de création d'une procédure, il est écrit IS/AS après la section des paramètres
Que signifie ce AS? a-til la même valeur que le IS et peut-on utiliser indifféremment l'un ou l'autre.?

Pour ce qui concerne le mot clé REPLACE dans la syntaxe de création d'une procédure ou d'une fonction, est-ce bien du fait du mot clé REPLACE que je peux ensuite effectuer un ALTER function ou un ALTER procedure?
D'après ce que j'ai compris, si je ne fais pas cela, il me sera impossible de faire un ALTER function ou un ALTER procedure?

J'ai fait un test et me suis rendu compte que j'ai pu faire un alter function même si je n'ai pas indiqué REPLACE dans la syntaxe de création de cette fonction.

Pouvez-vous m'expliquer mon erreur, si erreur il y a et ce que je n'ai pas bien compris.

Je vous en remercie.

D'après ce que j'ai appris, la commande ALTER function ou ALTER procedure permet de changer la definition d'une fonction ou d'une procédure, mais pas son code ( ses instructions).

Est ce bien exact?
Si c'est le cas, est-il si important alors de préciser OR REPLACE dans la syntaxe de création d'une procédure ou d'une fonction?

Existe t-il une machine virtuelle , dans le moteur pl/SQl qui permet d'exécuter les programmes pl/sql, puisque le code est d'abord modifié en pseude-code, il faut un outil pour executer ce code ensuite.

En ce qui concerne les paramètres de l'instruction ALTER FUNCTION , il sont entre autres les suivants:
schema: specifie le schema qui contient la fonction

function:specifie le nom de la fonction à recompiler

COMPILE:specifier COMPILE pour que la base de données recompile la fonction

J'ai tenté de lancer l'instruction alter function non definie avec OR REPLACE avec ce paramètre ,et la compilation a bien eu lieu.

Pouvez-vous m'expliquer pourquoi( pas de message d'erreur reçu)

DEBUG:Specifer DEBUG pour informer le compilateur PL/SQL compiler de generer et de stocker le code en vue qu'il soit debuggué

Dans quels cas importants ces paramètres s'appliquent-ils?

Il est dit , dans le cours dont je dispose, qu'un fichier script avec l'instruction CREATE PROCEDURE permet de modifier l'instruction en cas d'erreurs de compilation ou d'exécution.
N'est ce pas plutôt l'instruction CREATE OR REPLACE qui le permet?

soit la procédure suivante
Code:

1 2 3 4 VARIABLE g_phone_no VARCHAR2(15) BEGIN := g_phone_no= '8006330575'; END; EXECUTE format_phone(:g_phone_no);
Pouvez-vous me confirmer le cheminement de la variable g_phone_no:
Tout d'abord, dans le bloc anonyme commençant par BEGIN, ci-dessus, elle prend la valeur 8006330575
Ce qui implique que la variable g_phone_no a pour valeur 8006330575
Ensuite, cette valeur est utilisée pour le paramètre réél :=g_phone_no de la procedure format_phone
Je vous demande cela, pour bien confirmer que c'est du fait que la variable g_phone_no existe en dehors du bloc anonyme, que je peux reutiliser sa valeur dans l'appel à la procedure format_phone

Fonctions stockées

Pouvez-vous m'expliquer cette phrase

Citation:

"on peut appeler des fonctions stockées à partir d'expression SQL pour augmenter l'indépendance des données .
Ceci permet de traiter des analyses complexes de données dans le serveur Oracle plutôt que d'extraire les données dans une application afin d'y executer la fonction."

Pouvez-vous me donner un exemple qui illustre ces propos et permet de mieux les comprendre.

Je vous en remercie.
Par ailleurs, que signifient
- "augmenter l'indépendance des données"
pouvez vous me donner un exemple.Je vous en remercie beaucoup.

-traiter des analyses complexes des données dans le serveur Oracle

Que signifie le fait que les fonctions stockées permettent de gérer de nouveaux types de données?

Voici la phrase suivante" les expression SQL pouvant provoquer des effets de bord sont les suivantes INSERT, UPDATE, DELETE
Ceci signifie qu'il n'est pas permis, par le biais d'une fonction stockée de modifier les valeurs de la base de données, mais de simplement ramener une valeur à partir de celles dejà existantes dans les colonnes de base de données .

Pouvez vous m'expliquer pourquoi une telle restriction êst nécessaire.

Vous remerciant encore beaucoup de votre aide sur tous ces points ,

Cordialement.

new_wave

08/09/2011, 16h46
Pomalaix

En préambule, on ne peut que louer la minutie que vous mettez dans cette étude !
Je vous conseillerais en complément de consulter la doc PL/SQL de chez Oracle, car vous y trouverez notamment toutes les explications concernant les diverses options de syntaxe.

Et pour les questions qui vous resteront, il serait plus simple que vous les numérotiez, car sinon ça va être la pagaille pour s'y retrouver lors des réponses.

procedures-fonctions stockées-message reformulé-

Bonjour,
Je vais donc m'appliquer à mettre des numéros face à chaque question

Je travaille en ce moment sur un cours portant sur les procédures et les fonctions stockées pl/sql.

J'ai quelques questions à ce sujet.
Je vous remercie beaucoup d'avoir la gentillesse de bien vouloir y répondre.

procedures stockées

Quand, le mode de paramètre est OUT, il est indiqué dans le cours dont je dispose, que le paramètre formel agit en tant que
variable non initialisée et ne peut pas être affecté à une autre variable ou à lui-même.
En outre, le paramètre réel dans le mode paramètre OUT doit être une variable.
Voici un exemple de procedure stockée avec des paramètre OUT
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE OR REPLACE PROCEDURE query_emp ( p_id IN emp.empno%TYPE, p_name OUT emp_ename%TYPE, p_salary OUT emp.sal%TYPE, p_comm OUT emp.comm%TYPE) IS BEGIN SELECT ename, sal, comm INTO p_name, p_salary,p_comm WHERE empno =p_id; END query_emp; /
Dans cette procédure, un des paramètre OUT est, par exemple p_name.
C'est bien une variable non initialisée, et que je ne peux pas initialiser.

1-Lorqu'il est dit que l'on ne peut affecter cette variable à lui-même, cela signifie t-il que je ne peux pas avoir,dans le code, une expresion telle que p_name= p_name;
Dans quel cas pourrais-je avoir une telle instruction?

2-Dans la syntaxe de création d'une procédure, il est écrit IS/AS après la section des paramètres
Que signifie ce AS? a-til la même valeur que le IS et peut-on utiliser indifféremment l'un ou l'autre.?

3-Pour ce qui concerne le mot clé REPLACE dans la syntaxe de création d'une procédure ou d'une fonction, est-ce bien du fait du mot clé REPLACE que je peux ensuite effectuer un ALTER function ou un ALTER procedure?
D'après ce que j'ai compris, si je ne fais pas cela, il me sera impossible de faire un ALTER function ou un ALTER procedure?
Est ce exact?

J'ai fait un test et me suis rendu compte que j'ai pu faire un alter function même si je n'ai pas indiqué REPLACE dans la syntaxe de création de cette fonction.

Pouvez-vous m'expliquer mon erreur, si erreur il y a, et ce que je n'ai pas bien compris.

Je vous en remercie.

4-D'après ce que j'ai appris, la commande ALTER function ou ALTER procedure permet de changer la definition d'une fonction ou d'une procédure, mais pas son code ( ses instructions).

Est ce bien exact?

5-En ce qui concerne les paramètres de l'instruction ALTER FUNCTION , il sont entre autres les suivants:
schema: specifie le schema qui contient la fonction

function:specifie le nom de la fonction à recompiler

COMPILE:specifier COMPILE pour que la base de données recompile la fonction

J'ai tenté de lancer l'instruction alter function non définie avec OR REPLACE avec ce paramètre ,et la compilation a bien eu lieu.

Pouvez-vous m'expliquer pourquoi( pas de message d'erreur reçu)

DEBUG:Specifer DEBUG pour informer le compilateur PL/SQL compiler de generer et de stocker le code en vue qu'il soit debuggué

Dans quels cas importants tous ces paramètres s'appliquent-ils?

6-Existe t-il une machine virtuelle , dans le moteur pl/SQl qui permet d'exécuter les programmes pl/sql, puisque le code est d'abord modifié en pseude-code, il faut un outil pour executer ce code ensuite.

7-soit la procédure suivante
Code:

1 2 3 VARIABLE g_phone_no VARCHAR2(15) BEGIN := g_phone_no= '8006330575'; END; EXECUTE format_phone(:g_phone_no);
Pouvez-vous me confirmer le cheminement de la variable g_phone_no:
Tout d'abord, dans le bloc anonyme commençant par BEGIN, ci-dessus, elle prend la valeur 8006330575
Ce qui implique que la variable g_phone_no a pour valeur 8006330575
Ensuite, cette valeur est utilisée pour le paramètre réél :=g_phone_no de la procedure format_phone
Je vous demande cela, pour bien confirmer que c'est du fait que la variable g_phone_no existe en dehors du bloc anonyme, que je peux reutiliser sa valeur dans l'appel à la procedure format_phone

Fonctions stockées

8-Pouvez-vous m'expliquer cette phrase
Citation:
"on peut appeler des fonctions stockées à partir d'expression SQL pour augmenter l'indépendance des données .
Ceci permet de traiter des analyses complexes de données dans le serveur Oracle plutôt que d'extraire les données dans une application afin d'y executer la fonction."
Pouvez-vous me donner un exemple qui illustre ces propos et permet de mieux les comprendre.

Je vous en remercie.
Par ailleurs, que signifient
- "augmenter l'indépendance des données"
pouvez vous me donner un exemple.Je vous en remercie beaucoup.

-traiter des analyses complexes des données dans le serveur Oracle

9-Que signifie le fait que les fonctions stockées permettent de gérer de nouveaux types de données?

10-Voici la phrase suivante" les expression SQL pouvant provoquer des effets de bord sont les suivantes INSERT, UPDATE, DELETE
Ceci signifie qu'il n'est pas permis, par le biais d'une fonction stockée de modifier les valeurs de la base de données, mais de simplement ramener une valeur à partir de celles dejà existantes dans les colonnes de base de données .

Pouvez vous m'expliquer pourquoi une telle restriction est nécessaire.

Vous remerciant encore beaucoup de votre aide sur tous ces points ,

Cordialement.

new_wave

10/09/2011, 12h19
Pomalaix

1) A l'intérieur de la procédure, tout paramètre de sortie OUT vaut NULL tant que vous ne lui avez pas donné une valeur.
Vous avez le droit de faire toutes les affectations que vous voulez, (y compris p_name:=p_name si ça vous chante, hormis le fait que ça n'a aucun intérêt).
Par exemple

Code:

p_name:=p_name + 10

le seul souci étant que NULL + 10, ça reste NULL.
Donc à un moment donné dans la procédure, il faut que vous ayez une affectation p_name:=quelque_chose_de_non_nul

2) IS et AS sont parfaitement interchangeables.

3) REPLACE a pour seul effet de remplacer la procédure si elle existait déjà. Il n'y a aucun lien avec une possibilité de faire un ALTER.

4) (re)lisez la doc Oracle à propos du ALTER PROCEDURE. Cela ne sert qu'à recompiler la procédure.

5) Voir point 3.
De plus, on ne compile avec informations de débogage que si l'on envisage de déboguer, car cela génère en interne du code supplémentaire, et donc moins performant.

6) Le "serveur Oracle" comporte plusieurs moteurs d'exécution, notamment le moteur SQL, le moteur PL/SQL, la machine virtuelle Java.
Dans une procédure ou fonction, qui combinent en temps normal des instructions SQL et des instructions PL/SQL pures (loop, if, variables, etc), il y a des allers retours fréquents, et bien sûr automatiques, entre le moteur SQL et le moteur PL/SQL.

7) Je ne comprends pas la question.
Au passage, votre code est incorrect. ":=" c'est pour l'affectation, ":" c'est pour signaler au moteur PL/SQL que la variable a été définie à l'extérieur du code PL/SQL.
Ce n'est pas

Code:

BEGIN := g_phone_no= '8006330575'; END;

mais

Code:

BEGIN :g_phone_no := '8006330575'; END;

8) "augmenter l'indépendance des données" : je ne vois pas...
Pour le reste, les fonctions et procédures stockées permettent de faire les calculs (traitements, analyses) directement dans la base, plutôt que de ramener de grandes quantités de données vers l'application cliente, qui se chargerait de faire ces calculs. La fonction va se contenter de ramener le résultat au client, la procédure va faire le traitement intégralement dans la base, rien n'étant (sauf paramètre OUT), renvoyé vers le client.

9) Le PL/SQL propose par défaut quelques types supplémentaires par rapport au SQL, par exemple BOOLEAN.
De plus, il vous permet de créer vos propres types, qui peuvent éventuellement être exploités par des fonctions. Mais c'est la même chose pour les procédures.

10) Quand vous faites "SELECT upper(nom) FROM t", c'est un simple SELECT, qui ne modifie en rien les données dans la base, mais vous les présente à l'affichage après une certaine transformation. Maintenant, si vous écrivez une fonction de votre cru f_maj, qui non seulement renvoie la valeur en majuscules, mais en plus effectue un quelconque UPDATE, INSERT ou UPDATE, lorsque vous feriez "SELECT f_maj(nom) from t", vous auriez un SELECT qui fait indirectement des modifications en base, ce qui serait bien entendu une catastophe.
12/09/2011, 22h56
new_wave

exercice résolu

Bonsoir et merci beaucoup de toutes ces réponses.
Je considère que le problème est résolu.

Cordialement.
Nathalie