1 / 32

Packages fournis par Oracle

Packages fournis par Oracle. Objectifs. A la fin de ce chapitre, vous pourrez : créer des instructions SQL dynamiques en utilisant DBMS_SQL et EXECUTE IMMEDIATE décrire l'utilisation et l'application de certains packages fournis par le serveur Oracle : DBMS_DDL DBMS_JOB DBMS_OUTPUT

benito
Download Presentation

Packages fournis par Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Packages fournis par Oracle

  2. Objectifs A la fin de ce chapitre, vous pourrez : • créer des instructions SQL dynamiques en utilisant DBMS_SQL et EXECUTE IMMEDIATE • décrire l'utilisation et l'application de certains packages fournis par le serveur Oracle : • DBMS_DDL • DBMS_JOB • DBMS_OUTPUT • UTL_FILE • UTL_HTTP et UTL_TCP

  3. Utiliser les packages fournis Les packages fournis par Oracle : • sont livrés avec le serveur Oracle, • étendent les fonctionnalités de la base de données • permettent d'accéder à certaines fonctionnalités SQL réservées normalement au langage PL/SQL

  4. Utiliser le code SQL dynamique natif Le code SQL dynamique : • est une instruction SQL qui contient des variables susceptibles de changer lors de l'exécution • est une instruction SQL stockée en tant que chaîne de caractères et contenant des marques de réservation • permet d'écrire du code à usage général • permet d'écrire et d'exécuter des instructions de définition de données, de contrôle des données ou de contrôle des sessions à partir du langage PL/SQL • est écrit en utilisant DBMS_SQL ou le langage SQL dynamique natif

  5. Flux d'exécution Les instructions SQL passent par plusieurs étapes : • analyse • affectation de valeur • exécution • extraction Remarque : Certaines étapes peuvent être ignorées.

  6. Utiliser le package DBMS_SQL Le package DBMS_SQL permet d'écrire du code SQL dynamique dans des procédures stockées et d'analyser les instructions LDD (Langage de définition de données). Le package inclut les procédures et les fonctions suivantes : • OPEN_CURSOR • PARSE • BIND_VARIABLE • EXECUTE • FETCH_ROWS • CLOSE_CURSOR

  7. Utiliser DBMS_SQL CREATE OR REPLACE PROCEDURE delete_all_rows (p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER) IS cursor_name INTEGER; BEGIN cursor_name := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cursor_name, 'DELETE FROM '||p_tab_name, DBMS_SQL.NATIVE ); p_rows_del := DBMS_SQL.EXECUTE (cursor_name); DBMS_SQL.CLOSE_CURSOR(cursor_name); END; / Utilisez le code SQL dynamique pour supprimer des lignes VARIABLE deleted NUMBER EXECUTE delete_all_rows('employees', :deleted) PRINT deleted

  8. Utiliser l'instruction EXECUTE IMMEDIATE Utilisez l'instruction EXECUTE IMMEDIATE pour améliorer les performances du code SQL dynamique natif. • La clause INTO est utilisée pour les interrogations monolignes et indique les variables ou enregistrements dans lesquels les valeurs des colonnes sont extraites • La clause USING est utilisée pour recevoir tous les arguments attachés. Le mode de paramètre par défaut est IN EXECUTE IMMEDIATE dynamic_string [INTO {define_variable [, define_variable] ... | record}] [USING [IN|OUT|IN OUT] bind_argument [, [IN|OUT|IN OUT] bind_argument] ... ];

  9. Instruction SQL dynamique utilisant EXECUTE IMMEDIATE CREATE PROCEDURE del_rows (p_table_name IN VARCHAR2, p_rows_deld OUT NUMBER) IS BEGIN EXECUTE IMMEDIATE 'delete from '||p_table_name; p_rows_deld := SQL%ROWCOUNT; END; / VARIABLE deleted NUMBER EXECUTE del_rows('test_employees',:deleted) PRINT deleted

  10. Utiliser le package DBMS_DDL Le package DBMS_DDL : • permet d'accéder à certaines instructions SQL LDD depuis des procédures stockées • inclut certaines procédures : • ALTER_COMPILE (object_type, owner, object_name) • ANALYZE_OBJECT (object_type, owner, name, method) Remarque : Le package s'exécute avec les privilèges de l'utilisateur appelant, plutôt qu'avec ceux du propriétaire du package, SYS. DBMS_DDL.ALTER_COMPILE('PROCEDURE','A_USER','QUERY_EMP') DBMS_DDL.ANALYZE_OBJECT('TABLE','A_USER','JOBS','COMPUTE')

  11. Utiliser DBMS_JOB pour la programmation DBMS_JOB permet de programmer et d'exécuter les programmes PL/SQL afin de : • soumettre des travaux • exécuter des travaux • modifier les paramètres d'exécution des travaux • supprimer des travaux • suspendre des travaux

  12. Sous-programmes DBMS_JOB Sous-programmes disponibles : • SUBMIT • REMOVE • CHANGE • WHAT • NEXT_DATE • INTERVAL • BROKEN • RUN

  13. Soumettre les travaux Vous pouvez soumettre les travaux en utilisant la procédure DBMS_JOB.SUBMIT. Paramètres disponibles : • JOB OUT BINARY_INTEGER • WHAT IN VARCHAR2 • NEXT_DATE IN DATE DEFAULT SYSDATE • INTERVAL IN VARCHAR2 DEFAULT 'NULL' • NO_PARSE IN BOOLEAN DEFAULT FALSE

  14. Soumettre des travaux Utilisez DBMS_JOB.SUBMIT pour placer un travail à exécuter dans la file d'attente des travaux. VARIABLE jobno NUMBER BEGIN DBMS_JOB.SUBMIT ( job => :jobno, what => 'OVER_PACK.ADD_DEPT(''EDUCATION'',2710);', next_date => TRUNC(SYSDATE + 1), interval => 'TRUNC(SYSDATE + 1)' ); COMMIT; END; / PRINT jobno

  15. Modifier les caractéristiques des travaux • DBMS_JOB.CHANGE : modifie les paramètres WHAT, NEXT_DATE et INTERVAL • DBMS_JOB.INTERVAL : modifie le paramètre INTERVAL • DBMS_JOB.NEXT_DATE : modifie la date d'exécution suivante • DBMS_JOB.WHAT : modifie le paramètre WHAT

  16. Exécuter, supprimer et suspendre des travaux • DBMS_JOB.RUN : exécute immédiatement un travail soumis • DBMS_JOB.REMOVE : supprime un travail soumis de la file d'attente des travaux • DBMS_JOB.BROKEN : identifie un travail soumis comme suspendu (un travail suspendu ne s'exécute pas)

  17. Visualiser les informations sur les travaux soumis • Utilisez la vue du dictionnaire DBA_JOBS pour consulter l'état des travaux soumis. • Utilisez la vue du dictionnaire DBA_JOBS_RUNNING pour afficher les travaux en cours d'exécution. SELECT job, log_user, next_date, next_sec, broken, what FROM DBA_JOBS;

  18. Utiliser le package DBMS_OUTPUT Le package DBMS_OUTPUT vous permet de sortir des messages des blocs PL/SQL. Procédures disponibles : • PUT • NEW_LINE • PUT_LINE • GET_LINE • GET_LINES • ENABLE/DISABLE

  19. Interagir avec les fichiers du système d'exploitation • Le package UTL_FILE fourni par Oracle : • offre des fonctions d'E/S sur les fichiers texte • est disponible avec la version 7.3 et les versions ultérieures • Le package DBMS_LOB fourni par Oracle : • permet d'effectuer des opérations en lecture seule sur les fichiers BFILES externes • est disponible avec la version 8 et les versions ultérieures • permet d'effectuer des opérations de lecture et d'écriture sur les objets LOB internes

  20. Présentation du package UTL_FILE • Le package UTL_FILE étend les E/S aux fichiers texte en PL/SQL • Il assure la sécurité des répertoires du serveur via le fichier init.ora • Il offre des fonctions d'E/S similaires à celle d'un système d'exploitation standard • ouverture des fichiers • extraction de texte • insertion de texte • fermeture des fichiers • utilisation des exceptions spécifiques au package UTL_FILE

  21. Oui Traiter les fichiers en utilisant le package UTL_FILE Extraire les lignes du fichier texte Ouvrir le fichier texte Autres lignes à traiter ? Fermer le fichier texte Non Insérer les lignes dans le fichier texte

  22. Procédures et fonctions du package UTL_FILE • Fonction FOPEN • Fonction IS_OPEN • Procédure GET_LINE • Procédure PUT, PUT_LINE, et PUTF • Procédure NEW_LINE • Procédure FFLUSH • Procédure FCLOSE, et FCLOSE_ALL

  23. Exceptions spécifiques au package UTL_FILE • INVALID_PATH • INVALID_MODE • INVALID_FILEHANDLE • INVALID_OPERATION • READ_ERROR • WRITE_ERROR • INTERNAL_ERROR

  24. Fonctions FOPEN et IS_OPEN FUNCTION FOPEN (location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2) RETURN UTL_FILE.FILE_TYPE; FUNCTION IS_OPEN (file_handle IN FILE_TYPE) RETURN BOOLEAN;

  25. Utiliser UTL_FILE sal_status.sql CREATE OR REPLACE PROCEDURE sal_status (p_filedir IN VARCHAR2, p_filename IN VARCHAR2) IS v_filehandle UTL_FILE.FILE_TYPE; CURSOR emp_info IS SELECT last_name, salary, department_id FROM employees ORDER BY department_id; v_newdeptno employees.department_id%TYPE; v_olddeptno employees.department_id%TYPE := 0; BEGIN v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,'w'); UTL_FILE.PUTF (v_filehandle,'SALARY REPORT: GENERATED ON %s\n', SYSDATE); UTL_FILE.NEW_LINE (v_filehandle); FOR v_emp_rec IN emp_info LOOP v_newdeptno := v_emp_rec.department_id; ...

  26. Utiliser UTL_FILE sal_status.sql ... IF v_newdeptno <> v_olddeptno THEN UTL_FILE.PUTF (v_filehandle, 'DEPARTMENT: %s\n', v_emp_rec.department_id); END IF; UTL_FILE.PUTF (v_filehandle,' EMPLOYEE: %s earns: %s\n', v_emp_rec.last_name, v_emp_rec.salary); v_olddeptno := v_newdeptno; END LOOP; UTL_FILE.PUT_LINE (v_filehandle, '*** END OF REPORT ***'); UTL_FILE.FCLOSE (v_filehandle); EXCEPTION WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR (-20001, 'Invalid File.'); WHEN UTL_FILE.WRITE_ERROR THEN RAISE_APPLICATION_ERROR (-20002, 'Unable to write to file'); END sal_status; /

  27. Package UTL_HTTP Le package UTL_HTTP : • permet d'effectuer des appels programme externes à l'aide du protocole HTTP depuis les langages PL/SQL et SQL, afin d'accéder aux données sur Internet • contient les fonctions REQUEST et REQUEST_PIECES, qui utilisent l'URL d'un site en tant que paramètre, contactent le site et renvoient les données obtenues sur celui-ci • peut être défini dans les fonctions ci-dessus en utilisant un paramètre proxy, si le client est protégé par un pare-feu • déclenche les exceptions INIT_FAILED ou REQUEST_FAILED en cas d'échec d'un appel HTTP • génère un message signalant une erreur HTML si l'URL indiquée n'est pas accessible

  28. Utiliser le package UTL_HTTP SELECT UTL_HTTP.REQUEST('http://www.oracle.com', 'edu-proxy.us.oracle.com') FROM DUAL;

  29. Utiliser le package UTL_TCP Le package UTL_TCP : • permet aux applications PL/SQL de communiquer avec les serveurs TCP/IP externes via le protocole TCP/IP • contient des fonctions permettant d'établir et d'interrompre les connexions, de lire ou d'écrire les données binaires ou textuelles d'un service via une connexion ouverte • requiert un hôte et un port distant, ainsi qu'un hôte et un port local, en tant qu'arguments pour ses fonctions • déclenche des exceptions lorsque la taille de la mémoire tampon est insuffisante, lorsqu'une connexion n'offre plus de données à lire, lorsqu'une erreur réseau générique se produit ou lorsque des arguments incorrects sont transmis à un appel de fonction

  30. DBMS_ALERT DBMS_APPLICATION_INFO DBMS_DESCRIBE DBMS_LOCK DBMS_SESSION DBMS_SHARED_POOL DBMS_TRANSACTION DBMS_UTILITY Packages fournis par Oracle Autres packages fournis par Oracle :

  31. Synthèse Ce chapitre vous a permis d'apprendre à : • tirer parti des packages préconfigurés fournis par Oracle • créer des packages en utilisant le script catproc.sql • créer des packages de façon individuelle

  32. Présentation de l'exercice 7 Dans cet exercice, vous allez utiliser : • DBMS_SQL pour créer du code SQL dynamique • DBMS_DDL pour analyser une table • DBMS_JOB pour programmer une tâche • UTL_FILE pour générer des états de type texte

More Related