Dernière mise à jour :2008-07-24

informatique

Communication entre application Microsoft et procédures de BD Oracle

Dernièrement, j'écrivais un premier article portant sur une méthode d'accès aux bases de données de type Oracle via des applications Microsoft (Atelier d'introduction à Oracle objects for OLE (oo4o)).

L'article suivant est en quelque sorte la suite de l'article en question puisqu'il présente un survol de la création de procédures dans un environnement Oracle et donne un exemple d'exécution d'une telle procédure via la technologie web de Microsoft, Active server pages à l'aide de oo4o (Oracle objects for Ole).

note : Si vous ne disposez pas de oo4o, vous pouvez télécharger ces objets à partir du site d'Oracle.

Première étape : Création d'une procédure

Dans le monde d'Oracle, il existe quatre types de structures de bloque de code PL/SQL :

  1. procédures
  2. fonctions
  3. bloque de code anonyme
  4. package

L'article suivant porte plus spécifiquement sur la création de procédures dont certaines contiendront des bloque de code sans nom.

La structure d'une procédure est la suivante :

  1. [Section Entête]
    IS
  2. [Section déclaration]
    BEGIN
  3. [Section Exécution]
    EXCEPTION
  4. [Section Exception]
    END;

Section entête

Définition de du type de la structure (PROCEDURE ou FUNCTION) ainsi que du nom de celle-ci et le cas échéant, de ses paramètres.

Section déclaration

Déclaration de toute variable, curseur etc.. utilisé dans ce bloque de code.

Section exécution

Description des actions qui seront exécutées à l'aide des valeurs des variables locales et/ou paramètres.

Section exception

Gestion des problèmes qui subviennent durant la phase d'exécution.

Note : Les bloques de code anonymes sont formés de toutes les sections indiquées ci-dessus à l'exception de la section entête.

L'exemple ci-dessous est basé sur les tables fictives suivante :

TABLE COMMANDE
COM_PRO_SEQ (clef) COM_USE_SEQ (clef) COM_DATE (clef) COM_QTE
TABLE INVENTAIRE
INV_PRO_SEQ (clef) INV_PRO_QTE

Imaginons que le champ COM_PRO_SEQ et INV_PRO_SEQ sont tous deux reliés à une table Produits dans laquelle PRO_SEQ représente la PK (primary key «clé primaire»). Immaginons que le champ COM_USE_SEQ est pour sa part relié à une table USER et que le champ USE_SEQ de celle-ci est la PK.

À partir de ces tables fictives, nous pouvons débuter notre exemple.

Mise en situation

Vous travaillez pour une compagnie qui fait la création d'application web. Votre client est une entreprise de vente d'articles de sport. Celle-ci possède déjà une base de données de type Oracle dans laquelle les tables exposée ci-dessus sont stockées. L'entreprise décide de faire la vente de ses produits sur Internet. Le site devra être relié à la BD Oracle.

Vous débutez donc la conception de celui-ci et vous en êtes rendu à combler le besoin suivant :

Création d'une procédure qui aura pour but, la gestion des commandes et la mise à jour de l'inventaire. Cette procédure sera utilisé par la page finale du module de commandes des clients de l'entreprise. La procédure sera aussi utilisé par une page de l'intranet de l'entreprise dans laquelle, les employés pourront détruire les commandes qu'ils désire.

Voici donc le listing du code de cette procédure.

Listing 1.0 Procédure
1. PROCEDURE change_commande (v_type IN VARCHAR2,
2.     v_pro_seq IN VARCHAR2,
3.     v_use_seq IN VARCHAR2,
4.     v_date IN VARCHAR2,
5.     v_qte IN NUMBER)
6. IS
7. 
8.    v_ancienne_qte NUMBER;
9.   
10.    CURSOR cur_qte
11.    IS
12.    SELECT inv_pro_qte
13.    FROM inventaire
14.    WHERE inv_pro_seq = v_pro_seq;
15.   
16. BEGIN
17. 
18.    OPEN cur_qte;
19.    FETCH cur_qte INTO v_ancienne_qte;
20.    CLOSE cur_qte;
21.    IF (v_type = 'I') Then
22.     INSERT INTO commande (com_pro_seq, com_use_seq, com_date, com_qte)
23.      VALUES (v_pro_seq, v_use_seq, To_Date(v_date, "YY-MM-DD"), v_qte);
24.     UPDATE inventaire
25.      SET inv_pro_qte = v_ancienne_qte - v_qte
26.      WHERE inv_pro_seq = v_pro_seq;
27.     COMMIT;
28.    ELSE
29.     DELETE
30.     FROM commande
31.     WHERE com_pro_seq = v_pro_seq
32.      AND
33.      com_use_seq = v_use_seq
34.      AND
35.      com_date = v_date;
36.     COMMIT;
37.    END IF
38. END change_commande;

Description de la procédure

La section entête est définie entre les lignes 1 et 5. On spécifie d'abord le type du bloque de code (dans notre cas c'est «PROCEDURE»). Suivant le mot «PROCEDURE» nous indiquons le nom de la procédure (change_commande) et finalement, ont définit la liste des différents paramètres. Chaque paramètre est séparé du suivant par une virgule. On indique en premier lieu le nom du paramètre. Dans le cas du premier paramètre de la procédure ont inscrit «v_type». On spécifit ensuite le mode du paramètre. Celui-ci peut être IN (Lecture seul), OUT (écriture) ou encore IN OUT (Lecture et écriture). Dans le cas de notre premier paramètre ainsi que des suivants, ont indique IN puisque ces paramètres ne seront utilisés qu'en lecture seule. Finalement, on indique le type de donnée du paramètre (VARCHAR2 dans le cas du premier paramètre).

