Création du package admin_pkg
Cette section explique comment créer le package admin_pkg, comment ses sous-programmes fonctionnent, comment accorder le privilège d'exécution sur le package à l'utilisateur qui en a besoin et comment cet utilisateur peut appeler l'un de ses sous-programmes.
Pour créer le package admin_pkg, procédez comme suit :
-
Connectez-vous à Oracle Database en tant qu'utilisateur app_admin.
Pour obtenir des instructions, reportez-vous à Connexion à Oracle Database à partir de SQL*Plus ou à Connexion à Oracle Database à partir de SQL Developer.
-
Créez les synonymes suivants :
CREATE SYNONYM departments FOR app_data.departments; CREATE SYNONYM jobs FOR app_data.jobs; CREATE SYNONYM departments_sequence FOR app_data.departments_sequence;Vous pouvez entrer les instructions CREATE SYNONYM dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également créer les tables à l'aide de l'outil SQL Developer Create Synonym.
-
Créez la spécification de package.
-
Créez le corps de package.
Voir aussi :
Création de la spécification de package pour admin_pkg
Remarque : vous devez être connecté à Oracle Database en tant qu'utilisateur app_admin.
Pour créer la spécification de PACKAGE pour admin_pkg, l'API pour les administrateurs d'application, utilisez l'instruction CREATE PACKAGE suivante. Vous pouvez entrer l'instruction dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également créer le package à l'aide de l'outil SQL Developer Create Package.
CREATE OR REPLACE PACKAGE admin_pkg
AS
PROCEDURE update_job
( p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE := NULL,
p_min_salary IN jobs.min_salary%TYPE := NULL,
p_max_salary IN jobs.max_salary%TYPE := NULL );
PROCEDURE add_job
( p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE,
p_min_salary IN jobs.min_salary%TYPE,
p_max_salary IN jobs.max_salary%TYPE );
PROCEDURE update_department
( p_department_id IN departments.department_id%TYPE,
p_department_name IN departments.department_name%TYPE := NULL,
p_manager_id IN departments.manager_id%TYPE := NULL,
p_update_manager_id IN BOOLEAN := FALSE );
FUNCTION add_department
( p_department_name IN departments.department_name%TYPE,
p_manager_id IN departments.manager_id%TYPE )
RETURN departments.department_id%TYPE;
END admin_pkg;
/Voir aussi :
-
Oracle Database PL/SQL Language Reference, pour plus d'informations sur l'instruction CREATE PACKAGE
Création du corps du package pour admin_pkg
Remarque : vous devez être connecté à Oracle Database en tant qu'utilisateur app_admin.
Pour créer le corps du PACKAGE pour admin_pkg, l'API pour les administrateurs d'application, utilisez l'instruction CREATE PACKAGE BODY suivante. Vous pouvez entrer l'instruction dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également créer le package à l'aide de l'outil SQL Developer Create Body.
CREATE OR REPLACE PACKAGE BODY admin_pkg
AS
PROCEDURE update_job
( p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE := NULL,
p_min_salary IN jobs.min_salary%TYPE := NULL,
p_max_salary IN jobs.max_salary%TYPE := NULL )
IS
BEGIN
UPDATE jobs
SET job_title = NVL( p_job_title, job_title ),
min_salary = NVL( p_min_salary, min_salary ),
max_salary = NVL( p_max_salary, max_salary )
WHERE job_id = p_job_id;
END update_job;
PROCEDURE add_job
( p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE,
p_min_salary IN jobs.min_salary%TYPE,
p_max_salary IN jobs.max_salary%TYPE )
IS
BEGIN
INSERT INTO jobs ( job_id, job_title, min_salary, max_salary )
VALUES ( p_job_id, p_job_title, p_min_salary, p_max_salary );
END add_job;
PROCEDURE update_department
( p_department_id IN departments.department_id%TYPE,
p_department_name IN departments.department_name%TYPE := NULL,
p_manager_id IN departments.manager_id%TYPE := NULL,
p_update_manager_id IN BOOLEAN := FALSE )
IS
BEGIN
IF ( p_update_manager_id ) THEN
UPDATE departments
SET department_name = NVL( p_department_name, department_name ),
manager_id = p_manager_id
WHERE department_id = p_department_id;
ELSE
UPDATE departments
SET department_name = NVL( p_department_name, department_name )
WHERE department_id = p_department_id;
END IF;
END update_department;
FUNCTION add_department
( p_department_name IN departments.department_name%TYPE,
p_manager_id IN departments.manager_id%TYPE )
RETURN departments.department_id%TYPE
IS
l_department_id departments.department_id%TYPE;
BEGIN
INSERT INTO departments ( department_id, department_name, manager_id )
VALUES ( departments_sequence.NEXTVAL, p_department_name, p_manager_id )
RETURNING department_id INTO l_department_id;
RETURN l_department_id;
END add_department;
END admin_pkg;
/Voir aussi :
-
Oracle Database PL/SQL Language Reference, pour plus d'informations sur l'instruction CREATE PACKAGE BODY
Tutoriel : Affichage du fonctionnement des sous-programmes admin_pkg
A l'aide de SQL*Plus, ce tutoriel montre comment fonctionnent les sous-programmes du package admin_pkg. Le tutoriel montre également comment fonctionne le déclencheur jobs_aufer.
Remarque : vous devez être connecté à Oracle Database en tant qu'utilisateur app_admin à partir de SQL*Plus.
Etapes à suivre pour afficher le fonctionnement des sous-programmes admin_pkg :
-
Afficher les informations sur le travail dont l'ID est AD_VP :
SELECT * FROM jobs WHERE job_id = 'AD_VP';Résultats :
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_VP Administration Vice President 15000 30000 -
Augmentez le salaire maximum pour ce poste et affichez à nouveau les informations le concernant :
EXEC admin_pkg.update_job( 'AD_VP', p_max_salary => 31000 ); SELECT * FROM jobs WHERE job_id = 'AD_VP';Résultats :
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_VP Administration Vice President 15000 31000 -
Affichez les informations relatives à l'emploi dont le code est IT_PROG :
SELECT * FROM jobs WHERE job_id = 'IT_PROG';Résultats :
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- IT_PROG Programmer 4000 10000 -
Essayez d'augmenter le salaire maximum pour ce poste :
EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 );Résultat (depuis SQL*Plus) :
SQL> EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 ); BEGIN admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 ); END; * ERROR at line 1: ORA-20001: Salary update would violate 5 existing employee records ORA-06512: at "APP_DATA.JOBS_AUFER", line 12 ORA-04088: error during execution of trigger 'APP_DATA.JOBS_AUFER' ORA-06512: at "APP_ADMIN.ADMIN_PKG", line 10 ORA-06512: at line 1 -
Ajoutez un nouveau travail et affichez les informations le concernant :
EXEC admin_pkg.add_job( 'AD_CLERK', 'Administrative Clerk', 3000, 7000 ); SELECT * FROM jobs WHERE job_id = 'AD_CLERK';Résultats :
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_CLERK Administrative Clerk 3000 7000 -
Afficher les informations sur le service 100 :
SELECT * FROM departments WHERE department_id = 100;Résultats :
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 100 Finance 108 -
Modifiez le nom et le responsable du département 100 et affichez les informations le concernant :
EXEC admin_pkg.update_department( 100, 'Financial Services' ); EXEC admin_pkg.update_department( 100, p_manager_id => 111, p_update_manager_id => true ); SELECT * FROM departments WHERE department_id = 100;Résultats :
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 100 Financial Services 111
Voir aussi : "Création et gestion de packages"
Accorder le privilège Execute à app_admin_user
Remarque : vous devez être connecté à Oracle Database en tant qu'utilisateur app_admin.
Pour accorder le privilège d'exécution sur le package admin_pkg à app_admin_user (administrateur d'application), utilisez l'instruction GRANT suivante. Vous pouvez entrer l'instruction dans SQL*Plus ou dans la feuille de calcul de SQL Developer.
GRANT EXECUTE ON admin_pkg TO app_admin_user;Voir aussi :
-
Manuel Oracle Database SQL Language Reference, pour plus d'informations sur l'instruction GRANT
Tutoriel : Appel de add_department en tant qu'app_admin_user
A l'aide de SQL*Plus, ce tutoriel explique comment appeler la fonction app_admin.admin_pkg.add_department en tant qu'utilisateur app_admin_user (administrateur d'application), puis comment afficher les informations relatives au nouveau service.
Etapes à suivre pour appeler admin_pkg.add_department en tant qu'app_admin_user :
-
Connectez-vous à Oracle Database en tant qu'utilisateur app_admin_user depuis SQL*Plus.
Pour obtenir des instructions, reportez-vous à Connexion à Oracle Database à partir de SQL*Plus.
-
Créez le synonyme suivant :
CREATE SYNONYM admin_pkg FOR app_admin.admin_pkg; -
Déclarez une variable attachée pour la valeur renvoyée par la fonction :
VARIABLE n NUMBER -
Ajoutez un nouveau service sans responsable :
EXEC :n := admin_pkg.add_department( 'New department', NULL ); -
Affichez l'ID du responsable du nouveau service :
PRINT :nRésultats :
N ---------- 275
Pour afficher les informations sur le nouveau service, procédez comme suit :
-
Connectez-vous à Oracle Database en tant qu'utilisateur app_admin.
-
Affichez les informations relatives au nouveau service :
SELECT * FROM departments WHERE department_name LIKE 'New department%';Résultats :
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 275 New department