Creazione del pacchetto admin_pkg
Questa sezione mostra come creare il pacchetto admin_pkg, come funzionano i suoi sottoprogrammi, come concedere il privilegio di esecuzione sul pacchetto all'utente che lo richiede e come tale utente può richiamare uno dei suoi sottoprogrammi.
Per creare il pacchetto admin_pkg:
-
Eseguire la connessione a Oracle Database come utente app_admin.
Per le istruzioni, vedere "Connessione a Oracle Database da SQL*Plus" o "Connessione a Oracle Database da SQL Developer".
-
Creare i seguenti sinonimi:
CREATE SYNONYM departments FOR app_data.departments; CREATE SYNONYM jobs FOR app_data.jobs; CREATE SYNONYM departments_sequence FOR app_data.departments_sequence;È possibile immettere le istruzioni CREATE SYNONYM in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile creare le tabelle con lo strumento SQL Developer Crea sinonimo.
-
Creare la specifica package.
-
Creare il corpo del package.
Vedere anche:
Creazione della specifica di pacchetto per admin_pkg
Nota: è necessario essere connessi a Oracle Database come utente app_admin.
Per creare la specifica del pacchetto per admin_pkg, l'API per gli amministratori dell'applicazione, utilizzare la seguente istruzione CREATE PACKAGE. È possibile immettere l'istruzione in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile creare il package con lo strumento SQL Developer Crea 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;
/Vedere anche:
-
Oracle Database PL/SQL Language Reference per informazioni sull'istruzione CREATE PACKAGE
Creazione del corpo del pacchetto per admin_pkg
Nota: è necessario essere connessi a Oracle Database come utente app_admin.
Per creare il corpo del pacchetto per admin_pkg, l'API per gli amministratori dell'applicazione, utilizzare la seguente istruzione CREATE PACKAGE BODY. È possibile immettere l'istruzione in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile creare il pacchetto con lo strumento SQL Developer Crea corpo.
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;
/Vedere anche:
-
Oracle Database PL/SQL Language Reference per informazioni sull'istruzione CREATE PACKAGE BODY
Esercitazione: Visualizzazione del funzionamento dei sottoprogrammi admin_pkg
Utilizzando SQL*Plus, questa esercitazione mostra come funzionano i sottoprogrammi del package admin_pkg. Il tutorial mostra anche come funziona il trigger jobs_aufer.
Nota: è necessario essere connessi a Oracle Database come utente app_admin da SQL*Plus.
Passi per mostrare il funzionamento dei sottoprogrammi admin_pkg:
-
Mostra le informazioni sulla mansione con ID AD_VP:
SELECT * FROM jobs WHERE job_id = 'AD_VP';Risultato:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_VP Administration Vice President 15000 30000 -
Aumentare lo stipendio massimo per questa mansione e mostrare di nuovo le informazioni su di essa:
EXEC admin_pkg.update_job( 'AD_VP', p_max_salary => 31000 ); SELECT * FROM jobs WHERE job_id = 'AD_VP';Risultato:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_VP Administration Vice President 15000 31000 -
Mostra le informazioni sulla mansione con ID IT_PROG:
SELECT * FROM jobs WHERE job_id = 'IT_PROG';Risultato:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- IT_PROG Programmer 4000 10000 -
Prova ad aumentare lo stipendio massimo per questo lavoro:
EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 );Risultato (da 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 -
Aggiungere un nuovo job e mostrare le informazioni su di esso:
EXEC admin_pkg.add_job( 'AD_CLERK', 'Administrative Clerk', 3000, 7000 ); SELECT * FROM jobs WHERE job_id = 'AD_CLERK';Risultato:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_CLERK Administrative Clerk 3000 7000 -
Mostra le informazioni sul reparto 100:
SELECT * FROM departments WHERE department_id = 100;Risultato:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 100 Finance 108 -
Modificare il nome e il manager del reparto 100 e visualizzare le informazioni su di esso:
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;Risultato:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 100 Financial Services 111
Vedere anche: "Creazione e gestione di package"
Concessione del privilegio di esecuzione a app_admin_user
Nota: è necessario essere connessi a Oracle Database come utente app_admin.
Per concedere il privilegio di esecuzione sul pacchetto admin_pkg a app_admin_user (amministratore dell'applicazione), utilizzare la seguente istruzione GRANT. È possibile immettere l'istruzione in SQL*Plus o nel foglio di lavoro di SQL Developer.
GRANT EXECUTE ON admin_pkg TO app_admin_user;Vedere anche:
-
Oracle Database SQL Language Reference per informazioni sull'istruzione GRANT
Esercitazione: richiamo di add_department come app_admin_user
Utilizzando SQL*Plus, questa esercitazione mostra come richiamare la funzione app_admin.admin_pkg.add_department come utente app_admin_user (amministratore dell'applicazione) e quindi visualizzare le informazioni sul nuovo reparto.
Passi per richiamare admin_pkg.add_department come app_admin_user:
-
Connettersi a Oracle Database come utente app_admin_user da SQL*Plus.
Per le istruzioni, vedere "Connessione a Oracle Database da SQL*Plus".
-
Crea questo sinonimo:
CREATE SYNONYM admin_pkg FOR app_admin.admin_pkg; -
Dichiarare una bind variable per il valore restituito della funzione:
VARIABLE n NUMBER -
Aggiungere un nuovo reparto senza un manager:
EXEC :n := admin_pkg.add_department( 'New department', NULL ); -
Mostra l'ID del manager del nuovo reparto:
PRINT :nRisultato:
N ---------- 275
Passi per visualizzare le informazioni sul nuovo reparto:
-
Eseguire la connessione a Oracle Database come utente app_admin.
-
Mostra le informazioni sul nuovo reparto:
SELECT * FROM departments WHERE department_name LIKE 'New department%';Risultato:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 275 New department