La section déclaration pour sa part est situé entre les lignes 6 et 15. c'est le mot 'IS' qui délimite le début de cette section. La première déclaration est une variable de type «NUMBER» (ligne 8). Suivant celle-ci, un curseur est déclaré entre les lignes 10 et 14. Pour les débutant en PL/SQL, voici en bref ce qu'est un curseur :

Lorsque vous exécutez une requête SQL, le RDBMS Oracle cré un espace de travail privé dédié à cette requête. Cet espace contient de l'information à propos de la requête SQL et le jeu d'enregistrements retourné ou affecté par la requête. Le curseur PL/SQL est un mécanisme par lequel on peu donner un nom à cet espace et manipuler les données qu'il contient. De façon simple, le curseur peut être imaginé comme un pointeur que l'on déplace d'un enregistrement à un autre dans une table de la BD.

Dans le cas de notre exemple, notre curseur est déclaré en ligne 10 avec le mot réservé «CURSOR». Nous lui donnons ensuite le nom de «cur_qte». La ligne suivante indique le début de la section déclaration du curseur à l'aide du mot «IS». La requête SQL sur les lignes 12, 13 et 14 indique que le curseur doit retourner le champ «inv_pro_qte» tiré de la table «inventaire» sur l'unique enregistrement pour lequel la comparaison du champ «inv_pro_seq» (clef primaire) est égal au paramètre «v_pro_seq».

Commence ensuite la zone d'exécution qui s'étalle sur le reste des lignes de la procédure (excluant la dernière qui spécifie la fin de celle-ci).

La première opération est l'ouverture du curseur «cur_qte». Ensuite (ligne 19) nous dirigeons le contenu du curseur dans une variable «v_ancienne_qte».

Note : Une variable a été choisi ici car nous savons que le curseur ne rapporte qu'un champ seul et qu'il ne changera jamais. Dans le cas où celui-ci aurait retourné plusieurs champs ou aurait été sujet à de futur changements, nous aurions pu déclarer une variable du type d'enregistrement correspondant à notre curseur en utilisant le mot clef «%ROWTYPE» (syntaxe : votre_variable votre_curseur%ROWTYPE). Cette méthode permet d'assurer la conformité entre les types et formats du curseur et celui de la variable.

Notre variable contient maintenant la quantitié du produit concerné en inventaire.

La ligne 21 contient une instruction conditionnelle vérifiant la valeur du paramètre «v_type». Si ca valeur est 'I', la procédure sait qu'il est question d'une nouvelle commande. Dans le cas contraire, c'est une supression. Le bloque d'instruction correspondant au mode 'I' exécute l'insertion d'une nouvelle commande dans la table commande et diminu la quantité du produit en inventaire en enlevant la valeur du paramètre «v_qte» de la variable «v_ancienne_qte» récupérée du le curseur. Le second bloque dans l'instruction conditionnel est une requête DELETE qui détruit la commande du client. À la fin de chacun des bloques d'instructions conditionnels, l'instruction «COMMIT» a été placé pour indiquer à Oracle d'appliquer définitivement les changements.

Lorsque la procédure est terminée, vous pouver la compiler à l'aide de votre outil préféré comme par exemple Toad.

Maintenant que la procédure est terminée et que vous la jugé fonctionnel, vous pouvez maintenant passer à la création de vos pages ASP. Pour abréger notre exemple et surtout, pour ne pas montrer deux fois la même chose, nous ne montrerons que la création de la page ASP d'insertion d'une nouvelle commande.

Voici donc, le listing du code de la page.

