7 - Free | slideum.com

7 - Free

Transcript 7 - Free

Packages fournis par Oracle

7-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

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 7-3

Utiliser le code SQL dynamique natif

7-4

• • • • •

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 dynamique natif ou le langage SQL

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.

7-5

Utiliser le package DBMS_SQL

7-6 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-8

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

7-9

Utiliser l'instruction EXECUTE IMMEDIATE

Utilisez l'instruction EXECUTE IMMEDIATE pour améliorer les performances du code SQL dynamique natif.

• •

EXECUTE IMMEDIATE dynamic_string [INTO {define_variable [, define_variable] ... | record}] [USING [IN|OUT|IN OUT] bind_argument [, [IN|OUT|IN OUT] bind_argument] ... ]; 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 arguments attachés. Le mode de paramètre par défaut est IN USING est utilisée pour recevoir tous les

7-11

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

Utiliser le package DBMS_DDL

7-12

• •

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) DBMS_DDL.ALTER_COMPILE('PROCEDURE','A_USER','QUERY_EMP')

–

ANALYZE_OBJECT (object_type, owner, name, method) DBMS_DDL.ANALYZE_OBJECT('TABLE','A_USER','JOBS','COMPUTE') 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 .

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 7-13

7-14

Sous-programmes DBMS_JOB

• • • • • • • •

Sous-programmes disponibles : SUBMIT REMOVE CHANGE WHAT NEXT_DATE INTERVAL BROKEN RUN

7-15

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

7-16

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

Modifier les caractéristiques des travaux

• • • •

DBMS_JOB.CHANGE

NEXT_DATE et : modifie les paramètres INTERVAL WHAT , DBMS_JOB.INTERVAL

INTERVAL : modifie le paramètre DBMS_JOB.NEXT_DATE

suivante : modifie la date d'exécution DBMS_JOB.WHAT

: modifie le paramètre WHAT 7-17

Exécuter, supprimer et suspendre des travaux

• • •

DBMS_JOB.RUN

soumis : exécute immédiatement un travail 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) 7-18

7-19

Visualiser les informations sur les travaux soumis

•

Utilisez la vue du dictionnaire DBA_JOBS consulter l'état des travaux soumis.

pour SELECT job, log_user, next_date, next_sec, broken, what FROM DBA_JOBS;

•

Utilisez la vue du dictionnaire DBA_JOBS_RUNNING pour afficher les travaux en cours d'exécution.

7-20

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

7-21

Interagir avec les fichiers du système d'exploitation

• •

Le package UTL_FILE fourni par Oracle :

– –

Le package DBMS_LOB 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 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

Présentation du package UTL_FILE

7-22

• • •

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

Traiter les fichiers en utilisant le package UTL_FILE

Ouvrir le fichier texte Extraire les lignes du fichier texte Insérer les lignes dans le fichier texte Oui Autres lignes à traiter ?

Non Fermer le fichier texte 7-24

Procédures et fonctions du package UTL_FILE

• • • • • • •

Fonction FOPEN Fonction Procédure Procédure IS_OPEN GET_LINE PUT, PUT_LINE, et PUTF Procédure Procédure Procédure NEW_LINE FFLUSH FCLOSE, et FCLOSE_ALL 7-25

Exceptions spécifiques au package UTL_FILE

• • • • • • •

INVALID_PATH INVALID_MODE INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR WRITE_ERROR INTERNAL_ERROR 7-27

7-28

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;

7-29

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; ...

7-30

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; /

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 REQUEST_FAILED ou 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

7-31

Utiliser le package UTL_HTTP

SELECT UTL_HTTP.REQUEST('http://www.oracle.com', 'edu-proxy.us.oracle.com') FROM DUAL; 7-32

7-34

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

Packages fournis par Oracle

• • • • •

Autres packages fournis par Oracle : DBMS_ALERT DBMS_APPLICATION_INFO DBMS_DESCRIBE

• • •

DBMS_SHARED_POOL DBMS_TRANSACTION DBMS_UTILITY DBMS_LOCK DBMS_SESSION 7-35

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 7-40

Présentation de l'exercice 7

• • • •

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

7 - Free

Transcript 7 - Free

Packages fournis par Oracle

Objectifs

Utiliser les packages fournis

Utiliser le code SQL dynamique natif

Flux d'exécution

Utiliser le package DBMS_SQL

Utiliser DBMS_SQL

Utiliser l'instruction EXECUTE IMMEDIATE

Instruction SQL dynamique utilisant EXECUTE IMMEDIATE

Utiliser le package DBMS_DDL

Utiliser DBMS_JOB pour la programmation

Sous-programmes DBMS_JOB

Soumettre les travaux

Soumettre des travaux

Modifier les caractéristiques des travaux

Exécuter, supprimer et suspendre des travaux

Visualiser les informations sur les travaux soumis

Utiliser le package DBMS_OUTPUT

Interagir avec les fichiers du système d'exploitation

Présentation du package UTL_FILE

Traiter les fichiers en utilisant le package UTL_FILE

Procédures et fonctions du package UTL_FILE

Exceptions spécifiques au package UTL_FILE

Fonctions FOPEN et IS_OPEN

Utiliser UTL_FILE

Utiliser UTL_FILE

Package UTL_HTTP

Utiliser le package UTL_HTTP

Utiliser le package UTL_TCP

Packages fournis par Oracle

Synthèse

Présentation de l'exercice 7

Directory