Criando o pacote admin_pkg

Esta seção mostra como criar o pacote admin_pkg, como seus subprogramas funcionam, como conceder o privilégio de execução no pacote ao usuário que precisa dele e como esse usuário pode chamar um de seus subprogramas.

Para criar o pacote admin_pkg:

Conecte-se ao Oracle Database como usuário app_admin.

Para obter instruções, consulte "Connecting to Oracle Database from SQL*Plus" ou "Connecting to Oracle Database from SQL Developer".
Crie os seguintes sinônimos:
```
 CREATE SYNONYM departments FOR app_data.departments;
 CREATE SYNONYM jobs FOR app_data.jobs;
 CREATE SYNONYM departments_sequence FOR app_data.departments_sequence;
```
Você pode informar as instruções CREATE SYNONYM no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode criar as tabelas com a ferramenta SQL Developer Create Synonym.
Crie a especificação do pacote.
Crie o corpo do pacotes.

Consulte também:

Criando a especificação de pacote para admin_pkg

Observação: Você deve estar conectado ao Oracle Database como usuário app_admin.

Para criar a especificação de pacote para admin_pkg, a API para administradores de aplicativos, use a seguinte instrução CREATE PACKAGE. Você pode informar a instrução no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode criar o pacote com a ferramenta 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;
/

Consulte também:

"Sobre o Aplicativo"
"Criando e Gerenciando Pacotes"
Referência da Linguagem PL/SQL do Oracle Database para informações sobre a instrução CREATE PACKAGE

Criando o corpo do pacote para admin_pkg

Observação: Você deve estar conectado ao Oracle Database como usuário app_admin.

Para criar o corpo do pacote para admin_pkg, a API para administradores de aplicativos, use a seguinte instrução CREATE PACKAGE BODY. Você pode informar a instrução no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode criar o pacote com a ferramenta 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;
/

Consulte também:

"Sobre o Aplicativo"
"Criando e Gerenciando Pacotes"
Oracle Database PL/SQL Language Reference para informações sobre a instrução CREATE PACKAGE BODY

Tutorial: Mostrando como funcionam os subprogramas admin_pkg

Usando o SQL*Plus, este tutorial mostra como os subprogramas do pacote admin_pkg funcionam. O tutorial também mostra como o trigger jobs_aufer funciona.

Observação: Você deve estar conectado ao Oracle Database como usuário app_admin do SQL*Plus.

Etapas para mostrar como os subprogramas admin_pkg funcionam:

Mostre as informações sobre o cargo cujo ID seja AD_VP:

 SELECT * FROM jobs WHERE job_id = 'AD_VP';

Resultado:

 JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

 ---------- ----------------------------------- ---------- ----------
 AD_VP      Administration Vice President            15000      30000

Aumente o salário máximo para este cargo e mostre as informações sobre ele novamente:

 EXEC admin_pkg.update_job( 'AD_VP', p_max_salary => 31000 );
 SELECT * FROM jobs WHERE job_id = 'AD_VP';

Resultado:

 JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

 ---------- ----------------------------------- ---------- ----------
 AD_VP      Administration Vice President            15000      31000

Mostre as informações sobre o cargo cujo ID seja IT_PROG:

 SELECT * FROM jobs WHERE job_id = 'IT_PROG';

Resultado:

 JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

 ---------- ----------------------------------- ---------- ----------
 IT_PROG    Programmer                                4000      10000

Tente aumentar o salário máximo deste cargo:

 EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 );

Resultado (do 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

Adicione um novo job e mostre as informações sobre ele:

 EXEC admin_pkg.add_job( 'AD_CLERK', 'Administrative Clerk', 3000, 7000 );
 SELECT * FROM jobs WHERE job_id = 'AD_CLERK';

Resultado:

 JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

 ---------- ----------------------------------- ---------- ----------
 AD_CLERK   Administrative Clerk                      3000       7000

Mostre as informações sobre o departamento 100:

 SELECT * FROM departments WHERE department_id = 100;

Resultado:

 DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID

 ------------- ------------------------------ ----------
           100 Finance                               108

Altere o nome e o gerente do departamento 100 e mostre as informações sobre ele:

 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;

Resultado:

 DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID

 ------------- ------------------------------ ----------
           100 Financial Services                    111

Consulte Também: "Criando e Gerenciando Pacotes"

Concedendo o Privilégio de Execução a app_admin_user

Observação: Você deve estar conectado ao Oracle Database como usuário app_admin.

Para conceder o privilégio de execução no pacote admin_pkg a app_admin_user (um administrador de aplicativo), use a seguinte instrução GRANT. Você pode informar a instrução no SQL*Plus ou na Planilha do SQL Developer.

GRANT EXECUTE ON admin_pkg TO app_admin_user;

Consulte também:

"Esquemas do Aplicativo"
Referência da Linguagem SQL do Oracle Database para informações sobre a instrução GRANT

Tutorial: Chamando add_department como app_admin_user

Usando o SQL*Plus, este tutorial mostra como chamar a função app_admin.admin_pkg.add_department como o usuário app_admin_user (um administrador de aplicativo) e, em seguida, ver as informações sobre o novo departamento.

Etapas para chamar admin_pkg.add_department como app_admin_user:

Estabeleça conexão com o Oracle Database como usuário app_admin_user do SQL*Plus.

Para obter instruções, consulte "Estabelecendo Conexão com o Oracle Database no SQL*Plus".

Crie este sinônimo:

 CREATE SYNONYM admin_pkg FOR app_admin.admin_pkg;

Declare uma variável de bind para o valor de retorno da função:
```
 VARIABLE n NUMBER
```

Adicione um novo departamento sem um gerente:

 EXEC :n := admin_pkg.add_department( 'New department', NULL );

Exiba o ID do gerente do novo departamento:

 PRINT :n

Resultado:

         N

 ----------
       275

Etapas para ver as informações sobre o novo departamento:

Conecte-se ao Oracle Database como usuário app_admin.

Mostre as informações sobre o novo departamento:

 SELECT * FROM departments WHERE department_name LIKE 'New department%';

Resultado:

 DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID

 ------------- ------------------------------ ----------
           275 New department