CHAPITRE 1 LES PROCEDURES STOCKEES ORACLE Chapitre 1 LES PROCEDURES STOCKEES ORACLE ............................... 1 1.1 L'appel de procédure ou de fonction en utilisant les RPC (Remote Procedure call).................................................................................................... 2 1.1.1 Principes et démarche........................................................................ 2 1.1.1.1 Créer un objet de type transaction.................................................. 2 1.1.1.2 Déclaration de la "Local External Function" ................................. 3 1.1.1.3 Modification des propriétés de l’application ................................. 4 1.1.1.4 Appel .............................................................................................. 5 1.1.2 Travailler avec une procédure stockée (Paramètre IN)..................... 6 1.1.2.1 La procédure Oracle....................................................................... 6 1.1.2.2 L'appel ............................................................................................ 6 1.1.3 Travailler avec une procédure stockée (Paramètre OUT)................. 7 1.1.3.1 La procédure Oracle....................................................................... 8 1.1.3.2 La déclaration dans le UserObject ................................................. 8 1.1.3.3 L'appel ............................................................................................ 8 1.1.4 Appel d'une fonction stockée (Mode RPC) : .................................. 10 1.1.4.1 La fonction Oracle........................................................................ 10 1.1.4.2 La déclaration dans le UserObject ............................................... 10 1.1.4.3 La script PowerBuilder ................................................................ 10 1.2 L'appel de procédure en utilisant les instructions DECLARE et EXECUTE ........................................................................................................ 11 1.2.1 La Procédure ................................................................................... 11 1.2.2 L'appel ............................................................................................. 12 1.3 Les datawindows et les procédures stockées ........................................... 13 1.3.1 Le DSN............................................................................................ 13 1.3.2 La procédure stockée....................................................................... 14 1.3.3 Le DWO : Construction .................................................................. 15 1.3.4 Le script de la DataWindow............................................................ 16
PowerBuilder - Procédures stockées
1.1 L'APPEL DE PROCEDURE OU DE FONCTION EN UTILISANT LES RPC (REMOTE PROCEDURE CALL) 1.1.1 Principes et démarche Pour utiliser une procédure stockée (ou une fonction stockée) Oracle il faut : • Créer un userObject de type transaction • Déclarer au sein de ce UserObject une "Local External Function" • Préciser que SQLCA est du type de ce UserObject au niveau des propriétés de l’application • Appeler la procédure
1.1.1.1
Créer un objet de type transaction
Dans la PowerBar cliquez sur le bouton New Allez dans l'onglet PbObject et sélectionnez StandardClass
Cliquez sur OK et sélectionnez le type Transaction
© Pascal Buguet
Imprimé le 19 août 2004
Page 2
PowerBuilder - Procédures stockées Vous arrivez sur la fenêtre de votre Uo dans une feuille de script.
1.1.1.2
Déclaration de la "Local External Function"
Sélectionnez l'onglet Declare Instance Variables. Allez dans la première liste et sélectionnez Declare. Allez ensuite dans la deuxième liste et sélectionnez Local External Functions.
Cliquez droit ou dans le menu Edit puis sélectionnez Paste Special / SQL / Remote Stored Procedure(s)…
Sélectionnez la procédure qui vous intéresse (Insert_Ville_in) et cliquez sur OK
PowerBuilder colle ce texte dans la feuille de déclaration : subroutine INSERT_VILLE_IN(string A_CP,string A_NOM_VILLE,string A_ID_PAYS) RPCFUNC
© Pascal Buguet
Imprimé le 19 août 2004
Page 3
PowerBuilder - Procédures stockées Avec - Subroutine : procédure - Nom de la procédure - Nom(s) de paramètre et type - RPCFUNC : procédure distante Vous aurez la première ligne dans la feuille de déclaration.
Vous enregistrez votre UserObject sous le nom : Uo_Trans_Oracle.
1.1.1.3
Modification des propriétés de l’application
Sélectionnez l'objet Application
Allez dans les propriétés et cliquez sur Additional Properties
© Pascal Buguet
Imprimé le 19 août 2004
Page 4
PowerBuilder - Procédures stockées
Dans l'onglet Variables Types remplacez Transaction par uo_trans_oracle .
Enregistrez l'application.
1.1.1.4
Appel
Syntaxe
ObjectTransactionnel.NomDeProcédure ( Paramètre1 , Paramètre2 ,….)
Exemple
SQLCA.InsDept ( sle_dept.text , sle_nomdept.text)
Compléments : Ne mettez pas les : devant les paramètres. Si la procédure stockée ne comporte que des paramètres IN faire comme précédemment En revanche si la procédure stockée comporte des paramètres OUT il faudra : initialiser les paramètres avec des valeurs adéquates lors de l’appel faire précéder de REF chaque nom de paramètre OUT
© Pascal Buguet
Imprimé le 19 août 2004
Page 5
PowerBuilder - Procédures stockées
1.1.2 Travailler avec une procédure stockée (Paramètre IN) • L'écran
1.1.2.1
La procédure Oracle
Create or Replace procedure insert_ville_in ( a_cp in varchar , a_nom_ville in varchar , a_id_pays in varchar ) is begin insert into villes( id_ville, cp , nom_ville, id_pays) values (seq_villes.nextval ,a_cp , a_nom_ville , a_id_pays ) ; commit ; end; /
1.1.2.2
L'appel
// Clicked de cb_insert_ville_in string ls_cp, ls_nom_ville, ls_id_pays ls_cp = trim(sle_cp_in.text) ls_nom_ville = trim(sle_nom_ville_in.text) ls_id_pays = trim(sle_id_pays_in.text) sqlca.insert_ville_in(ls_cp, ls_nom_ville, ls_id_pays) ;
© Pascal Buguet
Imprimé le 19 août 2004
Page 6
PowerBuilder - Procédures stockées
1.1.3 Travailler avec une procédure stockée (Paramètre OUT) • Les écrans Quand ça marche
Quand la clé étrangère est introuvable
Quand un champ obligatoire est vide
© Pascal Buguet
Imprimé le 19 août 2004
Page 7
PowerBuilder - Procédures stockées
1.1.3.1
La procédure Oracle
Create or Replace procedure insert_ville_out ( a_cp in varchar , a_nom_ville in varchar , a_id_pays in varchar , a_erreur out number , a_erreur_texte out varchar ) is vide exception ; begin if a_cp is null then raise Vide ; end if ; if a_nom_ville is null then raise Vide ; end if ; if a_id_pays is null then raise Vide ; end if ; insert into villes( id_ville, cp , nom_ville, id_pays) values (seq_villes.nextval ,a_cp , a_nom_ville , a_id_pays ) ; commit; Exception when Vide then a_erreur := 0 ; a_erreur_texte := 'vide' ; when others then a_erreur := SQLCODE; a_erreur_texte := SUBSTR(SQLERRM,1,100); end; /
1.1.3.2
La déclaration dans le UserObject
Subroutine INSERT_VILLE_OUT(string A_CP,string A_NOM_VILLE,string A_ID_PAYS,ref double A_ERREUR,ref string A_ERREUR_TEXTE) RPCFUNC
1.1.3.3
L'appel
// Clicked de cb_insert_ville_out double ld_code_erreur string ls_texte_erreur sle_code_erreur.text = "" mle_texte_erreur.text = "" ld_code_erreur = 0 // Local Double ls_texte_erreur = space(100)
© Pascal Buguet
Imprimé le 19 août 2004
Page 8
PowerBuilder - Procédures stockées sqlca.insert_ville_out(sle_cp_out.text, sle_nom_ville_out.text, sle_id_pays_out.text, ref ld_code_erreur, ref ls_texte_erreur) ; sle_code_erreur.text = string(ld_code_erreur) mle_texte_erreur.text = ls_texte_erreur Remarque : ne pas oubliez d'initialiser les variables qui servent de paramètres OUT.
© Pascal Buguet
Imprimé le 19 août 2004
Page 9
PowerBuilder - Procédures stockées
1.1.4 Appel d'une fonction stockée (Mode RPC) : • Ecran
1.1.4.1
La fonction Oracle
Create or Replace Function fct_select_ville ( a_id_ville in number ) return string is v_nom_ville varchar2 (50); begin select nom_ville into v_nom_ville from villes where id_ville = a_id_ville ; return v_nom_ville ; end ; /
1.1.4.2
La déclaration dans le UserObject
Function string FCT_SELECT_VILLE(double A_ID_VILLE) RPCFUNC
1.1.4.3
La script PowerBuilder
// Clicked de cb_fct_select_ville double ld_id_ville string ls_nom_ville ld_id_ville = double(sle_id_ville.text) ls_nom_ville = sqlca.fct_select_ville(ld_id_ville) ; sle_nom_ville.text = trim(ls_nom_ville) Commentaires : Le mode d'appel est similaire à celui de l'appel des procédures que les paramètres soient IN ou OUT.
© Pascal Buguet
Imprimé le 19 août 2004
Page 10
PowerBuilder - Procédures stockées
1.2 L'APPEL DE PROCEDURE EN UTILISANT LES INSTRUCTIONS DECLARE ET EXECUTE • Objectif et contraintes Exécuter une procédure sans passer par un UserObject. Cette méthode n'autorise que les procédures stockées avec des paramètres IN. • L'écran
1.2.1 La Procédure Reprenons la procédure Stockée Insert_ville_IN ne comportant que des paramètres IN Create or Replace procedure insert_ville_in ( a_cp in varchar , a_nom_ville in varchar , a_id_pays in number ) is begin insert into villes( id_ville, cp , nom_ville, id_pays) values (seq_villes.nextval ,a_cp , a_nom_ville , a_id_pays ) ; commit ; end; /
© Pascal Buguet
Imprimé le 19 août 2004
Page 11
PowerBuilder - Procédures stockées
1.2.2 L'appel • Syntaxe DECLARE AliasProcédure FOR NomDeProcedure :paramètre1, :paramètre2 USING ObjetTransactionnel ;
EXECUTE AliasProcédure ;
• Exemple // Clicked de cb_insert_ville_in string ls_cp, ls_nom_ville, ls_id_pays ls_cp = trim(sle_cp_in.text) ls_nom_ville = trim(sle_nom_ville_in.text) ls_id_pays = trim(sle_id_pays_in.text) Declare p_ins_ville procedure for insert_ville_in :ls_cp, :ls_nom_ville, :ls_id_pays using sqlca ; if sqlca.sqlcode 0 then messagebox("Code Erreur Declare : " + string(sqlca.SqlDBCode), sqlca.sqlErrText) return end if Execute p_ins_ville ;
© Pascal Buguet
Imprimé le 19 août 2004
Page 12
PowerBuilder - Procédures stockées
1.3 LES DATAWINDOWS ET LES PROCEDURES STOCKEES Une DataWindow peut être basée sur une procédure stockée
1.3.1 L'interface
1.3.2 Le DSN
Dans le DSN vous devez préciser que les procédures stockées peuvent renvoyer un ou plusieurs résultats. C'est la case à cocher Procedure returns Results.
© Pascal Buguet
Imprimé le 19 août 2004
Page 13
PowerBuilder - Procédures stockées
1.3.3 La procédure stockée • Rappel de la syntaxe CREATE OR REPLACE PACKAGE nom_du_package IS TYPE nouveau_type IS REF CURSOR RETURN villes%ROWTYPE ; PROCEDURE nom_de_la_procedure(nom_argument IN OUT nouveau_type) ; END ; / CREATE OR REPLACE PROCEDURE nom_du_package_ nom_de_la_procedure (nom_argument IN OUT nom_du_package.nouveau_type) AS BEGIN OPEN nom_argument FOR SELECT * FROM villes ; END; /
Notez le nom de la procédure : package_procédure • Le script REM --CREATE OR REPLACE PACKAGE pack_psrs1 IS TYPE rs_villes IS REF CURSOR RETURN villes%ROWTYPE ; PROCEDURE p_villes1(rc_villes IN OUT rs_villes) ; END ; / REM --CREATE OR REPLACE PROCEDURE pack_psrs1_p_villes1(rc_villes IN OUT pack_psrs1.rs_villes) AS BEGIN OPEN rc_villes FOR SELECT * FROM villes ; END; /
© Pascal Buguet
Imprimé le 19 août 2004
Page 14
PowerBuilder - Procédures stockées
1.3.4 Le DWO : Construction Vous sélectionnez le style de présentation
Vous choisissez comme source Stored Procedure.
Vous sélectionnez la procédure stockée (Manual ResultSet est facultatif)
© Pascal Buguet
Imprimé le 19 août 2004
Page 15
PowerBuilder - Procédures stockées
Vous spécifiez ( si vous avez coché Manual ResultSet)
etc
1.3.5 Le script de la DataWindow // --- Script du constructor de la DW this.settransobject(sqlca) this.retrieve()
© Pascal Buguet
Imprimé le 19 août 2004
Page 16
PowerBuilder - Procédures stockées
1.3.6 La mise à jour Par défaut les DWO basés sur les procédures stockées renvoyant un ResultSet ne permettent pas de faire de mises à jour. Il faut pour cela passer par des procédures stockées spécialisées
1.3.6.1 La mise à jour par l'intermédiaire d'autres procédures stockées de MAJ Vous créez – sous Oracle trois procédures stockées correspondant aux trois actions de MAJ sur la table. En voici les codes : REM --- Insert_ville de PB8 create or replace procedure insert_ville_in ( a_cp in varchar , a_nom_ville in varchar , a_id_pays in varchar ) is begin insert into villes( id_ville, cp , nom_ville, id_pays) values (seq_villes.nextval ,a_cp , a_nom_ville , a_id_pays ) ; commit ; end; / REM --- delete_ville de PB8 create or replace procedure delete_ville ( a_cp in varchar) is begin delete from villes where cp = a_cp ; commit ; end ; / REM --- Update_ville de PB8 create or replace procedure update_ville_in ( a_id_ville in number, a_cp in varchar , a_nom_ville in varchar , a_id_pays in varchar ) is begin update villes set id_ville = a_id_ville, cp = a_cp , nom_ville = a_nom_ville, id_pays = a_id_pays where id_ville = a_id_ville ; commit ; end; /
© Pascal Buguet
Imprimé le 19 août 2004
Page 17
PowerBuilder - Procédures stockées
Vous allez ensuite dans le menu Rows du DWO et vous cliquez sur l'item Stored Procedure Update
vous sélectionnez pour chaque action la procédure correspondante.
© Pascal Buguet
Imprimé le 19 août 2004
Page 18
PowerBuilder - Procédures stockées
© Pascal Buguet
Imprimé le 19 août 2004
Page 19