Listing 1.0 Page ASP
1. <html>
2. <head>
3. <title>a525g - exemple oo4o</title>
4. </head>
5. <body>
6. <%
7. const ORAPARM_INPUT = 1
8. const ORAPARM_OUTPUT = 2
9. const ORAPARM_BOTH = 3
10. 
11. const ORATYPE_VARCHAR2 = 1
12. const ORATYPE_NUMBER = 2
13. const ORATYPE_SINT = 3
14. const ORATYPE_FLOAT = 4
15. const ORATYPE_STRING = 5
16. const ORATYPE_VARCHAR = 9
17. const ORATYPE_DATE = 12
18. const ORATYPE_UINT = 68
19. const ORATYPE_CHAR = 96
20. const ORATYPE_CHARZ = 97
21. 
22. On Error Resume Next
23. 
24. Dim objOraSession
25. Dim objOraDatabase
26. 
27. Set objOraSession = CreateObject("OracleInProcServer.XOraSession")
28. Set objOraDatabase = objOraSession.OpenDatabase("reference_a_votre_bd", "utilisateur/mot_de_passe", 0)
29. 
30. 'Gestion des erreurs
31. If (objOraSession.LastServerErr > 0) Then
32.  Response.Write "Erreur : " & objOraSession.LastServerErr & " - " & objOraSession.LastServerErrText
33. Else
34.  Dim intResult
35. 
36.  objOraDatabase.Parameters.Add "v_type", "I", ORAPARAM_INPUT
37.  objOraDatabase.Parameters("v_type").ServerType = ORATYPE_VARCHAR2
38. 
39.  objOraDatabase.Parameters.Add "v_pro_seq", "a525", ORAPARAM_INPUT
40.  objOraDatabase.Parameters("v_pro_seq").ServerType = ORATYPE_VARCHAR2
41. 
42.  objOraDatabase.Parameters.Add "v_use_seq", "user123", ORAPARAM_INPUT
43.  objOraDatabase.Parameters("v_use_seq").ServerType = ORATYPE_VARCHAR2
44. 
45.  objOraDatabase.Parameters.Add "v_date", "02-05-23", ORAPARAM_INPUT
46.  objOraDatabase.Parameters("v_date").ServerType = ORATYPE_VARCHAR2
47. 
48.  objOraDatabase.Parameters.Add "v_qte", 12, ORAPARAM_INPUT
49.  objOraDatabase.Parameters("v_qte").ServerType = ORATYPE_NUMBER
50. 
51.  intResult = objOraDatabase.ExecuteSql("begin PRC_WEB_UPDATE_SESSINTRA(:v_type,:v_pro_seq,:v_use_seq,:v_date,:v_qte); end; ")
52. 
53.  objOraDataBase.Parameters.Remove "v_type"
54.  objOraDataBase.Parameters.Remove "v_pro_seq"
55.  objOraDataBase.Parameters.Remove "v_use_seq"
56.  objOraDataBase.Parameters.Remove "v_date"
57.  objOraDataBase.Parameters.Remove "v_qte"
58. End IF
59. 
60. Set objOraDynaset = Nothing
61. Set objOraDatabase = Nothing
62. %>
63. <p align="center">Votre commande a été enregistrée avec succès.</p> 64. </body>
65. </html>

Description du listing

Cet article juge que vous connaissez déjà les bases du langage HTML et du langage ASP. Si vous ne connaissez pas les bases de ces langages, visitez les pages du site a525g. Vous y trouverez plusieurs articles traitant de ces langages. Nous passerons donc outre les premières lignes sur lesquelles sont écrites les principales balises d'un fichier HTML et irons tout de suite aux lignes 7 à 20 sur lesquelles une série de constantes sont inscrites.

Les deux groupes de constantes définies serviront dans la définition des paramètres et de leurs valeurs passés à la procédure. Le premier groupe de constante (lignes 7,8,9) identifit des constantes reliées au mode des paramètres. Le second identifit les type de données auxquels peuvent être associés les paramètres.

Ensuite, en ligne 27, nous effectuons la création d'un objet OraSession pour créer une instance du serveur oo4o. Cet objet servira d'interface pour établir la connection à la base de données Oracle.

À la ligne suivante, nous effectuons la création d'un objet OraDatabase. Celui-ci représentera notre connection utilisateur à l'instance de base de données Oracle. C'est aussi à partir de ses méthodes que nous pourrons activer la procédure.

C'est entre les lignes 36 et 49 que nous créons les paramètres. Ceux-ci sont renfermé dans la collection «Parameters». C'est à l'aide de la méthode «Add» de cette collection que nous ajoutons chacun des paramètres. Les trois arguments de cette méthode sont le nom du paramètre, la valeur de celui-ci et finalement, sont mode. Sur chacune des lignes suivant l'ajout, nous spécifions le type de données en utilisant la propriété ServerType.

La principale ligne de tout le programme est la ligne 51. C'est sur cette ligne que la procédure sera exécuté à l'aide de la méthode «ExecuteSql» de notre objet «OraDatabase». La syntaxe est la suivante pour l'exécution d'une procédure :

«begin nom_procedure(:nom_param1,:nom_param2,...); end;»

La variable intResult est placé pour recevoir le nombre de ligne affectées par l'utilisation de la méthode. Il ne reste plus ensuite qu'à enlever les paramètres de la collection, ce qui pourrait être utile si nous aurions voulu exécuter une autre procédure sur la même page avec des paramètres différent. Et finalement, il faut, comme vous le savez déjà si vous programmez souvent en ASP, détruire les fameux objets pour libérer la mémoire du serveur (lignes 60 et 61).

Et voilà le travail! Vous savez maintenant la base de la création de procédure PL/SQL et de l'exécution de celles-ci à l'aide d'une application web de Microsoft.

Auteur : Sylvain Bilodeau

Date de mise en ligne : 2002-05-20 02:00:00

Aucun commentaire pour l'instant.