4D for OCI et PL/SQL

Noreddine Margoum

Cette note a pour but de vous montrer l’utilisation de routines écrites en PL/SQL au sein d’une application 4D For OCI. ♪

- Titre : 4D for OCI et PL/SQL

- Auteur : Noreddine Margoum

- Parution : 1 janvier 2005

- Licence : Ce document est issu de https://www.developpez.com et reste la propriété exclusive de son auteur. La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.

Sommaire

- I. PL/SQL

- Fonction Factorielle

- Procédure LIREINFOS

- II. Implémentation PL/SQL dans 4D for OCI

- Implémentation de la fonction PL/SQL ‘Factorielle’

- Implémentation de la procédure PL/SQL ‘LIREINFOS’

- III. Utilisation de la base de test

- IV. Conclusion

- V. Base exemple

I - PL/SQL

Le langage PL/SQL (Procedural Language/Structured Query Language) est une extension au langage SQL. PL/SQL intègre donc SQL tout en proposant la force d’un langage procédural (structures de contrôles, programmation orientée objet…).
Le propos de cette note n’étant pas une présentation du langage PL/SQL, le lecteur est donc invité à se documenter sur ce sujet.
Toutefois, les exemples abordés sont très simples, le but étant simplement de donner une idée de la manière d’utiliser du PL/SQL avec 4D For OCI.

Nous allons vous présenter un exemple de fonction en PL/SQL et un exemple de procédure en PL/SQL, exemples qui vont être utilisés ensuite dans l’application 4D For OCI.

Fonction Factorielle

-- on utilise ici le concept de récursivité pour calculer une factorielle
-- rappel : factorielle de N = N ! = 1*2x*3…*N
-- rappel : factorielle de 0 = 0 ! = 1 (par définition)

FUNCTION Factorielle (facto IN NUMBER)
RETURN NUMBER IS
BEGIN
-- condition d’arrêt de la fonction récursive : le paramètre en entrée est égal à 0
IF facto = 0 THEN
RETURN 1;    ELSE
-- appel récursif : une factorielle est le produit du facteur courant (N)
-- et de la factorielle de son prédécesseur (N-1)
RETURN facto*Factorielle(facto-1);
END IF;
END;

Commentaire sur la fonction ‘Factorielle’

Les commentaires en langage PL/SQL sont introduits par la succession de deux tirets.

L’entête de la fonction indique le nom de la fonction, le paramètre d’entrée et son type, ainsi que le type du paramètre retourné. Les paramètres d’entrée et de sortie sont ici tous deux de type NUMBER, correspondant approximativement au type réel de 4D.

Le mot-clé IN indique que le paramètre est donné en entrée (Lecture). De même, d’autres mots-clés existent : OUT, paramètre en sortie (Ecriture) et IN OUT, paramètre en entrée-sortie (Lecture/Ecriture).

Le corps de la fonction ‘Factorielle’ est compris entre les mots-clés BEGIN et END.

Remarquez que chaque instruction PL/SQL doit se terminer par un point-virgule.

Procédure LIREINFOS

PROCEDURE LIREINFOS (vUser OUT VARCHAR2,
vDate OUT VARCHAR2, vTime OUT VARCHAR2) IS
BEGIN
-- retourne le nom de l’utilisateur courant
SELECT USER INTO vUser FROM DUAL;
-- retourne la date du serveur Oracle
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY') INTO vDate FROM DUAL;
-- retourne l’heure du serveur Oracle
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') INTO vTime FROM DUAL;
END;

Commentaire sur la procédure ‘LIREINFOS’

La procédure ‘LIREINFOS’ écrit dans les paramètres qu’elle reçoit, le nom de l’utilisateur courant, la date et l’heure du serveur Oracle sous forme de chaînes de caractères.

La fonction SYSDATE, fonction SQL d’Oracle, retourne la date et l’heure du système.

On effectue ensuite la conversion de ces deux parties, date et heure, vers une des deux variables devant recevoir la date et l’heure, en utilisant le format approprié. La conversion est faite par la fonction TO_CHAR, qui est une fonction interne à Oracle.

II - Implémentation PL/SQL dans 4D for OCI

Implémentation de la fonction PL/SQL ‘Factorielle’

Méthode projet ‘Ex_Fonction_Factorielle’

