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’
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
`Méthode Ex_Fonction_Factorielle
`exécute une fonction PL/SQL 'Factorielle' qui retourne la factorielle d'un nombre
C_ENTIER LONG(vl_Envhp;vl_Errhp;vl_Svchp)
C_ENTIER LONG(vl_Stmthp_Create;vl_Stmthp_Exec;vl_Stmthp_Del;vl_Bind)
C_TEXTE(vt_UserName;vt_Password;vt_HostName;vt_SQL;vt_ErrorMessage)
C_ENTIER LONG(vl_Status;vl_ErrorCode)
C_ENTIER LONG(vl_Input)
C_REEL(vr_Output)
C_POINTEUR(vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3)
`paramètres de connexion (A MODIFIER)
vt_UserName:="Scott" `Nom d'utilisateur
vt_Password:="Tiger" `Mot de passe
vt_HostName:="ORA8QA" `Chaîne de connexion
vl_Input:=0 `nombre dont on voudra connaître la factorielle
vr_Output:=0 `résultat du calcul de la factorielle
`Allocation du handle environnement
vl_Status:=OCIEnvCreate (vl_Envhp;OCI_DEFAULT )
Si (vl_Status=OCI_SUCCESS )
`Allocation d'un handle erreur
vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Errhp;OCI_HTYPE_ERROR )
Si (vl_Status=OCI_SUCCESS )
` Ouverture de la connexion et de la session
vl_Status:=OCILogon (vl_Envhp;vl_Errhp;vl_Svchp;vt_UserName;vt_Password;vt_HostName)
Si (vl_Status=OCI_SUCCESS )
` Allocation du handle requête. Ce handle nous servira à créer / stocker
` la fonction PL/SQL 'Factorielle' sur Oracle
vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Create;OCI_HTYPE_STMT )
Si (vl_Status=OCI_SUCCESS )
` Définition de la fonction 'Factorielle' en PL/SQL
vt_SQL:=""
vt_SQL:=vt_SQL+"CREATE OR REPLACE FUNCTION Factorielle (facto IN NUMBER)"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"RETURN NUMBER IS"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"BEGIN"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"IF facto = 0"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"THEN RETURN 1;"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"ELSE"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"RETURN facto*Factorielle(facto-1);"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"END IF;"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"END;"
vl_Status:=OCIStmtPrepare (vl_Stmthp_Create;vl_Errhp;vt_SQL;OCI_DEFAULT )
vl_Status:=OCIStmtExecute(vl_Svchp;vl_Stmthp_Create;vl_errhp;1;0;0;0;OCI_DEFAULT )
` la fonction Factorielle est maintenant stockée sur Oracle. On va pouvoir la tester...
` Allocation du handle requête. Ce handle nous servira à tester la fonction
` stockée sur Oracle PL/SQL 'Factorielle'
vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Exec;OCI_HTYPE_STMT )
Si (vl_Status=OCI_SUCCESS )
t_SQL:=""
vt_SQL:=vt_SQL+"BEGIN"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+":DataOutput:=Factorielle(:DataInput);"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"END;"
vl_Input:=Num(Demander("Calculez la factorielle de : "))
vl_Status:=OCIStmtPrepare (vl_Stmthp_Exec;vl_Errhp;vt_SQL;OCI_DEFAULT )
` réalisation d'un Bind par nom pour l'appel de la fonction 'Factorielle'
vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":DataOutput";
->vr_Output;SQLT_FLT ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT;BIND_OUT )
vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":DataInput";
->vl_Input;SQLT_INT ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT ;BIND_IN)
vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Exec;vl_Errhp;1;0;0;0;OCI_DEFAULT )
Si (vl_Status#OCI_SUCCESS )
vl_Status:=OCIErrorGet (vl_Errhp;1;vl_ErrorCode;vt_ErrorMessage)
ALERTE("Erreur Oracle "+Chaine(vl_ErrorCode)+" : "+vt_ErrorMessage)
Fin de si
ALERTE(Chaine(vl_Input)+"! = "+Chaine(vr_Output))
` Allocation du handle requête. Ce handle nous servira à supprimer
` la fonction PL/SQL 'Factorielle' stockée sur Oracle
vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Del;OCI_HTYPE_STMT )
Si (vl_Status=OCI_SUCCESS )
vt_SQL:=""
vt_SQL:=vt_SQL+"DROP FUNCTION FACTORIELLE"
vl_Status:=OCIStmtPrepare (vl_Stmthp_Del;vl_Errhp;vt_SQL;OCI_DEFAULT )
vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Del;vl_errhp;1;0;0;0;OCI_DEFAULT )
` libération du handle requête qui a servi à supprimer la fonction Factorielle
vl_Status:=OCIHandleFree (vl_Stmthp_Del)
Fin de si
` libération du handle requête qui a servi à tester la fonction Factorielle
vl_Status:=OCIHandleFree (vl_Stmthp_Exec)
Fin de si
` libération du handle requête qui a servi à créer la fonction 'Factorielle'
vl_Status:=OCIHandleFree (vl_Stmthp_Create)
Fin de si
vl_Status:=OCILogoff (vl_Svchp;vl_Errhp)
Fin de si
vl_Status:=OCIHandleFree (vl_Errhp)
Fin de si
vl_Status:=OCIHandleFree (vl_Envhp)
vl_Status:=OCICleanUp
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’
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
` méthode ‘Ex_Procedure_LIREINFOS’
` exécute une procédure PL/SQL ‘LIREINFOS’ qui retourne l'utilisateur couramment connecté
` à la base Oracle ainsi que la date et l'heure du serveur sur lequel tourne la base Oracle
C_ENTIER LONG(vl_Envhp;vl_Errhp;vl_Svchp)
C_ENTIER LONG(vl_Stmthp_Create;vl_Stmthp_Exec;vl_Stmthp_Del;vl_Bind)
C_TEXTE(vt_UserName;vt_Password;vt_HostName;vt_SQL;vt_ErrorMessage)
C_ENTIER LONG(vl_Status;vl_ErrorCode)
C_ENTIER LONG(vl_Input)
C_ENTIER LONG(vl_Output)
C_POINTEUR(vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3)
C_TEXTE(vt_MyUser;vt_MyDate;vt_MyTime)
` paramètres de connexion (A MODIFIER)
vt_UserName:="Scott" `Nom d'utilisateur
vt_Password:="Tiger" `Mot de passe
vt_HostName:="ORA8QA" `Chaîne de connexion
` données à récupérer via la l'appel de la procédure PL/SQL LIREINFOS
vt_MyUser:=""
vt_MyDate:=""
vt_MyTime:=""
` Allocation du handle environnement
vl_Status:=OCIEnvCreate (vl_Envhp;OCI_DEFAULT )
Si (vl_Status=OCI_SUCCESS )
` Allocation d'un handle erreur
vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Errhp;OCI_HTYPE_ERROR )
Si (vl_Status=OCI_SUCCESS )
` Ouverture de la connexion et de la session
vl_Status:=OCILogon (vl_Envhp;vl_Errhp;vl_Svchp;vt_UserName;vt_Password;vt_HostName)
Si (vl_Status=OCI_SUCCESS )
` Allocation du handle requête. Ce handle nous servira à créer
` et stocker la procédure PL/SQL LIREINFOS sur Oracle
vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Create;OCI_HTYPE_STMT )
Si (vl_Status=OCI_SUCCESS )
` Définition de la fonction 'LIREINFOS' en PL/SQL
vt_SQL:=""
vt_SQL:=vt_SQL+"CREATE OR REPLACE PROCEDURE LIREINFOS (vUser OUT VARCHAR2,"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"vDate OUT VARCHAR2, vTime OUT VARCHAR2) IS"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"BEGIN"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"SELECT USER INTO vUser FROM DUAL;"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY') INTO vDate FROM DUAL;"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') INTO vTime FROM DUAL;"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"END;"
vl_Status:=OCIStmtPrepare (vl_Stmthp_Create;vl_Errhp;vt_SQL;OCI_DEFAULT )
vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Create;vl_errhp;1;0;0;0;OCI_DEFAULT )
` la fonction LIREINFOS est maintenant stockée sur Oracle. On va pouvoir la tester...
vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Exec;OCI_HTYPE_STMT )
Si (vl_Status=OCI_SUCCESS )
vt_SQL:=""
vt_SQL:=vt_SQL+"BEGIN"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"LIREINFOS(:MyUser,:MyDate,:MyTime);"+Caractere(ASCII LF )
vt_SQL:=vt_SQL+"END;"
vl_Status:=OCIStmtPrepare (vl_Stmthp_Exec;vl_Errhp;vt_SQL;OCI_DEFAULT )
` réalisation d'un Bind par nom pour l'appel de la procédure 'LIREINFOS'
vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":MyUser";
->vt_MyUser;SQLT_STR ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT ;BIND_OUT )
vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":MyDate";
->vt_MyDate;SQLT_STR ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT ;BIND_OUT )
vl_Status:=OCIBindByName (vl_Stmthp_Exec;vl_Bind;vl_Errhp;":MyTime";
->vt_MyTime;SQLT_STR ;vp_Null_Ind1;vp_Null_Ind2;vp_Null_Ind3;OCI_DEFAULT ;BIND_OUT )
vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Exec;vl_Errhp;1;0;0;0;OCI_DEFAULT )
Si (vl_Status#OCI_SUCCESS )
vl_Status:=OCIErrorGet (vl_Errhp;1;vl_ErrorCode;vt_ErrorMessage)
ALERTE("Erreur Oracle "+Chaine(vl_ErrorCode)+" : "+vt_ErrorMessage)
Fin de si
MyData:=""
MyData:=MyData+"Utilisateur courant : "+vt_MyUser+Caractere(Retour chariot )
MyData:=MyData+"Date serveur : "+vt_MyDate+Caractere(Retour chariot )
MyData:=MyData+"Heure serveur : "+vt_MyTime+Caractere(Retour chariot )
ALERTE(MyData)
` Allocation du handle requête. Ce handle nous servira à supprimer la procédure
` PL/SQL 'LIREINFOS' stockée sur Oracle
vl_Status:=OCIHandleAlloc (vl_Envhp;vl_Stmthp_Del;OCI_HTYPE_STMT )
Si (vl_Status=OCI_SUCCESS )
vt_SQL:=""
vt_SQL:=vt_SQL+"DROP PROCEDURE LIREINFOS"
vl_Status:=OCIStmtPrepare (vl_Stmthp_Del;vl_Errhp;vt_SQL;OCI_DEFAULT )
vl_Status:=OCIStmtExecute (vl_Svchp;vl_Stmthp_Del;vl_errhp;1;0;0;0;OCI_DEFAULT )
` libération du handle requête qui a servi à supprimer la procédure 'LIREINFOS'
vl_Status:=OCIHandleFree (vl_Stmthp_Del)
Fin de si
` libération du handle requête qui a servi à tester la procédure 'LIREINFOS'
vl_Status:=OCIHandleFree (vl_Stmthp_Exec)
Fin de si
` libération du handle requête qui a servi à créer la procédure 'LIREINFOS'
vl_Status:=OCIHandleFree (vl_Stmthp_Create)
Fin de si
vl_Status:=OCILogoff (vl_Svchp;vl_Errhp)
Fin de si
vl_Status:=OCIHandleFree (vl_Errhp)
Fin de si
vl_Status:=OCIHandleFree (vl_Envhp)
vl_Status:=OCICleanUp
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 :
2.
3.
4.
`paramètres de connexion (A MODIFIER)
vt_UserName:="Scott" `Nom d'utilisateur
vt_Password:="Tiger" `Mot de passe
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.