Etape 1 : Modélisation sous Windesign MEA / MLR Etape 2 : Création du compte applicatif Oracle Création du compte spécifique GRANT Connect , Resource TO mauduit_gescom IDENTIFIED BY mauduit ; ALTER USER mauduit_gescom DEFAULT TABLESPACE BD50_DATA ; Etape 3 : Génération du script SQL /// .... (cf le fichier SQL) Etape 4 : transfert de données SELECT TABLE_NAME , TABLESPACE_NAME FROM TABS ; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ CATEGORIE BD50_DATA COMMANDE BD50_DATA CLIENT BD50_DATA PRODUIT BD50_DATA LIGNE_COMMANDE BD50_DATA 5 rows selected SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'GESCOM'; TABLE_NAME ------------------------------ CATEGORIE COMMANDE CLIENT PRODUIT LIGNE_COMMANDE Peuplement (l'ordre des tables est important du fait des contraintes imposées au système) INSERT INTO CATEGORIE SELECT * FROM GESCOM.CATEGORIE ; Meme chose sur PRODUIT, CLIENT, COMMANDE, LIGNE_COMMANDE COMMIT; -> COMMIT succeeded. Etape 5 : PRocédures stockées /------+ | | | A | | | +------/ Augmente le prix des articles, de 10 % si prix supérieur à 500 de 20 % sinon Vprodref indique la référence du produit (clé primaire de la table PRODUIT) px : contient dans un premier temps le prix de l'article, puis son prix après augmentation Instructions d'affectation : px := px * 1.1; et px := px * 1.2; Structures itératives et conditionnelles COMMIT : permet de réaliser les changements Obligatoire ? CREATE OR REPLACE PROCEDURE AugmenterPrix(vprodref CHAR) IS px NUMBER(6,2); BEGIN SELECT PROD_PRIX INTO px FROM PRODUIT WHERE PROD_REF = vprodref; IF px > 500.00 THEN px := px * 1.1; ELSE px := px * 1.2; END IF; UPDATE PRODUIT SET PROD_PRIX = px WHERE PROD_REF = vprodref; COMMIT; END AugmenterPrix; / INSERT INTO PRODUIT VALUES ('TV70CO', 'Télévision Couleur 70 cm', 1000); EXECUTE AugmenterPrix('TV70CO'); SELECT * FROM PRODUIT WHERE PROD_REF = 'TV70CO'; PROD_REF PROD_DES PROD_PRIX -------- ------------------------------------------------------------ ---------------------- TV70CO Télévision Couleur 70 cm 1100 /------+ | | | B | | | +------/ CREATE OR REPLACE PROCEDURE StatProd (prodref IN CHAR, qte OUT INT, nbcli OUT INT) IS qte INT, nbcli INT BEGIN SELECT SUM(PROD_QTE) INTO qte FROM LIGNE_COMMANDE WHERE PROD_REF = prodref; SELECT COUNT(DISTINCT CLI_NUM) INTO nbcli FROM LIGNE_COMMANDE , COMMANDE WHERE LIGNE_COMMANDE.cDE_NUM = COMMANDE.CDE_NUM AND LIGNE_COMMANDe.PROD_REF = prodref; END StatProd; / INSERT INTO PRODUIT VALUES ('TVCO84', 'Télévision couleur 84 cm', 1300); VARIABLE VQTE NUMBER; VARIABLE VNBCLI NUMBER; EXECUTE StatPRod('TVCO84', :VQTE, :VNBCLI); (ne fonctionne pas) /------+ | | | C | | | +------/ Création d'une procédure stockée avec SQL*Developper CREATE OR REPLACE PROCEDURE MAUDUIT_GESCOM.GESCOM_AJOUTERPRODUIT (v_prodref produit.prod_ref%TYPE ,v_proddes produit.prod_des%TYPE ,v_prodprix produit.prod_prix%TYPE) AS BEGIN INSERT INTO produit (PROD_REF, PROD_DES, PROD_PRIX) VALUES (v_prodref, v_proddes, v_prodprix); COMMIT; END; EXECUTE GESCOM_AJOUTERPRODUIT('TVCO1K', 'Télévision couleur 110 cm', 140.50); Les contraintes sur l'unicité du système interdisent l'ajout d'un meme produit deux fois de suite => ca foire /------+ | | | D | | | +------/ CREATE OR REPLACE PROCEDURE MAJ_PRODUIT (v_prodref IN produit.prod_ref%TYPE, v_proddes IN produit.prod_des%TYPE) IS BEGIN UPDATE produit SET prod_des = v_proddes WHERE prod_ref = v_prodref; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202, 'Pas de produit mis à jour.'); ELSE COMMIT; END IF; END maj_produit; Ok; pas de soucis rencontré lors de l'éxecution. /------+ | | | E | | | +------/ Utilisation de l'outil "Oracle Jdevelopper 10G" PROCEDURE supp_produit (v_prodref IN produit.prod_ref%TYPE) IS BEGIN DELETE FROM produit WHERE prod_ref = v_prodref; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202, 'Pas de produit supprimé.'); ELSE COMMIT; END IF; END supp_produit; La suppression du produit 'TVCO84' ne fonctionne pas. Existence dans d'autres tables ? /------+ | | | F | | | +------/ FUNCTION lib_produit (v_prodref IN produit.prod_ref%TYPE) RETURN VARCHAR2 IS lib_prod varchar2(30); BEGIN SELECT PROD_DES INTO lib_prod FROM PRODUIT WHERE PROD_REF = v_prodref; RETURN(lib_prod); END; SQL> variable v_lib varchar2(30); SQL> begin 2 :v_lib := lib_produit('TVCO84'); 3 end; 4 / SQL> print v_lib; V_LIB -------------------------------- Telévision couleur 84 cm On constate effectivement un message d'erreur en cas de non existence dans la table. ajout de EXCEPTION WHEN no_data_found then return('Produit inconnu'); apres le RETURN Apres exécution sur un produit inconnu, on remarque que l'exception est bien prise en compte. SQL> begin 2 :v_lib := lib_produit('TVC100'); 3 end; 4 / Procédure PL/SQL terminée avec succès. SQL> print v_lib; V_LIB -------------------------------- Produit inconnu Affichage des variables déclarées : SQL> var; SQL> var; variable v_lib type de données VARCHAR2(30) /------+ | | | G | | | +------/ FUNCTION lib_produit (v_prodref IN produit.prod_ref%TYPE) RETURN VARCHAR2 IS lib_prod varchar2(30); BEGIN SELECT PROD_DES INTO lib_prod FROM PRODUIT WHERE PROD_REF = v_prodref; RETURN(lib_prod); EXCEPTION WHEN no_data_found then return('Produit inconnu'); END lib_produit; CREATE OR REPLACE FUNCTION valider_produit (v_prodref IN produit.prod_ref%TYPE) RETURN number IS res number; BEGIN SELECT 1 INTO res from produit WHERE prod_ref = v_prodref; IF SQL%FOUND THEN return 1; END IF; Exception when no_data_found then return 0; END; TVCO84 : retour 1, le produit existe Retour 0 sur un produit inexistant. On peut modifier afin de retourner un booléen, plus approprié ici CREATE OR REPLACE FUNCTION valider_produit (v_prodref IN produit.prod_ref%TYPE) RETURN boolean IS res number; BEGIN SELECT 1 INTO res from produit WHERE prod_ref = v_prodref; IF SQL%FOUND THEN return true; END IF; Exception when no_data_found then return false; END; /------+ | | | H | | | +------/ CREATE OR REPLACE PROCEDURE inserer_ligne_commande (v_numcde IN LIGNE_COMMANDE.cde_num%TYPE default 9999, v_prodref IN LIGNE_COMMANDE.prod_ref%TYPE default 'TVCO84', v_qte IN LIGNE_COMMANDE.prod_qte%TYPE default 0) IS BEGIN IF (valider_produit(v_prodref)) THEN INSERT INTO LIGNE_COMMANDE VALUES (v_numcde, v_prodref, v_qte); COMMIT; ELSE DBMS_OUTPUT.PU_LINE('Référence de produit incorrecte.'); END IF; END inserer_ligneccde; Le renvoi de type booléen ne semblait pas fonctionner dans SQL Developper L'ajout de la procédure d'insertion fonctionne toutefois Version 2 : CREATE OR REPLACE PROCEDURE inserer_ligne_commande (v_numcde IN LIGNE_CDE.numcde%TYPE default 9999, v_numlig IN LIGNE_CDE.numlig%TYPE default 1, v_prodref IN LIGNE_CDE.prodref%TYPE default 'TVCO84', v_prix IN LIGNE_CDE.prix%TYPE default 0, v_qte IN LIGNE_CDE.quantite%TYPE default 0) IS BEGIN IF valider_produit(v_prodref) THEN INSERT INTO LIGNE_CDE(numcde, numlig, prodref, prix, quantite) VALUES (v_numcde, v_numlig, v_prodrefn v_prix, v_qte); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE('Référence de produit incorrecte'); END IF END inserer_lignecommande; / /------+ | | | I | | | +------/ Création d'un package /------+ | | | J | | | +------/ Génération sous Win'Design du script triggers (cf MLR_T.SQL) /------+ | | | J | | | +------/ Optimisation -> redondance d'information dans certaines tables (dénormalisation) -> Découpage des tables (historisation des anciennes transactions dans la table des commandes par exemple)