1. `Méthode Ex_Fonction_Factorielle

2. `exécute une fonction PL/SQL 'Factorielle' qui retourne la factorielle d'un nombre

4. C_ENTIER LONG(vl_Envhp;vl_Errhp;vl_Svchp)

5. C_ENTIER LONG(vl_Stmthp_Create;vl_Stmthp_Exec;vl_Stmthp_Del;vl_Bind)

6. C_TEXTE(vt_UserName;vt_Password;vt_HostName;vt_SQL;vt_ErrorMessage)

7. C_ENTIER LONG(vl_Status;vl_ErrorCode)

8. C_ENTIER LONG(vl_Input)

9. C_REEL(vr_Output)

10. C_POINTEUR(vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3)

12. `paramètres de connexion (A MODIFIER)

13. vt_UserName:="Scott" `Nom d'utilisateur

14. vt_Password:="Tiger" `Mot de passe

15. vt_HostName:="ORA8QA" `Chaîne de connexion

17. vl_Input:=0 `nombre dont on voudra connaître la factorielle

18. vr_Output:=0 `résultat du calcul de la factorielle

19. `Allocation du handle environnement

20. vl_Status:=OCIEnvCreate (vl_Envhp;OCI_DEFAULT )

21. Si (vl_Status=OCI_SUCCESS )

22. `Allocation d'un handle erreur

23. vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Errhp;OCI_HTYPE_ERROR )

24. Si (vl_Status=OCI_SUCCESS )

25. ` Ouverture de la connexion et de la session

26. vl_Status:=OCILogon (vl_Envhp;vl_Errhp;vl_Svchp;vt_UserName;vt_Password;vt_HostName)

27. Si (vl_Status=OCI_SUCCESS )

28. ` Allocation du handle requête. Ce handle nous servira à créer / stocker

29. ` la fonction PL/SQL 'Factorielle' sur Oracle

30. vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Create;OCI_HTYPE_STMT )

31. Si (vl_Status=OCI_SUCCESS )

32. ` Définition de la fonction 'Factorielle' en PL/SQL

33. vt_SQL:=""

34. vt_SQL:=vt_SQL+"CREATE OR REPLACE FUNCTION Factorielle (facto IN NUMBER)"+Caractere(ASCII LF )

35. vt_SQL:=vt_SQL+"RETURN NUMBER IS"+Caractere(ASCII LF )

36. vt_SQL:=vt_SQL+"BEGIN"+Caractere(ASCII LF )

37. vt_SQL:=vt_SQL+"IF facto = 0"+Caractere(ASCII LF )

38. vt_SQL:=vt_SQL+"THEN RETURN 1;"+Caractere(ASCII LF )

39. vt_SQL:=vt_SQL+"ELSE"+Caractere(ASCII LF )

40. vt_SQL:=vt_SQL+"RETURN facto*Factorielle(facto-1);"+Caractere(ASCII LF )

41. vt_SQL:=vt_SQL+"END IF;"+Caractere(ASCII LF )

42. vt_SQL:=vt_SQL+"END;"

43. vl_Status:=OCIStmtPrepare (vl_Stmthp_Create;vl_Errhp;vt_SQL;OCI_DEFAULT )

44. vl_Status:=OCIStmtExecute(vl_Svchp;vl_Stmthp_Create;vl_errhp;1;0;0;0;OCI_DEFAULT )

45. ` la fonction Factorielle est maintenant stockée sur Oracle. On va pouvoir la tester...

46. ` Allocation du handle requête. Ce handle nous servira à tester la fonction

47. ` stockée sur Oracle PL/SQL 'Factorielle'

48. vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Exec;OCI_HTYPE_STMT )

49. Si (vl_Status=OCI_SUCCESS )

50. t_SQL:=""

51. vt_SQL:=vt_SQL+"BEGIN"+Caractere(ASCII LF )

52. vt_SQL:=vt_SQL+":DataOutput:=Factorielle(:DataInput);"+Caractere(ASCII LF )

53. vt_SQL:=vt_SQL+"END;"

54. vl_Input:=Num(Demander("Calculez la factorielle de : "))

55. vl_Status:=OCIStmtPrepare (vl_Stmthp_Exec;vl_Errhp;vt_SQL;OCI_DEFAULT )

56. ` réalisation d'un Bind par nom pour l'appel de la fonction 'Factorielle'

57. vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":DataOutput";

58. ->vr_Output;SQLT_FLT ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT;BIND_OUT )

59. vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":DataInput";

60. ->vl_Input;SQLT_INT ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT ;BIND_IN)

61. vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Exec;vl_Errhp;1;0;0;0;OCI_DEFAULT )

62. Si (vl_Status#OCI_SUCCESS )

63. vl_Status:=OCIErrorGet (vl_Errhp;1;vl_ErrorCode;vt_ErrorMessage)

64. ALERTE("Erreur Oracle "+Chaine(vl_ErrorCode)+" : "+vt_ErrorMessage)

65. Fin de si

66. ALERTE(Chaine(vl_Input)+"! = "+Chaine(vr_Output))

67. ` Allocation du handle requête. Ce handle nous servira à supprimer

68. ` la fonction PL/SQL 'Factorielle' stockée sur Oracle

69. vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Del;OCI_HTYPE_STMT )

70. Si (vl_Status=OCI_SUCCESS )

71. vt_SQL:=""

72. vt_SQL:=vt_SQL+"DROP FUNCTION FACTORIELLE"

73. vl_Status:=OCIStmtPrepare (vl_Stmthp_Del;vl_Errhp;vt_SQL;OCI_DEFAULT )

74. vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Del;vl_errhp;1;0;0;0;OCI_DEFAULT )

75. ` libération du handle requête qui a servi à supprimer la fonction Factorielle

76. vl_Status:=OCIHandleFree (vl_Stmthp_Del)

77. Fin de si

78. ` libération du handle requête qui a servi à tester la fonction Factorielle

79. vl_Status:=OCIHandleFree (vl_Stmthp_Exec)

80. Fin de si

81. ` libération du handle requête qui a servi à créer la fonction 'Factorielle'

82. vl_Status:=OCIHandleFree (vl_Stmthp_Create)

83. Fin de si

84. vl_Status:=OCILogoff (vl_Svchp;vl_Errhp)

85. Fin de si

86. vl_Status:=OCIHandleFree (vl_Errhp)

87. Fin de si

88. vl_Status:=OCIHandleFree (vl_Envhp)

89. vl_Status:=OCICleanUp

90. Fin de si

Commentaire sur la méthode projet ‘Ex_Fonction_Factorielle’ :

L’algorithme général de la méthode projet ‘Ex_Fonction_Factorielle’ se décompose en trois étapes :

• définition et création de la fonction PL/SQL ‘Factorielle’ l’instruction CREATE OR REPLACE précédant la définition de la fonction proprement dite, stocke la fonction sur le serveur Oracle. Notez l’utilisation du caractère ‘line feed’ pour séparer les différentes lignes de la fonction PL/SQL ;

• appel dynamique de cette fonction : pour réaliser le test de la fonction, on appelle cette dernière avec le paramètre d’entrée et le paramètre de sortie au sein d’un bloc PL/SQL BEGIN..END. Ce bloc est dit anonyme, car il n’est pas stocké dans la base Oracle et se compile et s’exécute à la volée ;

• suppression de la fonction : ici, on exécute une simple requête SQL statique qui supprime la fonction précédemment stockée.

Le cœur de cette méthode projet est bien la réalisation du ‘Bind’, association des variables 4D et des paramètres de la fonction PL/SQL, avec la commande OCIBindByName. Ce qui est intéressant de noter ici, c’est la valeur BIND_IN ou BIND_OUT du dernier paramètre de cette commande pour indiquer respectivement si la donnée est fournie par la variable 4D (paramètre en entrée) ou récupérée dans la variable 4D (paramètre en sortie).

Implémentation de la procédure PL/SQL ‘LIREINFOS’

Méthode projet ‘Ex_Procedure_LIREINFOS’

1. ` méthode ‘Ex_Procedure_LIREINFOS’

2. ` exécute une procédure PL/SQL ‘LIREINFOS’ qui retourne l'utilisateur couramment connecté

3. ` à la base Oracle ainsi que la date et l'heure du serveur sur lequel tourne la base Oracle

5. C_ENTIER LONG(vl_Envhp;vl_Errhp;vl_Svchp)

6. C_ENTIER LONG(vl_Stmthp_Create;vl_Stmthp_Exec;vl_Stmthp_Del;vl_Bind)

7. C_TEXTE(vt_UserName;vt_Password;vt_HostName;vt_SQL;vt_ErrorMessage)

8. C_ENTIER LONG(vl_Status;vl_ErrorCode)

9. C_ENTIER LONG(vl_Input)

10. C_ENTIER LONG(vl_Output)

11. C_POINTEUR(vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3)

12. C_TEXTE(vt_MyUser;vt_MyDate;vt_MyTime)

14. ` paramètres de connexion (A MODIFIER)

15. vt_UserName:="Scott" `Nom d'utilisateur

16. vt_Password:="Tiger" `Mot de passe

17. vt_HostName:="ORA8QA" `Chaîne de connexion

18. ` données à récupérer via la l'appel de la procédure PL/SQL LIREINFOS

19. vt_MyUser:=""

20. vt_MyDate:=""

21. vt_MyTime:=""

22. ` Allocation du handle environnement

23. vl_Status:=OCIEnvCreate (vl_Envhp;OCI_DEFAULT )

24. Si (vl_Status=OCI_SUCCESS )

25. ` Allocation d'un handle erreur

26. vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Errhp;OCI_HTYPE_ERROR )

27. Si (vl_Status=OCI_SUCCESS )

28. ` Ouverture de la connexion et de la session

29. vl_Status:=OCILogon (vl_Envhp;vl_Errhp;vl_Svchp;vt_UserName;vt_Password;vt_HostName)

30. Si (vl_Status=OCI_SUCCESS )

31. ` Allocation du handle requête. Ce handle nous servira à créer

32. ` et stocker la procédure PL/SQL LIREINFOS sur Oracle

33. vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Create;OCI_HTYPE_STMT )

34. Si (vl_Status=OCI_SUCCESS )

35. ` Définition de la fonction 'LIREINFOS' en PL/SQL

36. vt_SQL:=""

37. vt_SQL:=vt_SQL+"CREATE OR REPLACE PROCEDURE LIREINFOS (vUser OUT VARCHAR2,"+Caractere(ASCII LF )

38. vt_SQL:=vt_SQL+"vDate OUT VARCHAR2, vTime OUT VARCHAR2) IS"+Caractere(ASCII LF )

39. vt_SQL:=vt_SQL+"BEGIN"+Caractere(ASCII LF )

40. vt_SQL:=vt_SQL+"SELECT USER INTO vUser FROM DUAL;"+Caractere(ASCII LF )

41. vt_SQL:=vt_SQL+"SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY') INTO vDate FROM DUAL;"+Caractere(ASCII LF )

42. vt_SQL:=vt_SQL+"SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') INTO vTime FROM DUAL;"+Caractere(ASCII LF )

43. vt_SQL:=vt_SQL+"END;"

44. vl_Status:=OCIStmtPrepare (vl_Stmthp_Create;vl_Errhp;vt_SQL;OCI_DEFAULT )

45. vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Create;vl_errhp;1;0;0;0;OCI_DEFAULT )

46. ` la fonction LIREINFOS est maintenant stockée sur Oracle. On va pouvoir la tester...

47. vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Exec;OCI_HTYPE_STMT )

48. Si (vl_Status=OCI_SUCCESS )

49. vt_SQL:=""

50. vt_SQL:=vt_SQL+"BEGIN"+Caractere(ASCII LF )

51. vt_SQL:=vt_SQL+"LIREINFOS(:MyUser,:MyDate,:MyTime);"+Caractere(ASCII LF )

52. vt_SQL:=vt_SQL+"END;"

53. vl_Status:=OCIStmtPrepare (vl_Stmthp_Exec;vl_Errhp;vt_SQL;OCI_DEFAULT )

54. ` réalisation d'un Bind par nom pour l'appel de la procédure 'LIREINFOS'

55. vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":MyUser";

56. ->vt_MyUser;SQLT_STR ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT ;BIND_OUT )

57. vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":MyDate";

58. ->vt_MyDate;SQLT_STR ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT ;BIND_OUT )

59. vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":MyTime";

60. ->vt_MyTime;SQLT_STR ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT ;BIND_OUT )

61. vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Exec;vl_Errhp;1;0;0;0;OCI_DEFAULT )

62. Si (vl_Status#OCI_SUCCESS )

63. vl_Status:=OCIErrorGet (vl_Errhp;1;vl_ErrorCode;vt_ErrorMessage)

64. ALERTE("Erreur Oracle "+Chaine(vl_ErrorCode)+" : "+vt_ErrorMessage)

65. Fin de si

66. MyData:=""

67. MyData:=MyData+"Utilisateur courant : "+vt_MyUser+Caractere(Retour chariot )

68. MyData:=MyData+"Date serveur : "+vt_MyDate+Caractere(Retour chariot )

69. MyData:=MyData+"Heure serveur : "+vt_MyTime+Caractere(Retour chariot )

70. ALERTE(MyData)

71. ` Allocation du handle requête. Ce handle nous servira à supprimer la procédure

72. ` PL/SQL 'LIREINFOS' stockée sur Oracle

73. vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Del;OCI_HTYPE_STMT )

74. Si (vl_Status=OCI_SUCCESS )

75. vt_SQL:=""

76. vt_SQL:=vt_SQL+"DROP PROCEDURE LIREINFOS"

77. vl_Status:=OCIStmtPrepare (vl_Stmthp_Del;vl_Errhp;vt_SQL;OCI_DEFAULT )

78. vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Del;vl_errhp;1;0;0;0;OCI_DEFAULT )

79. ` libération du handle requête qui a servi à supprimer la procédure 'LIREINFOS'

80. vl_Status:=OCIHandleFree (vl_Stmthp_Del)

81. Fin de si

82. ` libération du handle requête qui a servi à tester la procédure 'LIREINFOS'

83. vl_Status:=OCIHandleFree (vl_Stmthp_Exec)

84. Fin de si

85. ` libération du handle requête qui a servi à créer la procédure 'LIREINFOS'

86. vl_Status:=OCIHandleFree (vl_Stmthp_Create)

87. Fin de si

88. vl_Status:=OCILogoff (vl_Svchp;vl_Errhp)

89. Fin de si

90. vl_Status:=OCIHandleFree (vl_Errhp)

91. Fin de si

92. vl_Status:=OCIHandleFree (vl_Envhp)

93. vl_Status:=OCICleanUp

94. Fin de si

Commentaire sur la méthode projet ‘Ex_Procedure_LIREINFOS’ :

L’algorithme général de la méthode projet ‘Ex_Procedure_LIREINFOS’ est identique à celui de ‘Ex_Fonction_Factorielle’ précédemment décrit. L’intérêt principal de cette méthode est uniquement de montrer l’utilisation d’une procédure PL/SQL, après celle d’une fonction.

Ici, toutes les variables 4D sont passées comme paramètres de sortie (BIND_OUT), car on récupère des informations (utilisateur, date et heure) depuis la base Oracle vers 4D. On est conscient qu’il existe sûrement d’autres manières plus judicieuses de récupérer ces informations, en utilisant par exemple un simple appel SQL statique, au lieu de passer par une procédure ; mais rappelons que le but de cet exemple est purement didactique.

III - Utilisation de la base de test

La base de test, construite en 4D 2004, contient uniquement les deux méthodes projet présentées précédemment ‘Ex_Fonction_Factorielle’ et ‘Ex_Procedure_LIREINFOS’. Ces deux méthodes peuvent s’utiliser depuis le mode structure. Avant d’utiliser ces deux méthodes, il faut, dans chacune d’elles, modifier les valeurs des paramètres de connexion, en en-tête des méthodes, juste après la déclaration des variables. Bien évidemment, il faudra y mettre vos propres valeurs, afin d’utiliser la base de test. Les paramètres de connexion se présentent ainsi :

1. `paramètres de connexion (A MODIFIER)

2. vt_UserName:="Scott" `Nom d'utilisateur

3. vt_Password:="Tiger" `Mot de passe

4. vt_HostName:="ORA8QA" `Chaîne de connexion

IV - Conclusion

Cette note vous a montré l’utilisation du langage PL/SQL au sein d’une application 4D For OCI, au travers des exemples d’une procédure et d’une fonction PL/SQL. PL/SQL étant un langage puissant (SQL dynamique, programmation orientée objet…), il apporte une grande souplesse et beaucoup de possibilités au sein d’une application 4D For OCI.

Nous souhaitons que cette note vous ait donné des idées pour tirer profit de cette association PL/SQL et 4D For OCI.

V - Base exemple

Téléchargez la base exemple.